(2012/02/10 20:39), Shigeru Hanada wrote:
> (2012/02/08 20:51), Shigeru Hanada wrote:
>> Attached revised patches. Changes from last version are below.
> <snip>
>
> I've found and fixed a bug which generates wrong remote query when any
> column of a foreign table has been dropped. Also regression test for
> this case is added.
>
> I attached only pgsql_fdw_v8.patch, because pgsql_fdw_pushdown_v3.patch
> in last post still can be applied onto v8 patch.
>
> Regards,
The patches have been applied, but role-related regression tests failed
in my environment. I fixed it in a similar fashion of
/src/test/regress/sql/foreign_data.sql. Please find attached a updated
patch for the regression tests.
BTW, What do you think about this?
http://archives.postgresql.org/pgsql-hackers/2012-01/msg00229.php
Best regards,
Etsuro Fujita
*** sql/pgsql_fdw.sql.orig 2012-02-13 19:52:08.000000000 +0900
--- sql/pgsql_fdw.sql 2012-02-13 19:44:17.000000000 +0900
***************
*** 2,7 ****
--- 2,19 ----
-- create FDW objects
-- ===================================================================
+ -- Clean up in case a prior regression run failed
+
+ -- Suppress NOTICE messages when roles don't exist
+ SET client_min_messages TO 'error';
+
+ DROP ROLE IF EXISTS pgsql_fdw_user;
+
+ RESET client_min_messages;
+
+ CREATE ROLE pgsql_fdw_user LOGIN SUPERUSER;
+ SET SESSION AUTHORIZATION 'pgsql_fdw_user';
+
CREATE EXTENSION pgsql_fdw;
CREATE SERVER loopback1 FOREIGN DATA WRAPPER pgsql_fdw;
***************
*** 168,173 ****
--- 180,186 ----
EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = abs(t1.c2);
EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2;
DROP OPERATOR === (int, int) CASCADE;
+ DROP OPERATOR !== (int, int) CASCADE;
DROP FUNCTION pgsql_fdw_abs(int);
-- ===================================================================
***************
*** 212,216 ****
-- ===================================================================
-- cleanup
-- ===================================================================
DROP EXTENSION pgsql_fdw CASCADE;
!
--- 225,231 ----
-- ===================================================================
-- cleanup
-- ===================================================================
+ DROP SCHEMA "S 1" CASCADE;
DROP EXTENSION pgsql_fdw CASCADE;
! \c
! DROP ROLE pgsql_fdw_user;
*** expected/pgsql_fdw.out.orig 2012-02-13 19:52:03.000000000 +0900
--- expected/pgsql_fdw.out 2012-02-13 19:51:49.000000000 +0900
***************
*** 1,6 ****
--- 1,13 ----
-- ===================================================================
-- create FDW objects
-- ===================================================================
+ -- Clean up in case a prior regression run failed
+ -- Suppress NOTICE messages when roles don't exist
+ SET client_min_messages TO 'error';
+ DROP ROLE IF EXISTS pgsql_fdw_user;
+ RESET client_min_messages;
+ CREATE ROLE pgsql_fdw_user LOGIN SUPERUSER;
+ SET SESSION AUTHORIZATION 'pgsql_fdw_user';
CREATE EXTENSION pgsql_fdw;
CREATE SERVER loopback1 FOREIGN DATA WRAPPER pgsql_fdw;
CREATE SERVER loopback2 FOREIGN DATA WRAPPER pgsql_fdw
***************
*** 130,147 ****
ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (colname 'C 1');
ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (colname 'C 1');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access
privileges | FDW Options | Description
!
-----------+----------+-------------------+---------------------+-------------------+-------------+-------------
! pgsql_fdw | postgres | pgsql_fdw_handler | pgsql_fdw_validator |
| |
(1 row)
\des+
!
List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type |
Version |
FDW Options
| Description
!
-----------+----------+----------------------+-------------------+------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
! loopback1 | postgres | pgsql_fdw | | |
| (authtype 'value', service 'value', connect_timeout 'value', dbname
'value', host 'value', hostaddr 'value', port 'value', tty 'value', options
'value', application_name 'value', keepalives 'value', keepalives_idle 'value',
keepalives_interval 'value', sslmode 'value', sslcert 'value', sslkey 'value',
sslrootcert 'value', sslcrl 'value') |
! loopback2 | postgres | pgsql_fdw | | |
| (dbname 'contrib_regression', fetch_count '2')
|
(2 rows)
\deu+
--- 137,154 ----
ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (colname 'C 1');
ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (colname 'C 1');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator |
Access privileges | FDW Options | Description
!
-----------+----------------+-------------------+---------------------+-------------------+-------------+-------------
! pgsql_fdw | pgsql_fdw_user | pgsql_fdw_handler | pgsql_fdw_validator |
| |
(1 row)
\des+
!
List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type
| Version |
FDW Options
| Description
!
-----------+----------------+----------------------+-------------------+------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
! loopback1 | pgsql_fdw_user | pgsql_fdw | |
| | (authtype 'value', service 'value', connect_timeout 'value', dbname
'value', host 'value', hostaddr 'value', port 'value', tty 'value', options
'value', application_name 'value', keepalives 'value', keepalives_idle 'value',
keepalives_interval 'value', sslmode 'value', sslcert 'value', sslkey 'value',
sslrootcert 'value', sslcrl 'value') |
! loopback2 | pgsql_fdw_user | pgsql_fdw | |
| | (dbname 'contrib_regression', fetch_count '2')
|
(2 rows)
\deu+
***************
*** 349,354 ****
--- 356,362 ----
(2 rows)
DROP OPERATOR === (int, int) CASCADE;
+ DROP OPERATOR !== (int, int) CASCADE;
DROP FUNCTION pgsql_fdw_abs(int);
-- ===================================================================
-- parameterized queries
***************
*** 490,498 ****
-- connection management
-- ===================================================================
SELECT srvname, usename FROM pgsql_fdw_connections;
! srvname | usename
! -----------+----------
! loopback2 | postgres
(1 row)
SELECT pgsql_fdw_disconnect(srvid, usesysid) FROM pgsql_fdw_get_connections();
--- 498,506 ----
-- connection management
-- ===================================================================
SELECT srvname, usename FROM pgsql_fdw_connections;
! srvname | usename
! -----------+----------------
! loopback2 | pgsql_fdw_user
(1 row)
SELECT pgsql_fdw_disconnect(srvid, usesysid) FROM pgsql_fdw_get_connections();
***************
*** 509,514 ****
--- 517,526 ----
-- ===================================================================
-- cleanup
-- ===================================================================
+ DROP SCHEMA "S 1" CASCADE;
+ NOTICE: drop cascades to 2 other objects
+ DETAIL: drop cascades to table "S 1"."T 1"
+ drop cascades to table "S 1"."T 2"
DROP EXTENSION pgsql_fdw CASCADE;
NOTICE: drop cascades to 6 other objects
DETAIL: drop cascades to server loopback1
***************
*** 517,519 ****
--- 529,533 ----
drop cascades to user mapping for public
drop cascades to foreign table ft1
drop cascades to foreign table ft2
+ \c
+ DROP ROLE pgsql_fdw_user;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
