Postgresql – How to set up last access (timestamptz) in table 1( basic info table) when working with many other tables having foreign key from table 1

foreign keypostgresqlrow-modification-timetimestamp

Let's say the database have 5 tables for sake of simplicity (there're many more in reality). Table 1 has the basic information of a user which is not changed so often. However, the other tables are accessed very often. All these other tables have the user-id as the foreign key. I need to make a column lastAccess in table 1 such that whenever the user works with any of the table, user's lastAccess in table 1 is updated.

I'm working with [python-socketio] (https://github.com/miguelgrinberg/python-socketio) server connected to PostgreSQL database using python-psycopg2 connector.

It seems like a task which every person working with databases should know. I am new to working with RDBMS and exactly find a way to do it.
A general direction, if a solution is not possible, will also be really appreciated.

Best Answer

First, I would advise against access as a term. I suggest mtime myself, but "access" is horrible. Access implies SELECT to me.

Rather than putting one access time on a user table, and having every trigger point to it consider putting one modified-time (mtime) on every row, in all tables. If you don't have an modified-time (mtime), you can create it.

ALTER TABLE tbl ADD COLUMN mtime timestamp DEFAULT CURRENT_TIMESTAMP;

If the tables aren't created yet, I would make the above NOT NULL also. After the column is created, follow the instructions on this answer

If you have one timestamp on table foo, being referenced by multiple triggers, then you get into an area where you can either

  • have a race condition or a locking condition.
  • require a more complex write-operation.

I wouldn't do the above. And it sounds less useful. If you want a time on a user table, I would make it relevant to the user by perhaps updating it when the user logs in or something of that sort. I would not store metadata from other tables on my user table.

Related Question