Обсуждение: Updating system catalogs after a tuple deletion
Hi guys,
I have an experimental piece of code like this (that tries to remove
pg_relcheck entries):
deleted = 0;
while (HeapTupleIsValid(tup = heap_getnext(rcscan, 0))) {simple_heap_delete(rcrel, &tup->t_self);++deleted;
/* Keep catalog indices current */CatalogOpenIndices(Num_pg_relcheck_indices, Name_pg_relcheck_indices,
relidescs);CatalogIndexInsert(relidescs, Num_pg_relcheck_indices, rcrel,
tup);CatalogCloseIndices(Num_pg_relcheck_indices,relidescs);
}
What do I use instead of the CatalogIndexInsert command to tell the index
that a tuple has been removed? I don't see an equivalent CatalogIndexDelete
function...
Is there a better way of doing this?
Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> 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
> Nothing. The tuple isn't really gone, and neither are its index > entries. Getting rid of them later is VACUUM's problem. So the piece of code at the bottom of the AddRelationRawConstraints function that adds the tuple to the indices in heap.c is only necessary because it's _adding_ a tuple? > BTW, there already is code that cleans out pg_relcheck: see > RemoveRelCheck() in src/backend/catalog/heap.c. I am aware of the RemoveRelCheck() function - however it seems to be somewhat misnamed as it seems to simply delete all CHECKS on a relation. Another question: How should I handle RESTRICT/CASCADE? (For CHECK clauses only at the moment) SQL99 doesn't seem to be 100% clear on this, but it seems that if the user specifies: * Nothing: Just drop the constraint, not worrying about dependencies. (Perhaps issuing a NOTICE if there are.) * RESTRICT: Refuse to drop the constraint if it's referred to by another check constraint (impossible?), or if it's referred to in a function or trigger (how do I check this?) or if a view is dependent on it (impossible?) * CASCADE: Like RESTRICT, just drop any objects that depend on the constraint. (This is not easy, and I think I will for the time being issue an ERROR saying that CASCADE is not implemented.) This is especially difficult since I doubt that DROP FUNCTION x CASCADE or DROP VIEW x CASCADE are implemented...? 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... Remarks? Chris
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?
> > 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? I recently had a patch of mine committed to heap.c (rev 1.163->1.164) in the AddRelationRawConstraints function that loops to make sure that automatically generated constraint names are unique. It seems to me that it would be relatively straightforward to make sure that the constraint name is unique in all the inherited tables as well. I've never messed with inheritance, so I probably won't look at implementing that any time soon... Chris
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 |/
Philip Warner <pjw@rhyme.com.au> 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
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 |/
Stephan Szabo <sszabo@megazone23.bigpanda.com> 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
At 23:34 14/05/01 -0400, Tom Lane wrote: >Philip Warner <pjw@rhyme.com.au> 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 objectswhich 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 |/
On Mon, 14 May 2001, Tom Lane wrote: > Philip Warner <pjw@rhyme.com.au> 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.
> 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. Chris
(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 <table>_$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.
> 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 <table>_$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... Chris
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 <table>_$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.