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

Commitf248206

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 parentb8f4599 commitf248206

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
@@ -47,6 +47,7 @@
4747
#include"utils/acl.h"
4848
#include"utils/builtins.h"
4949
#include"utils/fmgroids.h"
50+
#include"utils/guc.h"
5051
#include"utils/lsyscache.h"
5152
#include"utils/memutils.h"
5253
#include"utils/rel.h"
@@ -80,7 +81,8 @@ typedef struct storeInfo
8081
*/
8182
staticDatumdblink_record_internal(FunctionCallInfofcinfo,boolis_async);
8283
staticvoidprepTuplestoreResult(FunctionCallInfofcinfo);
83-
staticvoidmaterializeResult(FunctionCallInfofcinfo,PGresult*res);
84+
staticvoidmaterializeResult(FunctionCallInfofcinfo,PGconn*conn,
85+
PGresult*res);
8486
staticvoidmaterializeQueryResult(FunctionCallInfofcinfo,
8587
PGconn*conn,
8688
constchar*conname,
@@ -110,6 +112,8 @@ static char *escape_param_str(const char *from);
110112
staticvoidvalidate_pkattnums(Relationrel,
111113
int2vector*pkattnums_arg,int32pknumatts_arg,
112114
int**pkattnums,int*pknumatts);
115+
staticintapplyRemoteGucs(PGconn*conn);
116+
staticvoidrestoreLocalGucs(intnestlevel);
113117

114118
/* Global */
115119
staticremoteConn*pconn=NULL;
@@ -597,7 +601,7 @@ dblink_fetch(PG_FUNCTION_ARGS)
597601
errmsg("cursor \"%s\" does not exist",curname)));
598602
}
599603

600-
materializeResult(fcinfo,res);
604+
materializeResult(fcinfo,conn,res);
601605
return (Datum)0;
602606
}
603607

@@ -742,7 +746,7 @@ dblink_record_internal(FunctionCallInfo fcinfo, bool is_async)
742746
}
743747
else
744748
{
745-
materializeResult(fcinfo,res);
749+
materializeResult(fcinfo,conn,res);
746750
}
747751
}
748752
}
@@ -798,7 +802,7 @@ prepTuplestoreResult(FunctionCallInfo fcinfo)
798802
* The PGresult will be released in this function.
799803
*/
800804
staticvoid
801-
materializeResult(FunctionCallInfofcinfo,PGresult*res)
805+
materializeResult(FunctionCallInfofcinfo,PGconn*conn,PGresult*res)
802806
{
803807
ReturnSetInfo*rsinfo= (ReturnSetInfo*)fcinfo->resultinfo;
804808

@@ -808,7 +812,7 @@ materializeResult(FunctionCallInfo fcinfo, PGresult *res)
808812
PG_TRY();
809813
{
810814
TupleDesctupdesc;
811-
boolis_sql_cmd= false;
815+
boolis_sql_cmd;
812816
intntuples;
813817
intnfields;
814818

@@ -869,13 +873,18 @@ materializeResult(FunctionCallInfo fcinfo, PGresult *res)
869873
if (ntuples>0)
870874
{
871875
AttInMetadata*attinmeta;
876+
intnestlevel=-1;
872877
Tuplestorestate*tupstore;
873878
MemoryContextoldcontext;
874879
introw;
875880
char**values;
876881

877882
attinmeta=TupleDescGetAttInMetadata(tupdesc);
878883

884+
/* Set GUCs to ensure we read GUC-sensitive data types correctly */
885+
if (!is_sql_cmd)
886+
nestlevel=applyRemoteGucs(conn);
887+
879888
oldcontext=MemoryContextSwitchTo(
880889
rsinfo->econtext->ecxt_per_query_memory);
881890
tupstore=tuplestore_begin_heap(true, false,work_mem);
@@ -912,6 +921,9 @@ materializeResult(FunctionCallInfo fcinfo, PGresult *res)
912921
tuplestore_puttuple(tupstore,tuple);
913922
}
914923

924+
/* clean up GUC settings, if we changed any */
925+
restoreLocalGucs(nestlevel);
926+
915927
/* clean up and return the tuplestore */
916928
tuplestore_donestoring(tupstore);
917929
}
@@ -1045,6 +1057,7 @@ static PGresult *
10451057
storeQueryResult(storeInfo*sinfo,PGconn*conn,constchar*sql)
10461058
{
10471059
boolfirst= true;
1060+
intnestlevel=-1;
10481061
PGresult*res;
10491062

10501063
if (!PQsendQuery(conn,sql))
@@ -1064,6 +1077,15 @@ storeQueryResult(storeInfo *sinfo, PGconn *conn, const char *sql)
10641077
if (PQresultStatus(sinfo->cur_res)==PGRES_SINGLE_TUPLE)
10651078
{
10661079
/* got one row from possibly-bigger resultset */
1080+
1081+
/*
1082+
* Set GUCs to ensure we read GUC-sensitive data types correctly.
1083+
* We shouldn't do this until we have a row in hand, to ensure
1084+
* libpq has seen any earlier ParameterStatus protocol messages.
1085+
*/
1086+
if (first&&nestlevel<0)
1087+
nestlevel=applyRemoteGucs(conn);
1088+
10671089
storeRow(sinfo,sinfo->cur_res,first);
10681090

10691091
PQclear(sinfo->cur_res);
@@ -1084,6 +1106,9 @@ storeQueryResult(storeInfo *sinfo, PGconn *conn, const char *sql)
10841106
}
10851107
}
10861108

1109+
/* clean up GUC settings, if we changed any */
1110+
restoreLocalGucs(nestlevel);
1111+
10871112
/* return last_res */
10881113
res=sinfo->last_res;
10891114
sinfo->last_res=NULL;
@@ -2765,3 +2790,73 @@ validate_pkattnums(Relation rel,
27652790
errmsg("invalid attribute number %d",pkattnum)));
27662791
}
27672792
}
2793+
2794+
/*
2795+
* Copy the remote session's values of GUCs that affect datatype I/O
2796+
* and apply them locally in a new GUC nesting level. Returns the new
2797+
* nestlevel (which is needed by restoreLocalGucs to undo the settings),
2798+
* or -1 if no new nestlevel was needed.
2799+
*
2800+
* We use the equivalent of a function SET option to allow the settings to
2801+
* persist only until the caller calls restoreLocalGucs. If an error is
2802+
* thrown in between, guc.c will take care of undoing the settings.
2803+
*/
2804+
staticint
2805+
applyRemoteGucs(PGconn*conn)
2806+
{
2807+
staticconstchar*constGUCsAffectingIO[]= {
2808+
"DateStyle",
2809+
"IntervalStyle"
2810+
};
2811+
2812+
intnestlevel=-1;
2813+
inti;
2814+
2815+
for (i=0;i<lengthof(GUCsAffectingIO);i++)
2816+
{
2817+
constchar*gucName=GUCsAffectingIO[i];
2818+
constchar*remoteVal=PQparameterStatus(conn,gucName);
2819+
constchar*localVal;
2820+
2821+
/*
2822+
* If the remote server is pre-8.4, it won't have IntervalStyle, but
2823+
* that's okay because its output format won't be ambiguous. So just
2824+
* skip the GUC if we don't get a value for it. (We might eventually
2825+
* need more complicated logic with remote-version checks here.)
2826+
*/
2827+
if (remoteVal==NULL)
2828+
continue;
2829+
2830+
/*
2831+
* Avoid GUC-setting overhead if the remote and local GUCs already
2832+
* have the same value.
2833+
*/
2834+
localVal=GetConfigOption(gucName, false, false);
2835+
Assert(localVal!=NULL);
2836+
2837+
if (strcmp(remoteVal,localVal)==0)
2838+
continue;
2839+
2840+
/* Create new GUC nest level if we didn't already */
2841+
if (nestlevel<0)
2842+
nestlevel=NewGUCNestLevel();
2843+
2844+
/* Apply the option (this will throw error on failure) */
2845+
(void)set_config_option(gucName,remoteVal,
2846+
PGC_USERSET,PGC_S_SESSION,
2847+
GUC_ACTION_SAVE, true,0);
2848+
}
2849+
2850+
returnnestlevel;
2851+
}
2852+
2853+
/*
2854+
* Restore local GUCs after they have been overlaid with remote settings.
2855+
*/
2856+
staticvoid
2857+
restoreLocalGucs(intnestlevel)
2858+
{
2859+
/* Do nothing if no new nestlevel was created */
2860+
if (nestlevel>0)
2861+
AtEOXact_GUC(true,nestlevel);
2862+
}

‎contrib/dblink/expected/dblink.out

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

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

0 commit comments

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