Thread: [BUGS] Can't read oprcode from remote pg_operator
It seems that, if you have a foreign table pointing to pg_operator in another cluster, you cannot return the oprcode column for certain rows. I'm getting this on 10 beta 3. I have imported pg_operator using IMPORT FOREIGN SCHEMA, and I get the following errors: # SELECT * FROM pg.pg_operator ORDER BY oprname DESC, oprleft, oprright LIMIT 701; ERROR: more than one function named "pg_catalog.tsquery_phrase" CONTEXT: column "oprcode" of foreign table "pg_operator" # SELECT * FROM pg.pg_operator ORDER BY oprname, oprleft, oprright LIMIT 701; ERROR: more than one function named "pg_catalog.jsonb_delete" CONTEXT: column "oprcode" of foreign table "pg_operator" This seems like a bug, although I suspect this may be explainable as expected and wanted behaviour. Regards Thom -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Thom Brown <thom@linux.com> writes:
> It seems that, if you have a foreign table pointing to pg_operator in
> another cluster, you cannot return the oprcode column for certain
> rows. I'm getting this on 10 beta 3.
I don't think that has anything to do with remoteness or not, nor which
PG version you try. The regproc representation is inherently ambiguous,
so even locally you will get:
regression=# select oprcode::text::regproc from pg_operator;
ERROR: more than one function named "pg_catalog.tsquery_phrase"
Likewise for, eg, pg_aggregate.aggfnoid.
> I have imported pg_operator using IMPORT FOREIGN SCHEMA,
If that seemed like a widely useful thing to do, I might be more
worried. But if you want to do this, I'd suggest making a view
over pg_operator that casts the regproc columns to regprocedure,
and then importing that. Even then, it will fail if the remote
catalog contains references to any functions that don't exist
locally.
We've talked in the past about deprecating regproc/regoper and
converting those columns to the fully qualified types; that
would help the ambiguity part of your issue, though not the
nonexistence part. But that's blocked on teaching genbki.pl
to do the equivalent of regprocedurein and fill those columns
with numeric OIDs in the BKI file.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On 5 September 2017 at 12:57, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Thom Brown <thom@linux.com> writes: >> It seems that, if you have a foreign table pointing to pg_operator in >> another cluster, you cannot return the oprcode column for certain >> rows. I'm getting this on 10 beta 3. > > I don't think that has anything to do with remoteness or not, nor which > PG version you try. The regproc representation is inherently ambiguous, > so even locally you will get: > > regression=# select oprcode::text::regproc from pg_operator; > ERROR: more than one function named "pg_catalog.tsquery_phrase" > > Likewise for, eg, pg_aggregate.aggfnoid. I guess it was only revealed by my accessing the table remotely. A plain select locally works, but a plain select remotely, doesn't. >> I have imported pg_operator using IMPORT FOREIGN SCHEMA, > > If that seemed like a widely useful thing to do, I might be more > worried. But if you want to do this, I'd suggest making a view > over pg_operator that casts the regproc columns to regprocedure, > and then importing that. Even then, it will fail if the remote > catalog contains references to any functions that don't exist > locally. Yeah, I think I'll make a view in my case. Thanks. > We've talked in the past about deprecating regproc/regoper and > converting those columns to the fully qualified types; that > would help the ambiguity part of your issue, though not the > nonexistence part. But that's blocked on teaching genbki.pl > to do the equivalent of regprocedurein and fill those columns > with numeric OIDs in the BKI file. Thom -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs