Re: [Proposal] vacuumdb --schema only - Mailing list pgsql-hackers

Le 08/04/2022 à 02:46, Justin Pryzby a écrit :
> On Wed, Apr 06, 2022 at 07:43:42PM +0200, Gilles Darold wrote:
>> Thanks for the review, all these changes are available in new version v6
>> of the patch and attached here.
> This is failing in CI (except on macos, which is strangely passing).
> http://cfbot.cputube.org/gilles-darold.html
>
>
https://api.cirrus-ci.com/v1/artifact/task/5379693443547136/log/src/bin/scripts/tmp_check/log/regress_log_100_vacuumdb
>
> not ok 59 - vacuumdb --schema "Foo" postgres exit code 0
>
> #   Failed test 'vacuumdb --schema "Foo" postgres exit code 0'
> #   at t/100_vacuumdb.pl line 151.
> not ok 60 - vacuumdb --schema schema only: SQL found in server log
>
> #   Failed test 'vacuumdb --schema schema only: SQL found in server log'
> #   at t/100_vacuumdb.pl line 151.
> #                   '2022-04-06 18:15:36.313 UTC [34857][not initialized] [[unknown]][:0] LOG:  connection received:
host=[local]
> # 2022-04-06 18:15:36.314 UTC [34857][client backend] [[unknown]][3/2801:0] LOG:  connection authorized:
user=postgresdatabase=postgres application_name=100_vacuumdb.pl
 
> # 2022-04-06 18:15:36.318 UTC [34857][client backend] [100_vacuumdb.pl][3/2802:0] LOG:  statement: SELECT
pg_catalog.set_config('search_path','', false);
 
> # 2022-04-06 18:15:36.586 UTC [34857][client backend] [100_vacuumdb.pl][:0] LOG:  disconnection: session time:
0:00:00.273user=postgres database=postgres host=[local]
 
> # '
> #     doesn't match '(?^:VACUUM "Foo".bar)'

I'm surprised because make check do do not reports errors running on an 
Ubuntu 20.04 and CentOs 8:


t/010_clusterdb.pl ........ ok
t/011_clusterdb_all.pl .... ok
t/020_createdb.pl ......... ok
t/040_createuser.pl ....... ok
t/050_dropdb.pl ........... ok
t/070_dropuser.pl ......... ok
t/080_pg_isready.pl ....... ok
t/090_reindexdb.pl ........ ok
t/091_reindexdb_all.pl .... ok
t/100_vacuumdb.pl ......... ok
t/101_vacuumdb_all.pl ..... ok
t/102_vacuumdb_stages.pl .. ok
t/200_connstr.pl .......... ok
All tests successful.
Files=13, Tests=233, 17 wallclock secs ( 0.09 usr  0.02 sys + 6.63 cusr  
2.68 csys =  9.42 CPU)
Result: PASS


In tmp_check/log/regress_log_100_vacuumdb:

# Running: vacuumdb --schema "Foo" postgres
vacuumdb: vacuuming database "postgres"
ok 59 - vacuumdb --schema "Foo" postgres exit code 0
ok 60 - vacuumdb --schema schema only: SQL found in server log

In PG log:

2022-04-08 11:01:44.519 CEST [17223] 100_vacuumdb.pl LOG: statement: 
RESET search_path;
2022-04-08 11:01:44.519 CEST [17223] 100_vacuumdb.pl LOG: statement: 
WITH listed_objects (object_oid, column_list) AS (
           VALUES ('"Foo"'::pg_catalog.regnamespace::pg_catalog.oid, 
NULL::pg_catalog.text)
         )
         SELECT c.relname, ns.nspname, listed_objects.column_list FROM 
pg_catalog.pg_class c
          JOIN pg_catalog.pg_namespace ns ON c.relnamespace 
OPERATOR(pg_catalog.=) ns.oid
          LEFT JOIN pg_catalog.pg_class t ON c.reltoastrelid 
OPERATOR(pg_catalog.=) t.oid
          JOIN listed_objects ON listed_objects.object_oid 
OPERATOR(pg_catalog.=) ns.oid
          WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array['r', 'm'])
          ORDER BY c.relpages DESC;
2022-04-08 11:01:44.521 CEST [17223] 100_vacuumdb.pl LOG: statement: 
SELECT pg_catalog.set_config('search_path', '', false);
2022-04-08 11:01:44.521 CEST [17223] 100_vacuumdb.pl LOG: statement: 
VACUUM "Foo".bar;

And if I run the command manually:

$ /usr/local/pgsql/bin/vacuumdb -e -h localhost --schema '"Foo"' -d 
contrib_regress -U postgres
SELECT pg_catalog.set_config('search_path', '', false);
vacuumdb: vacuuming database "contrib_regress"
RESET search_path;
WITH listed_objects (object_oid, column_list) AS (
   VALUES ('"Foo"'::pg_catalog.regnamespace::pg_catalog.oid, 
NULL::pg_catalog.text)
)
SELECT c.relname, ns.nspname, listed_objects.column_list FROM 
pg_catalog.pg_class c
  JOIN pg_catalog.pg_namespace ns ON c.relnamespace 
OPERATOR(pg_catalog.=) ns.oid
  LEFT JOIN pg_catalog.pg_class t ON c.reltoastrelid 
OPERATOR(pg_catalog.=) t.oid
  JOIN listed_objects ON listed_objects.object_oid 
OPERATOR(pg_catalog.=) ns.oid
  WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array['r', 'm'])
  ORDER BY c.relpages DESC;
SELECT pg_catalog.set_config('search_path', '', false);

VACUUM "Foo".bar;


$ echo $?
0

I don't know what happen on cfbot, investigating...


-- 
Gilles Darold