Re: SKIP_LOCKED test causes random buildfarm failures

Hi,

On 2019-11-06 16:54:38 -0500, Tom Lane wrote:
> Every once in awhile we get failures like this one:
> 
> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=gull&dt=2019-11-05%2008%3A27%3A27
> 
> diff -U3 /home/pgsql/build-farm/buildroot-clang/HEAD/pgsql.build/../pgsql/src/test/regress/expected/vacuum.out
/home/pgsql/build-farm/buildroot-clang/HEAD/pgsql.build/src/test/regress/results/vacuum.out
> --- /home/pgsql/build-farm/buildroot-clang/HEAD/pgsql.build/../pgsql/src/test/regress/expected/vacuum.out
2019-08-1103:02:18.921535948 -0700
 
> +++ /home/pgsql/build-farm/buildroot-clang/HEAD/pgsql.build/src/test/regress/results/vacuum.out    2019-11-05
00:50:42.381244885-0800
 
> @@ -204,6 +204,7 @@
>  -- SKIP_LOCKED option
>  VACUUM (SKIP_LOCKED) vactst;
>  VACUUM (SKIP_LOCKED, FULL) vactst;
> +WARNING:  skipping vacuum of "vactst" --- lock not available
>  ANALYZE (SKIP_LOCKED) vactst;
>  -- ensure VACUUM and ANALYZE don't have a problem with serializable
>  SET default_transaction_isolation = serializable;
> 
> 
> No doubt this is a conflict with autovacuum.  There are two reasonable
> ways to remove the test instability:

I assume you consider disabling autovacuum for that table not a
reasonable approach? Due to the danger that it could end up still
running, e.g. due to anti-wraparound or such?


> * Crank up client_min_messages to more than WARNING for this test
> stanza.

Hm.


> * Downgrade the "skipping" messages to DEBUG1 or less.
> 
> I kind of wonder why we are issuing a "WARNING" when the statement
> does exactly what you asked it to, anyway.  At most I'd expect
> that to be a NOTICE condition.

I don't know what lead us to doing so, but it doesn't seem reasonable to
allow the user to see whether the table has actually been vacuumed. I
would assume that one uses SKIP_LOCKED partially to avoid unnecessary
impacts in production due to other tasks starting to block on e.g. a
VACUUM FULL, even though without the "ordered queueing" everything could
just go on working fine. I'm not sure that indicates whether WARNING or
NOTICE is the best choice.

So I'd be inclined to go with the client_min_messages approach?


Greetings,

Andres Freund