Re: [HACKERS] I cant find it or I'm just lazy ?
Bruce Momjian <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] I cant find it or I'm just lazy ?
I just tested the MOVE -5 in a simple case, and it worked:
test=> begin;
BEGIN
test=> declare xx cursor for select * from pg_class;
DECLARE CURSOR
test=> move from xx;
MOVE 157
test=> move -5 from xx;
MOVE 5
test=> 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
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] I cant find it or I'm just lazy ?
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
Re: [HACKERS] I cant find it or I'm just lazy ?
On Wed, Feb 26, 2003 at 09:44:14AM +, 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] I cant find it or I'm just lazy ?
On Tuesday 25 February 2003 18:57, Jeroen T. Vermeulen wrote: > On Tue, Feb 25, 2003 at 07:34:12PM +, 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 ! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] I cant find it or I'm just lazy ?
On Tue, Feb 25, 2003 at 07:34:12PM +, 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] I cant find it or I'm just lazy ?
On Tuesday 25 February 2003 17:14, Jeroen T. Vermeulen wrote: > On Tue, Feb 25, 2003 at 05:55:59PM +, 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 ! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] I cant find it or I'm just lazy ?
On Tue, Feb 25, 2003 at 05:55:59PM +, 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] I cant find it or I'm just lazy ?
-- Forwarded Message -- Subject: Re: [HACKERS] I cant find it or I'm just lazy ? Date: Tue, 25 Feb 2003 17:51:13 + From: Darko Prenosil <[EMAIL PROTECTED]> To: Christoph Haller <[EMAIL PROTECTED]> 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 +, 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 ! --- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] I cant find it or I'm just lazy ?
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] I cant find it or I'm just lazy ?
> > On Tuesday 25 February 2003 09:28, Christoph Haller wrote: > > > On Mon, Feb 24, 2003 at 07:53:05PM +, 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] I cant find it or I'm just lazy ?
> > > 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] I cant find it or I'm just lazy ?
On Tuesday 25 February 2003 09:28, Christoph Haller wrote: > > On Mon, Feb 24, 2003 at 07:53:05PM +, 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 ! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] I cant find it or I'm just lazy ?
On Tuesday 25 February 2003 09:28, Christoph Haller wrote:
> > On Mon, Feb 24, 2003 at 07:53:05PM +, 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 !
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] I cant find it or I'm just lazy ?
>
> On Mon, Feb 24, 2003 at 07:53:05PM +, 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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
