BUG #4763: postgres service unstable, even during install

The following bug has been logged online:

Bug reference: 4763
Logged by: Kevin Field
Email address: kev(at)brantaero(dot)com
PostgreSQL version: 8.4-beta1
Operating system: Windows Server 2003 Standard Edition SP2
Description: postgres service unstable, even during install
Details:

Using
http://www.postgresql.org/ftp/binary/v8.4beta/win32/postgresql-8.4-beta1.zip
on a console mode session with SAV autoprotect disabled (and besides told to
exclude anything below "C:\Program Files\PostgreSQL\"), three times now I've
tried to install (no stack builder, then adminpack+fuzzystring+pldebugger
and plpgsql+plperl+plperlu) and encountered the following problems.

The installer gets to the step "Activating procedural languages..." and an
error window pops up:

Database command error: server closed the connection unexpectedly
[square character]This probably means the server terminated abnormally
[square character]before or while processing the request

...and indeed, the service is not running. If I follow along with the
Services window open during install, at one point the new service shows up
and has status "Starting..." but I'm not sure if it fails to start or if it
starts and then the procedural language thing makes it bail.

I OK the box and another one comes:

"Failed to enable procedural language plperl. The files are installed but
are not activated in any database."

And another:

"Database command error: no connection to the server"

Then repeat the last two for "plperlu (untrusted)". Then:

"Failed to connect to the 'template1' database. Contrib files are
installed, but are not activated in any databases."

...and then finally "Could not connect to server" and asking whether it's
accepting connections.

If after the very first error message I go into Services and start it
manually, the last two messages here do not come up, but the rest still do.

Then I get a success message.

However, after that when I try to run a script to dump another database and
restore it onto the beta, I get a "file not found" error, which is really
odd both because it was working fine on the 2009-03-24 build and because the
permissions have not changed. Aside from that, which is it's own problem,
after that error the service is no longer running and has to be started
again.

This makes me think that something similar happens during the install, so
that something fails with the plperl setup that causes the service to shut
down (rather than it failing to start up in the first place.)

Anyway both the 2009-01-01 and 2009-03-24 builds seemed smoother to set up
and much more stable. Not sure why that would be...

Thanks,
Kev

I found an additional message in the Application Event Log:

2009-04-17 12:05:00 GMT FATAL: could not create lock file
"postmaster.pid": Permission denied

> However, after that when I try to run a script to dump another database and
> restore it onto the beta, I get a "file not found" error, which is really
> odd both because it was working fine on the 2009-03-24 build and because the
> permissions have not changed. Aside from that, which is it's own problem,
> after that error the service is no longer running and has to be started
> again.
>
> This makes me think that something similar happens during the install, so
> that something fails with the plperl setup that causes the service to shut
> down (rather than it failing to start up in the first place.)

If I shut down the 8.4-beta1 service and start up the 2009-03-24
service on the same port and run the script the same way (it's a perl
script I'm accessing through a browser), it runs fine, so I can't see
how it would be an Apache or Perl permissions issue (i.e., what those
two are running as)--I just hit F5, it's launching the same pg_restore
binary from the same account on the same restore file. The one way is
fine, the other says, "No such file or directory" (sorry: not "file
not found," that was a bit imprecise of me earlier.)

I found I was looking in the wrong log for more detail, but I found
some:

pg_restore: WARNING: database "production" does not exist
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 519; 2612 47275
PROCEDURAL LANGUAGE plperl mysuperuser
pg_restore: [archiver (db)] could not execute query: server closed the
connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
Command was: CREATE PROCEDURAL LANGUAGE plperl;

After that it's an error with every statement because there's no
connection to the server.

Now the really weird thing is, "production" doesn't exist on my
2009-03-24 server either. That's the one the dump is *from*, but I'm
using "pg_restore -d dev", so it shouldn't matter that it doesn't
exist (and indeed it doesn't on the 2009-03-24 server, because that
works fine, note that I'm even using the 8.4-beta1 pg_restore binary
for both cases.) And for the record the script drops "dev" and
creates it from template0 right before trying to restore over it.

Is it possible it's looking for Perl in the wrong place? (Hence the
"No such file..." error that somehow makes it back to my Perl script?)

Kev

On Thu, Apr 23, 2009 at 4:30 PM, Kevin Field <kevinjamesfield(at)gmail(dot)com> wrote:

> Is it possible it's looking for Perl in the wrong place?  (Hence the
> "No such file..." error that somehow makes it back to my Perl script?)

What version of perl do you have?

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

On Apr 23, 12:13 pm, dp(dot)(dot)(dot)(at)pgadmin(dot)org (Dave Page) wrote:
> On Thu, Apr 23, 2009 at 4:30 PM, Kevin Field <kevinjamesfi(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> > Is it possible it's looking for Perl in the wrong place? (Hence the
> > "No such file..." error that somehow makes it back to my Perl script?)
>
> What version of perl do you have?

I have 5.8.8 in C:\Perl and 5.10.0 in C:\Perl5.10

But no problems with 2009-03-24...aren't both that and 8.4-beta1
linked against 5.8.8?

On Apr 23, 11:30 am, Kevin Field <kevinjamesfi(dot)(dot)(dot)(at)gmail(dot)com> wrote:
>
> pg_restore: WARNING: database "production" does not exist
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 519; 2612 47275
> PROCEDURAL LANGUAGE plperl mysuperuser
> pg_restore: [archiver (db)] could not execute query: server closed the
> connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> Command was: CREATE PROCEDURAL LANGUAGE plperl;

Just for yucks, I tried creating the database 'production' (despite
the fact that that shouldn't make a difference) and re-running the
script, and it gave the same error minus the first line.

On Thu, Apr 23, 2009 at 6:43 PM, Kevin Field <kevinjamesfield(at)gmail(dot)com> wrote:
> On Apr 23, 12:13 pm, dp(dot)(dot)(dot)(at)pgadmin(dot)org (Dave Page) wrote:
>> On Thu, Apr 23, 2009 at 4:30 PM, Kevin Field <kevinjamesfi(dot)(dot)(dot)(at)gmail(dot)com> wrote:
>> > Is it possible it's looking for Perl in the wrong place?  (Hence the
>> > "No such file..." error that somehow makes it back to my Perl script?)
>>
>> What version of perl do you have?
>
> I have 5.8.8 in C:\Perl and 5.10.0 in C:\Perl5.10
>
> But no problems with 2009-03-24...aren't both that and 8.4-beta1
> linked against 5.8.8?

The community installer for beta 1 uses Perl 5.10. The one-click uses
5.8 in beta 1 and earlier snapshots. Both will use 5.10 for beta 2 (as
well as Python 2.6 and TCL 8.5).

I'm wondering if it's barfing because it's finding the wrong version
when it tries to install pl/perl. That would also explain a report I
saw of the installer failing with a similar error.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

On Apr 23, 2:25 pm, dp(dot)(dot)(dot)(at)pgadmin(dot)org (Dave Page) wrote:
> On Thu, Apr 23, 2009 at 6:43 PM, Kevin Field <kevinjamesfi(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> > On Apr 23, 12:13 pm, dp(dot)(dot)(dot)(at)pgadmin(dot)org (Dave Page) wrote:
> >> On Thu, Apr 23, 2009 at 4:30 PM, Kevin Field <kevinjamesfi(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> >> > Is it possible it's looking for Perl in the wrong place? (Hence the
> >> > "No such file..." error that somehow makes it back to my Perl script?)
>
> >> What version of perl do you have?
>
> > I have 5.8.8 in C:\Perl and 5.10.0 in C:\Perl5.10
>
> > But no problems with 2009-03-24...aren't both that and 8.4-beta1
> > linked against 5.8.8?
>
> The community installer for beta 1 uses Perl 5.10. The one-click uses
> 5.8 in beta 1 and earlier snapshots. Both will use 5.10 for beta 2 (as
> well as Python 2.6 and TCL 8.5).
>
> I'm wondering if it's barfing because it's finding the wrong version
> when it tries to install pl/perl. That would also explain a report I
> saw of the installer failing with a similar error.

Good to know. And yay for 5.10! That's a welcome upgrade.

So I need to keep my 5.8 around currently for other uses--how can I
hint at the correct location for it?

On Fri, Apr 24, 2009 at 1:17 PM, Kevin Field <kevinjamesfield(at)gmail(dot)com> wrote:

> Good to know.  And yay for 5.10!  That's a welcome upgrade.
>
> So I need to keep my 5.8 around currently for other uses--how can I
> hint at the correct location for it?

In theory, by setting the path for the server only. In practice I'm
not sure how you could do that, except by possibly modifying the
per-user path setting for the service account.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

On Apr 24, 8:40 am, dp(dot)(dot)(dot)(at)pgadmin(dot)org (Dave Page) wrote:
> >
> > So I need to keep my 5.8 around currently for other uses--how can I
> > hint at the correct location for it?
>
> In theory, by setting the path for the server only. In practice I'm
> not sure how you could do that, except by possibly modifying the
> per-user path setting for the service account.

Hmm...our service account is also needed for 5.8. I suppose I could
make a new service account for it. Will this be solved in beta2,
probably?

On Fri, Apr 24, 2009 at 2:12 PM, Kevin Field <kevinjamesfield(at)gmail(dot)com> wrote:
> On Apr 24, 8:40 am, dp(dot)(dot)(dot)(at)pgadmin(dot)org (Dave Page) wrote:
>> >
>> > So I need to keep my 5.8 around currently for other uses--how can I
>> > hint at the correct location for it?
>>
>> In theory, by setting the path for the server only. In practice I'm
>> not sure how you could do that, except by possibly modifying the
>> per-user path setting for the service account.
>
> Hmm...our service account is also needed for 5.8.  I suppose I could
> make a new service account for it.  Will this be solved in beta2,
> probably?

I don't know if there is any way we can solve it, except by reverting
back to 5.8 or advising users to use only one version.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

On Apr 24, 9:32 am, dp(dot)(dot)(dot)(at)pgadmin(dot)org (Dave Page) wrote:
>
> I don't know if there is any way we can solve it, except by reverting
> back to 5.8 or advising users to use only one version.

LOL...ah, great. Well, I'd love to move to 5.10 for both.

A note in the docs would be handy either way.

On Apr 24, 9:32 am, dp(dot)(dot)(dot)(at)pgadmin(dot)org (Dave Page) wrote:
>
> I don't know if there is any way we can solve it, except by reverting
> back to 5.8 or advising users to use only one version.

I just had an idea--at least in the ActiveState distributions (not
sure about Strawberry or Vanilla) they include versioned-filename
binaries. So you can launch perl5.8.8.exe or perl5.10.0.exe and as
long as it's in the path you get the exact right one. (As I found out
when I went to try to figure out per-user paths, I somehow have both
versions in the universal path.) Would that help?

On Fri, Apr 24, 2009 at 3:09 PM, Kevin Field <kevinjamesfield(at)gmail(dot)com> wrote:
> On Apr 24, 9:32 am, dp(dot)(dot)(dot)(at)pgadmin(dot)org (Dave Page) wrote:
>>
>> I don't know if there is any way we can solve it, except by reverting
>> back to 5.8 or advising users to use only one version.
>
> I just had an idea--at least in the ActiveState distributions (not
> sure about Strawberry or Vanilla) they include versioned-filename
> binaries.  So you can launch perl5.8.8.exe or perl5.10.0.exe and as
> long as it's in the path you get the exact right one.  (As I found out
> when I went to try to figure out per-user paths, I somehow have both
> versions in the universal path.)  Would that help?

No, because we use the DLLs, not the .exe. But they do seem to be
versioned anyway, so there must be something else going on :-(

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

On Apr 26, 2:08 pm, dp(dot)(dot)(dot)(at)pgadmin(dot)org (Dave Page) wrote:
> On Fri, Apr 24, 2009 at 3:09 PM, Kevin Field <kevinjamesfi(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> > On Apr 24, 9:32 am, dp(dot)(dot)(dot)(at)pgadmin(dot)org (Dave Page) wrote:
>
> >> I don't know if there is any way we can solve it, except by reverting
> >> back to 5.8 or advising users to use only one version.
>
> > I just had an idea--at least in the ActiveState distributions (not
> > sure about Strawberry or Vanilla) they include versioned-filename
> > binaries. So you can launch perl5.8.8.exe or perl5.10.0.exe and as
> > long as it's in the path you get the exact right one. (As I found out
> > when I went to try to figure out per-user paths, I somehow have both
> > versions in the universal path.) Would that help?
>
> No, because we use the DLLs, not the .exe. But they do seem to be
> versioned anyway, so there must be something else going on :-(

:( Well on my end, I haven't been able to figure out how to get it to
see the right version. Do you know when the next beta or win32
installer build might be out so I can try again?

On May 1, 12:41 pm, Kevin Field <kevinjamesfi(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> On Apr 26, 2:08 pm, dp(dot)(dot)(dot)(at)pgadmin(dot)org (Dave Page) wrote:
>
> > On Fri, Apr 24, 2009 at 3:09 PM, Kevin Field <kevinjamesfi(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> > > On Apr 24, 9:32 am, dp(dot)(dot)(dot)(at)pgadmin(dot)org (Dave Page) wrote:
>
> > >> I don't know if there is any way we can solve it, except by reverting
> > >> back to 5.8 or advising users to use only one version.
>
> > > I just had an idea--at least in the ActiveState distributions (not
> > > sure about Strawberry or Vanilla) they include versioned-filename
> > > binaries. So you can launch perl5.8.8.exe or perl5.10.0.exe and as
> > > long as it's in the path you get the exact right one. (As I found out
> > > when I went to try to figure out per-user paths, I somehow have both
> > > versions in the universal path.) Would that help?
>
> > No, because we use the DLLs, not the .exe. But they do seem to be
> > versioned anyway, so there must be something else going on :-(
>
> :( Well on my end, I haven't been able to figure out how to get it to
> see the right version. Do you know when the next beta or win32
> installer build might be out so I can try again?

Through some testing today I determined:

1) the installer decides on whether you have Perl / where it is before
the first opportunity to click 'back', so, right at the beginning.
2) if I rename my Perl 5.8 directory--whether or not I rename my Perl
5.10 directory--the installer cannot find it and does not present pl/
perl[u] as an option a few Next-clicks later.
3) if I rename my Perl 5.10 directory to where I had had my Perl 5.8
directory, it's still not presented as an option

So...what, is it insisting on perl58.dll at the beginning of the
install and then perl510.dll when it goes to actually use it? Let's
see...i'll rename things back (so it'll be an option), select the pl/
perl options, and then rename the directories before clicking 'next'.
Result: same errors when it goes to activate plperl and plperlu. I
guess not.

On May 2, 12:09 pm, Kevin Field <kevinjamesfi(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> On May 1, 12:41 pm, Kevin Field <kevinjamesfi(dot)(dot)(dot)(at)gmail(dot)com> wrote:
>
>
>
> > On Apr 26, 2:08 pm, dp(dot)(dot)(dot)(at)pgadmin(dot)org (Dave Page) wrote:
>
> > > On Fri, Apr 24, 2009 at 3:09 PM, Kevin Field <kevinjamesfi(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> > > > On Apr 24, 9:32 am, dp(dot)(dot)(dot)(at)pgadmin(dot)org (Dave Page) wrote:
>
> > > >> I don't know if there is any way we can solve it, except by reverting
> > > >> back to 5.8 or advising users to use only one version.
>
> > > > I just had an idea--at least in the ActiveState distributions (not
> > > > sure about Strawberry or Vanilla) they include versioned-filename
> > > > binaries. So you can launch perl5.8.8.exe or perl5.10.0.exe and as
> > > > long as it's in the path you get the exact right one. (As I found out
> > > > when I went to try to figure out per-user paths, I somehow have both
> > > > versions in the universal path.) Would that help?
>
> > > No, because we use the DLLs, not the .exe. But they do seem to be
> > > versioned anyway, so there must be something else going on :-(
>
> > :( Well on my end, I haven't been able to figure out how to get it to
> > see the right version. Do you know when the next beta or win32
> > installer build might be out so I can try again?
>
> Through some testing today I determined:
>
> 1) the installer decides on whether you have Perl / where it is before
> the first opportunity to click 'back', so, right at the beginning.
> 2) if I rename my Perl 5.8 directory--whether or not I rename my Perl
> 5.10 directory--the installer cannot find it and does not present pl/
> perl[u] as an option a few Next-clicks later.
> 3) if I rename my Perl 5.10 directory to where I had had my Perl 5.8
> directory, it's still not presented as an option
>
> So...what, is it insisting on perl58.dll at the beginning of the
> install and then perl510.dll when it goes to actually use it? Let's
> see...i'll rename things back (so it'll be an option), select the pl/
> perl options, and then rename the directories before clicking 'next'.
> Result: same errors when it goes to activate plperl and plperlu. I
> guess not.

This is actually a bit worse than I thought--I can't ditch 5.8, then.
I haven't tried a full uninstall of 5.8 and 5.10 and then installing
5.10 and then installing pgsql. I guess that's the next thing to try.

On May 2, 12:11 pm, Kevin Field <kevinjamesfi(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> On May 2, 12:09 pm, Kevin Field <kevinjamesfi(dot)(dot)(dot)(at)gmail(dot)com> wrote:
>
>
>
> > On May 1, 12:41 pm, Kevin Field <kevinjamesfi(dot)(dot)(dot)(at)gmail(dot)com> wrote:
>
> > > On Apr 26, 2:08 pm, dp(dot)(dot)(dot)(at)pgadmin(dot)org (Dave Page) wrote:
>
> > > > On Fri, Apr 24, 2009 at 3:09 PM, Kevin Field <kevinjamesfi(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> > > > > On Apr 24, 9:32 am, dp(dot)(dot)(dot)(at)pgadmin(dot)org (Dave Page) wrote:
>
> > > > >> I don't know if there is any way we can solve it, except by reverting
> > > > >> back to 5.8 or advising users to use only one version.
>
> > > > > I just had an idea--at least in the ActiveState distributions (not
> > > > > sure about Strawberry or Vanilla) they include versioned-filename
> > > > > binaries. So you can launch perl5.8.8.exe or perl5.10.0.exe and as
> > > > > long as it's in the path you get the exact right one. (As I found out
> > > > > when I went to try to figure out per-user paths, I somehow have both
> > > > > versions in the universal path.) Would that help?
>
> > > > No, because we use the DLLs, not the .exe. But they do seem to be
> > > > versioned anyway, so there must be something else going on :-(
>
> > > :( Well on my end, I haven't been able to figure out how to get it to
> > > see the right version. Do you know when the next beta or win32
> > > installer build might be out so I can try again?
>
> > Through some testing today I determined:
>
> > 1) the installer decides on whether you have Perl / where it is before
> > the first opportunity to click 'back', so, right at the beginning.
> > 2) if I rename my Perl 5.8 directory--whether or not I rename my Perl
> > 5.10 directory--the installer cannot find it and does not present pl/
> > perl[u] as an option a few Next-clicks later.
> > 3) if I rename my Perl 5.10 directory to where I had had my Perl 5.8
> > directory, it's still not presented as an option
>
> > So...what, is it insisting on perl58.dll at the beginning of the
> > install and then perl510.dll when it goes to actually use it? Let's
> > see...i'll rename things back (so it'll be an option), select the pl/
> > perl options, and then rename the directories before clicking 'next'.
> > Result: same errors when it goes to activate plperl and plperlu. I
> > guess not.
>
> This is actually a bit worse than I thought--I can't ditch 5.8, then.
> I haven't tried a full uninstall of 5.8 and 5.10 and then installing
> 5.10 and then installing pgsql. I guess that's the next thing to try.

Okay, I uninstalled both 5.8 and 5.10, installed 5.10 fresh, and it
doesn't detect it during install. I tried another fresh install of
5.10 with PerlScript. Same deal. Now I'm trying a fresh install with
all options checked. Still no dice. What exactly is it looking for?
Has anybody had a successful install of ActiveState Perl 5.10 build
1004 followed by PostgreSQL 8.4-beta1 with plperl[u]?

On Sat, May 2, 2009 at 9:15 PM, Kevin Field <kevinjamesfield(at)gmail(dot)com> wrote:

> Okay, I uninstalled both 5.8 and 5.10, installed 5.10 fresh, and it
> doesn't detect it during install.  I tried another fresh install of
> 5.10 with PerlScript.  Same deal.  Now I'm trying a fresh install with
> all options checked.  Still no dice.  What exactly is it looking for?
> Has anybody had a successful install of ActiveState Perl 5.10 build
> 1004 followed by PostgreSQL 8.4-beta1 with plperl[u]?

The installer is still looking for perl58.dll, whilst the server
needs perl510.dll. I've committed a fix for that (and the other PLs
which were similarly afflicted).

I've rebuilt the installer (using the existing binaries from the last
build) and uploaded it to http://developer.pgadmin.org/~dpage/. Please
give it a whirl.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com