Thread: information_schema.referential_constraints broken?

This query:

select * from information_schema.referential_constraints;

under PG 8.4.5 (Windows platform) produces this error message:

ERROR:  operator is not unique: smallint[] <@ smallint[]

LINE 1: select $1 <@ $2 and $2 <@ $1

                  ^

HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.

QUERY:  select $1 <@ $2 and $2 <@ $1

CONTEXT:  SQL function "_pg_keysequal" during inlining

********** Error **********

ERROR: operator is not unique: smallint[] <@ smallint[]

SQL state: 42725

Hint: Could not choose a best candidate operator. You might need to add explicit type casts.

Context: SQL function "_pg_keysequal" during inlining

I don’t have more recent 8.4 releases to try it on.

It works fine on PG 9.1.3.

Did anyone else experienced this problem?

Regards,

Igor Neyman

Answering my own question.

Replacing original definition of _pg_keysequal (in information_schema):

CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean

    LANGUAGE sql IMMUTABLE  -- intentionally not STRICT, to allow inlining

    AS 'select $1 <@ $2 and $2 <@ $1';

with this one:

CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean

    LANGUAGE sql IMMUTABLE  -- intentionally not STRICT, to allow inlining

    AS 'select $1 operator(pg_catalog.<@) $2 and $2 operator(pg_catalog.<@) $1';

makes queries like:

select * from information_schema.referential_constraints;

against information_schema.referential_constraints work without errors.

Sorry, for the noise.

Igor Neyman