Alexander Korotkov <aekorotkov@gmail.com> wrote:
> when pg_dump is run with both --serializable-deferrable and -j
> options to pg_dump, it returns errors:
>
> pg_dump: [archiver (db)] query failed: ERROR: a snapshot-importing transaction must not be READ ONLY DEFERRABLE
> pg_dump: [archiver (db)] query failed: ERROR: a snapshot-importing transaction must not be READ ONLY DEFERRABLE
> pg_dump: [archiver (db)] query failed: ERROR: a snapshot-importing transaction must not be READ ONLY DEFERRABLE
> pg_dump: [parallel archiver] query was: SET TRANSACTION SNAPSHOT '0001E300-1'
> pg_dump: [archiver (db)] query failed: ERROR: a snapshot-importing transaction must not be READ ONLY DEFERRABLE
>
> I've checked it on 9.4.0 and 9.3.5.
>
> So, these options are incompatible now.
The only way to make them compatible would be to have some way for
an exported snapshot to indicate that it is safe against
serialization anomalies if used for a serializable transaction, and
to only generate an error if a non-safe snapshot is used with the
--serializable-deferrable option.
> Could we start snapshot-importing transaction with repeatable
> read isolation level?
You can if you don't use the option which specifies that you want
serializable behavior. Why specify --serializable-deferrable if
you don't?
> AFAICS, they should read exactly same data as snapshot-exporting
> serializable transaction.
Sort of. The behavior once they have a snapshot and are running is
the same; the difference is whether the snapshot can see a
transient state which would not be consistent with some serial
order of transaction execution. For examples, see:
https://wiki.postgresql.org/wiki/SSI#Read_Only_Transactions
It's really a question of why you want the dump. If it is for
crash recovery, you don't need --serializable-deferrable. If you
want to restore it to run reports that only show states consistent
with some serial execution of serializable transactions you *do*
need --serializable-deferrable, so that the snapshot used for the
dump reflects a point in the commit stream when no serialization
anomalies can be visible to a read-only transaction.
> If not, could pg_dump return some more friendly error before
> actually trying to dump?
Sure, until such time as it is possible to request a
serializable-safe snapshot and flag it as such on export (and check
for that in pg_dump), we could add a validation that these two
options are incompatible. If there are no objections, I can push
something to do that.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company