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   |/

Mon, 07 Aug 2000 11:01:43 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Constraint stuff
On Mon, 7 Aug 2000, Philip Warner wrote:

> >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. 

That makes more sense, yes. :)  Although not all of those things would
probably use it immediately.  

> 
> >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.

I was thinking of it more for getting a textual representation back out
of the dependencies for constraints and thinking that I might want to 
reference something other than its name that's on something that's
referenced. And actually referncing column n, meant more like attrno
n of the row that m refers to. (Sort of like your thing below for
%%table:OID,attrno)

> >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.

True, I just wanted to point it out in case someone had some thought on
changing it so that the system somehow fixed such references, but it
seems like alter function is more likely :)

> >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...

:) Basically that's sort of what I was proposing with the %m.n above where
I was referencing a reference rather than the oid directly and the n was 
basically attd-id (and the reference stored the type so i didn't need
it. But if it had to be sent from the parser then your format probably
makes more sense.  I was thinking about reversing from the stored
expression in some fashion (but that wouldn't recover a coalesce or
something like that)


Mon, 7 Aug 2000 10:01:30 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Constraint stuff