6

I'm curious if Postgres has anything in place where I could limit the amount of rows kept for a ID.

For example, say I have a users table and login_coordinates table. Every time the user logs in, the most current coordinates get fed to a column in the users table, as well as inserting into the login_coordinates table.

I only want to keep the last 10 records, and deleting the 11th(oldest) record in the login_coordinates table for all users.

Users

   user_id            | current_coordinates        |    
----------------------+----------------------------+
    1                 | aaaa.bbbbb, aaaaa.bbbbbb   |    
    2                 | zzzz.xxxxxx, xxxxx.xxxcxx  |    
    3                 | dddd.xxxxxx, xxxxx.xxxcxx  |  

Login Coordinates

  coordinates_id      | old_login_coordinates      |        user_id           |
----------------------+----------------------------+--------------------------+
    1                 | aaaa.bbbbb, aaaaa.bbbbbb   |     1                    |
    2                 | xxxxx.xxxxxx, xxxxx.xxxcxx |     1                    |
    3                 | xxxxx.xxxxxx, xxxxx.xxxcxx |     1                    |

Is there anything that would limit the records to 10 coordinate_id per user, always deleting the oldest records?

I'm using PostgreSQL 9.5.

2
  • Please always declare your version of Postgres. Also essential here: do you have concurrent write access? Can a user log in multiple times at once or is this impossible? May 4, 2016 at 2:34
  • Concurrent write access will be allowed, but we will be doing row locks, and the likelihood of concurrent writes will be low as the main system is primarily read only. A user can login multiple times. May 4, 2016 at 13:09

2 Answers 2

5

as well as inserting into the login_coordinates table

I don't see the benefit of redundant storage. Just write to the table login_coordinates. Easy enough to access with only 10 rows per user. Don't update the user row as well.

Basically, what @Ziggy already suggested, with more flesh. Based on this table:

CREATE TABLE login_coordinates (
  login_coordinates_id serial PRIMARY KEY
, user_id integer NOT NULL  -- REFERENCES users
, login_at timestamptz NOT NULL DEFAULT now()
, coordinates point NOT NULL
);

Using OFFSET 9 LIMIT 1 to pick nr. 10:

DELETE FROM login_coordinates lc
WHERE  login_at <= (
   SELECT login_at
   FROM   login_coordinates
   WHERE  user_id = lc.user_id
   ORDER  BY login_at DESC
   OFFSET 9  -- only found if at least 10 rows present
   LIMIT  1
   )
AND    user_id = 1;

INSERT INTO login_coordinates (user_id, coordinates)
VALUES (1, '(1, 2)');
4
  • Redundant storage is mostly for auditing purpose. We don't want to fill our users table with auditing information like time and place of past logins. When a user logs in, we will be keeping the most recent login in the users table, along with a json web token. For auditing, we will keep the last 9 + current login (10 total) in a separate table. We will allow multiple logins, but limit that based on location (3 total). May 4, 2016 at 13:36
  • @unseen_damage: But: A user can login multiple times. If each login updates the row in users, I see concurrency trouble ahead. The current session might consider the login of a later session with the same user as its own. Etc. May 4, 2016 at 18:57
  • Unless I have implemented this wrong, the above query deletes all entries after #10 is entered in. Is that the expected behavior? May 20, 2016 at 20:58
  • @unseen_damage: Sorry, that was an error on my side. Sort order must be descending to get the "oldest" timestamp. Consider the fixed query. May 21, 2016 at 2:38
2

You could implement an ON INSERT trigger and delete, for the same user, any rows with a timestamp older than the oldest (MIN) of a subselect ordered by timestamp DESC limit 10.

Edit, using an array in users:

An alternative is using an array straight in the users table:

Add a type and a new array in users:

CREATE TYPE past_logon AS (login_at timestamptz, coordinates point);
ALTER TABLE users ADD past_logons past_logon[];

This can be added to the function that handles the logon (replace point(1,1) and = 1 with point and user in logon function parameters)

UPDATE users SET
  past_logons = (now(), point(1,1))::past_logon || past_logons[1:9]
WHERE user_id = 1;

Example for retrieving the list of past logons from the array

SELECT u.user_id, pl.*
FROM users u
LEFT JOIN LATERAL unnest(u.past_logons) pl ON true
ORDER BY pl.login_at;
1
  • Edited the answer to include an example using an array instead of another table for purposes like this. May 5, 2016 at 0:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.