Cursor with hold emits the same row more than once across commits in 8.3.7

From: Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Cursor with hold emits the same row more than once across commits in 8.3.7
Date: 2009-06-09 06:15:38
Message-ID: 4A2DFE0A.6050502@catalyst.net.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

Short Desc: Cursor with hold emits the same row more than once across
commits in 8.3.7
Os : Debian Etch amd64 / Ubuntu Jaunty amd64
Pg : 8.3.7
Build options: Official package and also compiled from source with
--enable-integer-datetimes
Detailed Desc:

A construction of the form

DECLARE cur CURSOR WITH HOLD FOR SELECT * FROM obj

loop
FETCH 1000 FROM cur
process 'em
COMMIT

results in some of the same rows being emitted more than once, altho the
final rowcount is correct (i.e some rows end up being never seen).

Originally discovered using a perl DBI program, and we wondered if the
version of DBD::Pg might be an issue, so a c library program was written
to test this - and it exhibits the problem too (see attached for schema
and program). The table rows are reasonably wide:

select attname,n_distinct,avg_width from pg_stats where tablename='obj';
attname | n_distinct | avg_width
-------------+------------+-----------
obj_id | -1 | 4
obj_link_id | 5 | 4
obj_fil | 13035 | 1188

which may be a factor(tuplestore issues?)... The table is reasonably
sizable (10000000 rows). I can attach the generation program for this
dataset if required.

regards

Mark

Attachment Content-Type Size
cursor-bug.tar.gz application/x-gzip 1.5 KB

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andreas Nolte 2009-06-09 10:42:08 Bug in pg_dump.exe/pg_restore (Version 8.4 beta 2)
Previous Message Tom Lane 2009-06-08 13:57:35 Re: BUG #4843: non standard use string literal warning in RAISE WARNING

Browse pgsql-hackers by date

  From Date Subject
Next Message Grzegorz Jaskiewicz 2009-06-09 07:24:36 Re: Patch for automating partitions in PostgreSQL 8.4 Beta 2
Previous Message Kedar Potdar 2009-06-09 05:46:24 Re: Patch for automating partitions in PostgreSQL 8.4 Beta 2