Skip site navigation (1) Skip section navigation (2)

Re: Partitioning vs ON CONFLICT

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: "Shinoda, Noriyoshi" <noriyoshi(dot)shinoda(at)hpe(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Peter Geoghegan <pg(at)bowt(dot)ie>, Simon Riggs <simon(at)2ndquadrant(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, Thom Brown <thom(at)linux(dot)com>
Subject: Re: Partitioning vs ON CONFLICT
Date: 2017-03-30 09:54:04
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers

Thanks a lot for testing.

On 2017/03/30 10:30, Shinoda, Noriyoshi wrote:
> Hello, 
> I tried this feature using most recently snapshot. In case of added constraint PRIMARY KEY for partition table, INSERT ON CONFLICT DO NOTHING statement failed with segmentaion fault.
> If the primary key constraint was not created on the partition, this statement executed successfully.
> - Test
> postgres=> CREATE TABLE part1(c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY RANGE (c1) ;
> postgres=> CREATE TABLE part1p1 PARTITION OF part1 FOR VALUES FROM (100) TO (200) ;
> postgres=> ALTER TABLE part1p1 ADD CONSTRAINT pk_part1p1 PRIMARY KEY (c1) ;
> postgres=> INSERT INTO part1 VALUES (100, 'init') ON CONFLICT DO NOTHING ;
> server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
> !> \q

I found out why the crash occurs, but while I was trying to fix it, I
started growing doubtful about the way this is being handled currently.

Patch to fix the crash would be to pass 'true' instead of 'false' for
speculative when ExecSetupPartitionTupleRouting() calls ExecOpenIndices()
on leaf partitions.  That will initialize the information needed when
ExecInsert() wants check for conflicts using the constraint-enforcing
indexes.  If we do initialize the speculative insertion info (which will
fix the crash), ExecCheckIndexConstraints() will be called on a given leaf
partition's index to check if there is any conflict.  But since the insert
was performed on the root table, conflicts should be checked across all
the partitions, which won't be the case.  Even though the action is
NOTHING, the check for conflicts still uses only that one leaf partition's
index, which seems insufficient.

Commit 8355a011a0 enabled specifying ON CONFLICT DO NOTHING on when
inserting into a partitioned root table, but given the above, I think we
might need to reconsider it.


In response to


pgsql-hackers by date

Next:From: Venkata B NagothiDate: 2017-03-30 09:59:15
Subject: Re: [HACKERS] Bug in Physical Replication Slots (at least 9.5)?
Previous:From: Kuntal GhoshDate: 2017-03-30 09:40:12
Subject: Re: increasing the default WAL segment size

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group