Обсуждение: libpq questuion
While update table whithin fetch forward loop cause infinite fetching of updated touple? (code fragment below cause infinite displayng record with oid 20864) ---------------------------------------------------------------------------- PQexec(_conn, "BEGIN"); PQexec(_conn, "DECLARE curr1 CURSOR FOR select oid, name from domains;"); while(1) { _res = PQexec(_conn, "FETCH FORWARD 1 IN curr1"); if( PQresultStatus(_res) != PGRES_TUPLES_OK ) break; PQexec(_conn, "update domains set type = 3 where oid = 20864" ); printf("oid: %s name: %s\n", PQgetvalue(_res,0,0),PQgetvalue(_res,0,1)); } --------------------------------------------------------------------------- -- Dmitry Samersoff DM\S, dms@wplus.net, AIM: Samersoff http://devnull.wplus.net
Dmitry Samersoff wrote: > > While update table whithin fetch forward loop > cause infinite fetching of updated touple? > > (code fragment below cause infinite > displayng record with oid 20864) > > > ---------------------------------------------------------------------------- > PQexec(_conn, "BEGIN"); > PQexec(_conn, "DECLARE curr1 CURSOR FOR select oid, name from domains;"); > > while(1) > { > _res = PQexec(_conn, "FETCH FORWARD 1 IN curr1"); > if( PQresultStatus(_res) != PGRES_TUPLES_OK ) break; > > PQexec(_conn, "update domains set type = 3 where oid = 20864" ); > printf("oid: %s name: %s\n", PQgetvalue(_res,0,0),PQgetvalue(_res,0,1)); > } > --------------------------------------------------------------------------- Which Postgres version? I guess it is a side effect from the visibility of tuples (records) in conjunction with portals (cursors). The reason must be that the the UPDATE inside the loop add's new tuples to the table at it's end. It also issues a CommandCounterIncrement(), so the new tuples get visible to the already running scan for the portal. I planned to get my hands onto the visibility code in tqual.c after v6.5 to prepare the system for deferred queries. I have some things about it in mind, but must discuss the details with Vadim before I start implementing it. They interfere with MVCC. Should I go for it before v6.5? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #
Hello all, > -----Original Message----- > From: owner-pgsql-hackers@postgreSQL.org > [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Jan Wieck > Sent: Monday, February 08, 1999 11:47 PM > To: dms@wplus.net > Cc: pgsql-hackers@hub.org > Subject: Re: [HACKERS] libpq questuion > > > Dmitry Samersoff wrote: > > > > > While update table whithin fetch forward loop > > cause infinite fetching of updated touple? > > > > (code fragment below cause infinite > > displayng record with oid 20864) > > > > > > > ------------------------------------------------------------------ > ---------- > > PQexec(_conn, "BEGIN"); > > PQexec(_conn, "DECLARE curr1 CURSOR FOR select oid, name from > domains;"); > > > > while(1) > > { > > _res = PQexec(_conn, "FETCH FORWARD 1 IN curr1"); > > if( PQresultStatus(_res) != PGRES_TUPLES_OK ) break; > > > > PQexec(_conn, "update domains set type = 3 where oid = 20864" ); > > printf("oid: %s name: %s\n", > PQgetvalue(_res,0,0),PQgetvalue(_res,0,1)); > > } > > > ------------------------------------------------------------------ > --------- > > Which Postgres version? > > I guess it is a side effect from the visibility of tuples > (records) in conjunction with portals (cursors). > > The reason must be that the the UPDATE inside the loop add's > new tuples to the table at it's end. It also issues a > CommandCounterIncrement(), so the new tuples get visible to > the already running scan for the portal. > Current cursors are strangely sensitive in some cases. So 4 months ago,I requested INSENSITIVE cursors.. I'm very happy if you will implement INSENSITIVE cursors. And all cursors will be INSENSITIVE even if there are no INSENSITIVE keywords ? Thanks. Hiroshi Inoue Inoue@tpf.co.jp