pg_upgrade 14 -> 15 fails when using columnar 11.1 in child table

[amalek215]

When a child table in a partitioned table is using citus_columnar then an upgrade from PSQL 14 to 15 fails

PostgreSQL 14.5 and 15.1
citus/citus_columnar 11.1-1
RHEL 7.9

Steps to reproduce:

$ mkdir test_citus_pg_14_15_upgrade
$ /usr/pgsql-14/bin/initdb -D test_citus_pg_14_15_upgrade/data14
$ /usr/pgsql-15/bin/initdb -D test_citus_pg_14_15_upgrade/data15
$ echo  "shared_preload_libraries = 'citus'" >> test_citus_pg_14_15_upgrade/data15/postgresql.conf
$ echo  "shared_preload_libraries = 'citus'" >> test_citus_pg_14_15_upgrade/data14/postgresql.conf
$ /usr/pgsql-14/bin/pg_ctl -D test_citus_pg_14_15_upgrade/data14 start
$ psql -c "CREATE EXTENSION citus_columnar ; CREATE TABLE foo (d DATE NOT NULL) PARTITION BY RANGE (d); CREATE TABLE foo3 PARTITION OF foo FOR VALUES FROM ('2009-02-01') TO ('2009-03-01') USING COLUMNAR; "
$ pg_ctl -D test_citus_pg_14_15_upgrade/data14 stop
$ /usr/pgsql-15/bin/pg_upgrade --link --old-datadir="/var/lib/pgsql/test_citus_pg_14_15_upgrade/data14" --new-datadir="/var/lib/pgsql/test_citus_pg_14_15_upgrade/data15" --old-bindir="/usr/pgsql-14/bin" --new-bindir="/usr/pgsql-15/bin"

Output from upgrade command:

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok
Checking for new cluster tablespace directories             ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_xact to new server                           ok
Setting oldest XID for new cluster                          ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
  postgres
*failure*

Consult the last few lines of "/var/lib/pgsql/test_citus_pg_14_15_upgrade/data15/pg_upgrade_output.d/20221221T093126.357/log/pg_upgrade_dump_14486.log" for
the probable cause of the failure.
Failure, exiting

$ egrep -B3 -A3 '(warning)|(ERROR)' /var/lib/pgsql/test_citus_pg_14_15_upgrade/data15/pg_upgrade_output.d/20221221T094039.598/log/pg_upgrade_dump_14486.log
command: "/usr/pgsql-15/bin/pg_dump" --host /var/lib/pgsql --port 50432 --username postgres --schema-only --quote-all-identifiers --binary-upgrade --format=custom  --file="/var/lib/pgsql/test_citus_pg_14_15_upgrade/data15/pg_upgrade_output.d/20221221T094039.598/dump/pg_upgrade_dump_14486.custom" 'dbname=postgres' >> "/var/lib/pgsql/test_citus_pg_14_15_upgrade/data15/pg_upgrade_output.d/20221221T094039.598/log/pg_upgrade_dump_14486.log" 2>&1
pg_dump: warning: could not resolve dependency loop among these items:
pg_dump: warning: could not resolve dependency loop among these items:
pg_dump: warning: could not resolve dependency loop among these items:
pg_dump: warning: could not resolve dependency loop among these items:
pg_dump: warning: could not resolve dependency loop among these items:
pg_dump: warning: could not resolve dependency loop among these items:


command: "/usr/pgsql-15/bin/pg_restore" --host /var/lib/pgsql --port 50432 --username postgres --clean --create --exit-on-error --verbose --dbname template1 "/var/lib/pgsql/test_citus_pg_14_15_upgrade/data15/pg_upgrade_output.d/20221221T094039.598/dump/pg_upgrade_dump_14486.custom" >> "/var/lib/pgsql/test_citus_pg_14_15_upgrade/data15/pg_upgrade_output.d/20221221T094039.598/log/pg_upgrade_dump_14486.log" 2>&1
--
pg_restore: creating TABLE ATTACH "public.foo3"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3950; 0 0 TABLE ATTACH foo3 postgres
pg_restore: error: could not execute query: ERROR:  could not open relation with OID 0
Command was: ALTER TABLE ONLY "public"."foo" ATTACH PARTITION "public"."foo3" FOR VALUES FROM ('2009-02-01') TO ('2009-03-01');

[aykut-bozkurt]

Citus needs to perform some operations before and after PG upgrade operations.

Did you follow the PG upgrade path on PG upgrade path ?

[amalek215]

I did not run citus_prepare_pg_upgrade b/c that function as well as citus_finish_citus_upgrade do not exist when only using citus_columnar extension

[amalek215]

when I run a straight pg_dump on the database I get more informative errors (unlike the empty warnings from the dump part of the upgrade)

pg_dump: warning: could not resolve dependency loop among these items:
pg_dump:   TABLE foo3  (ID 223 OID 16443)
pg_dump:   ACCESS METHOD columnar  (ID 1686 OID 16412)
pg_dump:   INDEX stripe_pkey  (ID 3950 OID 16395)
pg_dump:   CONSTRAINT stripe_pkey  (ID 3951 OID 16396)
pg_dump:   POST-DATA BOUNDARY  (ID 4102)
pg_dump:   TABLE DATA foo3  (ID 4100 OID 16443)
pg_dump: warning: could not resolve dependency loop among these items:
pg_dump:   ACCESS METHOD columnar  (ID 1686 OID 16412)
pg_dump:   INDEX stripe_pkey  (ID 3950 OID 16395)
pg_dump:   CONSTRAINT stripe_pkey  (ID 3951 OID 16396)
pg_dump:   POST-DATA BOUNDARY  (ID 4102)
pg_dump:   TABLE DATA foo3  (ID 4100 OID 16443)
pg_dump:   PRE-DATA BOUNDARY  (ID 4101)
pg_dump: warning: could not resolve dependency loop among these items:
pg_dump:   ACCESS METHOD columnar  (ID 1686 OID 16412)
pg_dump:   INDEX chunk_group_pkey  (ID 3952 OID 16402)
pg_dump:   CONSTRAINT chunk_group_pkey  (ID 3953 OID 16403)
pg_dump:   POST-DATA BOUNDARY  (ID 4102)
pg_dump:   TABLE DATA foo3  (ID 4100 OID 16443)
pg_dump:   PRE-DATA BOUNDARY  (ID 4101)
pg_dump: warning: could not resolve dependency loop among these items:
pg_dump:   ACCESS METHOD columnar  (ID 1686 OID 16412)
pg_dump:   INDEX options_pkey  (ID 3946 OID 16390)
pg_dump:   CONSTRAINT options_pkey  (ID 3947 OID 16391)
pg_dump:   POST-DATA BOUNDARY  (ID 4102)
pg_dump:   TABLE DATA foo3  (ID 4100 OID 16443)
pg_dump:   PRE-DATA BOUNDARY  (ID 4101)
pg_dump: warning: could not resolve dependency loop among these items:
pg_dump:   ACCESS METHOD columnar  (ID 1686 OID 16412)
pg_dump:   INDEX chunk_pkey  (ID 3954 OID 16409)
pg_dump:   CONSTRAINT chunk_pkey  (ID 3955 OID 16410)
pg_dump:   POST-DATA BOUNDARY  (ID 4102)
pg_dump:   TABLE DATA foo3  (ID 4100 OID 16443)
pg_dump:   PRE-DATA BOUNDARY  (ID 4101)
pg_dump: warning: could not resolve dependency loop among these items:
pg_dump:   ACCESS METHOD columnar  (ID 1686 OID 16412)
pg_dump:   INDEX stripe_first_row_number_idx  (ID 3948 OID 16397)
pg_dump:   CONSTRAINT stripe_first_row_number_idx  (ID 3949 OID 16398)
pg_dump:   POST-DATA BOUNDARY  (ID 4102)
pg_dump:   TABLE DATA foo3  (ID 4100 OID 16443)
pg_dump:   PRE-DATA BOUNDARY  (ID 4101)
--

[aykut-bozkurt]

I could also reproduce even if I create citus as extension + followed PG upgrade path. We will check the problem.

#!/bin/bash

set -euo pipefail

rm -rf test_citus_pg_14_15_upgrade
mkdir test_citus_pg_14_15_upgrade
.pgenv/pgsql-14.0/bin/initdb -D test_citus_pg_14_15_upgrade/data14
.pgenv/pgsql-15.0/bin/initdb -D test_citus_pg_14_15_upgrade/data15
echo  "shared_preload_libraries = 'citus'" >> test_citus_pg_14_15_upgrade/data15/postgresql.conf
echo  "shared_preload_libraries = 'citus'" >> test_citus_pg_14_15_upgrade/data14/postgresql.conf
.pgenv/pgsql-14.0/bin/pg_ctl -D test_citus_pg_14_15_upgrade/data14 start
psql  -c "create extension citus; CREATE TABLE foo (d DATE NOT NULL) PARTITION BY RANGE (d); CREATE TABLE foo3 PARTITION OF foo FOR VALUES FROM ('2009-02-01') TO ('2009-03-01') USING COLUMNAR; "
psql -c "select pg_catalog.citus_prepare_pg_upgrade();"
.pgenv/pgsql-14.0/bin/pg_ctl -D test_citus_pg_14_15_upgrade/data14 stop
.pgenv/pgsql-15.0/bin/pg_upgrade --link --old-datadir="test_citus_pg_14_15_upgrade/data14" --new-datadir="test_citus_pg_14_15_upgrade/data15" --old-bindir=".pgenv/pgsql-14.0/bin" --new-bindir=".pgenv/pgsql-15.0/bin"
.pgenv/pgsql-15.0/bin/pg_ctl -D test_citus_pg_14_15_upgrade/data15 start
psql -c "select pg_catalog.citus_finish_pg_upgrade();"

[onurctirtir]

#5437 was believed to be fixed (by #5456) for the versions >= 11.0 but apparently this is not the case for columnar partition tables.

Got this backtrace regarding the error, by using ALTER system set backtrace_functions="relation_open";:

2023-01-11 12:37:03.483 +03 [32620] ERROR:  could not open relation with OID 0
2023-01-11 12:37:03.483 +03 [32620] BACKTRACE:
        postgres: onurctirtir postgres [local] ALTER TABLE(relation_open+0xa8) [0x561fcce49129]
        postgres: onurctirtir postgres [local] ALTER TABLE(index_open+0x21) [0x561fcced3bde]
        /opt/pgenv/pgsql-15.1/lib/citus_columnar.so(+0x11d30) [0x7fd5673acd30]
        /opt/pgenv/pgsql-15.1/lib/citus_columnar.so(FindNextStripeByRowNumber+0x31) [0x7fd5673acade]
        /opt/pgenv/pgsql-15.1/lib/citus_columnar.so(+0x148f7) [0x7fd5673af8f7]
        /opt/pgenv/pgsql-15.1/lib/citus_columnar.so(ColumnarBeginRead+0xf3) [0x7fd5673aef41]
        /opt/pgenv/pgsql-15.1/lib/citus_columnar.so(+0x18867) [0x7fd5673b3867]
        /opt/pgenv/pgsql-15.1/lib/citus_columnar.so(+0x189c8) [0x7fd5673b39c8]
        postgres: onurctirtir postgres [local] ALTER TABLE(+0x35a779) [0x561fcd0b8779]
        postgres: onurctirtir postgres [local] ALTER TABLE(+0x365cca) [0x561fcd0c3cca]
        postgres: onurctirtir postgres [local] ALTER TABLE(+0x364a0c) [0x561fcd0c2a0c]
        postgres: onurctirtir postgres [local] ALTER TABLE(+0x362189) [0x561fcd0c0189]
        postgres: onurctirtir postgres [local] ALTER TABLE(AlterTable+0x75) [0x561fcd0bfd87]
        postgres: onurctirtir postgres [local] ALTER TABLE(+0x6367e4) [0x561fcd3947e4]
        postgres: onurctirtir postgres [local] ALTER TABLE(standard_ProcessUtility+0x105c) [0x561fcd39409d]
        /opt/pgenv/pgsql-15.1/lib/citus_columnar.so(+0x1c684) [0x7fd5673b7684]
        /opt/pgenv/pgsql-15.1/lib/citus.so(multi_ProcessUtility+0x204) [0x7fd568219237]
        postgres: onurctirtir postgres [local] ALTER TABLE(ProcessUtility+0x10d) [0x561fcd393009]
        postgres: onurctirtir postgres [local] ALTER TABLE(+0x633ae3) [0x561fcd391ae3]
        postgres: onurctirtir postgres [local] ALTER TABLE(+0x633d59) [0x561fcd391d59]
        postgres: onurctirtir postgres [local] ALTER TABLE(PortalRun+0x371) [0x561fcd3911c6]
        postgres: onurctirtir postgres [local] ALTER TABLE(+0x62c3e8) [0x561fcd38a3e8]
        postgres: onurctirtir postgres [local] ALTER TABLE(PostgresMain+0x7da) [0x561fcd38efb2]
        postgres: onurctirtir postgres [local] ALTER TABLE(+0x55da0c) [0x561fcd2bba0c]
        postgres: onurctirtir postgres [local] ALTER TABLE(+0x55d2f1) [0x561fcd2bb2f1]
        postgres: onurctirtir postgres [local] ALTER TABLE(+0x559526) [0x561fcd2b7526]
        postgres: onurctirtir postgres [local] ALTER TABLE(PostmasterMain+0x1374) [0x561fcd2b6cbf]
        postgres: onurctirtir postgres [local] ALTER TABLE(+0x452e71) [0x561fcd1b0e71]
        /lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xf3) [0x7fd56a81e0b3]
        postgres: onurctirtir postgres [local] ALTER TABLE(_start+0x2e) [0x561fcce2c8be]
2023-01-11 12:37:03.483 +03 [32620] STATEMENT:  ALTER TABLE ONLY "public"."foo" ATTACH PARTITION "public"."foo3" FOR VALUES FROM ('2009-02-01') TO ('2009-03-01');

For a more complete picture, traced how an ATTACH PARTITION command normally works and got below stacktrace from gdb (for a columnar partition). Apparently, Postgres tests partition keys of all the tuples against partitioning by condition. However, stripe_first_row_number_idx is not there yet and this is probably because of those dependency loops (#5510). This doesn't cause a problem if the table DDL doesn't require an operation that needs to do a table scan/rewrite (such as ATTACH PARTITION); hence we were not seeing this issue on regular (non-partition) columnar tables.

relation_open(Oid relationId, LOCKMODE lockmode) (\opt\pgenv\src\postgresql-13.9\src\backend\access\common\relation.c:49)
index_open(Oid relationId, LOCKMODE lockmode) (\opt\pgenv\src\postgresql-13.9\src\backend\access\index\indexam.c:136)
citus.so!StripeMetadataLookupRowNumber(Relation relation, uint64 rowNumber, Snapshot snapshot, RowNumberLookupMode lookupMode) (\home\onurctirtir\citus\src\backend\columnar\columnar_metadata.c:778)
citus.so!FindNextStripeByRowNumber(Relation relation, uint64 rowNumber, Snapshot snapshot) (\home\onurctirtir\citus\src\backend\columnar\columnar_metadata.c:659)
citus.so!AdvanceStripeRead(ColumnarReadState * readState) (\home\onurctirtir\citus\src\backend\columnar\columnar_reader.c:702)
citus.so!ColumnarBeginRead(Relation relation, TupleDesc tupleDescriptor, List * projectedColumnList, List * whereClauseList, MemoryContext scanContext, Snapshot snapshot, _Bool randomAccess) (\home\onurctirtir\citus\src\backend\columnar\columnar_reader.c:234)
citus.so!init_columnar_read_state(Relation relation, TupleDesc tupdesc, Bitmapset * attr_needed, List * scanQual, MemoryContext scanContext, Snapshot snapshot, _Bool randomAccess) (\home\onurctirtir\citus\src\backend\columnar\columnar_tableam.c:280)
citus.so!columnar_getnextslot(TableScanDesc sscan, ScanDirection direction, TupleTableSlot * slot) (\home\onurctirtir\citus\src\backend\columnar\columnar_tableam.c:335)
table_scan_getnextslot(TableScanDesc sscan, ScanDirection direction, TupleTableSlot * slot) (\opt\pgenv\src\postgresql-13.9\src\include\access\tableam.h:906)
ATRewriteTable(AlteredTableInfo * tab, Oid OIDNewHeap, LOCKMODE lockmode) (\opt\pgenv\src\postgresql-13.9\src\backend\commands\tablecmds.c:5469)
ATRewriteTables(AlterTableStmt * parsetree, List ** wqueue, LOCKMODE lockmode, AlterTableUtilityContext * context) (\opt\pgenv\src\postgresql-13.9\src\backend\commands\tablecmds.c:5188)
ATController(AlterTableStmt * parsetree, Relation rel, List * cmds, _Bool recurse, LOCKMODE lockmode, AlterTableUtilityContext * context) (\opt\pgenv\src\postgresql-13.9\src\backend\commands\tablecmds.c:4128)
AlterTable(AlterTableStmt * stmt, LOCKMODE lockmode, AlterTableUtilityContext * context) (\opt\pgenv\src\postgresql-13.9\src\backend\commands\tablecmds.c:3780)
ProcessUtilitySlow(ParseState * pstate, PlannedStmt * pstmt, const char * queryString, ProcessUtilityContext context, ParamListInfo params, QueryEnvironment * queryEnv, DestReceiver * dest, QueryCompletion * qc) (\opt\pgenv\src\postgresql-13.9\src\backend\tcop\utility.c:1317)
standard_ProcessUtility(PlannedStmt * pstmt, const char * queryString, ProcessUtilityContext context, ParamListInfo params, QueryEnvironment * queryEnv, DestReceiver * dest, QueryCompletion * qc) (\opt\pgenv\src\postgresql-13.9\src\backend\tcop\utility.c:1086)
citus.so!ProcessUtilityInternal(PlannedStmt * pstmt, const char * queryString, ProcessUtilityContext context, ParamListInfo params, struct QueryEnvironment * queryEnv, DestReceiver * dest, QueryCompletion * completionTag) (\home\onurctirtir\citus\src\backend\distributed\commands\utility_hook.c:703)
citus.so!multi_ProcessUtility(PlannedStmt * pstmt, const char * queryString, ProcessUtilityContext context, ParamListInfo params, struct QueryEnvironment * queryEnv, DestReceiver * dest, QueryCompletion * completionTag) (\home\onurctirtir\citus\src\backend\distributed\commands\utility_hook.c:291)
citus.so!ColumnarProcessUtility(PlannedStmt * pstmt, const char * queryString, ProcessUtilityContext context, ParamListInfo params, struct QueryEnvironment * queryEnv, DestReceiver * dest, QueryCompletion * completionTag) (\home\onurctirtir\citus\src\backend\columnar\columnar_tableam.c:2238)
ProcessUtility(PlannedStmt * pstmt, const char * queryString, ProcessUtilityContext context, ParamListInfo params, QueryEnvironment * queryEnv, DestReceiver * dest, QueryCompletion * qc) (\opt\pgenv\src\postgresql-13.9\src\backend\tcop\utility.c:520)
PortalRunUtility(Portal portal, PlannedStmt * pstmt, _Bool isTopLevel, _Bool setHoldSnapshot, DestReceiver * dest, QueryCompletion * qc) (\opt\pgenv\src\postgresql-13.9\src\backend\tcop\pquery.c:1153)
PortalRunMulti(Portal portal, _Bool isTopLevel, _Bool setHoldSnapshot, DestReceiver * dest, DestReceiver * altdest, QueryCompletion * qc) (\opt\pgenv\src\postgresql-13.9\src\backend\tcop\pquery.c:1309)
PortalRun(Portal portal, long count, _Bool isTopLevel, _Bool run_once, DestReceiver * dest, DestReceiver * altdest, QueryCompletion * qc) (\opt\pgenv\src\postgresql-13.9\src\backend\tcop\pquery.c:788)
exec_simple_query(const char * query_string) (\opt\pgenv\src\postgresql-13.9\src\backend\tcop\postgres.c:1238)
PostgresMain(int argc, char ** argv, const char * dbname, const char * username) (\opt\pgenv\src\postgresql-13.9\src\backend\tcop\postgres.c:4347)
BackendRun(Port * port) (\opt\pgenv\src\postgresql-13.9\src\backend\postmaster\postmaster.c:4550)
BackendStartup(Port * port) (\opt\pgenv\src\postgresql-13.9\src\backend\postmaster\postmaster.c:4234)
ServerLoop() (\opt\pgenv\src\postgresql-13.9\src\backend\postmaster\postmaster.c:1739)
PostmasterMain(int argc, char ** argv) (\opt\pgenv\src\postgresql-13.9\src\backend\postmaster\postmaster.c:1412)
main(int argc, char ** argv) (\opt\pgenv\src\postgresql-13.9\src\backend\main\main.c:210)