I recently fell afoul of a weird edge case while writing an extension. It
seems Postgres allows for an ambiguous column reference to oid in the where
clause when joining to pg_roles. It just arbitrarily chooses pg_roles.oid and
ignores the conflicting name. Example:
postgres=# CREATE TABLE t_demo();
CREATE TABLE
postgres=# SELECT r.rolname FROM pg_class c JOIN pg_roles r ON (c.relowner =
r.oid) WHERE oid = 't_demo'::regclass;
rolname
---------
(0 rows)
postgres=# SELECT r.rolname FROM pg_class c JOIN pg_roles r ON (c.relowner =
r.oid) WHERE c.oid = 't_demo'::regclass;
rolname
----------
postgres
(1 row)
postgres=# SELECT r.rolname FROM pg_class c JOIN pg_roles r ON (c.relowner =
r.oid) WHERE r.oid = 't_demo'::regclass;
rolname
---------
(0 rows)
It seems like ambiguous oid references generally hit a different error message
than normal ambiguous column references.
postgres=# CREATE TABLE t1(x int, y int) WITH OIDS;
CREATE TABLE
postgres=# CREATE TABLE t2(x int, y int) WITH OIDS;
CREATE TABLE
postgres=# SELECT * FROM t1 JOIN t2 ON (t1.x = t2.x) WHERE y = 5;
ERROR: column reference "y" is ambiguous
LINE 1: SELECT * FROM t1 JOIN t2 ON (t1.x = t2.x) WHERE y = 5;
postgres=# SELECT * FROM t1 JOIN t2 ON (t1.x = t2.x) WHERE oid = 5;
ERROR: column "oid" does not exist
LINE 1: SELECT * FROM t1 JOIN t2 ON (t1.x = t2.x) WHERE oid = 5;
^
HINT: There is a column named "oid" in table "t1", but it cannot be referenced
from this part of the query.
It’s clear that oids are getting to another code path normally and I suspected
that it is related to the fact that pg_roles is a view with an explicit oid
column. So I tried this test:
postgres=# CREATE VIEW v1 AS SELECT x, y, oid FROM t1;
CREATE VIEW
postgres=# SELECT * FROM v1 JOIN t2 ON (v1.x = t2.x) WHERE oid = 5;
x | y | oid | x | y
---+---+-----+---+---
(0 rows)
It would appear that unqualified oid columns do not make it all the way to
where clause evaluation, whereas columns that happen to be named oid do survive
that far. Therefore, postgres does not realize that is has an ambiguous column
reference on its hands and binds to column presented by the view. I could
definitely be wrong because I haven’t looked a the code but that is what the
behavior looks like.
This bug was first found on 9.3.19, but I just tested this against 10.1 as well.
- Matt K
