I recently fell afoul of a weird edge case while writing an extension. It seems Postgres allows for an ambiguous
columnreference to oid in the where clause when joining to pg_roles. It just arbitrarily chooses pg_roles.oid and
ignoresthe 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_rolesis 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
thathappen to be named oid do survive that far. Therefore, postgres does not realize that is has an ambiguous column
referenceon its hands and binds to column presented by the view. I could definitely be wrong because I haven’t looked
athe 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
