PostgreSQL INSERT with statements

Question

I'm using a postgres database and my problem includes two tables, simplified versions of them are below.

CREATE TABLE events(
    id SERIAL PRIMARY KEY NOT NULL,
    max_persons INTEGER NOT NULL
);

and

CREATE TABLE requests(
    id SERIAL PRIMARY KEY NOT NULL,
    confirmed BOOLEAN NOT NULL,
    creation_time TIMESTAMP DEFAULT NOW(),
    event_id INTEGER NOT NULL /*foreign key*/
);

There are n events and each event can have up to events.max_persons participants. New requests need to be confirmed and are valid up to 30 minutes. After that period the requests will be ignored, if they were not confirmed.

Now what I want to do is only insert a new request, when the sum of all confirmed requests and all requests that are still valid, but not confirmed, is less than events.max_persons.

I already have a query to select a single event. Here is a simplified version of it, just to give you an idea, how it should work

SELECT 
    e.id,
    SUM(CASE WHEN r.confirmed = 1 THEN 1 ELSE 0 END) AS number_confirmed
    SUM(CASE WHEN r.creation_time > (CURRENT_TIMESTAMP - INTERVAL '30 MINUTE') AND r.confirmed = 0 THEN 1 ELSE 0 END) AS number_reserved,
    e.max_persons
FROM events e, requests r
WHERE l.id = ? 
    AND r.event_id = e.id             
    AND (r.confirmed = 1 OR r.creation_time > (CURRENT_TIMESTAMP - INTERVAL '30 MINUTE'))
GROUP BY e.id, e.max_persons              
HAVING SUM(CASE WHEN r.confirmed = 1 OR (r.creation_time > (CURRENT_TIMESTAMP - INTERVAL '30 MINUTE')) THEN 1 ELSE 0 END) < e.max_persons";

Is it possibile to achieve this with a single INSERT - command?

Laurenz Albe · Accepted Answer · 2016-10-05 12:21:18Z

2

You could do that like this:

INSERT INTO requests
   SELECT * FROM (VALUES (...)) row
      WHERE ...

and write a WHERE clause that is only true if your condition is satisfied.

But there is a fundamental problem with that approach, namely that it is subject to a race condition.

If two such statements run at the same time, both may find the condition satisfied, but when each one has added its row and commits, the condition can be violated. That is because none of the statements can see the effects of the other one before they commit.

There are two solutions for this:

Lock the table before you test and insert. That is simple, but very bad for concurrency.
Use SERIALIZABLE transactions throughout. Then this should cause a serialization error, and one of the statements has to be retried and will find the condition violated when it does.

answered Oct 5, 2016 at 12:21

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

3 Comments

PrototypeX7 Over a year ago

Hello, sorry for the late answer. Currently I solved my problem by running a query which gets the difference between max_persons and number_booked/number_reserved and only execute the insert statement, if it's bigger than 0. Is that a viable practice? And another question, if I'm using SERIALIZABLE transactions, am I ok with problems like when two persons try to sign up for the same event at the same time, but only one place is left?

Laurenz Albe Over a year ago

Your current technique is vulnerable to the problem I indicated in my answer --- if two such transactions run in parallel, and only one place is free, they will both add a new request. With SERIALIZABLE that could not happen. One of the transactions would be terminated with a serialization error and had to be retried. During the retry it would show that the event is already booked out.

PrototypeX7 Over a year ago

Thanks, going to try that out!

Collectives™ on Stack Overflow

PostgreSQL INSERT with statements

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related