Looping and testing the result of a cte in a PostgreSQL trigger

Question

PostgreSQL 11.1

I am using the following trigger to obtain a "new" chart number from the chart_gap table:

CREATE FUNCTION phoenix.next_chart()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF 
AS $BODY$
BEGIN
  -- Check for empty chart number
  IF NEW.chart_number is null or NEW.chart_number = 0 THEN
    WITH ins AS (
      SELECT chart_number
      FROM   chart_gap
      WHERE  pg_try_advisory_xact_lock(chart_number)
      LIMIT  1
    )
    DELETE FROM chart_gap c
    USING  ins i
    WHERE  i.chart_number = c.chart_number
    RETURNING i.chart_number INTO NEW.chart_number;
  END IF;

  RETURN NEW;
END;
$BODY$;

How would I add a test to be sure the chart_number that is returned does not already exist (in table patients) and if it does, then force a loop to get the next chart number in the table, etc.., until an unused chart_number is found? That is, the trigger must return new and unused chart_numbers.

TIA

Note: my initial thoughts are to use recursive cte, but think a straight forward loop might be faster?

score 1 · Accepted Answer · answered Jan 27 '20 at 07:12

1

I expect that you have a good reason for not using sequence values.

You could simply surround the code block with LOOP / END LOOP; and add a SELECT count(*) at the end to test if there are any rows with that chart_number. That should be safe from race conditions as long as every transaction takes that route.

You have to make sure that the INSERT takes place in the same transaction, while the advisory lock is held.

As an alternative to the advisory lock, you could also

SELECT chart_number
FROM chart_gap
FOR UPDATE SKIP LOCKED
LIMIT 1;

which might be simpler.

answered Jan 27 '20 at 07:12

Laurenz Albe

209,280
17
206
263

Much thanks. The initial reason for not using a sequence, is that years ago (when this trigger was first written), I wanted to avoid using certain chart numbers. However, since the initial reason has long since been corrected, a sequence now would be good--but since chart numbers were initially somewhat randomly assigned, I still would have the problem of potentially producing a chart number that has already been used. – Alan Wayne Jan 27 '20 at 17:49
Set the sequence to a value higher than the vales in the table, then there won't be a collision. – Laurenz Albe Jan 27 '20 at 20:16
You would think (hope) so, but values have been entered all over the map leaving 100's of thousand of skipped usable integers. :) (Aside note, I just posted another related question -- I don't seem to be able to use the correct syntax for count(*). :( – Alan Wayne Jan 27 '20 at 23:17

Looping and testing the result of a cte in a PostgreSQL trigger

1 Answers1