Обсуждение: BUG #15998: query to return a table column list gives error on a missing foreign data wrapper library

The following bug has been logged on the website:

Bug reference:      15998
Logged by:          reiner peterke
Email address:      zedaardv@drizzle.com
PostgreSQL version: 11.5
Operating system:   Centos  7.6.1810 x86
Description:

This is a very corner case situation.
Found originally with the oracle_fdw but can reproduce with the
postgres_fdw.
Happened on at a customers site when we changed the binaries from postgres
from community version to another version.
database server was started without the foreign data wrapper having been
installed (tested with oracle_fdw and postgres_fdw)
a query was run from a GUI tool to get the column list for a non-foreign
table.  when the query was run the error was thrown
ERROR:  could not access file "$libdir/oracle_fdw": No such file or
directory

Have been able to reproduce the behavior on postgres 10 and 11 with the
postgres_fdw on centos 7 on ppc64le. 
Take a postgres database with foreign servers and foreign tables.
remove the fdw library.
i simulated this by
mv postgres_fdw.so postgres_fdw.so.xx

run the query below on a normal table
the query needs to be adjusted to to have a table oid  (version runs in
postgres 11)
SELECT
             typ.oid AS typoid, nspname, relname, attname, attrelid, attnum,
attnotnull,
             attidentity != '' AS isidentity,
             CASE WHEN typ.typtype = 'd' THEN typ.typtypmod ELSE atttypmod
END AS typmod,
             CASE WHEN atthasdef THEN (SELECT pg_get_expr(adbin, cls.oid)
FROM pg_attrdef WHERE adrelid = cls.oid AND adnum = attr.attnum) ELSE NULL
END AS default,
             CASE WHEN col.is_updatable = 'YES' THEN true ELSE false END AS
is_updatable,
             EXISTS (
               SELECT * FROM pg_index
               WHERE pg_index.indrelid = cls.oid AND
                     pg_index.indisprimary AND
                     attnum = ANY (indkey)
             ) AS isprimarykey,
             EXISTS (
               SELECT * FROM pg_index
               WHERE pg_index.indrelid = cls.oid AND
                     pg_index.indisunique AND
                     pg_index.indnkeyatts = 1 AND
                     attnum = pg_index.indkey[0]
             ) AS isunique
        FROM pg_attribute AS attr
        JOIN pg_type AS typ ON attr.atttypid = typ.oid
          JOIN pg_class AS cls ON cls.oid = attr.attrelid
        JOIN pg_namespace AS ns ON ns.oid = cls.relnamespace
        LEFT OUTER JOIN information_schema.columns AS col ON
col.table_schema = nspname AND
             col.table_name = relname AND
             col.column_name = attname
        WHERE
             atttypid <> 0 AND
             relkind IN ('r', 'v', 'm') AND
             NOT attisdropped AND
             nspname NOT IN ('pg_catalog', 'information_schema') AND
             attnum > 0 AND
             ((attr.attrelid=1354075 AND attr.attnum=1))
ORDER BY attnum

throws error
ERROR:  could not access file "$libdir/postgres_fdw": No such file or
directory
commenting out the case statement 
 CASE WHEN col.is_updatable = 'YES' THEN true ELSE false END AS
is_updatable,

causes the query to run without error.

It seems odd to me that this query generates such an error
running a query with just the case statement works fine.
select case when col.is_updatable = 'YES' then true else false end as
is_updatable from information_schema.columns col where table_name =
'batch';
I am not sure how to dig further into this at the moment

On Tue, Sep 10, 2019 at 6:46 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
> Have been able to reproduce the behavior on postgres 10 and 11 with the
> postgres_fdw on centos 7 on ppc64le.
> Take a postgres database with foreign servers and foreign tables.
> remove the fdw library.
> i simulated this by
> mv postgres_fdw.so postgres_fdw.so.xx
>
> run the query below on a normal table
> the query needs to be adjusted to to have a table oid  (version runs in
> postgres 11)
> SELECT
>              typ.oid AS typoid, nspname, relname, attname, attrelid, attnum,
> attnotnull,
>              attidentity != '' AS isidentity,
>              CASE WHEN typ.typtype = 'd' THEN typ.typtypmod ELSE atttypmod
> END AS typmod,
>              CASE WHEN atthasdef THEN (SELECT pg_get_expr(adbin, cls.oid)
> FROM pg_attrdef WHERE adrelid = cls.oid AND adnum = attr.attnum) ELSE NULL
> END AS default,
>              CASE WHEN col.is_updatable = 'YES' THEN true ELSE false END AS
> is_updatable,
>              EXISTS (
>                SELECT * FROM pg_index
>                WHERE pg_index.indrelid = cls.oid AND
>                      pg_index.indisprimary AND
>                      attnum = ANY (indkey)
>              ) AS isprimarykey,
>              EXISTS (
>                SELECT * FROM pg_index
>                WHERE pg_index.indrelid = cls.oid AND
>                      pg_index.indisunique AND
>                      pg_index.indnkeyatts = 1 AND
>                      attnum = pg_index.indkey[0]
>              ) AS isunique
>         FROM pg_attribute AS attr
>         JOIN pg_type AS typ ON attr.atttypid = typ.oid
>           JOIN pg_class AS cls ON cls.oid = attr.attrelid
>         JOIN pg_namespace AS ns ON ns.oid = cls.relnamespace
>         LEFT OUTER JOIN information_schema.columns AS col ON
> col.table_schema = nspname AND
>              col.table_name = relname AND
>              col.column_name = attname
>         WHERE
>              atttypid <> 0 AND
>              relkind IN ('r', 'v', 'm') AND
>              NOT attisdropped AND
>              nspname NOT IN ('pg_catalog', 'information_schema') AND
>              attnum > 0 AND
>              ((attr.attrelid=1354075 AND attr.attnum=1))
> ORDER BY attnum
>
> throws error
> ERROR:  could not access file "$libdir/postgres_fdw": No such file or
> directory

I'm now traveling, so I cannot try to reproduce this.  Could you send
the EXPLAIN output for this query?

> commenting out the case statement
>  CASE WHEN col.is_updatable = 'YES' THEN true ELSE false END AS
> is_updatable,
>
> causes the query to run without error.

Seems reasonable to me; the is_updatable reference would cause the
IsForeignRelUpdatable() call for postgres_fdw foreign tables IIUC, so
removing that would be a workaround for this issue if that works for
you.

Best regards,
Etsuro Fujita

Etsuro Fujita <etsuro.fujita@gmail.com> writes:
> On Tue, Sep 10, 2019 at 6:46 PM PG Bug reporting form
> <noreply@postgresql.org> wrote:
>> Take a postgres database with foreign servers and foreign tables.
>> remove the fdw library.
>> [ Then a query on information_schema.columns ]
>> throws error
>> ERROR:  could not access file "$libdir/postgres_fdw": No such file or
>> directory
>>
>> commenting out the case statement
>> CASE WHEN col.is_updatable = 'YES' THEN true ELSE false END AS
>> is_updatable,
>> causes the query to run without error.

> Seems reasonable to me; the is_updatable reference would cause the
> IsForeignRelUpdatable() call for postgres_fdw foreign tables IIUC, so
> removing that would be a workaround for this issue if that works for
> you.

Yeah.  Seems like not-a-bug to me: if you inquire about the updatability
of a foreign table, we really can't answer that without consulting
the relevant FDW.

            regards, tom lane