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)