Re: Partitioning vs ON CONFLICT

This should be added to the open items list. I am not able to add it
myself, as I don't have "editor" privileges on open items wiki. I have
requested for those privileges.

On Thu, Mar 30, 2017 at 7:00 AM, Shinoda, Noriyoshi
<noriyoshi.shinoda@hpe.com> 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) ;
> 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 while processing 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 CONFLICT DO 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 transaction and 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
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers



-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company