Re: [HACKERS] Review: Patch FORCE_NULL option for copy COPY in CSV mode
On Wed, Mar 5, 2014 at 09:49:30PM +0900, Michael Paquier wrote: On Wed, Mar 5, 2014 at 7:44 AM, Andrew Dunstan and...@dunslane.net wrote: I have picked this up and committed the patch. Thanks to all. Sorry for coming after the battle, but while looking at what has been committed I noticed that copy2.sql is actually doing twice in a row the same test: COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c)); 1,, \. -- should succeed with no effect (b remains an empty string, c remains NULL) COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c)); 2,, \. See? For both tests the quotes are placed on the same column, the 3rd. I think that one of them should be like that, with the quotes on the 2nd column = 2,, The attached patch corrects that... and a misplaced comment. Regards, Thanks. Patch applied. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Patch FORCE_NULL option for copy COPY in CSV mode
On Fri, Mar 7, 2014 at 05:08:54PM +0900, Michael Paquier wrote: On Thu, Mar 6, 2014 at 12:09 AM, Tom Lane t...@sss.pgh.pa.us wrote: Andrew Dunstan and...@dunslane.net writes: On 03/05/2014 09:11 AM, Michael Paquier wrote: After testing this feature, I noticed that FORCE_NULL and FORCE_NOT_NULL can both be specified with COPY on the same column. Strictly they are not actually contradictory, since FORCE NULL relates to quoted null strings and FORCE NOT NULL relates to unquoted null strings. Arguably the docs are slightly loose on this point. Still, applying both FORCE NULL and FORCE NOT NULL to the same column would be rather perverse, since it would result in a quoted null string becoming null and an unquoted null string becoming not null. Given the remarkable lack of standardization of CSV output, who's to say that there might not be data sources out there for which this is the desired behavior? It's weird, I agree, but I think throwing an error for the combination is not going to be helpful. It's not like somebody might accidentally write both on the same column. +1 for clarifying the docs, though, more or less in the words you used above. Following that, I have hacked the patch attached to update the docs with an additional regression test (actually replaces a test that was the same as the one before in copy2). I am attaching as well a second patch for file_fdw, to allow the use of force_null and force_not_null on the same column, to be consistent with COPY. Regards, Correction, this is the patch applied, not the earlier version. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Patch FORCE_NULL option for copy COPY in CSV mode
On Wed, Apr 23, 2014 at 5:07 AM, Bruce Momjian br...@momjian.us wrote: On Fri, Mar 7, 2014 at 05:08:54PM +0900, Michael Paquier wrote: On Thu, Mar 6, 2014 at 12:09 AM, Tom Lane t...@sss.pgh.pa.us wrote: Andrew Dunstan and...@dunslane.net writes: On 03/05/2014 09:11 AM, Michael Paquier wrote: After testing this feature, I noticed that FORCE_NULL and FORCE_NOT_NULL can both be specified with COPY on the same column. Strictly they are not actually contradictory, since FORCE NULL relates to quoted null strings and FORCE NOT NULL relates to unquoted null strings. Arguably the docs are slightly loose on this point. Still, applying both FORCE NULL and FORCE NOT NULL to the same column would be rather perverse, since it would result in a quoted null string becoming null and an unquoted null string becoming not null. Given the remarkable lack of standardization of CSV output, who's to say that there might not be data sources out there for which this is the desired behavior? It's weird, I agree, but I think throwing an error for the combination is not going to be helpful. It's not like somebody might accidentally write both on the same column. +1 for clarifying the docs, though, more or less in the words you used above. Following that, I have hacked the patch attached to update the docs with an additional regression test (actually replaces a test that was the same as the one before in copy2). I am attaching as well a second patch for file_fdw, to allow the use of force_null and force_not_null on the same column, to be consistent with COPY. Regards, Correction, this is the patch applied, not the earlier version. Thanks for taking the time to look at that. -- Michael
Re: [HACKERS] Review: Patch FORCE_NULL option for copy COPY in CSV mode
On Thu, Mar 6, 2014 at 12:09 AM, Tom Lane t...@sss.pgh.pa.us wrote: Andrew Dunstan and...@dunslane.net writes: On 03/05/2014 09:11 AM, Michael Paquier wrote: After testing this feature, I noticed that FORCE_NULL and FORCE_NOT_NULL can both be specified with COPY on the same column. Strictly they are not actually contradictory, since FORCE NULL relates to quoted null strings and FORCE NOT NULL relates to unquoted null strings. Arguably the docs are slightly loose on this point. Still, applying both FORCE NULL and FORCE NOT NULL to the same column would be rather perverse, since it would result in a quoted null string becoming null and an unquoted null string becoming not null. Given the remarkable lack of standardization of CSV output, who's to say that there might not be data sources out there for which this is the desired behavior? It's weird, I agree, but I think throwing an error for the combination is not going to be helpful. It's not like somebody might accidentally write both on the same column. +1 for clarifying the docs, though, more or less in the words you used above. Following that, I have hacked the patch attached to update the docs with an additional regression test (actually replaces a test that was the same as the one before in copy2). I am attaching as well a second patch for file_fdw, to allow the use of force_null and force_not_null on the same column, to be consistent with COPY. Regards, -- Michael diff --git a/contrib/file_fdw/file_fdw.c b/contrib/file_fdw/file_fdw.c index 7fb1dbc..97a35d0 100644 --- a/contrib/file_fdw/file_fdw.c +++ b/contrib/file_fdw/file_fdw.c @@ -267,11 +267,6 @@ file_fdw_validator(PG_FUNCTION_ARGS) (errcode(ERRCODE_SYNTAX_ERROR), errmsg(conflicting or redundant options), errhint(option \force_not_null\ supplied more than once for a column))); - if(force_null) -ereport(ERROR, - (errcode(ERRCODE_SYNTAX_ERROR), - errmsg(conflicting or redundant options), - errhint(option \force_not_null\ cannot be used together with \force_null\))); force_not_null = def; /* Don't care what the value is, as long as it's a legal boolean */ (void) defGetBoolean(def); @@ -284,11 +279,6 @@ file_fdw_validator(PG_FUNCTION_ARGS) (errcode(ERRCODE_SYNTAX_ERROR), errmsg(conflicting or redundant options), errhint(option \force_null\ supplied more than once for a column))); - if(force_not_null) -ereport(ERROR, - (errcode(ERRCODE_SYNTAX_ERROR), - errmsg(conflicting or redundant options), - errhint(option \force_null\ cannot be used together with \force_not_null\))); force_null = def; (void) defGetBoolean(def); } diff --git a/contrib/file_fdw/input/file_fdw.source b/contrib/file_fdw/input/file_fdw.source index 0c278aa..b608372 100644 --- a/contrib/file_fdw/input/file_fdw.source +++ b/contrib/file_fdw/input/file_fdw.source @@ -91,24 +91,22 @@ ALTER FOREIGN TABLE text_csv OPTIONS (SET format 'csv'); \pset null _null_ SELECT * FROM text_csv; +-- force_not_null and force_null can be used together on the same column +ALTER FOREIGN TABLE text_csv ALTER COLUMN word1 OPTIONS (force_null 'true'); +ALTER FOREIGN TABLE text_csv ALTER COLUMN word3 OPTIONS (force_not_null 'true'); + -- force_not_null is not allowed to be specified at any foreign object level: ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_not_null '*'); -- ERROR ALTER SERVER file_server OPTIONS (ADD force_not_null '*'); -- ERROR CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_not_null '*'); -- ERROR CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_not_null '*'); -- ERROR --- force_not_null cannot be specified together with force_null -ALTER FOREIGN TABLE text_csv ALTER COLUMN word1 OPTIONS (force_null 'true'); --ERROR - -- force_null is not allowed to be specified at any foreign object level: ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_null '*'); -- ERROR ALTER SERVER file_server OPTIONS (ADD force_null '*'); -- ERROR CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_null '*'); -- ERROR CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_null '*'); -- ERROR --- force_null cannot be specified together with force_not_null -ALTER FOREIGN TABLE text_csv ALTER COLUMN word3 OPTIONS (force_not_null 'true'); --ERROR - -- basic query tests SELECT * FROM agg_text WHERE b 10.0 ORDER BY a; SELECT * FROM agg_csv ORDER BY a; diff --git a/contrib/file_fdw/output/file_fdw.source b/contrib/file_fdw/output/file_fdw.source index 2bec160..bc183b8 100644 --- a/contrib/file_fdw/output/file_fdw.source +++ b/contrib/file_fdw/output/file_fdw.source @@ -115,6 +115,9 @@ SELECT * FROM text_csv; ABC | abc|| (5 rows) +-- force_not_null and force_null can be used together on the same column +ALTER FOREIGN TABLE text_csv ALTER COLUMN word1 OPTIONS (force_null 'true'); +ALTER FOREIGN TABLE text_csv ALTER COLUMN word3 OPTIONS
Re: [HACKERS] Review: Patch FORCE_NULL option for copy COPY in CSV mode
On Wed, Mar 5, 2014 at 7:44 AM, Andrew Dunstan and...@dunslane.net wrote: I have picked this up and committed the patch. Thanks to all. Sorry for coming after the battle, but while looking at what has been committed I noticed that copy2.sql is actually doing twice in a row the same test: COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c)); 1,, \. -- should succeed with no effect (b remains an empty string, c remains NULL) COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c)); 2,, \. See? For both tests the quotes are placed on the same column, the 3rd. I think that one of them should be like that, with the quotes on the 2nd column = 2,, The attached patch corrects that... and a misplaced comment. Regards, -- Michael diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out index 76dea28..e8fb3d1 100644 --- a/src/test/regress/expected/copy2.out +++ b/src/test/regress/expected/copy2.out @@ -383,7 +383,6 @@ SELECT * FROM vistest; (2 rows) -- Test FORCE_NOT_NULL and FORCE_NULL options --- should succeed with b set to an empty string and c set to NULL CREATE TEMP TABLE forcetest ( a INT NOT NULL, b TEXT NOT NULL, @@ -392,6 +391,7 @@ CREATE TEMP TABLE forcetest ( e TEXT ); \pset null NULL +-- should succeed with b set to an empty string and c set to NULL BEGIN; COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c)); COMMIT; diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql index e2be21f..63332bd 100644 --- a/src/test/regress/sql/copy2.sql +++ b/src/test/regress/sql/copy2.sql @@ -271,7 +271,6 @@ SELECT * FROM vistest; COMMIT; SELECT * FROM vistest; -- Test FORCE_NOT_NULL and FORCE_NULL options --- should succeed with b set to an empty string and c set to NULL CREATE TEMP TABLE forcetest ( a INT NOT NULL, b TEXT NOT NULL, @@ -280,6 +279,7 @@ CREATE TEMP TABLE forcetest ( e TEXT ); \pset null NULL +-- should succeed with b set to an empty string and c set to NULL BEGIN; COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c)); 1,, @@ -289,7 +289,7 @@ SELECT b, c FROM forcetest WHERE a = 1; -- should succeed with no effect (b remains an empty string, c remains NULL) BEGIN; COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c)); -2,, +2,, \. COMMIT; SELECT b, c FROM forcetest WHERE a = 2; -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Patch FORCE_NULL option for copy COPY in CSV mode
After testing this feature, I noticed that FORCE_NULL and
FORCE_NOT_NULL can both be specified with COPY on the same column.
This does not seem correct. The attached patch adds some more error
handling, and a regression test case for that.
Regards,
--
Michael
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 70ee7e5..540da91 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -1457,6 +1457,26 @@ BeginCopy(bool is_from,
}
}
+ /*
+* Check if both force_null and force_not_null are used on the same
+* columns.
+*/
+ if (cstate-force_null cstate-force_notnull)
+ {
+ int i;
+
+ for (i = 0; i num_phys_attrs; i++)
+ {
+ if (cstate-force_notnull_flags[i]
+ cstate-force_null_flags[i])
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+errmsg(conflicting or
redundant options),
+errhint(\force_not_null\
and \force_null\ specified for the same column \%s\,
+
NameStr(tupDesc-attrs[i]-attname;
+ }
+ }
+
/* Use client encoding when ENCODING option is not specified. */
if (cstate-file_encoding 0)
cstate-file_encoding = pg_get_client_encoding();
diff --git a/src/test/regress/expected/copy2.out
b/src/test/regress/expected/copy2.out
index 76dea28..5341b09 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -418,6 +418,12 @@ ERROR: null value in column b violates not-null
constraint
DETAIL: Failing row contains (3, null, , null, null).
CONTEXT: COPY forcetest, line 1: 3,,
ROLLBACK;
+-- FORCE_NULL and FORCE_NOT_NULL cannot be used on the same column
+BEGIN;
+COPY forcetest (a) FROM STDIN WITH (FORMAT csv, FORCE_NULL(a),
FORCE_NOT_NULL(a));
+ERROR: conflicting or redundant options
+HINT: force_not_null and force_null specified for the same column a
+ROLLBACK;
-- should fail with not referenced by COPY error
BEGIN;
COPY forcetest (d, e) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b));
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index e2be21f..91dc902 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -299,6 +299,10 @@ COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv,
FORCE_NULL(b), FORCE_NOT_N
3,,
\.
ROLLBACK;
+-- FORCE_NULL and FORCE_NOT_NULL cannot be used on the same column
+BEGIN;
+COPY forcetest (a) FROM STDIN WITH (FORMAT csv, FORCE_NULL(a),
FORCE_NOT_NULL(a));
+ROLLBACK;
-- should fail with not referenced by COPY error
BEGIN;
COPY forcetest (d, e) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b));
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Patch FORCE_NULL option for copy COPY in CSV mode
On 03/05/2014 09:11 AM, Michael Paquier wrote: After testing this feature, I noticed that FORCE_NULL and FORCE_NOT_NULL can both be specified with COPY on the same column. This does not seem correct. The attached patch adds some more error handling, and a regression test case for that. Strictly they are not actually contradictory, since FORCE NULL relates to quoted null strings and FORCE NOT NULL relates to unquoted null strings. Arguably the docs are slightly loose on this point. Still, applying both FORCE NULL and FORCE NOT NULL to the same column would be rather perverse, since it would result in a quoted null string becoming null and an unquoted null string becoming not null. I'd be more inclined just to tighten the docs and maybe expand the regression tests a bit, but I could be persuaded the other way if people think it's worth it. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Patch FORCE_NULL option for copy COPY in CSV mode
2014-03-05 23:27 GMT+09:00 Andrew Dunstan and...@dunslane.net: On 03/05/2014 09:11 AM, Michael Paquier wrote: After testing this feature, I noticed that FORCE_NULL and FORCE_NOT_NULL can both be specified with COPY on the same column. This does not seem correct. The attached patch adds some more error handling, and a regression test case for that. Strictly they are not actually contradictory, since FORCE NULL relates to quoted null strings and FORCE NOT NULL relates to unquoted null strings. Arguably the docs are slightly loose on this point. Still, applying both FORCE NULL and FORCE NOT NULL to the same column would be rather perverse, since it would result in a quoted null string becoming null and an unquoted null string becoming not null. Too frazzled to recall clearly right now, but I think that was the somewhat counterintuitive conclusion I originally came to. I'd be more inclined just to tighten the docs and maybe expand the regression tests a bit, but I could be persuaded the other way if people think it's worth it. Might be worth doing if it's an essentially useless feature, if only to preempt an unending chain of bug reports. Many thanks for everyone's input on this, and apologies for not giving the patch enough rigorous attention. Regards Ian Barwick -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Patch FORCE_NULL option for copy COPY in CSV mode
On Wed, Mar 5, 2014 at 11:37 PM, Ian Lawrence Barwick barw...@gmail.com wrote: 2014-03-05 23:27 GMT+09:00 Andrew Dunstan and...@dunslane.net: On 03/05/2014 09:11 AM, Michael Paquier wrote: After testing this feature, I noticed that FORCE_NULL and FORCE_NOT_NULL can both be specified with COPY on the same column. This does not seem correct. The attached patch adds some more error handling, and a regression test case for that. Strictly they are not actually contradictory, since FORCE NULL relates to quoted null strings and FORCE NOT NULL relates to unquoted null strings. Arguably the docs are slightly loose on this point. Still, applying both FORCE NULL and FORCE NOT NULL to the same column would be rather perverse, since it would result in a quoted null string becoming null and an unquoted null string becoming not null. Too frazzled to recall clearly right now, but I think that was the somewhat counterintuitive conclusion I originally came to. In this case I may be an intuitive guy :), but OK I see your point. So if we specify both this produces the exact opposite as the default, default being an empty string inserted for a quoted empty string and NULL inserted for a non-quoted empty string. So yes I'm fine with a note on the docs about that, and some more regression tests. Btw, if we allow this behavior in COPY, why doesn't file_fdw allow both options to be allowed on the same column for a foreign table? Current behavior of file_fdw seems rather inconsistent with COPY as it stands now. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Patch FORCE_NULL option for copy COPY in CSV mode
On Wed, Mar 5, 2014 at 11:58 PM, Michael Paquier michael.paqu...@gmail.com wrote: So if we specify both this produces the exact opposite of the default, default being an empty string inserted for a quoted empty string and NULL inserted for a non-quoted empty string. So yes I'm fine with a note on the docs about that, and some more regression tests. For people who did not get this one, here is a short example: =# ¥pset null 'null' Null display (null) is null. =# create table aa (a text); CREATE TABLE =# COPY aa FROM STDIN WITH (FORMAT csv); Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. \. =# select * from aa; a -- null (2 rows) =# truncate aa; TRUNCATE TABLE Time: 12.149 ms =# COPY aa FROM STDIN WITH (FORMAT csv, FORCE_NULL(a), FORCE_NOT_NULL(a)); Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. \. Time: 3776.662 ms =# select * from aa; a -- null (2 rows) -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Patch FORCE_NULL option for copy COPY in CSV mode
Andrew Dunstan and...@dunslane.net writes: On 03/05/2014 09:11 AM, Michael Paquier wrote: After testing this feature, I noticed that FORCE_NULL and FORCE_NOT_NULL can both be specified with COPY on the same column. Strictly they are not actually contradictory, since FORCE NULL relates to quoted null strings and FORCE NOT NULL relates to unquoted null strings. Arguably the docs are slightly loose on this point. Still, applying both FORCE NULL and FORCE NOT NULL to the same column would be rather perverse, since it would result in a quoted null string becoming null and an unquoted null string becoming not null. Given the remarkable lack of standardization of CSV output, who's to say that there might not be data sources out there for which this is the desired behavior? It's weird, I agree, but I think throwing an error for the combination is not going to be helpful. It's not like somebody might accidentally write both on the same column. +1 for clarifying the docs, though, more or less in the words you used above. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Patch FORCE_NULL option for copy COPY in CSV mode
On 03/03/2014 06:48 AM, Michael Paquier wrote: On Mon, Mar 3, 2014 at 1:13 PM, Andrew Dunstan and...@dunslane.net wrote: That difference actually made the file_fdw regression results plain wrong, in my view, in that they expected a quoted empty string to be turned to null even when the null string was something else. I've adjusted this and the docs and propose to apply the attached patch in the next day or two unless there are any objections. Unless I'm overlooking something, output from SELECT * FROM text_csv; in 'output/file_fdw.source' still needs updating? While reading this patch, I found a small typo in copy2.[sql|out] = s/violiaton/violation/g I have picked this up and committed the patch. Thanks to all. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Patch FORCE_NULL option for copy COPY in CSV mode
On Mon, Mar 3, 2014 at 1:13 PM, Andrew Dunstan and...@dunslane.net wrote: That difference actually made the file_fdw regression results plain wrong, in my view, in that they expected a quoted empty string to be turned to null even when the null string was something else. I've adjusted this and the docs and propose to apply the attached patch in the next day or two unless there are any objections. Unless I'm overlooking something, output from SELECT * FROM text_csv; in 'output/file_fdw.source' still needs updating? While reading this patch, I found a small typo in copy2.[sql|out] = s/violiaton/violation/g Regards, -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Patch FORCE_NULL option for copy COPY in CSV mode
2014-03-02 8:26 GMT+09:00 Andrew Dunstan and...@dunslane.net:
On 01/29/2014 10:59 AM, Ian Lawrence Barwick wrote:
2014/1/29 Ian Lawrence Barwick barw...@gmail.com:
2014-01-29 Andrew Dunstan and...@dunslane.net:
On 01/28/2014 05:55 AM, Ian Lawrence Barwick wrote:
Hi Payal
Many thanks for the review, and my apologies for not getting back to
you earlier.
Updated version of the patch attached with suggested corrections.
On a very quick glance, I see that you have still not made adjustments
to
contrib/file_fdw to accommodate this new option. I don't see why this
COPY
option should be different in that respect.
Hmm, that idea seems to have escaped me completely. I'll get onto it
forthwith.
Striking while the keyboard is hot... version with contrib/file_fdw
modifications
attached.
I have reviewed this. Generally it's good, but the author has made a
significant error - the idea is not to force a quoted empty string to null,
but to force a quoted null string to null, whatever the null string might
be. The default case has these the same, but if you specify a non-empty null
string they aren't.
The author slaps himself on the forehead while regretting he was temporally
constricted when dealing with the patch and never thought to look beyond
the immediate use case.
Thanks for the update, much appreciated.
That difference actually made the file_fdw regression results plain wrong,
in my view, in that they expected a quoted empty string to be turned to null
even when the null string was something else.
I've adjusted this and the docs and propose to apply the attached patch in
the next day or two unless there are any objections.
Unless I'm overlooking something, output from SELECT * FROM text_csv;
in 'output/file_fdw.source' still needs updating?
Regards
Ian Barwick
diff --git a/contrib/file_fdw/data/text.csv b/contrib/file_fdw/data/text.csv
new file mode 100644
index ed348a9..f55d9cf
*** a/contrib/file_fdw/data/text.csv
--- b/contrib/file_fdw/data/text.csv
***
*** 1,4
! AAA,aaa
! XYZ,xyz
! NULL,NULL
! ABC,abc
--- 1,5
! AAA,aaa,123,
! XYZ,xyz,,321
! NULL,NULL,NULL,NULL
! NULL,NULL,NULL,NULL
! ABC,abc,,
diff --git a/contrib/file_fdw/file_fdw.c b/contrib/file_fdw/file_fdw.c
new file mode 100644
index 5639f4d..7fb1dbc
*** a/contrib/file_fdw/file_fdw.c
--- b/contrib/file_fdw/file_fdw.c
*** struct FileFdwOption
*** 48,56
/*
* Valid options for file_fdw.
! * These options are based on the options for COPY FROM command.
! * But note that force_not_null is handled as a boolean option attached to
! * each column, not as a table option.
*
* Note: If you are adding new option for user mapping, you need to modify
* fileGetOptions(), which currently doesn't bother to look at user mappings.
--- 48,56
/*
* Valid options for file_fdw.
! * These options are based on the options for the COPY FROM command.
! * But note that force_not_null and force_null are handled as boolean options
! * attached to a column, not as table options.
*
* Note: If you are adding new option for user mapping, you need to modify
* fileGetOptions(), which currently doesn't bother to look at user mappings.
*** static const struct FileFdwOption valid_
*** 69,75
{null, ForeignTableRelationId},
{encoding, ForeignTableRelationId},
{force_not_null, AttributeRelationId},
!
/*
* force_quote is not supported by file_fdw because it's for COPY TO.
*/
--- 69,75
{null, ForeignTableRelationId},
{encoding, ForeignTableRelationId},
{force_not_null, AttributeRelationId},
! {force_null, AttributeRelationId},
/*
* force_quote is not supported by file_fdw because it's for COPY TO.
*/
*** file_fdw_validator(PG_FUNCTION_ARGS)
*** 187,192
--- 187,193
Oid catalog = PG_GETARG_OID(1);
char *filename = NULL;
DefElem*force_not_null = NULL;
+ DefElem*force_null = NULL;
List *other_options = NIL;
ListCell *cell;
*** file_fdw_validator(PG_FUNCTION_ARGS)
*** 243,252
}
/*
! * Separate out filename and force_not_null, since ProcessCopyOptions
! * won't accept them. (force_not_null only comes in a boolean
! * per-column flavor here.)
*/
if (strcmp(def-defname, filename) == 0)
{
if (filename)
--- 244,253
}
/*
! * Separate out filename and column-specific options, since
! * ProcessCopyOptions won't accept them.
*/
+
if (strcmp(def-defname, filename) == 0)
{
if (filename)
*** file_fdw_validator(PG_FUNCTION_ARGS)
*** 255,270
errmsg(conflicting or redundant options)));
filename = defGetString(def);
}
else if (strcmp(def-defname, force_not_null) == 0)
{
if (force_not_null)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg(conflicting or redundant options)));
force_not_null =
Re: [HACKERS] Review: Patch FORCE_NULL option for copy COPY in CSV mode
On 03/02/2014 10:06 PM, Ian Lawrence Barwick wrote: 2014-03-02 8:26 GMT+09:00 Andrew Dunstan and...@dunslane.net: On 01/29/2014 10:59 AM, Ian Lawrence Barwick wrote: 2014/1/29 Ian Lawrence Barwick barw...@gmail.com: 2014-01-29 Andrew Dunstan and...@dunslane.net: On 01/28/2014 05:55 AM, Ian Lawrence Barwick wrote: Hi Payal Many thanks for the review, and my apologies for not getting back to you earlier. Updated version of the patch attached with suggested corrections. On a very quick glance, I see that you have still not made adjustments to contrib/file_fdw to accommodate this new option. I don't see why this COPY option should be different in that respect. Hmm, that idea seems to have escaped me completely. I'll get onto it forthwith. Striking while the keyboard is hot... version with contrib/file_fdw modifications attached. I have reviewed this. Generally it's good, but the author has made a significant error - the idea is not to force a quoted empty string to null, but to force a quoted null string to null, whatever the null string might be. The default case has these the same, but if you specify a non-empty null string they aren't. The author slaps himself on the forehead while regretting he was temporally constricted when dealing with the patch and never thought to look beyond the immediate use case. Thanks for the update, much appreciated. That difference actually made the file_fdw regression results plain wrong, in my view, in that they expected a quoted empty string to be turned to null even when the null string was something else. I've adjusted this and the docs and propose to apply the attached patch in the next day or two unless there are any objections. Unless I'm overlooking something, output from SELECT * FROM text_csv; in 'output/file_fdw.source' still needs updating? Yes, you're right. Will fix. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Patch FORCE_NULL option for copy COPY in CSV mode
On 01/29/2014 10:59 AM, Ian Lawrence Barwick wrote:
2014/1/29 Ian Lawrence Barwick barw...@gmail.com:
2014-01-29 Andrew Dunstan and...@dunslane.net:
On 01/28/2014 05:55 AM, Ian Lawrence Barwick wrote:
Hi Payal
Many thanks for the review, and my apologies for not getting back to
you earlier.
Updated version of the patch attached with suggested corrections.
On a very quick glance, I see that you have still not made adjustments to
contrib/file_fdw to accommodate this new option. I don't see why this COPY
option should be different in that respect.
Hmm, that idea seems to have escaped me completely. I'll get onto it forthwith.
Striking while the keyboard is hot... version with contrib/file_fdw
modifications
attached.
I have reviewed this. Generally it's good, but the author has made a
significant error - the idea is not to force a quoted empty string to
null, but to force a quoted null string to null, whatever the null
string might be. The default case has these the same, but if you specify
a non-empty null string they aren't.
That difference actually made the file_fdw regression results plain
wrong, in my view, in that they expected a quoted empty string to be
turned to null even when the null string was something else.
I've adjusted this and the docs and propose to apply the attached patch
in the next day or two unless there are any objections.
cheers
andrew
diff --git a/contrib/file_fdw/data/text.csv b/contrib/file_fdw/data/text.csv
index ed348a9..f55d9cf 100644
--- a/contrib/file_fdw/data/text.csv
+++ b/contrib/file_fdw/data/text.csv
@@ -1,4 +1,5 @@
-AAA,aaa
-XYZ,xyz
-NULL,NULL
-ABC,abc
+AAA,aaa,123,
+XYZ,xyz,,321
+NULL,NULL,NULL,NULL
+NULL,NULL,NULL,NULL
+ABC,abc,,
diff --git a/contrib/file_fdw/file_fdw.c b/contrib/file_fdw/file_fdw.c
index 5639f4d..7fb1dbc 100644
--- a/contrib/file_fdw/file_fdw.c
+++ b/contrib/file_fdw/file_fdw.c
@@ -48,9 +48,9 @@ struct FileFdwOption
/*
* Valid options for file_fdw.
- * These options are based on the options for COPY FROM command.
- * But note that force_not_null is handled as a boolean option attached to
- * each column, not as a table option.
+ * These options are based on the options for the COPY FROM command.
+ * But note that force_not_null and force_null are handled as boolean options
+ * attached to a column, not as table options.
*
* Note: If you are adding new option for user mapping, you need to modify
* fileGetOptions(), which currently doesn't bother to look at user mappings.
@@ -69,7 +69,7 @@ static const struct FileFdwOption valid_options[] = {
{null, ForeignTableRelationId},
{encoding, ForeignTableRelationId},
{force_not_null, AttributeRelationId},
-
+ {force_null, AttributeRelationId},
/*
* force_quote is not supported by file_fdw because it's for COPY TO.
*/
@@ -187,6 +187,7 @@ file_fdw_validator(PG_FUNCTION_ARGS)
Oid catalog = PG_GETARG_OID(1);
char *filename = NULL;
DefElem*force_not_null = NULL;
+ DefElem*force_null = NULL;
List *other_options = NIL;
ListCell *cell;
@@ -243,10 +244,10 @@ file_fdw_validator(PG_FUNCTION_ARGS)
}
/*
- * Separate out filename and force_not_null, since ProcessCopyOptions
- * won't accept them. (force_not_null only comes in a boolean
- * per-column flavor here.)
+ * Separate out filename and column-specific options, since
+ * ProcessCopyOptions won't accept them.
*/
+
if (strcmp(def-defname, filename) == 0)
{
if (filename)
@@ -255,16 +256,42 @@ file_fdw_validator(PG_FUNCTION_ARGS)
errmsg(conflicting or redundant options)));
filename = defGetString(def);
}
+ /*
+ * force_not_null is a boolean option; after validation we can discard
+ * it - it will be retrieved later in get_file_fdw_attribute_options()
+ */
else if (strcmp(def-defname, force_not_null) == 0)
{
if (force_not_null)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
- errmsg(conflicting or redundant options)));
+ errmsg(conflicting or redundant options),
+ errhint(option \force_not_null\ supplied more than once for a column)));
+ if(force_null)
+ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg(conflicting or redundant options),
+ errhint(option \force_not_null\ cannot be used together with \force_null\)));
force_not_null = def;
/* Don't care what the value is, as long as it's a legal boolean */
(void) defGetBoolean(def);
}
+ /* See comments for force_not_null above */
+ else if (strcmp(def-defname, force_null) == 0)
+ {
+ if (force_null)
+ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg(conflicting or redundant options),
+ errhint(option \force_null\ supplied more than once for a column)));
+ if(force_not_null)
+ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg(conflicting or redundant options),
+ errhint(option \force_null\ cannot be used together with
Re: [HACKERS] Review: Patch FORCE_NULL option for copy COPY in CSV mode
2014/1/29 Ian Lawrence Barwick barw...@gmail.com:
2014-01-29 Andrew Dunstan and...@dunslane.net:
On 01/28/2014 05:55 AM, Ian Lawrence Barwick wrote:
Hi Payal
Many thanks for the review, and my apologies for not getting back to
you earlier.
Updated version of the patch attached with suggested corrections.
On a very quick glance, I see that you have still not made adjustments to
contrib/file_fdw to accommodate this new option. I don't see why this COPY
option should be different in that respect.
Hmm, that idea seems to have escaped me completely. I'll get onto it
forthwith.
Striking while the keyboard is hot... version with contrib/file_fdw
modifications
attached.
Regards
Ian Barwick
diff --git a/contrib/file_fdw/data/text.csv b/contrib/file_fdw/data/text.csv
new file mode 100644
index ed348a9..c7e243c
*** a/contrib/file_fdw/data/text.csv
--- b/contrib/file_fdw/data/text.csv
***
*** 1,4
! AAA,aaa
! XYZ,xyz
! NULL,NULL
! ABC,abc
--- 1,4
! AAA,aaa,123,
! XYZ,xyz,,321
! NULL,NULL,NULL,NULL
! ABC,abc,,
diff --git a/contrib/file_fdw/file_fdw.c b/contrib/file_fdw/file_fdw.c
new file mode 100644
index 5639f4d..5877512
*** a/contrib/file_fdw/file_fdw.c
--- b/contrib/file_fdw/file_fdw.c
*** struct FileFdwOption
*** 48,56
/*
* Valid options for file_fdw.
! * These options are based on the options for COPY FROM command.
! * But note that force_not_null is handled as a boolean option attached to
! * each column, not as a table option.
*
* Note: If you are adding new option for user mapping, you need to modify
* fileGetOptions(), which currently doesn't bother to look at user mappings.
--- 48,56
/*
* Valid options for file_fdw.
! * These options are based on the options for the COPY FROM command.
! * But note that force_not_null and force_null are handled as boolean options
! * attached to a column, not as a table option.
*
* Note: If you are adding new option for user mapping, you need to modify
* fileGetOptions(), which currently doesn't bother to look at user mappings.
*** static const struct FileFdwOption valid_
*** 69,75
{null, ForeignTableRelationId},
{encoding, ForeignTableRelationId},
{force_not_null, AttributeRelationId},
!
/*
* force_quote is not supported by file_fdw because it's for COPY TO.
*/
--- 69,75
{null, ForeignTableRelationId},
{encoding, ForeignTableRelationId},
{force_not_null, AttributeRelationId},
! {force_null, AttributeRelationId},
/*
* force_quote is not supported by file_fdw because it's for COPY TO.
*/
*** file_fdw_validator(PG_FUNCTION_ARGS)
*** 187,192
--- 187,193
Oid catalog = PG_GETARG_OID(1);
char *filename = NULL;
DefElem*force_not_null = NULL;
+ DefElem*force_null = NULL;
List *other_options = NIL;
ListCell *cell;
*** file_fdw_validator(PG_FUNCTION_ARGS)
*** 243,252
}
/*
! * Separate out filename and force_not_null, since ProcessCopyOptions
! * won't accept them. (force_not_null only comes in a boolean
! * per-column flavor here.)
*/
if (strcmp(def-defname, filename) == 0)
{
if (filename)
--- 244,253
}
/*
! * Separate out filename and column-specific options, since
! * ProcessCopyOptions won't accept them.
*/
+
if (strcmp(def-defname, filename) == 0)
{
if (filename)
*** file_fdw_validator(PG_FUNCTION_ARGS)
*** 255,270
errmsg(conflicting or redundant options)));
filename = defGetString(def);
}
else if (strcmp(def-defname, force_not_null) == 0)
{
if (force_not_null)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg(conflicting or redundant options)));
force_not_null = def;
/* Don't care what the value is, as long as it's a legal boolean */
(void) defGetBoolean(def);
}
else
other_options = lappend(other_options, def);
}
--- 256,297
errmsg(conflicting or redundant options)));
filename = defGetString(def);
}
+ /*
+ * force_not_null is a boolean option; after validation we can discard
+ * it - it will be retrieved later in get_file_fdw_attribute_options()
+ */
else if (strcmp(def-defname, force_not_null) == 0)
{
if (force_not_null)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg(conflicting or redundant options),
! errhint(option \force_not_null\ supplied more than once for a column)));
! if(force_null)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg(conflicting or redundant options),
! errhint(option \force_not_null\ cannot be used together with \force_null\)));
force_not_null = def;
/* Don't care what the value is, as long as it's a legal boolean */
(void) defGetBoolean(def);
}
+ /* See comments for force_not_null above */
Re: [HACKERS] Review: Patch FORCE_NULL option for copy COPY in CSV mode
2013-11-01 Payal Singh pa...@omniti.com: The post was made before I subscribed to the mailing list, so posting my review separately. The link to the original patch mail is http://www.postgresql.org/message-id/CAB8KJ=jS-Um4TGwenS5wLUfJK6K4rNOm_V6GRUj+tcKekL2=g...@mail.gmail.com Hi, This patch implements the following TODO item: Allow COPY in CSV mode to control whether a quoted zero-length string is treated as NULL Currently this is always treated as a zero-length string, which generates an error when loading into an integer column Re: [PATCHES] allow CSV quote in NULL http://archives.postgresql.org/pgsql-hackers/2007-07/msg00905.php http://wiki.postgresql.org/wiki/Todo#COPY I had a very definite use-case for this functionality recently while importing CSV files generated by Oracle, and was somewhat frustrated by the existence of a FORCE_NOT_NULL option for specific columns, but not one for FORCE_NULL. I'll add this to the November commitfest. Regards Ian Barwick == Contents Purpose == This patch introduces a new 'FORCE_NULL' option which has the opposite functionality of the already present 'FORCE_NOT_NULL' option for the COPY command. Prior to this option there was no way to convert a zeroed-length quoted value to a NULL value when COPY FROM is used to import data from CSV formatted files. == Submission Review == The patch is in the correct context diff format. It includes changes to the documentation as well as additional regression tests. The description has been discussed and defined in the previous mails leading to this patch. = Functionality Review = CORRECTION NEEDED - Due to a minor error in code (details in 'Code Review' section below), force_null option is not limited to COPY FROM, and works even when COPY TO is used. This should instead give an error message. The updated documentation describes the added functionality clearly. All regression tests passed successfully. Code compilation after including patch was successful. No warnings either. Manually tested COPY FROM with FORCE_NULL for a number of scenarios, all with expected outputs. No issues. Been testing the patch for a few days, no crashes or weird behavior observed. = Code Formatting Review (Needs Improvement) = Looks good, the tabs between variable declaration and accompanying comment can be improved. = Code Review (Needs Improvement) = 1. There is a NOTE: force_not_null option are not applied to the returned fields. before COPY FROM block. Similar note should be added for force_null option too. 2. One of the conditions to check and give an error if force_null is true and copy from is false is wrong, cstate-force_null should be checked instead of cstate-force_notnull: /* Check force_notnull */ if (!cstate-csv_mode cstate-force_notnull != NIL) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg(COPY force not null available only in CSV mode))); if (cstate-force_notnull != NIL !is_from) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg(COPY force not null only available using COPY FROM))); /* Check force_null */ if (!cstate-csv_mode cstate-force_null != NIL) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg(COPY force null available only in CSV mode))); == if (cstate-force_notnull != NIL !is_from) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg(COPY force null only available using COPY FROM))); === Suggested Changes Conclusion === The Above mentioned error condition should be corrected. Minor comments and tab changes are upto the author. In all, suggested modifications aside, the patch works well and in my opinion, would be a useful addition to the COPY command. Hi Payal Many thanks for the review, and my apologies for not getting back to you earlier. Updated version of the patch attached with suggested corrections. I'm not sure about the tabs in the variable declarations - the whole section seems to be all over the place (regardless of whether tabs are set to 4 or 8 spaces) and could do with tidying up, however I didn't want to expand the scope of the patch too much. Quick recap of the reasons behind this patch - we had a bunch of CSV files (and by bunch I mean
Re: [HACKERS] Review: Patch FORCE_NULL option for copy COPY in CSV mode
On 01/28/2014 05:55 AM, Ian Lawrence Barwick wrote: Hi Payal Many thanks for the review, and my apologies for not getting back to you earlier. Updated version of the patch attached with suggested corrections. On a very quick glance, I see that you have still not made adjustments to contrib/file_fdw to accommodate this new option. I don't see why this COPY option should be different in that respect. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Patch FORCE_NULL option for copy COPY in CSV mode
2014-01-29 Andrew Dunstan and...@dunslane.net: On 01/28/2014 05:55 AM, Ian Lawrence Barwick wrote: Hi Payal Many thanks for the review, and my apologies for not getting back to you earlier. Updated version of the patch attached with suggested corrections. On a very quick glance, I see that you have still not made adjustments to contrib/file_fdw to accommodate this new option. I don't see why this COPY option should be different in that respect. Hmm, that idea seems to have escaped me completely. I'll get onto it forthwith. Regards Ian Barwick -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
