Hi,
I have a table that stores a location identifier per person which will be
appended to many times.
However, for many queries in this system we only need to know the most recent
location per person, which is limited to about 1000 records.
Is the following trigger/index strategy a reasonable and safe approach to fast
access to the latest location records per person?
1. A boolean column (latest_record default true) to identify the latest
record per person
2. A before insert trigger that updates all other records for that person to
latest_record = false
3. A partial index on the latest_record column where latest_record is true
Aside from performance, is it safe to update other records in the table from
the insert trigger in this way?
Minimal example is shown below:
create table location_records
(
id bigserial,
person_id bigint,
location_id bigint,
latest_record boolean not null default true
);
create function latest_record_update() returns trigger as
$$
BEGIN
update location_records set latest_record = false where person_id =
new.person_id and latest_record is true and id != new.id;
return new;
END;
$$ language plpgsql;
create trigger latest_record_trigger before insert on location_records
for each row execute procedure latest_record_update();
create index latest_record_index on location_records(latest_record) where
latest_record is true;
insert into location_records(person_id,location_id) values (1,1);
insert into location_records(person_id,location_id) values (1,2);
insert into location_records(person_id,location_id) values (1,3);
insert into location_records(person_id,location_id) values (2,3);
insert into location_records(person_id,location_id) values (2,4);
select * from location_records;
Best regards,
Alastair
