number of connections to postmaster

Check out techdocs.postgresql.org and download PL/Sh by Peter Eisentraut. You
can do the job with the help of an embedded Shell script.
As far as I know there is no other (easy way) of retrieving that information.

Try something like that:

CREATE OR REPLACE FUNCTION "backends_number"() RETURNS "text" AS '
#!/bin/sh
expr $(ps ax | egrep -e ''postgres:.*local'' | wc -l) - 1
' LANGUAGE 'plsh';

I have not tested this extensively but it should work.
Note: PL/Sh is not a trusted embedded languages (system calls are allowed) ->
use a separate database for that and don't use your production database
(security!!!!).

Hans

"Percy" <percy_yip@yahoo.com> writes:

Is there any way to find out the number of connections to postmaster?

In 7.2, "select count(*) from pg_stat_activity" should do it. See
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/monitoring.html

regards, tom lane

Three questions:

1. In a script, how do I set the value of a date attr to the current
date. I've tried a few obvious approaches but nothing works yet.

2.If I do:
psql <db> -c "select current_date;"
I get:

date
------------
2002-02-23
(1 row)

I can use:
psql <db> -c "select current_date;" | tail -3 | head -1
to just get the date value, but is there simpler way?

And lastly, I believe there are a few environmental variables used to
control various PostgreSQL behaviours, eg: date/time format. Where is
there a list of these? (I can't find them in the docs)

Thanks

=============================================================================

_-''-_ <Brent Wood> b.wood@niwa.cri.nz Ph: +64(4)386-0300
'o \/ NIWA, Box 14901,

) /\ Kilbirnie, Wellington, New Zealand

`-===-' #include <std_disclaimer>

It looks that you want the date, for usage at the shell level. Why not just do "date +%Y-%m-%d" ?

cheers,
thalis

On Fri, 22 Feb 2002, Brent Wood wrote:

Tom
When I try that, I get

template1=# select count(*) from pg_stat_activity;
ERROR: Relation 'pg_stat_activity' does not exist

thanks
Pam Wampler

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, February 21, 2002 7:35 PM
To: Percy
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] number of connections to postmaster

"Percy" <percy_yip@yahoo.com> writes:

Is there any way to find out the number of connections to postmaster?

In 7.2, "select count(*) from pg_stat_activity" should do it. See
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/monitoring.html

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

On Fri, 22 Feb 2002, Pam Wampler wrote:

Tom
When I try that, I get

template1=# select count(*) from pg_stat_activity;
ERROR: Relation 'pg_stat_activity' does not exist

Hi,

Maybe you are running an old version of PostgreSQL? Tom says,
pg_stat_activity comes with v7.2 .

Regards,

--

Devrim GUNDUZ

devrim@oper.metu.edu.tr
devrim.gunduz@linux.org.tr
devrimg@tr.net

Web : http://devrim.oper.metu.edu.tr
------------------------------------------------------------------

Pam Wampler <Pam_Wampler@taylorwhite.com> writes:

Tom
When I try that, I get

template1=# select count(*) from pg_stat_activity;
ERROR: Relation 'pg_stat_activity' does not exist

That would imply you're not running 7.2.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

yes I am running 7.1.3
thanks
pam

-----Original Message-----
From: Devrim GUNDUZ [mailto:devrim@oper.metu.edu.tr]
Sent: Friday, February 22, 2002 8:46 AM
To: Pam Wampler
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] number of connections to postmaster

On Fri, 22 Feb 2002, Pam Wampler wrote:

Tom
When I try that, I get

template1=# select count(*) from pg_stat_activity;
ERROR: Relation 'pg_stat_activity' does not exist

Hi,

Maybe you are running an old version of PostgreSQL? Tom says,
pg_stat_activity comes with v7.2 .

Regards,

--

Devrim GUNDUZ

devrim@oper.metu.edu.tr
devrim.gunduz@linux.org.tr
devrimg@tr.net

Web : http://devrim.oper.metu.edu.tr
------------------------------------------------------------------

psql <db> -A -t -c "select current_date;" will do what
you appear to want.

But, as already pointed out, you are probably better
off using the methods provided in your scripting
language (shell or whatever).

--- Brent Wood <baw@frc.niwa.cri.nz> wrote:

Three questions:

1. In a script, how do I set the value of a date
attr to the current
date. I've tried a few obvious approaches but
nothing works yet.

2.If I do:
psql <db> -c "select current_date;"
I get:

date
------------
2002-02-23
(1 row)

I can use:
psql <db> -c "select current_date;" | tail
-3 | head -1
to just get the date value, but is there simpler
way?

And lastly, I believe there are a few environmental
variables used to
control various PostgreSQL behaviours, eg: date/time
format. Where is
there a list of these? (I can't find them in the
docs)

Thanks

=============================================================================

_-''-_ <Brent Wood> b.wood@niwa.cri.nz
Ph: +64(4)386-0300
'o \/ NIWA, Box 14901,

) /\ Kilbirnie, Wellington, New Zealand

`-===-' #include <std_disclaimer>

---------------------------(end of
broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

__________________________________________________
Do You Yahoo!?
Yahoo! Sports - Coverage of the 2002 Olympic Games
http://sports.yahoo.com

Try:

psql <db> -A -t -c "select current_date;"

Pam Wampler wrote:

Tom
When I try that, I get

template1=# select count(*) from pg_stat_activity;
ERROR: Relation 'pg_stat_activity' does not exist

Tom said "in 7.2". So why do you expect it to work in a
pre-7.2 version?

And BTW:

SELECT numbackends FROM pg_stat_database ...

could do it too.

Jan

thanks
Pam Wampler

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, February 21, 2002 7:35 PM
To: Percy
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] number of connections to postmaster

"Percy" <percy_yip@yahoo.com> writes:

Is there any way to find out the number of connections to postmaster?

In 7.2, "select count(*) from pg_stat_activity" should do it. See
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/monitoring.html

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com