Re: ALTER TABLE SET ACCESS METHOD on partitioned tables

Hello Alvaro,

28.03.2024 18:58, Alvaro Herrera wrote:
> Grumble. I don't like initialization at declare time, so far from the
> code that depends on the value. But the alternative would have been to
> assign right where this blocks starts, an additional line. I pushed it
> like you had it.

I've stumbled upon a test failure caused by the test query added in that
commit:
--- .../src/test/regress/expected/create_am.out      2024-03-28 12:14:11.700764888 -0400
+++ .../src/test/recovery/tmp_check/results/create_am.out 2024-03-31 03:10:28.172244122 -0400
@@ -549,7 +549,10 @@
 ERROR:  access method "btree" is not of type TABLE
 -- Other weird invalid cases that cause problems
 CREATE FOREIGN TABLE fp PARTITION OF pg_am DEFAULT SERVER x;
-ERROR:  "pg_am" is not partitioned
+ERROR:  deadlock detected
+DETAIL:  Process 3076180 waits for AccessShareLock on relation 1259 of database 16386; blocked by process 3076181.
+Process 3076181 waits for AccessShareLock on relation 2601 of database 16386; blocked by process 3076180.
+HINT:  See server log for query details.
 -- Drop table access method, which fails as objects depends on it
 DROP ACCESS METHOD heap2;
 ERROR:  cannot drop access method heap2 because other objects depend on it

027_stream_regress_primary.log contains:
2024-03-31 03:10:26.728 EDT [3076181] pg_regress/vacuum LOG: statement: VACUUM FULL pg_class;
...
2024-03-31 03:10:26.797 EDT [3076180] pg_regress/create_am LOG: statement: CREATE FOREIGN TABLE fp PARTITION OF pg_am
DEFAULT SERVER x;
...
2024-03-31 03:10:28.183 EDT [3076181] pg_regress/vacuum LOG: statement: VACUUM FULL pg_database;

This simple demo confirms the issue:
for ((i=1;i<=20;i++)); do
echo "iteration $i"
echo "VACUUM FULL pg_class;" | psql >psql-1.log &
echo "CREATE FOREIGN TABLE fp PARTITION OF pg_am DEFAULT SERVER x;" | psql >psql-2.log &
wait
done

...
iteration 15
ERROR:  "pg_am" is not partitioned
iteration 16
ERROR:  deadlock detected
DETAIL:  Process 2556377 waits for AccessShareLock on relation 1259 of database 16384; blocked by process 2556378.
Process 2556378 waits for AccessShareLock on relation 2601 of database 16384; blocked by process 2556377.
HINT:  See server log for query details.
...

Best regards,
Alexander