CODEWITHSUNDEEP
databasepostgresqllimit
Aaron Elias
Aaron Elias

Reputation: 31

How to limit the number of rows in a specific table with PostgreSQL?

We are trying to limit the number of registrations on our SIP server in the simplest way possible. More specifically, we have decided to limit the number of entries in the users table. The server is running PostgreSQL as the DBMS.

Is it possible to limit the number of entries in a specific table? For example, we want to limit the number of rows to 100 and we do not want any more entries on that table unless some of the old ones are deleted.

Please advise.

Upvotes: 3

Views: 6531

Answers (3)

amanbolat
amanbolat

Reputation: 81

It is a little bit late, but we had the same problem so I would like to share my solution. I assume that user table have some parent key or some relation. You can do it in transaction by locking the parent record and checking a count of total users that meet your condition. If it is less than limit then you can insert new user. If you try to do that in another transaction you will be locked until the first one finished, or you can use SKIP LOCKED to get an empty result and finish the transaction.

Code:

BEGIN;
SELECT * FROM user_parent WHERE /* condition */ FOR UPDATE;
SELECT count(1) FROM "user" WHERE /* condition */;
// check count in your code
// if count <= limit; then continue; else ROLLBACK
// INSERT your users
COMMIT;

Upvotes: 1

elsadek
elsadek

Reputation: 1086

As usual, google brought me over here :

CONSTRAINT nomore_than_x_rows
CHECK( table_id < 101 );

given that the id is of type serial

Upvotes: 0

Craig Ringer
Craig Ringer

Reputation: 324375

It's possible, but that's a pretty bad way of solving this problem. You'll have to create a trigger that acquires an EXCLUSIVE lock on the table then checks the count() on the table before allowing a commit or, if the insert would add too many rows, doing a RAISE EXCEPTION to abort the transaction.

Two triggers, actually; a BEFORE INSERT OR DELETE trigger that does the LOCK TABLE ... IN EXCLUSIVE MODE and an AFTER INSERT trigger that checks the COUNT of the table and decides whether to raise an exception. A simple BEFORE trigger is insufficient, since it can't tell if an INSERT might add more than one row, so a multi-valued insert or INSERT ... SELECT might cause the limit to be exceeded.

Other sessions that want to update the table will have to wait until the transaction doing the INSERT finishes. If the app isn't designed for this it could easily cause deadlocks.

The app probably doesn't expect to get an error when it INSERTs into the sessions table so it might not deal with that gracefully. It's rather unlikely to give the user a neat "too many registrations" error.

I haven't written a sample trigger for this because I think it's an absolutely terrible idea to do this. If you want to set a limit on how many users can be registered in your SIP server, configure your SIP server appropriately. If there's no such config option, modify it to add one or use a different SIP server that does have one.

Upvotes: 4

Related Questions