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" 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
