pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIs there a work around for partition key needed for constraint
10+ messages / 5 participants
[nested] [flat]
* Is there a work around for partition key needed for constraint
@ 2018-10-24 18:56 Jorge Torralba <jorge.torralba@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jorge Torralba @ 2018-10-24 18:56 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Is there a work around for partition key needed for constraint
@ 2018-10-24 19:04 legrand legrand <legrand_legrand@hotmail.com>
parent: Jorge Torralba <jorge.torralba@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: legrand legrand @ 2018-10-24 19:04 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Is there a work around for partition key needed for constraint
@ 2018-10-24 22:11 Jorge Torralba <jorge.torralba@gmail.com>
parent: legrand legrand <legrand_legrand@hotmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Jorge Torralba @ 2018-10-24 22:11 UTC (permalink / raw)
To: legrand_legrand@hotmail.com; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Is there a work around for partition key needed for constraint
@ 2018-10-24 22:33 Keith Fiske <keith.fiske@crunchydata.com>
parent: Jorge Torralba <jorge.torralba@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Keith Fiske @ 2018-10-24 22:33 UTC (permalink / raw)
To: Jorge Torralba <jorge.torralba@gmail.com>; +Cc: legrand_legrand@hotmail.com; pgsql-admin
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.
https://github.com/pgpartman/pg_partman
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.
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Is there a work around for partition key needed for constraint
@ 2018-10-25 13:59 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Keith Fiske <keith.fiske@crunchydata.com>
0 siblings, 0 replies; 10+ messages in thread
From: Alvaro Herrera @ 2018-10-25 13:59 UTC (permalink / raw)
To: Keith Fiske <keith.fiske@crunchydata.com>; +Cc: Jorge Torralba <jorge.torralba@gmail.com>; legrand_legrand@hotmail.com; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Is there a work around for partition key needed for constraint
@ 2018-10-25 21:02 legrand legrand <legrand_legrand@hotmail.com>
parent: Jorge Torralba <jorge.torralba@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: legrand legrand @ 2018-10-25 21:02 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Is there a work around for partition key needed for constraint
@ 2018-10-25 21:16 Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
parent: legrand legrand <legrand_legrand@hotmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jehan-Guillaume (ioguix) de Rorthais @ 2018-10-25 21:16 UTC (permalink / raw)
To: legrand legrand <legrand_legrand@hotmail.com>; +Cc: pgsql-admin
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).
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Is there a work around for partition key needed for constraint
@ 2018-10-25 21:36 legrand legrand <legrand_legrand@hotmail.com>
parent: Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
0 siblings, 1 reply; 10+ messages in thread
From: legrand legrand @ 2018-10-25 21:36 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Is there a work around for partition key needed for constraint
@ 2018-10-25 22:54 Jorge Torralba <jorge.torralba@gmail.com>
parent: legrand legrand <legrand_legrand@hotmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jorge Torralba @ 2018-10-25 22:54 UTC (permalink / raw)
To: PAscal L <legrand_legrand@hotmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Is there a work around for partition key needed for constraint
@ 2018-10-26 05:33 legrand legrand <legrand_legrand@hotmail.com>
parent: Jorge Torralba <jorge.torralba@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: legrand legrand @ 2018-10-26 05:33 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2018-10-26 05:33 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-10-24 18:56 Is there a work around for partition key needed for constraint Jorge Torralba <jorge.torralba@gmail.com>
2018-10-24 19:04 ` legrand legrand <legrand_legrand@hotmail.com>
2018-10-24 22:11 ` Jorge Torralba <jorge.torralba@gmail.com>
2018-10-24 22:33 ` Keith Fiske <keith.fiske@crunchydata.com>
2018-10-25 13:59 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2018-10-25 21:02 ` legrand legrand <legrand_legrand@hotmail.com>
2018-10-25 21:16 ` Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
2018-10-25 21:36 ` legrand legrand <legrand_legrand@hotmail.com>
2018-10-25 22:54 ` Jorge Torralba <jorge.torralba@gmail.com>
2018-10-26 05:33 ` legrand legrand <legrand_legrand@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox