Re: database-level lockdown

Wouldn't it be easier just to prevent connections to the database
while your transaction is executed?
EG:
<Connect to your_database>
UPDATE pg_database
SET datallowconn FALSE
WHERE datname = 'your_database' ;
START TRANSACTION;
<Do your_transaction>
COMMIT;
UPDATE pg_database
SET datallowconn TRUE
WHERE datname = 'your_database' ;

On Fri, Jul 3, 2015 at 1:25 PM, Filipe Pina <filipe(dot)pina(at)impactzero(dot)pt>
wrote:

> 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(dot)pina(at)impactzero(dot)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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote: Filipe Pina <
> filipe(dot)pina(at)impactzero(dot)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
>
>

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.