Re: Partitioning vs ON CONFLICT - Mailing list pgsql-hackers

Hello, 

I tried this feature using most recently snapshot. In case of added constraint PRIMARY KEY for partition table, INSERT
ONCONFLICT 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) ;
CREATE TABLE
postgres=> CREATE TABLE part1p1 PARTITION OF part1 FOR VALUES FROM (100) TO (200) ;
CREATE TABLE
postgres=> ALTER TABLE part1p1 ADD CONSTRAINT pk_part1p1 PRIMARY KEY (c1) ;
ALTER TABLE
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
whileprocessing the request.
 
The connection to the server was lost. Attempting reset: Failed.
!> \q

- Part of data/log/postgresql.log file 
2017-03-30 10:20:09.161 JST [12323] LOG:  server process (PID 12337) was terminated by signal 11: Segmentation fault
2017-03-30 10:20:09.161 JST [12323] DETAIL:  Failed process was running: INSERT INTO part1 VALUES (100, 'init') ON
CONFLICTDO NOTHING ;
 
2017-03-30 10:20:09.161 JST [12323] LOG:  terminating any other active server processes
2017-03-30 10:20:09.163 JST [12345] FATAL:  the database system is in recovery mode
2017-03-30 10:20:09.164 JST [12329] WARNING:  terminating connection because of crash of another server process
2017-03-30 10:20:09.164 JST [12329] DETAIL:  The postmaster has commanded this server process to roll back the current
transactionand exit, because another server process exited abnormally and possibly corrupted shared memory.
 

- Environment
OS: Red Hat Enterprise Linux 7 Update 1 (x86-64) 
Snapshot: 2017-03-29 20:30:05 with default configure.

Best Regards,

--
Noriyoshi Shinoda

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Amit Langote
Sent: Tuesday, March 28, 2017 9:56 AM
To: Robert Haas <robertmhaas@gmail.com>
Cc: Peter Geoghegan <pg@bowt.ie>; Simon Riggs <simon@2ndquadrant.com>; PostgreSQL Hackers
<pgsql-hackers@postgresql.org>;Thom Brown <thom@linux.com>
 
Subject: Re: [HACKERS] Partitioning vs ON CONFLICT

On 2017/03/27 23:40, Robert Haas wrote:
> On Thu, Mar 9, 2017 at 7:20 PM, Amit Langote 
> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>> On 2017/03/10 9:10, Amit Langote wrote:
>>> On 2017/03/09 23:25, Robert Haas wrote:
>>>> On Fri, Feb 17, 2017 at 1:47 AM, Amit Langote wrote:
>>>>> I updated the patch.  Now it's reduced to simply removing the 
>>>>> check in
>>>>> transformInsertStmt() that prevented using *any* ON CONFLICT on 
>>>>> partitioned tables at all.
>>>>
>>>> This patch no longer applies.
>>>
>>> Rebased patch is attached.
>>
>> Oops, really attached this time,
> 
> Committed with a bit of wordsmithing of the documentation.

Thanks.

Regards,
Amit