Subject: Re: BUG #15437: Segfault during insert into declarative partitioned
 table with a trigger creating partition
References: <15437-3fe01ee66bd1bae1@postgresql.org>
From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Message-ID: <e8079b1f-3e41-d1dc-1575-ff3cce7d76ac@lab.ntt.co.jp>
Date: Fri, 19 Oct 2018 11:40:34 +0900
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:52.0) Gecko/20100101
 Thunderbird/52.9.1
MIME-Version: 1.0
In-Reply-To: <15437-3fe01ee66bd1bae1@postgresql.org>
Content-Type: multipart/mixed;
 boundary="------------576F11F44669F2A634E69C68"
Content-Language: en-US
To: skaurus@gmail.com, pgsql-bugs@lists.postgresql.org,
        PG Bug reporting form <noreply@postgresql.org>
Precedence: bulk

This is a multi-part message in MIME format.
--------------576F11F44669F2A634E69C68
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

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

--------------576F11F44669F2A634E69C68
Content-Type: text/plain; charset=UTF-8;
 name="check-table-not-used-when-create-partition-of-1.patch"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="check-table-not-used-when-create-partition-of-1.patch"

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2NvbW1hbmRzL3RhYmxlY21kcy5jIGIvc3JjL2Jh
Y2tlbmQvY29tbWFuZHMvdGFibGVjbWRzLmMKaW5kZXggM2UxMTJiNGVmNC4uN2IzZDA1ZDA4
YiAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvY29tbWFuZHMvdGFibGVjbWRzLmMKKysrIGIv
c3JjL2JhY2tlbmQvY29tbWFuZHMvdGFibGVjbWRzLmMKQEAgLTE5OTAsOCArMTk5MCwxNyBA
QCBNZXJnZUF0dHJpYnV0ZXMoTGlzdCAqc2NoZW1hLCBMaXN0ICpzdXBlcnMsIGNoYXIgcmVs
cGVyc2lzdGVuY2UsCiAJCWlmICghaXNfcGFydGl0aW9uKQogCQkJcmVsYXRpb24gPSBoZWFw
X29wZW5ydihwYXJlbnQsIFNoYXJlVXBkYXRlRXhjbHVzaXZlTG9jayk7CiAJCWVsc2UKKwkJ
ewogCQkJcmVsYXRpb24gPSBoZWFwX29wZW5ydihwYXJlbnQsIEFjY2Vzc0V4Y2x1c2l2ZUxv
Y2spOwogCisJCQkvKgorCQkJICogQ2hlY2sgZm9yIGFjdGl2ZSB1c2VzIG9mIHRoZSByZWxh
dGlvbiBpbiB0aGUgY3VycmVudAorCQkJICogdHJhbnNhY3Rpb24sIHN1Y2ggYXMgYmVpbmcg
dXNlZCBpbiBzb21lIG1hbm5lciBieSBhbiBlbmNsb3NpbmcKKwkJCSAqIGNvbW1hbmQsIGxp
a2UgYnkgaW5zZXJ0J3MgdHVwbGUgcm91dGluZy4KKwkJCSAqLworCQkJQ2hlY2tUYWJsZU5v
dEluVXNlKHJlbGF0aW9uLCAiQ1JFQVRFIFRBQkxFIFBBUlRJVElPTiBPRiIpOworCQl9CisK
IAkJLyoKIAkJICogV2UgZG8gbm90IGFsbG93IHBhcnRpdGlvbmVkIHRhYmxlcyBhbmQgcGFy
dGl0aW9ucyB0byBwYXJ0aWNpcGF0ZSBpbgogCQkgKiByZWd1bGFyIGluaGVyaXRhbmNlLgpk
aWZmIC0tZ2l0IGEvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9jcmVhdGVfdGFibGUub3V0
IGIvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9jcmVhdGVfdGFibGUub3V0CmluZGV4IDc0
NGI5OTkwYTYuLmFjODA2ZTliZjMgMTAwNjQ0Ci0tLSBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhw
ZWN0ZWQvY3JlYXRlX3RhYmxlLm91dAorKysgYi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVk
L2NyZWF0ZV90YWJsZS5vdXQKQEAgLTkxMCwzICs5MTAsMTYgQEAgRVJST1I6ICBjYW5ub3Qg
Y3JlYXRlIGEgdGVtcG9yYXJ5IHJlbGF0aW9uIGFzIHBhcnRpdGlvbiBvZiBwZXJtYW5lbnQg
cmVsYXRpb24gInAKIGNyZWF0ZSB0ZW1wIHRhYmxlIHRlbXBfcGFydCBwYXJ0aXRpb24gb2Yg
dGVtcF9wYXJ0ZWQgZGVmYXVsdDsgLS0gb2sKIGRyb3AgdGFibGUgcGVybV9wYXJ0ZWQgY2Fz
Y2FkZTsKIGRyb3AgdGFibGUgdGVtcF9wYXJ0ZWQgY2FzY2FkZTsKKy0tIGNoZWNrIHRoYXQg
YWRkaW5nIHBhcnRpdGlvbnMgdG8gYSB0YWJsZSB3aGlsZSBpdCBpcyBiZWluZyB1c2VkIGlz
IHByZXZlbnRlZAorY3JlYXRlIHRhYmxlIGNoZWNrX25vdF9pbl91c2UgKGEgaW50KSBwYXJ0
aXRpb24gYnkgbGlzdCAoYSk7CitjcmVhdGUgZnVuY3Rpb24gYWRkX3BhcnRfZnVuYyAoKSBy
ZXR1cm5zIHRyaWdnZXIgbGFuZ3VhZ2UgcGxwZ3NxbCBhcyAkJAorICBiZWdpbgorICAgZXhl
Y3V0ZSAnY3JlYXRlIHRhYmxlIGNoZWNrX25vdF9pbl91c2UxIHBhcnRpdGlvbiBvZiBjaGVj
a19ub3RfaW5fdXNlIGRlZmF1bHQnOworICBlbmQgJCQ7CitjcmVhdGUgdHJpZ2dlciBhZGRf
cGFydF90cmlnIGJlZm9yZSBpbnNlcnQgb24gY2hlY2tfbm90X2luX3VzZQorICBmb3IgZWFj
aCBzdGF0ZW1lbnQgZXhlY3V0ZSBwcm9jZWR1cmUgYWRkX3BhcnRfZnVuYygpOworaW5zZXJ0
IGludG8gY2hlY2tfbm90X2luX3VzZSB2YWx1ZXMgKDEpOworRVJST1I6ICBjYW5ub3QgQ1JF
QVRFIFRBQkxFIFBBUlRJVElPTiBPRiAiY2hlY2tfbm90X2luX3VzZSIgYmVjYXVzZSBpdCBp
cyBiZWluZyB1c2VkIGJ5IGFjdGl2ZSBxdWVyaWVzIGluIHRoaXMgc2Vzc2lvbgorQ09OVEVY
VDogIFNRTCBzdGF0ZW1lbnQgImNyZWF0ZSB0YWJsZSBjaGVja19ub3RfaW5fdXNlMSBwYXJ0
aXRpb24gb2YgY2hlY2tfbm90X2luX3VzZSBkZWZhdWx0IgorUEwvcGdTUUwgZnVuY3Rpb24g
YWRkX3BhcnRfZnVuYygpIGxpbmUgMyBhdCBFWEVDVVRFCitkcm9wIHRhYmxlIGNoZWNrX25v
dF9pbl91c2U7CmRpZmYgLS1naXQgYS9zcmMvdGVzdC9yZWdyZXNzL3NxbC9jcmVhdGVfdGFi
bGUuc3FsIGIvc3JjL3Rlc3QvcmVncmVzcy9zcWwvY3JlYXRlX3RhYmxlLnNxbAppbmRleCA4
MWZhNzY1OGIwLi5hMzRkZDhmMGU3IDEwMDY0NAotLS0gYS9zcmMvdGVzdC9yZWdyZXNzL3Nx
bC9jcmVhdGVfdGFibGUuc3FsCisrKyBiL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2NyZWF0ZV90
YWJsZS5zcWwKQEAgLTczMCwzICs3MzAsMTQgQEAgY3JlYXRlIHRlbXAgdGFibGUgdGVtcF9w
YXJ0IHBhcnRpdGlvbiBvZiBwZXJtX3BhcnRlZCBkZWZhdWx0OyAtLSBlcnJvcgogY3JlYXRl
IHRlbXAgdGFibGUgdGVtcF9wYXJ0IHBhcnRpdGlvbiBvZiB0ZW1wX3BhcnRlZCBkZWZhdWx0
OyAtLSBvawogZHJvcCB0YWJsZSBwZXJtX3BhcnRlZCBjYXNjYWRlOwogZHJvcCB0YWJsZSB0
ZW1wX3BhcnRlZCBjYXNjYWRlOworCistLSBjaGVjayB0aGF0IGFkZGluZyBwYXJ0aXRpb25z
IHRvIGEgdGFibGUgd2hpbGUgaXQgaXMgYmVpbmcgdXNlZCBpcyBwcmV2ZW50ZWQKK2NyZWF0
ZSB0YWJsZSBjaGVja19ub3RfaW5fdXNlIChhIGludCkgcGFydGl0aW9uIGJ5IGxpc3QgKGEp
OworY3JlYXRlIGZ1bmN0aW9uIGFkZF9wYXJ0X2Z1bmMgKCkgcmV0dXJucyB0cmlnZ2VyIGxh
bmd1YWdlIHBscGdzcWwgYXMgJCQKKyAgYmVnaW4KKyAgIGV4ZWN1dGUgJ2NyZWF0ZSB0YWJs
ZSBjaGVja19ub3RfaW5fdXNlMSBwYXJ0aXRpb24gb2YgY2hlY2tfbm90X2luX3VzZSBkZWZh
dWx0JzsKKyAgZW5kICQkOworY3JlYXRlIHRyaWdnZXIgYWRkX3BhcnRfdHJpZyBiZWZvcmUg
aW5zZXJ0IG9uIGNoZWNrX25vdF9pbl91c2UKKyAgZm9yIGVhY2ggc3RhdGVtZW50IGV4ZWN1
dGUgcHJvY2VkdXJlIGFkZF9wYXJ0X2Z1bmMoKTsKK2luc2VydCBpbnRvIGNoZWNrX25vdF9p
bl91c2UgdmFsdWVzICgxKTsKK2Ryb3AgdGFibGUgY2hlY2tfbm90X2luX3VzZTsK
--------------576F11F44669F2A634E69C68--