Hi,
I have no idea on how to fix this. I've attached the test case to
reproduce it.
First execute the test SQL file, then select the last UPDATE query, and
hit F7. The graphical explain is kinda ugly because two nodes are one on
the other. That's not the first time I meet this bug. For example, when
you explain an DELETE query on a table with foreign keys.
If you know how to fix this, that would be great because I'm completely
lost here.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
drop schema if exists refgeo cascade;
create schema refgeo;
drop table if exists refgeo.hzone cascade;
create table refgeo.hzone (
hid serial -- historical id
, id serial -- normal id
, name varchar -- zone name
, funcid varchar -- functional identifier
, geom varchar -- varchar for the zone
, sdate timestamp default current_timestamp -- start date :
data is valid from that date on
, edate timestamp default null -- end date : data is valid
until that date
);
-- the view to current data
create or replace view
refgeo.zone as
select
id
, name
, funcid
, geom
from
refgeo.hzone
where
edate is null;
-- make this view updatable
-- this mechanism will be almost automatic with PG >= 9.3
-- insert
create rule refgeo_zone_ins as
on insert to refgeo.zone do instead
insert into refgeo.hzone (id, name, funcid, geom) values (NEW.id, NEW.name,
NEW.funcid, NEW.geom);
-- update
create rule refgeo_zone_up as
on update to refgeo.zone do instead
(
update refgeo.hzone set edate = current_timestamp where id = OLD.id and edate
is null;
insert into refgeo.hzone (id, name, funcid, geom) values (OLD.id, NEW.name,
NEW.funcid, NEW.geom);
);
;
-- datsup current value instead of delete
create rule refgeo_zone_del as
on delete to refgeo.zone do instead
update refgeo.hzone set edate = current_timestamp where id = OLD.id and edate
is null;
/* test it */
truncate refgeo.hzone;
insert into
refgeo.hzone (id, name, funcid, geom, sdate, edate)
select
n as id
, 'Point ' || n::text as name
, n as funcid
, ''
, current_timestamp - interval '1 month' as sdate
, current_timestamp as edate
from
generate_series(1, 1000) as n
union
select
n as id
, 'Point ' || n::text as name
, n + 1000 as funcid
, ''
, current_timestamp as sdate
, null as edate
from
generate_series(1, 1000) as n;
-- get all data
select * from refgeo.hzone;
-- get current data
select * from refgeo.zone;
-- insert new data
insert into refgeo.zone (id, name, funcid, geom) values (3500, 'Point 3500',
3500, 'POINT(33 33)'::varchar);
-- see new data
select * from refgeo.zone order by id desc limit 10;
select * from refgeo.hzone order by id desc limit 10;
-- update our point
update refgeo.zone set geom = 'POINT(42 42)'::varchar where id = 3500;
select * from refgeo.zone order by id desc limit 10;
select * from refgeo.hzone order by id desc limit 10;
--
Sent via pgadmin-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
