If PostgreSQL failed to compile on your computer or you found a bug that
is likely to be specific to one platform then please fill out this form
and e-mail it to pgsql-ports (AT) postgresql (DOT) org.

To report any other bug, fill out the form below and e-mail it to
pgsql-bugs (AT) postgresql (DOT) org.

If you not only found the problem but solved it and generated a patch
then e-mail it to pgsql-patches (AT) postgresql (DOT) org instead. Please use the
command "diff -c" to generate the patch.

You may also enter a bug report at http://www.postgresql.org/ instead of
e-mail-ing this form.

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D
POSTGRESQL BUG REPORT TEMPLATE
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D


Your name : Vivien MALERBA
Your email address : malerba (AT) gnome-db (DOT) org


System Configuration
---------------------
Architecture (example: Intel Pentium) :Intel Pentium

Operating System (example: Linux 2.0.26 ELF) :Linux 2.4.7-10

PostgreSQL version (example: PostgreSQL-7.3): PostgreSQL-7.4 Beta 4

Compiler used (example: gcc 2.95.2) :gcc 3.0.2


Please enter a FULL description of your problem:
------------------------------------------------
The information_schema.referential_constraints retuns wrong data because
there is
an incomplete joining condition in the WHERE clause.




Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------





If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
Fix the buggy joining condition in the view itself. Here is the working
view (probably
to be integrated into backend/catalog/information_schema.sql).

Sorry, I did not have the time to produce a patch...

CREATE VIEW referential_constraints AS
SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
CAST(con.conname AS sql_identifier) AS constraint_name,
CAST(current_database() AS sql_identifier) AS
unique_constraint_catalog,
CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,

CAST(
CASE con.confmatchtype WHEN 'f' THEN 'FULL'
WHEN 'p' THEN 'PARTIAL'
WHEN 'u' THEN 'NONE' END
AS character_data) AS match_option,

CAST(
CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
WHEN 'n' THEN 'SET NULL'
WHEN 'd' THEN 'SET DEFAULT'
WHEN 'r' THEN 'RESTRICT'
WHEN 'a' THEN 'NOACTION' END
AS character_data) AS update_rule,

CAST(
CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
WHEN 'n' THEN 'SET NULL'
WHEN 'd' THEN 'SET DEFAULT'
WHEN 'r' THEN 'RESTRICT'
WHEN 'a' THEN 'NOACTION' END
AS character_data) AS delete_rule

FROM pg_namespace ncon,
pg_constraint con,
pg_class c,
pg_constraint pkc,
pg_namespace npkc,
pg_user u

WHERE ncon.oid =3D con.connamespace
AND con.conrelid =3D c.oid
AND con.confkey =3D pkc.conkey
AND pkc.connamespace =3D npkc.oid
AND c.relowner =3D u.usesysid
AND c.relkind =3D 'r'
AND con.contype =3D 'f'
AND con.confrelid =3D pkc.conrelid
AND u.usename =3D current_user;

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

malerba (AT) gnome-db (DOT) org writes:
I think this is correct as far as it goes, but there are more problems.

For one, I believe we also need to check the contype of the pkc row;
otherwise matches against check constraints are possible.

Another problem is that the view will fail to list FK constraints at all
if it cannot identify a matching unique constraint. Which there may not
be (the backend code for creating an FK checks for a matching unique
index, quite a different animal). And the check for match is inadequate
anyway, because it is using "con.confkey = pkc.conkey", which only
matches if the unique constraint lists the same columns *in the same
order* as the FK constraint does. The backend code does not require
that.

A more robust way to handle things would be to make use of pg_depend to
find the index the FK constraint depends on and then chain to the unique
constraint associated with that index. However, we need to decide what
to do if there is no such unique constraint. I don't think "omit the FK
constraint from the view" is the right answer. We could return nulls
for the unique_constraint_schema and unique_constraint_name, or we could
return the name of the index itself (not standard, but then the
underlying situation isn't standard either).

Comments?

Another question is whether to force an initdb after making this change.
If we don't, existing beta testers may continue to use the incorrect
view definition.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Peter Eisentraut <peter_e (AT) gmx (DOT) net> writes:
Mmm ... can't say that I agree. The FK constraint itself is a perfectly
good constraint. It may be that ignoring such constraints is okay per
the letter of the spec, but given that we have the extension to support
FK constraints on non-constraint-associated indexes, it seems to me that
this view should cope too.

No, because that would entail a genuine loss of capability: FK
constraints couldn't be built using indexes that were made by CREATE
UNIQUE INDEX rather than through the unique/pk constraint syntax.
In particular this would mean that non-btree indexes could not be used.
(Yes, I know that as of today we don't have UNIQUE support in any of the
non-btree index types, but that will change. IIRC Neil Conway has
already been working on unique hashes, and I'm sure GIST will support it
eventually as well.)

Good point. But we don't support explicit foreign key constraints on
system tables, and probably aren't going to start anytime soon, so I
think this is probably okay.

Okay. I've been reviewing the rest of information_schema and have found
some other issues, but am not all the way through yet. Please hold off
the initdb force until I've reported on the other stuff.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Peter Eisentraut <peter_e (AT) gmx (DOT) net> writes:
So are you proposing we extend the constraint syntax instead? I think
it's better to keep our nonstandard index support in a separate,
nonstandard statement (CREATE INDEX) rather than mash it together with
spec-mandated syntax. That seems like a recipe for getting stuck when
the spec adds extensions.

The way that was just discussed --- with a deferrable constraint, you
don't elog immediately when the index detects a collision, but make a
note to recheck that particular key value at the time the constraint
should be enforced. I can't imagine that we'd want to do unique
constraints without any index support. How would you avoid having to
check lots and lots of uninteresting rows?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)