pg_upgrade failed if view is based on sequence

From: tushar <tushar(dot)ahuja(at)enterprisedb(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: pg_upgrade failed if view is based on sequence
Date: 2017-07-20 12:23:05
Message-ID: 603f3f0a-f89d-ae8b-1da9-a92fac16086d@enterprisedb.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Fabrízio de Royes Mello 2017-07-20 12:27:22 Re: [PATCH] A hook for session start
Previous Message Ashutosh Bapat 2017-07-20 12:18:48 Re: <> join selectivity estimate question