BUG #14351: Upsert not working in case of partitioned tables

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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