Why must a cursor be defined in an open transaction? Obviously there's
a good reason, but I can't figure it out. On a high level, what would
be involved in allowing a cursor to outlive the transaction that
created it?
Cursors seem as if they have some nice performance benefits (esp. if
you're not using all rows found), but their usefulness drops
considerably since you must leave a transaction open.
eric
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org
Nov 23 '05
18  3377 
Eric Ridge wrote: On Apr 6, 2004, at 11:54 AM, Jan Wieck wrote: If the underlying query is for example a simple sequential scan, then
the result set is not materialized but every future fetch operation
will read directly from the base table. This would obviously get
screwed up if vacuum would think nobody needs those rows any more.
Is vacuum the only thing that would muck with the rows?
Vacuum is the only thing that cares for the dustmites, yes.
I need to setup a 7.4 test server and play with this some, and figure
out if the benefits are really what I want them to be. I do appreciate
the insight into how cursors work... it helps a lot!
Experience and knowledge can only be replaced by more experience and
more knowledge.
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 7: don't forget to increase your free space map settings
On Apr 7, 2004, at 12:43 AM, Joe Conway wrote: Eric Ridge wrote: On Apr 6, 2004, at 11:54 AM, Jan Wieck wrote: And now you know why they are so good if you don't use all rows.
This benefit I think goes away if you use Joe Conway's suggestion of
WITH HOLD.
Okay, so WITH HOLD is actually materializing the entire resultset
(sequential scan or otherwise)? If that's true, you're right, some
of the benefits do go away.
Keep in mind that the tuplestore stays in memory as long as it fits
within sort_mem kilobytes. And you can do:
More good information. Thanks!
Is the tuplestore basically just an array of ItemPointer-s? In mean,
it's not a copy of each entire row, is it?
eric
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
On Apr 7, 2004, at 7:51 AM, Jan Wieck wrote: Eric Ridge wrote: On Apr 6, 2004, at 11:54 AM, Jan Wieck wrote: If the underlying query is for example a simple sequential scan,
then the result set is not materialized but every future fetch
operation will read directly from the base table. This would
obviously get screwed up if vacuum would think nobody needs those
rows any more.
Is vacuum the only thing that would muck with the rows?
Vacuum is the only thing that cares for the dustmites, yes.
And WITH HOLD is strong enough to defend against a vacuum, I hope... I need to setup a 7.4 test server and play with this some, and figure
out if the benefits are really what I want them to be. I do
appreciate the insight into how cursors work... it helps a lot!
Experience and knowledge can only be replaced by more experience and
more knowledge.
Very wise words.
My real problem is that the JDBC drivers (and I assume this is true for
all client interfaces) buffer the results of a SELECT in memory,
because the backend pushes out all the tuples as the response. I'm not
dealing with a large number of rows (only a few thousand), but they've
very wide, and many contain fields with multi-megabyte data. In some
situations, when I've got a lot of open ResultSets, the JVM throws
OutOfMemory errors.
One half-baked thought was to hack the JDBC drivers to have 'em gzip
large resultsets in memory. Wouldn't completely solve the problem, but
would probably help quite a bit. But the better solution is to use
cursors. We're not in a position to upgrade to 7.4 just yet, so we'll
just deal with the OutOfMemory errors until we can.
eric
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org
On 4/6/04 11:09 PM, "Tom Lane" <tg*@sss.pgh.pa .us> wrote: What "out of memory thing"? The tuplestore code is perfectly capable of
spilling to disk --- in fact the usual performance gripe against it has
to do with spilling too soon, because sort_mem is set too small.
I tried doing a mass update of all rows with a single SQL statement in psql
and after it ran for many hours, I got 'out of memory'. I didn't try that
using C and WITH HOLD. I assumed it ran out of swap space, but was sleeping
at the time.
Wes
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
On Wed, 7 Apr 2004, Eric Ridge wrote:
My real problem is that the JDBC drivers (and I assume this is true for
all client interfaces) buffer the results of a SELECT in memory,
because the backend pushes out all the tuples as the response. I'm not
dealing with a large number of rows (only a few thousand), but they've
very wide, and many contain fields with multi-megabyte data. In some
situations, when I've got a lot of open ResultSets, the JVM throws
OutOfMemory errors.
The 7.4 jdbc driver has the ability to use cursors behind the scenes on
queries. This is done by calling Statement.setFe tchSize(n) to retrieve n
rows at a time. There are a number of other restrictions: you must be in
a transaction and the ResultSet type must be FORWARD_ONLY. You can use
the 7.4 jdbc driver against a 7.3 server as well so this may provide some
relief.
Kris Jurka
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly
Eric Ridge wrote: Is the tuplestore basically just an array of ItemPointer-s? In mean,
it's not a copy of each entire row, is it?
Yup, it is copied:
src/backend/utils/sort/tuplestore.c:tu plestore_puttup le()
8<--------------------------------------------------------
/*
* Accept one tuple and append it to the tuplestore.
*
* Note that the input tuple is always copied; the caller need not save
* it.
8<--------------------------------------------------------
Joe
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
On Apr 7, 2004, at 2:01 PM, Kris Jurka wrote: The 7.4 jdbc driver has the ability to use cursors behind the scenes on
queries. This is done by calling Statement.setFe tchSize(n) to
retrieve n
rows at a time. There are a number of other restrictions: you must be
in
a transaction and the ResultSet type must be FORWARD_ONLY. You can use
the 7.4 jdbc driver against a 7.3 server as well so this may provide
some
relief.
I remember reading about this long ago. It's not really an option in
my little world because of the open transaction bit. My solution is
eventually going to be to upgrade to 7.4... heh, probably about the
time 7.5 is released. *sigh*
eric
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
On Apr 7, 2004, at 7:48 PM, Joe Conway wrote: Eric Ridge wrote: Is the tuplestore basically just an array of ItemPointer-s? In mean,
it's not a copy of each entire row, is it?
Yup, it is copied:
wow. I should go back and read the archives to see if this was
discussed already, but I can't help but wonder if there's a way to only
copy pointers to the tuples. I realize VACUUM could screw it up, but
maybe something could be invented (or re-used) to help guard against
that.
eric
---------------------------(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
Eric Ridge <eb*@tcdi.com > writes: wow. I should go back and read the archives to see if this was
discussed already, but I can't help but wonder if there's a way to only
copy pointers to the tuples. I realize VACUUM could screw it up, but
maybe something could be invented (or re-used) to help guard against
that.
Still looking for that free lunch, eh?
If you want to leave the tuples on-disk, then you hold a transaction
open to ensure that VACUUM won't whisk them out from under you. That's
what the normal non-HOLD cursor case will do.
If you don't want to leave the tuples on-disk, you have to copy them
someplace. You can do that with a HOLD cursor.
AFAICS any other solution will simply reinvent one or the other of these
techniques.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster 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: Dave Anderson |
last post by:
Can anyone point me to a good resource for learning cursors in MSSQL?
Thanks
Dave
|
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: Mark Harrison |
last post by:
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:
|
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: 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: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
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...
| | |
