Postgres Pro
Downloads
Home   >   mailing lists

Thread: is date_part immutable or not?

is date_part immutable or not?

From
Олег Самойлов
Date:
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?

Re: is date_part immutable or not?

From
Олег Самойлов
Date:
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?

> 21 авг. 2020 г., в 14:57, Олег Самойлов <splarv@ya.ru> написал(а):
>
> 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?
>
>

Re: is date_part immutable or not?

From
Laurenz Albe
Date:
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

Re: is date_part immutable or not?

From
Олег Самойлов
Date:
> 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
thedaylight 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.