Re: Cursor With_Hold Performance Workarounds/Optimization

On Thu, Oct 19, 2017 at 04:06:47PM +0200, Leon Winter wrote:
> > What other things did you try, and how did they fail? In particular,
> > what happened when you used
> >
> > UPDATE table2
> > SET [things based on table1]
> > FROM table1 [qualified] JOIN table2 ON ([conditions])
>
> well, it is not the ideal way of doing things but then again this SQL is merely
> a consequence of the legacy 4GL language and runtime environment we are running
> (and trying to migrate to Postgres). We have a lot of those SQL structures and
> would prefer not to change all of them to avoid this situation. Currently there
> are also two database backends, one being the old legacy database and the other
> being Postgres and we are extremely limited from the (lacking) capabilities of
> the old database. We are surely planning to change many SQL statements to make
> better use of the database but not at this point for this issue.
>
> The loops are more complex in reality of course, more like:
>
> open cursor for select from table1
> loop
> { fetch some entries from cursor
> call some external application
> do some crazy complicated calculations based on some user input in the UI *
> update table2
> commit
> }
>
> The calculations inside the loop are written in some dynamic
> high-level language and cannot easily be translated into SQL.
>
> tl;dr: Existing code base has a lot of these patterns. General
> solution on database (interfacing) level is required.

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.

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.

Would you want something that compiles from the user inputs to SQL?
Might that have a more general utility?

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate