Re: [BUGS] Can't read oprcode from remote pg_operator

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