File Foreign Table Doesn't Exist when in Exception

On 4/16/20 3:39 PM, Virendra Kumar wrote:

Hello Everyone,

I have a weird situation with file_fdw extension when I am creating a
foreign table in anonymous block. Here is setup:

Create extension and server:
======================
postgres=# create extension file_fdw;
CREATE EXTENSION
postgres=# CREATE SERVER log_server FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER

Here is anonymous block, when I query the foreign table (FT) created in
block with incorrect data. I get error and the FT is lost. See below:
====================
postgres=# DO $$
postgres$# DECLARE
postgres$# v_ft_file_name text;
postgres$# temp_variable text;
postgres$# v_sql text;
postgres$# log_min_time date;
postgres$# BEGIN
postgres$#
postgres$# v_ft_file_name:='abc.csv';
postgres$#
postgres$# v_sql:= 'CREATE FOREIGN TABLE "'||v_ft_file_name||'"(
postgres$#                   user_name                     text,
postgres$#                   database_name                 text,
postgres$#                   connection_from               text
postgres$#                 ) SERVER log_server
postgres$#                 OPTIONS (filename
''/opt/postgres/122/data/'||v_ft_file_name||''')';
postgres$# execute v_sql;
postgres$# v_sql:='select min(user_name) from "'||v_ft_file_name||'"';
postgres$# execute v_sql into log_min_time; <-- Querying from FT with
incorrect data
postgres$#
postgres$# END;
postgres$# $$ LANGUAGE 'plpgsql';
ERROR:  missing data for column "database_name"
CONTEXT:  COPY abc.csv, line 1: "aa,bb,cc"
SQL statement "select min(user_name) from "abc.csv""
PL/pgSQL function inline_code_block line 19 at EXECUTE

postgres=#
postgres=# select ftrelid::regclass::text from pg_foreign_table
postgres-# where ftrelid::regclass::text like '%abc.csv%';
 ftrelid
---------
(0 rows)

When I don't query the FT I can see the foreign table:
=================
postgres=# DO $$
postgres$# DECLARE
postgres$# v_ft_file_name text;
postgres$# temp_variable text;
postgres$# v_sql text;
postgres$# log_min_time date;
postgres$# BEGIN
postgres$#
postgres$# v_ft_file_name:='abc.csv';
postgres$#
postgres$# v_sql:= 'CREATE FOREIGN TABLE "'||v_ft_file_name||'"(
postgres$#                   user_name                     text,
postgres$#                   database_name                 text,
postgres$#                   connection_from               text
postgres$#                 ) SERVER log_server
postgres$#                 OPTIONS (filename
''/opt/postgres/122/data/'||v_ft_file_name||''')';
postgres$# execute v_sql;
postgres$#
postgres$# v_sql:='select min(user_name) from "'||v_ft_file_name||'"';
postgres$# --execute v_sql into log_min_time; <-Commented SELECT on FT
postgres$#
postgres$# END;
postgres$# $$ LANGUAGE 'plpgsql';
DO
postgres=#
postgres=#
postgres=# select ftrelid::regclass::text from pg_foreign_table
postgres-# where ftrelid::regclass::text like '%abc.csv%';
  ftrelid
-----------
 "abc.csv"
(1 row)

postgres=#

When I query the table outside anonymous block it is still there. So I
am thinking may be I am missing some concept here or hitting a bug:
====================
postgres=# select min(user_name) from "abc.csv";
ERROR:  missing data for column "database_name"

To me it looks like your CSV data is either missing the column/data for
the column database_name or the data is malformed.

CONTEXT:  COPY abc.csv, line 1: "aa,bb,cc"
postgres=#
postgres=#
postgres=# select ftrelid::regclass::text from
pg_foreign_table
where ftrelid::regclass::text like '%abc.csv%';
  ftrelid
-----------
 "abc.csv"
(1 row)

Regards,
Virendra Kumar

--
Adrian Klaver
adrian.klaver@aklaver.com

On 4/16/20 3:59 PM, Virendra Kumar wrote:
Please reply to list also.
Ccing list.

Thank you Adrian!

I know the data is malformed I am more concerned about the behavior that
the foreign table itself doesn't exists when it has malformed data and
is being queried in anonymous block.

https://www.postgresql.org/docs/12/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

"By default, any error occurring in a PL/pgSQL function aborts execution
of the function, and indeed of the surrounding transaction as well. You
can trap errors and recover from them by using a BEGIN block with an
EXCEPTION clause. The syntax is an extension of the normal syntax for a
BEGIN block: ..."

Regards,
Virendra

On Thursday, April 16, 2020, 3:47:08 PM PDT, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:

On 4/16/20 3:39 PM, Virendra Kumar wrote:

Hello Everyone,

I have a weird situation with file_fdw extension when I am creating a
foreign table in anonymous block. Here is setup:

Create extension and server:
======================
postgres=# create extension file_fdw;
CREATE EXTENSION
postgres=# CREATE SERVER log_server FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER

Here is anonymous block, when I query the foreign table (FT) created in
block with incorrect data. I get error and the FT is lost. See below:
====================
postgres=# DO $$
postgres$# DECLARE
postgres$# v_ft_file_name text;
postgres$# temp_variable text;
postgres$# v_sql text;
postgres$# log_min_time date;
postgres$# BEGIN
postgres$#
postgres$# v_ft_file_name:='abc.csv';
postgres$#
postgres$# v_sql:= 'CREATE FOREIGN TABLE "'||v_ft_file_name||'"(
postgres$#                   user_name                     text,
postgres$#                   database_name                 text,
postgres$#                   connection_from               text
postgres$#                 ) SERVER log_server
postgres$#                 OPTIONS (filename
''/opt/postgres/122/data/'||v_ft_file_name||''')';
postgres$# execute v_sql;
postgres$# v_sql:='select min(user_name) from "'||v_ft_file_name||'"';
postgres$# execute v_sql into log_min_time; <-- Querying from FT with
incorrect data
postgres$#
postgres$# END;
postgres$# $$ LANGUAGE 'plpgsql';
ERROR:  missing data for column "database_name"
CONTEXT:  COPY abc.csv, line 1: "aa,bb,cc"
SQL statement "select min(user_name) from "abc.csv""
PL/pgSQL function inline_code_block line 19 at EXECUTE

postgres=#
postgres=# select ftrelid::regclass::text from pg_foreign_table
postgres-# where ftrelid::regclass::text like '%abc.csv%';
   ftrelid
---------
(0 rows)

When I don't query the FT I can see the foreign table:
=================
postgres=# DO $$
postgres$# DECLARE
postgres$# v_ft_file_name text;
postgres$# temp_variable text;
postgres$# v_sql text;
postgres$# log_min_time date;
postgres$# BEGIN
postgres$#
postgres$# v_ft_file_name:='abc.csv';
postgres$#
postgres$# v_sql:= 'CREATE FOREIGN TABLE "'||v_ft_file_name||'"(
postgres$#                   user_name                     text,
postgres$#                   database_name                 text,
postgres$#                   connection_from               text
postgres$#                 ) SERVER log_server
postgres$#                 OPTIONS (filename
''/opt/postgres/122/data/'||v_ft_file_name||''')';
postgres$# execute v_sql;
postgres$#
postgres$# v_sql:='select min(user_name) from "'||v_ft_file_name||'"';
postgres$# --execute v_sql into log_min_time; <-Commented SELECT on FT
postgres$#
postgres$# END;
postgres$# $$ LANGUAGE 'plpgsql';
DO
postgres=#
postgres=#
postgres=# select ftrelid::regclass::text from pg_foreign_table
postgres-# where ftrelid::regclass::text like '%abc.csv%';
    ftrelid
-----------
   "abc.csv"
(1 row)

postgres=#

When I query the table outside anonymous block it is still there. So I
am thinking may be I am missing some concept here or hitting a bug:
====================
postgres=# select min(user_name) from "abc.csv";
ERROR:  missing data for column "database_name"

To me it looks like your CSV data is either missing the column/data for
the column database_name or the data is malformed.

CONTEXT:  COPY abc.csv, line 1: "aa,bb,cc"
postgres=#
postgres=#
postgres=# select ftrelid::regclass::text from
pg_foreign_table
where ftrelid::regclass::text like '%abc.csv%';
    ftrelid
-----------
   "abc.csv"
(1 row)

Regards,
Virendra Kumar

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com