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(dot)shinoda(at)hpe(dot)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(at)postgresql(dot)org [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Amit Langote
> Sent: Tuesday, March 28, 2017 9:56 AM
> To: 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: [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(at)lab(dot)ntt(dot)co(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

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