is date_part immutable or not?
PostgreSQL 12.4
Just create partitioned table for PostgreSQL logs
CREATE TABLE pglog.pglog (
log_time timestamp(3) with time zone,
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp with time zone,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text
) PARTITION BY LIST (date_part('isodow', log_time));
ERROR: functions in partition key expression must be marked IMMUTABLE
But, date_part is immutable
Schema | pg_catalog
Name | date_part
Result data type | double precision
Argument data types | text, time with time zone
Type | func
Volatility | immutable
Parallel | safe
Owner | postgres
Security | invoker
Access privileges |
Language | internal
Source code | timetz_part
Description | extract field from time with time zone
What is wrong here?
My mistake.
Schema | pg_catalog
Name | date_part
Result data type | double precision
Argument data types | text, timestamp with time zone
Type | func
Volatility | stable
Parallel | safe
Owner | postgres
Security | invoker
Access privileges |
Language | internal
Source code | timestamptz_part
Description | extract field from timestamp with time zone
is stable, but
Schema | pg_catalog
Name | date_part
Result data type | double precision
Argument data types | text, date
Type | func
Volatility | immutable
Parallel | safe
Owner | postgres
Security | invoker
Access privileges |
Language | sql
Source code | select pg_catalog.date_part($1, cast($2 as timestamp without tim
e zone))
Description | extract field from date
is immutable and
Schema | pg_catalog
Name | date_part
Result data type | double precision
Argument data types | text, timestamp without time zone
Type | func
Volatility | immutable
Parallel | safe
Owner | postgres
Security | invoker
Access privileges |
Language | internal
Source code | timestamp_part
Description | extract field from timestamp
immutable too. But date_part('isodow', log_time::date) and date_part('isodow', log_time::timestamp) don't work too.
How to workaround this?
Show quoted text
21 авг. 2020 г., в 14:57, Олег Самойлов <splarv@ya.ru> написал(а):
PostgreSQL 12.4
Just create partitioned table for PostgreSQL logsCREATE TABLE pglog.pglog (
log_time timestamp(3) with time zone,
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp with time zone,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text
) PARTITION BY LIST (date_part('isodow', log_time));ERROR: functions in partition key expression must be marked IMMUTABLE
But, date_part is immutable
Schema | pg_catalog
Name | date_part
Result data type | double precision
Argument data types | text, time with time zone
Type | func
Volatility | immutable
Parallel | safe
Owner | postgres
Security | invoker
Access privileges |
Language | internal
Source code | timetz_part
Description | extract field from time with time zoneWhat is wrong here?
On Fri, 2020-08-21 at 14:57 +0300, Олег Самойлов wrote:
Just create partitioned table for PostgreSQL logs
CREATE TABLE pglog.pglog (
log_time timestamp(3) with time zone,
[...]
) PARTITION BY LIST (date_part('isodow', log_time));ERROR: functions in partition key expression must be marked IMMUTABLE
Two approaches:
1. Use "timestamp without time zone".
2. Partition in some other way, for example BY RANGE (log_time).
Your list partitions don't make a lot of sense to me.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
21 авг. 2020 г., в 16:14, Laurenz Albe <laurenz.albe@cybertec.at> написал(а):
Two approaches:1. Use "timestamp without time zone".
Yep, I redefined to timestamp without time zone. Thus loose timezone information from source, but happily there is not the daylight savings time shift in my country now.
2. Partition in some other way, for example BY RANGE (log_time).
Your list partitions don't make a lot of sense to me.
This copies default name structure of the postgresql log files.