Several PostGreSQL questions.
Greetings.
We've recently been seeing several database crashes. The last things seen
in the logs are :
postmaster: StreamConnection: accept: Too many open files in system
postmaster: StreamConnection: accept: Too many open files in system
FATAL 1: ReleaseLruFile: No open files available to be closed
IpcSemaphoreLock: semop failed (Identifier removed) id=512
IpcSemaphoreLock: semop failed (Identifier removed) id=513
(repeated about 20-30 times)
IpcSemaphoreUnlock: semop failed (Invalid argument) id=515
FATAL: s_lock(40014065) at spin.c:127, stuck spinlock. Aborting.
FATAL: s_lock(40014065) at spin.c:127, stuck spinlock. Aborting.
FATAL: s_lock(40014065) at spin.c:127, stuck spinlock. Aborting.
(repeated about 100 times)
This is PostgreSQL v7.0.2 under a Debian 2.2 system with a custom 2.2.17
kernel installed. I have the maximum per-process file descriptor limit set
to 1024, and the maximum system file descriptor limit set to 8192, so I'm
confused about the first messages.
I'm also seeing :
Sorry, too many clients already
Sorry, too many clients already
Sorry, too many clients already
notices, but I thought I increased the number of allowed conncetions.
What's the correct way to go about doing this? :)
If you do a:
/usr/sbin/lsof |wc
What is the output? First number from wc is the number of lines, which in
this case translates to the number of open file descriptors. If this is
close to your limit, then bump your limit.
Show quoted text
-----Original Message-----
From: Warren Vanichuk [SMTP:pyber@street-light.com]
Sent: Friday, November 17, 2000 12:41 PM
To: pgsql-general@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: [GENERAL] Several PostGreSQL questions.Greetings.
We've recently been seeing several database crashes. The last things seen
in the logs are :postmaster: StreamConnection: accept: Too many open files in system
postmaster: StreamConnection: accept: Too many open files in system
FATAL 1: ReleaseLruFile: No open files available to be closed
IpcSemaphoreLock: semop failed (Identifier removed) id=512
IpcSemaphoreLock: semop failed (Identifier removed) id=513
(repeated about 20-30 times)
IpcSemaphoreUnlock: semop failed (Invalid argument) id=515FATAL: s_lock(40014065) at spin.c:127, stuck spinlock. Aborting.
FATAL: s_lock(40014065) at spin.c:127, stuck spinlock. Aborting.
FATAL: s_lock(40014065) at spin.c:127, stuck spinlock. Aborting.
(repeated about 100 times)This is PostgreSQL v7.0.2 under a Debian 2.2 system with a custom 2.2.17
kernel installed. I have the maximum per-process file descriptor limit
set
to 1024, and the maximum system file descriptor limit set to 8192, so I'm
confused about the first messages.I'm also seeing :
Sorry, too many clients already
Sorry, too many clients already
Sorry, too many clients alreadynotices, but I thought I increased the number of allowed conncetions.
What's the correct way to go about doing this? :)
Import Notes
Resolved by subject fallback
I have a table "t" with a timestamp column "s". I am trying to issue a query
to find all rows where s is exactly some number of days old (rounded off).
I have tried this:
select * from t where date_part('day', age('now', s)) = ?
But this only looks at the day of the month; e.g. if my parameter is "5",
then it will return all rows that are 5 days old, as well as 1 month 5 days,
as well as 2 months 5 days, etc.
I have also tried this:
select * from t where date_part('day', age('now', s)) = ? and
date_part('month', age('now', status_last_update)) = 0
But this restricts my parameter to 30 days or less; greater than 30 days and
the query doesn't return anything.
What is the correct way to express this?
Thank you,
Bryan
Whoops, I had a typo in my translation; the second query I quoted should
read as follows:
select * from t where date_part('day', age('now', s)) = ? and
date_part('month', age('now', s)) = 0
Thanks,
Bryan
----- Original Message -----
From: "Bryan (Mailing Lists)" <bryan_lists@netmeme.org>
To: <pgsql-general@postgresql.org>
Sent: Friday, November 17, 2000 3:33 PM
Subject: [GENERAL] Date arithmatic question
I have a table "t" with a timestamp column "s". I am trying to issue a
query
to find all rows where s is exactly some number of days old (rounded off).
I have tried this:
select * from t where date_part('day', age('now', s)) = ?
But this only looks at the day of the month; e.g. if my parameter is "5",
then it will return all rows that are 5 days old, as well as 1 month 5
days,
as well as 2 months 5 days, etc.
I have also tried this:
select * from t where date_part('day', age('now', s)) = ? and
date_part('month', age('now', status_last_update)) = 0But this restricts my parameter to 30 days or less; greater than 30 days
and
Show quoted text
the query doesn't return anything.
What is the correct way to express this?
Thank you,
Bryan
Hi Bryan,
Try this:
select * from t where date_part('days', 'now'::timestamp - s) = ?;
Subtracting two timestamps in this way returns an 'interval', which
(unless you use the 'age' function) doesn't figure out the months and
the years - so 3 months would be rendered as "92 00:00"
Also, note that I think you need to use "days" rather than "day" as the
1st arg to date_part.
Hope this helps you.
Francis Solomon
Show quoted text
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Bryan (Mailing
Lists)
Sent: 17 November 2000 22:34
To: pgsql-general@postgresql.org
Subject: [GENERAL] Date arithmatic questionI have a table "t" with a timestamp column "s". I am trying
to issue a query
to find all rows where s is exactly some number of days old
(rounded off).I have tried this:
select * from t where date_part('day', age('now', s)) = ?
But this only looks at the day of the month; e.g. if my
parameter is "5",
then it will return all rows that are 5 days old, as well as
1 month 5 days,
as well as 2 months 5 days, etc.I have also tried this:
select * from t where date_part('day', age('now', s)) = ? and
date_part('month', age('now', status_last_update)) = 0But this restricts my parameter to 30 days or less; greater
than 30 days and
the query doesn't return anything.What is the correct way to express this?
Thank you,
Bryan