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

Daniel Farina <daniel(at)heroku(dot)com> writes:
> On Sun, Mar 10, 2013 at 12:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Hmm ... the buildfarm just rubbed my nose in a more immediate issue,
>> which is that postgres_fdw is vulnerable to problems if the remote
>> server is using different GUC settings than it is for things like
>> timezone and datestyle.

> Forgive my naivety: why would a timestamptz value not be passed
> through the _in/_out function locally at least once (hence, respecting
> local GUCs) when using the FDW? Is the problem overhead in not
> wanting to parse and re-format the value on the local server?

No, the problem is that ambiguous dates may be transferred incorrectly
to or from the remote server. Once a timestamp value is inside our
server, we are responsible for getting it to the remote end accurately,
IMO.

Here's an example using the "loopback" server that's set up by the
postgres_fdw regression test:

contrib_regression=# show datestyle; -- this is the style the "remote" session will be using
DateStyle
-----------
ISO, MDY
(1 row)

contrib_regression=# create table remote (f1 timestamptz);
CREATE TABLE
contrib_regression=# create foreign table ft (f1 timestamptz) server loopback options (table_name 'remote');
CREATE FOREIGN TABLE
contrib_regression=# set datestyle = german, dmy;
SET
contrib_regression=# select now();
now
--------------------------------
11.03.2013 09:40:17.401173 EDT
(1 row)

contrib_regression=# insert into ft values(now());
INSERT 0 1
contrib_regression=# select *, now() from ft;
f1 | now
--------------------------------+-------------------------------
03.11.2013 08:40:58.682679 EST | 11.03.2013 09:41:30.96724 EDT
(1 row)

The remote end has entirely misinterpreted the day vs month fields.
Now, to hit this you need to be using a datestyle which will print
in an ambiguous format, so the "ISO" and "Postgres" styles are
not vulnerable; but "German" and "SQL" are.

> I suppose that means any non-immutable in/out function pair may have
> to be carefully considered, and the list is despairingly long...but
> finite:

A look at pg_dump says that it only worries about setting datestyle,
intervalstyle, and extra_float_digits.

regards, tom lane