> I don't know quite how to put this, but it's not clear to me that the > difficulties in this situation are things PostgreSQL could resolve > even with much larger development resources than are currently > available.
There does not seem to exist a cursor/portal/pointer semantic that can survive unrelated changes to the database inside a single connection (and is not super expensive like With_Hold). To some instance a similar behavior can be simulated by using a second connection. I assume most people avoid having this situation at all by changing their implementation (to for example a more dynamic UPDATE statement like you suggested). > If you're updating what are perforce small batches of records in the > UI, there's excellent reason to pull only those batches, mark them as > being "in process," process them, then update the marked ones as > "done" or whatever other states they can get to. > > As to "crazy complicated calculations," this is what active databases > are all about. SQL is Turing complete, so you really can do it. Of course all the things we do *could* be done in SQL itself which would be best solution but there is a huge legacy code base in 4GL that one cannot automatically translate into semantically equivalent SQL statements. During a such a loop user input can also be requested for example. > Would you want something that compiles from the user inputs to SQL? > Might that have a more general utility? Well, like I said, a 4GL to SQL conversion would be desirable but would require a lot of effort. Thus one wants to mix the languages and currently one would loop in 4GL, holding a SQL cursor/portal and do some stuff (which might include SQL update statements). One could also imagine looping in SQL and calling the 4GL runtime for each result row to do the computation. I am not sure that is ideal if such an operation waits on user input. Also one would need to analyze every loop looking for update statements and then automatically re-structure them to update statements with dependencies. -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers