pg_upgrade vs user created range type extension

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: pg_upgrade vs user created range type extension
Date: 2016-09-22 21:56:53
Message-ID: 34854939-02d7-f591-5677-ce2994104599@dunslane.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


I have just encountered an apparent bug in pg_upgrade (or possibly pg_dump).

To recreate, install the cranges extension - which can be obtained via
"git clone https://bitbucket.org/adunstan/pg-closed-ranges.git" - for
both 9.4 and 9.5. Create a fresh 9.4 instance, create a database and in
it run "create extension cranges schema pg_catalog". Then create a fresh
9.5 instance and try to pg_upgrade from the 9.4 instance to the 9.5
instance. Here's the tail of the log:

pg_restore: creating SCHEMA "public"
pg_restore: creating COMMENT "SCHEMA "public""
pg_restore: creating EXTENSION "cranges"
pg_restore: creating COMMENT "EXTENSION "cranges""
pg_restore: creating SHELL TYPE "pg_catalog.cdaterange"
pg_restore: creating FUNCTION
"pg_catalog.cdaterange_canonical("cdaterange")"
pg_restore: creating TYPE "pg_catalog.cdaterange"
pg_restore: creating SHELL TYPE "pg_catalog.cint4range"
pg_restore: creating FUNCTION
"pg_catalog.cint4range_canonical("cint4range")"
pg_restore: creating TYPE "pg_catalog.cint4range"
pg_restore: creating SHELL TYPE "pg_catalog.cint8range"
pg_restore: creating FUNCTION
"pg_catalog.cint8range_canonical("cint8range")"
pg_restore: creating TYPE "pg_catalog.cint8range"
pg_restore: creating FUNCTION "pg_catalog.cdaterange("date", "date")"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 191; 1255 16389
FUNCTION cdaterange("date", "date") andrew
pg_restore: [archiver (db)] could not execute query: ERROR: function
"cdaterange" already exists with same argument types
Command was: CREATE FUNCTION "cdaterange"("date", "date")
RETURNS "cdaterange"
LANGUAGE "internal" IMMUTABLE
AS $$range_construct...

The end result is that I currently can't upgrade a database using this
extension, which is rather ugly.

Similar things happen if I put the extension in public instead of
pg_catalog.

cheers

andrew

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Thomas Munro 2016-09-22 23:10:17 Re: Tracking wait event for latches
Previous Message Robert Haas 2016-09-22 19:57:46 Re: Parallel tuplesort (for parallel B-Tree index creation)