Re: constraints and sql92 information_schema compliance

On Wed, 15 Mar 2006, Clark C. Evans wrote:

> On Tue, Mar 14, 2006 at 11:11:29PM -0800, Stephan Szabo wrote:
> | When we're allowing other order access, immediately reorder the
> | constraint information to match the primary key order.
>
> Let me try to parrot. In PostgreSQL, the pairing information between
> the foreign-key and unique-key constraint is available; even though it
> isn't represented in the information_schema. Hence, this option
> re-orders the foreign-key columns to match the corresponding canidate
> key constraint (unique _or_ foreign key). If so, I like it.

Right, at create time (a,b) references t(d,c) where the key is actually
t(c,d) would get treated as if the user had actually written (b,a)
references t(c,d) if it's set up to accept that at all.

> | This helps out
> | with IS since the loaded constraint should display properly, but
> | theoretically could change the visual representation after load for people
> | who don't care about this option.
>
> I doubt that the actual ordering of the columns in the foreign
> key constraint matters to people; so I don't see a downside with
> this option other than the perhaps unexpected difference.

The main case I could see is if an app thinks it knows what the key should
look like (and looks at the catalogs or psql output or pg_dump output or
the output of a function that gives back the key information in an api
potentially) and now sees the key disappear and/or a new key appear after
the upgrade.

This option seems like the best apart from that one sticking point.

> | Change the representation unconditionally on dump. Basically reorder the
> | constraint at dump time to always generate a dump in SQL03 order. This has
> | the same downside as the above except only after another dump/restore.
>
> You could do both?

Well, if you do the first, you're effectively doing this one as well,
since it'll always dump in SQL03 order from that point forward. It looks
like we can't really meaningfully change the behavior against old
versions, so this would only affect dumps of 8.2 servers or later in any
case.