Обсуждение: Interfaces that support cursors
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
Network Administrator <netadmin@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
Quoting Doug McNaught <doug@mcnaught.org>: > Network Administrator <netadmin@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
Network Administrator <netadmin@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
Quoting Doug McNaught <doug@mcnaught.org>: > Network Administrator <netadmin@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
> 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 >
Quoting Jonathan Bartlett <johnnyb@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
> 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
Quoting Jonathan Bartlett <johnnyb@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
doug@mcnaught.org (Doug McNaught) writes: > Network Administrator <netadmin@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)