[#1011205] Using Npgsql on a Hot Standby node (Streaming replication) -

View Trackers | Bugs | Download .csv | Monitor

2012-06-15 12:51
Submitted by:
Jean David TECHER (dtecher)
Assigned to:
Nobody (None)
Npgsql Version:
Using Npgsql on a Hot Standby node (Streaming replication) -

Detailed description
We are going to migrate from PG 8.2 to PG 9.1

Wiht PG 9.1, Streaming Replication will be used in the following context:

- 1 master (primary)
- 3 slaves (standby)

Our slaves need to be configured in Hot Standby mode so our clients could run read-only queries directly on the slaves.
Npgsql 2.0.6 is used and bundled in our tools.

However Npgsql could not properly used on the slave-side because there are a few commands (unlisten, notify) disallowed on the slave-side.
This command are allowed on the master-side.
It implies that our tools - on the slaves - crashed.

The only solution I found is to commend out all commands (like unlisten, notify) in the following files (see the patch at the end of this bug report)

- src/Npgsql/NpgsqState.cs
- src/Npgsql/NpgsqlConnector.cs

For Npsql 2.0.11 you have to change the expected lines but the patch may be the same.

Then our issue didn't occur: our tool stopped to crash.

Even if this workaround fixes our issue and disallowing synchronization for notifications messages is not used for us, I think it may have a better fix to do that.

Kind regards.

enterprisedb@bp-dev-lyon:/tmp$ cat Npgsql2.0.6_fix_for_PPAS9.1.patch
--- Npgsql2.0.6.src_old/src/Npgsql/NpgsqlState.cs 2009-05-05 20:00:34.000000000 +0200
+++ Npgsql2.0.6.src_new/src/Npgsql/NpgsqlState.cs 2012-06-13 17:22:40.000000000 +0200
@@ -105,7 +105,9 @@ namespace Npgsql
//Qlisten notifytest;notify notifytest;
Stream stm = context.Stream;
string uuidString = "uuid" + Guid.NewGuid().ToString("N");
- PGUtil.WriteString("Qlisten " + uuidString + ";notify " + uuidString + ";", stm);
+ // Fix for PPAS 9.1 - exclude "notify ..." for slave using Streaming Replication
+ //PGUtil.WriteString("Qlisten " + uuidString + ";notify " + uuidString + ";", stm);
+ PGUtil.WriteString("Qlisten " + uuidString +
+ ";", stm);
Queue<byte> buffer = new Queue<byte>();
byte[] convertBuffer = new byte[36];
for (;;)
@@ -128,7 +130,9 @@ namespace Npgsql
case -1:
throw new EndOfStreamException();
case 'Z':
- context.Query(new NpgsqlCommand("UNLISTEN *", context));
+ // Fix for PPAS 9.1 - exclude "UNLISTEN *" for slave using Streaming Replication
+ //context.Query(new NpgsqlCommand("UNLISTEN *", context));
+ context.Query(new NpgsqlCommand("--
+ UNLISTEN *", context));
--- Npgsql2.0.6.src_old/src/Npgsql/NpgsqlConnector.cs 2009-05-29 09:15:20.000000000 +0200
+++ Npgsql2.0.6.src_new/src/Npgsql/NpgsqlConnector.cs 2012-06-13 17:00:36.000000000 +0200
@@ -378,7 +378,9 @@ namespace Npgsql

internal void ReleaseRegisteredListen()
- Query(new NpgsqlCommand("unlisten *", this));
+ // Fix for PPAS 9.1 - exclude "notify ..." for slave using Streaming Replication
+ //Query(new NpgsqlCommand("unlisten *", this));
+ Query(new NpgsqlCommand("-- unlisten *", this));

/// <summary>


Date: 2013-06-18 17:47
Sender: Francisco Figueiredo jr.

From Forum post:

Hi, Luke!

Thank you very much for your feedback. I'm glad to know it is working ok for you. Unfortunately, as this patch comments a code which is needed when working with prepared statements, I'll have to check another way to fix it.

One idea is to catch the error when issuing the unlisten and ignore it. Another possibility is to check if the server connected is in hot standby mode and don't call the unlisten (this check I don't know how to do yet but I'll find it out if needed).
Date: 2013-06-04 10:09
Sender: Luke Hill

Please see

Attempting to close a pooled connection to a hot-standby server throws "NpgsqlException (0x80004005): ERROR: 25006: cannot execute UNLISTEN during recovery".

Could somebody please add a configuration option to Npgsql to skip sending the "UNLISTEN" command to a hot-standby server? This would enable read-only queries to be executed against the standby server using pooled connections. This could be useful for load-balancing scenarios. In the case where we noticed the problem, we were attempting to detect if a [Web] client had been disconnected from the primary/master server and connected to the standby/slave server [in a fail-over scenario].

Francisco Figueiredo jr suggested the successful work-around of turning-off connection pooling [which in our case was done in the C# "App/Web.config" file database connection strings].

Attached Files:


No Changes Have Been Made to This Item

Powered By FusionForge