Postgres Pro
Downloads
Home   >   mailing lists

Thread: [BUGS] Seems bug in postgres_fdw?

[BUGS] Seems bug in postgres_fdw?

From
Sachin Kotwal
Date:
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

Re: [BUGS] Seems bug in postgres_fdw?

From
Tom Lane
Date:
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