Thread: pg metadata and doc bug

Dear PostgreSQL developers,

#### 1

I stumbled upon an obscure bug (or undesirable feature:-) in the schema 
metadata accessible through the information_schema, and possibly 
pg_catalog as well. As it was mixed in a bug in some in my code, it was 
hard for me to identify it.

The issue is that when one does (in pg 8.3.5)
    ALTER TABLE foo ADD CONSTRAINT xxx UNIQUE ON (...);

this results in a constraint *and* an index, but when one does only the 
corresponding:
    CREATE UNIQUE INDEX foo(...);

then the index is created but there is no constraint. So what?

The consequence arises downhill when one declares a foreign key which uses 
this index as a target. The FK constraint is accepted, but as the metadata 
contents does not include the constraint, you cannot find the relevant 
informations by joining the various information_schema relations.

I was just looking for this information, how unlucky of me:-)

See the attached file for an example. Comment out the index creation and 
uncomment the unique constraint to see the difference in the metadata
(information_schema, and possibly underlying pg_catalog).

ITSM that the fix is that a 'CREATE UNIQUE INDEX...' shoud also add the 
corresponding constraint.


#### 2

Also, there is a minor bug in the documentation, which was the another 
source of my troubles:
 information_schema.KEY_COLUMN_USAGE.position_in_unique_constraint

is tagged as "NOT IMPLEMENTED", but it looks like it is implemented.

-- 
Fabien.

Fabien COELHO <coelho@cri.ensmp.fr> writes:
> The issue is that when one does (in pg 8.3.5)
>      ALTER TABLE foo ADD CONSTRAINT xxx UNIQUE ON (...);
> this results in a constraint *and* an index, but when one does only the 
> corresponding:
>      CREATE UNIQUE INDEX foo(...);
> then the index is created but there is no constraint.

This is intentional.  You didn't create a constraint in the sense of the
SQL standard, and furthermore it may very well be impossible to
represent the index as a constraint in information_schema.  (For
instance, the index might be functional or partial --- in fact, it most
likely is special in some way, or you'd not have bothered to use the
nonstandard syntax to make it.)


> Also, there is a minor bug in the documentation, which was the another 
> source of my troubles:
>   information_schema.KEY_COLUMN_USAGE.position_in_unique_constraint
> is tagged as "NOT IMPLEMENTED", but it looks like it is implemented.

Yeah, looks like the documentation is out of date there.
        regards, tom lane

Dear Tom,

>> The issue is that when one does (in pg 8.3.5)
>>      ALTER TABLE foo ADD CONSTRAINT xxx UNIQUE ON (...);
>> this results in a constraint *and* an index, but when one does only the
>> corresponding:
>>      CREATE UNIQUE INDEX foo(...);
>> then the index is created but there is no constraint.
>
> This is intentional.  You didn't create a constraint in the sense of the
> SQL standard, and furthermore it may very well be impossible to
> represent the index as a constraint in information_schema.  (For
> instance, the index might be functional or partial --- in fact, it most
> likely is special in some way, or you'd not have bothered to use the
> nonstandard syntax to make it.)

Ok. I can understand that.

ISTM that I still have a bug: I have a query on the information_schema 
which returns stupid results because there is no matching constraint.

The other way to "fix" is that the "foreign key" declaration should be 
rejected because there is no unique constraint on the target attribute. I 
guess that the FK checks that there is an index while it should 
(logically) check that there is a unique constraint, which implies the 
index.

Thanks for your answer,

-- 
Fabien.