Thread: BUG advisory_lock

DECLARE
  varUnlocked boolean;
BEGIN
varUnlocked:=3D pg_try_advisory_lock( 783264760123456 ); -- Try to lock fun=
ction
IF not varUnlocked THEN return NULL; END IF; -- exit if locking is unsucess=
full

WRONG QUERY HERE WHICH INTERRUPT EXECUTION

varUnlocked:=3D pg_advisory_unlock( 783264760123456 ); -- unlock function
END

NEXT time when I run this function I always get NULL until server reboot

EXPECTED: Pg must unlock if function execution failed=

<Eugen.Konkov@aldec.com> writes:
> EXPECTED: Pg must unlock if function execution failed

This is not a bug; it is the intended and documented behavior
of advisory locks.

            regards, tom lane

Eugen.Konkov@aldec.com wrote:
> DECLARE
>   varUnlocked boolean;
> BEGIN
> varUnlocked:= pg_try_advisory_lock( 783264760123456 ); -- Try to lock function
> IF not varUnlocked THEN return NULL; END IF; -- exit if locking is unsucessfull
>
> WRONG QUERY HERE WHICH INTERRUPT EXECUTION
>
> varUnlocked:= pg_advisory_unlock( 783264760123456 ); -- unlock function
> END
>
> NEXT time when I run this function I always get NULL until server reboot
>
> EXPECTED: Pg must unlock if function execution failed

You can unlock it in an EXCEPTION block to be safe.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.