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

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