Обсуждение: postmaster locking issues
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 theconclusion that this problem could be due to some internal locking problem in Postgres. This issue of the locking abilities of the postmasterhas 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
"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.pidIs 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
<blockquote type="CITE"> <br /><a href="http://www.hotmail.com">Hi all,</a></blockquote> I am using postgres 7.0.2 and havingsome problems after adding new tables to an existing database. I initially created a database for a web applicationusing 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 deleterecords from the new tables. But For some reason, If I use the same querries in web application, it does not returnany data. In otherwords, the web application sees only the tables created initially when the database was created andit does not see the tables added after the creation of the tables. Are there any commands to update the database serverabout the newly created tables. I restarted the postgres database server, restarted the Apache webserver. It stilldoes not recognise the new tables in my web application. I made sure that all the querries are alright, because I copiedand pasted the querries from my web application onto the psql command line, they all worked. I don't know if this ispostgres related problem or not. <p>Thanks, I really appreciate your help, <br />Nataraj
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
> 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
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
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
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 | sauronsauron | sauronspares | saurontemplate1 | sauronudmsearch | sauroninfinite_test | sauron (6 rows) -- Dominic J. Eidson "Baruk Khazad! Khazad ai-menu!" - Gimli ------------------------------------------------------------------------------- http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/
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 :)
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
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 usesysidFROM 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/
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
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
nataraj@cdxc.com writes: Are you sure you created the tables in the right database? Mike.
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.
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.