[Info-Ingres] Time limited SQL

[Roy Hann]

"Roger Hill" <rhill at hillconsult.com> wrote in message 
news:mailman.63.1195058004.18164.info-ingres at kettleriverconsulting.com...
> Hi.
>
> I have a job (delete from x where y...)
> that runs for many hours (not on Ingres I hasten to add).
>
> Does anyone here know a way to write self-terminating code along the lines 
> of
>
> delete from x where y ..
> and time <5am

It's a grey area.  I am pretty sure that if you wrote ...where 'now' < 
'15-nov-2007 05:00'... in Ingres, the truth of the expression would be 
evaluated using a constant value of 'now', so it wouldn't help.  But if you 
wrote ...where date('now') < '15-nov-2007 05:00'...  'now' would be 
evaluated for each row so it would probably would work as you want.  But 
there is no knowing if your target DBMS thinks the same way.

> so that when 5am comes it shuts itself down cleanly? (And I'll just run it
> again the next night, to delete a few more rows).
>
> I can from an external monitor kill the SQL at 5am, but I need it to be a
> clean termination, not just a kill.
>
> Any ideas? Am I missing something blindingly obvious?

A quota query might help if you use subsets that are small enough that you 
can be sure they'll be finished in time.   But if it works I wouldn't say it 
was obvious!    The following example is just a SELECT, but it show the 
idea.  It picks out the subset of parts with the three highest prices:

SELECT * FROM parts p1
WHERE 3 > (SELECT COUNT(DISTINCT p2.unit_cost)
           FROM parts p2
           WHERE p2.unit_cost > p1.unit_cost)

Roy