Re: BUG #15437: Segfault during insert into declarative partitioned table with a trigger creating partition

On 2018/10/18 20:57, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 15437
> Logged by: Dmitry Shalashov
> Email address: skaurus(at)gmail(dot)com
> PostgreSQL version: 10.5
> Operating system: Debian 9
> Description:
>
> I tried to use declarative partitioning and, to avoid creating partitions by
> hand, to make them in ON BEFORE STATEMENT trigger. Trigger executes
> successfully (proved that with RAISE NOTICE), but server crashes.
>
> Error in the log could look like that:
>
> [24329207.147193] postgres[23599]: segfault at 15948 ip 00005652ff2e586e sp
> 00007fffd9ee5a50 error 4 in postgres[5652ff17d000+6de000]
> 2018-10-18 14:52:13 MSK [4312]: [17-1] user=,db= LOG: server process (PID
> 4636) was terminated by signal 11: Segmentation fault
> 2018-10-18 14:52:13 MSK [4312]: [18-1] user=,db= DETAIL: Failed process was
> running: INSERT INTO test (value) VALUES (1);
> 2018-10-18 14:52:13 MSK [4312]: [19-1] user=,db= LOG: terminating any other
> active server processes
>
> Test case follows:
>
> CREATE TABLE test (id serial NOT NULL, value integer NOT NULL, ctime
> timestamp NOT NULL DEFAULT now()) PARTITION BY RANGE (ctime);
>
> CREATE OR REPLACE FUNCTION week_table_suffix_from_ts(ts timestamp with time
> zone) RETURNS text
> LANGUAGE plpgsql
> AS $$
> DECLARE
> table_suffix text := replace(substring((date_trunc('week', ts))::text
> from 1 for 10),'-','');
> BEGIN
> RETURN table_suffix;
> END;
> $$;
>
> CREATE OR REPLACE FUNCTION test_trigger_func() RETURNS trigger
> LANGUAGE plpgsql
> AS $$
> DECLARE
> table_prefix text := 'test_';
> table_suffix text := week_table_suffix_from_ts(now());
> table_name text := 'creatives_' || table_suffix;
> table_exists boolean;
> BEGIN
> EXECUTE format(
> 'SELECT EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = $1 AND
> tablename = $2)'
> ) INTO table_exists USING 'public', table_name;
>
> IF NOT table_exists THEN
> EXECUTE format(
> 'CREATE TABLE IF NOT EXISTS %I PARTITION OF test FOR VALUES FROM
> (%L) TO (%L)'::text,
> table_name,
> date_trunc('week', now()),
> date_trunc('week', now() + interval '1 week')
> );
> END IF;
>
> RETURN NULL;
> END;
> $$;
>
> CREATE TRIGGER test_trigger BEFORE INSERT ON test FOR EACH STATEMENT EXECUTE
> PROCEDURE test_trigger_func();
>
> INSERT INTO test (value) VALUES (1);

Thanks for the report.

The problem here is with the server allowing to create a partition of the
table being inserted into, inside the table's BEFORE INSERT trigger.
Generally speaking, the command that's run inside the trigger shouldn't
have been allowed to proceed if it might change the table's properties
that the execution of the ongoing command is depending upon.

In this case, initialized state of the ongoing command (insert) says that
there is no partition, but the command executed inside the trigger
violated that assumption by adding a partition. The bug here is that the
command executed by the trigger shouldn't have been allowed to complete.

I propose the attached to fix that.

Thanks,
Amit