So, as database level locks do not exist (as per
https://wiki.postgresql.org/wiki/Lock_database), I've thought of having
a function that would lock *every* table in the database (far from the
same, but would probably work for me).
Something like:
CREATE OR REPLACE FUNCTION lockdown()
RETURNS void AS $$
DECLARE
t information_schema.tables.table_name%TYPE;
BEGIN
FOR t in SELECT table_name
FROM information_schema.tables
WHERE table_schema='public'
AND table_type='BASE TABLE'
LOOP
EXECUTE 'LOCK ' || t;
END LOOP;
END
$$
LANGUAGE plpgsql;
But the problem is that calling the function starts its own transaction
and once it returns, locks are removed..
Basically the workflow is (pseudo code coming from
Django/python/psycopg2/external, not within pgsql):
function generic_function_restarter(developer_function) {
# try 4 times to execute developer function and if all of them fail
# (due to SQLSTATE 40001 serialization failures),
# lock database and execute one last time
for 1 in [1..4] {
try {
call developer_function()
return 'success'
}
except SQLSTATE_40001 {
continue
}
except other_error {
return other_error
}
# only reaches here if all tries failed with SQLSTATE_40001
try {
START TRANSACTION
call lockdown()
call developer_function()
COMMIT TRANSACTION
return 'success'
}
except any_error {
# implicit ROLLBACK
return any_error
}
}
So, my problem here is that "call lockdown()" will place the locks and
remove them upon returning... Is it possible to execute a function
without creating a subtransaction?
I could place the locks from the adapter directly at the outter
transaction level but I have the feeling that performance would be
worse...
Thanks,
Filipe
On Sex, Jun 12, 2015 at 5:25 , Filipe Pina <filipe.p...@impactzero.pt>
wrote:
Exactly, that’s why there’s a limit on the retry number. On the
last try I wanted something like full lockdown to make sure the
transaction will not fail due to serialiazation failure (if no other
processes are touching the database, it can’t happen).
So if two transactions were retrying over and over, the first one to
reach max_retries would activate that “global lock” making the
other one wait and then the second one would also be able to
successfully commit...
On 11/06/2015, at 20:27, Tom Lane <t...@sss.pgh.pa.us> wrote:
Filipe Pina <filipe.p...@impactzero.pt> writes:
It will try 5 times to execute each instruction (in case of
OperationError) and in the last one it will raise the last error
it
received, aborting.
Now my problem is that aborting for the last try (on a restartable
error - OperationalError code 40001) is not an option... It simply
needs to get through, locking whatever other processes and queries
it
needs.
I think you need to reconsider your objectives. What if two or more
transactions are repeatedly failing and retrying, perhaps because
they
conflict? They can't all forcibly win.
regards, tom lane