Queue in SQL

Hi!

Question:
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?
==================

Explanation
I intend to use libpq and C. as far as I know to retrieve the result from a
query I need to do the following:

BEGIN A TRANSACTION
DECLARE CURSOR cursor FOR SELECT
FETCH one row from cursor
Process
CLOSE cursor
DELETE row
COMMIT

Let say I have 100,000 records in my table, I have to pick up only the first
row. I do not have any special criteria to execute a query, and I only need
the oldest added row (let say the first row if the table is indexed).
How can I narrow the search criteria, not to receive all of the 100,000
record in the result?
Unfortunately, I do not know the internal working of the SELECT. I think it
should create a temporary object to store the result. This object size may
depend on the number of the rows in the result and the size of the stored
data per row. I can fetch the rows one by one using this temporary object.

Kind Regards,
Gyorgy Molnar

On Tue, 2001-11-27 at 15:11, Gyorgy Molnar wrote:
> How can I retrieve only the first row from a table?
> ==================
>
> Explanation
> I intend to use libpq and C. as far as I know to retrieve the result from a
> query I need to do the following:
>
> BEGIN A TRANSACTION
> DECLARE CURSOR cursor FOR SELECT
> FETCH one row from cursor
> Process
> CLOSE cursor
> DELETE row
> COMMIT
>
> Let say I have 100,000 records in my table, I have to pick up only the first
> row. I do not have any special criteria to execute a query, and I only need
> the oldest added row (let say the first row if the table is indexed).
> How can I narrow the search criteria, not to receive all of the 100,000
> record in the result?

If I understood you right, you can simply do

select <...> limit 1

Markus Bertheau

-----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(at)xyzzy(dot)dhs(dot)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-----

Hi Gyorgy,

Try this:

BEGIN;
SELECT * FROM table ORDER BY oid LIMIT 1 FOR UPDATE;
DELETE FROM table WHERE oid=(SELECT MIN(oid) FROM table);
COMMIT;

Few notes:

1. You might want to add an index over the oid column:
CREATE INDEX "my_idx" ON table(oid);

2. If you are executing this series from a programming language, you can
probably just change the first SELECT to "SELECT oid, * FROM ..." and just
grab out the oid and pass it is a parameter to the DELETE, rather than
having to do the aggregate subselect.

Chris

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of Gyorgy Molnar
> Sent: Tuesday, 27 November 2001 10:11 PM
> To: pgsql-sql(at)postgresql(dot)org
> Subject: [SQL] Queue in SQL
>
>
> Hi!
>
> Question:
> 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?
> ==================
>
> Explanation
> I intend to use libpq and C. as far as I know to retrieve the
> result from a
> query I need to do the following:
>
> BEGIN A TRANSACTION
> DECLARE CURSOR cursor FOR SELECT
> FETCH one row from cursor
> Process
> CLOSE cursor
> DELETE row
> COMMIT
>
> Let say I have 100,000 records in my table, I have to pick up
> only the first
> row. I do not have any special criteria to execute a query, and I
> only need
> the oldest added row (let say the first row if the table is indexed).
> How can I narrow the search criteria, not to receive all of the 100,000
> record in the result?
> Unfortunately, I do not know the internal working of the SELECT.
> I think it
> should create a temporary object to store the result. This object size may
> depend on the number of the rows in the result and the size of the stored
> data per row. I can fetch the rows one by one using this temporary object.
>
> Kind Regards,
> Gyorgy Molnar
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>