Обсуждение: BUG #14351: Upsert not working in case of partitioned tables
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDM1MQpMb2dnZWQgYnk6ICAg ICAgICAgIE1hcmNvIENvbG9tYm8KRW1haWwgYWRkcmVzczogICAgICBpbmcu bWFyY28uY29sb21ib0BnbWFpbC5jb20KUG9zdGdyZVNRTCB2ZXJzaW9uOiA5 LjUuNApPcGVyYXRpbmcgc3lzdGVtOiAgIENlbnRPUyA3LjIKRGVzY3JpcHRp b246ICAgICAgICAKCkhpLCBJJ20gdHJ5aW5nIHRvIHVzZSBhIHVwc2VydCBx dWVyeSBvbiBhIHBhcnRpdGlvbmVkIHRhYmxlLiBXaGlsZSBzYW1lCnF1ZXJ5 L2RhdGEgaW1wb3J0IHdvcmtzIGZyb20gYSBzdGFuZGFyZCB0YWJsZSwgdGhp cyBkb2VzIG5vdCB3b3JrIGluIGNhc2UKdGFibGUgaXMgcGFydGlvbmVkLiBJ IHNlZSBubyBtZW50aW9uIGFueXdoZXJlIHRoYXQgYSBwYXJ0aXRpb25lZCB0 YWJsZSBkb2VzCm5vdCBzdXBwb3J0IHVwc2VydCBxdWVyaWVzLiANCkVycm9y IGlzOg0KDQpFUlJPUjogIGR1cGxpY2F0ZSBrZXkgdmFsdWUgdmlvbGF0ZXMg dW5pcXVlIGNvbnN0cmFpbnQKImRoXzFfcF8yMDE2MDkwNF9wa2V5Ig0KREVU QUlMOiAgS2V5IChpZCwgdHMsIGRpZCwgcGlkKT0oMjc0MiwgMjAxNi0wOS0w MSAxNzowMDowMCwgMSwgMCkgYWxyZWFkeQpleGlzdHMuDQpDT05URVhUOiAg U1FMIHN0YXRlbWVudCAiaW5zZXJ0IGludG8gcGFydGl0aW9ucy5kaF8xX3Bf MjAxNjA5MDQgdmFsdWVzCihuZXcuKikiDQpQTC9wZ1NRTCBmdW5jdGlvbiBk aF8xX3RyZ19pbnMoKSBsaW5lIDEyIGF0IFNRTCBzdGF0ZW1lbnQKCg==
On Mon, Oct 03, 2016 at 02:21:00PM +0000, ing.marco.colombo@gmail.com wrote: > The following bug has been logged on the website: > > Bug reference: 14351 > Logged by: Marco Colombo > Email address: ing.marco.colombo@gmail.com > PostgreSQL version: 9.5.4 > Operating system: CentOS 7.2 > Description: > > Hi, I'm trying to use a upsert query on a partitioned table. > > While same > query/data import works from a standard table, this does not work in case > table is partioned. I see no mention anywhere that a partitioned table does > not support upsert queries. > Error is: > > ERROR: duplicate key value violates unique constraint > "dh_1_p_20160904_pkey" > DETAIL: Key (id, ts, did, pid)=(2742, 2016-09-01 17:00:00, 1, 0) already > exists. > CONTEXT: SQL statement "insert into partitions.dh_1_p_20160904 values > (new.*)" > PL/pgSQL function dh_1_trg_ins() line 12 at SQL statement What is the PL/pgsql function, and how are you calling it? In future, you could help get your problem addressed much more easily by sending a complete self-contained example reproducing the problem. If you can't do that, the bare minimum you need to send is the code that caused the problem and the error it produced. Best, David. -- David Fetter <david(at)fetter(dot)org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Sorry, here is: ----- creation CREATE TABLE DH_1 ( ID NUMERIC NOT NULL, TS TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, DID NUMERIC NOT NULL, PID NUMERIC NOT NULL, DURATION NUMERIC NOT NULL, AVGVALUE NUMERIC NOT NULL ); CREATE UNIQUE INDEX PK_DH_1 ON DH_1 (ID, TS, DID, PID); ALTER TABLE DH_1 ADD CONSTRAINT PK_DH_1 PRIMARY KEY USING INDEX PK_DH_1 ; ---trigger on table for partitioning CREATE TRIGGER dh_1_trg_ins BEFORE INSERT ON public.dh_1 FOR EACH ROW EXECUTE PROCEDURE public.dh_1_trg_ins(); CREATE OR REPLACE FUNCTION public.dh_1_trg_ins() RETURNS trigger AS $BODY$ begin if ( new.TS >= '2016-09-25 00:00:00'::timestamp(0) and new.TS < '2016-10-02 00:00:00'::timestamp(0) ) then insert into partitions.dh_1_p_20161002 values (new.*); elsif ( new.TS >= '2016-09-18 00:00:00'::timestamp(0) and new.TS < '2016-09-25 00:00:00'::timestamp(0) ) then insert into partitions.dh_1_p_20160925 values (new.*); elsif ( new.TS >= '2016-09-11 00:00:00'::timestamp(0) and new.TS < '2016-09-18 00:00:00'::timestamp(0) ) then insert into partitions.dh_1_p_20160918 values (new.*); elsif ( new.TS >= '2016-09-04 00:00:00'::timestamp(0) and new.TS < '2016-09-11 00:00:00'::timestamp(0) ) then insert into partitions.dh_1_p_20160911 values (new.*); elsif ( new.TS >= '2016-08-28 00:00:00'::timestamp(0) and new.TS < '2016-09-04 00:00:00'::timestamp(0) ) then insert into partitions.dh_1_p_20160904 values (new.*); elsif ( new.TS >= '2016-08-21 00:00:00'::timestamp(0) and new.TS < '2016-08-28 00:00:00'::timestamp(0) ) then insert into partitions.dh_1_p_20160828 values (new.*); elsif ( new.TS >= '2016-08-14 00:00:00'::timestamp(0) and new.TS < '2016-08-21 00:00:00'::timestamp(0) ) then insert into partitions.dh_1_p_20160821 values (new.*); elsif ( new.TS < '2016-08-14 00:00:00'::timestamp(0) ) then insert into partitions.dh_1_p_20160814 values (new.*); else raise exception 'Out of partition: value %', new.TS; end if; return null; end; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; --------------------------------- -----Query that generate error insert into dh_1 (id, ts, did, pid, duration, avgvalue) select ... select section with some duplicated data ... on conflict (id, ts, did, pid) do update set duration = excluded.duration, avgvalue = excluded.avgvalue; With this query, error 'ERROR: duplicate key value violates unique constraint ...' is raised. Same table and query but with no trigger/partitioning and same data, are working correctly. Thanks and regards 2016-10-03 22:08 GMT+02:00 David Fetter <david@fetter.org>: > On Mon, Oct 03, 2016 at 02:21:00PM +0000, ing.marco.colombo@gmail.com > wrote: > > The following bug has been logged on the website: > > > > Bug reference: 14351 > > Logged by: Marco Colombo > > Email address: ing.marco.colombo@gmail.com > > PostgreSQL version: 9.5.4 > > Operating system: CentOS 7.2 > > Description: > > > > Hi, I'm trying to use a upsert query on a partitioned table. > > > > While same > > query/data import works from a standard table, this does not work in case > > table is partioned. I see no mention anywhere that a partitioned table > does > > not support upsert queries. > > Error is: > > > > ERROR: duplicate key value violates unique constraint > > "dh_1_p_20160904_pkey" > > DETAIL: Key (id, ts, did, pid)=(2742, 2016-09-01 17:00:00, 1, 0) already > > exists. > > CONTEXT: SQL statement "insert into partitions.dh_1_p_20160904 values > > (new.*)" > > PL/pgSQL function dh_1_trg_ins() line 12 at SQL statement > > What is the PL/pgsql function, and how are you calling it? > > In future, you could help get your problem addressed much more easily > by sending a complete self-contained example reproducing the problem. > If you can't do that, the bare minimum you need to send is the code > that caused the problem and the error it produced. > > Best, > David. > -- > David Fetter <david(at)fetter(dot)org> http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter > Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate > -- Ing. Marco Colombo
Hi, are information provided sufficient to reproduce the problem? Thanks and regards 2016-10-03 22:31 GMT+02:00 Marco Colombo <ing.marco.colombo@gmail.com>: > Sorry, here is: > > ----- creation > CREATE TABLE DH_1 > ( > ID NUMERIC NOT NULL, > TS TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, > DID NUMERIC NOT NULL, > PID NUMERIC NOT NULL, > DURATION NUMERIC NOT NULL, > AVGVALUE NUMERIC NOT NULL > ); > > CREATE UNIQUE INDEX PK_DH_1 ON DH_1 > (ID, TS, DID, PID); > > ALTER TABLE DH_1 ADD > CONSTRAINT PK_DH_1 > PRIMARY KEY > USING INDEX PK_DH_1 > ; > > ---trigger on table for partitioning > > CREATE TRIGGER dh_1_trg_ins > BEFORE INSERT > ON public.dh_1 > FOR EACH ROW > EXECUTE PROCEDURE public.dh_1_trg_ins(); > > CREATE OR REPLACE FUNCTION public.dh_1_trg_ins() > RETURNS trigger AS > $BODY$ > begin > if ( new.TS >= '2016-09-25 00:00:00'::timestamp(0) and new.TS < > '2016-10-02 00:00:00'::timestamp(0) ) then > insert into partitions.dh_1_p_20161002 values (new.*); > elsif ( new.TS >= '2016-09-18 00:00:00'::timestamp(0) and new.TS < > '2016-09-25 00:00:00'::timestamp(0) ) then > insert into partitions.dh_1_p_20160925 values (new.*); > elsif ( new.TS >= '2016-09-11 00:00:00'::timestamp(0) and new.TS < > '2016-09-18 00:00:00'::timestamp(0) ) then > insert into partitions.dh_1_p_20160918 values (new.*); > elsif ( new.TS >= '2016-09-04 00:00:00'::timestamp(0) and new.TS < > '2016-09-11 00:00:00'::timestamp(0) ) then > insert into partitions.dh_1_p_20160911 values (new.*); > elsif ( new.TS >= '2016-08-28 00:00:00'::timestamp(0) and new.TS < > '2016-09-04 00:00:00'::timestamp(0) ) then > insert into partitions.dh_1_p_20160904 values (new.*); > elsif ( new.TS >= '2016-08-21 00:00:00'::timestamp(0) and new.TS < > '2016-08-28 00:00:00'::timestamp(0) ) then > insert into partitions.dh_1_p_20160828 values (new.*); > elsif ( new.TS >= '2016-08-14 00:00:00'::timestamp(0) and new.TS < > '2016-08-21 00:00:00'::timestamp(0) ) then > insert into partitions.dh_1_p_20160821 values (new.*); > elsif ( new.TS < '2016-08-14 00:00:00'::timestamp(0) ) then > insert into partitions.dh_1_p_20160814 values (new.*); > else > raise exception 'Out of partition: value %', new.TS; > end if; > return null; > end; > $BODY$ > LANGUAGE plpgsql VOLATILE > COST 100; > > --------------------------------- > > -----Query that generate error > > > insert into dh_1 (id, ts, did, pid, duration, avgvalue) > > select ... select section with some duplicated data ... > > on conflict (id, ts, did, pid) do > update set > duration = excluded.duration, > avgvalue = excluded.avgvalue; > > With this query, error 'ERROR: duplicate key value violates unique > constraint ...' is raised. > Same table and query but with no trigger/partitioning and same data, are > working correctly. > > Thanks and regards > > > 2016-10-03 22:08 GMT+02:00 David Fetter <david@fetter.org>: > >> On Mon, Oct 03, 2016 at 02:21:00PM +0000, ing.marco.colombo@gmail.com >> wrote: >> > The following bug has been logged on the website: >> > >> > Bug reference: 14351 >> > Logged by: Marco Colombo >> > Email address: ing.marco.colombo@gmail.com >> > PostgreSQL version: 9.5.4 >> > Operating system: CentOS 7.2 >> > Description: >> > >> > Hi, I'm trying to use a upsert query on a partitioned table. >> > >> > While same >> > query/data import works from a standard table, this does not work in >> case >> > table is partioned. I see no mention anywhere that a partitioned table >> does >> > not support upsert queries. >> > Error is: >> > >> > ERROR: duplicate key value violates unique constraint >> > "dh_1_p_20160904_pkey" >> > DETAIL: Key (id, ts, did, pid)=(2742, 2016-09-01 17:00:00, 1, 0) >> already >> > exists. >> > CONTEXT: SQL statement "insert into partitions.dh_1_p_20160904 values >> > (new.*)" >> > PL/pgSQL function dh_1_trg_ins() line 12 at SQL statement >> >> What is the PL/pgsql function, and how are you calling it? >> >> In future, you could help get your problem addressed much more easily >> by sending a complete self-contained example reproducing the problem. >> If you can't do that, the bare minimum you need to send is the code >> that caused the problem and the error it produced. >> >> Best, >> David. >> -- >> David Fetter <david(at)fetter(dot)org> http://fetter.org/ >> Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter >> Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com >> >> Remember to vote! >> Consider donating to Postgres: http://www.postgresql.org/about/donate >> > > > > -- > Ing. Marco Colombo > -- Ing. Marco Colombo
Marco Colombo <ing.marco.colombo@gmail.com> writes:
> Hi, are information provided sufficient to reproduce the problem?
I think you're hoping for a feature that doesn't exist, and is unlikely to
do so anytime soon. The INSERT ... ON CONFLICT clause describes what to
do in case the unique constraint on table dh_1 is violated. But it isn't,
since indeed no insert into dh_1 happens at all. Some other constraint on
some other table is being violated. The fact that that other insert is
being driven from an ON-INSERT trigger belonging to dh_1 isn't enough to
make a connection --- after all, that trigger could do anything at all.
If we had a true partitioning feature where the connection between the
table insertions was hardwired into the system (rather than emerging from
user-written triggers) and the partitions were all guaranteed to have
identical unique constraints, then it would be reasonable to expect INSERT
... ON CONFLICT to handle conflicts within the partitions. But we're
still some ways away from having that.
regards, tom lane
Thank you very much! Appreciated 2016-10-06 19:56 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>: > Marco Colombo <ing.marco.colombo@gmail.com> writes: > > Hi, are information provided sufficient to reproduce the problem? > > I think you're hoping for a feature that doesn't exist, and is unlikely to > do so anytime soon. The INSERT ... ON CONFLICT clause describes what to > do in case the unique constraint on table dh_1 is violated. But it isn't, > since indeed no insert into dh_1 happens at all. Some other constraint on > some other table is being violated. The fact that that other insert is > being driven from an ON-INSERT trigger belonging to dh_1 isn't enough to > make a connection --- after all, that trigger could do anything at all. > > If we had a true partitioning feature where the connection between the > table insertions was hardwired into the system (rather than emerging from > user-written triggers) and the partitions were all guaranteed to have > identical unique constraints, then it would be reasonable to expect INSERT > ... ON CONFLICT to handle conflicts within the partitions. But we're > still some ways away from having that. > > regards, tom lane > -- Ing. Marco Colombo