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

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