Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitcc3f281

Browse files
committed
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 valuethe same way it was meant. pg_dump has been aware of this hazard for along time, but postgres_fdw needs to deal with it too. To fix dataretrieval from the remote server, set the necessary remote GUC settings atconnection startup. (We were already assuming that settings made thenwould persist throughout the remote session.) To fix data transmission tothe remote server, temporarily force the relevant GUCs to the right valueswhen 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 tothink of defining one uber-GUC that would override any settings that mightrender printed data values unportable. But of course, older remote serverswouldn't know any such thing and would still need this logic.While at it, revert commitf7951ee, sincethis provides a real fix. (The timestamptz given in the error messagereturned from the "remote" server will now reliably be shown in UTC.)
1 parent8f9cc41 commitcc3f281

File tree

6 files changed

+122
-29
lines changed

6 files changed

+122
-29
lines changed

‎contrib/postgres_fdw/connection.c

Lines changed: 40 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -67,6 +67,7 @@ static bool xact_got_connection = false;
6767
staticPGconn*connect_pg_server(ForeignServer*server,UserMapping*user);
6868
staticvoidcheck_conn_params(constchar**keywords,constchar**values);
6969
staticvoidconfigure_remote_session(PGconn*conn);
70+
staticvoiddo_sql_command(PGconn*conn,constchar*sql);
7071
staticvoidbegin_remote_xact(ConnCacheEntry*entry);
7172
staticvoidpgfdw_xact_callback(XactEventevent,void*arg);
7273
staticvoidpgfdw_subxact_callback(SubXactEventevent,
@@ -314,11 +315,43 @@ check_conn_params(const char **keywords, const char **values)
314315
staticvoid
315316
configure_remote_session(PGconn*conn)
316317
{
317-
constchar*sql;
318-
PGresult*res;
318+
intremoteversion=PQserverVersion(conn);
319319

320320
/* Force the search path to contain only pg_catalog (see deparse.c) */
321-
sql="SET search_path = pg_catalog";
321+
do_sql_command(conn,"SET search_path = pg_catalog");
322+
323+
/*
324+
* Set remote timezone; this is basically just cosmetic, since all
325+
* transmitted and returned timestamptzs should specify a zone explicitly
326+
* anyway.However it makes the regression test outputs more predictable.
327+
*
328+
* We don't risk setting remote zone equal to ours, since the remote
329+
* server might use a different timezone database.
330+
*/
331+
do_sql_command(conn,"SET timezone = UTC");
332+
333+
/*
334+
* Set values needed to ensure unambiguous data output from remote. (This
335+
* logic should match what pg_dump does. See also set_transmission_modes
336+
* in postgres_fdw.c.)
337+
*/
338+
do_sql_command(conn,"SET datestyle = ISO");
339+
if (remoteversion >=80400)
340+
do_sql_command(conn,"SET intervalstyle = postgres");
341+
if (remoteversion >=90000)
342+
do_sql_command(conn,"SET extra_float_digits = 3");
343+
else
344+
do_sql_command(conn,"SET extra_float_digits = 2");
345+
}
346+
347+
/*
348+
* Convenience subroutine to issue a non-data-returning SQL command to remote
349+
*/
350+
staticvoid
351+
do_sql_command(PGconn*conn,constchar*sql)
352+
{
353+
PGresult*res;
354+
322355
res=PQexec(conn,sql);
323356
if (PQresultStatus(res)!=PGRES_COMMAND_OK)
324357
pgfdw_report_error(ERROR,res, true,sql);
@@ -339,7 +372,6 @@ static void
339372
begin_remote_xact(ConnCacheEntry*entry)
340373
{
341374
intcurlevel=GetCurrentTransactionNestLevel();
342-
PGresult*res;
343375

344376
/* Start main transaction if we haven't yet */
345377
if (entry->xact_depth <=0)
@@ -353,10 +385,7 @@ begin_remote_xact(ConnCacheEntry *entry)
353385
sql="START TRANSACTION ISOLATION LEVEL SERIALIZABLE";
354386
else
355387
sql="START TRANSACTION ISOLATION LEVEL REPEATABLE READ";
356-
res=PQexec(entry->conn,sql);
357-
if (PQresultStatus(res)!=PGRES_COMMAND_OK)
358-
pgfdw_report_error(ERROR,res, true,sql);
359-
PQclear(res);
388+
do_sql_command(entry->conn,sql);
360389
entry->xact_depth=1;
361390
}
362391

@@ -370,10 +399,7 @@ begin_remote_xact(ConnCacheEntry *entry)
370399
charsql[64];
371400

372401
snprintf(sql,sizeof(sql),"SAVEPOINT s%d",entry->xact_depth+1);
373-
res=PQexec(entry->conn,sql);
374-
if (PQresultStatus(res)!=PGRES_COMMAND_OK)
375-
pgfdw_report_error(ERROR,res, true,sql);
376-
PQclear(res);
402+
do_sql_command(entry->conn,sql);
377403
entry->xact_depth++;
378404
}
379405
}
@@ -509,10 +535,7 @@ pgfdw_xact_callback(XactEvent event, void *arg)
509535
{
510536
caseXACT_EVENT_PRE_COMMIT:
511537
/* Commit all remote transactions during pre-commit */
512-
res=PQexec(entry->conn,"COMMIT TRANSACTION");
513-
if (PQresultStatus(res)!=PGRES_COMMAND_OK)
514-
pgfdw_report_error(ERROR,res, true,"COMMIT TRANSACTION");
515-
PQclear(res);
538+
do_sql_command(entry->conn,"COMMIT TRANSACTION");
516539

517540
/*
518541
* If there were any errors in subtransactions, and we made
@@ -647,10 +670,7 @@ pgfdw_subxact_callback(SubXactEvent event, SubTransactionId mySubid,
647670
{
648671
/* Commit all remote subtransactions during pre-commit */
649672
snprintf(sql,sizeof(sql),"RELEASE SAVEPOINT s%d",curlevel);
650-
res=PQexec(entry->conn,sql);
651-
if (PQresultStatus(res)!=PGRES_COMMAND_OK)
652-
pgfdw_report_error(ERROR,res, true,sql);
653-
PQclear(res);
673+
do_sql_command(entry->conn,sql);
654674
}
655675
else
656676
{

‎contrib/postgres_fdw/deparse.c

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -469,8 +469,12 @@ appendWhereClause(StringInfo buf,
469469
List*exprs,
470470
boolis_first)
471471
{
472+
intnestlevel;
472473
ListCell*lc;
473474

475+
/* Make sure any constants in the exprs are printed portably */
476+
nestlevel=set_transmission_modes();
477+
474478
foreach(lc,exprs)
475479
{
476480
RestrictInfo*ri= (RestrictInfo*)lfirst(lc);
@@ -487,6 +491,8 @@ appendWhereClause(StringInfo buf,
487491

488492
is_first= false;
489493
}
494+
495+
reset_transmission_modes(nestlevel);
490496
}
491497

492498
/*

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1983,10 +1983,10 @@ INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive
19831983
ERROR: new row for relation "T 1" violates check constraint "c2positive"
19841984
DETAIL: Failing row contains (1111, -2, null, null, null, (^-^;), null, null).
19851985
CONTEXT: Remote SQL command: INSERT INTO "S 1"."T 1"("C 1", c2) VALUES ($1, $2)
1986-
UPDATE ft1 SET c2 = -c2, c4 = null WHERE c1 = 1; -- c2positive
1986+
UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive
19871987
ERROR: new row for relation "T 1" violates check constraint "c2positive"
1988-
DETAIL: Failing row contains (1, -1, 00001_trig_update,null, 1970-01-02 00:00:00, 1, 1 , foo).
1989-
CONTEXT: Remote SQL command: UPDATE "S 1"."T 1" SET c2 = $2, c4 = $3 WHERE ctid = $1
1988+
DETAIL: Failing row contains (1, -1, 00001_trig_update,1970-01-02 08:00:00+00, 1970-01-02 00:00:00, 1, 1 , foo).
1989+
CONTEXT: Remote SQL command: UPDATE "S 1"."T 1" SET c2 = $2 WHERE ctid = $1
19901990
-- Test savepoint/rollback behavior
19911991
select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
19921992
c2 | count
@@ -2142,10 +2142,10 @@ select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
21422142
(13 rows)
21432143

21442144
savepoint s3;
2145-
update ft2 set c2 = -2, c4 = null where c2 = 42; -- fail on remote side
2145+
update ft2 set c2 = -2 where c2 = 42; -- fail on remote side
21462146
ERROR: new row for relation "T 1" violates check constraint "c2positive"
2147-
DETAIL: Failing row contains (10, -2, 00010_trig_update_trig_update,null, 1970-01-11 00:00:00, 0, 0 , foo).
2148-
CONTEXT: Remote SQL command: UPDATE "S 1"."T 1" SET c2 = $2, c4 = $3 WHERE ctid = $1
2147+
DETAIL: Failing row contains (10, -2, 00010_trig_update_trig_update,1970-01-11 08:00:00+00, 1970-01-11 00:00:00, 0, 0 , foo).
2148+
CONTEXT: Remote SQL command: UPDATE "S 1"."T 1" SET c2 = $2 WHERE ctid = $1
21492149
rollback to savepoint s3;
21502150
select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
21512151
c2 | count

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 64 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,7 @@
3030
#include"optimizer/var.h"
3131
#include"parser/parsetree.h"
3232
#include"utils/builtins.h"
33+
#include"utils/guc.h"
3334
#include"utils/lsyscache.h"
3435
#include"utils/memutils.h"
3536

@@ -1554,9 +1555,12 @@ create_cursor(ForeignScanState *node)
15541555
if (numParams>0&& !fsstate->extparams_done)
15551556
{
15561557
ParamListInfoparams=node->ss.ps.state->es_param_list_info;
1558+
intnestlevel;
15571559
List*param_numbers;
15581560
ListCell*lc;
15591561

1562+
nestlevel=set_transmission_modes();
1563+
15601564
param_numbers= (List*)
15611565
list_nth(fsstate->fdw_private,FdwScanPrivateExternParamIds);
15621566
foreach(lc,param_numbers)
@@ -1598,6 +1602,9 @@ create_cursor(ForeignScanState *node)
15981602
prm->value);
15991603
}
16001604
}
1605+
1606+
reset_transmission_modes(nestlevel);
1607+
16011608
fsstate->extparams_done= true;
16021609
}
16031610

@@ -1705,6 +1712,56 @@ fetch_more_data(ForeignScanState *node)
17051712
MemoryContextSwitchTo(oldcontext);
17061713
}
17071714

1715+
/*
1716+
* Force assorted GUC parameters to settings that ensure that we'll output
1717+
* data values in a form that is unambiguous to the remote server.
1718+
*
1719+
* This is rather expensive and annoying to do once per row, but there's
1720+
* little choice if we want to be sure values are transmitted accurately;
1721+
* we can't leave the settings in place between rows for fear of affecting
1722+
* user-visible computations.
1723+
*
1724+
* We use the equivalent of a function SET option to allow the settings to
1725+
* persist only until the caller calls reset_transmission_modes().If an
1726+
* error is thrown in between, guc.c will take care of undoing the settings.
1727+
*
1728+
* The return value is the nestlevel that must be passed to
1729+
* reset_transmission_modes() to undo things.
1730+
*/
1731+
int
1732+
set_transmission_modes(void)
1733+
{
1734+
intnestlevel=NewGUCNestLevel();
1735+
1736+
/*
1737+
* The values set here should match what pg_dump does.See also
1738+
* configure_remote_session in connection.c.
1739+
*/
1740+
if (DateStyle!=USE_ISO_DATES)
1741+
(void)set_config_option("datestyle","ISO",
1742+
PGC_USERSET,PGC_S_SESSION,
1743+
GUC_ACTION_SAVE, true,0);
1744+
if (IntervalStyle!=INTSTYLE_POSTGRES)
1745+
(void)set_config_option("intervalstyle","postgres",
1746+
PGC_USERSET,PGC_S_SESSION,
1747+
GUC_ACTION_SAVE, true,0);
1748+
if (extra_float_digits<3)
1749+
(void)set_config_option("extra_float_digits","3",
1750+
PGC_USERSET,PGC_S_SESSION,
1751+
GUC_ACTION_SAVE, true,0);
1752+
1753+
returnnestlevel;
1754+
}
1755+
1756+
/*
1757+
* Undo the effects of set_transmission_modes().
1758+
*/
1759+
void
1760+
reset_transmission_modes(intnestlevel)
1761+
{
1762+
AtEOXact_GUC(true,nestlevel);
1763+
}
1764+
17081765
/*
17091766
* Utility routine to close a cursor.
17101767
*/
@@ -1791,16 +1848,20 @@ convert_prep_stmt_params(PgFdwModifyState *fmstate,
17911848
/* 1st parameter should be ctid, if it's in use */
17921849
if (tupleid!=NULL)
17931850
{
1851+
/* don't need set_transmission_modes for TID output */
17941852
p_values[pindex]=OutputFunctionCall(&fmstate->p_flinfo[pindex],
17951853
PointerGetDatum(tupleid));
17961854
pindex++;
17971855
}
17981856

17991857
/* get following parameters from slot */
1800-
if (slot!=NULL)
1858+
if (slot!=NULL&&fmstate->target_attrs!=NIL)
18011859
{
1860+
intnestlevel;
18021861
ListCell*lc;
18031862

1863+
nestlevel=set_transmission_modes();
1864+
18041865
foreach(lc,fmstate->target_attrs)
18051866
{
18061867
intattnum=lfirst_int(lc);
@@ -1815,6 +1876,8 @@ convert_prep_stmt_params(PgFdwModifyState *fmstate,
18151876
value);
18161877
pindex++;
18171878
}
1879+
1880+
reset_transmission_modes(nestlevel);
18181881
}
18191882

18201883
Assert(pindex==fmstate->p_nums);

‎contrib/postgres_fdw/postgres_fdw.h

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,10 @@
2020

2121
#include"libpq-fe.h"
2222

23+
/* in postgres_fdw.c */
24+
externintset_transmission_modes(void);
25+
externvoidreset_transmission_modes(intnestlevel);
26+
2327
/* in connection.c */
2428
externPGconn*GetConnection(ForeignServer*server,UserMapping*user,
2529
boolwill_prep_stmt);

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -316,7 +316,7 @@ ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0);
316316

317317
INSERT INTO ft1(c1, c2)VALUES(11,12);-- duplicate key
318318
INSERT INTO ft1(c1, c2)VALUES(1111,-2);-- c2positive
319-
UPDATE ft1SET c2=-c2, c4=nullWHERE c1=1;-- c2positive
319+
UPDATE ft1SET c2=-c2WHERE c1=1;-- c2positive
320320

321321
-- Test savepoint/rollback behavior
322322
select c2,count(*)from ft2where c2<500group by1order by1;
@@ -337,7 +337,7 @@ select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
337337
release savepoint s2;
338338
select c2,count(*)from ft2where c2<500group by1order by1;
339339
savepoint s3;
340-
update ft2set c2=-2, c4=nullwhere c2=42;-- fail on remote side
340+
update ft2set c2=-2where c2=42;-- fail on remote side
341341
rollback to savepoint s3;
342342
select c2,count(*)from ft2where c2<500group by1order by1;
343343
release savepoint s3;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp