Thread: Is there a work around for partition key needed for constraint

Hi
something similar is already discussed here:

https://www.postgresql-archive.org/PostgreSQL-11-global-index-td6032681.html

Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

On 2018-Oct-24, Keith Fiske wrote:

> The real solution will have to wait on a global index in a future version.

Actually, there might be another way to implement it, namely to use the
speculative tokens on every affected local btree, then do the actual
insert in the correct index, then remove the unused speculative tokens
from all the other indexes.  Then you don't need a global tree at all.

I'm not planning to work on this, and haven't heard that anyone is.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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 ;

applied for each partition
CREATE TRIGGER check_pkey BEFORE INSERT ON tabpart1
     FOR EACH ROW EXECUTE PROCEDURE public.f_check_pkey();

CREATE TRIGGER check_pkey BEFORE INSERT ON tabpart2
     FOR EACH ROW EXECUTE PROCEDURE public.f_check_pkey();

...

postgres=# insert into tabpart values (1,1,'2018-10-24');
INSERT 0 1
postgres=# insert into tabpart values (1,1,'2018-10-24');
INSERT 0 0
postgres=# insert into tabpart values (1,1,'2018-10-25');
INSERT 0 0


If you want to be informed of Duplicated rows you can
add :
      IF count(1) >= 1 FROM tabpart WHERE account_id  = NEW.account_id 
                                   AND customer_id = NEW.customer_id 
          THEN
        RAISE NOTICE 'duplicate key value violates unique constraint "%" ON "%"', 
          TG_NAME, TG_TABLE_NAME 
          USING DETAIL = format('Key (account_id,customer_id)=(%s,%s) already
exists.',NEW.account_id, NEW.customer_id);
            RETURN NULL;
      ELSE




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

On Thu, 25 Oct 2018 14:02:46 -0700 (MST)
legrand legrand <legrand_legrand@hotmail.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).

Jehan-Guillaume (ioguix) de Rorthais wrote
> On Thu, 25 Oct 2018 14:02:46 -0700 (MST)
> 
> 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).

Yes you are right, I forgot to speak about locking, and performance
degradation 
with the number of partitions ... as discussed in the previous thread ;o)



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

Jorge Torralba wrote
> Thanks for the suggestion. But there is one slight problem :)
> 
> DETAIL:  Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.

Given exemple, works on a 11beta2 release. Triggers have to be created at 
PARTITION level, not at TABLE level (where it is not supported ;o(.

Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html