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