Can one have Temporary Rows?

Question

I understand that Temporary Tables in Postgres and MS SQL Server persist until the end of the session. When the user’s session ends, any temp tables created by that user are dropped automatically. The temp table is not visible between users.

I need both the visibility across user sessions and the temporary nature where the data disappears when the user session ends (either closes gracefully or breaks).

Is there any way to have a permanent table visible to all but temporary rows? Is there any automatic way to have a user’s inserted rows disappear at the end of their session, yet be visible to other users during that session?

Answer 1

You can make a procedure/job that cleans up the tables. It would:

1. Check if the user is active
2. If so, ignore and move on to the next user or complete
3. If not, truncate/delete the table (after moving the records to a historical table if you desire)

Rinse/repeat every xx minutes.

We have used some variation of this in production. I usually do the coding in a stored procedure, and the "repeat" part comes from a SQL Server Agent job, which executes the procedure on a schedule. I try to keep it as modular as I can.

Answer 2

Found solution for PostgreSQL. There is no real "temporary" rows but it allows to filter out the rows created in the sessions that already closed.

It based on the fact that the temporary tables names from all sessions could be listed by querying the pg_class table.

Preparation:

create sequence seq_session_mark; -- Will be unique session ID

-- Test table
create table test_temp_rows(
  id serial,
  sid bigint default current_setting('public.sid')::bigint,
  x text);

-- This view returns only rows created in active sessions
create view v_temp_rows as
  select * from test_temp_rows
  where
    sid = any(
      select substring(relname, length('session_mark_#'))::bigint
      from pg_class
      where relname like 'session_mark_%');

Usage:

Just after the start of each session execute:

-- Generate new session ID and store it
select set_config('public.sid', nextval('seq_session_mark')::text, false);

-- Create temporary table with name containing session ID
do $$
begin
  execute 'create temp table session_mark_' || current_setting('public.sid') || '()';
end $$;

Test:

Session 1:

postgres=# insert into test_temp_rows(x) values ('a');

Session 2:

postgres=# table v_temp_rows;
┌────┬─────┬───┐
│ id │ sid │ x │
╞════╪═════╪═══╡
│  1 │   1 │ a │
└────┴─────┴───┘
postgres=# insert into test_temp_rows(x) values ('b');

Session 1:

postgres=# table v_temp_rows;
┌────┬─────┬───┐
│ id │ sid │ x │
╞════╪═════╪═══╡
│  1 │   1 │ a │
│  2 │   2 │ b │
└────┴─────┴───┘

Close session 1, then in session 2:

postgres=# table v_temp_rows;
┌────┬─────┬───┐
│ id │ sid │ x │
╞════╪═════╪═══╡
│  2 │   2 │ b │
└────┴─────┴───┘

You could periodically clean up your table:

delete from test_temp_rows
where sid != all(
  select substring(relname, length('session_mark_#'))::bigint
  from pg_class
  where relname like 'session_mark_%');

PS: Probably something similar also could by done in MS SQL Server.

Answer 3

For SQL server (this is a bit hacky but it works)

if you surround your entire set of actions in a transaction and then rollback at the end of the session(or disconnect if auto commit is off). and with every read you use with(readuncommitted)

eg.

--table definition

CREATE TABLE [LockTest](
    [id] [bigint] NOT NULL,
    [value1] [nchar](10) NOT NULL
)

User 1

BEGIN TRANSACTION

INSERT INTO LockTest
SELECT 1,'test'

User 2 can now run

select * from LockTest with (readuncommitted)
--obviously don't do select * this is just an small scale example

User 2 can see the entry created by user 1

Now user 1 disconnects or runs a forced rollback their entries to the table are removed so if user 2 re-runs their query the data is no longer there