CODEWITHSUNDEEP
postgresqllocksmutual-exclusion
gdoug
gdoug

Reputation: 775

Is there a way to generate some set of rows exactly once on demand in Postgres?

The basic problem is that we are managing a significant amount of generated rows, and it is mission critical that this data is generated exactly once and only if necessary. Suppose you have a data relation:

CREATE TABLE sometable (
    id SERIAL,
    refID INTEGER,
    ...
);

Now, in some PL/PGSQL function we have:

...
-- Advisory locks didn't help here? :(
IF FALSE = SELECT EXISTS( SELECT 1 FROM sometable WHERE refID = dataID) THEN
    -- Generate fixed number of new rows in sometable that reference dataID.
END IF;
...

In short, the rows that should not be generated more than once some times are. As noted, advisory locks of the form PERFORM pg_advisory_lock(dataID) sadly did not help prevent this. Is there any hope?

EDIT: Forgot to mention that I ran into the duplicate data issue when testing with pgbench.

EDIT 2: Incorrect code fix, clarify issue.

Upvotes: 1

Views: 128

Answers (1)

Richard Huxton
Richard Huxton

Reputation: 22943

Perhaps the simplest solution is just to have a separate processed_ids table with a unique constraint on the id in question. Your function can try to insert to that table and if there is an exception then that ID is already processed.

Upvotes: 1

Related Questions