file_fdw.diffs

text/plain
Filename: file_fdw.diffs
Type: text/plain
Part: 2
Message: Re: Password identifiers, protocol aging and SCRAM protocol
*** /home/vpopov/Projects/pwdtest.new/postgresql/contrib/file_fdw/expected/file_fdw.out	2016-03-15 16:11:08.209906170 +0300
--- /home/vpopov/Projects/pwdtest.new/postgresql/contrib/file_fdw/results/file_fdw.out	2016-03-15 16:11:09.497917931 +0300
***************
*** 104,122 ****
  ) SERVER file_server
  OPTIONS (format 'text', filename '/home/vpopov/Projects/pwdtest.new/postgresql/contrib/file_fdw/data/text.csv', null 'NULL');
  SELECT * FROM text_csv; -- ERROR
! ERROR:  COPY force not null available only in CSV mode
  ALTER FOREIGN TABLE text_csv OPTIONS (SET format 'csv');
  \pset null _null_
  SELECT * FROM text_csv;
!  word1 | word2  | word3  | word4  
! -------+--------+--------+--------
!  AAA   | aaa    | 123    | 
!  XYZ   | xyz    |        | 321
!  NULL  | _null_ | _null_ | _null_
!  NULL  | _null_ | _null_ | _null_
!  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 (force_not_null 'true');
--- 104,114 ----
  ) SERVER file_server
  OPTIONS (format 'text', filename '/home/vpopov/Projects/pwdtest.new/postgresql/contrib/file_fdw/data/text.csv', null 'NULL');
  SELECT * FROM text_csv; -- ERROR
! ERROR:  unrecognized node type: 539
  ALTER FOREIGN TABLE text_csv OPTIONS (SET format 'csv');
  \pset null _null_
  SELECT * FROM text_csv;
! ERROR:  unrecognized node type: 539
  -- 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');
***************
*** 148,345 ****
  HINT:  Valid options in this context are: filename, format, header, delimiter, quote, escape, null, encoding
  -- basic query tests
  SELECT * FROM agg_text WHERE b > 10.0 ORDER BY a;
!   a  |   b    
! -----+--------
!   42 | 324.78
!  100 | 99.097
! (2 rows)
! 
  SELECT * FROM agg_csv ORDER BY a;
!   a  |    b    
! -----+---------
!    0 | 0.09561
!   42 |  324.78
!  100 |  99.097
! (3 rows)
! 
  SELECT * FROM agg_csv c JOIN agg_text t ON (t.a = c.a) ORDER BY c.a;
!   a  |    b    |  a  |    b    
! -----+---------+-----+---------
!    0 | 0.09561 |   0 | 0.09561
!   42 |  324.78 |  42 |  324.78
!  100 |  99.097 | 100 |  99.097
! (3 rows)
! 
! -- error context report tests
! SELECT * FROM agg_bad;               -- ERROR
! ERROR:  invalid input syntax for type real: "aaa"
! CONTEXT:  COPY agg_bad, line 3, column b: "aaa"
! -- misc query tests
! \t on
! EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv;
!  Foreign Scan on public.agg_csv
!    Output: a, b
!    Foreign File: /home/vpopov/Projects/pwdtest.new/postgresql/contrib/file_fdw/data/agg.csv
! 
! \t off
! PREPARE st(int) AS SELECT * FROM agg_csv WHERE a = $1;
! EXECUTE st(100);
!   a  |   b    
! -----+--------
!  100 | 99.097
! (1 row)
! 
! EXECUTE st(100);
!   a  |   b    
! -----+--------
!  100 | 99.097
! (1 row)
! 
! DEALLOCATE st;
! -- tableoid
! SELECT tableoid::regclass, b FROM agg_csv;
!  tableoid |    b    
! ----------+---------
!  agg_csv  |  99.097
!  agg_csv  | 0.09561
!  agg_csv  |  324.78
! (3 rows)
! 
! -- updates aren't supported
! INSERT INTO agg_csv VALUES(1,2.0);
! ERROR:  cannot insert into foreign table "agg_csv"
! UPDATE agg_csv SET a = 1;
! ERROR:  cannot update foreign table "agg_csv"
! DELETE FROM agg_csv WHERE a = 100;
! ERROR:  cannot delete from foreign table "agg_csv"
! -- but this should be allowed
! SELECT * FROM agg_csv FOR UPDATE;
!   a  |    b    
! -----+---------
!  100 |  99.097
!    0 | 0.09561
!   42 |  324.78
! (3 rows)
! 
! -- constraint exclusion tests
! \t on
! EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;
!  Foreign Scan on public.agg_csv
!    Output: a, b
!    Filter: (agg_csv.a < 0)
!    Foreign File: /home/vpopov/Projects/pwdtest.new/postgresql/contrib/file_fdw/data/agg.csv
! 
! \t off
! SELECT * FROM agg_csv WHERE a < 0;
!  a | b 
! ---+---
! (0 rows)
! 
! SET constraint_exclusion = 'on';
! \t on
! EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;
!  Result
!    Output: a, b
!    One-Time Filter: false
! 
! \t off
! SELECT * FROM agg_csv WHERE a < 0;
!  a | b 
! ---+---
! (0 rows)
! 
! RESET constraint_exclusion;
! -- table inheritance tests
! CREATE TABLE agg (a int2, b float4);
! ALTER FOREIGN TABLE agg_csv INHERIT agg;
! SELECT tableoid::regclass, * FROM agg;
!  tableoid |  a  |    b    
! ----------+-----+---------
!  agg_csv  | 100 |  99.097
!  agg_csv  |   0 | 0.09561
!  agg_csv  |  42 |  324.78
! (3 rows)
! 
! SELECT tableoid::regclass, * FROM agg_csv;
!  tableoid |  a  |    b    
! ----------+-----+---------
!  agg_csv  | 100 |  99.097
!  agg_csv  |   0 | 0.09561
!  agg_csv  |  42 |  324.78
! (3 rows)
! 
! SELECT tableoid::regclass, * FROM ONLY agg;
!  tableoid | a | b 
! ----------+---+---
! (0 rows)
! 
! -- updates aren't supported
! UPDATE agg SET a = 1;
! ERROR:  cannot update foreign table "agg_csv"
! DELETE FROM agg WHERE a = 100;
! ERROR:  cannot delete from foreign table "agg_csv"
! -- but this should be allowed
! SELECT tableoid::regclass, * FROM agg FOR UPDATE;
!  tableoid |  a  |    b    
! ----------+-----+---------
!  agg_csv  | 100 |  99.097
!  agg_csv  |   0 | 0.09561
!  agg_csv  |  42 |  324.78
! (3 rows)
! 
! ALTER FOREIGN TABLE agg_csv NO INHERIT agg;
! DROP TABLE agg;
! -- privilege tests
! SET ROLE file_fdw_superuser;
! SELECT * FROM agg_text ORDER BY a;
!   a  |    b    
! -----+---------
!    0 | 0.09561
!   42 |  324.78
!   56 |     7.8
!  100 |  99.097
! (4 rows)
! 
! SET ROLE file_fdw_user;
! SELECT * FROM agg_text ORDER BY a;
!   a  |    b    
! -----+---------
!    0 | 0.09561
!   42 |  324.78
!   56 |     7.8
!  100 |  99.097
! (4 rows)
! 
! SET ROLE no_priv_user;
! SELECT * FROM agg_text ORDER BY a;   -- ERROR
! ERROR:  permission denied for relation agg_text
! SET ROLE file_fdw_user;
! \t on
! EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_text WHERE a > 0;
!  Foreign Scan on public.agg_text
!    Output: a, b
!    Filter: (agg_text.a > 0)
!    Foreign File: /home/vpopov/Projects/pwdtest.new/postgresql/contrib/file_fdw/data/agg.data
! 
! \t off
! -- privilege tests for object
! SET ROLE file_fdw_superuser;
! ALTER FOREIGN TABLE agg_text OWNER TO file_fdw_user;
! ALTER FOREIGN TABLE agg_text OPTIONS (SET format 'text');
! SET ROLE file_fdw_user;
! ALTER FOREIGN TABLE agg_text OPTIONS (SET format 'text');
! ERROR:  only superuser can change options of a file_fdw foreign table
! SET ROLE file_fdw_superuser;
! -- cleanup
! RESET ROLE;
! DROP EXTENSION file_fdw CASCADE;
! NOTICE:  drop cascades to 8 other objects
! DETAIL:  drop cascades to server file_server
! drop cascades to user mapping for file_fdw_user on server file_server
! drop cascades to user mapping for file_fdw_superuser on server file_server
! drop cascades to user mapping for no_priv_user on server file_server
! drop cascades to foreign table agg_text
! drop cascades to foreign table agg_csv
! drop cascades to foreign table agg_bad
! drop cascades to foreign table text_csv
! DROP ROLE file_fdw_superuser, file_fdw_user, no_priv_user;
--- 140,150 ----
  HINT:  Valid options in this context are: filename, format, header, delimiter, quote, escape, null, encoding
  -- basic query tests
  SELECT * FROM agg_text WHERE b > 10.0 ORDER BY a;
! ERROR:  unrecognized node type: 539
  SELECT * FROM agg_csv ORDER BY a;
! ERROR:  unrecognized node type: 539
  SELECT * FROM agg_csv c JOIN agg_text t ON (t.a = c.a) ORDER BY c.a;
! server closed the connection unexpectedly
! 	This probably means the server terminated abnormally
! 	before or while processing the request.
! connection to server was lost

======================================================================