File Foreign Table Doesn't Exist when in Exception
| От | Virendra Kumar |
|---|---|
| Тема | File Foreign Table Doesn't Exist when in Exception |
| Дата | |
| Msg-id | 642480622.1859025.1587076788135@mail.yahoo.com обсуждение исходный текст |
| Ответы |
Re: File Foreign Table Doesn't Exist when in Exception
|
| Список | pgsql-general |
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
В списке pgsql-general по дате отправления: