[postgis-tickets] [PostGIS] #2223: Rules on geometry_columns make noisy backup/restore

[PostGIS]

#2223: Rules on geometry_columns make noisy backup/restore
-----------------------------------+----------------------------------------
 Reporter:  pramsey                |       Owner:  robe         
     Type:  defect                 |      Status:  new          
 Priority:  medium                 |   Milestone:  PostGIS 2.0.4
Component:  build/upgrade/install  |     Version:  2.0.x        
 Keywords:  rules                  |  
-----------------------------------+----------------------------------------

Comment(by rburhum):

 Actually Regina, I tracked down a bug with this process. It seems that
 this error is not completely harmless. It does not restore the permissions
 properly for the postgis tables

 My tables look like this before the pg_dump:


 {{{
                   List of relations
  Schema |           Name            | Type  | Owner
 --------+---------------------------+-------+--------
  public | fcf_agricultural_worker_8 | table | user_1
  public | fcf_jail_prison_19        | table | user_1
  public | fcf_jail_prison_6         | table | user_1
  public | has_police_station_7      | table | user_1
  public | planning_neighborhoods_2  | table | user_1
  public | planning_neighborhoods_20 | table | user_1
  public | route_points_30           | table | user_1
  public | routes_28                 | table | user_1
  public | spatial_ref_sys           | table | user_1
  public | track_points_31           | table | user_1
  public | tracks_29                 | table | user_1
  public | waypoints_27              | table | user_1
  public | waypoints_3               | table | user_1
  public | wtc_pump_station_10       | table | user_1
  public | wtc_pump_station_11       | table | user_1
  public | wtc_pump_station_12       | table | user_1
  public | wtc_pump_station_13       | table | user_1
  public | wtc_pump_station_14       | table | user_1
  public | wtc_pump_station_15       | table | user_1
  public | wtc_pump_station_16       | table | user_1
  public | wtc_pump_station_17       | table | user_1
  public | wtc_pump_station_18       | table | user_1
  public | wtc_pump_station_9        | table | user_1

 }}}

 after a pg_dump and a pg_restore, I get the following error/warnings:


 {{{
 [localhost] local: pg_restore -U postgres  -d project_5
 /tmp/daily/pg_dumps/project_5.dmp
 pg_restore: [archiver (db)] Error while PROCESSING TOC:
 pg_restore: [archiver (db)] Error from TOC entry 3109; 2618 18169 RULE
 geometry_columns_delete user_1
 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;



 pg_restore: [archiver (db)] Error from TOC entry 3107; 2618 18167 RULE
 geometry_columns_insert user_1
 pg_restore: [archiver (db)] could not execute query: ERROR:  rule
 "geometry_columns_insert" for relation "geometry_columns" already exists
     Command was: CREATE RULE geometry_columns_insert AS ON INSERT TO
 geometry_columns DO INSTEAD NOTHING;



 pg_restore: [archiver (db)] Error from TOC entry 3108; 2618 18168 RULE
 geometry_columns_update user_1
 pg_restore: [archiver (db)] could not execute query: ERROR:  rule
 "geometry_columns_update" for relation "geometry_columns" already exists
     Command was: CREATE RULE geometry_columns_update AS ON UPDATE TO
 geometry_columns DO INSTEAD NOTHING;



 WARNING: errors ignored on restore: 3

 Warning: local() encountered an error (return code 1) while executing
 'pg_restore -U postgres  -d project_5 /tmp/daily/pg_dumps/project_5.dmp'
 }}}

 {{{
 Although the database data seems to have been restored properly, the
 permissions for the geometry_column table was not

 project_5=> \dt
                    List of relations
  Schema |           Name            | Type  |  Owner
 --------+---------------------------+-------+----------
  public | fcf_agricultural_worker_8 | table | user_1
  public | fcf_jail_prison_19        | table | user_1
  public | fcf_jail_prison_6         | table | user_1
  public | has_police_station_7      | table | user_1
  public | planning_neighborhoods_2  | table | user_1
  public | planning_neighborhoods_20 | table | user_1
  public | route_points_30           | table | user_1
  public | routes_28                 | table | user_1
  public | spatial_ref_sys           | table | postgres
  public | track_points_31           | table | user_1
  public | tracks_29                 | table | user_1
  public | waypoints_27              | table | user_1
  public | waypoints_3               | table | user_1
  public | wtc_pump_station_10       | table | user_1
  public | wtc_pump_station_11       | table | user_1
  public | wtc_pump_station_12       | table | user_1
  public | wtc_pump_station_13       | table | user_1
  public | wtc_pump_station_14       | table | user_1
  public | wtc_pump_station_15       | table | user_1
  public | wtc_pump_station_16       | table | user_1
  public | wtc_pump_station_17       | table | user_1
  public | wtc_pump_station_18       | table | user_1
  public | wtc_pump_station_9        | table | user_1
 (23 rows)

 project_5=>
 }}}

 Trying to query geometry_columns fails, which make all the client code
 that relies on that (i.e everything) fail.

 {{{
 project_5=> SELECT f_geometry_column, srid FROM geometry_columns WHERE
 f_table_name='FCF_Agricultural_Worker_8';
 ERROR:  permission denied for relation geometry_columns
 }}}

 I am going to just manually write something to fix it after the load, but
 I figured you may want to be aware of this.

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/2223#comment:5>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.