Cursor not getting all rows

On Tue, 2005-05-17 at 12:19, Joseph Shraibman wrote:

I'm running:

PostgreSQL 7.4.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2
20030222 (Red Hat Linux 3.2.2-5)

I do this:

BEGIN;
SELECT count(*) FROM u, d WHERE u.id = d.id AND ... ;
DECLARE cname CURSOR FOR SELECT u.field, d.field FROM u, d WHERE u.id =
d.id AND ... ;

At the end of the fetching if the number of fetched does not equal the
number from the SELECT count(*) I print out a warning message. It
happens every once in a while. Today it happened four times.

1) missed 1 out of 703773
2) missed 3 out of 703765
3) missed 10 out of 703743
4) missed 12 out of 703660

How is this possible? Because they are in the same transaction the
count and the cursor should see the exact same data, right?

Only if you set transaction isolation to serializable.

Scott Marlowe wrote:

Only if you set transaction isolation to serializable.

So am I getting data that was updated up until the time of the FETCH or
the DECLARE CURSOR?

Joseph Shraibman <jks@selectacast.net> writes:

I do this:

BEGIN;
SELECT count(*) FROM u, d WHERE u.id = d.id AND ... ;
DECLARE cname CURSOR FOR SELECT u.field, d.field FROM u, d WHERE u.id =
d.id AND ... ;

At the end of the fetching if the number of fetched does not equal the
number from the SELECT count(*) I print out a warning message. It
happens every once in a while. Today it happened four times.

How is this possible? Because they are in the same transaction the
count and the cursor should see the exact same data, right?

Not unless you are using SERIALIZABLE mode --- otherwise the cursor will
see whatever changes were committed during execution of the first SELECT.

regards, tom lane

On Tue, 2005-05-17 at 12:49, Joseph Shraibman wrote:

Scott Marlowe wrote:

Only if you set transaction isolation to serializable.

So am I getting data that was updated up until the time of the FETCH or
the DECLARE CURSOR?

The data shouldn't change between the declare and the fetch, it's
between the count(*) and the declare that it can change.