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

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

nataraj@cdxc.com writes:

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

Mike.

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

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

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

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/

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

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

...

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.

I cannot test this now, but I think
"select * from pg_database" should do it.

Mario

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/

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

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:

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.