Re: POC PATCH: copy from ... exceptions to: (was Re: VLDB Features) - Mailing list pgsql-hackers

Hi!

Thank you, Damir, for your patch. It is very interesting to review it!

It seemed to me that the names of variables are not the same everywhere.

I noticed that you used ignore_datatype_errors_specified variable in copy.c , but guc has a short name ignore_datatype_errors. Also you used the short variable name in CopyFormatOptions structure.
Name used ignore_datatype_errors_specified is seemed very long to me, may be use a short version of it (ignore_datatype_errors) in copy.c too?

Besides, I noticed that you used ignored_errors variable in CopyFromStateData structure and it's name is strikingly similar to name (ignore_datatype_errors), but they have different meanings.
Maybe it will be better to rename it as ignored_errors_counter?

I tested last version v5-0001-Add-new-COPY-option-IGNORE_DATATYPE_ERRORS.patch with bytea data type and transaction cases. Eventually, I didn't find any problem there.
I described my steps in more detail, if I missed something.

First of all, I ran copy function with IGNORE_DATATYPE_ERRORS parameter being in transaction block.

File t2.csv exists:

id,b
769,\
1,\6e
2,\x5
5,\x
Test:
CREATE TABLE t (id INT , b  BYTEA) ;
postgres=# BEGIN;
copy t FROM '/home/alena/postgres/t2.csv'  WITH (format 'csv', IGNORE_DATATYPE_ERRORS, delimiter ',', HEADER);
SAVEPOINT my_savepoint;
BEGIN
WARNING:  invalid input syntax for type bytea
WARNING:  invalid input syntax for type bytea
WARNING:  invalid hexadecimal data: odd number of digits
WARNING:  3 rows were skipped due to data type incompatibility
COPY 1
SAVEPOINT
postgres=*# copy t FROM '/home/alena/postgres/t2.csv'  WITH (format 'csv', IGNORE_DATATYPE_ERRORS, delimiter ',', HEADER);
WARNING:  invalid input syntax for type bytea
WARNING:  invalid input syntax for type bytea
WARNING:  invalid hexadecimal data: odd number of digits
WARNING:  3 rows were skipped due to data type incompatibility
COPY 1
postgres=*# ROLLBACK TO my_savepoint;
ROLLBACK
postgres=*# select * from t;
 id | b  
----+----
  5 | \x
(1 row)

postgres=*# copy t FROM '/home/alena/postgres/t2.csv'  WITH (format 'csv', IGNORE_DATATYPE_ERRORS, delimiter ',', HEADER);
WARNING:  invalid input syntax for type bytea
WARNING:  invalid input syntax for type bytea
WARNING:  invalid hexadecimal data: odd number of digits
WARNING:  3 rows were skipped due to data type incompatibility
COPY 1
postgres=*# select * from t;
 id | b  
----+----
  5 | \x
  5 | \x
(2 rows)

postgres=*# commit;
COMMIT

I tried to use the similar test and moved transaction block in function:
CREATE FUNCTION public.log2()
 RETURNS void
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
BEGIN;
copy t FROM '/home/alena/postgres/t2.csv'  WITH (format 'csv', IGNORE_DATATYPE_ERRORS, delimiter ',', HEADER);
SAVEPOINT my_savepoint;
END;
$function$;

postgres=# delete from t;

postgres=# select 1 as t from log2();
WARNING:  invalid input syntax for type bytea
WARNING:  invalid input syntax for type bytea
WARNING:  invalid hexadecimal data: odd number of digits
WARNING:  3 rows were skipped due to data type incompatibility
 t
---
 1
(1 row)

Secondly I checked function copy with bytea datatype.
t1.csv consists:
id,b
769,\x2d
1,\x6e
2,\x5c
5,\x

And I ran it:postgres=# delete from t;
DELETE 4
postgres=# copy t FROM '/home/alena/postgres/t2.csv'  WITH (format 'csv', IGNORE_DATATYPE_ERRORS, delimiter ',', HEADER);
WARNING:  invalid input syntax for type bytea
WARNING:  invalid input syntax for type bytea
WARNING:  invalid hexadecimal data: odd number of digits
WARNING:  3 rows were skipped due to data type incompatibility
COPY 1
postgres=# select * from t;
 id | b  
----+----
  5 | \x
(1 row)

-- 
--- 
Alena Rybakina
Postgres Professional