How to get advisory lock in shell?

Recently we had interesting problem. There are some maintenance tools, that do stuff that touch database. One can be calling repack, another can be specific dump, and yet another can be applying migrations from application.

The problem is that they can step over each other toes, and cause issues.

So we needed to add some way to prevent them from running at the same time…

Continue reading How to get advisory lock in shell?

How to limit rows to at most N per category – fix

A bit ago I wrote a blog post that was supposed to show how to keep number of rows in table to N per some category.

Unfortunately, I overlooked a problem related to concurrency.

Continue reading How to limit rows to at most N per category – fix

Picking task from queue – revisit

Some time ago, I wrote blogpost about how to pick a task from queue, without locking.

It was written in 2013, and as such it couldn't reflect everything we have now in PostgreSQL – namely SKIP LOCKED – which was added to PostgreSQL over year later.

Two people mentioned SKIP LOCKED in comments, but it was before it was committed even to git repo. But now, we have, officially released, PostgreSQL version with this mechanism, so let's see what it can do.

Continue reading Picking task from queue – revisit

Pick a task to work on

There is new blogpost on this subject!

There are cases where system stores list of things to do, and then there are some worker processes that check the list, pick something to work on, do it, and remove from the list.

Proper solution is to use some kind of queuing system. There is even PgQ which works withing PostgreSQL, but some people are not happy with it, as it requires compilation and installation. So they just use plain selects.

Will that work OK?

Continue reading Pick a task to work on

Waiting for 9.1 – Transaction level advisory locks

On 18th of February, Itagaki Takahiro committed patch:

Add transaction-level advisory locks.
 
They share the same locking namespace with the existing session-level
advisory locks, but they are automatically released at the end of the
current transaction and cannot be released explicitly via unlock
functions.
 
Marko Tiikkaja, reviewed by me.

Continue reading Waiting for 9.1 – Transaction level advisory locks