Thread: 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. > -----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=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? :)
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)) = 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
>
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
> -----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 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)) = 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
>
>
>