Re: Reply: Can we specify transaction level when connectting toexternal postgresql server via postgres_fdw

There is another situation,

We have a demo table with about 17000000 rows, the "select count(1) from demotable" statement finishes with-in 3 seconds when executed directlly against the Greenplum database,but it takes about 230 seconds to finish when executed via postgres_fdw inside PostgreSQL 9.3 beta2, I guess that it may because of postgres_fdw pulling data to the PostgreSQL instance and counts the rows there, but I think the query optimizer should pass through the count() function to the Greenplum end, and gets only the result back.

Regards,

Xiaobo Gu

------------------ Original ------------------
From: "guxiaobo1982"<guxiaobo1982(at)qq(dot)com>;
Date: Jul 16, 2013
To: "Tom Lane"<tgl(at)sss(dot)pgh(dot)pa(dot)us>;
Cc: "Jov"<amutu(at)amutu(dot)com>; "pgsql-general"<pgsql-general(at)postgresql(dot)org>;
Subject: Reply: Reply: [GENERAL] Can we specify transaction level when connectting toexternal postgresql server via postgres_fdw

Greeplum is based on 8.2.15, so we can only use the read-only option.

------------------ Original ------------------
Sender: "Tom Lane"<tgl(at)sss(dot)pgh(dot)pa(dot)us>;
Send time: Tuesday, Jul 16, 2013 1:57 PM
To: "guxiaobo1982"<guxiaobo1982(at)qq(dot)com>;
Cc: "Jov"<amutu(at)amutu(dot)com>; "pgsql-general"<pgsql-general(at)postgresql(dot)org>;
Subject: Re: Reply: [GENERAL] Can we specify transaction level when connectting toexternal postgresql server via postgres_fdw

"=?gb18030?B?Z3V4aWFvYm8xOTgy?=" <guxiaobo1982(at)qq(dot)com> writes:
> It works for insert and select statement under serializable level, but it seems update/delete statements are not support, is that true?
> template1=# update user_info set info='1234' where id=102;
> ERROR: DECLARE CURSOR ... FOR UPDATE/SHARE is not supported
> DETAIL: Cursors must be READ ONLY.

The postgres_fdw documentation says

postgres_fdw can be used with remote servers dating back to
PostgreSQL 8.3. Read-only capability is available back to 8.1.

I don't recall exactly when Greenplum forked off from Postgres, but
8.1 or so wouldn't surprise me. The quoted error message looks about
like what you'd get when trying to use postgres_fdw with a pre-8.3
remote server.

regards, tom lane
.