Re: database-level lockdown

Yes, I've tried to come up with guideline to enumerate tables used in
each process, but it's not simple because it's python application
calling pgsql functions that use other functions, so it's tricky for a
developer re-using existing functions to enumerate the tables used for
those. Even if everything is well documented and can be re-used seems
like a nasty task...

For now, I'm locking all to be able to close the gap, but I'm also
wondering if I could do it in a pgsql function as I mentioned in the
question:

FUNCTION A
-> FUNCTION B
----> lock TABLE
-> FUNCTION C
----> TABLE is not locked anymore because function B frees it as soon
as it returns

Is there someway to have a function that locks some tables on the
"outter" transaction instead of its own subtransaction?

On Seg, Jul 6, 2015 at 3:08 , Filipe Pina <filipe(dot)pina(at)impactzero(dot)pt>
wrote:
>
>
> On Dom, Jul 5, 2015 at 2:50 , Greg Sabino Mullane <greg(at)turnstep(dot)com>
> wrote:
>>
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: RIPEMD160
>>
>>
>> Filipe Pina said:
>>
>>> I really can't find any other solution for what I need (in short:
>>> make sure
>>> no transactions are left out due to serialization failures)
>>
>> I think you may have been too quick to rule out advisory locks as a
>> solution.
>> Yes, you will need wrappers around all other calls, but
>> extraordinary problems
>> call for extraordinary solutions.
>>
>>> I could place the locks from the adapter directly at the outer
>>> transaction
>>> level but I have the feeling that performance would be worse...
>>
>> Well, performance has really got to take a back seat, given your
>> other
>> requirements. ;) Locking certainly *could* work - and is arguably
>> the best
>> solution, as that's what locks are for. Just have your Very
>> Important Transaction
>> retry, and upon reaching that critical number, exclusively lock just
>> the tables
>> being used, then try again. If you don't know which tables are being
>> used,
>> I suggest storing that somewhere your class can find it, or moving
>> away
>> from such a generic class.
>>
>> There are other solutions (e.g. forcing conflicting processes to
>> quit
>> and sleep a second), but the locking one seems the easiest.
>>
>> - --
>> Greg Sabino Mullane greg(at)turnstep(dot)com
>> End Point Corporation http://www.endpoint.com/
>> PGP Key: 0x14964AC8 201507050943
>> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
>> -----BEGIN PGP SIGNATURE-----
>>
>> iEYEAREDAAYFAlWZNeoACgkQvJuQZxSWSshVngCgpzGg7/OXRcyE2JgwDxDTFr9X
>> o7UAn3ENNgmIVqPpR4j1kyooiu+Ool7A
>> =6FSv
>> -----END PGP SIGNATURE-----
>>
>>