Re: File Foreign Table Doesn't Exist when in Exception - Mailing list pgsql-general

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