Signed-off-by: Daniel Farina <dfar...@truviso.com> --- contrib/dblink/expected/dblink.out | 272 ++++++++++++++++++++++++++++++++++++ contrib/dblink/sql/dblink.sql | 112 +++++++++++++++ 2 files changed, 384 insertions(+), 0 deletions(-)
diff --git a/contrib/dblink/expected/dblink.out b/contrib/dblink/expected/dblink.out index d39aa45..788b2a3 100644 --- a/contrib/dblink/expected/dblink.out +++ b/contrib/dblink/expected/dblink.out @@ -872,6 +872,278 @@ SELECT * from dblink_get_notify(); -------------+--------+------- (0 rows) +-- test COPY ... TO FUNCTION support +CREATE SCHEMA dblink_copy_to_function; +SET search_path = dblink_copy_to_function, public; +CREATE TABLE xyzzy(f1 int, f2 text, f3 text[], primary key (f1,f2)); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "xyzzy_pkey" for table "xyzzy" +INSERT INTO xyzzy VALUES (0,'a','{"a0","b0","c0"}'); +INSERT INTO xyzzy VALUES (1,'b','{"a1","b1","c1"}'); +INSERT INTO xyzzy VALUES (2,'c','{"a2","b2","c2"}'); +INSERT INTO xyzzy VALUES (3,'d','{"a3","b3","c3"}'); +INSERT INTO xyzzy VALUES (4,'e','{"a4","b4","c4"}'); +INSERT INTO xyzzy VALUES (5,'f','{"a5","b5","c5"}'); +INSERT INTO xyzzy VALUES (6,'g','{"a6","b6","c6"}'); +INSERT INTO xyzzy VALUES (7,'h','{"a7","b7","c7"}'); +INSERT INTO xyzzy VALUES (8,'i','{"a8","b8","c8"}'); +INSERT INTO xyzzy VALUES (9,'j','{"a9","b9","c9"}'); +CREATE TABLE bar(f1 int, f2 text, f3 text[], primary key (f1,f2)); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "bar_pkey" for table "bar" +INSERT INTO bar VALUES (100,'w','{"a100","b100","c100"}'); +INSERT INTO bar VALUES (101,'x','{"a101","b101","c101"}'); +CREATE TABLE baz(f1 int, f2 text, f3 text[], primary key (f1,f2)); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "baz_pkey" for table "baz" +INSERT INTO baz VALUES (102,'y','{"a102","b102","c102"}'); +INSERT INTO baz VALUES (103,'z','{"a103","b103","c103"}'); +CREATE TABLE plugh(f1 int, f2 text, f3 text[], primary key (f1,f2)); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "plugh_pkey" for table "plugh" +INSERT INTO plugh VALUES (104,'u','{"a102","b102","c102"}'); +INSERT INTO plugh VALUES (105,'v','{"a103","b103","c103"}'); +SELECT dblink_connect('copytofunction','dbname=contrib_regression'); + dblink_connect +---------------- + OK +(1 row) + +SELECT dblink_exec('copytofunction', + 'SET search_path = dblink_copy_to_function, public;'); + dblink_exec +------------- + SET +(1 row) + +-- ensure that original base data is present +SELECT * +FROM dblink('copytofunction', 'SELECT * FROM xyzzy') AS t(a int, b text, c text[]); + a | b | c +---+---+------------ + 0 | a | {a0,b0,c0} + 1 | b | {a1,b1,c1} + 2 | c | {a2,b2,c2} + 3 | d | {a3,b3,c3} + 4 | e | {a4,b4,c4} + 5 | f | {a5,b5,c5} + 6 | g | {a6,b6,c6} + 7 | h | {a7,b7,c7} + 8 | i | {a8,b8,c8} + 9 | j | {a9,b9,c9} +(10 rows) + +-- try doing a few consecutive copies with one open connection +SELECT dblink_copy_open('copytofunction', 'xyzzy', false); + dblink_copy_open +------------------ + OK +(1 row) + +COPY bar TO FUNCTION dblink_copy_write; +COPY baz TO FUNCTION dblink_copy_write; +SELECT dblink_copy_end(); + dblink_copy_end +----------------- + OK +(1 row) + +-- confirm that data has arrived +SELECT * +FROM dblink('copytofunction', 'SELECT * FROM xyzzy') AS t(a int, b text, c text[]); + a | b | c +-----+---+------------------ + 0 | a | {a0,b0,c0} + 1 | b | {a1,b1,c1} + 2 | c | {a2,b2,c2} + 3 | d | {a3,b3,c3} + 4 | e | {a4,b4,c4} + 5 | f | {a5,b5,c5} + 6 | g | {a6,b6,c6} + 7 | h | {a7,b7,c7} + 8 | i | {a8,b8,c8} + 9 | j | {a9,b9,c9} + 100 | w | {a100,b100,c100} + 101 | x | {a101,b101,c101} + 102 | y | {a102,b102,c102} + 103 | z | {a103,b103,c103} +(14 rows) + +-- try doing a binary COPY +SELECT dblink_copy_open('copytofunction', 'xyzzy', true); + dblink_copy_open +------------------ + OK +(1 row) + +COPY plugh TO FUNCTION dblink_copy_write BINARY; +SELECT dblink_copy_end(); + dblink_copy_end +----------------- + OK +(1 row) + +-- confirm that data has arrived +SELECT * +FROM dblink('copytofunction', 'SELECT * FROM xyzzy') AS t(a int, b text, c text[]); + a | b | c +-----+---+------------------ + 0 | a | {a0,b0,c0} + 1 | b | {a1,b1,c1} + 2 | c | {a2,b2,c2} + 3 | d | {a3,b3,c3} + 4 | e | {a4,b4,c4} + 5 | f | {a5,b5,c5} + 6 | g | {a6,b6,c6} + 7 | h | {a7,b7,c7} + 8 | i | {a8,b8,c8} + 9 | j | {a9,b9,c9} + 100 | w | {a100,b100,c100} + 101 | x | {a101,b101,c101} + 102 | y | {a102,b102,c102} + 103 | z | {a103,b103,c103} + 104 | u | {a102,b102,c102} + 105 | v | {a103,b103,c103} +(16 rows) + +-- try using reset to abort out of a copy state +SELECT dblink_copy_open('copytofunction', 'xyzzy', true); + dblink_copy_open +------------------ + OK +(1 row) + +COPY plugh TO FUNCTION dblink_copy_write BINARY; +SELECT dblink_connection_reset('copytofunction'); + dblink_connection_reset +------------------------- + +(1 row) + +-- should fail, as COPY should have been aborted +SELECT dblink_copy_end(); +ERROR: COPY end failed +-- no new data should have appeared +SELECT * +FROM dblink('copytofunction', 'SELECT * FROM xyzzy') AS t(a int, b text, c text[]); + a | b | c +-----+---+------------------ + 0 | a | {a0,b0,c0} + 1 | b | {a1,b1,c1} + 2 | c | {a2,b2,c2} + 3 | d | {a3,b3,c3} + 4 | e | {a4,b4,c4} + 5 | f | {a5,b5,c5} + 6 | g | {a6,b6,c6} + 7 | h | {a7,b7,c7} + 8 | i | {a8,b8,c8} + 9 | j | {a9,b9,c9} + 100 | w | {a100,b100,c100} + 101 | x | {a101,b101,c101} + 102 | y | {a102,b102,c102} + 103 | z | {a103,b103,c103} + 104 | u | {a102,b102,c102} + 105 | v | {a103,b103,c103} +(16 rows) + +-- should be a no-op, since no transaction should be active at this +-- point +SELECT dblink_connection_reset('copytofunction'); + dblink_connection_reset +------------------------- + +(1 row) + +-- generate an error in the remote transaction +SELECT dblink_exec('copytofunction','BEGIN'); + dblink_exec +------------- + BEGIN +(1 row) + +SELECT * FROM dblink('copytofunction', 'SELECT 1 / 0') AS t (a int); +ERROR: division by zero +CONTEXT: Error occurred on dblink connection named "unnamed": could not execute query. +-- rollback the errored transaction +SELECT dblink_connection_reset('copytofunction'); + dblink_connection_reset +------------------------- + +(1 row) + +-- should just work, if reset didn't actually reset the transaction +-- state an error would result. +SELECT * FROM dblink('copytofunction', 'SELECT 1;') AS t (a int); + a +--- + 1 +(1 row) + +-- try a really long identifier to test string handlig in +-- dblink_copy_open. This should neatly hit NAMEDATALEN on most +-- systems, or 64 - 1 +create table +"012345678901234567890123456789012345678901234567890123456789012" (a int); +-- should put the connection into the COPY state without complaint... +SELECT dblink_copy_open('copytofunction', + '012345678901234567890123456789012345678901234567890123456789012', + true); + dblink_copy_open +------------------ + OK +(1 row) + +COPY (SELECT generate_series(1, 5)) TO FUNCTION dblink_copy_write BINARY; +SELECT dblink_copy_end(); + dblink_copy_end +----------------- + OK +(1 row) + +-- check to see if data made it +SELECT * FROM + "012345678901234567890123456789012345678901234567890123456789012"; + a +--- + 1 + 2 + 3 + 4 + 5 +(5 rows) + +-- postgres truncates long identifiers and advertises with a NOTICE, +-- and as of right now dblink does no remote-machine NOTICE handling. +-- The result is silent truncation to the remote machine's +-- NAMEDATALEN. +SELECT dblink_copy_open('copytofunction', + '012345678901234567890123456789012345678901234567890123456789012345678', + true); + dblink_copy_open +------------------ + OK +(1 row) + +COPY (SELECT generate_series(6, 10)) TO FUNCTION dblink_copy_write BINARY; +SELECT dblink_copy_end(); + dblink_copy_end +----------------- + OK +(1 row) + +-- check to see if data made it +SELECT * FROM + "012345678901234567890123456789012345678901234567890123456789012"; + a +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + SELECT dblink_disconnect(); dblink_disconnect ------------------- diff --git a/contrib/dblink/sql/dblink.sql b/contrib/dblink/sql/dblink.sql index d0ad876..919fd78 100644 --- a/contrib/dblink/sql/dblink.sql +++ b/contrib/dblink/sql/dblink.sql @@ -405,4 +405,116 @@ SELECT notify_name, be_pid = (select t.be_pid from dblink('select pg_backend_pid SELECT * from dblink_get_notify(); +-- test COPY ... TO FUNCTION support +CREATE SCHEMA dblink_copy_to_function; +SET search_path = dblink_copy_to_function, public; +CREATE TABLE xyzzy(f1 int, f2 text, f3 text[], primary key (f1,f2)); +INSERT INTO xyzzy VALUES (0,'a','{"a0","b0","c0"}'); +INSERT INTO xyzzy VALUES (1,'b','{"a1","b1","c1"}'); +INSERT INTO xyzzy VALUES (2,'c','{"a2","b2","c2"}'); +INSERT INTO xyzzy VALUES (3,'d','{"a3","b3","c3"}'); +INSERT INTO xyzzy VALUES (4,'e','{"a4","b4","c4"}'); +INSERT INTO xyzzy VALUES (5,'f','{"a5","b5","c5"}'); +INSERT INTO xyzzy VALUES (6,'g','{"a6","b6","c6"}'); +INSERT INTO xyzzy VALUES (7,'h','{"a7","b7","c7"}'); +INSERT INTO xyzzy VALUES (8,'i','{"a8","b8","c8"}'); +INSERT INTO xyzzy VALUES (9,'j','{"a9","b9","c9"}'); + +CREATE TABLE bar(f1 int, f2 text, f3 text[], primary key (f1,f2)); +INSERT INTO bar VALUES (100,'w','{"a100","b100","c100"}'); +INSERT INTO bar VALUES (101,'x','{"a101","b101","c101"}'); + +CREATE TABLE baz(f1 int, f2 text, f3 text[], primary key (f1,f2)); +INSERT INTO baz VALUES (102,'y','{"a102","b102","c102"}'); +INSERT INTO baz VALUES (103,'z','{"a103","b103","c103"}'); + +CREATE TABLE plugh(f1 int, f2 text, f3 text[], primary key (f1,f2)); +INSERT INTO plugh VALUES (104,'u','{"a102","b102","c102"}'); +INSERT INTO plugh VALUES (105,'v','{"a103","b103","c103"}'); + +SELECT dblink_connect('copytofunction','dbname=contrib_regression'); +SELECT dblink_exec('copytofunction', + 'SET search_path = dblink_copy_to_function, public;'); + +-- ensure that original base data is present +SELECT * +FROM dblink('copytofunction', 'SELECT * FROM xyzzy') AS t(a int, b text, c text[]); + +-- try doing a few consecutive copies with one open connection +SELECT dblink_copy_open('copytofunction', 'xyzzy', false); +COPY bar TO FUNCTION dblink_copy_write; +COPY baz TO FUNCTION dblink_copy_write; +SELECT dblink_copy_end(); + +-- confirm that data has arrived +SELECT * +FROM dblink('copytofunction', 'SELECT * FROM xyzzy') AS t(a int, b text, c text[]); + +-- try doing a binary COPY +SELECT dblink_copy_open('copytofunction', 'xyzzy', true); +COPY plugh TO FUNCTION dblink_copy_write BINARY; +SELECT dblink_copy_end(); + +-- confirm that data has arrived +SELECT * +FROM dblink('copytofunction', 'SELECT * FROM xyzzy') AS t(a int, b text, c text[]); + +-- try using reset to abort out of a copy state +SELECT dblink_copy_open('copytofunction', 'xyzzy', true); +COPY plugh TO FUNCTION dblink_copy_write BINARY; +SELECT dblink_connection_reset('copytofunction'); + +-- should fail, as COPY should have been aborted +SELECT dblink_copy_end(); + +-- no new data should have appeared +SELECT * +FROM dblink('copytofunction', 'SELECT * FROM xyzzy') AS t(a int, b text, c text[]); + +-- should be a no-op, since no transaction should be active at this +-- point +SELECT dblink_connection_reset('copytofunction'); + +-- generate an error in the remote transaction +SELECT dblink_exec('copytofunction','BEGIN'); +SELECT * FROM dblink('copytofunction', 'SELECT 1 / 0') AS t (a int); + +-- rollback the errored transaction +SELECT dblink_connection_reset('copytofunction'); + +-- should just work, if reset didn't actually reset the transaction +-- state an error would result. +SELECT * FROM dblink('copytofunction', 'SELECT 1;') AS t (a int); + +-- try a really long identifier to test string handlig in +-- dblink_copy_open. This should neatly hit NAMEDATALEN on most +-- systems, or 64 - 1 +create table +"012345678901234567890123456789012345678901234567890123456789012" (a int); + +-- should put the connection into the COPY state without complaint... +SELECT dblink_copy_open('copytofunction', + '012345678901234567890123456789012345678901234567890123456789012', + true); +COPY (SELECT generate_series(1, 5)) TO FUNCTION dblink_copy_write BINARY; +SELECT dblink_copy_end(); + +-- check to see if data made it +SELECT * FROM + "012345678901234567890123456789012345678901234567890123456789012"; + +-- postgres truncates long identifiers and advertises with a NOTICE, +-- and as of right now dblink does no remote-machine NOTICE handling. +-- The result is silent truncation to the remote machine's +-- NAMEDATALEN. +SELECT dblink_copy_open('copytofunction', + '012345678901234567890123456789012345678901234567890123456789012345678', + true); +COPY (SELECT generate_series(6, 10)) TO FUNCTION dblink_copy_write BINARY; +SELECT dblink_copy_end(); + +-- check to see if data made it +SELECT * FROM + "012345678901234567890123456789012345678901234567890123456789012"; + SELECT dblink_disconnect(); -- 1.6.5.3 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers