Re: UPDATEDs slowing SELECTs in a fully cached database

Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Huh?  If there was never an XID, there's no commit WAL record,
> hence nothing to make asynchronous.

If you look at the RecordTransactionCommit() function in xact.c
you'll see that's not correct.  Currently the commit record has
nothing to do with whether it synchronizes on WAL writes.  In
particular, this section around line 1096 is where the choice is
made:

    if ((wrote_xlog && synchronous_commit > SYNCHRONOUS_COMMIT_OFF)
        || forceSyncCommit || nrels > 0)
    {
        /*
         * Synchronous commit case:

In the OP's test case, wrote_xlog is true, while forceSyncCommit is
false and nrels == 0.

It doesn't seem like commit of a read-only transaction should be a
magical time for pruning WAL entries to hit the disk, so it probably
would work to modify that "if" to not drop into the synchronous
commit code if the transaction is explicitly declared READ ONLY or
if it never acquired an XID; although there's likely some better way
to deal with it.

-Kevin