RE: [HACKERS] Updating system catalogs after a tuple deletion
On Wed, 23 May 2001, Christopher Kings-Lynne wrote: > > Actually, I realized that in the face of multiple inheritance, dynamically > > generated constraint names still fail with our current default naming > > scheme. What happens when two tables both have a $1 and then you inherit > > from both of them, at this point it's pretty much too late to rename the > > constraint on one of the parents and I think right now the constraints get > > named $1 and $2. Either, we should punt, and make it so they both end up > > $1, or perhaps we should change $1 to something like _$1 where > > table is the table name of the table on which the constraint was defined. > > So if you have table1 with an unnamed constraint, it and all of its > > children would see the constraint as table1_$1. > > Even if we implemented this, it wouldn't fix the problem of duplicated user > specified constraint names under multiple inheritance. It seems a many-many > pg_constraint table it the only clean solution... I'm not sure that there is a workable solution for user specified names without going the constraint names should be unique throughout solution (which Tom doesn't want, and actually neither do I really even though I bring it up as a compliance issue). I think that users will have to be assumed to be smart enough not to screw themselves up with badly named constraints. We definately need better storage of our constraints. I liked the constraint is stored once with pointers from referencing tables idea. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [HACKERS] Updating system catalogs after a tuple deletion
(This machine still is having trouble with mx records :( ) On Mon, 21 May 2001, Christopher Kings-Lynne wrote: > > Actually this brings up a problem I'm having with ALTER TABLE ADD > > CONSTRAINT and since it mostly affects you with DROP CONSTRAINT, I'll > > bring it up here. If you have a table that has check constraints or > > is inherited from multiple tables, what's the correct way to name an > > added constraint that's being inherited? If it's $2 in the parent, > > but the child already has a $2 defined, what should be done? The > > reason this affects drop constraint is knowing what to drop in the > > child. If you drop $2 on the parent, what constraint(s) on the child > > get dropped? > > It occurs to me that there's a solution to this problem. All you need to do > is in heap.c in the piece of code I modified earlier for generating > constraint names and checking specified ones you simply make sure it is > unique for the parent table and for ALL its children. > > This will stop people (1) adding named constraints that aren't unique across > all children, noting that these new constraints need to be added to the > children as well as the parent and (2) dynamically generated constraint > names will be unique across all children and also can then be immediately > propagated to inherited tables. > > With this enforced, surely there is a _guaranteed_ match between the name of > a parent constraint and the same constraint in the inherited tables? The > only problem, I guess, would be when you import data from old versions of > PostgreSQL into a new version that has this assumption/restriction. Actually, I realized that in the face of multiple inheritance, dynamically generated constraint names still fail with our current default naming scheme. What happens when two tables both have a $1 and then you inherit from both of them, at this point it's pretty much too late to rename the constraint on one of the parents and I think right now the constraints get named $1 and $2. Either, we should punt, and make it so they both end up $1, or perhaps we should change $1 to something like _$1 where table is the table name of the table on which the constraint was defined. So if you have table1 with an unnamed constraint, it and all of its children would see the constraint as table1_$1. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Updating system catalogs after a tuple deletion
On Mon, 14 May 2001, Tom Lane wrote: > Philip Warner <[EMAIL PROTECTED]> writes: > > It is worth considering skipping the entire 'copy to children' approach? > > Something like: > > pg_constraints(constraint_id, constraint_name, constraint_details) > > pg_relation_constraints(rel_id, constraint_id) > > Then, when we drop constraint 'FRED', the relevant rows of these tables are > > deleted. There is only ever one copy of the constraint definition. > > This would work if we abandon the idea that a table cannot have > multiple constraints of the same name (which seems like an unnecessary > restriction to me anyway). I'm not sure it would. You could have two constraint_ids with the same name still as long as there's no constraint on constraint_name, both would presumably be deleted on a drop. Since rel_id is only part of pg_relation_constraints you wouldn't want the constraint_name to be forced unique (barring the spec definition) anyway, so there'd be nothing to prevent you from naming all your constraints FRED, just you'd have a better way to refer to a particular constraint than its name internally. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Updating system catalogs after a tuple deletion
At 23:34 14/05/01 -0400, Tom Lane wrote: >Philip Warner <[EMAIL PROTECTED]> writes: >> It is worth considering skipping the entire 'copy to children' approach? >> Something like: >> pg_constraints(constraint_id, constraint_name, constraint_details) >> pg_relation_constraints(rel_id, constraint_id) >> Then, when we drop constraint 'FRED', the relevant rows of these tables are >> deleted. There is only ever one copy of the constraint definition. ... > >A small advantage of doing it this way is that it'd be easier to detect >the case where the same constraint is multiply inherited from more than >one parent, as in > Other advantages include: - easy ALTER TABLE ALTER CONSTRAINT (does it exist?) - cleaner pg_dump code - possibility to have NULL names for system objects which avoids any namespace corruption. It's probably worth adding extra information to the pg_constraints table to include inform,ation about how it was created (pk, fk, user-defined etc). Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Updating system catalogs after a tuple deletion
Stephan Szabo <[EMAIL PROTECTED]> writes: > Actually this brings up a problem I'm having with ALTER TABLE ADD > CONSTRAINT and since it mostly affects you with DROP CONSTRAINT, I'll > bring it up here. If you have a table that has check constraints or > is inherited from multiple tables, what's the correct way to name an > added constraint that's being inherited? If it's $2 in the parent, > but the child already has a $2 defined, what should be done? The > reason this affects drop constraint is knowing what to drop in the > child. If you drop $2 on the parent, what constraint(s) on the child > get dropped? Seems like depending on the name is inadequate. Perhaps a column should be added to pg_relcheck to show that a constraint has been inherited. Maybe "rcinherit" = OID of parent's equivalent constraint, or 0 if constraint was not inherited. Then you could do the right things without making any assumptions about constraint names being the same. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [HACKERS] Updating system catalogs after a tuple deletion
At 19:50 14/05/01 -0700, Stephan Szabo wrote: > >If it's $2 in the parent, >but the child already has a $2 defined, what should be done? The >reason this affects drop constraint is knowing what to drop in the >child. If you drop $2 on the parent, what constraint(s) on the child >get dropped? > It is worth considering skipping the entire 'copy to children' approach? Something like: pg_constraints(constraint_id, constraint_name, constraint_details) pg_relation_constraints(rel_id, constraint_id) Then, when we drop constraint 'FRED', the relevant rows of these tables are deleted. There is only ever one copy of the constraint definition. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Updating system catalogs after a tuple deletion
Philip Warner <[EMAIL PROTECTED]> writes: > It is worth considering skipping the entire 'copy to children' approach? > Something like: > pg_constraints(constraint_id, constraint_name, constraint_details) > pg_relation_constraints(rel_id, constraint_id) > Then, when we drop constraint 'FRED', the relevant rows of these tables are > deleted. There is only ever one copy of the constraint definition. This would work if we abandon the idea that a table cannot have multiple constraints of the same name (which seems like an unnecessary restriction to me anyway). A small advantage of doing it this way is that it'd be easier to detect the case where the same constraint is multiply inherited from more than one parent, as in table P has a constraint C1 inherits from P C2 inherits from P GC1 inherits from C1,C2 Currently, GC1 ends up with two duplicate constraints, which wastes time on every insert/update. Not a very big deal, perhaps, but annoying. It'd be nice to recognize and remove the extra constraint. (However, the inherited-from link that I proposed a few minutes ago could do that too, if the link always points at the original constraint and not at the immediate ancestor.) BTW, any proposed DROP CONSTRAINT algorithm should be examined to make sure it doesn't fail on this sort of structure ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [HACKERS] Updating system catalogs after a tuple deletion
At 19:50 14/05/01 -0700, Stephan Szabo wrote: > >If it's $2 in the parent, >but the child already has a $2 defined, what should be done? The >reason this affects drop constraint is knowing what to drop in the >child. If you drop $2 on the parent, what constraint(s) on the child >get dropped? AFAIK, it is not possible to derive this. pg_dump makes the assumption that if the constraint source is the same, and both names start with '$', then it is inherited. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [HACKERS] Updating system catalogs after a tuple deletion
On Tue, 15 May 2001, Christopher Kings-Lynne wrote: > Lastly, inheritance? I plan to leave out worrying about inheritance for > starters, especially since it seems that half the constraints when added > don't even propagate themselves properly to child tables... Actually this brings up a problem I'm having with ALTER TABLE ADD CONSTRAINT and since it mostly affects you with DROP CONSTRAINT, I'll bring it up here. If you have a table that has check constraints or is inherited from multiple tables, what's the correct way to name an added constraint that's being inherited? If it's $2 in the parent, but the child already has a $2 defined, what should be done? The reason this affects drop constraint is knowing what to drop in the child. If you drop $2 on the parent, what constraint(s) on the child get dropped? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Updating system catalogs after a tuple deletion
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > What do I use instead of the CatalogIndexInsert command to tell the index > that a tuple has been removed? Nothing. The tuple isn't really gone, and neither are its index entries. Getting rid of them later is VACUUM's problem. BTW, there already is code that cleans out pg_relcheck: see RemoveRelCheck() in src/backend/catalog/heap.c. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly