5

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.

  • 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? – Erwin Brandstetter May 4 '16 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. – unseen_damage May 4 '16 at 13:09
4

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)');
  • 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). – unseen_damage May 4 '16 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. – Erwin Brandstetter May 4 '16 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? – unseen_damage May 20 '16 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. – Erwin Brandstetter May 21 '16 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;
  • Edited the answer to include an example using an array instead of another table for purposes like this. – Ziggy Crueltyfree Zeitgeister May 5 '16 at 0:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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