BUG #8695: Reloading dump fails at COMMENT ON EXTENSION plpgsql

From: chris(at)chrullrich(dot)net
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #8695: Reloading dump fails at COMMENT ON EXTENSION plpgsql
Date: 2013-12-22 01:56:13
Message-ID: E1VuYH7-0008Rz-SV@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 8695
Logged by: Christian Ullrich
Email address: chris(at)chrullrich(dot)net
PostgreSQL version: 9.3.2
Operating system: all

A non-superuser cannot reload any dump of a database that contains the
plpgsql extension, because the dump unconditionally attempts to set the
comment on that extension. This fails because plpgsql is owned by the
superuser who installed it.

This contradicts the manual, which says: "The dumps produced by pg_dump are
relative to template0." The plpgsql extension is present in template0, with
the identical comment, and therefore neither extension nor comment should be
dumped at all. (I know this is splitting hairs, because pg_dump does not
actually compare the subject database to template0, but still, the
contradiction is there.)

The extension itself is dumped as CREATE IF NOT EXISTS, so that works, but
there is no conditional syntax for comments, and since pg_dump does not know
whether the comment has been changed from the default, it could not use one

I can think of one possible fix (aside from simply filtering that line from
the dump): COMMENT could be a no-op if the requested comment is identical to
the existing one.

Another idea I had was to allow comments to be part of an extension, so that
pg_dump would not dump them, but that does not work because pg_dump does not
know if a comment has been changed from the original value. Not that anyone
would ever do that.


Browse pgsql-bugs by date

  From Date Subject
Next Message timi 2013-12-22 14:09:19 Re: hi,postgresql bug
Previous Message Sergey Konoplev 2013-12-22 00:49:23 Re: Hot standby 9.2.6 -> 9.2.6 PANIC: WAL contains references to invalid pages