From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Virendra Kumar <viru_7683(at)yahoo(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: File Foreign Table Doesn't Exist when in Exception |
Date: | 2020-04-16 23:07:22 |
Message-ID: | 4f3ffbc8-a713-5de1-f390-cbc059dff5e2@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Tim Cross | 2020-04-16 23:21:11 | Re: Using unlogged tables for web sessions |
Previous Message | Jeremy Schneider | 2020-04-16 22:47:34 | Re: Using of --data-checksums |