Re: Somebody has not thought through subscription locking considerations

On 30/03/17 07:25, Tom Lane wrote:
> I noticed this failure report:
> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=dangomushi&dt=2017-03-29%2019%3A45%3A27
>
> in which we find
>
> *** /home/buildfarm/data/buildroot/HEAD/pgsql.build/src/test/regress/expected/updatable_views.out Thu Mar 30 04:45:43 2017
> --- /home/buildfarm/data/buildroot/HEAD/pgsql.build/src/test/regress/results/updatable_views.out Thu Mar 30 05:32:37 2017
> ***************
> *** 349,354 ****
> --- 349,358 ----
> DROP VIEW ro_view10, ro_view12, ro_view18;
> DROP SEQUENCE seq CASCADE;
> NOTICE: drop cascades to view ro_view19
> + ERROR: deadlock detected
> + DETAIL: Process 7576 waits for AccessShareLock on relation 1259 of database 16384; blocked by process 7577.
> + Process 7577 waits for ShareRowExclusiveLock on relation 6102 of database 16384; blocked by process 7576.
> + HINT: See server log for query details.
> -- simple updatable view
> CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
> INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i);
>
> and the referenced bit of log is
>
> [58dc19dd.1d98:175] ERROR: deadlock detected
> [58dc19dd.1d98:176] DETAIL: Process 7576 waits for AccessShareLock on relation 1259 of database 16384; blocked by process 7577.
> Process 7577 waits for ShareRowExclusiveLock on relation 6102 of database 16384; blocked by process 7576.
> Process 7576: DROP SEQUENCE seq CASCADE;
> Process 7577: VACUUM FULL pg_class;
> [58dc19dd.1d98:177] HINT: See server log for query details.
> [58dc19dd.1d98:178] STATEMENT: DROP SEQUENCE seq CASCADE;
>
> Of course, 1259 is pg_class and 6102 is pg_subscription_rel.
>
> I await with interest an explanation of what "VACUUM FULL pg_class" is
> doing trying to acquire ShareRowExclusiveLock on pg_subscription_rel, not
> to mention why a DROP SEQUENCE is holding some fairly strong lock on that
> relation. *Especially* in a situation where no subscriptions exist ---
> but even if any did, this seems unacceptable on its face. Access to core
> catalogs like pg_class cannot depend on random other stuff.
>

Hmm, the DROP SEQUENCE is result of not having dependency info for
relations/subscriptions I think. I was told during review it's needless
bloat of dependency catalog. I guess we should revisit that. It's also
likely that RemoveSubscriptionRel will work fine with lower lock level.

I have no idea why VACUUM FULL of pg_class would touch the
pg_subscription_rel though, I'll have to dig into that.

I can see that locking for example pg_trigger or pg_depend in
ShareRowExclusiveLock will also block VACUUM FULL on pg_class (and other
related tables like pg_attribute). So maybe we just need to be careful
about not taking such a strong lock...

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services