Skip to content

Commit f248206

Browse files
tglsfdctglsfdc
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, temporarily adopt the remote server's settings while we ingest a query result. This is not a complete fix, since it doesn't do anything about ambiguous values in commands sent to the remote server; but there seems little we can do about that end of it given dblink's entirely textual API for transmitted commands. Back-patch to 9.2. The hazard exists in all versions, but this patch would need more work to apply before 9.2. Given the lack of field complaints about this issue, it doesn't seem worth the effort at present. Daniel Farina and Tom Lane
1 parent b8f4599 commit f248206

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
static Datum dblink_record_internal(FunctionCallInfo fcinfo, bool is_async);
8283
static void prepTuplestoreResult(FunctionCallInfo fcinfo);
83-
static void materializeResult(FunctionCallInfo fcinfo, PGresult *res);
84+
static void materializeResult(FunctionCallInfo fcinfo, PGconn *conn,
85+
PGresult *res);
8486
static void materializeQueryResult(FunctionCallInfo fcinfo,
8587
PGconn *conn,
8688
const char *conname,
@@ -110,6 +112,8 @@ static char *escape_param_str(const char *from);
110112
static void validate_pkattnums(Relation rel,
111113
int2vector *pkattnums_arg, int32 pknumatts_arg,
112114
int **pkattnums, int *pknumatts);
115+
static int applyRemoteGucs(PGconn *conn);
116+
static void restoreLocalGucs(int nestlevel);
113117

114118
/* Global */
115119
static remoteConn *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
static void
801-
materializeResult(FunctionCallInfo fcinfo, PGresult *res)
805+
materializeResult(FunctionCallInfo fcinfo, 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
TupleDesc tupdesc;
811-
bool is_sql_cmd = false;
815+
bool is_sql_cmd;
812816
int ntuples;
813817
int nfields;
814818

@@ -869,13 +873,18 @@ materializeResult(FunctionCallInfo fcinfo, PGresult *res)
869873
if (ntuples > 0)
870874
{
871875
AttInMetadata *attinmeta;
876+
int nestlevel = -1;
872877
Tuplestorestate *tupstore;
873878
MemoryContext oldcontext;
874879
int row;
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, const char *sql)
10461058
{
10471059
bool first = true;
1060+
int nestlevel = -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+
static int
2805+
applyRemoteGucs(PGconn *conn)
2806+
{
2807+
static const char *const GUCsAffectingIO[] = {
2808+
"DateStyle",
2809+
"IntervalStyle"
2810+
};
2811+
2812+
int nestlevel = -1;
2813+
int i;
2814+
2815+
for (i = 0; i < lengthof(GUCsAffectingIO); i++)
2816+
{
2817+
const char *gucName = GUCsAffectingIO[i];
2818+
const char *remoteVal = PQparameterStatus(conn, gucName);
2819+
const char *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+
return nestlevel;
2851+
}
2852+
2853+
/*
2854+
* Restore local GUCs after they have been overlaid with remote settings.
2855+
*/
2856+
static void
2857+
restoreLocalGucs(int nestlevel)
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)