Re: postgres_fdw vs data formatting GUCs (was Re: [v9.3] writable foreign tables)

Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> Having said that, I'd still be inclined to try to set the remote's
>> timezone GUC just so that error messages coming back from the remote
>> don't reflect a randomly different timezone, which was the basic issue
>> in the buildfarm failures we saw yesterday. OTOH, there is no guarantee
>> at all that the remote has the same timezone database we do, so it may
>> not know the zone or may think it has different DST rules than we think;
>> so it's not clear how far we can get with that. Maybe we should just
>> set the remote session's timezone to GMT always.

> Yeah, that seems the safest choice. What are the potential drawbacks,
> if any?

Hard to tell if there are any without testing it.

I remembered that there's a relatively inexpensive way to set GUC values
transiently within an operation, which is GUC_ACTION_SAVE; both
extension.c and ri_triggers.c are relying on that. So here's my
proposal for a fix:

* To make the remote end transmit values unambiguously, send SET
commands for the GUCs listed below during remote session setup.
(postgres_fdw is already assuming that such SETs will persist for the
whole session.)

* To make our end transmit values unambiguously, use GUC_ACTION_SAVE to
transiently change the GUCs listed below whenever we are converting
values to text form to send to the remote end. (This would include
deparsing of Const nodes as well as transmission of query parameters.)

* Judging from the precedent of pg_dump, these are the things we ought
to set this way:

DATESTYLE = ISO

INTERVALSTYLE = POSTGRES (skip on remote side, if version < 8.4)

EXTRA_FLOAT_DIGITS = 3 (or 2 on remote side, if version < 9.0)

* In addition I propose we set TIMEZONE = UTC on the remote side only.
This is, I believe, just a cosmetic hack so that timestamptz values
coming back in error messages will be printed consistently; it would let
us revert the kluge solution I put in place for this type of regression
failure:
http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=rover_firefly&dt=2013-03-10%2018%3A30%3A00

BTW, it strikes me that dblink is probably subject to at least some of
these same failure modes. I'm not personally volunteering to fix any
of this in dblink, but maybe someone ought to look into that.

Thoughts?

regards, tom lane