From: | "Jehan-Guillaume (ioguix) de Rorthais" <ioguix(at)free(dot)fr> |
---|---|
To: | legrand legrand <legrand_legrand(at)hotmail(dot)com> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: Is there a work around for partition key needed for constraint |
Date: | 2018-10-25 21:16:55 |
Message-ID: | 20181025231655.615d80c2@firost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Thu, 25 Oct 2018 14:02:46 -0700 (MST)
legrand legrand <legrand_legrand(at)hotmail(dot)com> wrote:
> Maybe there is a solution equivalent to ON CONFLICT DO NOTHING,
> with some BEFORE INSERT TRIGGERS returning NULL when row already exists.
>
> Something like:
>
> create table tabpart (account_id integer not null, customer_id integer not
> null, date_added date ) partition by list (date_added);
> create table tabpart1 partition of tabpart for values in ('2018-10-24');
> create table tabpart2 partition of tabpart for values in ('2018-10-25');
> ...
> create index tabpart_index_id on tabpart(account_id,customer_id);
>
> CREATE OR REPLACE FUNCTION f_check_pkey() RETURNS TRIGGER AS $$
> BEGIN
> IF count(1) >= 1 FROM tabpart WHERE account_id = NEW.account_id
> AND customer_id = NEW.customer_id
> THEN
> RETURN NULL;
> ELSE
> RETURN NEW;
> END IF;
> END;
> $$ LANGUAGE plpgsql ;
It seems to me you lack some locking to avoid duplicates. cf. my blog post we
discussed few weeks ago.
(btw, sorry, I somehow missed your answers on previous thread b/c holidays).
From | Date | Subject | |
---|---|---|---|
Next Message | legrand legrand | 2018-10-25 21:36:02 | Re: Is there a work around for partition key needed for constraint |
Previous Message | legrand legrand | 2018-10-25 21:02:46 | Re: Is there a work around for partition key needed for constraint |