Thread: Several PostGreSQL questions.

Several PostGreSQL questions.

From
Warren Vanichuk
Date:
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? :)


RE: Several PostGreSQL questions.

From
Matthew
Date:
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? :)

Date arithmatic question

From
"Bryan \(Mailing Lists\)"
Date:
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



Re: Date arithmatic question

From
"Bryan \(Mailing Lists\)"
Date:
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
>


RE: Date arithmatic question

From
"Francis Solomon"
Date:
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
>
>
>