Thread: selecting N record for each group
There are many ways to achieve this: http://rickosborne.org/blog/index.php/2008/01/07/sql-getting-top-n-rows-for-a-grouped-query/ http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/More-SQL-Server-2005-Solutions.aspx I was wondering if any of the available methods perform better on postgresql: The actual situation is: create table type( id int primary key, name varchar(32) ); create table list( id int primary key, name varchar(32), type int references type (id), inserted timestamp ); I'd like to get the type.name, list.name of the N newest items for a set of types (eg. where type.id in (1,2,4)). the list of types I'm interested in is composed of 20-100 types on 10K distinct types. list table has 1M record N is in the order of 5-20. I could use plpgsqql too but I'm not sure it could make any better compared to plain sql. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it
hi all, i have some problems with my postgresql database server : i develop some client-server program using postgre as its database (used by almost 100 client), my problem comes after some periode of time( some times 1 day) i can't connect to the database include from pgadmin, and i must restart the server and everything back to normal ( im using Win XP as its OS and postgre 8.2) is there any suggetions for me, please?? Thanks regards, Aldy
am Thu, dem 10.07.2008, um 13:57:15 +0700 mailte aldy folgendes: > hi all, First, don't hijack other threads, your mail contains a References-header: References: <20080709114550.7cc8f742@dawn.webthatworks.it> But this message contains to an other thread. In other words: don't answer to an email by deleting the body and create a new subject, create a new mail instead. > i have some problems with my postgresql database server : > i develop some client-server program using postgre as its database (used by > almost 100 client), my problem comes after some periode of time( some times > 1 day) i can't connect to the database include from pgadmin, and i must > restart the server and everything back to normal ( im using Win XP as its > OS and postgre 8.2) > is there any suggetions for me, please?? Maybe the clients do not close the connection and after some time you have more than 'max_connections' (Default 100). You can increase this value in your postgresql.conf. You can also check, how many active connections are open with 'select * from pg_stat_activity'. Hope that helps, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com> Sent: Thursday, July 10, 2008 14:14 >First, don't hijack other threads, your mail contains a >References-header: owkay, i'm sorry for that > Maybe the clients do not close the connection and after some time you > have more than 'max_connections' (Default 100). > > You can increase this value in your postgresql.conf. > > > You can also check, how many active connections are open with 'select * > from pg_stat_activity'. > > > Hope that helps, Andreas thanks for the answer, is there any procedure(utility) or configuration in postgresql which can auto close for connection that idle for some minutes thanks before > am Thu, dem 10.07.2008, um 13:57:15 +0700 mailte aldy folgendes: >> hi all, > > >> i have some problems with my postgresql database server : >> i develop some client-server program using postgre as its database (used >> by >> almost 100 client), my problem comes after some periode of time( some >> times >> 1 day) i can't connect to the database include from pgadmin, and i must >> restart the server and everything back to normal ( im using Win XP as its >> OS and postgre 8.2) >> is there any suggetions for me, please?? >
what is the message error? i have a similar problem whit a software, this software not closed the connection to the server and not reuse the previouslyopen. --- On Thu, 7/10/08, aldy <aldy@teodore.com> wrote: > From: aldy <aldy@teodore.com> > Subject: [SQL] i can't connect after some periode > To: pgsql-sql@postgresql.org > Date: Thursday, July 10, 2008, 6:57 AM > hi all, > i have some problems with my postgresql database server : > i develop some client-server program using postgre as its > database (used by > almost 100 client), my problem comes after some periode of > time( some times > 1 day) i can't connect to the database include from > pgadmin, and i must > restart the server and everything back to normal ( im using > Win XP as its OS > and postgre 8.2) > is there any suggetions for me, please?? > Thanks > > regards, > > Aldy > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
--- On Thu, 7/10/08, aldy <aldy@teodore.com> wrote: > From: aldy <aldy@teodore.com> > Subject: Re: [SQL] i can't connect after some periode > To: pgsql-sql@postgresql.org > Date: Thursday, July 10, 2008, 8:16 AM > From: "A. Kretschmer" > <andreas.kretschmer@schollglas.com> > Sent: Thursday, July 10, 2008 14:14 > > > >First, don't hijack other threads, your mail > contains a > >References-header: > > owkay, i'm sorry for that > > > Maybe the clients do not close the connection and > after some time you > > have more than 'max_connections' (Default > 100). > > > > You can increase this value in your postgresql.conf. > > > > > > You can also check, how many active connections are > open with 'select * > > from pg_stat_activity'. > > > > > > Hope that helps, Andreas > > thanks for the answer, > is there any procedure(utility) or configuration in > postgresql which can > auto close for connection that idle for some minutes > config parameters in postgres.conf tcp_keepalives_idle (integer) tcp_keepalives_interval (integer) tcp_keepalives_count (integer) http://www.postgresql.org/docs/8.3/interactive/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SETTINGS > thanks before > > > > am Thu, dem 10.07.2008, um 13:57:15 +0700 mailte aldy > folgendes: > >> hi all, > > > > > >> i have some problems with my postgresql database > server : > >> i develop some client-server program using postgre > as its database (used > >> by > >> almost 100 client), my problem comes after some > periode of time( some > >> times > >> 1 day) i can't connect to the database include > from pgadmin, and i must > >> restart the server and everything back to normal ( > im using Win XP as its > >> OS and postgre 8.2) > >> is there any suggetions for me, please?? > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
On Thu, Jul 10, 2008 at 8:22 AM, Lennin Caro <lennin.caro@yahoo.com> wrote: > > > > --- On Thu, 7/10/08, aldy <aldy@teodore.com> wrote: > >> From: aldy <aldy@teodore.com> >> Subject: Re: [SQL] i can't connect after some periode >> To: pgsql-sql@postgresql.org >> Date: Thursday, July 10, 2008, 8:16 AM >> From: "A. Kretschmer" >> <andreas.kretschmer@schollglas.com> >> Sent: Thursday, July 10, 2008 14:14 >> >> >> >First, don't hijack other threads, your mail >> contains a >> >References-header: >> >> owkay, i'm sorry for that >> >> > Maybe the clients do not close the connection and >> after some time you >> > have more than 'max_connections' (Default >> 100). >> > >> > You can increase this value in your postgresql.conf. >> > >> > >> > You can also check, how many active connections are >> open with 'select * >> > from pg_stat_activity'. >> > >> > >> > Hope that helps, Andreas >> >> thanks for the answer, >> is there any procedure(utility) or configuration in >> postgresql which can >> auto close for connection that idle for some minutes >> > config parameters in postgres.conf > > tcp_keepalives_idle (integer) > tcp_keepalives_interval (integer) > tcp_keepalives_count (integer) Note that if the client is still up and running, then this will not close the connection. However, if a firewall between client and server is dropping idle connections then this will harvest them. There is no built in functionality to disconnect idle connections that are still alive. You'd have to write some sort of shell script to find them and send them a SIGTERM signal (is that the right signal? I always forget which signal is the right one. Could be SIGHUP or SIGQUIT too. A better option might be to look into pgpool, which can allow you to keep your db connections down while having a large number of fairly cheap connections kept open on the client side.