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

On Sun, Mar 10, 2013 at 12:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I wrote:
>> There's a lot left to do here of course. One thing I was wondering
>> about was why we don't allow DEFAULTs to be attached to foreign-table
>> columns. There was no use in it before, but it seems sensible enough
>> now.
>
> 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. The failure seen here:
> http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=rover_firefly&dt=2013-03-10%2018%3A30%3A00
> is basically just cosmetic, but it's not hard to imagine non-cosmetic
> problems coming up. For instance, suppose our instance is running in
> DMY datestyle and transmits an ambiguous date to a remote running in
> MDY datestyle.
>
> We could consider sending our settings to the remote at connection
> establishment, but that doesn't seem terribly bulletproof --- what if
> someone does a local SET later? What seems safer is to set the remote
> to ISO style always, but then we have to figure out how to get the local
> timestamptz_out to emit that style without touching our local GUC.
> Ugh.

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?

Although it seems that if GUCs affected *parsing* then the problem
comes back again, since one may choose to canonicalize output from a
remote server (e.g. via setting ISO time in all cases) but should the
user have set up GUCs to interpret input in a particular way for a
data type that is not enough.

As-is this situation is already technically true for timestamptz in
the case of time stamps without any explicit time offset or time zone,
but since timestamptz_out doesn't ever render without the offset
(right?) it's not a problem. Close shave, though, and one that an
extension author could easily find themselves on the wrong side of.

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:

SELECT proname
FROM pg_proc WHERE EXISTS
(SELECT 1
FROM pg_type
WHERE pg_proc.oid = pg_type.typinput OR
pg_proc.oid = pg_type.typoutput OR
pg_proc.oid = pg_type.typsend OR
pg_proc.oid = pg_type.typreceive)
AND provolatile != 'i';

> (One more reason why GUCs that affect application-visible semantics are
dangerous.)

:(

--
fdr