Re: Constraint stuff

At 10:29 6/08/00 -0700, Stephan Szabo wrote:
>
>The problem with storing source is that it doesn't
>get changed when things change. Try altering
>a column name that has a check constraint, then
>dump the database.

Or renaming a referenced table - I think the current constraint system will
handle this since OIDs don't change.

>So, what I was thinking is, that if we have another
>table to store this kind of constraint info, it
>should probably store information for all constraints.
>I was thinking two tables, one (say pg_constraint)
>which stores basic information about the constraint
>(what type, the constraint name, primarily constraintd
>table, maybe owner if constraints have owners in SQL)
>and a source form (see more below).

This sounds reasonable.

>The second table stores references from this constraint.
>So any table, column, index, etc is stored here.
>Probably something of the form constraintoid,
>type of thing being referenced (the oid of the table?),
>the oid of the referenced thing and a number.

I would prefer to see this generalized: a dependencies table that lists
both the referrer OID *and* type, as well as the refrerenced thing oid &
type. This then allows things such as SQL functions to make entries in this
table as well as views etc etc.

>The number comes in to the source form thats stored.
>Anywhere that we're referencing something that a name
>is insufficient for (like a column name or table name)
>we put something into the source for that says
>referncing column n of the referenced thing m.

Don't know enough about the internals, but don't we have attr ids for this,
and/or won't OIDs work in most cases? Maybe I'm missing your point.

>- There are some problems I see right off both conceptually
>and implementation, but I thought someone might be able
>to come up with a better idea once it was presented (even
>if it's just a "not worth the effort" :) )

It seems to me that:

- 'format_constraint' is a good idea
- we need the dependency stuff
- dumping source in canonical form is best put in the backend
(philosophical point)
- I presume it's a first part of a full implementation of 'alter table
add/drop constraint...'

so I don't think it's a waste of time.

>One of the problems I see is that if taken to its end,
>would you store function oids here?

Sounds sensible.

>If so, that might
>make it harder to allow a drop function/create function
>to ever work transparently in the future.

I *think* it doesn't work now; yes you can drop the function, but AFAIK,
the constraint references the old one (at least that's true for normal
triggers). What you are proposing makes people aware that they are about to
break more things than they know.

>Plus, I'm not even really sure if it would be reasonable
>to get a source form like I was thinking of for check
>constraints really.

I suspect it has to depend on how the constraint is acually checked. If
They are checkied by using table OIDs then you need to store the OID
dependency and *somehow* reconstruct the source. If they are checked using
table names (getting OID each time), then store the name and the raw source
(maybe). You need to handle renaming of tables referenced in CHECK clauses.
I hate rename (but I use it).

Maybe you can do something nasty like store the source with escape
characters and OIDs in place of names. This is not as bad as it sounds, I
think. It also gets around the problem that original source may be
unrecoverable (eg. COALESCE is translated to CASE in the parser, so a CHECK
clause that uses COALESCE will never be fully recoverable - although most
people would not see this as a problem). This messing around would have to
be done in the parser, I would guess. So:

check exists(select * from reftbl where reffld=tbl.origfld)

might become:

check exists(select * from %%table:<OID>%% where
%%table-attr:<Table-OID>,<Attd-ID>%%
= %%table:<OID>%%.%%table-attr:<Table-OID>,<Attd-ID>%%

Looking at this, maybe it's not such a good idea after all...

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 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 |/