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

Commit8a3b677

Browse files
committed
Fix contrib/dblink to handle inconsistent DateStyle/IntervalStyle safely.
If the remote database's settings of these GUCs are different from ours,ambiguous datetime values may be read incorrectly. To fix, temporarilyadopt the remote server's settings while we ingest a query result.This is not a complete fix, since it doesn't do anything about ambiguousvalues in commands sent to the remote server; but there seems little wecan do about that end of it given dblink's entirely textual API fortransmitted commands.Back-patch to 9.2. The hazard exists in all versions, but this patchwould need more work to apply before 9.2. Given the lack of fieldcomplaints about this issue, it doesn't seem worth the effort at present.Daniel Farina and Tom Lane
1 parent549dae0 commit8a3b677

File tree

3 files changed

+372
-5
lines changed

3 files changed

+372
-5
lines changed

‎contrib/dblink/dblink.c

Lines changed: 100 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,7 @@
5353
#include"utils/acl.h"
5454
#include"utils/builtins.h"
5555
#include"utils/fmgroids.h"
56+
#include"utils/guc.h"
5657
#include"utils/lsyscache.h"
5758
#include"utils/memutils.h"
5859
#include"utils/rel.h"
@@ -86,7 +87,8 @@ typedef struct storeInfo
8687
*/
8788
staticDatumdblink_record_internal(FunctionCallInfofcinfo,boolis_async);
8889
staticvoidprepTuplestoreResult(FunctionCallInfofcinfo);
89-
staticvoidmaterializeResult(FunctionCallInfofcinfo,PGresult*res);
90+
staticvoidmaterializeResult(FunctionCallInfofcinfo,PGconn*conn,
91+
PGresult*res);
9092
staticvoidmaterializeQueryResult(FunctionCallInfofcinfo,
9193
PGconn*conn,
9294
constchar*conname,
@@ -118,6 +120,8 @@ static void validate_pkattnums(Relation rel,
118120
int**pkattnums,int*pknumatts);
119121
staticboolis_valid_dblink_option(constPQconninfoOption*options,
120122
constchar*option,Oidcontext);
123+
staticintapplyRemoteGucs(PGconn*conn);
124+
staticvoidrestoreLocalGucs(intnestlevel);
121125

122126
/* Global */
123127
staticremoteConn*pconn=NULL;
@@ -605,7 +609,7 @@ dblink_fetch(PG_FUNCTION_ARGS)
605609
errmsg("cursor \"%s\" does not exist",curname)));
606610
}
607611

608-
materializeResult(fcinfo,res);
612+
materializeResult(fcinfo,conn,res);
609613
return (Datum)0;
610614
}
611615

@@ -750,7 +754,7 @@ dblink_record_internal(FunctionCallInfo fcinfo, bool is_async)
750754
}
751755
else
752756
{
753-
materializeResult(fcinfo,res);
757+
materializeResult(fcinfo,conn,res);
754758
}
755759
}
756760
}
@@ -806,7 +810,7 @@ prepTuplestoreResult(FunctionCallInfo fcinfo)
806810
* The PGresult will be released in this function.
807811
*/
808812
staticvoid
809-
materializeResult(FunctionCallInfofcinfo,PGresult*res)
813+
materializeResult(FunctionCallInfofcinfo,PGconn*conn,PGresult*res)
810814
{
811815
ReturnSetInfo*rsinfo= (ReturnSetInfo*)fcinfo->resultinfo;
812816

@@ -816,7 +820,7 @@ materializeResult(FunctionCallInfo fcinfo, PGresult *res)
816820
PG_TRY();
817821
{
818822
TupleDesctupdesc;
819-
boolis_sql_cmd= false;
823+
boolis_sql_cmd;
820824
intntuples;
821825
intnfields;
822826

@@ -877,13 +881,18 @@ materializeResult(FunctionCallInfo fcinfo, PGresult *res)
877881
if (ntuples>0)
878882
{
879883
AttInMetadata*attinmeta;
884+
intnestlevel=-1;
880885
Tuplestorestate*tupstore;
881886
MemoryContextoldcontext;
882887
introw;
883888
char**values;
884889

885890
attinmeta=TupleDescGetAttInMetadata(tupdesc);
886891

892+
/* Set GUCs to ensure we read GUC-sensitive data types correctly */
893+
if (!is_sql_cmd)
894+
nestlevel=applyRemoteGucs(conn);
895+
887896
oldcontext=MemoryContextSwitchTo(
888897
rsinfo->econtext->ecxt_per_query_memory);
889898
tupstore=tuplestore_begin_heap(true, false,work_mem);
@@ -920,6 +929,9 @@ materializeResult(FunctionCallInfo fcinfo, PGresult *res)
920929
tuplestore_puttuple(tupstore,tuple);
921930
}
922931

932+
/* clean up GUC settings, if we changed any */
933+
restoreLocalGucs(nestlevel);
934+
923935
/* clean up and return the tuplestore */
924936
tuplestore_donestoring(tupstore);
925937
}
@@ -1053,6 +1065,7 @@ static PGresult *
10531065
storeQueryResult(storeInfo*sinfo,PGconn*conn,constchar*sql)
10541066
{
10551067
boolfirst= true;
1068+
intnestlevel=-1;
10561069
PGresult*res;
10571070

10581071
if (!PQsendQuery(conn,sql))
@@ -1072,6 +1085,15 @@ storeQueryResult(storeInfo *sinfo, PGconn *conn, const char *sql)
10721085
if (PQresultStatus(sinfo->cur_res)==PGRES_SINGLE_TUPLE)
10731086
{
10741087
/* got one row from possibly-bigger resultset */
1088+
1089+
/*
1090+
* Set GUCs to ensure we read GUC-sensitive data types correctly.
1091+
* We shouldn't do this until we have a row in hand, to ensure
1092+
* libpq has seen any earlier ParameterStatus protocol messages.
1093+
*/
1094+
if (first&&nestlevel<0)
1095+
nestlevel=applyRemoteGucs(conn);
1096+
10751097
storeRow(sinfo,sinfo->cur_res,first);
10761098

10771099
PQclear(sinfo->cur_res);
@@ -1092,6 +1114,9 @@ storeQueryResult(storeInfo *sinfo, PGconn *conn, const char *sql)
10921114
}
10931115
}
10941116

1117+
/* clean up GUC settings, if we changed any */
1118+
restoreLocalGucs(nestlevel);
1119+
10951120
/* return last_res */
10961121
res=sinfo->last_res;
10971122
sinfo->last_res=NULL;
@@ -2898,3 +2923,73 @@ is_valid_dblink_option(const PQconninfoOption *options, const char *option,
28982923

28992924
return true;
29002925
}
2926+
2927+
/*
2928+
* Copy the remote session's values of GUCs that affect datatype I/O
2929+
* and apply them locally in a new GUC nesting level. Returns the new
2930+
* nestlevel (which is needed by restoreLocalGucs to undo the settings),
2931+
* or -1 if no new nestlevel was needed.
2932+
*
2933+
* We use the equivalent of a function SET option to allow the settings to
2934+
* persist only until the caller calls restoreLocalGucs. If an error is
2935+
* thrown in between, guc.c will take care of undoing the settings.
2936+
*/
2937+
staticint
2938+
applyRemoteGucs(PGconn*conn)
2939+
{
2940+
staticconstchar*constGUCsAffectingIO[]= {
2941+
"DateStyle",
2942+
"IntervalStyle"
2943+
};
2944+
2945+
intnestlevel=-1;
2946+
inti;
2947+
2948+
for (i=0;i<lengthof(GUCsAffectingIO);i++)
2949+
{
2950+
constchar*gucName=GUCsAffectingIO[i];
2951+
constchar*remoteVal=PQparameterStatus(conn,gucName);
2952+
constchar*localVal;
2953+
2954+
/*
2955+
* If the remote server is pre-8.4, it won't have IntervalStyle, but
2956+
* that's okay because its output format won't be ambiguous. So just
2957+
* skip the GUC if we don't get a value for it. (We might eventually
2958+
* need more complicated logic with remote-version checks here.)
2959+
*/
2960+
if (remoteVal==NULL)
2961+
continue;
2962+
2963+
/*
2964+
* Avoid GUC-setting overhead if the remote and local GUCs already
2965+
* have the same value.
2966+
*/
2967+
localVal=GetConfigOption(gucName, false, false);
2968+
Assert(localVal!=NULL);
2969+
2970+
if (strcmp(remoteVal,localVal)==0)
2971+
continue;
2972+
2973+
/* Create new GUC nest level if we didn't already */
2974+
if (nestlevel<0)
2975+
nestlevel=NewGUCNestLevel();
2976+
2977+
/* Apply the option (this will throw error on failure) */
2978+
(void)set_config_option(gucName,remoteVal,
2979+
PGC_USERSET,PGC_S_SESSION,
2980+
GUC_ACTION_SAVE, true,0);
2981+
}
2982+
2983+
returnnestlevel;
2984+
}
2985+
2986+
/*
2987+
* Restore local GUCs after they have been overlaid with remote settings.
2988+
*/
2989+
staticvoid
2990+
restoreLocalGucs(intnestlevel)
2991+
{
2992+
/* Do nothing if no new nestlevel was created */
2993+
if (nestlevel>0)
2994+
AtEOXact_GUC(true,nestlevel);
2995+
}

‎contrib/dblink/expected/dblink.out

Lines changed: 176 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -913,3 +913,179 @@ SELECT dblink_build_sql_delete('test_dropped', '1', 1,
913913
DELETE FROM test_dropped WHERE id = '2'
914914
(1 row)
915915

916+
-- test local mimicry of remote GUC values that affect datatype I/O
917+
SET datestyle = ISO, MDY;
918+
SET intervalstyle = postgres;
919+
SET timezone = UTC;
920+
SELECT dblink_connect('myconn','dbname=contrib_regression');
921+
dblink_connect
922+
----------------
923+
OK
924+
(1 row)
925+
926+
SELECT dblink_exec('myconn', 'SET datestyle = GERMAN, DMY;');
927+
dblink_exec
928+
-------------
929+
SET
930+
(1 row)
931+
932+
-- single row synchronous case
933+
SELECT *
934+
FROM dblink('myconn',
935+
'SELECT * FROM (VALUES (''12.03.2013 00:00:00+00'')) t')
936+
AS t(a timestamptz);
937+
a
938+
------------------------
939+
2013-03-12 00:00:00+00
940+
(1 row)
941+
942+
-- multi-row synchronous case
943+
SELECT *
944+
FROM dblink('myconn',
945+
'SELECT * FROM
946+
(VALUES (''12.03.2013 00:00:00+00''),
947+
(''12.03.2013 00:00:00+00'')) t')
948+
AS t(a timestamptz);
949+
a
950+
------------------------
951+
2013-03-12 00:00:00+00
952+
2013-03-12 00:00:00+00
953+
(2 rows)
954+
955+
-- single-row asynchronous case
956+
SELECT *
957+
FROM dblink_send_query('myconn',
958+
'SELECT * FROM
959+
(VALUES (''12.03.2013 00:00:00+00'')) t');
960+
dblink_send_query
961+
-------------------
962+
1
963+
(1 row)
964+
965+
CREATE TEMPORARY TABLE result AS
966+
(SELECT * from dblink_get_result('myconn') as t(t timestamptz))
967+
UNION ALL
968+
(SELECT * from dblink_get_result('myconn') as t(t timestamptz));
969+
SELECT * FROM result;
970+
t
971+
------------------------
972+
2013-03-12 00:00:00+00
973+
(1 row)
974+
975+
DROP TABLE result;
976+
-- multi-row asynchronous case
977+
SELECT *
978+
FROM dblink_send_query('myconn',
979+
'SELECT * FROM
980+
(VALUES (''12.03.2013 00:00:00+00''),
981+
(''12.03.2013 00:00:00+00'')) t');
982+
dblink_send_query
983+
-------------------
984+
1
985+
(1 row)
986+
987+
CREATE TEMPORARY TABLE result AS
988+
(SELECT * from dblink_get_result('myconn') as t(t timestamptz))
989+
UNION ALL
990+
(SELECT * from dblink_get_result('myconn') as t(t timestamptz))
991+
UNION ALL
992+
(SELECT * from dblink_get_result('myconn') as t(t timestamptz));
993+
SELECT * FROM result;
994+
t
995+
------------------------
996+
2013-03-12 00:00:00+00
997+
2013-03-12 00:00:00+00
998+
(2 rows)
999+
1000+
DROP TABLE result;
1001+
-- Try an ambiguous interval
1002+
SELECT dblink_exec('myconn', 'SET intervalstyle = sql_standard;');
1003+
dblink_exec
1004+
-------------
1005+
SET
1006+
(1 row)
1007+
1008+
SELECT *
1009+
FROM dblink('myconn',
1010+
'SELECT * FROM (VALUES (''-1 2:03:04'')) i')
1011+
AS i(i interval);
1012+
i
1013+
-------------------
1014+
-1 days -02:03:04
1015+
(1 row)
1016+
1017+
-- Try swapping to another format to ensure the GUCs are tracked
1018+
-- properly through a change.
1019+
CREATE TEMPORARY TABLE result (t timestamptz);
1020+
SELECT dblink_exec('myconn', 'SET datestyle = ISO, MDY;');
1021+
dblink_exec
1022+
-------------
1023+
SET
1024+
(1 row)
1025+
1026+
INSERT INTO result
1027+
SELECT *
1028+
FROM dblink('myconn',
1029+
'SELECT * FROM (VALUES (''03.12.2013 00:00:00+00'')) t')
1030+
AS t(a timestamptz);
1031+
SELECT dblink_exec('myconn', 'SET datestyle = GERMAN, DMY;');
1032+
dblink_exec
1033+
-------------
1034+
SET
1035+
(1 row)
1036+
1037+
INSERT INTO result
1038+
SELECT *
1039+
FROM dblink('myconn',
1040+
'SELECT * FROM (VALUES (''12.03.2013 00:00:00+00'')) t')
1041+
AS t(a timestamptz);
1042+
SELECT * FROM result;
1043+
t
1044+
------------------------
1045+
2013-03-12 00:00:00+00
1046+
2013-03-12 00:00:00+00
1047+
(2 rows)
1048+
1049+
DROP TABLE result;
1050+
-- Check error throwing in dblink_fetch
1051+
SELECT dblink_open('myconn','error_cursor',
1052+
'SELECT * FROM (VALUES (''1''), (''not an int'')) AS t(text);');
1053+
dblink_open
1054+
-------------
1055+
OK
1056+
(1 row)
1057+
1058+
SELECT *
1059+
FROM dblink_fetch('myconn','error_cursor', 1) AS t(i int);
1060+
i
1061+
---
1062+
1
1063+
(1 row)
1064+
1065+
SELECT *
1066+
FROM dblink_fetch('myconn','error_cursor', 1) AS t(i int);
1067+
ERROR: invalid input syntax for integer: "not an int"
1068+
-- Make sure that the local settings have retained their values in spite
1069+
-- of shenanigans on the connection.
1070+
SHOW datestyle;
1071+
DateStyle
1072+
-----------
1073+
ISO, MDY
1074+
(1 row)
1075+
1076+
SHOW intervalstyle;
1077+
IntervalStyle
1078+
---------------
1079+
postgres
1080+
(1 row)
1081+
1082+
-- Clean up GUC-setting tests
1083+
SELECT dblink_disconnect('myconn');
1084+
dblink_disconnect
1085+
-------------------
1086+
OK
1087+
(1 row)
1088+
1089+
RESET datestyle;
1090+
RESET intervalstyle;
1091+
RESET timezone;

0 commit comments

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