NetNews Usenet Archive 1992 #18/NN_1992_18.iso/spool/comp/database/6160

Internet Message Format | 1992-08-17 | 5.3 KB

Xref: sparky comp.databases:6160 comp.databases.theory:364
Path: sparky!uunet!oracle!unrepliable!bounce
Newsgroups: comp.databases,comp.databases.theory
From: mfriedma@uucp (Michael Friedman)
Subject: Re: Relational Queues
Message-ID: <[e-mail]>
Sender: [e-mail] (Oracle News Poster)
Nntp-Posting-Host: appseq
Organization: Oracle Corporation
References: <[e-mail]> <[e-mail]> <[e-mail]>
Date: Sun, 16 Aug 1992 06:22:39 GMT
X-Disclaimer: This message was written by an unauthenticated user
              at Oracle Corporation.  The opinions expressed are those
              of the user and not necessarily those of Oracle.
Lines: 95

In article <[e-mail]> [e-mail] (Greg Higgins) writes:
>In article <[e-mail]> mfriedma@uucp (Michael Friedman) writes:
>>In article <[e-mail]> [e-mail] (Greg Higgins) writes:
>>>In article <[e-mail]> mfriedma@uucp (Michael Friedman) writes:
>>>>In article <[e-mail]> [e-mail] (Jonathan Edwards) writes:

>>>>>How do you implement a standard FIFO queue in the relational model?

>>>>>Here's the rules: multiple producers, multiple consumers. No possibility of
>>>>>two consumers getting the same record. Ability for consumer to wait if the
>>>>>queue is empty (no polling allowed!). Strict FIFO, but to make it a little
>>>>>easier, I only ask that a record enqueued by one transaction come before all
>>>>>records enqueued by other transactions that START after the first one COMMITS.
>>
>>>>>I believe that this can only be done by setting up a central server program,
>>>>>called from the producers and consumers (perhaps via RPC), which serializes
>>>>>access to the relation representing the queue. If this is true, doesn't anyone
>>>>>else find this a rather glaring weakness?

>>>>The SQL standard supports sequences.  Just create an increasing
>>>>sequence and use it to number each item you add to the table.  Then
>>>>when you pop items off, just select where SEQ_NUMBER = MAX(select
>>>>seq_number from table).

>>>Hardly. This implements a LIFO queue.

>>Sorry.  Momentary attack of braindeadness.  Just replace MAX with MIN.

>The guy next to me is working on MIN; I want MIN + 1, but then so
>do the other 20 people in the room.

Good point.  Adding simultaneous users makes it tougher.

Well, tag it.

update TABLE
    set IN_USE = 'Y'
    set PROCESS_USER = _MY_PROCESS_ID
    WHERE SEQ_NUMBER = MAX(select seq_number from table where
                IN_USE = 'N');


>>>>In general, however, this wouldn't even be a weakness if we didn't
>>>>have a solution.

>>>>SQL is intended to solve real world problems, not comp sci questions.
>>>>If you don't have the information in your database to know which item
>>>>in your list should be dealt with next then either you didn't design
>>>>your database right or it doesn't matter which comes next.

>>>Wanting a FIFO queue is not just a comp sci problem. It is a real world
>>>problem, and one which application developers need to deal with frequently.

>>>Consider a help desk situation where you have a first line tech support
>>>screening calls, entering them into the database, and then putting the
>>>folks who need more help on hold for second level support.

>>>You want second level support to pick up those call in the correct order.

>>>Note that the correct order may not be the serial number, since presumably
>>>that would be the initial entry into the database by the first line support.

>>So what is the correct order?  Probably it's the order in which they
>>were put on hold for second level support.  So how would I handle that
>>if I didn't have sequences?  Hmm.  Well, would a timestamp do?  Yup.
>>And if you don't have a time stamp you didn't design the database
>>right.

>>SQL has its limitations (explosion of parts, for one) but this isn't
>>one of them.

>"comp sci questions" is vendorese for "Shut up, buy my product, and we'll
>figure out later if it can actually do the job." I've heard it a lot,
>especially from representatives of a large, well known database
>vendor. If put to me when I'm in the right mood, as I apparently was
>the other day, it gets my dander up.

Well, considering that SQL is pretty much standard between Oracle and
all of our significant competitors I'm not going to run arround
making a fuss about how SQL can do the job.  It's an industry
standard and we don't need to defend it any more.

As for "comp sci questions" versus real word questions, I really do
believe there is a difference.  I'll admit that you've given a pretty
good example of a need for a FIFO queue, but in the vast majority of
applications you really don't need one.  Or a circular buffer.  Or a
LIFO queue.  Sometimes you need things that behave a little bit like
them - like making your inventory application do LIFO or FIFO
accounting - but you're unlikely to implement it as a queue even if
you're pretending that the goods sold are the (last or first) to be
placed in your warehouse.

--
-------------------------------------------------------------------------------
I am not an official Oracle spokesman.  I speak for myself and no one else.