Re: regression, deadlock in high frequency single-row UPDATE

Andrew Sackville-West wrote:
> Some months later (stupid business priorities...) we have something to
> report on this. We have been able to produce a minimal schema to
> demonstrate the problem, and successfully induced the deadlock using
> pgbench (thanks to Paulo Tanimoto, cc-ed here).
>
> Please see:
>
> https://gist.github.com/andrewsw-janrain/40d1687db013b1e7c3b3
>
> for detailed instructions on how to trigger the deadlock.
>
> I would be thrilled to learn that we've done something wrong here,
> otherwise I think this represents a regression introduced in 9.3.

To avoid having to visit an external URL that might well not exist at
all, and to simplify the test case which still has too many extraneous
details, here's a simpler version that still causes the UPDATE to
deadlock:

-- schema.sql:
create table pktab (id int primary key, data serial not null);
create table fktab (fk int references pktab);
insert into pktab values (1);

-- query.sql:
update pktab set data = default;
insert into fktab values (1);

This doesn't deadlock in 9.2, yet in 9.3 it raises an error in a few
seconds with pgbench -c 16.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services