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

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