Re: Can't read oprcode from remote pg_operator

On 5 September 2017 at 12:57, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Thom Brown <thom(at)linux(dot)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