Re: Multithreaded queue in PgSQL

Hello all,

I've initially brought the question to Nix, so I'll try to clarify
situation.

Whole point is to have multiple services accessing same table and
dividing the work, so locking with waiting for lock to be released is
out of question.

I want to deploy same Java Spring Web application to multiple servers
all present in same environment accessing same database. Thus, on each
server there will be same business services running. One of them is
quartz scheduling service which sends newsletters to newsletter
recipients. Hibernate is used for persistence, with Postgres as RDBMS.
It is not important for this discussion how newsletters are being
created but they end up in the database in following tables:

newsletter (_newsletter_id_, newsletter_content, newsletter_status,
newsletter_status_date)
newsletter_recipient (_newsletter_id_, _newsletter_recipient_email_,
newsletter_recipient_status, newsletter_recipient_status_date)

newsletter and newsletter_recipient stand in one-to-many relationship
with newsletter_recipient.newsletter_id being a FK to
newsletter.newsletter_id. PK of each relation is underlined.

Idea is that each service checks for newsletter recipients which either
have status NOT_SENT, or have status PROCESSING but
newsletter_recipient_status_date is more than 1h old (indicating that
newsletter has been tried to be sent, but service didn't complete
sending or at least didn't complete updating status to SENT, so we need
to retry). Each service should take, e.g. up to 10 such rows, and lock
them, so other services see these rows as locked and they shouldn't wait
for rows to become unlocked, but should try getting next 10 rows, all
until either such batch has been acquired or there are no more such rows.

I'm trying to make implementation more generic, not to use Postgres
specific SQL, and through Hibernate and Spring configuration make
services acquire lock on batch of rows, when trying to acquire lock on
batch of rows an exception should be thrown if rows are already locked
by a different service, and through that exception I intend to signal to
other services that they should try to handle and acquire lock on next
batch of rows. Will see how that goes.

Regards,
Stevo.

valgog wrote:
> On Jun 10, 1:58 pm, alok(dot)(dot)(dot)(at)yahoo(dot)com (Nikola Milutinovic) wrote:
>
>>> You may find that the PGQ component of skytools is what you want:
>>> http://pgfoundry.org/projects/skytools
>>> http://skytools.projects.postgresql.org/doc/
>>> http://skytools.projects.postgresql.org/doc/pgq-sql.html
>>>
>> Thanks, we will look into it. Still, I am surprised to learn that SQL as such cannot handle it. I do realize that the question is not trivial. Would setting transaction isolation level to SERIALIZABLE help in any way? Would locking of the entire table help in any way?
>>
>> Nix.
>>
>
> The easiest solution that I have found by now, is to use advisory
> lock, so that every thread is waiting until the other has released the
> lock.
>
> -- Valentine
>
>