pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew G. Hammond <drew@xyzzy.dhs.org>
To: Gyorgy Molnar <gyorgy.molnar@home.com>
To: pgsql-sql@postgresql.org
Subject: Re: Queue in SQL
Date: Wed, 28 Nov 2001 16:20:58 -0500
Message-ID: <E169C8c-00025D-00@xyzzy.lan.internal> (raw)
In-Reply-To: <001601c1774d$66bb1b90$3201a8c0@Athlon>
References: <001601c1774d$66bb1b90$3201a8c0@Athlon>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 2001 November 27 09:11 am, Gyorgy Molnar wrote:

> I need to store some incoming data and retrieve them one by one (LIFO).
> Different processes will manage the storage and the retrieval.
> How can I retrieve only the first row from a table?

DROP TABLE queue; DROP SEQUENCE queue_id_seq;
CREATE TABLE queue (id SERIAL UNIQUE,
  data TEXT);

- -- to insert into queue
INSERT INTO queue (data) VALUES ('first');
INSERT INTO queue (data) VALUES ('second');

- -- to remove from queue
BEGIN;
LOCK queue IN EXCLUSIVE MODE;
SELECT * FROM queue ORDER BY id LIMIT 1;
DELETE FROM queue WHERE id = 1;     -- use the id retrieved above
COMMIT;

Key features:
- - the SERIAL data type draws it's values from a SEQUENCE, which allows us to 
easily maintain the order of the queue.
- - by marking it UNIQUE, we have implicitly defined an index on the column, 
which will make the ORDER BY clause in the SELECT and the WHERE clause in the 
DELETE more efficient.
- - wrapping the whole thing in a transaction and using a LOCK should ensure 
correct behaviour in a concurrent situation.

- -- 
Andrew G. Hammond     mailto:drew@xyzzy.dhs.org   http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F                  613-389-5481
5CD3 62B0 254B DEB1 86E0  8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iEYEARECAAYFAjwFVTsACgkQCT73CrRXhLHEJQCeNVW/3xh/PTfuRsykUz8+ff55
vVEAniFOBIC4FBEeKFwYKN103YbKXFyd
=WMz8
-----END PGP SIGNATURE-----



view thread (4+ messages)  latest in thread

Message-ID: <E169C8c-00025D-00@xyzzy.lan.internal>
Permalink:  ../E169C8c-00025D-00@xyzzy.lan.internal/
Also on:    postgresql.org/message-id/E169C8c-00025D-00@xyzzy.lan.internal

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: drew@xyzzy.dhs.org, gyorgy.molnar@home.com
  Subject: Re: Queue in SQL
  In-Reply-To: <E169C8c-00025D-00@xyzzy.lan.internal>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox