From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: skaurus@gmail.com, pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>
Subject: Re: BUG #15437: Segfault during insert into declarative partitioned table with a trigger creating partition
Date: Fri, 19 Oct 2018 11:40:34 +0900
Message-ID: <e8079b1f-3e41-d1dc-1575-ff3cce7d76ac@lab.ntt.co.jp> (raw)
In-Reply-To: <15437-3fe01ee66bd1bae1@postgresql.org>
References: <15437-3fe01ee66bd1bae1@postgresql.org>
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@gmail.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
Attachments:check-table-not-used-when-create-partition-of-1.patch (text/plain, 2.9K) downloaddiff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 3e112b4ef4..7b3d05d08b 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -1990,8 +1990,17 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
if (!is_partition)
relation = heap_openrv(parent, ShareUpdateExclusiveLock);
else
+ {
relation = heap_openrv(parent, AccessExclusiveLock);
+ /*+ * Check for active uses of the relation in the current+ * transaction, such as being used in some manner by an enclosing+ * command, like by insert's tuple routing.+ */+ CheckTableNotInUse(relation, "CREATE TABLE PARTITION OF");+ }+
/*
* We do not allow partitioned tables and partitions to participate in
* regular inheritance.
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.outindex 744b9990a6..ac806e9bf3 100644--- a/src/test/regress/expected/create_table.out+++ b/src/test/regress/expected/create_table.out@@ -910,3 +910,16 @@ ERROR: cannot create a temporary relation as partition of permanent relation "p
create temp table temp_part partition of temp_parted default; -- ok
drop table perm_parted cascade;
drop table temp_parted cascade;
+-- check that adding partitions to a table while it is being used is prevented+create table check_not_in_use (a int) partition by list (a);+create function add_part_func () returns trigger language plpgsql as $$+ begin+ execute 'create table check_not_in_use1 partition of check_not_in_use default';+ end $$;+create trigger add_part_trig before insert on check_not_in_use+ for each statement execute procedure add_part_func();+insert into check_not_in_use values (1);+ERROR: cannot CREATE TABLE PARTITION OF "check_not_in_use" because it is being used by active queries in this session+CONTEXT: SQL statement "create table check_not_in_use1 partition of check_not_in_use default"+PL/pgSQL function add_part_func() line 3 at EXECUTE+drop table check_not_in_use;diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sqlindex 81fa7658b0..a34dd8f0e7 100644--- a/src/test/regress/sql/create_table.sql+++ b/src/test/regress/sql/create_table.sql@@ -730,3 +730,14 @@ create temp table temp_part partition of perm_parted default; -- error
create temp table temp_part partition of temp_parted default; -- ok
drop table perm_parted cascade;
drop table temp_parted cascade;
++-- check that adding partitions to a table while it is being used is prevented+create table check_not_in_use (a int) partition by list (a);+create function add_part_func () returns trigger language plpgsql as $$+ begin+ execute 'create table check_not_in_use1 partition of check_not_in_use default';+ end $$;+create trigger add_part_trig before insert on check_not_in_use+ for each statement execute procedure add_part_func();+insert into check_not_in_use values (1);+drop table check_not_in_use;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: Langote_Amit_f8@lab.ntt.co.jp, noreply@postgresql.org
Subject: Re: BUG #15437: Segfault during insert into declarative partitioned table with a trigger creating partition
In-Reply-To: <e8079b1f-3e41-d1dc-1575-ff3cce7d76ac@lab.ntt.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox