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/
Show quoted text
Support bacteria! They're the only culture some people have.
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
Import Notes
Resolved by subject fallback
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 !
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
Import Notes
Resolved by subject fallback
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 !
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
Import Notes
Resolved by subject fallback
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
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
Import Notes
Reply to msg id not found: 200302251755.59195.darko.prenosil@finteh.hrReference msg id not found: 200302251755.59195.darko.prenosil@finteh.hr | Resolved by subject fallback
---------- 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 !
-------------------------------------------------------
Import Notes
Resolved by subject fallback
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
Import Notes
Reply to msg id not found: 200302251934.12317.darko.prenosil@finteh.hr
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 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, Pennsylvania 19073On 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, Pennsylvania 19073On 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 !
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 99999999 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?
--
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, Pennsylvania 19073
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