From cc3f281ffb0a5d9b187e7a7b7de4a045809ff683 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 11 Mar 2013 21:31:28 -0400 Subject: Fix postgres_fdw's issues with inconsistent interpretation of data values. For datatypes whose output formatting depends on one or more GUC settings, we have to worry about whether the other server will interpret the value the same way it was meant. pg_dump has been aware of this hazard for a long time, but postgres_fdw needs to deal with it too. To fix data retrieval from the remote server, set the necessary remote GUC settings at connection startup. (We were already assuming that settings made then would persist throughout the remote session.) To fix data transmission to the remote server, temporarily force the relevant GUCs to the right values when we're about to convert any data values to text for transmission. This is all pretty grotty, and not very cheap either. It's tempting to think of defining one uber-GUC that would override any settings that might render printed data values unportable. But of course, older remote servers wouldn't know any such thing and would still need this logic. While at it, revert commit f7951eef89be78c50ea2241f593d76dfefe176c9, since this provides a real fix. (The timestamptz given in the error message returned from the "remote" server will now reliably be shown in UTC.) --- contrib/postgres_fdw/sql/postgres_fdw.sql | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'contrib/postgres_fdw/sql/postgres_fdw.sql') diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 2e9e17f5f93..007109c7c76 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -316,7 +316,7 @@ ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0); INSERT INTO ft1(c1, c2) VALUES(11, 12); -- duplicate key INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive -UPDATE ft1 SET c2 = -c2, c4 = null WHERE c1 = 1; -- c2positive +UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive -- Test savepoint/rollback behavior select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; @@ -337,7 +337,7 @@ select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; release savepoint s2; select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; savepoint s3; -update ft2 set c2 = -2, c4 = null where c2 = 42; -- fail on remote side +update ft2 set c2 = -2 where c2 = 42; -- fail on remote side rollback to savepoint s3; select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; release savepoint s3; -- cgit v1.2.3