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


Sat, 12 Jun 1999 22:57:38 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
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

Sat, 12 Jun 1999 21:46:45 +0200
Edmund Mergl <E.Mergl@bawue.de>
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.

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

Sat, 12 Jun 1999 16:04:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] destroydb doesn't close connection with client (httpd
	<-> pg)
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


Sun, 13 Jun 1999 00:04:43 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
Re: [HACKERS] destroydb doesn't close connection with client (httpd
	<-> pg)
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


Sun, 13 Jun 1999 00:41:32 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
Re: [HACKERS] destroydb doesn't close connection with client (httpd
	<-> pg)
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

Sat, 12 Jun 1999 16:58:47 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] destroydb doesn't close connection with client (httpd
	<-> pg)
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

Sat, 12 Jun 1999 14:27:47 -0700
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] destroydb doesn't close connection with client
	(httpd <-> pg)

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

Sat, 12 Jun 1999 20:54:16 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] destroydb doesn't close connection with client (httpd
	<-> pg)
> 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

Sat, 12 Jun 1999 20:57:37 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] destroydb doesn't close connection with client (httpd
	<-> pg)

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

Mon, 29 Nov 1999 17:25:02 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] destroydb doesn't close connection with client (httpd
	<->  pg)
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

Mon, 29 Nov 1999 21:09:29 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] destroydb doesn't close connection with client (httpd
	<-> pg)