Horton

Thread

Commits

  1. Fix rare deadlock failure in create_am regression test.

  1. Rare deadlock failure in create_am test

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-09-04T02:13:27Z

    conchuela just showed an unusual failure:

https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=conchuela&dt=2020-09-03%2023%3A00%3A36

The core of it is a deadlock failure in create_am.sql; there's then
some follow-on noise from not having successfully dropped the test AM.
The deadlock looks like:

2020-09-04 01:05:06.904 CEST [609175:34] pg_regress/create_am LOG:  process 609175 detected deadlock while waiting for AccessExclusiveLock on relation 17347 of database 16384 after 4616.873 ms
2020-09-04 01:05:06.904 CEST [609175:35] pg_regress/create_am DETAIL:  Process holding the lock: 609183. Wait queue: .
2020-09-04 01:05:06.904 CEST [609175:36] pg_regress/create_am STATEMENT:  DROP ACCESS METHOD gist2 CASCADE;
2020-09-04 01:05:06.904 CEST [609175:37] pg_regress/create_am ERROR:  deadlock detected
2020-09-04 01:05:06.904 CEST [609175:38] pg_regress/create_am DETAIL:  Process 609175 waits for AccessExclusiveLock on relation 17347 of database 16384; blocked by process 609183.
	Process 609183 waits for RowExclusiveLock on relation 20095 of database 16384; blocked by process 609175.
	Process 609175: DROP ACCESS METHOD gist2 CASCADE;
	Process 609183: autovacuum: VACUUM ANALYZE public.fast_emp4000
2020-09-04 01:05:06.904 CEST [609175:39] pg_regress/create_am HINT:  See server log for query details.
2020-09-04 01:05:06.904 CEST [609175:40] pg_regress/create_am STATEMENT:  DROP ACCESS METHOD gist2 CASCADE;
2020-09-04 01:05:06.904 CEST [609183:11] LOG:  process 609183 acquired RowExclusiveLock on relation 20095 of database 16384 after 13377.776 ms
2020-09-04 01:04:52.895 CEST [609183:6] LOG:  automatic analyze of table "regression.public.tenk2" system usage: CPU: user: 0.03 s, system: 0.00 s, elapsed: 0.59 s

So it's not hard to understand the problem: DROP of an index AM, cascading
to an index, will need to acquire lock on the index and then lock on the
index's table.  Any other operation on the table, like say autovacuum,
is going to acquire locks in the other direction.

This is pretty rare, but not unheard of:

https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=grison&dt=2020-03-24%2022%3A00%3A23

(There might be more such failures, but I only looked back six months,
and these two are all I found in that window.)

I'm inclined to think that the best fix is to put

begin;
lock table tenk2;
...
commit;

around the DROP CASCADE.  We could alternatively disable autovacuum on
tenk2, but that could have undesirable side-effects.

			regards, tom lane

  2. Re: Rare deadlock failure in create_am test

    Thomas Munro <thomas.munro@gmail.com> — 2020-09-04T10:02:08Z

    On Fri, Sep 4, 2020 at 2:13 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> (There might be more such failures, but I only looked back six months,
> and these two are all I found in that window.)

Right:

 https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=conchuela&dt=2020-09-03%2023:00:36
 | HEAD          | DragonflyBSD
 https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=lapwing&dt=2020-04-08%2004:38:34
   | HEAD          | Debian
 https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=tern&dt=2020-03-30%2013:54:05
      | HEAD          | AIX
 https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=grison&dt=2020-03-24%2022:00:23
    | HEAD          | Raspbian
 https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=culicidae&dt=2020-03-03%2016:10:06
 | HEAD          | Debian
 https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=longfin&dt=2019-12-11%2023:59:06
   | HEAD          | macOS
 https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=longfin&dt=2019-12-11%2022:09:07
   | HEAD          | macOS
 https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=fairywren&dt=2019-12-10%2003:46:12
 | REL9_6_STABLE |
 https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=mandrill&dt=2019-07-10%2023:19:12
  | REL9_6_STABLE | AIX
 https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=dangomushi&dt=2018-06-23%2018:00:15
| HEAD          | Arch

> I'm inclined to think that the best fix is to put
>
> begin;
> lock table tenk2;
> ...
> commit;

Makes sense, except s/tenk2/fast_emp4000/, no?

  3. Re: Rare deadlock failure in create_am test

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-09-04T13:58:35Z

    Thomas Munro <thomas.munro@gmail.com> writes:
> On Fri, Sep 4, 2020 at 2:13 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I'm inclined to think that the best fix is to put
>> 
>> begin;
>> lock table tenk2;
>> ...
>> commit;

> Makes sense, except s/tenk2/fast_emp4000/, no?

Sorry, thinko ...

			regards, tom lane

  4. Re: Rare deadlock failure in create_am test

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2020-09-04T14:20:33Z

    On 2020-Sep-03, Tom Lane wrote:

> So it's not hard to understand the problem: DROP of an index AM, cascading
> to an index, will need to acquire lock on the index and then lock on the
> index's table.  Any other operation on the table, like say autovacuum,
> is going to acquire locks in the other direction.

Oh, of course.

> I'm inclined to think that the best fix is to put
> 
> begin;
> lock table [fast_emp4000];
> ...
> commit;
> 
> around the DROP CASCADE.

Yeah, sounds good.

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

  5. Re: Rare deadlock failure in create_am test

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-09-04T16:41:23Z

    Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> On 2020-Sep-03, Tom Lane wrote:
>> I'm inclined to think that the best fix is to put
>> 
>> begin;
>> lock table [fast_emp4000];
>> ...
>> commit;
>> 
>> around the DROP CASCADE.

> Yeah, sounds good.

Done.

			regards, tom lane

  6. Re: Rare deadlock failure in create_am test

    Andres Freund <andres@anarazel.de> — 2020-09-08T17:56:26Z

    On 2020-09-04 12:41:23 -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > On 2020-Sep-03, Tom Lane wrote:
> >> I'm inclined to think that the best fix is to put
> >> 
> >> begin;
> >> lock table [fast_emp4000];
> >> ...
> >> commit;
> >> 
> >> around the DROP CASCADE.
> 
> > Yeah, sounds good.
> 
> Done.

Thanks!