pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to PG close session
6+ messages / 3 participants
[nested] [flat]
* how to PG close session
@ 2010-10-01 13:45 Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
2010-10-01 14:01 ` Re: how to PG close session Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 6+ messages in thread
From: Little, Douglas @ 2010-10-01 13:45 UTC (permalink / raw)
To: pgsql-admin
Hi,
We have a number of long running java/jdbc applications that wait for events and then extract data from PG.
During the time the apps are running, The app is maintaining active connections to PG.
Is there a way that we can close/terminate the pg session so that the connections don't consume resource unnecessarily.
Thanks
Doug Little
Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741
Douglas.Little@orbitz.com<mailto:Douglas.Little@orbitz.com>
[cid:image001.jpg@01CB6144.EEEEAA00] orbitz.com<http://www.orbitz.com/; | ebookers.com<http://www.ebookers.com/; | hotelclub.com<http://www.hotelclub.com/; | cheaptickets.com<http://www.cheaptickets.com/; | ratestogo.com<http://www.ratestogo.com/; | asiahotels.com<http://www.asiahotels.com/;
Attachments:
image001.jpg (image/jpeg, 2.3K) download
view image
* Re: how to PG close session
2010-10-01 13:45 how to PG close session Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
@ 2010-10-01 14:01 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-10-01 14:12 ` Re: how to PG close session Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
0 siblings, 1 reply; 6+ messages in thread
From: Kevin Grittner @ 2010-10-01 14:01 UTC (permalink / raw)
To: Douglas Little <DOUGLAS.LITTLE@orbitz.com>; pgsql-admin
"Little, Douglas" <DOUGLAS.LITTLE@orbitz.com> wrote:
> We have a number of long running java/jdbc applications that wait
> for events and then extract data from PG.
> During the time the apps are running, The app is maintaining
> active connections to PG.
> Is there a way that we can close/terminate the pg session so that
> the connections don't consume resource unnecessarily.
A relatively small number of such connections shouldn't be a
problem. It's hard to make concrete suggestions on so little
information, but I would point out that you can write and install
your own functions which can do just about anything you want,
including making network connections. So, hypothetically, you could
register to receive certain information on a certain event in some
"requests" table, with a URL to signal when the event occurs, and
disconnect. Some trigger could recognize that an event has
occurred, and call a function to signal the given URL.
Just about anything is possible, it's just a Simple Matter Of
Programming....
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: how to PG close session
2010-10-01 13:45 how to PG close session Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
2010-10-01 14:01 ` Re: how to PG close session Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-10-01 14:12 ` Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
2010-10-01 14:24 ` Re: how to PG close session Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 6+ messages in thread
From: Little, Douglas @ 2010-10-01 14:12 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin
Hey Kevin,
Thanks for the response.
Currently it's keeping 16 open connections and it isn't even running. I believe that they(finance) can run 5 concurrent java apps and each opens a number of sessions. Currently all 16 sessions are owned by 1 datid. So potentially it's 80 connections, this is on top of all our cognos (which also leaves connections open). We're typically have 80 - 300 connections open.
I'm really looking for the PG command to close a session.
-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: Friday, October 01, 2010 9:01 AM
To: Little, Douglas; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] how to PG close session
"Little, Douglas" <DOUGLAS.LITTLE@orbitz.com> wrote:
> We have a number of long running java/jdbc applications that wait
> for events and then extract data from PG.
> During the time the apps are running, The app is maintaining
> active connections to PG.
> Is there a way that we can close/terminate the pg session so that
> the connections don't consume resource unnecessarily.
A relatively small number of such connections shouldn't be a
problem. It's hard to make concrete suggestions on so little
information, but I would point out that you can write and install
your own functions which can do just about anything you want,
including making network connections. So, hypothetically, you could
register to receive certain information on a certain event in some
"requests" table, with a URL to signal when the event occurs, and
disconnect. Some trigger could recognize that an event has
occurred, and call a function to signal the given URL.
Just about anything is possible, it's just a Simple Matter Of
Programming....
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: how to PG close session
2010-10-01 13:45 how to PG close session Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
2010-10-01 14:01 ` Re: how to PG close session Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-10-01 14:12 ` Re: how to PG close session Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
@ 2010-10-01 14:24 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-10-01 15:52 ` Re: how to PG close session Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
0 siblings, 1 reply; 6+ messages in thread
From: Kevin Grittner @ 2010-10-01 14:24 UTC (permalink / raw)
To: Douglas Little <DOUGLAS.LITTLE@orbitz.com>; pgsql-admin
"Little, Douglas" <DOUGLAS.LITTLE@orbitz.com> wrote:
> Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] wrote:
>> "Little, Douglas" <DOUGLAS.LITTLE@orbitz.com> wrote:
>>
>>> We have a number of long running java/jdbc applications that
>>> wait for events and then extract data from PG.
>>> During the time the apps are running, The app is maintaining
>>> active connections to PG.
>>> Is there a way that we can close/terminate the pg session so
>>> that the connections don't consume resource unnecessarily.
>> Some trigger could recognize that an event has occurred, and call
>> a function to signal
> Currently it's keeping 16 open connections and it isn't even
> running. I believe that they(finance) can run 5 concurrent java
> apps and each opens a number of sessions. Currently all 16
> sessions are owned by 1 datid. So potentially it's 80
> connections, this is on top of all our cognos (which also leaves
> connections open). We're typically have 80 - 300 connections
> open.
Oh, these connections aren't waiting for *database* events? Then
you should either restructure the apps or use a connection pool.
Two popular "drop in" connection poolers are pgbouncer and pgpool.
> I'm really looking for the PG command to close a session.
http://www.postgresql.org/docs/current/interactive/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL-TABLE
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: how to PG close session
2010-10-01 13:45 how to PG close session Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
2010-10-01 14:01 ` Re: how to PG close session Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-10-01 14:12 ` Re: how to PG close session Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
2010-10-01 14:24 ` Re: how to PG close session Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-10-01 15:52 ` Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
2010-10-01 19:03 ` Re: how to PG close session Greg Smith <greg@2ndquadrant.com>
0 siblings, 1 reply; 6+ messages in thread
From: Little, Douglas @ 2010-10-01 15:52 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin
Thanks
The only admin function that look close is
pg_terminate_backend(pid int) boolean Terminate a backend
I tried it and it couldn't be found. It is newist. We're running PG8.2.13/GP3.3.7
>Oh, these connections aren't waiting for *database* events? Then
>you should either restructure the apps or use a connection pool.
>Two popular "drop in" connection poolers are pgbouncer and pgpool.
We tried pgpool for awhile, but I think I'll have a look at the code first.
I did find in the JDBC doc connections.close which should do the trick I suspect.
Thanks
> I'm really looking for the PG command to close a session.
http://www.postgresql.org/docs/current/interactive/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL-TABLE
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: how to PG close session
2010-10-01 13:45 how to PG close session Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
2010-10-01 14:01 ` Re: how to PG close session Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-10-01 14:12 ` Re: how to PG close session Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
2010-10-01 14:24 ` Re: how to PG close session Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-10-01 15:52 ` Re: how to PG close session Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
@ 2010-10-01 19:03 ` Greg Smith <greg@2ndquadrant.com>
0 siblings, 0 replies; 6+ messages in thread
From: Greg Smith @ 2010-10-01 19:03 UTC (permalink / raw)
To: Little, Douglas <DOUGLAS.LITTLE@orbitz.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin
Little, Douglas wrote:
> The only admin function that look close is
> pg_terminate_backend(pid int) boolean Terminate a backend
> I tried it and it couldn't be found. It is newist. We're running PG8.2.13/GP3.3.7
>
Yes, that's an 8.4 function.
> We tried pgpool for awhile, but I think I'll have a look at the code first.
> I did find in the JDBC doc connections.close which should do the trick I suspect.
>
Does your Java app have an application server like Tomcat in it? Those
can be setup to work about as well as a PostgreSQL specific pooler, and
you get tighter integration with the application itself normally in the
process.
--
Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance" Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-10-01 19:03 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-10-01 13:45 how to PG close session Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
2010-10-01 14:01 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-10-01 14:12 ` Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
2010-10-01 14:24 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-10-01 15:52 ` Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
2010-10-01 19:03 ` Greg Smith <greg@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox