Hello Everybody,
     Sorry for mailing at both the addresses. The situation is a nightmare 
at our installation facility, hence the need to capture as much attention as 
possible.
     We are using PostgreSQL 7.0 along with Enhydra 3.0 application server 
to host a web site. It has been observed that sometimes (can't pinpoint when 
it starts) the postmaster instance 'hangs' and another starts. Then the new 
one hangs and another starts. This happens until the max limit for backends 
is reached (32 in our case). Then the whole application crashes.
     After some debugging in our code, we have come to the conclusion that 
this problem could be due to some internal locking problem in Postgres.
     This issue of the locking abilities of the postmaster has been 
discussed before (see the reference section below). However, it seems that 
it was dropped without any action plan, especially the part about point 3 : 
"Two PID files will be necessary, one to prevent mulitple instances of 
postmasters from running against the same data base, and one to prevent 
multiple instances from using the same port."
     Can anybody point us in the right direction? Thanks in advance.

References:
http://www.postgresql.org/mhonarc/pgsql-hackers/1998-10/msg00295.html
This is the first mail in the thread by Bill Allie.

Thank You,
Suchet Singh,
IMRglobal Corp.

P.S. Please mail any suggestions to suchet_singh@hotmail.com
________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com


Mon, 21 Aug 2000 02:57:59 GMT
"suchet singh khalsa" <suchet_singh@hotmail.com>
postmaster locking issues
"suchet singh khalsa" <suchet_singh@hotmail.com> writes:
>      This issue of the locking abilities of the postmaster has been 
> discussed before (see the reference section below). However, it seems that 
> it was dropped without any action plan, especially the part about point 3 : 
> "Two PID files will be necessary, one to prevent mulitple instances of 
> postmasters from running against the same data base, and one to prevent 
> multiple instances from using the same port."

No, this was fixed long since.  In 7.0 I see the following behavior:

Try to start a postmaster on an already-in-use port number:

	FATAL: StreamServerPort: bind() failed: Address already in use
	        Is another postmaster already running on that port?
	        If not, wait a few seconds and retry.
	postmaster: cannot create INET stream port

Try to start a postmaster on a free port in an in-use data directory:

	Can't create pid file: /home/postgres/testversion/data/postmaster.pid
	Is another postmaster (pid: 3124) running?

Proper detection of port conflicts may be platform-dependent ...
what platform are you running on?

Actually, given your stated observation:

>      We are using PostgreSQL 7.0 along with Enhydra 3.0 application server 
> to host a web site. It has been observed that sometimes (can't pinpoint when 
> it starts) the postmaster instance 'hangs' and another starts. Then the new 
> one hangs and another starts. This happens until the max limit for backends 
> is reached (32 in our case). Then the whole application crashes.

I'll bet that what you are seeing is not multiple postmasters at all,
but multiple backends.  Does "Enhydra" open up new database connections
without bothering to close old ones?  If so, that's where the problem
lies.  A backend will normally not quit until it sees a proper
termination message or connection closure from its client.  We've heard
of quite a number of broken apps that do not reliably close
connections...

			regards, tom lane

Tue, 22 Aug 2000 00:50:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: postmaster locking issues 
>
> Hi all,

I am using postgres 7.0.2 and having some problems after adding new tables to
an existing database. I initially created a database for a web application
using Perl, CGI and Apache webserver on Redhat Linux 6.2.  After createing the
database I added few tables using "Create table" command. Using the psql
commands I can querry the newly created tables and I can insert into and delete
records from the new tables. But For some reason, If I use the same querries in
web application, it does not return any data. In otherwords, the web
application sees only the tables created initially when the database was
created and it does not see the tables added after the creation of the tables.
Are there any commands to update the database server about the newly  created
tables. I restarted the postgres database server, restarted the Apache
webserver. It still does not recognise the new tables in my web application. I
made sure that all the querries are alright, because I copied and pasted the
querries from my web application onto the psql command line, they all worked. I
don't know if this is postgres related problem or not.

Thanks, I really appreciate your help,
Nataraj


 
Hi all,
I am using postgres 7.0.2 and having some problems after adding new tables
to an existing database. I initially created a database for a web application
using Perl, CGI and Apache webserver on Redhat Linux 6.2.  After createing
the database I added few tables using "Create table" command. Using the
psql commands I can querry the newly created tables and I can insert into
and delete records from the new tables. But For some reason, If I use the
same querries in web application, it does not return any data. In otherwords,
the web application sees only the tables created initially when the database
was created and it does not see the tables added after the creation of
the tables. Are there any commands to update the database server about
the newly  created tables. I restarted the postgres database server,
restarted the Apache webserver. It still does not recognise the new tables
in my web application. I made sure that all the querries are alright, because
I copied and pasted the querries from my web application onto the psql
command line, they all worked. I don't know if this is postgres related
problem or not.
Thanks, I really appreciate your help,
Nataraj
Tue, 22 Aug 2000 09:41:17 -0500
nataraj@cdxc.com
postgres 7.0.2
nataraj@cdxc.com writes:

Are you sure you created the tables in the right database?

Mike.

22 Aug 2000 11:13:29 -0400
Michael Alan Dorman <mdorman@mallet-assembly.org>
Re: postgres 7.0.2
nataraj@cdxc.com writes:
> I added few tables using "Create table" command. Using the psql
> commands I can querry the newly created tables and I can insert into
> and delete records from the new tables. But For some reason, If I use
> the same querries in web application, it does not return any data. In
> otherwords, the web application sees only the tables created initially
> when the database was created and it does not see the tables added
> after the creation of the tables.

Offhand I'm betting that your webserver is connecting to a different
database than the one you created the tables in.  If you aren't
careful to specify, the default is to connect to a database named the
same as your username, so it's easy to see how the webserver might be
connecting to a different db.  "psql -l" should list the databases
that your postmaster has, or you can do "select * from pg_database".

			regards, tom lane

Tue, 22 Aug 2000 11:30:13 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: postgres 7.0.2 
Tom Lane wrote:
> same as your username, so it's easy to see how the webserver might be
> connecting to a different db.  "psql -l" should list the databases
> that your postmaster has, or you can do "select * from pg_database".

Hi Tom - it was nice to meet you at the show last week.

This command you just showed here is extremely subtle and few people
know about it.

What would it take for you to map "show databases" to get that same
output? Same for "show tables" and "describe tbl_name". I think these
are standard SQL conventions which would be helpful for a lot of people.

Tim

-- 
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
408-542-5723

Tue, 22 Aug 2000 08:49:49 -0700
Tim Perdue <tim@sourceforge.net>
Re: postgres 7.0.2
> What would it take for you to map "show databases" to get that same
> output? Same for "show tables" and "describe tbl_name". I think these
> are standard SQL conventions which would be helpful for a lot of people.

I don't see anything about such commands in the spec ;-).  These things
strike me as user-interface operations rather than something the backend
ought to provide on its own.

I am a bit surprised to note that psql doesn't seem to have any
backslash command for listing databases.  Peter, what do you think?
Maybe "\db", seeing that \dd is already taken?

			regards, tom lane

Tue, 22 Aug 2000 11:54:49 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: postgres 7.0.2 
On Tue, 22 Aug 2000, Tom Lane wrote:

> I am a bit surprised to note that psql doesn't seem to have any
> backslash command for listing databases.  Peter, what do you think?
> Maybe "\db", seeing that \dd is already taken?

sauron=# \l
    List of databases
   Database    |  Owner
---------------+---------
 domains       | sauron
 sauron        | sauron
 spares        | sauron
 template1     | sauron
 udmsearch     | sauron
 infinite_test | sauron
(6 rows)


-- 
Dominic J. Eidson
                                        "Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/              http://www.the-infinite.org/~dominic/


Tue, 22 Aug 2000 11:11:32 -0500 (CDT)
"Dominic J. Eidson" <sauron@the-infinite.org>
Re: postgres 7.0.2 
Tom Lane <tgl@sss.pgh.pa.us> el dï¿½a Tue, 22 Aug 2000 11:54:49 -0400, 
escribiï¿½:

>I am a bit surprised to note that psql doesn't seem to have any
>backslash command for listing databases.  Peter, what do you think?
>Maybe "\db", seeing that \dd is already taken?

I don't know why postgres insist in using this cryptics escape commands ...
(and as you see, this commands are running out of namespace)

sergio


Tue, 22 Aug 2000 13:14:19 -0300
sak@tribctas.gba.gov.ar
Re: postgres 7.0.2
At 8/22/2000 11:54 AM -0400, Tom Lane wrote:
>I am a bit surprised to note that psql doesn't seem to have any
>backslash command for listing databases.  Peter, what do you think?
>Maybe "\db", seeing that \dd is already taken?

\l lists all databases

:)


Tue, 22 Aug 2000 11:16:50 -0500
Thomas Swan <tswan-lst@tangent.ics.olemiss.edu>
Re: postgres 7.0.2 
At 8/22/2000 11:54 AM -0400, Tom Lane wrote:
> > What would it take for you to map "show databases" to get that same
> > output? Same for "show tables" and "describe tbl_name". I think these
> > are standard SQL conventions which would be helpful for a lot of people.
>
>I don't see anything about such commands in the spec ;-).  These things
>strike me as user-interface operations rather than something the backend
>ought to provide on its own.

Actually, I think I understand the question.  The original person wants to 
be able to do a query and get a result containing a list of 
databases.   AFAIK, there isn't a way to do this using standard SQL-like 
statements.  Somebody correct me if I'm wrong.

Thomas


Tue, 22 Aug 2000 11:22:23 -0500
Thomas Swan <tswan-lst@tangent.ics.olemiss.edu>
Re: postgres 7.0.2 
On Tue, 22 Aug 2000, Thomas Swan wrote:

> Actually, I think I understand the question.  The original person wants to 
> be able to do a query and get a result containing a list of 
> databases.   AFAIK, there isn't a way to do this using standard SQL-like 
> statements.  Somebody correct me if I'm wrong.

SELECT pg_database.datname as "Database", pg_user.usename as "Owner" FROM
       pg_database, pg_user WHERE pg_database.datdba = pg_user.usesysid
UNION
SELECT pg_database.datname as "Database", NULL as "Owner" FROM pg_database
       WHERE pg_database.datdba NOT IN (SELECT usesysid FROM pg_user) 
       ORDER BY "Database";

(Which is what psql sends to the backend in response to the "\l" command.)

-- 
Dominic J. Eidson
                                        "Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/              http://www.the-infinite.org/~dominic/



Tue, 22 Aug 2000 12:04:27 -0500 (CDT)
"Dominic J. Eidson" <sauron@the-infinite.org>
Re: postgres 7.0.2 
Thomas Swan <tswan-lst@tangent.ics.olemiss.edu> writes:
> Actually, I think I understand the question.  The original person wants to 
> be able to do a query and get a result containing a list of 
> databases.   AFAIK, there isn't a way to do this using standard SQL-like 
> statements.  Somebody correct me if I'm wrong.

SELECT datname FROM pg_database;

I think Tim's real gripe is about having to know enough about the
contents of the system tables to be able to construct such a query.

			regards, tom lane

Tue, 22 Aug 2000 13:06:47 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: postgres 7.0.2 
Tom Lane wrote:

> I think Tim's real gripe is about having to know enough about the
> contents of the system tables to be able to construct such a query.

That's exactly right. I've used this thing for quite a while now and
still couldn't tell you what the various system tables do or even how to
tell what system tables exist.

Tim

-- 
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
408-542-5723

Tue, 22 Aug 2000 10:19:21 -0700
Tim Perdue <tim@sourceforge.net>
Re: postgres 7.0.2
Yes, I am sure. But after creating tables if I use
"grant READ,WRITE on shipment_history TO user";
it gives the error:
ERROR:  parser: parse error at or near "read". I thought the grant
command is similar to ORACLE. Is it different?

Thanks,
Nataraj

Michael Alan Dorman wrote:

> nataraj@cdxc.com writes:
>
> Are you sure you created the tables in the right database?
>
> Mike.


Fri, 25 Aug 2000 15:46:35 -0500
nataraj@cdxc.com
Re: postgres 7.0.2
nataraj@cdxc.com writes:
> Yes, I am sure. But after creating tables if I use
> "grant READ,WRITE on shipment_history TO user";
> it gives the error:
> ERROR:  parser: parse error at or near "read". I thought the grant
> command is similar to ORACLE. Is it different?

use \h grant in psql, or read the documentation.  Yes, it's different.

Mike.

25 Aug 2000 17:02:53 -0400
Michael Alan Dorman <mdorman@debian.org>
Re: postgres 7.0.2