Auto-partitioning trigger doesn't work as expected-CodePudding

I'm trying to implement auto-partitioning of a table

CREATE TABLE incoming_ais_messages (
    id uuid NOT NULL,
    "source" int4 NOT NULL,
    ais_channel varchar(8) NOT NULL,
    is_read bool NOT NULL,
    "time_stamp" timestamptz NOT null,
    address_type varchar(32) NOT NULL,
    "text" varchar NOT NULL,
    CONSTRAINT incoming_ais_messages_pkey PRIMARY KEY (id,time_stamp)
) partition by range ("time_stamp");

For that I use a function:

create or replace function create_partition() returns trigger as $auto_partition$
begin
raise notice 'create_partion called';
execute 'create table if not exists incoming_ais_messages_partition_' || to_char(now()::date, 'yyyy_mm_dd') || ' partition of incoming_ais_messages 
for values from (''' || to_char(now()::date, 'yyyy-mm-dd') || ''') to (''' || to_char((now()   interval '1 day')::date, 'yyyy-mm-dd') || ''');';

return new;
end;
$auto_partition$  language plpgsql;

And a trigger that should call it before any inserts:

create trigger auto_partition
before insert on incoming_ais_messages
for each row
execute procedure create_partition();

However when I insert something like:

INSERT INTO incoming_ais_messages (id, "source", ais_channel, is_read, "time_stamp", address_type, "text")
VALUES('123e4567-e89b-12d3-a456-426614174000'::uuid, 0, 'A', false, now(), 'DIRECT', 'text');

I get ther error:

SQL Error [23514]: ERROR: no partition of relation "incoming_ais_messages" found for row
  Detail: Partition key of the failing row contains (time_stamp) = (2022-07-21 18:01:41.787604 03).

After that I created the partition manually:

create table if not exists incoming_ais_messages_partition_1970_01_01 partition of incoming_ais_messages 
for values from (now()::date) to ((now()   interval '1 day')::date);

executed the same insert statement and got the error:

SQL Error [55006]: ERROR: cannot CREATE TABLE .. PARTITION OF "incoming_ais_messages" because it is being used by active queries in this session
  Where: SQL statement "create table if not exists incoming_ais_messages_partition_2022_07_21 partition of incoming_ais_messages 
for values from ('2022-07-21') to ('2022-07-22');"
PL/pgSQL function create_partition() line 4 at EXECUTE

Would be great to know what is wrong here. My solution is based on the approach described here https://evilmartians.com/chronicles/a-slice-of-life-table-partitioning-in-postgresql-databases (Section: Bonus: how to create partitions)

CodePudding user response：

PostgreSQL wants to know which partition the new rows will go into before it calls BEFORE ROW triggers, so the error is thrown before the CREATE gets a chance to run. (Note that the blog example is using a trigger on one table to create partition for a different table).

Doing what you want is possible (timescaledb extension does it, and you could research how if you want), but do yourself a favor and just pre-create a lot of partitions, and add a note to your calendar to add more in the future (as well as dropping old ones). Or write a cron job to do it.