Re: Index/trigger implementation for accessing latest records

Alastair McKinley wrote:

> 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();

For maximum safety, you should use BEFORE trigger only to modify the row
being inserted/updated (or to abort the operation); any change to other
rows should occur in an AFTER trigger. One risk associated with failing
to do that is that some other BEFORE trigger further modifies the new
row, making your trigger-invoked UPDATE wrong. Also, be sure to think
about possible infinite recursion.

Another angle to keep in mind is what happens with insertions of
historical records, i.e. those that are not latest (today you think "ah,
but that never happens" and three months from now this is requested as a
feature. Time to rethink the whole development ...) You'd clobber the
latest_record flag without a replacement for it, which is probably
undesirable.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services