Postgres Pro
Downloads
Home   >   mailing lists

Thread: [HACKERS] pg_upgrade failed if view is based on sequence

[HACKERS] pg_upgrade failed if view is based on sequence

From
tushar
Date:
Steps to reproduce -

v9.6

postgres=# create sequence seq_9166 start 1 increment 1;
CREATE SEQUENCE
postgres=# create or replace view v3_9166 as select * from seq_9166;
CREATE VIEW

v10

run pg_upgrade , going to fail with this error


command: "./pg_restore" --host 
/home/centos/pg10_14july/postgresql/edbpsql/bin --port 50432 --username 
edb --exit-on-error --verbose --dbname 'dbname=postgres' 
"pg_upgrade_dump_13269.custom" >> "pg_upgrade_dump_13269.log" 2>&1
pg_restore: connecting to database for restore
pg_restore: creating pg_largeobject "pg_largeobject"
pg_restore: creating pg_largeobject_metadata "pg_largeobject_metadata"
pg_restore: creating COMMENT "postgres"
pg_restore: creating SCHEMA "public"
pg_restore: creating COMMENT "SCHEMA "public""
pg_restore: creating TABLE "public.fb17136_tab1"
pg_restore: creating SEQUENCE "public.seq_9166"
pg_restore: creating VIEW "public.v3_9166"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 187; 1259 16392 VIEW 
v3_9166 edb
pg_restore: [archiver (db)] could not execute query: ERROR:  column 
seq_9166.sequence_name does not exist
LINE 14:  SELECT "seq_9166"."sequence_name",                 ^    Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT 
pg_catalog.binary_upgrade_set_next_pg_type_oid('16394'::pg_catalog.oid);


-- For binary upgrade, must preserve pg_type array oid
SELECT 
pg_catalog.binary_upgrade_set_next_array_pg_type_oid('16393'::pg_catalog.oid);


-- For binary upgrade, must preserve pg_class oids
SELECT 
pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('16392'::pg_catalog.oid);

CREATE VIEW "v3_9166" AS SELECT "seq_9166"."sequence_name",    "seq_9166"."last_value",    "seq_9166"."start_value",
"seq_9166"."increment_by",   "seq_9166"."max_value",    "seq_9166"."min_value",    "seq_9166"."cache_value",
"seq_9166"."log_cnt",   "seq_9166"."is_cycled",    "seq_9166"."is_called"   FROM "seq_9166";
 

-- 
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [HACKERS] pg_upgrade failed if view is based on sequence

From
Thom Brown
Date:
On 20 July 2017 at 13:23, tushar <tushar.ahuja@enterprisedb.com> wrote:
> Steps to reproduce -
>
> v9.6
>
> postgres=# create sequence seq_9166 start 1 increment 1;
> CREATE SEQUENCE
> postgres=# create or replace view v3_9166 as select * from seq_9166;
> CREATE VIEW
>
> v10
>
> run pg_upgrade , going to fail with this error
>
>
> command: "./pg_restore" --host
> /home/centos/pg10_14july/postgresql/edbpsql/bin --port 50432 --username edb
> --exit-on-error --verbose --dbname 'dbname=postgres'
> "pg_upgrade_dump_13269.custom" >> "pg_upgrade_dump_13269.log" 2>&1
> pg_restore: connecting to database for restore
> pg_restore: creating pg_largeobject "pg_largeobject"
> pg_restore: creating pg_largeobject_metadata "pg_largeobject_metadata"
> pg_restore: creating COMMENT "postgres"
> pg_restore: creating SCHEMA "public"
> pg_restore: creating COMMENT "SCHEMA "public""
> pg_restore: creating TABLE "public.fb17136_tab1"
> pg_restore: creating SEQUENCE "public.seq_9166"
> pg_restore: creating VIEW "public.v3_9166"
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 187; 1259 16392 VIEW
> v3_9166 edb
> pg_restore: [archiver (db)] could not execute query: ERROR:  column
> seq_9166.sequence_name does not exist
> LINE 14:  SELECT "seq_9166"."sequence_name",
>                  ^
>     Command was:
> -- For binary upgrade, must preserve pg_type oid
> SELECT
> pg_catalog.binary_upgrade_set_next_pg_type_oid('16394'::pg_catalog.oid);
>
>
> -- For binary upgrade, must preserve pg_type array oid
> SELECT
> pg_catalog.binary_upgrade_set_next_array_pg_type_oid('16393'::pg_catalog.oid);
>
>
> -- For binary upgrade, must preserve pg_class oids
> SELECT
> pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('16392'::pg_catalog.oid);
>
> CREATE VIEW "v3_9166" AS
>  SELECT "seq_9166"."sequence_name",
>     "seq_9166"."last_value",
>     "seq_9166"."start_value",
>     "seq_9166"."increment_by",
>     "seq_9166"."max_value",
>     "seq_9166"."min_value",
>     "seq_9166"."cache_value",
>     "seq_9166"."log_cnt",
>     "seq_9166"."is_cycled",
>     "seq_9166"."is_called"
>    FROM "seq_9166";

This is because sequence_name, start_value, increment_by, max_value,
min_value, cache_value and is_cycled are no longer output when
selecting from sequences.  Commit
1753b1b027035029c2a2a1649065762fafbf63f3 didn't take into account
upgrading sequences to 10.

Thom

Re: [HACKERS] pg_upgrade failed if view is based on sequence

From
Thom Brown
Date:
On 20 July 2017 at 14:04, Thom Brown <thom@linux.com> wrote:
> On 20 July 2017 at 13:23, tushar <tushar.ahuja@enterprisedb.com> wrote:
>> Steps to reproduce -
>>
>> v9.6
>>
>> postgres=# create sequence seq_9166 start 1 increment 1;
>> CREATE SEQUENCE
>> postgres=# create or replace view v3_9166 as select * from seq_9166;
>> CREATE VIEW
>>
>> v10
>>
>> run pg_upgrade , going to fail with this error
>>
>>
>> command: "./pg_restore" --host
>> /home/centos/pg10_14july/postgresql/edbpsql/bin --port 50432 --username edb
>> --exit-on-error --verbose --dbname 'dbname=postgres'
>> "pg_upgrade_dump_13269.custom" >> "pg_upgrade_dump_13269.log" 2>&1
>> pg_restore: connecting to database for restore
>> pg_restore: creating pg_largeobject "pg_largeobject"
>> pg_restore: creating pg_largeobject_metadata "pg_largeobject_metadata"
>> pg_restore: creating COMMENT "postgres"
>> pg_restore: creating SCHEMA "public"
>> pg_restore: creating COMMENT "SCHEMA "public""
>> pg_restore: creating TABLE "public.fb17136_tab1"
>> pg_restore: creating SEQUENCE "public.seq_9166"
>> pg_restore: creating VIEW "public.v3_9166"
>> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>> pg_restore: [archiver (db)] Error from TOC entry 187; 1259 16392 VIEW
>> v3_9166 edb
>> pg_restore: [archiver (db)] could not execute query: ERROR:  column
>> seq_9166.sequence_name does not exist
>> LINE 14:  SELECT "seq_9166"."sequence_name",
>>                  ^
>>     Command was:
>> -- For binary upgrade, must preserve pg_type oid
>> SELECT
>> pg_catalog.binary_upgrade_set_next_pg_type_oid('16394'::pg_catalog.oid);
>>
>>
>> -- For binary upgrade, must preserve pg_type array oid
>> SELECT
>> pg_catalog.binary_upgrade_set_next_array_pg_type_oid('16393'::pg_catalog.oid);
>>
>>
>> -- For binary upgrade, must preserve pg_class oids
>> SELECT
>> pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('16392'::pg_catalog.oid);
>>
>> CREATE VIEW "v3_9166" AS
>>  SELECT "seq_9166"."sequence_name",
>>     "seq_9166"."last_value",
>>     "seq_9166"."start_value",
>>     "seq_9166"."increment_by",
>>     "seq_9166"."max_value",
>>     "seq_9166"."min_value",
>>     "seq_9166"."cache_value",
>>     "seq_9166"."log_cnt",
>>     "seq_9166"."is_cycled",
>>     "seq_9166"."is_called"
>>    FROM "seq_9166";
>
> This is because sequence_name, start_value, increment_by, max_value,
> min_value, cache_value and is_cycled are no longer output when
> selecting from sequences.  Commit
> 1753b1b027035029c2a2a1649065762fafbf63f3 didn't take into account
> upgrading sequences to 10.

Actually, I'm not sure we need to bother fixing this.  In the view
creation, * has to be expanded to whatever columns exist at the time
of creating the view, and since most of those columns no longer exist
in v10, there's no way to get the view ported over without rewriting
it.  Anything that depends on the output of those columns would be
broken anyway.

Thom

Re: [HACKERS] pg_upgrade failed if view is based on sequence

From
Tom Lane
Date:
Thom Brown <thom@linux.com> writes:
> On 20 July 2017 at 13:23, tushar <tushar.ahuja@enterprisedb.com> wrote:
>> postgres=# create sequence seq_9166 start 1 increment 1;
>> CREATE SEQUENCE
>> postgres=# create or replace view v3_9166 as select * from seq_9166;
>> CREATE VIEW

> This is because sequence_name, start_value, increment_by, max_value,
> min_value, cache_value and is_cycled are no longer output when
> selecting from sequences.

Yes.  This will not be "fixed"; you'll have to adjust the view before
you can update it to v10.  (If you want those values, you should now
get them out of the pg_sequence catalog.)

This should have been called out as a significant incompatibility
in the v10 release notes, but I see that it's not listed in the
right section.  Will fix that ...
        regards, tom lane