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
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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
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:
Or, while you are still inside the inserting 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 );