Postgres round robin id sequence

Question

Say I have table:

CREATE TABLE custom_sequence (
  name       TEXT NOT NULL,
  number     INTEGER DEFAULT NULL,
  is_expired BOOLEAN DEFAULT FALSE
);

And on insert I have to find first expired number and put it in new record.

For example:

"a"   1    FALSE
"b"   2     TRUE
"c"   3    FALSE

insert new:

"c"   2    FALSE

I can do that using TRIGGER:

CREATE OR REPLACE FUNCTION write_custom_number()
  RETURNS TRIGGER AS
  $$
  DECLARE
    next_number INTEGER;
  BEGIN

    SELECT
      CASE
      WHEN count(*) > 0 THEN min(number)
      WHEN count(*) = 0 THEN
        CASE
        WHEN (SELECT
                count(*)
              FROM custom_sequence) > 0
        THEN (SELECT
                count(*)
              FROM custom_sequence) + 1
        WHEN (SELECT
                count(*)
              FROM custom_sequence) = 0
        THEN
          1
        END
      END
    INTO next_number
    FROM custom_sequence
    WHERE is_expired = TRUE;


    IF next_number IS NULL
    THEN
      next_number = 1;
    END IF;

    NEW.number := next_number;

    RETURN NEW;
  END
  $$
LANGUAGE plpgsql;

CREATE TRIGGER write_custom_number
BEFORE INSERT ON custom_sequence FOR EACH ROW
EXECUTE PROCEDURE write_custom_number();

But then I'll need to lock table for every INSERT! Is there any better way to solve this problem?

Could you explain why do you want to do that ? What is a "business problem" you are trying to solve with this code ? If a record #1 expires, then all subseqent records must get also number #1, is that right ? — krokodilko
– krokodilko, Commented Feb 24, 2014 at 14:31

Craig Ringer · Accepted Answer · 2014-02-24 23:39:53Z

1

No, there's no workaround. It's inherent to what you want. You can't reuse the first expired number and still have concurrency, since someone else might take it and insert it before you commit otherwise.

To make this concurrent you'd need some kind of global sequence manager that could keep track of IDs it'd already handed out and do dirty reads of the table to see its state. There's nothing like that in the database engine and you can't do it at the SQL level. It'd also be extremely hard to get right.

So no. If you want to re-use IDs, you have to do your transactions serially.

answered Feb 24, 2014 at 23:39

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres round robin id sequence

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related