In the program testlibpq.c, these five SQL statements are executed:
res = PQexec(conn, "BEGIN");
res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
res = PQexec(conn, "FETCH ALL in myportal");
res = PQexec(conn, "CLOSE myportal");
res = PQexec(conn, "END");
Is this just to illustrate how to create transactions and cursors, or is there
some material difference between trimming the program down to just:
res = PQexec(conn, "select * from pg_database");
Is there any value in my own query-only programs to declaring the cursor
for each search?
Many TIA,
Mark
--
Mark Harrison
Pixar Animation Studios
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match 5 2287
On Thursday 04 December 2003 22:46, Mark Harrison wrote: res = PQexec(conn, "BEGIN"); res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database"); res = PQexec(conn, "FETCH ALL in myportal"); res = PQexec(conn, "CLOSE myportal"); res = PQexec(conn, "END");
Is there any value in my own query-only programs to declaring the cursor for each search?
Well - if you want to scroll forward/backward through the resultset, you'd
want a cursor. Or, if your client had limited memory and the resultset was
large you might want to do so. PG will return all rows at once, so if your
SELECT returns 5 million rows you'll use a lot of RAM on the client side.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html mh@pixar.com (Mark Harrison) writes: In the program testlibpq.c, these five SQL statements are executed:
res = PQexec(conn, "BEGIN"); res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database"); res = PQexec(conn, "FETCH ALL in myportal"); res = PQexec(conn, "CLOSE myportal"); res = PQexec(conn, "END");
Is this just to illustrate how to create transactions and cursors, or is there some material difference between trimming the program down to just:
res = PQexec(conn, "select * from pg_database");
Is there any value in my own query-only programs to declaring the cursor for each search?
Yes, there is value in it, albeit not likely for that particular
query.
Let's suppose that you have a query that is ultimately going to return 45 million records.
If you use "select * from gory_query", then they'll all show up at
once.
If you declare a cursor, you can, in a loop, do something like:
while (data_left()) do {
res = PQexec(conn, "fetch 1000 in gory_query_curs or");
do_stuff_with(r es);
}
That'll pull the records in more bite-sized chunks, and allow you to
start processing data as soon as _some_ results come back. You don't
have to wait for the whole barrel of data to get dropped on you.
--
let name="cbbrowne" and tld="libertyrms .info" in String.concat "@" [name;tld];;
<http://dev6.int.libert yrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)
Richard Huxton wrote: On Thursday 04 December 2003 22:46, Mark Harrison wrote: res = PQexec(conn, "BEGIN"); res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database"); res = PQexec(conn, "FETCH ALL in myportal"); res = PQexec(conn, "CLOSE myportal"); res = PQexec(conn, "END");
Is there any value in my own query-only programs to declaring the cursor for each search?
Well - if you want to scroll forward/backward through the resultset, you'd want a cursor. Or, if your client had limited memory and the resultset was large you might want to do so. PG will return all rows at once, so if your SELECT returns 5 million rows you'll use a lot of RAM on the client side.
You really think people would ever want to store more than 640 rows?
Jan
--
#============== =============== =============== =============== ===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#============== =============== =============== ====== Ja******@Yahoo. com #
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Jan Wieck wrote: Richard Huxton wrote:
On Thursday 04 December 2003 22:46, Mark Harrison wrote:
res = PQexec(conn, "BEGIN"); res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database"); res = PQexec(conn, "FETCH ALL in myportal"); res = PQexec(conn, "CLOSE myportal"); res = PQexec(conn, "END");
Is there any value in my own query-only programs to declaring the cursor for each search?
Well - if you want to scroll forward/backward through the resultset, you'd want a cursor. Or, if your client had limited memory and the resultset was large you might want to do so. PG will return all rows at once, so if your SELECT returns 5 million rows you'll use a lot of RAM on the client side.
You really think people would ever want to store more than 640 rows?
Jan
Ha ha! With each one being 1K? You, sir, have a wicked sense of humor...
Mike Mascari ma*****@mascari .com
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Mark Harrison wrote: Is this just to illustrate how to create transactions and cursors, or is there some material difference between trimming the program down ...
But then you wouldn't be able to test that transactions, cursors and
queries work :) And you wouldn't be able to thumb your nose at your
friends who use that *other* popular database ;)
For your own purposes, you would only use cursors where you're expecting
to get back lots of data. Cursors not only save client memory, they save
network bandwidth too - you might have 2GB of RAM in your machine that
can copy data around at a rate of hundreds of megabytes per second, but
transferring that much data over a 100Mbps network takes time.
If you're looking to make your database feel faster, it can be better to
transfer one bunch of rows at a time. You might have an interface that
shows one pageful of details at a time - this is ideal cursor fodder,
since PostgreSQL can feed you the results in exactly the quantities that
you need for your pages.
Just a thought for the day :)
Alex
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Alban Hertroys |
last post by:
Oh no! It's me and transactions again :)
I'm not really sure whether this is a limitation of psycopg or
postgresql. When I use multiple cursors in a transaction, the records
inserted at the start of the transaction aren't visible to those later
on in that transaction (using a different cursor).
Attached is a simplified example (the except's are a bit blunt, I know)
of what I'm trying to do. In reality, the different cursors are...
|
by: Steven T. Hatton |
last post by:
Now that I have a better grasp of the scope and capabilities of the C++
Standard Library, I understand that products such as Qt actually provide
much of the same functionality through their own libraries. I'm not sure
if that's a good thing or not. AFAIK, most of Qt is compatable with the
Standard Library. That is, QLT can interoperate with STL, and you can
convert back and forth between std::string and Qt::QString, etc.
Are there any...
|
by: T.S.Negi |
last post by:
Hi All,
I want to avoid using cursors and loops in stored procedures.
Please suggest alternate solutions with example (if possible).
Any suggestion in these regards will be appreciated.
Thanks in advance,
T.S.Negi
|
by: Todd Huish |
last post by:
I have noticed something disturbing when retrieving datasets over a
relatively slow line (multiple T1). I am looking at about 25 seconds to
retrieve 500 rows via a php-odbc link. This same select from the cli is
for all intents practicaly instantaneous. After much research I discovered
that PHP by default uses a dynamic cursor type which can be quite a bit
slower than a forward only cursor. BTW I have been searching forward
only/read...
|
by: a |
last post by:
Hello,
I am doing some multithreading in an MDI app, and I can't seem to get the
cursor to stay as an Hourglass. I call:
Cursor.Current = cursors.wait
at the beginning of my routing, and set it back to cursors.default when the
thread ends (using a callback)
| |
by: Just Me |
last post by:
Does Me.Cursor.Current=Cursors.WaitCursor
set the current property of Me.Cursor to Cursors.WaitCursor
And Me.Cursor.Current=Cursors.Default set the Me.Current
property to something (default) stored in Me.Cursor.
Or is Cursors.Default some process wide cursor shape?
What is a correct statement?
|
by: H. Williams |
last post by:
I know the .Net Cursor class doesn't work with color cursors. So I'm
currently using the LoadCursorFromFile API with reflection to set color
cursors:
here is my code:
public static extern IntPtr LoadCursorFromFile(
string fileName );
IntPtr hwdCursor= LoadCursorFromFile( "color.cur" );
myCursor.GetType().InvokeMember("handle",BindingFlags.Public |
|
by: Netter |
last post by:
I'm confused about:
System.Windows.Forms.Cursor.Current = Cursors.WaitCursor
Seems I read in this NG that it is not necessary to reset Cursor.Current
because it gets reset automatically. Maybe when the sub is exited.
Is that correct?
If not, do I have to store the original value in order to reset it?
|
by: Dima Kuchin |
last post by:
Hello,
I was trying to find the information about when and where should I use
cursors in DB2, no luck. Maybe you can point me to some article that
describes just that (or tell me which page is it in DB2 SQL reference
book, if it's there)? Or maybe you can post a short answer here, if
there's no dedicated article?
Another question would be about scrollable cursors (just found out
about their existence) - what performance gains will I...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |