Horton

Thread

Commits

  1. Fix CREATE DATABASE so we can pg_upgrade DBs with OIDs above 2^31.

  1. BUG #17677: pg_upgrade error 14.5->15.0

    The Post Office <noreply@postgresql.org> — 2022-11-04T12:30:19Z

    The following bug has been logged on the website:

Bug reference:      17677
Logged by:          Sergey Pankov
Email address:      spankov@gotrg.com
PostgreSQL version: 15.0
Operating system:   Linux 3.10.0-1062.4.1.el7.x86_64 x86_64
Description:        

I have 14.5 instance .  I'am trying upgrade it via pg_upgrade utility.  On
the "Restoring database schemas in the new cluster" step i got error:
"command: "/usr/pgsql-15/bin/pg_restore" --host /var/run/pg_49001 --port
50432 --username postgres --create --exit-on-error --verbose --dbname
template1
"/var/lib/postgresql/pg_49001/15/data/pg_upgrade_output.d/20221104T071127.142/dump/pg_upgrade_dump_4236896942.custom"
>>
"/var/lib/postgresql/pg_49001/15/data/pg_upgrade_output.d/20221104T071127.142/log/pg_upgrade_dump_4236896942.log"
2>&1
pg_restore: connecting to database for restore
pg_restore: creating DATABASE
"rc16mo_r1_trg_taxcommitmentreporter_fzsymvlz"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 4784; 1262 4236896942 DATABASE
rc16mo_r1_trg_taxcommitmentreporter_fzsymvlz
rc16mo_r1_trg_taxcommitmentreporter_fzsymvlz
pg_restore: error: could not execute query: ERROR:  syntax error at or near
"4236896942"
LINE 1: ...eporter_fzsymvlz" WITH TEMPLATE = template0 OID = 4236896942...

I've try to execute CREATE DATABASE command on Postgresql 15.0 instance
manually and got the same error.                                            
                   ^
Command was: 
CREATE DATABASE "rc16mo_r1_trg_taxcommitmentreporter_fzsymvlz" WITH TEMPLATE
= template0 OID = 4236896942 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE
= 'en_US.UTF-8';"

I truncated OID parameter to 42362 and succesfully executed command
CREATE DATABASE "rc16mo_r1_trg_taxcommitmentreporter_fzsymvlz" WITH TEMPLATE
= template0  ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8'
OID=42362;

I've a dozens databases with relatively big OIDs:
postgres=# select oid, datname  from pg_database;
    oid     |                            datname
------------+----------------------------------------------------------------
4236897563 | rc16mo_r1_trg_rmavendorportalorderclaimsaggregator_sndeudbk
4236896942 | rc16mo_r1_trg_taxcommitmentreporter_fzsymvlz
  599915860 | rc17mo_r1__unitidentifiersrangeservice_nxozoosg
 So i couldn't upgrade instance with pg_upgrade utility .  Does it bug of
pg_upgrade and CREATE DATABASE commands or a feature?

  2. Re: BUG #17677: pg_upgrade error 14.5->15.0

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-11-04T13:35:36Z

    PG Bug reporting form <noreply@postgresql.org> writes:
> I've try to execute CREATE DATABASE command on Postgresql 15.0 instance
> manually and got the same error.                                            
>                    ^
> Command was: 
> CREATE DATABASE "rc16mo_r1_trg_taxcommitmentreporter_fzsymvlz" WITH TEMPLATE
> = template0 OID = 4236896942 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE
> = 'en_US.UTF-8';"

> I truncated OID parameter to 42362 and succesfully executed command
> CREATE DATABASE "rc16mo_r1_trg_taxcommitmentreporter_fzsymvlz" WITH TEMPLATE
> = template0  ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8'
> OID=42362;

Yeah, that evidently is a parser bug: can't handle OIDs above INT_MAX.
I thought maybe we could tweak pg_dump to quote the OID value, but nope:

# CREATE DATABASE "rc16mo_r1_trg_taxcommitmentreporter_fzsymvlz" WITH TEMPLATE
= template0 OID = '4236896942' ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE
= 'en_US.UTF-8';
ERROR:  oid requires an integer value

Somebody really worked at it to make this this broken :-(.

I'll see if I can get it fixed in time for 15.1.  Thanks for the
report!

			regards, tom lane