Re: Serialization errors in Postgres 9.4.0

On Thu, Jun 25, 2015 at 04:02:33PM +0000, BRUSSER Michael wrote:
> 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

Would you be so kind as to send along a way to reproduce the problem
you are seeing, ideally a minimal one?

> 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,

Yes

> but it would be helpful to understand why we did not see so many errors in the past.

Changing the transaction isolation level, which was mislabeled
"serializable" in 8.4 but was actually snapshot isolation, can produce
differences.

https://en.wikipedia.org/wiki/Snapshot_isolation

> Did something changed from 8.4.4 to 9.4.0?

Yes. 9.1 introduced SSI, which added serialization checks to the
snapshot isolation that existed before.

https://wiki.postgresql.org/wiki/SSI

> 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 for asking. These questions are quite reasonable.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate