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

Поиск
Список
Период
Сортировка
От Jov
Тема Re: Can we specify transaction level when connectting to external postgresql server via postgres_fdw
Дата
Msg-id CADyrUxOHdsz3BXM8XFKYvTyaacWKROxJXg1FXEYYc4VTM0urhw@mail.gmail.com
обсуждение исходный текст
Ответ на Can we specify transaction level when connectting to external postgresql server via postgres_fdw  ("guxiaobo1982" <guxiaobo1982@qq.com>)
Список pgsql-general
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 /*
363  * Start remote transaction or subtransaction, if needed.
364  *
365  * Note that we always use at least REPEATABLE READ in the remote session.
366  * This is so that, if a query initiates multiple scans of the same or
367  * different foreign tables, we will get snapshot-consistent results from
368  * those scans.  A disadvantage is that we can't provide sane emulation of
369  * READ COMMITTED behavior --- it would be nice if we had some other way to
370  * control which remote queries share a snapshot.
371  */
372 static void
373 begin_remote_xact(ConnCacheEntry *entry)
375     int         curlevel = GetCurrentTransactionNestLevel();
377     /* Start main transaction if we haven't yet */
378     if (entry->xact_depth <= 0)
379     {
380         const char *sql;
382         elog(DEBUG3, "starting remote transaction on connection %p",
383              entry->conn);
385         if (IsolationIsSerializable())
386             sql = "START TRANSACTION ISOLATION LEVEL SERIALIZABLE";
387         else
388             sql = "START TRANSACTION ISOLATION LEVEL REPEATABLE READ";
389         do_sql_command(entry->conn, sql);
390         entry->xact_depth = 1;
391     }

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




2013/7/11 guxiaobo1982 <guxiaobo1982@qq.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



В списке pgsql-general по дате отправления:

Предыдущее
От: "guxiaobo1982"
Дата:
Сообщение: Can we specify transaction level when connectting to external postgresql server via postgres_fdw
Следующее
От: Jayadevan M
Дата:
Сообщение: pg recovery