pg_upgrade failed if view is based on sequence

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers