> 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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers