Get rows from last insert statement by specific user

Question

I have many users in my web app (not the postgres user). Each of them capable of inserting item transactions on their own location.

How to get only rows just inserted by user_a?

Answer 1

Postgres does not save information about who wrote what exactly. If you need this, you have to do it yourself.

To save the current user or the session user (you decide) for every inserted row, I like to have a tiny meta table of eligible users with a tiny PK column and only safe that value with every row. To keep it simple and fast I don't even add a FK constraint.

Demo code (as admin or superuser):

CREATE SCHEMA admin;
REVOKE ALL ON SCHEMA admin FROM public;  -- only for admin

CREATE TABLE admin.login (
  login_id smallserial PRIMARY KEY
, login    text NOT NULL
, note     text
);
REVOKE ALL ON TABLE admin.login FROM public;

INSERT INTO admin.login(login, note) VALUES
  ('postgres', 'default superuser')
, ('user_a'  , 'someuser');          -- INSERT relevant roles

CREATE FUNCTION public.f_login_id()  -- usable by public
  RETURNS int2 AS
$func$
   SELECT login_id FROM admin.login WHERE login = current_user
$func$ LANGUAGE sql STABLE SECURITY DEFINER;

CREATE FUNCTION public.f_login_id(text)  -- overloaded function
  RETURNS int2 AS
$func$
   SELECT login_id FROM admin.login WHERE login = $1
$func$ LANGUAGE sql STABLE SECURITY DEFINER;

Then add a created_by column to each table that's supposed to log the insert role:

ALTER TABLE my_table ADD COLUMN created_by int2 DEFAULT public.f_login_id();

This is the simple, basic solution. Any user could alter values in created_by unless you do more.

Now this query can be your solution (for rows inserted after you ran the above). To get all rows inserted by a given role:

SELECT *
FROM   my_table
WHERE  created_by = public.f_login_id('user_a');

If you do this a lot, add an index on (created_by).

To also identify the latest transaction, you could work with the xmin saved in the header of every row. But that can be a bit tricky:

• How to view tuples changed in a PostgreSQL transaction?

Or, while you are still inside the inserting transaction:

• Can I select data inserted in the same uncommited transaction?

I would also add a column created_at timestamptz DEFAULT now() and work with that:

SELECT *
FROM   my_table t
WHERE  created_by = public.f_login_id('user_a');
AND    NOT EXISTS (
   SELECT 1 FROM my_table
   WHERE  created_by = t.created_by
   AND    created_at > t.created_at
   );