Opened 23 months ago

Last modified 17 months ago

#5334 new defect

topology edge view doesn't need a rule to insert data

Reported by: robe Owned by: strk
Priority: medium Milestone: PostGIS Fund Me
Component: topology Version: master
Keywords: Cc:

Description

I noticed that when I do this:

SELECT topology.CreateTopology('nyc_topo');

The nyc_topo.edge view has a INSERT rule. It doesn't even have an update rule.

Views that draw from a single table where that table has a primary key, are by default insertable and updatable. I think the change happened in PostgreSQL 9.0. Anyway longer ago than any version we support.

I propose you just remove this insert rule. Not sure why you didn't have an update rule to go along with it, unless there was a reason you didn't want this table updated.

Change History (5)

comment:1 by strk, 23 months ago

I think the INSERT rule was to set the abs_ prefixed fields of edge_data to the absolute value of the non-prefixed fields in edge view. But I also suspect that rule doesn't work currently as I feel I tried last week w/out success

comment:2 by strk, 23 months ago

Oh I was wrong, the INSERT INTO edge does work at doing the abs() thing:

=# insert into test.edge values (1,1,2,-1,1,0,0,'LINESTRING(0 0, 10 0)');
INSERT 0 1
=# select * from test.edge_data;
-[ RECORD 1 ]-------+-----------------------------------------------------------------------------------
edge_id             | 1
start_node          | 1
end_node            | 2
next_left_edge      | -1
abs_next_left_edge  | 1
next_right_edge     | 1
abs_next_right_edge | 1
left_face           | 0
right_face          | 0
geom                | 0102000000020000000000000000000000000000000000000000000000000024400000000000000000

Would that work w/out our own rule ?

comment:3 by strk, 23 months ago

As for the update rule, yes, we might add that

in reply to:  2 comment:4 by robe, 23 months ago

Replying to strk:

Oh I was wrong, the INSERT INTO edge does work at doing the abs() thing:

=# insert into test.edge values (1,1,2,-1,1,0,0,'LINESTRING(0 0, 10 0)');
INSERT 0 1
=# select * from test.edge_data;
-[ RECORD 1 ]-------+-----------------------------------------------------------------------------------
edge_id             | 1
start_node          | 1
end_node            | 2
next_left_edge      | -1
abs_next_left_edge  | 1
next_right_edge     | 1
abs_next_right_edge | 1
left_face           | 0
right_face          | 0
geom                | 0102000000020000000000000000000000000000000000000000000000000024400000000000000000

Would that work w/out our own rule ?

Okay I missed you were doing something extra, so nevermind about my complaint there.

Though I think you might still be better off getting rid of the rule and putting a trigger directly on the edge_data table. Cause with the view, this is only going to work if someone inserts into the view. It won't work if they insert directly into edge_data table.

Also I think PostgreSQL is trying to phase out use of rules and has been for a while, so better to go with a TRIGGER or an INSTEAD OF trigger.

comment:5 by strk, 17 months ago

Milestone: PostGIS 3.4.0PostGIS Fund Me
Note: See TracTickets for help on using tickets.