PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID16045
PG Version12.0
OSWindows 10 64bit
Opened2019-10-08 17:08:53+00
Reported byHans Buschmann
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      16045
Logged by:          Hans Buschmann
Email address:      (redacted)
PostgreSQL version: 12.0
Operating system:   Windows 10 64bit
Description:        

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 ();
                          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
"admin.q_tbl_archiv"

cpsdb=# \d+ q_tbl_archiv;
                                                   Table
"admin.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
with
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 
where 
column_name like '%_season'
and ordinal_position < 10
and table_name in (
 'table1'
,'table2'
-- 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
from 
	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

Messages

DateAuthorSubject
2019-10-08 17:08:53+00PG Bug reporting formBUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-09 13:24:36+00Tomas VondraRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-09 13:59:07+00Tomas VondraRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-09 14:07:01+00Tom LaneRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-09 14:18:41+00Tomas VondraRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-09 14:28:27+00Tomas VondraRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-09 14:50:52+00Hans BuschmannAW: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-09 23:07:59+00Tomas VondraRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-09 23:18:45+00Tom LaneRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-09 23:28:36+00Tomas VondraRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-09 23:41:54+00Tom LaneRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-10 01:48:13+00Andres FreundRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-10 13:43:03+00Tomas VondraRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-10 14:19:12+00Tom LaneRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-10 16:33:50+00Tomas VondraRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-10 20:14:20+00Tom LaneRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-10 20:40:22+00Tomas VondraRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-13 00:10:32+00Tomas VondraRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-13 18:26:48+00Tom LaneRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-13 20:38:48+00Tomas VondraRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-14 14:16:40+00Tom LaneRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-14 16:35:38+00Tomas VondraRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-15 00:18:17+00Tomas VondraRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-15 04:41:18+00Justin PryzbyRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-15 07:07:25+00Tomas VondraRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-16 11:33:44+00Tomas VondraRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-16 12:27:57+00Tom LaneRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-16 13:26:42+00Tom LaneRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-16 13:41:17+00Tomas VondraRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-16 14:33:43+00Tomas VondraRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12
2019-10-23 22:08:21+00Bruce MomjianRe: BUG #16045: vacuum_db crash and illegal memory alloc after pg_upgrade from PG11 to PG12