Populate a Postgres table with old values from a function (not a trigger)

Question

I have a function that updates a unique column mytable.value from oldvalue to newvalue.

The problem is that I'd like to store oldvalue along with the old modified time in a separate archive table.

I don't know how to do this using a plain plpgsql function (Postgres 9.6). I think I know how to do it using a trigger, but I want the archive table to be populated only in certain cases, so when the function is explicitly called, rather than every modification to mytable.

create or replace function updatevalue(oldvalue text, newvalue text) returns void
  language plpgsql
  as $$
begin
  update mytable set modified = now(), value = newvalue where value = oldvalue
end
$$;

How can I modify the above function to populate the archive table with the old modified time? I don't want to pass it from the application as an argument to the function because of possible race conditions.

Answer 1

Concerning "possible race conditions":
If you run separate INSERT and UPDATE statements for this you get exactly what you wanted to avoid: a race condition. Anything can happen to the old row between INSERT and UPDATE, it has not been locked.

Enclosing both in a transaction (like the currently accepted answer suggests) does not fix this. You could lock the row(s) with FOR UPDATE, or access row(s) only once with a data-modifying CTE:

WITH upd AS (
   UPDATE mytable n
   SET    modified = now()      -- consider a trigger instead
        , value = $newvalue     -- provide value once
   FROM   mytable o
   WHERE  o.value = $oldvalue   -- provide value once
   AND    n.value = o.value
   RETURNING o.*                -- returns pre-update row
   )
INSERT INTO archive
SELECT * FROM upd;

Row(s) in mytable are only accessed once. No race condition and a bit faster, too. Related:

• Return pre-UPDATE Column Values Using SQL Only - PostgreSQL Version
• Lock for SELECT so another process doesn't get old data

This is assuming identical structure for mytable and archive, so I omitted the target column list from the INSERT. If the structure is not identical or can change, be explicit and spell out columns instead.

You can wrap it into a (plpgsql or sql) function or execute it directly (as prepared statement).

Aside: Instead of setting modified manually, I would consider a trigger ON UPDATE or ON INSERT OR UPDATE taking care of this automatically:

• Timestamp can't be saved - postgresql

Alternative: explicit locking with FOR UPDATE

BEGIN;  -- one transaction

INSERT INTO archive
SELECT *
FROM   mytable
WHERE  value = $oldvalue
FOR    UPDATE;            -- lock !

UPDATE mytable
SET    modified = now()
     , value = $newvalue
WHERE  value = $oldvalue;

COMMIT;

If you wrap this in a function, it's one transaction automatically.

Related:

• Optimizing concurrent updates in Postgres
• Lock for SELECT so another process doesn't get old data

Answer 2

I think this is very simple, if I understand your question correctly.

So, if you want archive every old value and its modified time at update/function call, then you just need archive_table with structure:

mytable_pk_value int/bigint -- assumes that mytable have primary key/identity column, lets say "id"
mytable_modified_time -- with  mytable.modified datatype
mytable_oldvalue      -- with  mytable.value datatype

Then in function you have:

...
begin
  insert into archive_table 
  (mytable_pk_value, mytable_modified_time, mytable_oldvalue) 
  select id, modified, value 
  from mytable 
  where value = oldvalue;
  update mytable set modified = now(), value = newvalue where value = oldvalue
end
...