Seems bug in postgres_fdw?

Started by Sachin Kotwalalmost 9 years ago6 messages

kotsachin@gmail.com

almost 9 years ago

Hi All,

Below are findings for postgres_fdw related to timezone.

-- Remote Sever

Server A (timezone = 'US/Eastern')

Create table public.test_timestamp(
col1 varchar,
ins_ts timestamp without time zone not null default
current_timestamp
) without oids;

( Assume time is 10:00:00 AM EST ) insert into public.test_timestamp( col1
) values ('foo');
select ins_ts from public.test_timestamp;
--- Returns 10:00:00

delete from public.test_timestamp;

-- Local Server
Server B (timezone = 'US/Eastern')

CREATE SERVER server_1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (port
'5433', dbname 'pgbench');

CREATE USER MAPPING FOR PUBLIC SERVER server_1 OPTIONS (password '');

Create foreign table public.foreign_table(
col1 varchar
) SERVER server_1
OPTIONS (schema_name 'public', table_name 'test_timestamp');

( Assume time is 10:00:00 AM EST ) insert into public.foreign_table( col1 )
values ('from remote');

Now, on Server A, again issue
select ins_ts from public.test_timestamp;
--- Returns 15:00:00

Here , Why postgresql takes different time when remote table and foreign
table have different definition for timestamp column?

I think create foreign table should not allowed , If allowed it should give
same result.

--

Thanks and Regards,
Sachin Kotwal

tgl@sss.pgh.pa.us

almost 9 years ago

In reply to: Sachin Kotwal (#1)

Re: Seems bug in postgres_fdw?

Sachin Kotwal <kotsachin@gmail.com> writes:

Here , Why postgresql takes different time when remote table and foreign
table have different definition for timestamp column?

I believe postgres_fdw sets the timezone in its remote session to UTC
for predictability of results. Your table definition is really at fault
for being dependent on what the session timezone is.

Personally I'd make the ins_ts column be timestamp with time zone, but
if you really don't want to do that, you could consider making the default
expression be "current_timestamp AT TIME ZONE 'something'" to force the
rotated value to be in a particular zone.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

davidr@openscg.com

almost 9 years ago

In reply to: Tom Lane (#2)

1 attachment(s)

Re: [BUGS] Seems bug in postgres_fdw?

On Mon, Feb 27, 2017 at 10:10 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Sachin Kotwal <kotsachin@gmail.com> writes:

Here , Why postgresql takes different time when remote table and foreign
table have different definition for timestamp column?

I believe postgres_fdw sets the timezone in its remote session to UTC
for predictability of results. Your table definition is really at fault
for being dependent on what the session timezone is.

Personally I'd make the ins_ts column be timestamp with time zone, but
if you really don't want to do that, you could consider making the default
expression be "current_timestamp AT TIME ZONE 'something'" to force the
rotated value to be in a particular zone.

regards, tom lane

Tom -

Attached is a doc patch that updates the documentation for postgres-fdw to
include the actual values for the 4 session variables that are set. Does
that make sense to clarify?

Thanks
-Dave

Attachments:

0001-Document-postgres-fdw-session-settings-for-parameter.patchtext/x-patch; charset=US-ASCII; name=0001-Document-postgres-fdw-session-settings-for-parameter.patchDownload

From c00f4833993899e0f78b2950358822d4b1f0011a Mon Sep 17 00:00:00 2001
From: David Rader <davidr@openscg.com>
Date: Wed, 1 Mar 2017 16:42:14 -0500
Subject: [PATCH] Document postgres-fdw session settings for parameters

---
 doc/src/sgml/postgres-fdw.sgml | 31 ++++++++++++++++++++++++++-----
 1 file changed, 26 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index b31f373..eeae3cb 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -532,11 +532,32 @@
 
   <para>
    <filename>postgres_fdw</> likewise establishes remote session settings
-   for the parameters <xref linkend="guc-timezone">,
-   <xref linkend="guc-datestyle">, <xref linkend="guc-intervalstyle">,
-   and <xref linkend="guc-extra-float-digits">.  These are less likely
-   to be problematic than <varname>search_path</>, but can be handled
-   with function <literal>SET</> options if the need arises.
+   for the parameters: 
+   <itemizedlist spacing="compact">
+    <listitem>
+     <para>
+      <xref linkend="guc-timezone"> is set to <literal>UTC</>
+     </para>
+    </listitem>
+    <listitem>
+     <para>
+      <xref linkend="guc-datestyle"> is set to <literal>ISO</>
+     </para>
+    </listitem>
+    <listitem>
+     <para>
+      <xref linkend="guc-intervalstyle"> is set to <literal>postgres</>
+     </para>
+    </listitem>
+    <listitem>
+     <para>
+      <xref linkend="guc-extra-float-digits"> is set to <literal>3</> for remote
+      servers 9.0 and newer and is set to <literal>2</> for older versions
+     </para>
+    </listitem>
+   </itemizedlist>
+   These are less likely to be problematic than <varname>search_path</>, but 
+   can be handled with function <literal>SET</> options if the need arises.
   </para>
 
   <para>
-- 
2.5.0

robertmhaas@gmail.com

almost 9 years ago

In reply to: Rader, David (#3)

Re: [BUGS] Seems bug in postgres_fdw?

On Thu, Mar 2, 2017 at 3:28 AM, Rader, David <davidr@openscg.com> wrote:

Attached is a doc patch that updates the documentation for postgres-fdw to
include the actual values for the 4 session variables that are set. Does
that make sense to clarify?

From my point of view, this would be a sensible thing to document,
although I feel like the grammar is not quite right, because after
"establishes remote session settings for the parameters" my brain
expects a list of parameters rather than a list of parameters and the
corresponding values. I think it reads better if you change "for the
parameters" to "for various parameters".

</nitpicking>

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

davidr@openscg.com

almost 9 years ago

In reply to: Robert Haas (#4)

1 attachment(s)

Re: [BUGS] Seems bug in postgres_fdw?

On Sat, Mar 4, 2017 at 12:52 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Mar 2, 2017 at 3:28 AM, Rader, David <davidr@openscg.com> wrote:

Attached is a doc patch that updates the documentation for postgres-fdw

to

include the actual values for the 4 session variables that are set. Does
that make sense to clarify?

From my point of view, this would be a sensible thing to document,
although I feel like the grammar is not quite right, because after
"establishes remote session settings for the parameters" my brain
expects a list of parameters rather than a list of parameters and the
corresponding values. I think it reads better if you change "for the
parameters" to "for various parameters".

</nitpicking>

Revised doc patch attached with various parameters.

Attachments:

0001-Document-postgres-fdw-session-settings-for-parameter.patchtext/x-patch; charset=US-ASCII; name=0001-Document-postgres-fdw-session-settings-for-parameter.patchDownload

From 7bf12d7ed4e38b9c3d37ce2b2f786480f8fd764f Mon Sep 17 00:00:00 2001
From: David Rader <davidr@openscg.com>
Date: Mon, 6 Mar 2017 09:38:52 -0500
Subject: [PATCH] Document postgres-fdw session settings for parameters

---
 doc/src/sgml/postgres-fdw.sgml | 31 ++++++++++++++++++++++++++-----
 1 file changed, 26 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index b31f373..7a9b655 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -532,11 +532,32 @@
 
   <para>
    <filename>postgres_fdw</> likewise establishes remote session settings
-   for the parameters <xref linkend="guc-timezone">,
-   <xref linkend="guc-datestyle">, <xref linkend="guc-intervalstyle">,
-   and <xref linkend="guc-extra-float-digits">.  These are less likely
-   to be problematic than <varname>search_path</>, but can be handled
-   with function <literal>SET</> options if the need arises.
+   for various parameters: 
+   <itemizedlist spacing="compact">
+    <listitem>
+     <para>
+      <xref linkend="guc-timezone"> is set to <literal>UTC</>
+     </para>
+    </listitem>
+    <listitem>
+     <para>
+      <xref linkend="guc-datestyle"> is set to <literal>ISO</>
+     </para>
+    </listitem>
+    <listitem>
+     <para>
+      <xref linkend="guc-intervalstyle"> is set to <literal>postgres</>
+     </para>
+    </listitem>
+    <listitem>
+     <para>
+      <xref linkend="guc-extra-float-digits"> is set to <literal>3</> for remote
+      servers 9.0 and newer and is set to <literal>2</> for older versions
+     </para>
+    </listitem>
+   </itemizedlist>
+   These are less likely to be problematic than <varname>search_path</>, but 
+   can be handled with function <literal>SET</> options if the need arises.
   </para>
 
   <para>
-- 
2.5.0

robertmhaas@gmail.com

almost 9 years ago

In reply to: Rader, David (#5)

Re: [BUGS] Seems bug in postgres_fdw?

On Mon, Mar 6, 2017 at 9:45 AM, Rader, David <davidr@openscg.com> wrote:

Revised doc patch attached with various parameters.

Committed.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers