Manually Release Postgres LOCK

Question

I would like to know if there´s any command to unlock or release a lock made by the same transaction.

Pseudo-code

FUNCTION
    TRANSACTION
        LOOP
            TABLE LOCK
            table operations...
            "TABLE UNLOCK WANTED"
        END
    END OF TRANSACTION
END OF FUNCTION

The function query can take a while as the LOOP might be large, so I would like to be able to unlock before the transaction is fully finished.

Answer 1

No, it isn't possible. Locks are held until end of transaction, no exceptions.

Thus, you need to either:

• Use a nonstandard lock like an advisory lock, which requires everyone to check for it and respect it; or

• Do your work in transactions that commit autonomously, before the outer transaction is done. In PostgreSQL, lack of autonomous subtransaction support means that you must use dblink for this.