When the ::regclass is applied at the coordinator, it either fails to map OID to relname or even worse returns a wrong result. The following query from "inherit" test case in regression demonstrates this issue:
regression=# select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
relname | f1 | f2
---------+----+----
35090 | 3 | 3
35090 | 4 | 4
35093 | 3 | 3
35093 | 4 | 4
35267 | 1 | 1
35267 | 2 | 2
35270 | 1 | 1
35270 | 2 | 2
(8 rows)
regression=# explain verbose select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Sort (cost=387.51..392.61 rows=2041 width=12)
Output: (((bar.tableoid)::regclass)::text), bar.f1, bar.f2
Sort Key: (((bar.tableoid)::regclass)::text), bar.f1
-> Result (cost=100.00..275.30 rows=2041 width=12)
Output: ((bar.tableoid)::regclass)::text, bar.f1, bar.f2
-> Append (cost=100.00..265.10 rows=2041 width=12)
-> Remote Subquery Scan on all (datanode_1,datanode_2) (cost=100.00..100.02 rows=1 width=12)
Output: bar.tableoid, bar.f1, bar.f2
-> Seq Scan on pg_temp_2.bar (cost=0.00..0.00 rows=1 width=12)
Output: bar.tableoid, bar.f1, bar.f2
-> Remote Subquery Scan on all (datanode_1,datanode_2) (cost=100.00..165.08 rows=2040 width=12)
Output: bar2.tableoid, bar2.f1, bar2.f2
-> Seq Scan on pg_temp_2.bar2 (cost=0.00..30.40 rows=2040 width=12)
Output: bar2.tableoid, bar2.f1, bar2.f2
(14 rows)
Since OIDs of objects may differ on coordinators and datanodes, trying to convert an OID generated by a datanode at the coordinator gives wrong result.
Issue by pavanvd Monday Jun 29, 2015 at 05:20 GMT Originally opened as https://github.com/pavanvd/Postgres-XL-Private/issues/4
When the ::regclass is applied at the coordinator, it either fails to map OID to relname or even worse returns a wrong result. The following query from "inherit" test case in regression demonstrates this issue:
Since OIDs of objects may differ on coordinators and datanodes, trying to convert an OID generated by a datanode at the coordinator gives wrong result.