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

Is there a work around for partition key needed for constraint

From
Jorge Torralba
Date:
Working with postgres 11 now and trying to use declarative partition for its benefits and ability to update data and move to the proper partition. However, I am running into an issue with the limitation of having to include the partition key as part of the unique constraint.

For example,

I have a table, call it xxx with

account_id, customer_id, date_added and so on ...._

partitioned by range on the date_added.

then a bunch of partition tables with a range.

I want to be able to use insert into xxx on conflict ( account_id, customer_id ) do nothing.

However, since the requirements for a unique index on a partitioned table  must include the partition key of date_added, I am kind of stuck. 

Is there anyway to create a unique constraint on a partitioned table without using the column it is partitioned  by ?

Thanks

--
Thanks,

Jorge Torralba
----------------------------

Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.

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

From
Jorge Torralba
Date:
Wow, thats a convoluted solution and I don't know if it will allow the use of upsert. I hope this solution is in the works :(

On Wed, Oct 24, 2018 at 12:05 PM legrand legrand <legrand_legrand@hotmail.com> wrote:
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



--
Thanks,

Jorge Torralba
----------------------------

Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.

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

From
Keith Fiske
Date:


On Wed, Oct 24, 2018, 6:11 PM Jorge Torralba <jorge.torralba@gmail.com> wrote:
Wow, thats a convoluted solution and I don't know if it will allow the use of upsert. I hope this solution is in the works :(



I have a workaround for this in pg_partman. It uses a template table to apply the unique indexes to the child tables. Note that it doesn't enforce the unique constraint across all the child tables, only each child table. Since you're doing ON CONFLICT DO NOTHING, this may work ok for your situation tho. I don't think it would work if you needed it to move the rows between partitions on conflict tho, unless the constraint was part of the partition key. 

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


Keith



On Wed, Oct 24, 2018 at 12:05 PM legrand legrand <legrand_legrand@hotmail.com> wrote:
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



--
Thanks,

Jorge Torralba
----------------------------

Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.

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

From
Alvaro Herrera
Date:
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


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

From
legrand legrand
Date:
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


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

From
"Jehan-Guillaume (ioguix) de Rorthais"
Date:
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).


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

From
legrand legrand
Date:
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


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

From
Jorge Torralba
Date:
Thanks for the suggestion. But there is one slight problem :)

DETAIL:  Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.


On Thu, Oct 25, 2018 at 2:36 PM legrand legrand <legrand_legrand@hotmail.com> wrote:
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



--
Thanks,

Jorge Torralba
----------------------------

Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.

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

From
legrand legrand
Date:
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