Skip site navigation (1) Skip section navigation (2)

Making dirty reads possible?

From: Ellert van Koperen <ellert(at)vankoperen(dot)nl>
To: PostgreSQL <pgsql-sql(at)postgresql(dot)org>
Subject: Making dirty reads possible?
Date: 2004-12-05 17:48:56
Message-ID: 41B34A08.1070603@vankoperen.nl (view raw or flat)
Thread:
Lists: pgsql-sql
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
- EXECUTE does not circumvent the transaction

Is there a way around this?

Regards,
Ellert.


In response to

Responses

pgsql-sql by date

Next:From: Michael FuhrDate: 2004-12-06 04:03:24
Subject: Re: inserting values into types
Previous:From: Yasir MalikDate: 2004-12-05 04:13:29
Subject: Re: inserting values into types

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group