One point to clarify: while a general review of isolation levels could be
helpful, the problem I saw and addressed was specifically in the remote
listener. The remedy I’ve been working with is a one-line change. The
behavior we had without it seems similar to the observation Jan makes in
the bug 336 description.


--- src/slon/remote_listen.c.SAVE	2015-11-12 11:09:06.405693227 -0500
+++ src/slon/remote_listen.c	2015-11-12 11:13:00.157825287 -0500
@@ -318,7 +318,7 @@
 			}
 			if (PQserverVersion(dbconn) >= 90100)
 			{
-				slon_mkquery(&query1, "SET SESSION CHARACTERISTICS AS TRANSACTION
read only isolation level serializable deferrable");
+				slon_mkquery(&query1, "SET SESSION CHARACTERISTICS AS TRANSACTION
read only isolation level repeatable read");
 				res = PQexec(dbconn, dstring_data(&query1));
 				if (PQresultStatus(res) != PGRES_COMMAND_OK)
 				{

	Tom    :-)




On 11/20/15, 10:04 AM, "Steve Singer" <ssinger at ca.afilias.info> wrote:

>On 11/20/2015 09:56 AM, Jan Wieck wrote:
>> Without taking SYNC snapshots in a SERIALIZABLE transaction I believe
>> that a Slony-I replica could suffer the same inconsistency dangers that
>> a pg_dump without --serializable-deferrable can suffer. Namely a replica
>> would not be usable as a source for reporting. From the 9.4 pg_dump
>>docs:
>
>I was wondering if this is actually possible or not.
>
>The remote slon only selects from  sl_event and sl_log_*.  The remote
>worker is only going to see rows that are covered by a snapshot range in
>the SYNC in sl_event.  Rows in sl_log_* might be visible from a
>transaction point of view but they won't be captured by the where
>conditions for pulling from sl_log.
>
>The snapshot for the event in sl_log is done by the local sync
>connection which is a read-write connection not by a slon remote
>read-only connection.
>
>(I'm ignoring copy_set from the above analysis).
>
>
>> 
>> "This option is not beneficial for a dump which is intended only for
>> disaster recovery. It could be useful for a dump used to load a copy of
>> the database for reporting or other read-only load sharing while the
>> original database continues to be updated. Without it the dump may
>> reflect a state which is not consistent with any serial execution of the
>> transactions eventually committed. For example, if batch processing
>> techniques are used, a batch may show as closed in the dump without all
>> of the items which are in the batch appearing."
>> 
>> Changing the default isolation levels(s) may therefore change, what a
>> replica can safely be used for and I believe that creating reports is
>> one of the major use cases. Using options with big, bold, red, flashing
>> warnings in the documentation would be the only way to go.
>> 
>> 
>> Regards, Jan
>> 
>> 
>> 
>>>
>>>>
>>>> 	Tom    :-)
>>>>
>>>>
>>>> On 11/18/15, 10:35 AM, "Greg Sabino Mullane" <greg at endpoint.com>
>>>>wrote:
>>>>
>>>>> On Wed, Nov 18, 2015 at 02:26:15PM +0000, Tom Tignor wrote:
>>>>> ...
>>>>>> Sorry for the delay getting back. Inspired by your questions, I¹ve
>>>>>>been
>>>>>> reading up on SSI, the Cahill paper and slony1 and postgres code.
>>>>> ...
>>>>>
>>>>> It should be pointed out that 9.1 goes EOL (End of Life) in less than
>>>>> a year (Sep 2016), and transaction handling has changed a *lot* since
>>>>> then,
>>>>> so any changes that core Slony makes may not even work for you.
>>>>>
>>>>> (FWIW, I think dropping the isolation level in this particular
>>>>> instance seems safe, however.)
>>>>>
>>>>> --
>>>>> Greg Sabino Mullane greg at endpoint.com
>>>>> End Point Corporation
>>>>> PGP Key: 0x14964AC8
>>>>
>>>> _______________________________________________
>>>> Slony1-general mailing list
>>>> Slony1-general at lists.slony.info
>>>> http://lists.slony.info/mailman/listinfo/slony1-general
>>>>
>>>
>>> _______________________________________________
>>> Slony1-general mailing list
>>> Slony1-general at lists.slony.info
>>> http://lists.slony.info/mailman/listinfo/slony1-general
>>>
>> 
>> 
>