Re: constraints and sql92 information_schema compliance

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

> On Tue, Mar 14, 2006 at 10:01:16PM -0800, Stephan Szabo wrote:
> | The point is that because rows in a table don't have order (unless
> | information_schema has special rules) the two constraints above seem to
> | look the same to me in their representation in
> | information_schema.constraint_column_usage. If that's true then forcing
> | the referenced columns to match exactly doesn't actually fix the problem
> | with the representation in infomration schema. The same ambiguity exists.
>
> Actually, there is no ambiguity; it's just that constraint_column_usage
> view is absolutely useless. You want to be using key_column_usage.

Yeah, I remembered afterwards, so I had to send that later message.

This came up because I was trying to figure out at what point (if ever)
reordering should happen when the variable is set to allow references to
the unique constraint in other orders (see below), and while looking at
that, I poked at information_schema to see if I could come up with a good
reason to do one way or another and queried the wrong one and then worried
that I wasn't going to actually be solving the fundamental problem.

---

The main options seem to be:
When we're allowing other order access, immediately reorder the
constraint information to match the primary key order. 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.
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.
Change the representation on dump only if the flag is set (probably
exporting this as an option to pg_dump as well). This could be a little
more difficult to use, but pretty much causes the user to drive the
choice.