Обсуждение: Reply: Can we specify transaction level when connectting toexternal postgresql server via postgres_fdw
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.
CONTEXT: Remote SQL command: SELECT id, ctid FROM public.user_info WHERE ((id = 102)) FOR UPDATE
STATEMENT: update user_info set info='1234' where id=102;
ERROR: DECLARE CURSOR ... FOR UPDATE/SHARE is not supported
DETAIL: Cursors must be READ ONLY.
CONTEXT: Remote SQL command: SELECT id, ctid FROM public.user_info WHERE ((id = 102)) FOR UPDATE
template1=# delete from user_info where id=102;
ERROR: DECLARE CURSOR ... FOR UPDATE/SHARE is not supported
DETAIL: Cursors must be READ ONLY.
CONTEXT: Remote SQL command: SELECT ctid FROM public.user_info WHERE ((id = 102)) FOR UPDATE
STATEMENT: delete from user_info where id=102;
ERROR: DECLARE CURSOR ... FOR UPDATE/SHARE is not supported
DETAIL: Cursors must be READ ONLY.
CONTEXT: Remote SQL command: SELECT ctid FROM public.user_info WHERE ((id = 102)) FOR UPDATE
template1=# insert into user_info values(103,'abcde');
INSERT 0 1
template1=# select * from user_info where id=103;
id | info
-----+-------
103 | abcde
(1 row)
template1=# truncate table user_info;
ERROR: "user_info" is not a table
STATEMENT: truncate table user_info;
ERROR: "user_info" is not a table
template1=# truncate foreign table user_info;
ERROR: syntax error at or near "foreign" at character 10
STATEMENT: truncate foreign table user_info;
ERROR: syntax error at or near "foreign"
LINE 1: truncate foreign table user_info;
------------------ Original ------------------
Sender: "Jov"<amutu@amutu.com>;
Send time: Thursday, Jul 11, 2013 2:06 PM
To: "guxiaobo1982"<guxiaobo1982@qq.com>;
Cc: "pgsql-general"<pgsql-general@postgresql.org>;
Subject: Re: [GENERAL] Can we specify transaction level when connectting toexternal 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 /*
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)
374 {
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.
Jov
blog: http:amutu.com/blog
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
"=?gb18030?B?Z3V4aWFvYm8xOTgy?=" <guxiaobo1982@qq.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
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@qq.com>;
Date: Jul 16, 2013
To: "Tom Lane"<tgl@sss.pgh.pa.us>;
Cc: "Jov"<amutu@amutu.com>; "pgsql-general"<pgsql-general@postgresql.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@sss.pgh.pa.us>;
Send time: Tuesday, Jul 16, 2013 1:57 PM
To: "guxiaobo1982"<guxiaobo1982@qq.com>;
Cc: "Jov"<amutu@amutu.com>; "pgsql-general"<pgsql-general@postgresql.org>;
Subject: Re: Reply: [GENERAL] Can we specify transaction level when connectting toexternal postgresql server via postgres_fdw
> 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
.
On Fri, Jul 19, 2013 at 7:47 AM, guxiaobo1982 <guxiaobo1982@qq.com> wrote:
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.
Are you able to create a function in Greenplum which is a wrapper around that count(*) and call that via the postgres_fdw?