Обсуждение: destroydb doesn't close connection with client (httpd <-> pg)
I have Web site where I use persistent connection between httpd (Apache) and database (postgres,6.5). I noticed strange results I got after reloading page with results from query when I destroydb , createdb, fill db ( with the same data ). It seems backend doesn't close connection when db is destroyed and this produces unpredictable results. My application is written in Perl and uses DBI/DBD for persistent connection. I don't know is it DBI/DBD problem or backend must close all connections to DB when it destroyed. Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov wrote: > > I have Web site where I use persistent connection between > httpd (Apache) and database (postgres,6.5). I noticed strange > results I got after reloading page with results from query > when I destroydb , createdb, fill db ( with the same data ). > It seems backend doesn't close connection when db is destroyed > and this produces unpredictable results. My application is > written in Perl and uses DBI/DBD for persistent connection. > I don't know is it DBI/DBD problem or backend must close > all connections to DB when it destroyed. > > Regards, > Oleg > _____________________________________________________________ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 This is not DBI/DBD-Pg specific. A short test with psql shows, that this seems to be a bug of postgresql itself. Create and fill a database. Connect to this database with psql and perform some query. Without disconnecting destroy and re-create the database but insert this time different data. Performing the same query a second time will retrieve the same data as before Edmund -- Edmund Mergl mailto:E.Mergl@bawue.de Im Haldenhau 9 http://www.bawue.de/~mergl 70565 Stuttgart fon: +49 711 747503 Germany
I also think this is the bug in postgres. I didn't check if this behaivour is the same with 6.4.2 though. It could be nice if backend could communicate with 'persistent' client to tell him that database is destroyed and client probably could decide if he wants to try to reestablish connection. So in case database restored nothing dangerous would happens to client (pprobably just informational message in that case ). Regards, Oleg On Sat, 12 Jun 1999, Edmund Mergl wrote: > Date: Sat, 12 Jun 1999 21:46:45 +0200 > From: Edmund Mergl <E.Mergl@bawue.de> > To: Oleg Bartunov <oleg@sai.msu.su> > Cc: hackers@postgreSQL.org > Subject: Re: [HACKERS] destroydb doesn't close connection with client (httpd <-> pg) > > Oleg Bartunov wrote: > > > > I have Web site where I use persistent connection between > > httpd (Apache) and database (postgres,6.5). I noticed strange > > results I got after reloading page with results from query > > when I destroydb , createdb, fill db ( with the same data ). > > It seems backend doesn't close connection when db is destroyed > > and this produces unpredictable results. My application is > > written in Perl and uses DBI/DBD for persistent connection. > > I don't know is it DBI/DBD problem or backend must close > > all connections to DB when it destroyed. > > > > Regards, > > Oleg > > _____________________________________________________________ > > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > > Sternberg Astronomical Institute, Moscow University (Russia) > > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > > phone: +007(095)939-16-83, +007(095)939-23-83 > > > > This is not DBI/DBD-Pg specific. > > A short test with psql shows, that this seems to be > a bug of postgresql itself. > > Create and fill a database. Connect to this database with psql > and perform some query. Without disconnecting destroy and re-create > the database but insert this time different data. Performing > the same query a second time will retrieve the same data as before > > > Edmund > > > > -- > Edmund Mergl mailto:E.Mergl@bawue.de > Im Haldenhau 9 http://www.bawue.de/~mergl > 70565 Stuttgart fon: +49 711 747503 > Germany > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov <oleg@sai.msu.su> writes:
> I don't know is it DBI/DBD problem or backend must close
> all connections to DB when it destroyed.
You cannot destroy a DB while there are backends connected to it;
all hell breaks loose if you do. See thread "How to destroy your entire
Postgres installation" in pg-hackers in late Sept. 1998.
The correct fix is to add an interlock that prevents "destroydb" when
there are connected backends. I don't know just how that might be done,
however.
There should be a TODO item for this, but I don't see one: * Prevent destroydb when there are backends active in that
database
regards, tom lane
On Sat, 12 Jun 1999, Tom Lane wrote: > Date: Sat, 12 Jun 1999 16:04:30 -0400 > From: Tom Lane <tgl@sss.pgh.pa.us> > To: Oleg Bartunov <oleg@sai.msu.su> > Cc: hackers@postgreSQL.org > Subject: Re: [HACKERS] destroydb doesn't close connection with client (httpd <-> pg) > > Oleg Bartunov <oleg@sai.msu.su> writes: > > I don't know is it DBI/DBD problem or backend must close > > all connections to DB when it destroyed. > > You cannot destroy a DB while there are backends connected to it; > all hell breaks loose if you do. See thread "How to destroy your entire > Postgres installation" in pg-hackers in late Sept. 1998. I did it without any problem :-) > > The correct fix is to add an interlock that prevents "destroydb" when > there are connected backends. I don't know just how that might be done, > however. I'm not sure preventing "destroydb" is a good idea. Imagine some user connects to db (via psql) and hold it for a weekend. You will not be able to do some dbadmin work ? What's wrong if backend close connection, cleanup buffers etc. and inform client that db was destroyed. Client could decide - exit or try to reestablish connection. If you recreate that db nothing will happens. > > There should be a TODO item for this, but I don't see one: > * Prevent destroydb when there are backends active in that database The problem would be worse in case of 24*7 life-cycle of Web-database connectivity if you decide "prevent destroydb" feature ! I prefer to allow destroydb but close all active connections with this db and inform client. Client will decide what to do. As I understand DBI/DBD is already ready for such behaivour because it has ping method. I'm no sure about psql. Oleg > > regards, tom lane > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov <oleg@sai.msu.su> writes:
>> You cannot destroy a DB while there are backends connected to it;
>> all hell breaks loose if you do. See thread "How to destroy your entire
>> Postgres installation" in pg-hackers in late Sept. 1998.
> I did it without any problem :-)
You were lucky. I suffered severe database corruption in *other*
databases when I did it by accident last year.
>> The correct fix is to add an interlock that prevents "destroydb" when
>> there are connected backends. I don't know just how that might be done,
>> however.
> I'm not sure preventing "destroydb" is a good idea. Imagine some
> user connects to db (via psql) and hold it for a weekend.
If you cannot stop the connected backends first, you should not be able
to kill the database. I suppose a different way to look at it is that
destroydb should kill any backends connected to the database before it
does anything else. I'd suggest that that only happen with user
confirmation though, since destroying an active database seems to me
to be something you'd never really want to do.
> The problem would be worse in case of 24*7 life-cycle of Web-database
> connectivity if you decide "prevent destroydb" feature !
If you have an active website using a database, what are you doing
destroying the database? We're not talking about dropping a temp table
here... I find it hard to imagine what you'd use this "feature" for,
except for shooting yourself in the foot.
regards, tom lane
At 04:58 PM 6/12/99 -0400, Tom Lane wrote: >If you have an active website using a database, what are you doing >destroying the database? We're not talking about dropping a temp table >here... I find it hard to imagine what you'd use this "feature" for, >except for shooting yourself in the foot. Or upgrading Postgres :) Just joking...Tom, you're absolutely right, by definition a web site being backed by a db that's frequently created and destroyed is not a 24/7 site. It it's not 24/7, then you can knock it down, destroy and re-create the database, then coax the postmaster and webserver back into service. Most active web services will be denying some level of service if you simply drop a (non-temp) table. The destroy the whole db? Omigosh! - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, and other goodies at http://donb.photo.net
Added to TODO. > Oleg Bartunov <oleg@sai.msu.su> writes: > > I don't know is it DBI/DBD problem or backend must close > > all connections to DB when it destroyed. > > You cannot destroy a DB while there are backends connected to it; > all hell breaks loose if you do. See thread "How to destroy your entire > Postgres installation" in pg-hackers in late Sept. 1998. > > The correct fix is to add an interlock that prevents "destroydb" when > there are connected backends. I don't know just how that might be done, > however. > > There should be a TODO item for this, but I don't see one: > * Prevent destroydb when there are backends active in that database > > regards, tom lane > > -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Oleg Bartunov <oleg@sai.msu.su> writes: > >> You cannot destroy a DB while there are backends connected to it; > >> all hell breaks loose if you do. See thread "How to destroy your entire > >> Postgres installation" in pg-hackers in late Sept. 1998. > > > I did it without any problem :-) > > You were lucky. I suffered severe database corruption in *other* > databases when I did it by accident last year. Destroydb is reworked in 6.5 because it flushes all its buffers. I specifically fixed something like this. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
I also think this is the bug in postgres. I didn't check if this behaivour is the same with 6.4.2 though. It could be nice if backend could communicate with 'persistent' client to tell him that database is destroyed and client probably could decide if he wants to try to reestablish connection. So in case database restored nothing dangerous would happens to client (pprobably just informational message in that case ). Regards, Oleg On Sat, 12 Jun 1999, Edmund Mergl wrote: > Date: Sat, 12 Jun 1999 21:46:45 +0200 > From: Edmund Mergl <E.Mergl@bawue.de> > To: Oleg Bartunov <oleg@sai.msu.su> > Cc: hackers@postgreSQL.org > Subject: Re: [HACKERS] destroydb doesn't close connection with client (httpd <-> pg) > > Oleg Bartunov wrote: > > > > I have Web site where I use persistent connection between > > httpd (Apache) and database (postgres,6.5). I noticed strange > > results I got after reloading page with results from query > > when I destroydb , createdb, fill db ( with the same data ). > > It seems backend doesn't close connection when db is destroyed > > and this produces unpredictable results. My application is > > written in Perl and uses DBI/DBD for persistent connection. > > I don't know is it DBI/DBD problem or backend must close > > all connections to DB when it destroyed. > > > > Regards, > > Oleg > > _____________________________________________________________ > > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > > Sternberg Astronomical Institute, Moscow University (Russia) > > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > > phone: +007(095)939-16-83, +007(095)939-23-83 > > > > This is not DBI/DBD-Pg specific. > > A short test with psql shows, that this seems to be > a bug of postgresql itself. > > Create and fill a database. Connect to this database with psql > and perform some query. Without disconnecting destroy and re-create > the database but insert this time different data. Performing > the same query a second time will retrieve the same data as before > > > Edmund > > > > -- > Edmund Mergl mailto:E.Mergl@bawue.de > Im Haldenhau 9 http://www.bawue.de/~mergl > 70565 Stuttgart fon: +49 711 747503 > Germany > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Can someone comment on where we are on this? [Charset iso-8859-2 unsupported, filtering to ASCII...] > Oleg Bartunov wrote: > > > > I have Web site where I use persistent connection between > > httpd (Apache) and database (postgres,6.5). I noticed strange > > results I got after reloading page with results from query > > when I destroydb , createdb, fill db ( with the same data ). > > It seems backend doesn't close connection when db is destroyed > > and this produces unpredictable results. My application is > > written in Perl and uses DBI/DBD for persistent connection. > > I don't know is it DBI/DBD problem or backend must close > > all connections to DB when it destroyed. > > > > Regards, > > Oleg > > _____________________________________________________________ > > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > > Sternberg Astronomical Institute, Moscow University (Russia) > > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > > phone: +007(095)939-16-83, +007(095)939-23-83 > > > > This is not DBI/DBD-Pg specific. > > A short test with psql shows, that this seems to be > a bug of postgresql itself. > > Create and fill a database. Connect to this database with psql > and perform some query. Without disconnecting destroy and re-create > the database but insert this time different data. Performing > the same query a second time will retrieve the same data as before > > > Edmund > > > > -- > Edmund Mergl mailto:E.Mergl@bawue.de > Im Haldenhau 9 http://www.bawue.de/~mergl > 70565 Stuttgart fon: +49 711 747503 > Germany > > -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Can someone comment on where we are on this?
Problem's gone: you cannot destroy a DB containing active backends
anymore. That may not be quite the solution Edmund wanted ;-), but
it's effective.
>> Create and fill a database. Connect to this database with psql
>> and perform some query. Without disconnecting destroy and re-create
>> the database but insert this time different data. Performing
>> the same query a second time will retrieve the same data as before
regards, tom lane