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.
DROP TABLE bla CASCADE;
DROP TABLE foo CASCADE;
CREATE TABLE foo (
fid SERIAL PRIMARY KEY,
stuff TEXT DEFAULT ''::text NOT NULL
);
CREATE TABLE bla (
bid SERIAL PRIMARY KEY,
stuff TEXT NOT NULL
);
-- this should amount to a constraint...
CREATE UNIQUE INDEX foo_stuff_uniq ON foo USING btree (stuff);
-- BUT:
-- 1. it does not appear anywhere
-- in information_schema.table_constraints
-- (nor in pg_catalog.pg_constraint, but only in pg_catalog.pg_index(es))
-- 2. foo_bla unique_constraint_name (next) is empty
-- in information_schema.referential_constraints
-- however after this one: (uncomment to test)
-- ALTER TABLE foo ADD CONSTRAINT foo_stuff_uniq2 UNIQUE(stuff);
-- it appears both as a constraint AND an index, and
-- the unique_constraint_name is not empty.
ALTER TABLE ONLY bla
ADD CONSTRAINT "bla_foo" FOREIGN KEY (stuff) REFERENCES foo(stuff);
SELECT *
FROM information_schema.table_constraints
WHERE constraint_schema = 'public';
SELECT *
FROM information_schema.referential_constraints;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
