Attached are two patches that should allow Slony 2.0.7 to work with PG 9.1
I've run slony with these patches through a number of runs of the
clustertest suites and everything works as expected (lots of other bugs
fixed in 2.1 and 2.2 are still present)
Steve
>From 57c307d52c08394d30f4c94374a6e072ec4265fb Mon Sep 17 00:00:00 2001
From: Steve Singer <ssin...@ca.afilias.info>
Date: Thu, 21 Mar 2013 07:54:40 -0400
Subject: [PATCH 1/2] Revert "Revert "Bug 255.""
This reverts commit 5d52c57d691776e0888f8bbc774931f8edc997de.
This brings back the fix for Bug 255. READ COMMITTED is used
instead of SERIALIZABLE to avoid SSI pivot failures with PG 9.1
---
RELEASE | 4 ++++
src/slon/remote_listen.c | 23 +++++++++++++++++++++++
src/slon/remote_worker.c | 18 +++++++++++-------
src/slonik/slonik.c | 12 ++++++------
4 files changed, 44 insertions(+), 13 deletions(-)
diff --git a/RELEASE b/RELEASE
index fe6a46c..4c2d77c 100644
--- a/RELEASE
+++ b/RELEASE
@@ -367,6 +367,10 @@ Added post rc2
- Bug 236 :: Fix default formatting of timestamp in logs
+- Add express support to recognize PostgreSQL 9.1
+
+- Bug 255 :: Fix serialization issues when using PostgreSQL 9.1
+
Post Release Candidate 1
- Bug 260 :: Fix issue with FAILOVER when failing over an origin with
diff --git a/src/slon/remote_listen.c b/src/slon/remote_listen.c
index 6649579..b99fbae 100644
--- a/src/slon/remote_listen.c
+++ b/src/slon/remote_listen.c
@@ -292,6 +292,29 @@ remoteListenThread_main(void *cdata)
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);
diff --git a/src/slon/remote_worker.c b/src/slon/remote_worker.c
index 4957069..eb53efa 100644
--- a/src/slon/remote_worker.c
+++ b/src/slon/remote_worker.c
@@ -547,7 +547,7 @@ remoteWorkerThread_main(void *cdata)
*/
(void) slon_mkquery(&query1,
"begin transaction; "
- "set transaction isolation level serializable; ");
+ "set transaction isolation level read committed; ");
/*
* Event type specific processing
@@ -1081,7 +1081,7 @@ remoteWorkerThread_main(void *cdata)
/* Start the transaction again */
(void) slon_mkquery(&query3,
"begin transaction; "
- "set transaction isolation level serializable; ");
+ "set transaction isolation level read committed; ");
slon_appendquery(&query1,
"lock table %s.sl_config_lock; ",
rtcfg_namespace);
@@ -1281,7 +1281,7 @@ remoteWorkerThread_main(void *cdata)
* it was released above.
*/
slon_mkquery(&query1, "start transaction;"
- "set transaction isolation level serializable;");
+ "set transaction isolation level read committed;");
slon_appendquery(&query1,
"lock table %s.sl_config_lock; ",
rtcfg_namespace);
@@ -1303,7 +1303,7 @@ remoteWorkerThread_main(void *cdata)
if(copy_set_retries != 0)
{
slon_mkquery(&query1, "start transaction;"
- "set transaction isolation level serializable;");
+ "set transaction isolation level read committed;");
slon_appendquery(&query1,
"lock table %s.sl_config_lock; ",
rtcfg_namespace);
@@ -2644,11 +2644,13 @@ copy_set(SlonNode *node, SlonConn *local_conn, int set_id,
*/
if (sub_provider == set_origin)
{
+ int provider_version=PQserverVersion(pro_dbconn);
(void) slon_mkquery(&query1,
"start transaction; "
- "set transaction isolation level serializable; "
+ "set transaction isolation level serializable read only %s; "
"select \"pg_catalog\".txid_snapshot_xmin(\"pg_catalog\".txid_current_snapshot()) <= '%s'; ",
- event->ev_maxtxid_c);
+ provider_version>=90100 ? "deferrable" : ""
+ ,event->ev_maxtxid_c);
res1 = PQexec(pro_dbconn, dstring_data(&query1));
if (PQresultStatus(res1) != PGRES_TUPLES_OK)
{
@@ -2684,9 +2686,11 @@ copy_set(SlonNode *node, SlonConn *local_conn, int set_id,
}
else
{
+ int provider_version=PQserverVersion(pro_dbconn);
(void) slon_mkquery(&query1,
"start transaction; "
- "set transaction isolation level serializable; ");
+ "set transaction isolation level serializable read only %s; ",
+ provider_version >= 90100 ? "deferrable" : "" );
if (query_execute(node, pro_dbconn, &query1) < 0)
{
slon_disconnectdb(pro_conn);
diff --git a/src/slonik/slonik.c b/src/slonik/slonik.c
index 254a99e..796e638 100644
--- a/src/slonik/slonik.c
+++ b/src/slonik/slonik.c
@@ -1811,15 +1811,15 @@ load_slony_base(SlonikStmt * stmt, int no_id)
use_major = 8;
use_minor = 4;
}
- else if ((adminfo->pg_version >= 90000) && (adminfo->pg_version < 0100)) /* 9.0 */
+ else if ((adminfo->pg_version >= 90000) && (adminfo->pg_version < 90200)) /* 9.x */
{
/**
- * 9.0 and so far just like 8.4
+ * 9.0 and 9.1 are so far just like 8.4
**/
use_major=8;
use_minor=4;
}
- else /* 9.1 or higher */
+ else /* above 8.4 ??? */
{
use_major = 8;
use_minor = 4;
@@ -1896,15 +1896,15 @@ load_slony_functions(SlonikStmt * stmt, int no_id)
use_major = 8;
use_minor = 4;
}
- else if ((adminfo->pg_version >= 90000) && (adminfo->pg_version < 90100)) /* 9.0 */
+ else if ((adminfo->pg_version >= 90000) && (adminfo->pg_version < 90200)) /* 9.0 */
{
/**
- * 9.0 is so far just like 8.4
+ * 9.0 and 9.1 are so far just like 8.4
*/
use_major = 8;
use_minor = 4;
}
- else /* above 9.1 */
+ else /* above 8.4 */
{
use_major = 8;
use_minor = 4;
--
1.7.0.4
>From 6aa6f44a73227e5b6a1c28f3b12184f9a0be0f10 Mon Sep 17 00:00:00 2001
From: Steve Singer <ssin...@ca.afilias.info>
Date: Thu, 21 Mar 2013 07:56:30 -0400
Subject: [PATCH 2/2] Bug 285 fix move set race condition.
During a move set it is possible on subscriber node, that isn't the
old or new origin, for remoteWorkerThread_$oldorigin to start
processing a SYNC event before the ACCEPT_SET is processed
by the remoteWorkerThread_$neworigin. If the ACCEPT_SET
transaction then commits before the update sl_setsync
query is executed by the remoteWorkertThread_$oldorigin
the update will update a row for the new origin when
it intends to update the old origin. This messes up
sl_setsync.
This change will add a condition to the WHERE clause so
a remoteWorkerThread won't UPDATE rows for a different
origin
Conflicts:
RELEASE
---
src/slon/remote_worker.c | 5 ++---
1 files changed, 2 insertions(+), 3 deletions(-)
diff --git a/src/slon/remote_worker.c b/src/slon/remote_worker.c
index eb53efa..d7db2d2 100644
--- a/src/slon/remote_worker.c
+++ b/src/slon/remote_worker.c
@@ -632,7 +632,6 @@ remoteWorkerThread_main(void *cdata)
slon_mkquery(&query2, "rollback transaction; ");
query_execute(node, local_dbconn, &query2);
dstring_reset(&query2);
-
slon_retry();
}
}
@@ -4472,9 +4471,9 @@ sync_event(SlonNode *node, SlonConn *local_conn,
"update %s.sl_setsync set "
" ssy_seqno = '%s', ssy_snapshot = '%s', "
" ssy_action_list = '' "
- "where ssy_setid in (",
+ "where ssy_origin=%d and ssy_setid in (",
rtcfg_namespace,
- seqbuf, event->ev_snapshot_c);
+ seqbuf, event->ev_snapshot_c,node->no_id);
i = 0;
for (provider = wd->provider_head; provider; provider = provider->next)
{
--
1.7.0.4
