Autoincrement attribute based on another attribute

Question

I have a table showtime and a table ticket.

CREATE TABLE showtime (
    showtime_id     SERIAL PRIMARY KEY,
    theatre_id      INT REFERENCES theatre(theatre_id),
    showroom_id     INT REFERENCES showroom(showroom_id),
    movie_id        INT REFERENCES movie(movie_id),
    start_time      TIME,
    end_time        TIME,
    show_date       DATE
);

CREATE TABLE ticket (
    showtime_id     INT REFERENCES showtime(showtime_id),
    seat_number     SERIAL UNIQUE,
    price           NUMERIC(1000, 2) NOT NULL,
    time_bought     TIME,
    date_bought     DATE,
    wasUsed         BOOLEAN,
    PRIMARY KEY     (showtime_id, seat_number)
);

As you can see, each row in showtime will have a unique showtime_id. What I want to happen is when a new ticket is added, the seat_number should auto-increment from the last value tied with showtime_id:

ticket

showtime_id     |   seat_number   |   instead_of   |
      1         |       1         |       1        |
      1         |       2         |       2        |
      1         |       3         |       3        |
      1         |       4         |       4        |
      2         |       1         |       5        |
      2         |       2         |       6        |
      3         |       1         |       7        |
      4         |       1         |       8        |

Is this possible in PostgreSQL?

Not without some soft of locking: every time you insert a ticket you lock a corresponding showtime row first and can do that safely. Otherwise you could do that in runtime using window functions, since that data is easily retrievable. — zerkms
– zerkms, Commented May 2, 2015 at 2:02

Patrick · Accepted Answer · 2015-05-02 09:19:54Z

Rephrasing your question, this is what you want: Whenever you INSERT ticket with a certain showtime_id you want to assign seat_number to the next higher number than already present in ticket for that showtime_id. So what is the highest seat_number already present? Simple:

SELECT max(seat_number)
FROM ticket
WHERE showtime_id = ?; -- The ? being a parameter placeholder in languages like Java

The seat_number data type should not be serial because you will have duplicate seat numbers for different showtimes_ids. Make it a simple integer.

In a single INSERT statement:

INSERT INTO ticket VALUES
  (45,
   (SELECT max(seat_number) FROM ticket WHERE showtime_id = 45) + 1,
   ...
  );

To shield this logic from your users or application, you can use an insert trigger to avoid an erroneous or illegal (PK violation) seat number being entered:

CREATE FUNCTION assign_showtime_seat() RETURNS TRIGGER AS $$
BEGIN
  SELECT max(seat_number) + 1 INTO NEW.seat_number
  FROM ticket
  WHERE showtime_id = NEW.showtime_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_assign_showtime_seat
  BEFORE INSERT ON ticket
  FOR EACH ROW EXECUTE PROCEDURE assign_showtime_seat();

There is a small chance that you encounter a concurrency problem where two tickets for the same showtime_id are entered almost simultaneously. The first insert enters the trigger and computes a new seat_number. Before the INSERT completes the second ticket insert enters the trigger and computes the same seat_number. Either one then manages the actual INSERT and completes, the second ticket entry then fails on insert with a duplicate PRIMARY KEY violation. The documentation provides an example of recovering from such a concurrency problem.

Community · Accepted Answer · 2017-05-23 12:15:29Z

5

It is possible but either very unreliable for concurrent access or very expensive because you have to lock the whole table to be sure - locking rows will not be enough since you cannot lock rows that have not been entered, yet. (Well, with a separate showtime table, a row lock in the referenced table would help, but that only goes so far ... see comment by @zerkms)

If at all possible stick with the serial column you have now and use a VIEW or MATERIALIZED VIEW to arrive at your numbers:

CREATE MATERIALIZED VIEW v_ticket AS
SELECT *, row_number() OVER (PARTITION BY showtime_id
                             ORDER BY seat_number) AS seat_number_effective
FROM   ticket;

This gives you ascending numbers without gaps per showtime_id.

Very similar:

Serial numbers per group of rows for compound key

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered May 2, 2015 at 9:04

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

4 Comments

cctan Over a year ago

I chose this answer because this handles the problem raised by zerkms.

zerkms Over a year ago

"you cannot lock rows that have not been entered" --- but you only need to lock a showtime row which must exist already.

Erwin Brandstetter Over a year ago

@zerkms: OK, that should work for INSERTing the next seat_number. But it will still be slow (potentially crippling slow) under heavy concurrent write load. And then you still don't have a solution for gaps introduced by DELETEs or UPDATEs. Numbers from a SEQUENCE are reliable and fast, and the VIEW produces a gap-less result.

zerkms Over a year ago

@ErwinBrandstetter indeed (and I even mentioned that window functions is a better solution)

Collectives™ on Stack Overflow

Autoincrement attribute based on another attribute

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related