Interfaces that support cursors

[Network Administrator]

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)

[Doug McNaught]

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

[Network Administrator]

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

[Doug McNaught]

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

[Network Administrator]

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

[Jonathan Bartlett]

> 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

[Network Administrator]

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

[Christopher Browne]

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
[phone] x124 (land)

[Jonathan Bartlett]

> 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

[Network Administrator]

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)