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" <[email protected]> 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" <[email protected]>
>>>>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 [email protected]
>>>>> End Point Corporation
>>>>> PGP Key: 0x14964AC8
>>>>
>>>> _______________________________________________
>>>> Slony1-general mailing list
>>>> [email protected]
>>>> http://lists.slony.info/mailman/listinfo/slony1-general
>>>>
>>>
>>> _______________________________________________
>>> Slony1-general mailing list
>>> [email protected]
>>> http://lists.slony.info/mailman/listinfo/slony1-general
>>>
>>
>>
>
_______________________________________________
Slony1-general mailing list
[email protected]
http://lists.slony.info/mailman/listinfo/slony1-general
