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

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

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, Pennsylvania 19026

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, Pennsylvania 19026