Обсуждение: (BUG ?) unprefixed oid -> ERROR: cache lookup failed for function

(9.1.4)

Hello,

following query is wrong in my understanding,  as it doesn't specify which oid to use (pg_proc or pg_roles ?)

 but  it is accepted by the parser

select pg_get_functiondef(oid)   -- should be pg_get_functiondef(pg_proc.oid)  

 from pg_proc join pg_roles

     on ( pg_proc.proowner=pg_roles.oid )

 LIMIT 1

ERROR:  cache lookup failed for function 10

EXPLAIN VERBOSE:

Limit  (cost=0.00..0.31 rows=1 width=4)

  Output: (pg_get_functiondef(pg_authid.oid))

  ->  Nested Loop  (cost=0.00..1028.66 rows=3337 width=4)

        Output: pg_get_functiondef(pg_authid.oid)

        Join Filter: (pg_proc.proowner = pg_authid.oid)

        ->  Seq Scan on pg_catalog.pg_proc  (cost=0.00..318.37 rows=3337 width=4)

              Output: pg_proc.proname, pg_proc.pronamespace, pg_proc.proowner, pg_proc.prolang, pg_proc.procost, pg_proc.prorows, pg_proc.provariadic, pg_proc.proisagg, pg_proc.proiswindow, pg_proc.prosecdef, pg_proc.proisstrict, pg_proc.proretset, pg_proc.provolatile, pg_proc.pronargs, pg_proc.pronargdefaults, pg_proc.prorettype, pg_proc.proargtypes, pg_proc.proallargtypes, pg_proc.proargmodes, pg_proc.proargnames, pg_proc.proargdefaults, pg_proc.prosrc, pg_proc.probin, pg_proc.proconfig, pg_proc.proacl

        ->  Materialize  (cost=0.00..1.21 rows=14 width=4)

              Output: pg_authid.oid

              ->  Seq Scan on pg_catalog.pg_authid  (cost=0.00..1.14 rows=14 width=4)

                    Output: pg_authid.oid

best regards,

Marc Mamin

"Marc Mamin" <M.Mamin@intershop.de> writes:
> following query is wrong in my understanding,  as it doesn't specify
> which oid to use (pg_proc or pg_roles ?)
>  but  it is accepted by the parser

> select pg_get_functiondef(oid)   -- should be
> pg_get_functiondef(pg_proc.oid)
>  from pg_proc join pg_roles
>      on ( pg_proc.proowner=pg_roles.oid )
>  LIMIT 1

The reason that happens is that pg_roles has an explicit oid column (ie,
oid is a regular not system column in the view) so that name takes
precedence over the system column available from pg_proc.  Having to
have an explicit oid column in pg_roles isn't very nice, because of
precisely this type of inconsistency, but since it's a view not a table
there's not a lot of alternatives.

            regards, tom lane