Re: Index/trigger implementation for accessing latest records

Alastair McKinley <a(dot)mckinley(at)analyticsengines(dot)com> writes:

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

My personal bias will come out here ....

I don't think using a trigger is a good solution here. Although very
powerful, the problem with triggers is that they are a 'hidden' side
effect which is easily overlooked and often adds an additional
maintenance burden which could be avoided using alternative approaches.

Consider a few months down the road and your on holidays. One of your
colleagues is asked to add a new feature which involves inserting
records into this table. During testing, they observe an odd result - a
field changing which according to the SQL they wrote should not. The
simple new feature now takes twice as long to develop as your colleague
works out there is a trigger on the table. Worse yet, they don't notice
and put there changes into production and then issue start getting
raised about communications going to the wrong location for customers
etc.

Triggers often become a lot more complicated than they will initially
appear. In your example, what happens for updates as opposed to inserts?
What happens if the 'new' location is actually the same as a previously
recorded location etc.

In your case, I would try to make what your doing more explicit and
avoid the trigger. There are a number of ways to do this such as

- A function to insert the record. The function could check to see if
that customer has any previous records and if so, set the boolean flag
to false for all existing records and true for the new one. You might
even want to break it up into two functions so that you have one which
just sets the flag based on a unique key parameter - this would
provide a way of resetting the current location without having to do
an insert.

- Use a timestamp instead of a boolean and change your logic to select
the current location by selecting the record with the latest
timestamp.

- Keep the two actions as separate SQL - one to insert a record and one
to set the current location. This has the advantage of making actions
clear and easier to maintain and can be useful in domains where people
move between locations (for example, I've done this for a University
where the data represented the students current address, which would
change in and out of semester periods, but often cycle between two
addresses, their college and their parental home). The downside of
this approach is that applications which insert this information must
remember to execute both SQL statements. If you have multiple
interfaces, this might become a maintenance burden (one of the
advantages of using a DB function).

Tim

--
Tim Cross