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


Mon, 14 May 2001 16:41:56 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Updating system catalogs after a tuple deletion
"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

Mon, 14 May 2001 10:36:18 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Updating system catalogs after a tuple deletion 
> 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


Tue, 15 May 2001 10:12:24 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
RE: 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?



Mon, 14 May 2001 19:50:17 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
RE: Updating system catalogs after a tuple deletion 
> > 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


Tue, 15 May 2001 10:56:16 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
RE: Updating system catalogs after a tuple deletion 
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

Mon, 14 May 2001 23:10:44 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: 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   |/

Tue, 15 May 2001 13:24:46 +1000
Philip Warner <pjw@rhyme.com.au>
RE: Updating system catalogs after a tuple deletion 
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

Mon, 14 May 2001 23:34:33 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Updating system catalogs after a tuple deletion 
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 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   |/

Tue, 15 May 2001 14:52:43 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Updating system catalogs after a tuple deletion 
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.


Mon, 14 May 2001 21:55:31 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Updating system catalogs after a tuple deletion 
> 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


Mon, 21 May 2001 09:43:33 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
RE: 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 <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.



Tue, 22 May 2001 12:43:00 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
RE: Updating system catalogs after a tuple deletion 
> 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


Wed, 23 May 2001 10:41:41 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
RE: 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 <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.


Tue, 22 May 2001 19:52:49 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
RE: Updating system catalogs after a tuple deletion 