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.

Craig Ringer · Accepted Answer · 2014-01-23 09:49:10Z

6

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.

answered Jan 23, 2014 at 9:49

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Manually Release Postgres LOCK

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related