Обсуждение: pg_dump cosmetic problem while dumping/restoring rules
Hi,
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.
Solution could be not to issue commands to create abovementioned rules, or issue 'CREATE OR REPLACE RULE' instead of 'CREATE RULE'.
As I understand these errors are harmless, I would say - cosmetic defects.
Best regards,
Grazvydas Valeika
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
Andres Freund <andres@2ndquadrant.com> writes: >> 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. Indeed. I will look into that. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Gražvydas Valeika <gvaleika@gmail.com> writes: > - 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: This has been fixed by Joe Conway meanwhile. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
This has been fixed by Joe Conway meanwhile.
Nice,
thaks!