Thread: PostgreSQL 8.3, libpq and WHERE CURRENT OF
Hi hackers, Just looked at the new features of 8.3 and realized that positioned updates/deletes is now possible with this new release... We would use that if we could define the cursor name with a libpq function. Something similar to ODBC's SQLSetCursorName() function... For now we must use OIDs to emulate WHERE CURRENT OF, but since 8.1, OIDs are no more created by default and require additional configuration when setting up a PostgreSQL server. If I missed something, please point me to the docs where I can find this. Understand this is not an isolated project: We are a Development Tool vendor and have a bunch of customers migrating legacy applications from Informix to PostgreSQL... Thanks a lot! Sebastien FLAESCH Database Interfaces Four J's Development Tools
Sebastien FLAESCH wrote: > Just looked at the new features of 8.3 and realized that positioned > updates/deletes is now possible with this new release... > > We would use that if we could define the cursor name with a libpq function. I don't understand. When you open a cursor with DECLARE CURSOR, you give it a name. Doesn't that do what you want? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Thanks Heikki for this quick answer. Actually we do the following lipq calls: - PQPrepare(... "SELECT ... FROM ... FOR UPDATE" ... ) - PQexecPrepared(...) - PQntuples(...) / PQgetvalue(...) i.e. we don't use the DECLARE CURSOR instruction, we just prepare/execute the plain SELECT statement (with potential parameters)... I can't remember why but there was some limitation or problems to use the DECLARE CURSOR in our context... must dig in my mail archive to give you more details... FYI we do actually a PostgreSQL driver for our runtime VM... Does a simple PQPrepare() with a SELECT statement not create a cursor on the server side? If yes, would it not be possible to pass a cursor name as in ODBC? Best regards, Seb Heikki Linnakangas wrote: > Sebastien FLAESCH wrote: >> Just looked at the new features of 8.3 and realized that positioned >> updates/deletes is now possible with this new release... >> >> We would use that if we could define the cursor name with a libpq function. > > I don't understand. When you open a cursor with DECLARE CURSOR, you give > it a name. Doesn't that do what you want? >
Sebastien FLAESCH <sf@4js.com> writes:
> Does a simple PQPrepare() with a SELECT statement not create a cursor on
> the server side?
No. A prepared statement is just a query plan, not a query-in-progress.
The Bind/Execute messages sent by PQexecPrepared create something akin
to a cursor, but libpq doesn't expose any API for fetching one row at a
time in that context, so there's no way to use the "current row" anyway.
regards, tom lane
Tom Lane wrote: > Sebastien FLAESCH <sf@4js.com> writes: >> Does a simple PQPrepare() with a SELECT statement not create a cursor on >> the server side? > > No. A prepared statement is just a query plan, not a query-in-progress. Yes of course, I meant PQprepare() + PQexecPrepared() ... > > The Bind/Execute messages sent by PQexecPrepared create something akin > to a cursor, but libpq doesn't expose any API for fetching one row at a > time in that context, so there's no way to use the "current row" anyway. OK... that makes total sense (sorry I jump from one database to another and sometimes I forget implementation details of a specific driver). Sounds like I need to rework my driver to enable row by row fetching with the DECLARE CURSOR + FETCH commands... right? As I wrote: I knew these commands before, but for some reason I did not use that solution because I faced problems. I started with PostgreSQL 7.1 so maybe there was some limitation that does no more exist in 8.x ... Maybe it was because there is not real OPEN command, so you can't distinguish the preparation phase from the execution phase with the DECLARE CURSOR instruction... Or maybe because cursors could only exist inside a transaction block? Just found this in the 7.1 doc: http://www.postgresql.org/docs/7.1/static/sql-declare.html " ERROR: DECLARE CURSOR may only be used in begin/end transaction blocks This error occurs if the cursor is not declared within a transaction block. " Anyway... Thanks a lot guys for these quick answers. That changes from Oracle or SQL Server support. Seb
Ok... Tested with 8.2.3: Actually you can't DECLARE a cursor outside a transaction: test1=> declare c1 cursor for select * from dbit2; ERROR: DECLARE CURSOR may only be used in transaction blocks That's the main reason why we don't use DECLARE CURSOR... I understand we could use DECLARE CURSOR when a FOR UPDATE is detected, to allow UPDATE/DELETE WHERE CURRENT OF - these only make sense inside a transaction... But for normal cursors, we need to support multiple active result sets that can last outside a transaction block. Basically, we need all what you can do with ODBC cursors. Anyway, thanks for your help. Seb Tom Lane wrote: > Sebastien FLAESCH <sf@4js.com> writes: >> Does a simple PQPrepare() with a SELECT statement not create a cursor on >> the server side? > > No. A prepared statement is just a query plan, not a query-in-progress. > > The Bind/Execute messages sent by PQexecPrepared create something akin > to a cursor, but libpq doesn't expose any API for fetching one row at a > time in that context, so there's no way to use the "current row" anyway. > > regards, tom lane >
Forget this one, just missing the WITH HOLD option... Must teach myself a bit more before sending further mails. Seb Sebastien FLAESCH wrote: > Ok... > > Tested with 8.2.3: > > Actually you can't DECLARE a cursor outside a transaction: > > test1=> declare c1 cursor for select * from dbit2; > ERROR: DECLARE CURSOR may only be used in transaction blocks > > That's the main reason why we don't use DECLARE CURSOR... > > I understand we could use DECLARE CURSOR when a FOR UPDATE is > detected, to allow UPDATE/DELETE WHERE CURRENT OF - these only > make sense inside a transaction... > > But for normal cursors, we need to support multiple active result > sets that can last outside a transaction block. > > Basically, we need all what you can do with ODBC cursors. > > Anyway, thanks for your help. > > Seb > > Tom Lane wrote: >> Sebastien FLAESCH <sf@4js.com> writes: >>> Does a simple PQPrepare() with a SELECT statement not create a cursor on >>> the server side? >> >> No. A prepared statement is just a query plan, not a query-in-progress. >> >> The Bind/Execute messages sent by PQexecPrepared create something akin >> to a cursor, but libpq doesn't expose any API for fetching one row at a >> time in that context, so there's no way to use the "current row" anyway. >> >> regards, tom lane >> > >
Sebastien FLAESCH wrote: > Forget this one, just missing the WITH HOLD option... Must teach myself a bit > more before sending further mails. Seb AFAIK you cannot use "WITH HOLD" together with updateable cursors. I might be wrong, though... regards, Florian Pflug
Florian, Do you mean WITH HOLD cursors (+ FOR UPDATE) can also be used to do DELETE/UPDATE WHERE CURRENT OF (i.e. outside transactions) ? From my experience this is an Informix-only feature, but if PostgreSQL supports that it would help a lot to migrate existing applications. I understand it's not a usual feature - one typically SELECT FOR UPDATE and DELETE / UPDATE WHERE CURRENT OF in a transaction block. I will give it a try anyway. Further, I was wondering if DECLARE CURSOR could be slower as plain SELECT statements. I will see this rapidly anyway so if someone can give me some tips about the overhead/cost of moving to DECLARE CURSOR, it would help me a lot. Remember I am talking about a driver for our VM = moving to DECLARE CURSOR would apply to all SELECT statements used in an application. Thanks Seb Florian G. Pflug wrote: > Sebastien FLAESCH wrote: >> Forget this one, just missing the WITH HOLD option... Must teach >> myself a bit >> more before sending further mails. Seb > > AFAIK you cannot use "WITH HOLD" together with updateable cursors. > I might be wrong, though... > > regards, Florian Pflug >
Just found this in the doc: "WITH HOLD may not be specified when the query includes FOR UPDATE or FOR SHARE" Here is what the Informix doc says about WITH HOLD + FOR UPDATE: "It is possible to declare an update cursor with the WITH HOLD keywords, but the only reason to do so is to break a long series of updates into smaller transactions. You must fetch and update a particular row in the same transaction." However, I believe Informix keeps the locks set by WITH HOLD + FOR UPDATE cursors, even if a transaction is terminated... locks are released when the cursor is closed. Seb Florian G. Pflug wrote: > Sebastien FLAESCH wrote: >> Forget this one, just missing the WITH HOLD option... Must teach >> myself a bit >> more before sending further mails. Seb > > AFAIK you cannot use "WITH HOLD" together with updateable cursors. > I might be wrong, though... > > regards, Florian Pflug >