pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedRare deadlock failure in create_am test
6+ messages / 4 participants
[nested] [flat]
Commits from this thread:
2020-09-04 d54f99e415 aa4eeb38f3 43b6f3ed8b b2fcaed66a 44f4986dd9 afec6ba0ba Fix rare deadlock failure in create_am regression test.
* Rare deadlock failure in create_am test
@ 2020-09-04 02:13 Tom Lane <tgl@sss.pgh.pa.us>
2020-09-04 10:02 ` Re: Rare deadlock failure in create_am test Thomas Munro <thomas.munro@gmail.com>
2020-09-04 14:20 ` Re: Rare deadlock failure in create_am test Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Lane @ 2020-09-04 02:13 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Rare deadlock failure in create_am test
2020-09-04 02:13 Rare deadlock failure in create_am test Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-09-04 10:02 ` Thomas Munro <thomas.munro@gmail.com>
2020-09-04 13:58 ` Re: Rare deadlock failure in create_am test Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Thomas Munro @ 2020-09-04 10:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; Alvaro Herrera <alvherre@2ndquadrant.com>
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?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Rare deadlock failure in create_am test
2020-09-04 02:13 Rare deadlock failure in create_am test Tom Lane <tgl@sss.pgh.pa.us>
2020-09-04 10:02 ` Re: Rare deadlock failure in create_am test Thomas Munro <thomas.munro@gmail.com>
@ 2020-09-04 13:58 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2020-09-04 13:58 UTC (permalink / raw)
To: Thomas Munro <thomas.munro@gmail.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; Alvaro Herrera <alvherre@2ndquadrant.com>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Rare deadlock failure in create_am test
2020-09-04 02:13 Rare deadlock failure in create_am test Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-09-04 14:20 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-09-04 16:41 ` Re: Rare deadlock failure in create_am test Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Alvaro Herrera @ 2020-09-04 14:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Rare deadlock failure in create_am test
2020-09-04 02:13 Rare deadlock failure in create_am test Tom Lane <tgl@sss.pgh.pa.us>
2020-09-04 14:20 ` Re: Rare deadlock failure in create_am test Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2020-09-04 16:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-09-08 17:56 ` Re: Rare deadlock failure in create_am test Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2020-09-04 16:41 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: pgsql-hackers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Rare deadlock failure in create_am test
2020-09-04 02:13 Rare deadlock failure in create_am test Tom Lane <tgl@sss.pgh.pa.us>
2020-09-04 14:20 ` Re: Rare deadlock failure in create_am test Alvaro Herrera <alvherre@2ndquadrant.com>
2020-09-04 16:41 ` Re: Rare deadlock failure in create_am test Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-09-08 17:56 ` Andres Freund <andres@anarazel.de>
0 siblings, 0 replies; 6+ messages in thread
From: Andres Freund @ 2020-09-08 17:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; pgsql-hackers@lists.postgresql.org
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!
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2020-09-08 17:56 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-09-04 02:13 Rare deadlock failure in create_am test Tom Lane <tgl@sss.pgh.pa.us>
2020-09-04 10:02 ` Thomas Munro <thomas.munro@gmail.com>
2020-09-04 13:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-09-04 14:20 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-09-04 16:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-09-08 17:56 ` Andres Freund <andres@anarazel.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox