By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,372 Members | 1,906 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,372 IT Pros & Developers. It's quick & easy.

Interfaces that support cursors

P: n/a
I've got a Pg 7.1.3 release that I'm going to upgrade to 7.4 but in the meantime
I wanted to know if Pg.pm (or DBD:Pg) supported using cursors. I would guess
that they they don't because from what I understand every executed query in this
interface is implicitly wrapped in a transaction (thus there is an implicit
commit that would close the cursor).

I suppose I could use the fetchrow method (since the result set is not that big)
but in general, how should/could one handle implements cursors with this
interface. Thanks to all in advance...

--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Network Administrator <ne******@vcsn.com> writes:
I've got a Pg 7.1.3 release that I'm going to upgrade to 7.4 but in the meantime
I wanted to know if Pg.pm (or DBD:Pg) supported using cursors. I would guess
that they they don't because from what I understand every executed query in this
interface is implicitly wrapped in a transaction (thus there is an implicit
commit that would close the cursor).


I haven't used Pg.pm, but DBD::Pg has an "autocommit" flag when
creating a new connection, to control this behavior. If you turn off
autocommit, you can use DECLARE to create a cursor and FETCH to get
rows from it, just as with any other interface, as long as your
transaction is open.

The DBI and DBD::Pg docs describe everything pretty well.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #2

P: n/a
Quoting Doug McNaught <do**@mcnaught.org>:
Network Administrator <ne******@vcsn.com> writes:
I've got a Pg 7.1.3 release that I'm going to upgrade to 7.4 but in the

meantime
I wanted to know if Pg.pm (or DBD:Pg) supported using cursors. I would

guess
that they they don't because from what I understand every executed query in

this
interface is implicitly wrapped in a transaction (thus there is an

implicit
commit that would close the cursor).


I haven't used Pg.pm, but DBD::Pg has an "autocommit" flag when
creating a new connection, to control this behavior. If you turn off
autocommit, you can use DECLARE to create a cursor and FETCH to get
rows from it, just as with any other interface, as long as your
transaction is open.

The DBI and DBD::Pg docs describe everything pretty well.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


Ok, I did see the autocommit flag setting in DBD:Pg when I starting reading up
on the DBI/DBD interfacing methods so I guess I could recode for that. However,
how do you "maintain" the current transaction open if your script is writing
pages to the web. Even in mod_perl I think that there is a commit after the
script ends, no?

--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #3

P: n/a
Network Administrator <ne******@vcsn.com> writes:
Ok, I did see the autocommit flag setting in DBD:Pg when I starting
reading up on the DBI/DBD interfacing methods so I guess I could
recode for that. However, how do you "maintain" the current
transaction open if your script is writing pages to the web. Even
in mod_perl I think that there is a commit after the script ends,
no?


Oh, right--I didn't get that bit of your problem.

I think the conventional wisdom on this is that keeping transactions
open across web page deliveries is a Bad Idea. If you're just doing
the standard "show N records per page" thing, you can use LIMIT and
OFFSET on your SELECT call. This is going to be slower thn using a
transaction (because you're re-executing the query for every page) but
is fairly simple.

If you really want to have a DB transaction that covers multiple page
views, you need some kind of persistent application server rather than
CGI scripts, so you can keep open connections and application state
around.

-Doug

---------------------------(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

Nov 12 '05 #4

P: n/a
Quoting Doug McNaught <do**@mcnaught.org>:
Network Administrator <ne******@vcsn.com> writes:
Ok, I did see the autocommit flag setting in DBD:Pg when I starting
reading up on the DBI/DBD interfacing methods so I guess I could
recode for that. However, how do you "maintain" the current
transaction open if your script is writing pages to the web. Even
in mod_perl I think that there is a commit after the script ends,
no?


Oh, right--I didn't get that bit of your problem.

I think the conventional wisdom on this is that keeping transactions
open across web page deliveries is a Bad Idea. If you're just doing
the standard "show N records per page" thing, you can use LIMIT and
OFFSET on your SELECT call. This is going to be slower thn using a
transaction (because you're re-executing the query for every page) but
is fairly simple.

If you really want to have a DB transaction that covers multiple page
views, you need some kind of persistent application server rather than
CGI scripts, so you can keep open connections and application state
around.

-Doug


Ahhh, I didn't know about the offset part of limit. Sounds like winner- might
not be that bad since the query optimizer takes that into account when planning.

Thanks Doug.

--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #5

P: n/a
> Ok, I did see the autocommit flag setting in DBD:Pg when I starting reading up
on the DBI/DBD interfacing methods so I guess I could recode for that. However,
how do you "maintain" the current transaction open if your script is writing
pages to the web. Even in mod_perl I think that there is a commit after the
script ends, no?
Be careful you don't waste resources by leaving transactions open forever!

You can use something like PersistentPerl to make sure the script doesn't
terminate, but to get the behavior you're looking for you could only have
one script open at a time. You could also do master/worker scripts
communicating through sockets to keep the transaction open.

Jon


--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #6

P: n/a
Quoting Jonathan Bartlett <jo*****@eskimo.com>:
Ok, I did see the autocommit flag setting in DBD:Pg when I starting reading

up
on the DBI/DBD interfacing methods so I guess I could recode for that.

However,
how do you "maintain" the current transaction open if your script is

writing
pages to the web. Even in mod_perl I think that there is a commit after

the
script ends, no?


Be careful you don't waste resources by leaving transactions open forever!

You can use something like PersistentPerl to make sure the script doesn't
terminate, but to get the behavior you're looking for you could only have
one script open at a time. You could also do master/worker scripts
communicating through sockets to keep the transaction open.

Jon


--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


Absolutely- I was gonna dig into my mod_perl manual since I'm not sure if/how I
can make the HTML interfaces persist in Apache- I know the backend can (Apache
<-> Pg) but instead of increasing the complexity of things this time(all the
script does is write pages of images), I'll see how the selects with
offset/limit modifiers perform. I'm going to check our that Persistent Perl
product for future reference too. Thanks.

--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #7

P: n/a
do**@mcnaught.org (Doug McNaught) writes:
Network Administrator <ne******@vcsn.com> writes:
Ok, I did see the autocommit flag setting in DBD:Pg when I starting
reading up on the DBI/DBD interfacing methods so I guess I could
recode for that. However, how do you "maintain" the current
transaction open if your script is writing pages to the web. Even
in mod_perl I think that there is a commit after the script ends,
no?
Oh, right--I didn't get that bit of your problem.

I think the conventional wisdom on this is that keeping transactions
open across web page deliveries is a Bad Idea. If you're just doing
the standard "show N records per page" thing, you can use LIMIT and
OFFSET on your SELECT call. This is going to be slower thn using a
transaction (because you're re-executing the query for every page) but
is fairly simple.


If the set of data is pretty complex, this can Suck Really Badly.

A developer recently came to me with a more or less pathological case
where LIMIT/OFFSET on a particular query made it run for about 3000ms,
whereas dropping the LIMIT dropped query time to 75ms.

The problem was that the table was big, and the ORDER BY DATE caused
the LIMIT to force an index scan on the DATE field, when it would have
been preferable to use an index scan on customer ID, and sort the
resulting result set.

I haven't tried to "punt" that problem over to [PERFORM] because it's
pretty clear that a CURSOR is a better idea, as you suggest next.
If you really want to have a DB transaction that covers multiple page
views, you need some kind of persistent application server rather than
CGI scripts, so you can keep open connections and application state
around.


Right you are. The challenge, of course, is of how to properly expire
these objects.
--
(format nil "~S@~S" "cbbrowne" "libertyrms.info")
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)
Nov 12 '05 #8

P: n/a
> Absolutely- I was gonna dig into my mod_perl manual since I'm not sure if/how I
can make the HTML interfaces persist in Apache- I know the backend can (Apache
<-> Pg) but instead of increasing the complexity of things this time(all the
script does is write pages of images), I'll see how the selects with
offset/limit modifiers perform. I'm going to check our that Persistent Perl
product for future reference too. Thanks.


PersistentPerl Kicks Butt! You can use the standard CGI stuff, and still
get the speed benefits of hacks like mod_perl. Most of my bigger web
applications take about 5seconds for perl to parse, which would make them
useless without PersistentPerl.

One site I built using PersistentPerl is http://store.wolfram.com/ - I
don't know if they still use it, but I don't see why they would have
changed. It was a beauty.

Jon
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #9

P: n/a
Quoting Jonathan Bartlett <jo*****@eskimo.com>:
Absolutely- I was gonna dig into my mod_perl manual since I'm not sure

if/how I
can make the HTML interfaces persist in Apache- I know the backend can

(Apache
<-> Pg) but instead of increasing the complexity of things this time(all

the
script does is write pages of images), I'll see how the selects with
offset/limit modifiers perform. I'm going to check our that Persistent

Perl
product for future reference too. Thanks.


PersistentPerl Kicks Butt! You can use the standard CGI stuff, and still
get the speed benefits of hacks like mod_perl. Most of my bigger web
applications take about 5seconds for perl to parse, which would make them
useless without PersistentPerl.

One site I built using PersistentPerl is http://store.wolfram.com/ - I
don't know if they still use it, but I don't see why they would have
changed. It was a beauty.

Jon
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend


Cool... I can use this on the apps I wrote before I got into mod_perl. I might
just try to upgrade a couple of projects on Sunday. Let folks Monday morning
think there was a server upgrade :) Thanks again.

--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.