How can I use Postgres "ROLLBACK TO SAVEPOINT" with "FOR UPDATE SKIP LOCKED"?

[Andrew Stuart]

Postgres 10

I am trying to use "FOR UPDATE SKIP LOCKED" to make a work queue in
Postgres.

My goal is to be able to set status to 'complete' or 'failed' as the
outcome by using "ROLLBACK TO SAVEPOINT;" in the event that processing
fails.

I expected the code below  to result in a final status of "failed", but it
appears the final status is "waiting".

BEGIN;
  DROP TABLE IF EXISTS foo;
  SELECT id
  INTO foo
  FROM jobs
  WHERE status = 'waiting'
  AND status != 'failed'
  ORDER BY created ASC
  FOR UPDATE SKIP LOCKED
  LIMIT 1;

UPDATE jobs SET status = 'failed' WHERE id = (SELECT id from foo)
RETURNING *;
SAVEPOINT blah;
UPDATE jobs SET status = 'complete' WHERE id = (SELECT id from foo)
RETURNING *;


ROLLBACK TO SAVEPOINT blah;


Can anyone please suggest what I can do to use either COMMIT to for status
to be 'complete' or ROLLBACK TO SAVEPOINT blah for statgus to be 'failed'?

thanks!

Can I use Postgres rules to reset session variables before/after queries?

[Andrew Stuart]

I am using Postgres row level security and I wish to ensure it is not
possible for a specific session variable (that holds a tenant id) to remain
set in between transactions / queries.

The reason is because this is a web application that pools sessions and the
session is shared between user web queries - if that session variable is
set but not cleared, then it is possible that the next query - which is for
a different application user - might be executed using the session variable
from the previous query.

I wish to ensure that the only session variables that can be used are LOCAL
session variables (which must be used only in a transaction and are
temporary and do not remain set in the session after the transaction has
ended).

So the solution I have come up with is to use the Postgres rules system,
and clear the session variable using RESET or SET in between every
transaction or query. RESET
https://www.postgresql.org/docs/9.1/sql-reset.html or SET
https://www.postgresql.org/docs/9.1/sql-set.html both before and after
every SELECT, INSERT, UPDATE, DELETE query.

Although the Postgres rules system appears to have the capabilities to do
this but I cannot find much detail.

A google search says there are not many answers relating to "postgres
rules" and "session variables"

Can anyone suggest if session variables can be SET/RESET using Postgres
rules, or optionally perhaps there is a better way to do so?

thanks