Skip site navigation (1) Skip section navigation (2)

not null validation option in contrib/file_fdw

From: "Etsuro Fujita" <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: not null validation option in contrib/file_fdw
Date: 2012-04-13 07:59:23
Message-ID: 001901cd194b$56b50d40$041f27c0$@lab.ntt.co.jp (view raw or flat)
Thread:
Lists: pgsql-hackers
I updated the patch added to CF 2012-Next [1].  Attached is the updated
version of the patch.

For the discussion in [2], I've introduced a new generic option, validate
for file_fdw foreign tables, which specifies if file_fdw verifies that
tuples meet NOT NULL constraints.  The default value for the option is
'false', and if the value is set to 'true', then file_fdw verifies NOT NULL
constraints.  For example, a user can issue the following to let file_fdw
verify the constraint of the msg column.

    CREATE FOREIGN TABLE ft (id INTEGER, msg TEXT NOT NULL)
                  SERVER fs
                 OPTIONS (filename '/path/to/file', format 'csv',
                          delimiter ',', validate 'true');

At the SELECT time, ABORT is issued when a NOT NULL violation error has been
found, like COPY FROM.  Once the validation is done successfully using e.g.
SELECT COUNT(*), the user can set the option to 'false' using ALTER FOREIGN
TABLE.  I think this option is needed for flat files due to their lack of
ability to check such a constraint.

(I added NOT NULL checking to ileIterateForeignScan(), but not to
file_acquire_sample_rows().  Should we do that at
file_acquire_sample_rows()?  I think it is good to just recommend that users
do ANALYZE a foreign table after the validation.)

For the discussion in [3], I've added a new external function
ExecNotNullCheck() and call it from fileIterateForeignScan.

Any comments are welcome.

Best regards,
Etsuro Fujita

[1] https://commitfest.postgresql.org/action/patch_view?id=822
[2] http://archives.postgresql.org/message-id/1038.1331738954@sss.pgh.pa.us
[3] http://archives.postgresql.org/pgsql-hackers/2012-03/msg00809.php


Attachment: file_fdw_notnull_v2.patch
Description: application/octet-stream (10.6 KB)

Responses

pgsql-hackers by date

Next:From: Dimitri FontaineDate: 2012-04-13 08:24:52
Subject: Re: Last gasp
Previous:From: Jeff DavisDate: 2012-04-13 05:01:35
Subject: Re: Memory usage during sorting

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group