Обсуждение: File Foreign Table Doesn't Exist when in Exception
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
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$# 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$# DECLAREpostgres$# 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)
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
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