Re: pg_dump cosmetic problem while dumping/restoring rules

On 2012-12-11 16:34:35 +0200, Gražvydas Valeika wrote:
> with 9.2.2 I can see harmless cosmetic defect while dumping/restoring
> database with postgis extension.
>
> Steps to reproduce:
>
> - create new database;
> - CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
> - backup it;
> - create new database and restore it from this new backup.
>
>
> It produces 3 errors while executing these 3 statements:
>
> CREATE RULE geometry_columns_delete AS ON DELETE TO geometry_columns DO
> INSTEAD NOTHING;
> CREATE RULE geometry_columns_insert AS ON INSERT TO geometry_columns DO
> INSTEAD NOTHING;
> CREATE RULE geometry_columns_update AS ON UPDATE TO geometry_columns DO
> INSTEAD NOTHING;
>
> All 3 errors are similar to this:
>
> pg_restore: [archiver (db)] Error from TOC entry 3874; 2618 396850 RULE
> geometry_columns_delete postgres
> pg_restore: [archiver (db)] could not execute query: ERROR: rule
> "geometry_columns_delete" for relation "geometry_columns" already exists
> Command was: CREATE RULE geometry_columns_delete AS ON DELETE TO
> geometry_columns DO INSTEAD NOTHING;
>
>
> This happens because in the beginning of backup file there is issued
> command
> CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
> which creates those rules.

> As I understand these errors are harmless, I would say - cosmetic defects.

I'd say this is actually a real bug in the extensions tracking of
dependencies.
And it would have caused a failed restore if you had used single
transaction mode.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services