Re: [HACKERS] SERIALIZABLE on standby servers

On 7 November 2016 at 23:56, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com> wrote:

> The patch works by teaching the standby how to do somethings similar
> to what SERIALIZABLE READ ONLY DEFERRABLE does on the primary server,
> with some help from the primary server in the form of extra
> information in the WAL.

This is going in a direction I'm interested in, though I had some
independent thoughts.

> The basic idea is: the standby should wait until a point in the
> transaction stream where it can take a snapshot and either (1) there
> were no concurrent read/write SERIALIZABLE transactions running on the
> primary, or (2) the last concurrent read/write SERIALIZABLE
> transaction at snapshot time has now ended without creating a
> dangerous cycle with our transaction.
>
> In case (1), the primary sometimes adds an extra
> xl_xact_snapshot_safety struct to commit messages which says 'a
> snapshot taken after this commit and before any future SSI commits is
> safe, because there are no concurrent read/write SSI transactions at
> this moment'.

That has the problem that we need to decorate every commit. I'd be
looking for a way to make the default case the safe path, so we add
little new data to WAL.

ISTM possible to insert a wait prior to the commit record when we
detect that our commit would cause a pivot failure, so we actively try
to avoid unsafe orderings.

The purpose of that is to 1) ensure that the WAL ordering is a
Serializable ordering in most cases. The wait would have a timeout for
the cases where we can't determine safety. But is also has purpose 2)
reduce the number of serialization failures on primary.

Once we know that the WAL sequence is serializable we can use that
information for both physical and logical.

> In case (2), the xl_xact_snapshot_safety struct embedded in a commit
> record instead says 'a snapshot taken after this commit and before any
> future SSI commits is of unknown safety, because there are concurrent
> transactions; I'll tell you when it has been determined; please
> remember this token'. The token (which happens to be a CSN but that
> is not important) will appear in a future independent snapshot safety
> message which says whether a snapshot taken at that time is safe or
> unsafe.
>
> Note that xl_xact_snapshot_safety is embedded in the commit messages
> (for SSI transactions only), but also appears as its own WAL record to
> report the final state of a token from an earlier commit. So if you
> do a lot of non-overlapping writable SSI transactions, you'll get just
> a few extra bytes in each commit record, but overlapping transactions
> will generate a stream of extra snapshot safety messages, one for each
> commit involved.

I would want to add the Serilializable ordering info, not just "it is safe".

I think we'd need to add a timeout to the deferrable snapshot request,
because it could be hours.

> In order to generate follow-up snapshot safety messages, the patch
> creates 'hypothetical' transactions on the primary whenever a
> writeable SSI transaction commits, so that it can figure out whether
> such a transaction would conflict. These phantom transactions are
> proxies for any transaction that may be created on a standby at the
> same point in the transaction stream (with respect to SSI commits) on
> any standby, and survive in memory just until they are found to be
> safe or unsafe.

That needs a lot more explanation to determine the soundness of that
theory. README++

I think we need some isolationtester tests that generate various
orderings and then standby tests to check that works.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services