SQL92[EXPERTS]

Hey /prog/

Is it legal to modify a table while there's a read cursor open on it? Is it defined behavior?

For example, in some pseudo-code:

result = sql("SELECT * FROM faggot_table WHERE faggot_type=5;");
for each (result) {
  if (whatever) sql("DELETE FROM faggot_table WHERE faggot_id=3;");
}

The reason I ask is because I tested with several database engines from the ENTERPRISE to the _toy, and it works on some but errors out in others. So I can't use it in the general case.

However I'd like to know if it should work and some databases suck, if it shouldn't but is supported on some, or if it mustn't and it works by chance on some.

(It could work in some because they prefetch results, and maybe if they prefetch everything the cursor is already closed the moment you read the first row)

>>5,6
Usually I'd agree with you, but what if the condition for deletion can't be evaluated in SQL? (remember: SQL is not touring-complete)

Random example, you suddenly want to delete all the rows whose faggot_prime is not, in fact, a prime number. You can't to that directly in SQL (or maybe you can, don't make me pick another example).

You can:

* select to a temp table, read the temp table row by row issuing deletes on the main table if the condition is met

* read row by row generating a list, then delete using that list (a bit better if you don't expect to delete too much)

* do it cowboy style such as in the example above, reading row by row and deleting immediately when the condition is met

I can confirm that some of the SQL servers where the last option works internally implement cursors by generating a temporary table, so they're doing the first option for you whether you like it or not.