Alexander Korotkov <aekorot...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
