Env: Postgres 7.3.4 on Unix

I have a script to create a new database user and update pg_hba.conf.
I was wondering whether I need to restart server, or simply run pg_ctl
reload.
The question is what happens to all active clients when all backends get the
signal.

I ran such client process which was very busy querying and updating
database.
At the same time I kept executing "pg_ctl reload".  Soon after client
reported
database error.

Here's the excerpt from the database log:
... ...
2003-10-10 22:33:12 LOG:  Received SIGHUP, reloading configuration files
  <25 successful SIGHUPs, about 2 seconds apart from each other>
... ...
2003-10-10 22:37:05 ERROR:  cannot read block 0 of s_noteimportlinks:
Interrupted system call
...

 From the client log I see that problem occured while trying to SELECT
nextval from
sequence s_noteimportlinks

We are going to pass this script to the customers and we have to know what
to recommend:
reload or shut down/restart. I hope they won't do reload 25 times... but
they may have
25 or more active client processes at any time.

Thanks in advance,
Mike.



Fri, 10 Oct 2003 23:07:59 -0400
Michael Brusser <michael@synchronicity.com>
pg_ctl reload - is it safe?
On Fri, Oct 10, 2003 at 23:07:59 -0400,
  Michael Brusser <michael@synchronicity.com> wrote:
> Env: Postgres 7.3.4 on Unix
> 
> I have a script to create a new database user and update pg_hba.conf.

Do you run multiple databases with not all users having access to all
databases? If any valid use will have access to a particular database,
then you can use 'any' for the username in the pg_hba.conf file and
not have to worry about updating it.

Sat, 11 Oct 2003 08:53:08 -0500
Bruno Wolff III <bruno@wolff.to>
Re: pg_ctl reload - is it safe?
Michael Brusser <michael@synchronicity.com> writes:
> 2003-10-10 22:37:05 ERROR:  cannot read block 0 of s_noteimportlinks:
> Interrupted system call

Hmm.  I found this hard to believe at first, but indeed my local man
pages for read() and write() say they can return EINTR if interrupted
by a signal.  This may depend on the filesystem in use (are you using
NFS?)

We had probably better fix fd.c to retry on EINTR.

			regards, tom lane

Tue, 14 Oct 2003 12:00:57 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_ctl reload - is it safe? 
Yes, we use NFS. Many of our customers use it as well.
Mike.

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
... ...
 
> Michael Brusser <michael@synchronicity.com> writes:
> > 2003-10-10 22:37:05 ERROR:  cannot read block 0 of s_noteimportlinks:
> > Interrupted system call
> 
> Hmm.  I found this hard to believe at first, but indeed my local man
> pages for read() and write() say they can return EINTR if interrupted
> by a signal.  This may depend on the filesystem in use (are you using
> NFS?)
> 
> We had probably better fix fd.c to retry on EINTR.
> 
> 			regards, tom lane
> 


Tue, 14 Oct 2003 12:09:09 -0400
Michael Brusser <michael@synchronicity.com>
Re: pg_ctl reload - is it safe?
Michael Brusser <michael@synchronicity.com> writes:
> Yes, we use NFS. Many of our customers use it as well.

You are of course aware that this is not real safe...

			regards, tom lane

Tue, 14 Oct 2003 13:13:33 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_ctl reload - is it safe? 

Michael Brusser <michael@synchronicity.com> writes:

> > Michael Brusser <michael@synchronicity.com> writes:
> > > 2003-10-10 22:37:05 ERROR:  cannot read block 0 of s_noteimportlinks:
> > > Interrupted system call
> > 
> > Hmm.  I found this hard to believe at first, but indeed my local man
> > pages for read() and write() say they can return EINTR if interrupted
> > by a signal.  This may depend on the filesystem in use (are you using
> > NFS?)

The traditional unix semantics are the read/write my return EINTR if
interrupted -- but that that would only EVER happen for network connections.
The traditional semantics are that it would NEVER happen on disk i/o. BSD
kernels at least, and probably all unix kernels, do an uninterruptible sleep
on disk accesses, hence the dreaded "D" in ps listings.

> Yes, we use NFS. Many of our customers use it as well.

Normally NFS guarantees the traditional unix semantics. 
Unless you're using either "soft" or "intr" options.

If you are, well, stop.

If you use "intr" then this type of thing can happen. Lots of programs assume
the unix semantics for disk accesses. You can get all kinds of bugs when
they're violated.

If you use "soft" then the consequences can be much much worse. If your
fileserver were to reboot you could silently lose disk writes corrupting your
database.

-- 
greg


14 Oct 2003 14:09:18 -0400
Greg Stark <gsstark@mit.edu>
Re: pg_ctl reload - is it safe?
Tom Lane wrote:
> Michael Brusser <michael@synchronicity.com> writes:
> > Yes, we use NFS. Many of our customers use it as well.
> 
> You are of course aware that this is not real safe...

Maybe we should throw a "stop using NFS" if we get an EINTR from
read()/write(), or explain what NFS options they should avoid.

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

Tue, 14 Oct 2003 15:31:42 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_ctl reload - is it safe?
--On Tuesday, October 14, 2003 15:31:42 -0400 Bruce Momjian 
<pgman@candle.pha.pa.us> wrote:

> Tom Lane wrote:
>> Michael Brusser <michael@synchronicity.com> writes:
>> > Yes, we use NFS. Many of our customers use it as well.
>>
>> You are of course aware that this is not real safe...
>
> Maybe we should throw a "stop using NFS" if we get an EINTR from
> read()/write(), or explain what NFS options they should avoid.
Err, some of us use NetApp filers as NFS servers for our PG data, and I 
believe that that negates at least some of the risk.  I don't want to see
us (PG) not support something just because it *MAY* be unsafe.

LER

>
> --
>   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, Pennsylvania
> 19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>



-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Tue, 14 Oct 2003 14:37:01 -0500
Larry Rosenman <ler@lerctr.org>
Re: pg_ctl reload - is it safe?
Larry Rosenman wrote:
-- Start of PGP signed section.
> 
> 
> --On Tuesday, October 14, 2003 15:31:42 -0400 Bruce Momjian 
> <pgman@candle.pha.pa.us> wrote:
> 
> > Tom Lane wrote:
> >> Michael Brusser <michael@synchronicity.com> writes:
> >> > Yes, we use NFS. Many of our customers use it as well.
> >>
> >> You are of course aware that this is not real safe...
> >
> > Maybe we should throw a "stop using NFS" if we get an EINTR from
> > read()/write(), or explain what NFS options they should avoid.
> Err, some of us use NetApp filers as NFS servers for our PG data, and I 
> believe that that negates at least some of the risk.  I don't want to see
> us (PG) not support something just because it *MAY* be unsafe.

True.  I was going by the guy who said that only unsafe NFS flags will
cause this behavior --- and of course, 1/2 of it was a joke, and the
other 1/2 was just an idea thrown out.

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

Tue, 14 Oct 2003 15:38:32 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_ctl reload - is it safe?
On 14 Oct 2003, Greg Stark wrote:

> 
> Michael Brusser <michael@synchronicity.com> writes:
> 
> > > Michael Brusser <michael@synchronicity.com> writes:
> > > > 2003-10-10 22:37:05 ERROR:  cannot read block 0 of s_noteimportlinks:
> > > > Interrupted system call
> > > 
> > > Hmm.  I found this hard to believe at first, but indeed my local man
> > > pages for read() and write() say they can return EINTR if interrupted
> > > by a signal.  This may depend on the filesystem in use (are you using
> > > NFS?)
> 
> The traditional unix semantics are the read/write my return EINTR if
> interrupted -- but that that would only EVER happen for network connections.
> The traditional semantics are that it would NEVER happen on disk i/o. BSD
> kernels at least, and probably all unix kernels, do an uninterruptible sleep
> on disk accesses, hence the dreaded "D" in ps listings.
> 
> > Yes, we use NFS. Many of our customers use it as well.
> 
> Normally NFS guarantees the traditional unix semantics. 
> Unless you're using either "soft" or "intr" options.
> 
> If you are, well, stop.
> 
> If you use "intr" then this type of thing can happen. Lots of programs assume
> the unix semantics for disk accesses. You can get all kinds of bugs when
> they're violated.
> 
> If you use "soft" then the consequences can be much much worse. If your
> fileserver were to reboot you could silently lose disk writes corrupting your
> database.

What if the WAL was local on disk, and the data was going to nfs storage, 
would that be safe, or saferer?  :-)


Tue, 14 Oct 2003 13:39:17 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: pg_ctl reload - is it safe?
wscott.marlowe wrote:
> > If you use "intr" then this type of thing can happen. Lots of programs assume
> > the unix semantics for disk accesses. You can get all kinds of bugs when
> > they're violated.
> > 
> > If you use "soft" then the consequences can be much much worse. If your
> > fileserver were to reboot you could silently lose disk writes corrupting your
> > database.
> 
> What if the WAL was local on disk, and the data was going to nfs storage, 
> would that be safe, or saferer?  :-)

Not sure --- we do a sync() on the entire machine before recycling the WAL logs.

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

Tue, 14 Oct 2003 16:02:48 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_ctl reload - is it safe?