Re: [HACKERS] Partitioning/inherited tables vs FKs
On Thu, May 6, 2010 at 10:38 AM, Tom Lane t...@sss.pgh.pa.us wrote: * the index grows as the size of the total data set, it's not limited by partition size * can't cheaply drop one partition any more, you have to vacuum the (big) index first So I wholeheartedly agree with the general sentiment that if you need global indexes then partitioning just isn't really the right tool for you. But it occurs to me that we could defer the vacuum safely. I'm assuming a index heap-tid pointer for a global index would include a relid or some other identifier to specify which partition the tuple is in. If you drop that partition those can all just be left as dangling pointers as long as we don't reuse that id. So all we would need is some way to leave a catalog entry reserving that id. The data files can be truncated and deleted normally and whenever vacuum does run against the index it can clean up the catalog entries for the deleted partitions. But I would rather work on having unique and foreign key constraints that work on keys which include the partition key than work on global indexes. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioning/inherited tables vs FKs
On May 6, 2010, at 4:31 AM, Florian Pflug wrote: The use case for this was there were different news items, and there were another table for summaries, that could point to any of the news items table. Another use case could be a large partitioned table with an FK to the main table where the referring table might only contain very few interesting data. Yeah, this is a long-standing issue with inheritance. Table inheritance in postgres isn't much more than an implicit UNION done on selects plus some logic in ALTER TABLE to keep propagate structural changes. Indices and constraints basically always behave as if ONLY had been specified. I'm not even sure if the ids are globally unique in your example - it might be that each child's id serial column gets its very own sequence. One possible workaround is no create a table, say referred_ids, that contains all the ids from parent and all of its children, kept up-to-date via triggers, and point the FK constraint to that table. That also allows for a global unique constraint on the ids by definition a suitable unique or primary key constraint on referred_ids. What lies at the heart of this problem is the lack of multi-table indices and hence multi-table unique constraints in postgres. AFAIK with those in place the rest amounts to the removal of ONLY from the constraint check queries plus some code to propagate constraint triggers to child tables. FWIW, we use inheritance for something other than partitioning, and I created a trigger that provides a crude form of a foreign key constraint, as well as one that provides a crude global unique constraint on the PK. Both probably have holes and race conditions, but I figure they're better than just hoping no one screws something up. BTW, my intention is to release all the generic tools we've developed to pgFoundry, it just hasn't happened yet. If enough people find this stuff interesting I can try and up the priority on getting that done. (And if you're *really* wanting this stuff you could pay 2nd Quadrant or CMD to get it for you.) test...@workbook.local=# \df+ payment_instruments.tg_payment_instruments_unique List of functions -[ RECORD 1 ]---+ Schema | payment_instruments Name| tg_payment_instruments_unique Result data type| trigger Argument data types | Volatility | volatile Owner | cnuadmin Language| plpgsql Source code | : : DECLARE : name CONSTANT text := 'payment_instruments.tg_payment_instruments_unique'; : c_full_table_name CONSTANT text := TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME; : BEGIN : PERFORM tools.assert( TG_WHEN = 'BEFORE', TG_NAME || ' ON ' || c_full_table_name ||' must be an BEFORE trigger' ); : PERFORM tools.assert( TG_LEVEL = 'ROW', TG_NAME || ' ON ' || c_full_table_name ||' must be a row-level trigger' ); : : -- Deleting would break RI, so don't allow it. Granted, this should probably be a separate trigger, but... : PERFORM tools.assert( 'payment_instruments__payment_instruments__inherit__no_delete' : , TG_OP != 'DELETE' : , 'DELETEs are not allowed on ' || c_full_table_name || ' (they would break inheritance RI)' : ); : : RAISE DEBUG '%: : TG_OP = % : TG_TABLE_NAME = % : NEW.payment_instrument_id = %' : , name : , TG_OP : , TG_TABLE_NAME : , NEW.payment_instrument_id : ; : : -- Changing the PK would break RI, so we shouldn't allow it. Granted, this should probably be a separate trigger, but... : IF TG_OP = 'UPDATE' THEN : PERFORM tools.assert( 'payment_instruments__payment_instruments__inherit__pk_no_change' : , NEW.payment_instrument_id IS NOT DISTINCT FROM OLD.payment_instrument_id : , 'Changing payment_instrument_id on ' || c_full_table_name || ' is not allowed (it would break inheritance RI)' : ); : ELSE : -- Only check for dupes on insert, otherwise we'll see our own ID :
Re: [HACKERS] Partitioning/inherited tables vs FKs
The referential integrity triggers contain some extra magic that isn't easily simulatable in userland, and that is necessary to make the foreign key constraints airtight. We've discussed this previously but I don't remember which thread it was or the details of when things blow up. I think it's something like this: the parent has a tuple that is not referenced by any child. Transaction 1 begins, deletes the parent tuple (checking that it has no children), and pauses. Transaction 2 begins, adds a child tuple that references the parent tuple (checking that the parent exists, which it does), and commits. Transaction 1 commits. Will SELECT ... FOR SHARE not help? Regargs, Dmitry -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioning/inherited tables vs FKs
On Tue, May 11, 2010 at 2:16 AM, Dmitry Fefelov fo...@ac-sw.com wrote: The referential integrity triggers contain some extra magic that isn't easily simulatable in userland, and that is necessary to make the foreign key constraints airtight. We've discussed this previously but I don't remember which thread it was or the details of when things blow up. I think it's something like this: the parent has a tuple that is not referenced by any child. Transaction 1 begins, deletes the parent tuple (checking that it has no children), and pauses. Transaction 2 begins, adds a child tuple that references the parent tuple (checking that the parent exists, which it does), and commits. Transaction 1 commits. Will SELECT ... FOR SHARE not help? Try it, with the example above. I think you'll find that it doesn't. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioning/inherited tables vs FKs
On 2010-05-11 14:29 +0200, Robert Haas wrote: On Tue, May 11, 2010 at 2:16 AM, Dmitry Fefelov fo...@ac-sw.com wrote: The referential integrity triggers contain some extra magic that isn't easily simulatable in userland, and that is necessary to make the foreign key constraints airtight. We've discussed this previously but I don't remember which thread it was or the details of when things blow up. I think it's something like this: the parent has a tuple that is not referenced by any child. Transaction 1 begins, deletes the parent tuple (checking that it has no children), and pauses. Transaction 2 begins, adds a child tuple that references the parent tuple (checking that the parent exists, which it does), and commits. Transaction 1 commits. Will SELECT ... FOR SHARE not help? Try it, with the example above. I think you'll find that it doesn't. TXA = delete from foo; DELETE 1 TXB = select a from foo for share; -- waits What am I missing? Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioning/inherited tables vs FKs
2010/5/11 Marko Tiikkaja marko.tiikk...@cs.helsinki.fi: On 2010-05-11 14:29 +0200, Robert Haas wrote: On Tue, May 11, 2010 at 2:16 AM, Dmitry Fefelov fo...@ac-sw.com wrote: The referential integrity triggers contain some extra magic that isn't easily simulatable in userland, and that is necessary to make the foreign key constraints airtight. We've discussed this previously but I don't remember which thread it was or the details of when things blow up. I think it's something like this: the parent has a tuple that is not referenced by any child. Transaction 1 begins, deletes the parent tuple (checking that it has no children), and pauses. Transaction 2 begins, adds a child tuple that references the parent tuple (checking that the parent exists, which it does), and commits. Transaction 1 commits. Will SELECT ... FOR SHARE not help? Try it, with the example above. I think you'll find that it doesn't. TXA = delete from foo; DELETE 1 TXB = select a from foo for share; -- waits What am I missing? Slightly verbose example of what can go wrong: CREATE TABLE a (i int PRIMARY KEY); INSERT INTO a VALUES (1); CREATE TABLE b (a_id int); Start with T1: T1 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN T1 SELECT i FROM a WHERE i = 1 FOR SHARE; -- Does a with i = 1 exist? i --- 1 (1 Zeile) T1 INSERT INTO b VALUES (1); -- Great, it existed, insert row pointing to it in b. INSERT 0 1 Switch to T2: T2 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- Evil transaction T2 is intervening! BEGIN T2 SELECT i FROM a WHERE i = 1 FOR SHARE; -- Lock a with i = 1 FOR SHARE. i --- 1 (1 Zeile) T2 SELECT a_id FROM b WHERE a_id = 1; -- Check whether it's got anything pointing to it. a_id -- (0 Zeilen) T2 DELETE FROM a WHERE i = 1; -- Nope, so delete a with i = 1 (this blocks, because T1 is still holding the lock). Switch to T1: 1 COMMIT; -- Commit the insertion of a row pointing to a with i = 1 (this releases all locks that T1 is holding). COMMIT T2 continues: DELETE 1 T2 COMMIT; -- Commit the deletion of a with i = 1. COMMIT T2 SELECT * FROM b EXCEPT SELECT * FROM a; -- Check for inconsistencies. a_id -- 1 (1 Zeile) Woops. Nicolas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioning/inherited tables vs FKs
This is getting way off topic, but: On 5/11/10 3:55 PM +0300, Nicolas Barbier wrote: T2 SELECT i FROM a WHERE i = 1 FOR SHARE; -- Lock a with i = 1 FOR SHARE. i --- 1 (1 Zeile) T2 SELECT a_id FROM b WHERE a_id = 1; -- Check whether it's got anything pointing to it. a_id -- (0 Zeilen) T2 DELETE FROM a WHERE i = 1; -- Nope, so delete a with i = 1 (this blocks, because T1 is still holding the lock). Obviously you wouldn't delete anything with a SHARE lock. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioning/inherited tables vs FKs
2010/5/11 Marko Tiikkaja marko.tiikk...@cs.helsinki.fi: This is getting way off topic, but: On 5/11/10 3:55 PM +0300, Nicolas Barbier wrote: T2 SELECT i FROM a WHERE i = 1 FOR SHARE; -- Lock a with i = 1 FOR SHARE. i --- 1 (1 Zeile) T2 SELECT a_id FROM b WHERE a_id = 1; -- Check whether it's got anything pointing to it. a_id -- (0 Zeilen) T2 DELETE FROM a WHERE i = 1; -- Nope, so delete a with i = 1 (this blocks, because T1 is still holding the lock). Obviously you wouldn't delete anything with a SHARE lock. So where would you put a SELECT ... FOR SHARE to fix the problem? (Per Will SELECT ... FOR SHARE not help?.) I agree that my second FOR SHARE doesn't really make a lot of sense, but that doesn't disprove the fact that the first FOR SHARE fails to ensure consistency. Nicolas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioning/inherited tables vs FKs
On 5/11/10 4:07 PM +0300, Nicolas Barbier wrote: 2010/5/11 Marko Tiikkajamarko.tiikk...@cs.helsinki.fi: This is getting way off topic, but: On 5/11/10 3:55 PM +0300, Nicolas Barbier wrote: T2SELECT i FROM a WHERE i = 1 FOR SHARE; -- Lock a with i = 1 FOR SHARE. i --- 1 (1 Zeile) T2SELECT a_id FROM b WHERE a_id = 1; -- Check whether it's got anything pointing to it. a_id -- (0 Zeilen) T2DELETE FROM a WHERE i = 1; -- Nope, so delete a with i = 1 (this blocks, because T1 is still holding the lock). Obviously you wouldn't delete anything with a SHARE lock. So where would you put a SELECT ... FOR SHARE to fix the problem? (Per Will SELECT ... FOR SHARE not help?.) I agree that my second FOR SHARE doesn't really make a lot of sense, but that doesn't disprove the fact that the first FOR SHARE fails to ensure consistency. I took the SELECT ... FOR SHARE suggestion in a more general way, suggesting the use of row-level locks. T2 should be holding an exclusive row-level lock (SELECT ... FOR UPDATE) when checking for references. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioning/inherited tables vs FKs
On 5/11/10 4:11 PM +0300, I wrote: I took the SELECT ... FOR SHARE suggestion in a more general way, suggesting the use of row-level locks. T2 should be holding an exclusive row-level lock (SELECT ... FOR UPDATE) when checking for references. Hmm. Right, that transaction wouldn't see the rows in a serializable transaction so this doesn't solve the problem. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioning/inherited tables vs FKs
Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes: On 5/11/10 4:11 PM +0300, I wrote: I took the SELECT ... FOR SHARE suggestion in a more general way, suggesting the use of row-level locks. T2 should be holding an exclusive row-level lock (SELECT ... FOR UPDATE) when checking for references. Hmm. Right, that transaction wouldn't see the rows in a serializable transaction so this doesn't solve the problem. Yeah. The hidden magic in the built-in FK code is not locking (it does actually use SELECT FOR SHARE to lock rows). Rather, it's about doing tuple liveness checks using snapshots that aren't available at the SQL level, particularly in serializable transactions. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioning/inherited tables vs FKs
Nicolas Barbier nicolas.barb...@gmail.com wrote: Switch to T1: 1 COMMIT; -- Commit the insertion... COMMIT T2 continues: DELETE 1 T2 COMMIT; -- Commit the deletion of a with i = 1. COMMIT T2 SELECT * FROM b EXCEPT SELECT * FROM a; a_id -- 1 (1 Zeile) Woops. This is exactly the sort of issue for which true serializable behavior will provide a solution. I will be offering a patch to implement that for 9.1 once 9.0 settles down. FWIW when you commit T1, the patched code rolls back T2 with this message: T2 DELETE FROM a WHERE i = 1; ERROR: could not serialize access due to read/write dependencies among transactions HINT: The transaction might succeed if retried. Thanks for the example; I will it to the others. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioning/inherited tables vs FKs
On May 6, 2010, at 10:52 , Boszormenyi Zoltan wrote:
=# create table parent (id serial primary key, t text);
...
=# create table child () inherits (parent);
...
=# create table refer (id serial primary key, parent_id integer
...
=# insert into child (t) values ('a') returning id;
...
=# select * from parent;
id | t
+---
1 | a
(1 sor)
=# insert into refer (parent_id) values (1);
ERROR: insert or update on table refer violates foreign key
constraint refer_parent_id_fkey
DETAIL: Key (parent_id)=(1) is not present in table parent.
The use case for this was there were different news items,
and there were another table for summaries, that could point
to any of the news items table. Another use case could be
a large partitioned table with an FK to the main table where
the referring table might only contain very few interesting data.
Yeah, this is a long-standing issue with inheritance. Table inheritance in
postgres isn't much more than an implicit UNION done on selects plus some logic
in ALTER TABLE to keep propagate structural changes. Indices and constraints
basically always behave as if ONLY had been specified. I'm not even sure if the
ids are globally unique in your example - it might be that each child's id
serial column gets its very own sequence.
One possible workaround is no create a table, say referred_ids, that contains
all the ids from parent and all of its children, kept up-to-date via triggers,
and point the FK constraint to that table. That also allows for a global unique
constraint on the ids by definition a suitable unique or primary key constraint
on referred_ids.
What lies at the heart of this problem is the lack of multi-table indices and
hence multi-table unique constraints in postgres. AFAIK with those in place the
rest amounts to the removal of ONLY from the constraint check queries plus some
code to propagate constraint triggers to child tables.
best regards,
Florian Pflug
smime.p7s
Description: S/MIME cryptographic signature
Re: [HACKERS] Partitioning/inherited tables vs FKs
2010/5/6 Boszormenyi Zoltan z...@cybertec.at: =# insert into refer (parent_id) values (1); ERROR: insert or update on table refer violates foreign key constraint refer_parent_id_fkey DETAIL: Key (parent_id)=(1) is not present in table parent. The use case for this was there were different news items, and there were another table for summaries, that could point to any of the news items table. Another use case could be a large partitioned table with an FK to the main table where the referring table might only contain very few interesting data. No matter what are the semantics, the parent table in the inheritance chain cannot be used as and endpoint for FKs. Is it a bug, or intentional? i would call it a bug, but this is a known issue The only solution currently is that the referring table has to be partitioned the same way as the referred table in the FK, and its parent table has to be queried. no, you can install a trigger on the child table that verifies the existence of the id on your partitioned parent table, the SELECT you'll use inside that trigger will look at the entire set of tables (as long as you don't use FROM ONLY) also could be useful to put an index (even a PK) on every child to ensure uniqueness and make the SELECT more efficient, and of course a check constraint in every child emulating a partition key -- Jaime Casanova www.2ndQuadrant.com Soporte y capacitación de PostgreSQL -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioning/inherited tables vs FKs
On Thu, May 6, 2010 at 6:37 AM, Jaime Casanova ja...@2ndquadrant.com wrote: i would call it a bug, but this is a known issue The only solution currently is that the referring table has to be partitioned the same way as the referred table in the FK, and its parent table has to be queried. no, you can install a trigger on the child table that verifies the existence of the id on your partitioned parent table, the SELECT you'll use inside that trigger will look at the entire set of tables (as long as you don't use FROM ONLY) also could be useful to put an index (even a PK) on every child to ensure uniqueness and make the SELECT more efficient, and of course a check constraint in every child emulating a partition key The referential integrity triggers contain some extra magic that isn't easily simulatable in userland, and that is necessary to make the foreign key constraints airtight. We've discussed this previously but I don't remember which thread it was or the details of when things blow up. I think it's something like this: the parent has a tuple that is not referenced by any child. Transaction 1 begins, deletes the parent tuple (checking that it has no children), and pauses. Transaction 2 begins, adds a child tuple that references the parent tuple (checking that the parent exists, which it does), and commits. Transaction 1 commits. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioning/inherited tables vs FKs
Florian Pflug f...@phlo.org writes: What lies at the heart of this problem is the lack of multi-table indices and hence multi-table unique constraints in postgres. AFAIK with those in place the rest amounts to the removal of ONLY from the constraint check queries plus some code to propagate constraint triggers to child tables. Well, the lack of multi-table indexes certainly is the heart of the problem, but I'm not sure that inventing such a thing is the solution. Quite aside from the implementation difficulties involved in it, doing things that way would destroy some of the major reasons to partition tables at all: * the index grows as the size of the total data set, it's not limited by partition size * can't cheaply drop one partition any more, you have to vacuum the (big) index first * probably some other things I'm not thinking of at the moment. I think the real solution is to upgrade the partitioning infrastructure so that we can understand that columns are unique across the whole partitioned table, when the partitioning is done on that column and each partition has a unique index. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioning/inherited tables vs FKs
On May 6, 2010, at 16:38 , Tom Lane wrote: Florian Pflug f...@phlo.org writes: What lies at the heart of this problem is the lack of multi-table indices and hence multi-table unique constraints in postgres. AFAIK with those in place the rest amounts to the removal of ONLY from the constraint check queries plus some code to propagate constraint triggers to child tables. Well, the lack of multi-table indexes certainly is the heart of the problem, but I'm not sure that inventing such a thing is the solution. Quite aside from the implementation difficulties involved in it, doing things that way would destroy some of the major reasons to partition tables at all: * the index grows as the size of the total data set, it's not limited by partition size * can't cheaply drop one partition any more, you have to vacuum the (big) index first * probably some other things I'm not thinking of at the moment. I think the real solution is to upgrade the partitioning infrastructure so that we can understand that columns are unique across the whole partitioned table, when the partitioning is done on that column and each partition has a unique index. True, for partitioned tables multi-table indices reintroduce some of the performance problems that partitioning is supposed to avoid. But OTOH if you use table inheritance as a means to map data models (e.g. EER) more naturally to SQL, then multi-table indices have advantages over the partitioning-friendly solution you sketched above. With a multi-table index, SELECT * FROM PARENT WHERE ID=?? has complexity LOG(N*M) where M is the number of tables inheriting from PARENT (including PARENT itself), and N the average number of rows in these tables. With one index per child, the complexity is M*LOG(N) which is significantly higher if M is large. Constraint exclusion could reduce that to LOG(N), but only if each child is has it's own private ID range which precludes ID assignment from a global sequence and hence makes ID assignment much more complex and error-prone. Anyway, I was wondering why we need guaranteed uniqueness for FK relationships anyway. Because if we don't (which I didn't check prior to posting this I must admit), then why can't we simply remove the ONLY from the RI queries and let ALTER TABLE attach the RI triggers not only to the parent but also to all children. What am I missing? best regards, Florian Pflug smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] Partitioning/inherited tables vs FKs
Florian Pflug f...@phlo.org writes: Anyway, I was wondering why we need guaranteed uniqueness for FK relationships anyway. It's required by spec, and the semantics aren't terribly sensible without it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
