Serialization errors in Postgres 9.4.0

From: BRUSSER Michael <Michael(dot)BRUSSER(at)3ds(dot)com>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Serialization errors in Postgres 9.4.0
Date: 2015-06-25 16:02:33
Message-ID: B09192DCAA24E1439E57F73035042CB82DA0BBBA@AG-DCC-MBX11.dsone.3ds.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Some time ago we upgraded from v. 8.4.4 to 9.4.0 and we are seeing errors in some regression tests.

LOCATION: exec_simple_query, postgres.c:887
ERROR: 40001: could not serialize access due to read/write dependencies among transactions

Detail can be one of these:
DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
DETAIL: Reason code: Canceled on identification as a pivot, during write.
DETAIL: Reason code: Canceled on identification as a pivot, during conflict out checking.

Location is usually one of these:
LOCATION: PreCommit_CheckForSerializationFailure, predicate.c:4654
LOCATION: OnConflict_CheckForSerializationFailure, predicate.c:4600
LOCATION: CheckForSerializableConflictOut, predicate.c:3888

It may end up with
ERROR: 25P02: current transaction is aborted, commands ignored until end of transaction block

The database at this point is likely to have a barrage of read and write ops against few tables.
I understand that using serializable transaction we probably should be ready to retry,
but it would be helpful to understand why we did not see so many errors in the past.

Did something changed from 8.4.4 to 9.4.0?
Maybe Postgres has more aggressive predicate locking mechanism now?
Can it be that because of the small table size it performs sequential scan and locks the entire table?

Sorry if any of these questions are plain stupid.
Thanks,
Michael.

This email and any attachments are intended solely for the use of the individual or entity to whom it is addressed and may be confidential and/or privileged.

If you are not one of the named recipients or have received this email in error,

(i) you should not read, disclose, or copy it,

(ii) please notify sender of your receipt by reply email and delete this email and all attachments,

(iii) Dassault Systemes does not accept or assume any liability or responsibility for any use of or reliance on this email.

For other languages, go to http://www.3ds.com/terms/email-disclaimer

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jeff Janes 2015-06-25 16:06:31 Re: pgbench - allow backslash-continuations in custom scripts
Previous Message Sawada Masahiko 2015-06-25 15:49:06 Re: Support for N synchronous standby servers - take 2