On 11-11-20 05:47 AM, Cédric Villemain wrote:
2011/11/20 Steve Singer<[email protected]>:
I wonder out load if the slony remote worker needs to be in a
serializable transaction or if READ COMMITTED is good enough.
I also wonder if making the remote listener threads explicit read only
transactions would help.
if it is a read only transaction, then it is preferable to set it explicitely.
If I well understood Heikki, it should remove 'pivot' situation. See
http://wiki.postgresql.org/images/4/4f/SSI-PGConfEU2011.pdf (slide 22
for the immediate suggestions)
The attached patch makes the remoteListener read only and seems to
eliminate the pivot situations involving sl_event and sl_confirm.
Making the remote worker READ COMMITTED seemed to break things.
Maxim do you have a test environment that you can reproduce this
situation in, does the attach patch help?
The disorder tests against a 9.1 machine hit many of these read/write
dependencies in a test run. Slony just retries things and it
eventually works but I can see how a 500,000 item batch means that you
might often encounter an issue before finishing a SYNC.
My unverified guess is that changing this won't break slony but might
effect applications querying the slave that depend on the serializable
behaviour and maybe it should be a configuration parameter.
There are two places in remote_worker.c where it places the local
connection to serializable.
_______________________________________________
Slony1-general mailing list
[email protected]
http://lists.slony.info/mailman/listinfo/slony1-general
diff --git a/src/slon/remote_listen.c b/src/slon/remote_listen.c
index 2c8e425..229c341 100644
*** a/src/slon/remote_listen.c
--- b/src/slon/remote_listen.c
*************** remoteListenThread_main(void *cdata)
*** 293,298 ****
--- 293,321 ----
continue;
}
+ if(PQserverVersion(dbconn) >= 90100)
+ {
+ slon_mkquery(&query1,"SET SESSION CHARACTERISTICS AS TRANSACTION read only deferrable");
+ res = PQexec(dbconn, dstring_data(&query1));
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ slon_log(SLON_ERROR,
+ "remoteListenThread_%d: \"%s\" - %s",
+ node->no_id,
+ dstring_data(&query1), PQresultErrorMessage(res));
+ PQclear(res);
+ slon_disconnectdb(conn);
+ free(conn_conninfo);
+ conn = NULL;
+ conn_conninfo = NULL;
+ rc = sched_msleep(node, pa_connretry * 1000);
+ if (rc != SCHED_STATUS_OK && rc != SCHED_STATUS_CANCEL)
+ break;
+
+ continue;
+ }
+
+ }
slon_log(SLON_DEBUG1,
"remoteListenThread_%d: connected to '%s'\n",
node->no_id, conn_conninfo);
_______________________________________________
Slony1-general mailing list
[email protected]
http://lists.slony.info/mailman/listinfo/slony1-general
