BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: buschmann(at)nidsa(dot)net
Subject: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
Date: 2019-10-08 17:08:53
Message-ID: 16045-673e8fa6b5ace196@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs pgsql-hackers

The following bug has been logged on the website:

Bug reference: 16045
Logged by: Hans Buschmann
Email address: buschmann(at)nidsa(dot)net
PostgreSQL version: 12.0
Operating system: Windows 10 64bit

I just did a pg_upgrade from pg 11.5 to pg 12.0 on my development machine
under Windows 64bit (both distributions from EDB).

cpsdb=# select version ();
PostgreSQL 12.0, compiled by Visual C++ build 1914, 64-bit
(1 row)

The pg_upgrade with --link went flawlessly, I started (only!) the new server
12.0 and could connect and access individual databases.

As recommended by the resulting analyze_new_cluster.bat I tried a full
vacuumdb with:

"N:/pgsql/bin/vacuumdb" -U postgres --all --analyze-only

which crashed with
vacuumdb: vacuuming database "cpsdb"
vacuumdb: error: vacuuming of table "admin.q_tbl_archiv" in database "cpsdb"
failed: ERROR: compressed data is corrupted

I connected to the database through pgsql and looked at the table

cpsdb=# \d+ q_tbl_archiv;
Column | Type | Collation |
Nullable | Default | Storage | Stats target | Description
table_name | information_schema.sql_identifier | |
| | plain | |
column_name | information_schema.sql_identifier | |
| | plain | |
ordinal_position | information_schema.cardinal_number | |
| | plain | |
col_qualifier | text | |
| | extended | |
id_column | information_schema.sql_identifier | |
| | plain | |
id_default | information_schema.character_data | |
| | extended | |
Access method: heap

When trying to select * from q_tbl_archiv I got:

cpsdb=# select * from q_tbl_archiv;
ERROR: invalid memory alloc request size 18446744073709551613

This table was created a long time back under 9.5 or 9.6 with the (here
truncated) following command:

create table q_tbl_archiv as
qseason as (
select table_name,column_name, ordinal_position
,replace(column_name,'_season','') as col_qualifier
-- ,'id_'||replace(column_name,'_season','') as id_column
from information_schema.columns
column_name like '%_season'
and ordinal_position < 10
and table_name in (
-- here truncated:
-- ... (here where all table of mine having columns like xxx_season)
-- to reproduce, change to your own tablenames in a test database
order by table_name
select qs.*,c.column_name as id_column, c.column_default as id_default
qseason qs
left join information_schema.columns c on c.table_name=qs.table_name and
c.column_name like 'id_%'

Until now this table was always restored without error by migrating to a new
major version through pg_dump/initdb/pr_restore.

To verify the integrity of the table I restored the dump taken under pg_dump
from pg 11.5 just before the pg_upgrade to another machine.

The restore and analyze went OK and select * from q_tbl_archiv showed all
tuples, eg (edited):

cpsdb_dev=# select * from q_tbl_archiv;
table_name | column_name | ordinal_position | col_qualifier
| id_column | id_default
table1 | chm_season | 2 | chm
| |
table2 | cs_season | 2 | cs
| id_cs | nextval('table2_id_cs_seq'::regclass)

In conclusion, this seems to me like an error/omission of pg_upgrade.

It seems to handle these specially derived tables from information_schema
not correctly, resulting in failures of the upgraded database.

For me, this error is not so crucial, because this table is only used for
administrative purposes and can easily be restored from backup.

But I want to share my findings for the sake of other users of pg_upgrade.

Thanks for investigating!

Hans Buschmann


Browse pgsql-bugs by date

  From Date Subject
Next Message Stephen Frost 2019-10-08 18:03:02 Re: Non-null values of recovery functions after promote or crash of primary
Previous Message David G. Johnston 2019-10-08 16:36:57 Re: BUG #16044: Could not send data to server

Browse pgsql-hackers by date

  From Date Subject
Next Message Antonin Houska 2019-10-08 17:14:30 Re: pg_init
Previous Message Tomas Vondra 2019-10-08 16:47:59 Re: pg_init