Re: Can we specify transaction level when connectting to external postgresql server via postgres_fdw

from the doc:

The remote transaction uses SERIALIZABLE isolation level when the local
transaction has SERIALIZABLE isolation level; otherwise it uses REPEATABLE
READ isolation level. This choice ensures that if a query performs multiple
table scans on the remote server, it will get snapshot-consistent results
for all the scans. A consequence is that successive queries within a single
transaction will see the same data from the remote server, even if
concurrent updates are occurring on the remote server due to other
activities. That behavior would be expected anyway if the local transaction
usesSERIALIZABLE or REPEATABLE READ isolation level, but it might be
surprising for a READ COMMITTED local transaction. A future PostgreSQL release
might modify these rules.

now It can be set to REPEATABLE READ or higher level，and can't be lower
level.
from the src code :

362<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l362>/*
363<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l363>
* Start remote transaction or subtransaction, if needed.
364<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l364>
*
365<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l365>
* Note that we always use at least REPEATABLE READ in the remote
session.
366<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l366>
* This is so that, if a query initiates multiple scans of the same or
367<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l367>
* different foreign tables, we will get snapshot-consistent results
from
368<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l368>
* those scans. A disadvantage is that we can't provide sane emulation
of
369<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l369>
* READ COMMITTED behavior --- it would be nice if we had some other
way to
370<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l370>
* control which remote queries share a snapshot.
371<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l371>
*/
372<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l372>static
void
373<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l373>begin_remote_xact(ConnCacheEntry
*entry)
374<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l374>{
375<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l375>
int curlevel = GetCurrentTransactionNestLevel();
376<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l376>
377<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l377>
/* Start main transaction if we haven't yet */
378<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l378>
if (entry->xact_depth <= 0)
379<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l379>
{
380<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l380>
const char *sql;
381<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l381>
382<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l382>
elog(DEBUG3, "starting remote transaction on connection %p",
383<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l383>
entry->conn);
384<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l384>
385<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l385>
if (IsolationIsSerializable())
386<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l386>
sql = "START TRANSACTION ISOLATION LEVEL SERIALIZABLE";
387<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l387>
else
388<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l388>
sql = "START TRANSACTION ISOLATION LEVEL REPEATABLE READ";
389<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l389>
do_sql_command(entry->conn, sql);
390<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l390>
entry->xact_depth = 1;
391<http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/postgres_fdw/connection.c;h=ab098bea3af616c55cc7571936a8448b5b8b40d9;hb=f02b14f787980ac5b2f25407e5cd771e6b1a1dfc#l391>
}

it is hard code,so you can modify the code to add READ COMMIT level.but be
carefull the code comment.

Jov
blog: http:amutu.com/blog <http://amutu.com/blog>

2013/7/11 guxiaobo1982 <guxiaobo1982(at)qq(dot)com>

> Hi,
>
> We are try to connect to Greenplum database from PostgreSQL 9.3 beta2 via
> postgres_fdw, but the Greenplum Database does not support REPEATABLE READ
> transactions, can we specify other isolation leves?
>
> Regards,
>
> Xiaobo Gu
>
>
>