Thread: php with postgres
what do you think about plphp ?
> -----Original Message----- > From: ivan [mailto:iv@psycho.pl] > Sent: 12 July 2003 22:34 > To: pgsql-hackers@postgresql.org > Subject: [HACKERS] php with postgres > > > > what do you think about plphp ? I know a few people that would probably welcome it. Regards, Dave.
On Sat, 12 Jul 2003, Dave Page wrote: > > > > -----Original Message----- > > From: ivan [mailto:iv@psycho.pl] > > Sent: 12 July 2003 22:34 > > To: pgsql-hackers@postgresql.org > > Subject: [HACKERS] php with postgres > > > > > > > > what do you think about plphp ? > > I know a few people that would probably welcome it. > > Regards, Dave. > ok, but php should build this lang for postgres i think so, we should talk with php group ?
> -----Original Message----- > From: ivan [mailto:iv@psycho.pl] > Sent: 12 July 2003 23:02 > To: Dave Page > Cc: pgsql-hackers@postgresql.org > Subject: RE: [HACKERS] php with postgres > > > > > ok, but php should build this lang for postgres i think > so, we should talk with php group ? I doubt they will do the work - you would have to (or persuade someone else to). Regards, Dave.
ivan wrote: > ok, but php should build this lang for postgres i think > so, we should talk with php group ? I have been talking with several people about this on-and-off for a while now. If I can find some time in the next few months, I will probably write it (if no one beats me to it). I'm thinking that it should be written using Postgres 7.5devel (once 7.4 stable is branched) and PHP5 (which is in alpha/early-beta testing). Joe
Joe Conway wrote: > ivan wrote: >> ok, but php should build this lang for postgres i think >> so, we should talk with php group ? > > I have been talking with several people about this on-and-off for a > while now. If I can find some time in the next few months, I will > probably write it (if no one beats me to it). I'm thinking that it > should be written using Postgres 7.5devel (once 7.4 stable is branched) > and PHP5 (which is in alpha/early-beta testing). I had been briefly talking with Marcus Boerger (included in CC) from the PHP team about it. He knows the PHP5 SAPI embed well. Can you include him into the team (if not already)? From what I know about this SAPI I think the PL/Tcl implementation would be a good point to start from, as it looks very similar with respect to the possibilities. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck wrote: > I had been briefly talking with Marcus Boerger (included in CC) from the > PHP team about it. He knows the PHP5 SAPI embed well. Can you include > him into the team (if not already)? Sure! > From what I know about this SAPI I think the PL/Tcl implementation > would be a good point to start from, as it looks very similar with > respect to the possibilities. I was going to start from PL/R, which is a descendent of PL/Tcl -- reason being, in PL/R I've already got SRF/table-function support and polymorphic argument/return-type support working. Also, I've done a fair amount of work to preserve arrays and composite types as they move back-and-forth. My plan is to add a few missing things to PL/R over the next couple (or so) weeks, and then start PL/PHP from that: 1) Cache lookup based on function oid and argument signature ala the patch I recently sent in (and improved by Tom) forPL/pgSQL -- this is needed to properly support polymorphic arguments. 2) Trigger support -- just haven't needed this so far, but we'll want it in PL/PHP, so I may as well add it to PL/R too. 3) Re-add nested error handling -- I removed this from PL/R early on just to simplify life. Should be easy to drop backin. I've read some examples posted regarding the PHP embed SAPI, and it looks very similar to the R interpreter also. It should be fairly easy to drop the PHP embed calls in for the libR calls. The bulk of the work will be in modifying the data conversion functions that map Postgres composite types and arrays to similar structures in PHP. Help on the PHP side of things would be most appreciated, because that's the part I'm least familiar with. Joe
what aoubt stream ? in plpgsql you can just write command INSERT ... or DELETE if you want sht like this in php you need to correct zend i think . in php all var is declared as variant type but we need look at realy type. I have other view, to first write php interpreter to postgres, and then write a translator, which translate plphp code to C code . I cound be only a another way, to remember about speed (compiled code is always faster then src ) . Php source will be to testing, and to relese will be option to translate this src to C src and then compile it. ps. there could be also interpreter with debug version and relese version (without checking some things) dont forget about default value of functions args !! :> On Sun, 13 Jul 2003, Joe Conway wrote: > Jan Wieck wrote: > > I had been briefly talking with Marcus Boerger (included in CC) from the > > PHP team about it. He knows the PHP5 SAPI embed well. Can you include > > him into the team (if not already)? > > Sure! > > > From what I know about this SAPI I think the PL/Tcl implementation > > would be a good point to start from, as it looks very similar with > > respect to the possibilities. > > I was going to start from PL/R, which is a descendent of PL/Tcl -- > reason being, in PL/R I've already got SRF/table-function support and > polymorphic argument/return-type support working. Also, I've done a fair > amount of work to preserve arrays and composite types as they move > back-and-forth. > > My plan is to add a few missing things to PL/R over the next couple (or > so) weeks, and then start PL/PHP from that: > 1) Cache lookup based on function oid and argument signature ala the > patch I recently sent in (and improved by Tom) for PL/pgSQL -- this > is needed to properly support polymorphic arguments. > 2) Trigger support -- just haven't needed this so far, but we'll want it > in PL/PHP, so I may as well add it to PL/R too. > 3) Re-add nested error handling -- I removed this from PL/R early on > just to simplify life. Should be easy to drop back in. > > I've read some examples posted regarding the PHP embed SAPI, and it > looks very similar to the R interpreter also. It should be fairly easy > to drop the PHP embed calls in for the libR calls. The bulk of the work > will be in modifying the data conversion functions that map Postgres > composite types and arrays to similar structures in PHP. > > Help on the PHP side of things would be most appreciated, because that's > the part I'm least familiar with. > > Joe >
Hello ivan, Sunday, July 13, 2003, 10:12:43 PM, you wrote: i> what aoubt stream ? i> in plpgsql you can just write command INSERT ... or DELETE i> if you want sht like this in php you need to correct zend i think . i> in php all var is declared as variant type but we need look at realy type. In php we only have a few base types (int, float, bool, string) and some more complex types like array and objects. The general idea is that at least the base types can be converted without notice. This might be a problem when integrating php into postgres but i guess everything can be solved the php way. Since i also have zend commit rights i could fix things in this manner as long as the language itself doesn't change in any way. i> I have other view, to first write php interpreter to postgres, and then i> write a translator, which translate plphp code to C code . I cound be only i> a another way, to remember about speed (compiled code is always faster i> then src ) . Php source will be to testing, and to relese will be option i> to translate this src to C src and then compile it. The general idea should be to leave php as is. That is, it is an interpreter. During LT we were again able to speed it up very much. So performance difference from interpreter to a real php to c compiler shouldn't be a problem for the moment. Also i thing when someone consideres using php inside his database he a) does it because he doesn't know any other language or b) he uses very advanced language features. In both cases the performance problem is no issue. Additionally there are already tokenizers out which remove the compile step. So atm we only can't get rid of the interpreter overhead. Best regards,Marcus mailto:marcus.boerger@post.rwth-aachen.de
Hello Jan, Sunday, July 13, 2003, 1:22:09 PM, you wrote: JW> Joe Conway wrote: >> ivan wrote: >>> ok, but php should build this lang for postgres i think >>> so, we should talk with php group ? >> >> I have been talking with several people about this on-and-off for a >> while now. If I can find some time in the next few months, I will >> probably write it (if no one beats me to it). I'm thinking that it >> should be written using Postgres 7.5devel (once 7.4 stable is branched) >> and PHP5 (which is in alpha/early-beta testing). JW> I had been briefly talking with Marcus Boerger (included in CC) from the JW> PHP team about it. He knows the PHP5 SAPI embed well. Can you include JW> him into the team (if not already)? JW> From what I know about this SAPI I think the PL/Tcl implementation JW> would be a good point to start from, as it looks very similar with JW> respect to the possibilities. JW> Jan Hello all, Nice to hear from you all :-) I met Bruce during LinuxTag fair/conference here in germany and talked to him about this plan. I subscribed to the essential mailing lists right now but i am not yet a member of the postgres dev team. However i will give have a deeper look during next weekend or the next week. So that i can tell more in a few days. Apart from that you may bug me with postgres/php bugs since it seems i am current php's ext/pgsql maintainer. Best regards,Marcus mailto:marcus.boerger@post.rwth-aachen.de
Added to TODO:
o Add PL/PHP (Joe, Jan)
---------------------------------------------------------------------------
Joe Conway wrote:
> Jan Wieck wrote:
> > I had been briefly talking with Marcus Boerger (included in CC) from the
> > PHP team about it. He knows the PHP5 SAPI embed well. Can you include
> > him into the team (if not already)?
>
> Sure!
>
> > From what I know about this SAPI I think the PL/Tcl implementation
> > would be a good point to start from, as it looks very similar with
> > respect to the possibilities.
>
> I was going to start from PL/R, which is a descendent of PL/Tcl --
> reason being, in PL/R I've already got SRF/table-function support and
> polymorphic argument/return-type support working. Also, I've done a fair
> amount of work to preserve arrays and composite types as they move
> back-and-forth.
>
> My plan is to add a few missing things to PL/R over the next couple (or
> so) weeks, and then start PL/PHP from that:
> 1) Cache lookup based on function oid and argument signature ala the
> patch I recently sent in (and improved by Tom) for PL/pgSQL -- this
> is needed to properly support polymorphic arguments.
> 2) Trigger support -- just haven't needed this so far, but we'll want it
> in PL/PHP, so I may as well add it to PL/R too.
> 3) Re-add nested error handling -- I removed this from PL/R early on
> just to simplify life. Should be easy to drop back in.
>
> I've read some examples posted regarding the PHP embed SAPI, and it
> looks very similar to the R interpreter also. It should be fairly easy
> to drop the PHP embed calls in for the libR calls. The bulk of the work
> will be in modifying the data conversion functions that map Postgres
> composite types and arrays to similar structures in PHP.
>
> Help on the PHP side of things would be most appreciated, because that's
> the part I'm least familiar with.
>
> Joe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
Glad you got in touch with the right guys. Joe and Jan have both talked about doing PlPHP for a while. Marcus, would you check if PHP is using RESET ALL when passing persistent connection to new clients? We added that capability a few releases ago, specifically for PHP persistent connections, but I don't think that ever got into the PHP code. --------------------------------------------------------------------------- Marcus B?rger wrote: > Hello ivan, > > Sunday, July 13, 2003, 10:12:43 PM, you wrote: > > > i> what aoubt stream ? > i> in plpgsql you can just write command INSERT ... or DELETE > i> if you want sht like this in php you need to correct zend i think . > i> in php all var is declared as variant type but we need look at realy type. > > In php we only have a few base types (int, float, bool, string) and some more > complex types like array and objects. The general idea is that at least the > base types can be converted without notice. This might be a problem when > integrating php into postgres but i guess everything can be solved the php > way. Since i also have zend commit rights i could fix things in this manner as > long as the language itself doesn't change in any way. > > i> I have other view, to first write php interpreter to postgres, and then > i> write a translator, which translate plphp code to C code . I cound be only > i> a another way, to remember about speed (compiled code is always faster > i> then src ) . Php source will be to testing, and to relese will be option > i> to translate this src to C src and then compile it. > > The general idea should be to leave php as is. That is, it is an interpreter. > During LT we were again able to speed it up very much. So performance > difference from interpreter to a real php to c compiler shouldn't be a problem > for the moment. > Also i thing when someone consideres using php inside his database he a) does > it because he doesn't know any other language or b) he uses very advanced > language features. In both cases the performance problem is no issue. > Additionally there are already tokenizers out which remove the > compile step. So atm we only can't get rid of the interpreter overhead. > > Best regards, > Marcus mailto:marcus.boerger@post.rwth-aachen.de > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Marcus B?rger wrote: > BM> Marcus, would you check if PHP is using RESET ALL when passing > BM> persistent connection to new clients? We added that capability a few > BM> releases ago, specifically for PHP persistent connections, but I don't > BM> think that ever got into the PHP code. > > Unfortunately we don't do so yet. Do i need to check for errors or can i do it > unconditionally on conenction start? And i'd need to know how to check if it > is available (like starting with which version). It first appeared in PostgreSQL version 7.2. It doesn't generate any failures. It just resets all SET settting to their defaults, in case the previous client modified them. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Marcus B?rger wrote: > Hello Bruce, > > Monday, July 21, 2003, 9:37:08 PM, you wrote: > > BM> Marcus B?rger wrote: > >> BM> Marcus, would you check if PHP is using RESET ALL when passing > >> BM> persistent connection to new clients? We added that capability a few > >> BM> releases ago, specifically for PHP persistent connections, but I don't > >> BM> think that ever got into the PHP code. > >> > >> Unfortunately we don't do so yet. Do i need to check for errors or can i do it > >> unconditionally on conenction start? And i'd need to know how to check if it > >> is available (like starting with which version). > > BM> It first appeared in PostgreSQL version 7.2. It doesn't generate any > BM> failures. It just resets all SET settting to their defaults, in case > BM> the previous client modified them. > > > Committed for PHP 5.0 if there is a version 4.4 or 4.5 i'll commit the patch > for that version, too. Now we need someone with a big site to test whether it > brings any new problems. If there aren't problems i could probably also commit I think Theis originally complained about it, perhaps two years ago. > to PHP 4.3.3. Btw. i don't do RESET ALL when creating the link because that > shouldn't be necessary. Right. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
> Help on the PHP side of things would be most appreciated, because that's > the part I'm least familiar with. SAPI/Embed in PHP is very experimental which means that it can be molded to suit PL/PHP needs. To my knowlege its only used as a plugin for irssi (irc client which you can script using PHP), and a very poorly implemented MySQL module. Anyway if you need any help with it just shout :) Edin
Bruce Momjian wrote:
> Marcus B?rger wrote:
>> BM> Marcus, would you check if PHP is using RESET ALL when passing
>> BM> persistent connection to new clients? We added that capability a few
>> BM> releases ago, specifically for PHP persistent connections, but I don't
>> BM> think that ever got into the PHP code.
>>
>> Unfortunately we don't do so yet. Do i need to check for errors or can i do it
>> unconditionally on conenction start? And i'd need to know how to check if it
>> is available (like starting with which version).
>
> It first appeared in PostgreSQL version 7.2. It doesn't generate any
> failures. It just resets all SET settting to their defaults, in case
> the previous client modified them.
>
It does generate the usual error if the current transaction block is in
ABORT state. So the correct querystring to send would be something like
"ROLLBACK; RESET ALL"
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
Jan Wieck wrote: > Bruce Momjian wrote: > > Marcus B?rger wrote: > >> BM> Marcus, would you check if PHP is using RESET ALL when passing > >> BM> persistent connection to new clients? We added that capability a few > >> BM> releases ago, specifically for PHP persistent connections, but I don't > >> BM> think that ever got into the PHP code. > >> > >> Unfortunately we don't do so yet. Do i need to check for errors or can i do it > >> unconditionally on conenction start? And i'd need to know how to check if it > >> is available (like starting with which version). > > > > It first appeared in PostgreSQL version 7.2. It doesn't generate any > > failures. It just resets all SET settting to their defaults, in case > > the previous client modified them. > > > > It does generate the usual error if the current transaction block is in > ABORT state. So the correct querystring to send would be something like > > "ROLLBACK; RESET ALL" Oh, I remember that now as part of the persistent connection code. As I remember, we told them to do BEGIN;COMMIT; to clear any open transaction state passed to the new client. Is that in there? If not, it has to be added too. ROLLBACK will generate an error if you are not in a transaction, so it would fill the logs with errors. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Edin Kadribasic wrote: >>Help on the PHP side of things would be most appreciated, because that's >>the part I'm least familiar with. > > > SAPI/Embed in PHP is very experimental which means that it can be molded to > suit PL/PHP needs. To my knowlege its only used as a plugin for irssi (irc > client which you can script using PHP), and a very poorly implemented MySQL > module. > > Anyway if you need any help with it just shout :) > Great! I'm likely to take you up on that before it's over ;-) It will probably be a few more weeks before I can get started on this, but it's definitely on my short list. Joe
Hello Bruce, Monday, July 21, 2003, 9:15:05 PM, you wrote: BM> Glad you got in touch with the right guys. Joe and Jan have both talked BM> about doing PlPHP for a while. :-) BM> Marcus, would you check if PHP is using RESET ALL when passing BM> persistent connection to new clients? We added that capability a few BM> releases ago, specifically for PHP persistent connections, but I don't BM> think that ever got into the PHP code. Unfortunately we don't do so yet. Do i need to check for errors or can i do it unconditionally on conenction start? And i'd need to know how to check if it is available (like starting with which version). Best regards,Marcus mailto:marcus.boerger@post.rwth-aachen.de
Hello Bruce, Monday, July 21, 2003, 9:37:08 PM, you wrote: BM> Marcus B?rger wrote: >> BM> Marcus, would you check if PHP is using RESET ALL when passing >> BM> persistent connection to new clients? We added that capability a few >> BM> releases ago, specifically for PHP persistent connections, but I don't >> BM> think that ever got into the PHP code. >> >> Unfortunately we don't do so yet. Do i need to check for errors or can i do it >> unconditionally on conenction start? And i'd need to know how to check if it >> is available (like starting with which version). BM> It first appeared in PostgreSQL version 7.2. It doesn't generate any BM> failures. It just resets all SET settting to their defaults, in case BM> the previous client modified them. Committed for PHP 5.0 if there is a version 4.4 or 4.5 i'll commit the patch for that version, too. Now we need someone with a big site to test whether it brings any new problems. If there aren't problems i could probably also commit to PHP 4.3.3. Btw. i don't do RESET ALL when creating the link because that shouldn't be necessary. -- Best regards,Marcus mailto:marcus.boerger@post.rwth-aachen.de
Marcus Börger wrote: > > Here's the current log while reusing the persistent connection: > > DEBUG: InitPostgres > DEBUG: StartTransactionCommand > DEBUG: query: select getdatabaseencoding() > DEBUG: ProcessQuery > DEBUG: CommitTransactionCommand > DEBUG: StartTransactionCommand > DEBUG: query: RESET ALL > DEBUG: ProcessUtility: RESET ALL > DEBUG: CommitTransactionCommand > DEBUG: StartTransactionCommand > DEBUG: query: BEGIN;ROLLBACK; > DEBUG: ProcessUtility: BEGIN;ROLLBACK; > DEBUG: CommitTransactionCommand > DEBUG: StartTransactionCommand > DEBUG: ProcessUtility: BEGIN;ROLLBACK; > DEBUG: CommitTransactionCommand > DEBUG: pq_recvbuf: unexpected EOF on client connection > And this is the wrong order of things. The BEGIN;ROLLBACK; has to be done first, otherwise if the connection was left in an aborted open transaction by the previous script, the other two actions will fail. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Mon, 21 Jul 2003, Bruce Momjian wrote: > Jan Wieck wrote: > > Bruce Momjian wrote: > > > Marcus B?rger wrote: > > >> BM> Marcus, would you check if PHP is using RESET ALL when passing > > >> BM> persistent connection to new clients? We added that capability a few > > >> BM> releases ago, specifically for PHP persistent connections, but I don't > > >> BM> think that ever got into the PHP code. > > >> > > >> Unfortunately we don't do so yet. Do i need to check for errors or can i do it > > >> unconditionally on conenction start? And i'd need to know how to check if it > > >> is available (like starting with which version). > > > > > > It first appeared in PostgreSQL version 7.2. It doesn't generate any > > > failures. It just resets all SET settting to their defaults, in case > > > the previous client modified them. > > > > > > > It does generate the usual error if the current transaction block is in > > ABORT state. So the correct querystring to send would be something like > > > > "ROLLBACK; RESET ALL" > > Oh, I remember that now as part of the persistent connection code. As I > remember, we told them to do BEGIN;COMMIT; to clear any open transaction > state passed to the new client. Is that in there? If not, it has to be > added too. ROLLBACK will generate an error if you are not in a > transaction, so it would fill the logs with errors. Won't that break when we have nested transactions implemented? i.e. begin;commit; would just open a sub transaction and have no effect on the outer transaction...
scott.marlowe wrote: > > > It does generate the usual error if the current transaction block is in > > > ABORT state. So the correct querystring to send would be something like > > > > > > "ROLLBACK; RESET ALL" > > > > Oh, I remember that now as part of the persistent connection code. As I > > remember, we told them to do BEGIN;COMMIT; to clear any open transaction > > state passed to the new client. Is that in there? If not, it has to be > > added too. ROLLBACK will generate an error if you are not in a > > transaction, so it would fill the logs with errors. > > Won't that break when we have nested transactions implemented? i.e. > begin;commit; would just open a sub transaction and have no effect on the > outer transaction... Yes, it would break. I am not sure how we are going to flag that we want to rollback all nested transactions, maybe ROLLBACK ALL. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On 7/22/2003 11:18 AM, scott.marlowe wrote: >On Mon, 21 Jul 2003, Bruce Momjian wrote: > > > >>Jan Wieck wrote: >> >> >>>Bruce Momjian wrote: >>> >>> >>>>Marcus B?rger wrote: >>>> >>>> >>>>>BM> Marcus, would you check if PHP is using RESET ALL when passing >>>>>BM> persistent connection to new clients? We added that capability a few >>>>>BM> releases ago, specifically for PHP persistent connections, but I don't >>>>>BM> think that ever got into the PHP code. >>>>> >>>>>Unfortunately we don't do so yet. Do i need to check for errors or can i do it >>>>>unconditionally on conenction start? And i'd need to know how to check if it >>>>>is available (like starting with which version). >>>>> >>>>> >>>>It first appeared in PostgreSQL version 7.2. It doesn't generate any >>>>failures. It just resets all SET settting to their defaults, in case >>>>the previous client modified them. >>>> >>>> >>>> >>>It does generate the usual error if the current transaction block is in >>>ABORT state. So the correct querystring to send would be something like >>> >>> "ROLLBACK; RESET ALL" >>> >>> >>Oh, I remember that now as part of the persistent connection code. As I >>remember, we told them to do BEGIN;COMMIT; to clear any open transaction >>state passed to the new client. Is that in there? If not, it has to be >>added too. ROLLBACK will generate an error if you are not in a >>transaction, so it would fill the logs with errors. >> >> > >Won't that break when we have nested transactions implemented? i.e. >begin;commit; would just open a sub transaction and have no effect on the >outer transaction... > > I was just about to mention that one. Perhaps a ROLLBACK ALL would be of benefit to allow a clean state and start to work again. Doesn't autocommit behavior affect this as well? > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster > >
Thomas Swan wrote: > >>Oh, I remember that now as part of the persistent connection code. As I > >>remember, we told them to do BEGIN;COMMIT; to clear any open transaction > >>state passed to the new client. Is that in there? If not, it has to be > >>added too. ROLLBACK will generate an error if you are not in a > >>transaction, so it would fill the logs with errors. > >> > >> > > > >Won't that break when we have nested transactions implemented? i.e. > >begin;commit; would just open a sub transaction and have no effect on the > >outer transaction... > > > > > I was just about to mention that one. Perhaps a ROLLBACK ALL would be > of benefit to allow a clean state and start to work again. > > Doesn't autocommit behavior affect this as well? No, autocommit is now in the clients, but even if it was in the server, BEGIN;COMMIT would be OK. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
> > Won't that break when we have nested transactions implemented? i.e. > > begin;commit; would just open a sub transaction and have no effect on the > > outer transaction... > > Yes, it would break. I am not sure how we are going to flag that we > want to rollback all nested transactions, maybe ROLLBACK ALL. Shouldn't the results of PQtransactionStatus() override any 'pre-canned' guess about how to abort a potential transaction since you know the exact state of the protocol? If PQprotocolVersion() == 2 then do things the old way (always begin / rollback). If either of the above functions are not present (pre-7.4 version of PostgreSQL) then always begin / rollback.
Yes, but you probably want old clients to be able to use the new nested transaction code. --------------------------------------------------------------------------- Rod Taylor wrote: -- Start of PGP signed section. > > > Won't that break when we have nested transactions implemented? i.e. > > > begin;commit; would just open a sub transaction and have no effect on the > > > outer transaction... > > > > Yes, it would break. I am not sure how we are going to flag that we > > want to rollback all nested transactions, maybe ROLLBACK ALL. > > Shouldn't the results of PQtransactionStatus() override any 'pre-canned' > guess about how to abort a potential transaction since you know the > exact state of the protocol? > > If PQprotocolVersion() == 2 then do things the old way (always begin / > rollback). > > If either of the above functions are not present (pre-7.4 version of > PostgreSQL) then always begin / rollback. -- End of PGP section, PGP failed! -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Marcus B?rger wrote: > Yeah well it seems i gave a wrong impression what is actually happening now. > We don't handle any transaction at the moment but starting with the second > call to pg_pconnect on the same db we do RESET ALL. The following log is from > a three connections, so you can see two RESET ALL. > > DEBUG: BackendStartup: forked pid=28253 socket=8 > LOG: query: select getdatabaseencoding() > LOG: query: RESET ALL > LOG: query: RESET ALL > LOG: query: BEGIN;ROLLBACK; > LOG: unexpected EOF on client connection > > However it may be very usefull to terminate any open transaction before > reusing a persisten connection. Typically this happens when the same script > runs again. But anyway using transactions together with persistent conenctions > in a multithreaded environment isn't the best thing you could do. So our > options are > 1) tell the users to do 'auto commit mode' > 2) nested transactions > 3) locking > > >From my perspective 2) and 3) are bad ideas for the web environment. In other > words i guess we should leave it as is with transaction rollback only when the > client terminates (e.g. the webserver stops). I don't see why you wouldn't just do BEGIN;COMMIT;RESET ALL; when you pass the connection to a new client. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote: > Marcus B?rger wrote: >> However it may be very usefull to terminate any open transaction before >> reusing a persisten connection. Typically this happens when the same script >> runs again. But anyway using transactions together with persistent conenctions >> in a multithreaded environment isn't the best thing you could do. So our >> options are >> 1) tell the users to do 'auto commit mode' >> 2) nested transactions >> 3) locking >> >> >From my perspective 2) and 3) are bad ideas for the web environment. In other >> words i guess we should leave it as is with transaction rollback only when the >> client terminates (e.g. the webserver stops). > > I don't see why you wouldn't just do BEGIN;COMMIT;RESET ALL; when you > pass the connection to a new client. > Right, and I don't see why using transactions in a multithreaded environment would be a bad idea. However an application is designed, one logical unit of changes, called a business transaction, has to have one database transaction modifying the business relevantinformation. There could be other transactions involved for dialog handling and advisory locking. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Marcus B?rger wrote: > Hello Jan, > > Tuesday, July 22, 2003, 10:57:56 PM, you wrote: > > JW> Bruce Momjian wrote: > >> Marcus B?rger wrote: > >>> However it may be very usefull to terminate any open transaction before > >>> reusing a persisten connection. Typically this happens when the same script > >>> runs again. But anyway using transactions together with persistent conenctions > >>> in a multithreaded environment isn't the best thing you could do. So our > >>> options are > >>> 1) tell the users to do 'auto commit mode' > >>> 2) nested transactions > >>> 3) locking > >>> > >>> >From my perspective 2) and 3) are bad ideas for the web environment. In other > >>> words i guess we should leave it as is with transaction rollback only when the > >>> client terminates (e.g. the webserver stops). > >> > >> I don't see why you wouldn't just do BEGIN;COMMIT;RESET ALL; when you > >> pass the connection to a new client. > >> > > JW> Right, and I don't see why using transactions in a multithreaded > JW> environment would be a bad idea. However an application is designed, one > JW> logical unit of changes, called a business transaction, has to have one > JW> database transaction modifying the business relevant information. > JW> There could be other transactions involved for dialog handling and > JW> advisory locking. > > > So i'll update to "BEGIN;COMMIT;RESET ALL;" :-) Yes, I think that is it. The RESET ALL has to be at the end because you have to make sure the RESET is in a valid transaction. The only way to make sure autocommit doesn't effect you would be to do: BEGIN;COMMIT;BEGIN;RESET ALL;COMMIT; but the autocommit GUC option was only in 7.3 and will not be in 7.4, so this seems like overkill. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
> > DEBUG: InitPostgres > > DEBUG: StartTransactionCommand > > DEBUG: query: select getdatabaseencoding() > > DEBUG: ProcessQuery > > DEBUG: CommitTransactionCommand > > DEBUG: StartTransactionCommand > > DEBUG: query: RESET ALL > > DEBUG: ProcessUtility: RESET ALL > > DEBUG: CommitTransactionCommand > > DEBUG: StartTransactionCommand > > DEBUG: query: BEGIN;ROLLBACK; > > DEBUG: ProcessUtility: BEGIN;ROLLBACK; > > DEBUG: CommitTransactionCommand > > DEBUG: StartTransactionCommand > > DEBUG: ProcessUtility: BEGIN;ROLLBACK; > > DEBUG: CommitTransactionCommand > > DEBUG: pq_recvbuf: unexpected EOF on client connection > > > > And this is the wrong order of things. The BEGIN;ROLLBACK; has to be > done first, otherwise if the connection was left in an aborted open > transaction by the previous script, the other two actions will fail. Surely PHP can be modified so as to use the new 3.0 protocol feature to detect whether it's in a transaction or not, so as to avoid unnecssary querying? Chris
Hello Jan, Tuesday, July 22, 2003, 10:57:56 PM, you wrote: JW> Bruce Momjian wrote: >> Marcus B?rger wrote: >>> However it may be very usefull to terminate any open transaction before >>> reusing a persisten connection. Typically this happens when the same script >>> runs again. But anyway using transactions together with persistent conenctions >>> in a multithreaded environment isn't the best thing you could do. So our >>> options are >>> 1) tell the users to do 'auto commit mode' >>> 2) nested transactions >>> 3) locking >>> >>> >From my perspective 2) and 3) are bad ideas for the web environment. In other >>> words i guess we should leave it as is with transaction rollback only when the >>> client terminates (e.g. the webserver stops). >> >> I don't see why you wouldn't just do BEGIN;COMMIT;RESET ALL; when you >> pass the connection to a new client. >> JW> Right, and I don't see why using transactions in a multithreaded JW> environment would be a bad idea. However an application is designed, one JW> logical unit of changes, called a business transaction, has to have one JW> database transaction modifying the business relevant information. JW> There could be other transactions involved for dialog handling and JW> advisory locking. So i'll update to "BEGIN;COMMIT;RESET ALL;" :-) -- Best regards,Marcus mailto:marcus.boerger@post.rwth-aachen.de
Hello Bruce, Tuesday, July 22, 2003, 10:16:53 PM, you wrote: BM> Marcus B?rger wrote: >> Yeah well it seems i gave a wrong impression what is actually happening now. >> We don't handle any transaction at the moment but starting with the second >> call to pg_pconnect on the same db we do RESET ALL. The following log is from >> a three connections, so you can see two RESET ALL. >> >> DEBUG: BackendStartup: forked pid=28253 socket=8 >> LOG: query: select getdatabaseencoding() >> LOG: query: RESET ALL >> LOG: query: RESET ALL >> LOG: query: BEGIN;ROLLBACK; >> LOG: unexpected EOF on client connection >> >> However it may be very usefull to terminate any open transaction before >> reusing a persisten connection. Typically this happens when the same script >> runs again. But anyway using transactions together with persistent conenctions >> in a multithreaded environment isn't the best thing you could do. So our >> options are >> 1) tell the users to do 'auto commit mode' >> 2) nested transactions >> 3) locking >> >> >From my perspective 2) and 3) are bad ideas for the web environment. In other >> words i guess we should leave it as is with transaction rollback only when the >> client terminates (e.g. the webserver stops). BM> I don't see why you wouldn't just do BEGIN;COMMIT;RESET ALL; when you BM> pass the connection to a new client. Bruce you said RESET ALL is available since 7.2. I am currently checking for the lib version but it would be more correct to check something on the server. So the question what do i check? -- Best regards,Marcus mailto:marcus.boerger@post.rwth-aachen.de
Hello Jan, Tuesday, July 22, 2003, 1:28:18 PM, you wrote: JW> Marcus Börger wrote: >> >> Here's the current log while reusing the persistent connection: >> >> DEBUG: InitPostgres >> DEBUG: StartTransactionCommand >> DEBUG: query: select getdatabaseencoding() >> DEBUG: ProcessQuery >> DEBUG: CommitTransactionCommand >> DEBUG: StartTransactionCommand >> DEBUG: query: RESET ALL >> DEBUG: ProcessUtility: RESET ALL >> DEBUG: CommitTransactionCommand >> DEBUG: StartTransactionCommand >> DEBUG: query: BEGIN;ROLLBACK; >> DEBUG: ProcessUtility: BEGIN;ROLLBACK; >> DEBUG: CommitTransactionCommand >> DEBUG: StartTransactionCommand >> DEBUG: ProcessUtility: BEGIN;ROLLBACK; >> DEBUG: CommitTransactionCommand >> DEBUG: pq_recvbuf: unexpected EOF on client connection >> JW> And this is the wrong order of things. The BEGIN;ROLLBACK; has to be JW> done first, otherwise if the connection was left in an aborted open JW> transaction by the previous script, the other two actions will fail. Yeah well it seems i gave a wrong impression what is actually happening now. We don't handle any transaction at the moment but starting with the second call to pg_pconnect on the same db we do RESET ALL. The following log is from a three connections, so you can see two RESET ALL. DEBUG: BackendStartup: forked pid=28253 socket=8 LOG: query: select getdatabaseencoding() LOG: query: RESET ALL LOG: query: RESET ALL LOG: query: BEGIN;ROLLBACK; LOG: unexpected EOF on client connection However it may be very usefull to terminate any open transaction before reusing a persisten connection. Typically this happens when the same script runs again. But anyway using transactions together with persistent conenctions in a multithreaded environment isn't the best thing you could do. So our options are 1) tell the users to do 'auto commit mode' 2) nested transactions 3) locking From my perspective 2) and 3) are bad ideas for the web environment. In other words i guess we should leave it as is with transaction rollback only when the client terminates (e.g. the webserver stops). Best regards,Marcus mailto:marcus.boerger@post.rwth-aachen.de
Hello Bruce, Tuesday, July 22, 2003, 3:20:27 AM, you wrote: BM> Jan Wieck wrote: >> Bruce Momjian wrote: >> > Marcus B?rger wrote: >> >> BM> Marcus, would you check if PHP is using RESET ALL when passing >> >> BM> persistent connection to new clients? We added that capability a few >> >> BM> releases ago, specifically for PHP persistent connections, but I don't >> >> BM> think that ever got into the PHP code. >> >> >> >> Unfortunately we don't do so yet. Do i need to check for errors or can i do it >> >> unconditionally on conenction start? And i'd need to know how to check if it >> >> is available (like starting with which version). >> > >> > It first appeared in PostgreSQL version 7.2. It doesn't generate any >> > failures. It just resets all SET settting to their defaults, in case >> > the previous client modified them. >> > >> >> It does generate the usual error if the current transaction block is in >> ABORT state. So the correct querystring to send would be something like >> >> "ROLLBACK; RESET ALL" BM> Oh, I remember that now as part of the persistent connection code. As I BM> remember, we told them to do BEGIN;COMMIT; to clear any open transaction BM> state passed to the new client. Is that in there? If not, it has to be BM> added too. ROLLBACK will generate an error if you are not in a BM> transaction, so it would fill the logs with errors. Here's the current log while reusing the persistent connection: DEBUG: InitPostgres DEBUG: StartTransactionCommand DEBUG: query: select getdatabaseencoding() DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: RESET ALL DEBUG: ProcessUtility: RESET ALL DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: BEGIN;ROLLBACK; DEBUG: ProcessUtility: BEGIN;ROLLBACK; DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: ProcessUtility: BEGIN;ROLLBACK; DEBUG: CommitTransactionCommand DEBUG: pq_recvbuf: unexpected EOF on client connection -- Best regards,Marcus mailto:marcus.boerger@post.rwth-aachen.de
Christopher Kings-Lynne wrote: > > > DEBUG: InitPostgres > > > DEBUG: StartTransactionCommand > > > DEBUG: query: select getdatabaseencoding() > > > DEBUG: ProcessQuery > > > DEBUG: CommitTransactionCommand > > > DEBUG: StartTransactionCommand > > > DEBUG: query: RESET ALL > > > DEBUG: ProcessUtility: RESET ALL > > > DEBUG: CommitTransactionCommand > > > DEBUG: StartTransactionCommand > > > DEBUG: query: BEGIN;ROLLBACK; > > > DEBUG: ProcessUtility: BEGIN;ROLLBACK; > > > DEBUG: CommitTransactionCommand > > > DEBUG: StartTransactionCommand > > > DEBUG: ProcessUtility: BEGIN;ROLLBACK; > > > DEBUG: CommitTransactionCommand > > > DEBUG: pq_recvbuf: unexpected EOF on client connection > > > > > > > And this is the wrong order of things. The BEGIN;ROLLBACK; has to be > > done first, otherwise if the connection was left in an aborted open > > transaction by the previous script, the other two actions will fail. > > Surely PHP can be modified so as to use the new 3.0 protocol feature to > detect whether it's in a transaction or not, so as to avoid unnecssary > querying? Yes, you could, but it hardly seems worth it because they have to support old and new protocols. Eventually, yes, they could use that to eliminate the BEGIN;COMMIT. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Marcus B?rger wrote: > >> However it may be very usefull to terminate any open transaction before > >> reusing a persisten connection. Typically this happens when the same script > >> runs again. But anyway using transactions together with persistent conenctions > >> in a multithreaded environment isn't the best thing you could do. So our > >> options are > >> 1) tell the users to do 'auto commit mode' > >> 2) nested transactions > >> 3) locking > >> > >> >From my perspective 2) and 3) are bad ideas for the web environment. In other > >> words i guess we should leave it as is with transaction rollback only when the > >> client terminates (e.g. the webserver stops). > > BM> I don't see why you wouldn't just do BEGIN;COMMIT;RESET ALL; when you > BM> pass the connection to a new client. > > > Bruce you said RESET ALL is available since 7.2. I am currently checking for > the lib version but it would be more correct to check something on the server. > So the question what do i check? We usually use SELECT version(). -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
> > Surely PHP can be modified so as to use the new 3.0 protocol feature to > > detect whether it's in a transaction or not, so as to avoid unnecssary > > querying? > > Yes, you could, but it hardly seems worth it because they have to > support old and new protocols. Eventually, yes, they could use that to > eliminate the BEGIN;COMMIT. Just use a configure test to see if you have the appropriate function call in your libpq... Chris
Marcus B?rger wrote: > BM> Oh, I remember that now as part of the persistent connection code. As I > BM> remember, we told them to do BEGIN;COMMIT; to clear any open transaction > BM> state passed to the new client. Is that in there? If not, it has to be > BM> added too. ROLLBACK will generate an error if you are not in a > BM> transaction, so it would fill the logs with errors. > > Here's the current log while reusing the persistent connection: > > DEBUG: InitPostgres > DEBUG: StartTransactionCommand > DEBUG: query: select getdatabaseencoding() > DEBUG: ProcessQuery > DEBUG: CommitTransactionCommand > DEBUG: StartTransactionCommand > DEBUG: query: RESET ALL > DEBUG: ProcessUtility: RESET ALL > DEBUG: CommitTransactionCommand > DEBUG: StartTransactionCommand > DEBUG: query: BEGIN;ROLLBACK; > DEBUG: ProcessUtility: BEGIN;ROLLBACK; > DEBUG: CommitTransactionCommand > DEBUG: StartTransactionCommand > DEBUG: ProcessUtility: BEGIN;ROLLBACK; > DEBUG: CommitTransactionCommand > DEBUG: pq_recvbuf: unexpected EOF on client connection Sorry, this doesn't look right. It should be BEGIN;COMMIT;RESET ALL; What I see above looks more like RESET ALL;BEGIN;COMMIT;, which will not work if the RESET ALL is runs in aborted transaction created by the previous client connection. Also, it looks like BEGIN;ROLLBACK is being executed twice. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Marcus B?rger wrote: > >> BM> I don't see why you wouldn't just do BEGIN;COMMIT;RESET ALL; when you > >> BM> pass the connection to a new client. > >> > >> > >> Bruce you said RESET ALL is available since 7.2. I am currently checking for > >> the lib version but it would be more correct to check something on the server. > >> So the question what do i check? > > BM> We usually use SELECT version(). > > > ATM i have a patch doing the following: > Connect: > If PQprotocolVersion() is available and >= 3 PQparameterStatus() is available > then i check the server version. Else i check the lib version (*). > If the version to check is >= 7.2 ido one of the following: > - If one of PQprotocolVersion() and PQtransactionStatus() is unavailable or > protocol version < 3: > "BEGIN;COMMIT;RESET ALL;" I believe this should be BEGIN;ROLLBACK;RESET ALL; because our default for a client that disconnects is to abort the transaction. > - If protocol version >= 3 and transaction status == PQTRANS_IDLE: > "RESET ALL;" > - If protocol version >= 3 and transaction status != PQTRANS_IDLE: > "COMMIT;RESET ALL;" Should be "ROLLBACK;RESET ALL;". Nice version test code, sounds good. > > Disconnect: > - When PQprotocolVersion() And PQtransactionStatus() are available then > i check whether status is PQTRANS_IDLE. If so i do: > "ROLLBACK;" > - If the functions are not available in the client libs i do: > "BEGIN;ROLLBACK;" > > Does this sound the correct behavior? I am confused why you are doing stuff on connect and disconnect. Seems it should all be done on disconnect so you don't leave open transactions in the pooled connections --- it will keep locks around too long and reduce the usefulness of vacuum. If we clean up everything on disconnect, aren't we sure that the connection status will be fine? > And would "select split_part(version(), ' ', 2);" be too much of a slowdown to > do the version detection in the startup sequence completely correct? Seems fine. Since you are doing pooled connections, you shouldn't be doing this too often anyway. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Marcus B?rger wrote: > BM> I believe this should be BEGIN;ROLLBACK;RESET ALL; because our default > BM> for a client that disconnects is to abort the transaction. > > >> - If protocol version >= 3 and transaction status == PQTRANS_IDLE: > >> "RESET ALL;" > >> - If protocol version >= 3 and transaction status != PQTRANS_IDLE: > >> "COMMIT;RESET ALL;" > > BM> Should be "ROLLBACK;RESET ALL;". > > Because of above mentioned default? > The problem i have is that now we do the COMMIT - so this behavior change can > only go into php 5 with a big notice. Well, our behavior has always been to abort any open transctions on client exit. In fact, it is so obvious, we don't even document it. There are some SQL databases that commit on client exit, but that sounds just wrong to us. (They might distinguish between client close and client connection failure, but I am just guessing.) You could have removed money from one account but not added it to a second account when your client died. You can try it yourself by starting a transaction in psql, inserting some data, and exiting psql. When you reconnect, your data will not be in the database. Pooled connections should work just like non-pooled connections --- in fact, that is what we are trying to improve with RESET ALL, so it seems like a clear bug fix to change the code from COMMIT to ROLLBACK. Of course, you know the PHP community better than I do. > BM> Nice version test code, sounds good. > > >> > >> Disconnect: > >> - When PQprotocolVersion() And PQtransactionStatus() are available then > >> i check whether status is PQTRANS_IDLE. If so i do: > >> "ROLLBACK;" > >> - If the functions are not available in the client libs i do: > >> "BEGIN;ROLLBACK;" > >> > >> Does this sound the correct behavior? > > BM> I am confused why you are doing stuff on connect and disconnect. Seems > BM> it should all be done on disconnect so you don't leave open transactions > BM> in the pooled connections --- it will keep locks around too long and > BM> reduce the usefulness of vacuum. If we clean up everything on > BM> disconnect, aren't we sure that the connection status will be fine? > > >> And would "select split_part(version(), ' ', 2);" be too much of a slowdown to > >> do the version detection in the startup sequence completely correct? > > BM> Seems fine. Since you are doing pooled connections, you shouldn't be > BM> doing this too often anyway. > > I believe the point was "RESET ALL;". But maybe i can move all but that into > shutdown. I mean for me that sounds good. Only someone must enlighten me if > there could be a problem with that reset. No, no problems. You are guaranteed to get a reset values when you first start a backend, so there isn't anything to do on startup if you get the disconnect cleaned up properly, and as I mentioned, there are advantages to having that stuff done in shutdown so the transaction isn't left open. I see this pooled connection stuff is more complicated that it first appears. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Marcus Börger wrote:
> ATM i have a patch doing the following:
> Connect:
> If PQprotocolVersion() is available and >= 3 PQparameterStatus() is available
> then i check the server version. Else i check the lib version (*).
> If the version to check is >= 7.2 ido one of the following:
> - If one of PQprotocolVersion() and PQtransactionStatus() is unavailable or
> protocol version < 3:
> "BEGIN;COMMIT;RESET ALL;"
>
> - If protocol version >= 3 and transaction status == PQTRANS_IDLE:
> "RESET ALL;"
> - If protocol version >= 3 and transaction status != PQTRANS_IDLE:
> "COMMIT;RESET ALL;"
Shouldn't that be
ROLLBACK; RESET ALL;
and the other one "BEGIN; ROLLBACK; RESET ALL;" ?
I don't want to have the possibly partial transaction from a crashed PHP
script to be committed by default. At least it would be a significant
difference between persistent and non-persistent connections, because we
rollback if we loose the connection.
Jan
>
> Disconnect:
> - When PQprotocolVersion() And PQtransactionStatus() are available then
> i check whether status is PQTRANS_IDLE. If so i do:
> "ROLLBACK;"
> - If the functions are not available in the client libs i do:
> "BEGIN;ROLLBACK;"
>
> Does this sound the correct behavior?
>
> And would "select split_part(version(), ' ', 2);" be too much of a slowdown to
> do the version detection in the startup sequence completely correct?
>
>
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
Jan Wieck wrote: > Marcus B?rger wrote: > > ATM i have a patch doing the following: > > Connect: > > If PQprotocolVersion() is available and >= 3 PQparameterStatus() is available > > then i check the server version. Else i check the lib version (*). > > If the version to check is >= 7.2 ido one of the following: > > - If one of PQprotocolVersion() and PQtransactionStatus() is unavailable or > > protocol version < 3: > > "BEGIN;COMMIT;RESET ALL;" > > > > - If protocol version >= 3 and transaction status == PQTRANS_IDLE: > > "RESET ALL;" > > - If protocol version >= 3 and transaction status != PQTRANS_IDLE: > > "COMMIT;RESET ALL;" > > Shouldn't that be > > ROLLBACK; RESET ALL; > > and the other one "BEGIN; ROLLBACK; RESET ALL;" ? > > I don't want to have the possibly partial transaction from a crashed PHP > script to be committed by default. At least it would be a significant > difference between persistent and non-persistent connections, because we > rollback if we loose the connection. Right, as I just emailed. I wonder if we made the mistake of recommending BEGIN;COMMIT; to the PHP folks a while back, or whether they just did it themselves. I remember telling them they couldn't just do ROLLBACK because that would fill the logs, but I am not sure how they got BEGIN;COMMIT; rather than BEGIN;ROLLBACK. It just shows we need better communication between the communities. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Thu, Jul 24, 2003 at 05:52:07PM -0400, Bruce Momjian wrote: > Well, our behavior has always been to abort any open transctions on > client exit. In fact, it is so obvious, we don't even document it. > > There are some SQL databases that commit on client exit, but that sounds > just wrong to us. (They might distinguish between client close and But not so obvious, then, eh? I hadn't realised it wasn't documented, but I think there ought to be a mention of it, maybe in section 3.4, right after it talks about deciding to abort with ROLLBACK. You could add something like, "Also, because every transaction must either completely succeed or completely fail, PostgreSQL will automatically abort a transaction in process if the client disconnects before issuing COMMIT." A -- ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <andrew@libertyrms.info> M2P 2A8 +1 416 646 3304 x110
Are you all done with the PHP persistent connection changes? Need any help? --------------------------------------------------------------------------- Marcus B?rger wrote: > Hello Christopher, > > Wednesday, July 23, 2003, 6:49:05 AM, you wrote: > > >> > Surely PHP can be modified so as to use the new 3.0 protocol feature to > >> > detect whether it's in a transaction or not, so as to avoid unnecssary > >> > querying? > >> > >> Yes, you could, but it hardly seems worth it because they have to > >> support old and new protocols. Eventually, yes, they could use that to > >> eliminate the BEGIN;COMMIT. > > CKL> Just use a configure test to see if you have the appropriate function call > CKL> in your libpq... > > I've put that already into PHP yesturday so i could add the necessary check > now too. > > -- > Best regards, > Marcus mailto:marcus.boerger@post.rwth-aachen.de > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
OK, I checked the URL's, and I am a little confused. I don't understand
why the conditional transaction abort isn't in the same place as the
RESET ALL. Seems they should be done at the same time, though there is
an advantage to doing the RESET ALL during connect, because if someone
changes the postgres.conf setting between the disconnect and the
connect, you would then see the new values, which seems correct.
Is that what is happening? If it is, this line is wrong:
pg_result = PQexec(pgsql, "BEGIN;ROLLBACK;RESET ALL;");
because you have already rolled back the connection during the
disconnect. This is the code for old servers.
---------------------------------------------------------------------------
Marcus B?rger wrote:
> Hello Bruce,
>
> Thursday, July 24, 2003, 11:57:39 PM, you wrote:
>
> BM> Jan Wieck wrote:
> >> Marcus B?rger wrote:
> >> > ATM i have a patch doing the following:
> >> > Connect:
> >> > If PQprotocolVersion() is available and >= 3 PQparameterStatus() is available
> >> > then i check the server version. Else i check the lib version (*).
> >> > If the version to check is >= 7.2 ido one of the following:
> >> > - If one of PQprotocolVersion() and PQtransactionStatus() is unavailable or
> >> > protocol version < 3:
> >> > "BEGIN;COMMIT;RESET ALL;"
> >> >
> >> > - If protocol version >= 3 and transaction status == PQTRANS_IDLE:
> >> > "RESET ALL;"
> >> > - If protocol version >= 3 and transaction status != PQTRANS_IDLE:
> >> > "COMMIT;RESET ALL;"
> >>
> >> Shouldn't that be
> >>
> >> ROLLBACK; RESET ALL;
> >>
> >> and the other one "BEGIN; ROLLBACK; RESET ALL;" ?
> >>
> >> I don't want to have the possibly partial transaction from a crashed PHP
> >> script to be committed by default. At least it would be a significant
> >> difference between persistent and non-persistent connections, because we
> >> rollback if we loose the connection.
>
> BM> Right, as I just emailed. I wonder if we made the mistake of
> BM> recommending BEGIN;COMMIT; to the PHP folks a while back, or whether they
> BM> just did it themselves.
>
> BM> I remember telling them they couldn't just do ROLLBACK because that
> BM> would fill the logs, but I am not sure how they got BEGIN;COMMIT;
> BM> rather than BEGIN;ROLLBACK. It just shows we need better communication
> BM> between the communities.
>
>
>
> Ok, i'd of course appreciate more eyes before my commit then (i need to become
> more familiar with the whole postgres source). I've put three files online:
> - the diff against current php haed
> http://marcus-boerger.de/php/ext/pgsql/pgsql-persistent-20030725.diff.txt
> - the complete new file
> http://marcus-boerger.de/php/ext/pgsql/pgsql.c
> - the parts relevant (request start/stop)
> http://marcus-boerger.de/php/ext/pgsql/pgsql-request-start-stop.c
>
>
> Best regards,
> Marcus mailto:marcus.boerger@post.rwth-aachen.de
>
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
Hello Bruce, Thursday, July 24, 2003, 11:57:39 PM, you wrote: BM> Jan Wieck wrote: >> Marcus B?rger wrote: >> > ATM i have a patch doing the following: >> > Connect: >> > If PQprotocolVersion() is available and >= 3 PQparameterStatus() is available >> > then i check the server version. Else i check the lib version (*). >> > If the version to check is >= 7.2 ido one of the following: >> > - If one of PQprotocolVersion() and PQtransactionStatus() is unavailable or >> > protocol version < 3: >> > "BEGIN;COMMIT;RESET ALL;" >> > >> > - If protocol version >= 3 and transaction status == PQTRANS_IDLE: >> > "RESET ALL;" >> > - If protocol version >= 3 and transaction status != PQTRANS_IDLE: >> > "COMMIT;RESET ALL;" >> >> Shouldn't that be >> >> ROLLBACK; RESET ALL; >> >> and the other one "BEGIN; ROLLBACK; RESET ALL;" ? >> >> I don't want to have the possibly partial transaction from a crashed PHP >> script to be committed by default. At least it would be a significant >> difference between persistent and non-persistent connections, because we >> rollback if we loose the connection. BM> Right, as I just emailed. I wonder if we made the mistake of BM> recommending BEGIN;COMMIT; to the PHP folks a while back, or whether they BM> just did it themselves. BM> I remember telling them they couldn't just do ROLLBACK because that BM> would fill the logs, but I am not sure how they got BEGIN;COMMIT; BM> rather than BEGIN;ROLLBACK. It just shows we need better communication BM> between the communities. Ok, i'd of course appreciate more eyes before my commit then (i need to become more familiar with the whole postgres source). I've put three files online: - the diff against current php haed http://marcus-boerger.de/php/ext/pgsql/pgsql-persistent-20030725.diff.txt - the complete new file http://marcus-boerger.de/php/ext/pgsql/pgsql.c - the parts relevant (request start/stop) http://marcus-boerger.de/php/ext/pgsql/pgsql-request-start-stop.c Best regards,Marcus mailto:marcus.boerger@post.rwth-aachen.de
Hello Bruce, Wednesday, July 23, 2003, 6:18:13 AM, you wrote: BM> Marcus B?rger wrote: >> >> However it may be very usefull to terminate any open transaction before >> >> reusing a persisten connection. Typically this happens when the same script >> >> runs again. But anyway using transactions together with persistent conenctions >> >> in a multithreaded environment isn't the best thing you could do. So our >> >> options are >> >> 1) tell the users to do 'auto commit mode' >> >> 2) nested transactions >> >> 3) locking >> >> >> >> >From my perspective 2) and 3) are bad ideas for the web environment. In other >> >> words i guess we should leave it as is with transaction rollback only when the >> >> client terminates (e.g. the webserver stops). >> >> BM> I don't see why you wouldn't just do BEGIN;COMMIT;RESET ALL; when you >> BM> pass the connection to a new client. >> >> >> Bruce you said RESET ALL is available since 7.2. I am currently checking for >> the lib version but it would be more correct to check something on the server. >> So the question what do i check? BM> We usually use SELECT version(). ATM i have a patch doing the following: Connect:If PQprotocolVersion() is available and >= 3 PQparameterStatus() is availablethen i check the server version. Elsei check the lib version (*).If the version to check is >= 7.2 ido one of the following:- If one of PQprotocolVersion()and PQtransactionStatus() is unavailable or protocol version < 3: "BEGIN;COMMIT;RESET ALL;" - If protocol version >= 3 and transaction status == PQTRANS_IDLE: "RESET ALL;"- If protocol version >= 3 and transactionstatus != PQTRANS_IDLE: "COMMIT;RESET ALL;" Disconnect:- When PQprotocolVersion() And PQtransactionStatus() are available then i check whether status is PQTRANS_IDLE.If so i do: "ROLLBACK;"- If the functions are not available in the client libs i do: "BEGIN;ROLLBACK;" Does this sound the correct behavior? And would "select split_part(version(), ' ', 2);" be too much of a slowdown to do the version detection in the startup sequence completely correct? -- Best regards,Marcus mailto:marcus.boerger@post.rwth-aachen.de
Hello Bruce, Thursday, July 24, 2003, 11:05:21 PM, you wrote: BM> Marcus B?rger wrote: >> >> BM> I don't see why you wouldn't just do BEGIN;COMMIT;RESET ALL; when you >> >> BM> pass the connection to a new client. >> >> >> >> >> >> Bruce you said RESET ALL is available since 7.2. I am currently checking for >> >> the lib version but it would be more correct to check something on the server. >> >> So the question what do i check? >> >> BM> We usually use SELECT version(). >> >> >> ATM i have a patch doing the following: >> Connect: >> If PQprotocolVersion() is available and >= 3 PQparameterStatus() is available >> then i check the server version. Else i check the lib version (*). >> If the version to check is >= 7.2 ido one of the following: >> - If one of PQprotocolVersion() and PQtransactionStatus() is unavailable or >> protocol version < 3: >> "BEGIN;COMMIT;RESET ALL;" BM> I believe this should be BEGIN;ROLLBACK;RESET ALL; because our default BM> for a client that disconnects is to abort the transaction. >> - If protocol version >= 3 and transaction status == PQTRANS_IDLE: >> "RESET ALL;" >> - If protocol version >= 3 and transaction status != PQTRANS_IDLE: >> "COMMIT;RESET ALL;" BM> Should be "ROLLBACK;RESET ALL;". Because of above mentioned default? The problem i have is that now we do the COMMIT - so this behavior change can only go into php 5 with a big notice. BM> Nice version test code, sounds good. >> >> Disconnect: >> - When PQprotocolVersion() And PQtransactionStatus() are available then >> i check whether status is PQTRANS_IDLE. If so i do: >> "ROLLBACK;" >> - If the functions are not available in the client libs i do: >> "BEGIN;ROLLBACK;" >> >> Does this sound the correct behavior? BM> I am confused why you are doing stuff on connect and disconnect. Seems BM> it should all be done on disconnect so you don't leave open transactions BM> in the pooled connections --- it will keep locks around too long and BM> reduce the usefulness of vacuum. If we clean up everything on BM> disconnect, aren't we sure that the connection status will be fine? >> And would "select split_part(version(), ' ', 2);" be too much of a slowdown to >> do the version detection in the startup sequence completely correct? BM> Seems fine. Since you are doing pooled connections, you shouldn't be BM> doing this too often anyway. I believe the point was "RESET ALL;". But maybe i can move all but that into shutdown. I mean for me that sounds good. Only someone must enlighten me if there could be a problem with that reset. -- Best regards,Marcus mailto:marcus.boerger@post.rwth-aachen.de
Hello Bruce, Thursday, July 31, 2003, 6:11:04 AM, you wrote: BM> Are you all done with the PHP persistent connection changes? Need any BM> help? I think it's fine now (in PHP 5). We'll see how feedback is during the next weeks. Best regards,Marcus mailto:marcus.boerger@post.rwth-aachen.de
OK, can I see the new patches? I want to make sure everything is OK. Thanks. --------------------------------------------------------------------------- Marcus B?rger wrote: > Hello Bruce, > > Thursday, July 31, 2003, 6:11:04 AM, you wrote: > > > BM> Are you all done with the PHP persistent connection changes? Need any > BM> help? > > I think it's fine now (in PHP 5). We'll see how feedback is during the next > weeks. > > > Best regards, > Marcus mailto:marcus.boerger@post.rwth-aachen.de > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073