Thread: I cant find it or I'm just lazy ?
I need two answers I did not find in documentation :
How can I get exact number of rows in DECLARED CURSOR ?
OK, I can FETCH until NULL, but this does not fits my needs !
How can I get information is TRANSACTION already started ?
(TRANSACTION LEVEL)
The interface I'm using is libpq.
Regards !
On Mon, Feb 24, 2003 at 07:53:05PM +0000, Darko Prenosil wrote: > I need two answers I did not find in documentation : > > How can I get exact number of rows in DECLARED CURSOR ? > OK, I can FETCH until NULL, but this does not fits my needs ! You need to move to the end of the cursor. When you declare a cursor it doesn't run the query yet. You have to tell it to run the query before it can tell you how many rows it is. I think the command is MOVE. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Support bacteria! They're the only culture some people have.
Attachment
>
> On Mon, Feb 24, 2003 at 07:53:05PM +0000, Darko Prenosil wrote:
> > I need two answers I did not find in documentation :
> > How can I get exact number of rows in DECLARED CURSOR ?
> > OK, I can FETCH until NULL, but this does not fits my needs !
You may want to use FETCH ALL, otherwise what or your needs in detail?
>
> You need to move to the end of the cursor. When you declare a cursor
it
> doesn't run the query yet. You have to tell it to run the query before
it
> can tell you how many rows it is. I think the command is MOVE.
>
But how could one MOVE to the last row?
> How can I get information is TRANSACTION already started ?
> (TRANSACTION LEVEL)
>
Either
SHOW TRANSACTION ISOLATION LEVEL ;
NOTICE: TRANSACTION ISOLATION LEVEL is READ COMMITTED
SHOW VARIABLE
or
select current_setting('TRANSACTION ISOLATION LEVEL');
Regards, Christoph
On Tuesday 25 February 2003 09:28, Christoph Haller wrote:
> > On Mon, Feb 24, 2003 at 07:53:05PM +0000, Darko Prenosil wrote:
> > > I need two answers I did not find in documentation :
> > > How can I get exact number of rows in DECLARED CURSOR ?
> > > OK, I can FETCH until NULL, but this does not fits my needs !
>
> You may want to use FETCH ALL, otherwise what or your needs in detail?
>
> > You need to move to the end of the cursor. When you declare a cursor
>
> it
>
> > doesn't run the query yet. You have to tell it to run the query before
>
> it
>
> > can tell you how many rows it is. I think the command is MOVE.
>
> But how could one MOVE to the last row?
>
> > How can I get information is TRANSACTION already started ?
> > (TRANSACTION LEVEL)
>
> Either
> SHOW TRANSACTION ISOLATION LEVEL ;
> NOTICE: TRANSACTION ISOLATION LEVEL is READ COMMITTED
> SHOW VARIABLE
> or
> select current_setting('TRANSACTION ISOLATION LEVEL');
>
> Regards, Christoph
I did not mean 'TRANSACTION ISOLATION LEVEL', but 'TRANSACTION LEVEL' !
OK, it is bad construction - my fault !
What I meant is : IS-TRANSACTION-ALREADY-STARTED ?
I used 'TRANSACTION LEVEL' because I saw that Bruce is working on nested
transactions, so in future there could be more than one transaction started ?
Thanks for Your reply !
On Tuesday 25 February 2003 09:28, Christoph Haller wrote: > > On Mon, Feb 24, 2003 at 07:53:05PM +0000, Darko Prenosil wrote: > > > I need two answers I did not find in documentation : > > > How can I get exact number of rows in DECLARED CURSOR ? > > > OK, I can FETCH until NULL, but this does not fits my needs ! > > You may want to use FETCH ALL, otherwise what or your needs in detail? > If I use FETCH ALL all, all the data will be sent to client, then why to use CURSOR at all ? I need to reduce network trafic on slow connections ! Regards !
> > > How can I get information is TRANSACTION already started ? > I did not mean 'TRANSACTION ISOLATION LEVEL', but 'TRANSACTION LEVEL' ! > OK, it is bad construction - my fault ! > What I meant is : IS-TRANSACTION-ALREADY-STARTED ? > I used 'TRANSACTION LEVEL' because I saw that Bruce is working on nested > transactions, so in future there could be more than one transaction started ? I could use something like IS-TRANSACTION-ALREADY-STARTED too, but AFAIK there is no such thing. Correct me if I am wrong, please. Regards, Christoph
> > On Tuesday 25 February 2003 09:28, Christoph Haller wrote: > > > On Mon, Feb 24, 2003 at 07:53:05PM +0000, Darko Prenosil wrote: > > > > I need two answers I did not find in documentation : > > > > How can I get exact number of rows in DECLARED CURSOR ? > > > > OK, I can FETCH until NULL, but this does not fits my needs ! > > > > You may want to use FETCH ALL, otherwise what or your needs in detail? > > > If I use FETCH ALL all, all the data will be sent to client, then why to use > CURSOR at all ? I need to reduce network trafic on slow connections ! > I cannot see how you are going to reduce network traffic by knowing in advance how many rows will be returned. Anyway, you may MOVE until 0 instead of FETCH, or use the COUNT() function on the query to learn about the number of rows to be returned. Regards, Christoph
On Tue, Feb 25, 2003 at 02:04:50PM +0100, Christoph Haller wrote: > > Anyway, you may MOVE until 0 instead of FETCH, or use the COUNT() > function on the query to learn about the number of rows to be returned. Hmm... Wouldn't the reliability of a count() depend on the isolation level? OTOH the problem with MOVE ALL is that not all cursors support backward scrolling, apparently, and there is no clear documentation (or even diagnostics!) to determine whether they do. Jeroen
---------- Forwarded Message ---------- Subject: Re: [HACKERS] I cant find it or I'm just lazy ? Date: Tue, 25 Feb 2003 17:51:13 +0000 From: Darko Prenosil <darko.prenosil@finteh.hr> To: Christoph Haller <ch@rodos.fzk.de> On Tuesday 25 February 2003 13:04, Christoph Haller wrote: > > On Tuesday 25 February 2003 09:28, Christoph Haller wrote: > > > > On Mon, Feb 24, 2003 at 07:53:05PM +0000, Darko Prenosil wrote: > > > > > I need two answers I did not find in documentation : > > > > > How can I get exact number of rows in DECLARED CURSOR ? > > > > > OK, I can FETCH until NULL, but this does not fits my needs ! > > > > > > You may want to use FETCH ALL, otherwise what or your needs in > > detail? > > > If I use FETCH ALL all, all the data will be sent to client, then why > > to use > > > CURSOR at all ? I need to reduce network traffic on slow connections ! > > I cannot see how you are going to reduce network traffic by knowing in > advance > how many rows will be returned. > Anyway, you may MOVE until 0 instead of FETCH, or use the COUNT() > function on the query to learn about the number of rows to be returned. I am trying to create client buffer that will show only records that are needed by application(visible). Data should be send to client in "pages", not all the data at once. The idea is not to query for data that are already in the buffer. Regards ! -------------------------------------------------------
On Tue, Feb 25, 2003 at 05:55:59PM +0000, Darko Prenosil wrote: > > I am trying to create client buffer that will show only records that are > needed by application(visible). Data should be send to client in "pages", not > all the data at once. The idea is not to query for data that are already in > the buffer. BTW, if your applications happens to be in C++, libpqxx has a class called CachedResult that would take a lot of this work out of your hands. It transparently fetches rows on-demand and caches them so they don't get read more than once. It can also figure out the size of your result set for you. You can find libpqxx at http://pqxx.tk/ Jeroen
On Tuesday 25 February 2003 17:14, Jeroen T. Vermeulen wrote: > On Tue, Feb 25, 2003 at 05:55:59PM +0000, Darko Prenosil wrote: > > I am trying to create client buffer that will show only records that are > > needed by application(visible). Data should be send to client in "pages", > > not all the data at once. The idea is not to query for data that are > > already in the buffer. > > BTW, if your applications happens to be in C++, libpqxx has a class > called CachedResult that would take a lot of this work out of your > hands. It transparently fetches rows on-demand and caches them so > they don't get read more than once. It can also figure out the > size of your result set for you. > > You can find libpqxx at http://pqxx.tk/ >Unfortunately it is application written in QT library that should work on Windows too, but I'll take a look, I'm sure I can learn something from it ! Regards !
On Tue, Feb 25, 2003 at 07:34:12PM +0000, Darko Prenosil wrote: > > Unfortunately it is application written in QT library that should work on > Windows too, but I'll take a look, I'm sure I can learn something from it ! Well, libpqxx also runs on Windows but it takes a decent compiler (e.g. Visual C++ 6.0 isn't quite good enough) and some manual labour to set up your own project file. Jeroen
On Tuesday 25 February 2003 18:57, Jeroen T. Vermeulen wrote: > On Tue, Feb 25, 2003 at 07:34:12PM +0000, Darko Prenosil wrote: > > Unfortunately it is application written in QT library that should work on > > Windows too, but I'll take a look, I'm sure I can learn something from it > > ! > > Well, libpqxx also runs on Windows but it takes a decent compiler (e.g. > Visual C++ 6.0 isn't quite good enough) and some manual labour to set up > your own project file. > I got the sources yesterday. Thank you !
On Wed, Feb 26, 2003 at 09:44:14AM +0000, Darko Prenosil wrote: > > I got the sources yesterday. Thank you ! Let me know whether everything works for you. There's also a mailing list on pqxx.tk if you need it. Jeroen
Jeroen T. Vermeulen wrote: > On Tue, Feb 25, 2003 at 02:04:50PM +0100, Christoph Haller wrote: > > > > Anyway, you may MOVE until 0 instead of FETCH, or use the COUNT() > > function on the query to learn about the number of rows to be returned. > > Hmm... Wouldn't the reliability of a count() depend on the isolation > level? > > OTOH the problem with MOVE ALL is that not all cursors support backward > scrolling, apparently, and there is no clear documentation (or even > diagnostics!) to determine whether they do. 7.4 does document MOVE ALL as going to the end of the cursor. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Thu, Mar 06, 2003 at 02:08:34PM -0500, Bruce Momjian wrote: > > > > OTOH the problem with MOVE ALL is that not all cursors support backward > > scrolling, apparently, and there is no clear documentation (or even > > diagnostics!) to determine whether they do. > > 7.4 does document MOVE ALL as going to the end of the cursor. Yes, but to do anything interesting *after* that...... Jeroen
Jeroen T. Vermeulen wrote: > On Thu, Mar 06, 2003 at 02:08:34PM -0500, Bruce Momjian wrote: > > > > > > OTOH the problem with MOVE ALL is that not all cursors support backward > > > scrolling, apparently, and there is no clear documentation (or even > > > diagnostics!) to determine whether they do. > > > > 7.4 does document MOVE ALL as going to the end of the cursor. > > Yes, but to do anything interesting *after* that...... Oh, I see now. Yep. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Thursday 06 March 2003 19:08, Bruce Momjian wrote: > Jeroen T. Vermeulen wrote: > > On Tue, Feb 25, 2003 at 02:04:50PM +0100, Christoph Haller wrote: > > > Anyway, you may MOVE until 0 instead of FETCH, or use the COUNT() > > > function on the query to learn about the number of rows to be returned. > > > > Hmm... Wouldn't the reliability of a count() depend on the isolation > > level? > > > > OTOH the problem with MOVE ALL is that not all cursors support backward > > scrolling, apparently, and there is no clear documentation (or even > > diagnostics!) to determine whether they do. > > 7.4 does document MOVE ALL as going to the end of the cursor. Great, Bruce is back ! I drop the idea to use cursors for recordset buffering, and I'm using temp tables. MOVE ALL can solve my first problem, but It can't solve the other one: How to know if there is transaction in progress ? The final facts were: For cursor: Fast, and less memory (concerning that only query plan is stored on server).Against cursor: I can't determineif transaction is already in progress, so I do not know can I COMMIT on cursor close. (Maybe some other of myrecordset controls started transactions before) For table: I do not need transactionAgainst table: More memory, and slower positioning in the buffer(using LIMIT andOFFSET) OK it is slower, but it works ! I must say one more thing I noticed experimenting with cursors:Let's say that we have cursor with 10 rows, if we MOVE 11rows, cursor become unusable, because even if we after that MOVE -5, no row can be fetched. I do not think that this is bug, but at last notice should be raised with warning that we missed the cursors size. I even find the code that is working with cursor, and tried to figure out how to fix this, but it is too much for me. Sorry ! Regards !
I just tested the MOVE -5 in a simple case, and it worked:
test=> begin;BEGINtest=> declare xx cursor for select * from pg_class;DECLARE CURSORtest=> move 99999999 from xx;MOVE
157test=>move -5 from xx;MOVE 5test=> fetch 1 from xx; relname | relnamespace | reltype | relowner | relam |
relfilenode| relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts |
relchecks| reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass |
relacl--------------+--------------+---------+----------+-------+-------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+---------------
pg_namespace| 11 | 16595 | 1 | 0 | 16594 | 1 | 5 | 0 |
0 | t | f | r | 3 | 0 | 0 | 0 | 0 | 0 | t
| f | f | f | {=r/postgres}(1 row)
What I think you are seeing are that certain cursors can't go backwards.
However, I don't know the details. Anyone?
---------------------------------------------------------------------------
Darko Prenosil wrote:
> On Thursday 06 March 2003 19:08, Bruce Momjian wrote:
> > Jeroen T. Vermeulen wrote:
> > > On Tue, Feb 25, 2003 at 02:04:50PM +0100, Christoph Haller wrote:
> > > > Anyway, you may MOVE until 0 instead of FETCH, or use the COUNT()
> > > > function on the query to learn about the number of rows to be returned.
> > >
> > > Hmm... Wouldn't the reliability of a count() depend on the isolation
> > > level?
> > >
> > > OTOH the problem with MOVE ALL is that not all cursors support backward
> > > scrolling, apparently, and there is no clear documentation (or even
> > > diagnostics!) to determine whether they do.
> >
> > 7.4 does document MOVE ALL as going to the end of the cursor.
>
> Great, Bruce is back !
> I drop the idea to use cursors for recordset buffering, and I'm using temp
> tables. MOVE ALL can solve my first problem, but It can't solve the other
> one: How to know if there is transaction in progress ? The final facts were:
>
> For cursor:
> Fast, and less memory (concerning that only query plan is stored on server).
> Against cursor:
> I can't determine if transaction is already in progress, so I do not
> know can I COMMIT on cursor close. (Maybe some other of my recordset
> controls started transactions before)
>
> For table:
> I do not need transaction
> Against table:
> More memory, and slower positioning in the buffer(using LIMIT and OFFSET)
>
> OK it is slower, but it works !
>
> I must say one more thing I noticed experimenting with cursors:
> Let's say that we have cursor with 10 rows, if we MOVE 11 rows, cursor
> become unusable, because even if we after that MOVE -5, no row can be
> fetched. I do not think that this is bug, but at last notice should be raised
> with warning that we missed the cursors size. I even find the code that is
> working with cursor, and tried to figure out how to fix this, but it is too
> much for me. Sorry !
>
> Regards !
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> What I think you are seeing are that certain cursors can't go backwards.
Lots of the more complex plan node types don't correctly implement
backwards fetch. I've looked briefly at fixing that, but it looks like
it'd be a major pain in the rear for some cases, such as Agg nodes.
A stopgap I've been considering is to add code that knows which plan node
types can go backwards. Then, if the cursor logic needs to go backwards
on a plan type that doesn't support it, it could instead rewind to start
(all plan types seem to support Rescan) and step forwards the correct
number of rows. This could be horribly inefficient but at least it
would work.
A less inefficient solution would be to stick a Materialize node atop
the plan, but the trouble is that would be a huge penalty for the common
cases where no backwards scan is actually ever done. Maybe we could
have the cursor logic insert the Materialize node on-the-fly when the
first backwards motion command is received. Also, we could implement
the SQL keyword "SCROLL" and say that you have to specify SCROLL if you
don't want this extra work to occur (with SCROLL, we could insert
Materialize if needed before starting).
regards, tom lane