Hi co-fanatics.
I am working on a small prove of concept, and am running into a small
obstacle. (the prove of concept showing, amongs other things, that doing
calculations on a database works well, and that it is possible to let it
run 'jobs')
Considder the following stored procedure:
For reasons of size the complete code is located on my site:
http://www.vankoperen.nl/concepts/postgresql/primer/index.html
It generates prime numbers for (parameter) odd numbers starting from the
biggest known prime in the primes table.
The "controller" table makes it possible to abort execution, something
wich can be handy if you did a 'select primer(10000000);'
I am just getting to grips with the read cashing and the way to
circumvent it (using the EXECUTE function) so as to read data each time
and thus react to the newest data, especialy the data in the
"controller" table in this case.
Now what does not seem to work is the opposite thing: i can not, from
the console etc, read the new data as the function is generating it.
If i 'SELECT count(*);' at the start, or near the end of the running
function, it always returns the same. Only when the function is finished
it commits and the external select returns the new and correct value.
To monitor the function's progress (and for other reasons too, wich are
not important in this concept yet) i realy want to read either the
UNCOMMITTED data. Or some way to COMMIT it during the functions
execution, but currently only the whole function can be regarded as a
transaction, and nested transactions is not (yet) supported.
Some digging in the mailinglist archives pointed to isolation levels.
Apparently 'dirty reads' in theory:
[quote http://archives.postgresql.org/pgsql-hackers/2004-08/msg01417.php ]
It's only allowed when the transaction is in READ UNCOMMITTED isolation
level.
Something Postgres doesn't currently support. In fact I'm not aware of
any SQL
database that supports it, though I'm sure there's one somewhere.
You wouldn't normally want to use such a thing, but it could be useful for,
for example, seeing what progress a transaction has made for a UI progress
meter.
[/quote]
But not possible for real at the moment?
So, summarising:
- Nested transactions is not (yet) supported
- READ UNCOMMITTED isolation level is not (yet) supported
- the EXECUTE plpgsql construct does not circumvent the transaction
Is there a way around this?
Regards,
Ellert.
