[SQL] i can't connect after some periode
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
Re: [SQL] i can't connect after some periode
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: <[EMAIL PROTECTED]> 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] i can't connect after some periode
From: "A. Kretschmer" <[EMAIL PROTECTED]> 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?? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Design and Question
Hi,
I have BOTH a sql AND db design question.
I'm creating a cookbook DB with have broken the table into this:
RECIPE TABLE
Column | Type | Modifiers
---+--+--
id | integer | not null default nextval('recipes_id_seq'::regclass)
title | character varying(150) | not null
description | text | not null
servings | integer |
instruction | text | not null
photo | character varying(100) | not null
difficulty | integer |
cuisine | smallint |
course | smallint |
season | smallint |
dietary | smallint |
technique | smallint |
published_date | timestamp with time zone | not null
publishing_rights | boolean | not null
credits | character varying(100) | not null
approved | boolean | default false
cooktime | integer |
preptime | integer |
and this:
RECIPE DIET INFO TABLE
Column | Type | Modifiers
---+--+---
id | integer | not null default nextval('recipes_diet_id_seq'::regclass)
recipe_id | integer | not null
diet | character varying(1) |
RECIPE SEASON TABLE
Column | Type | Modifiers
---+--+-
id | integer | not null default nextval('recipes_season_id_seq'::regclass)
recipe_id | integer | not null
season | character varying(1) |
I can perform is query ->
select title from recipes where id in (select recipe_id from
recipes_season where season in ('P', 'W'));
title
---
ButterFlied Chicken Fillets with Lime
Balsamic Vinegar Chicken with Beans
(2 rows)
select title from recipes where id in (select recipe_id from
recipes_diet where diet in ('P'));
title
---
ButterFlied Chicken Fillets with Lime
How do I combine the two in a query?
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Problem in dynamic query execution in plpgsql
Hai all, I Have a problem,I have a plpgsql function == CREATE OR REPLACE FUNCTION function_to_get_ticket_wise_sales(VARCHAR,VARCHAR) RETURNS SETOF RECORD AS ' DECLARE dat_from_date ALIAS FOR $1; dat_to_date ALIAS FOR $2; dat_from DATE; dat_to DATE; vchr_query VARCHAR(1000); r RECORD; BEGIN dat_from := to_date(dat_from_date, ''DD MM ''); dat_to := to_date(dat_to_date,''DD MM ''); vchr_query := ''SELECT vchr_ticket_number,dat_ticket_issue,vchr_pax_name,vchr_sector, dbl_market_fare_inv, dbl_special_fare_inv,dbl_std_commn_percentage_inv,vchr_our_lpo, dbl_market_fare_inv AS flt_claim,dbl_supplier_amount,dbl_service_charge,dbl_selling_price,vchr_inv_document_number,fk_bint_supplier_id,chr_supplier_type, vchr_airline_numeric_code, vchr_account_code_inv , vchr_account_name_inv FROM tbl_ticket WHERE dat_ticket_issue BETWEEN ''|| dat_from || '' AND '' || dat_to || '' ; RAISE NOTICE ''Query : % '',vchr_query; vchr_query := vchr_query || '' AND (vchr_our_lpo = '' '' OR vchr_our_lpo = "VS") ''; FOR r in EXECUTE vchr_query LOOP RETURN NEXT r; END LOOP; RETURN; END ' language 'plpgsql'; == my problems are: 1 problem : in RAISE NOTICE query string is print like this, SELECT vchr_ticket_number,dat_ticket_issue,vchr_pax_name,vchr_sector, dbl_market_fare_inv, dbl_special_fare_inv,dbl_std_commn_percentage_inv,vchr_our_lpo, dbl_market_fare_inv AS flt_claim,dbl_supplier_amount,dbl_service_charge,dbl_selling_price,vchr_inv_document_number,fk_bint_supplier_id,chr_supplier_type, vchr_airline_numeric_code, vchr_account_code_inv , vchr_account_name_inv FROM tbl_ticket WHERE dat_ticket_issue BETWEEN 2008-04-01 AND 2008-07-10 when $1 = '2008-04-01' and $2 = '2008-04-10' , but i dont get the required result. I think that i will get the result if my query string will be like this(ie dates in single quote), : SELECT vchr_ticket_number,dat_ticket_issue,vchr_pax_name,vchr_sector, dbl_market_fare_inv, dbl_special_fare_inv,dbl_std_commn_percentage_inv,vchr_our_lpo, dbl_market_fare_inv AS flt_claim,dbl_supplier_amount,dbl_service_charge,dbl_selling_price,vchr_inv_document_number,fk_bint_supplier_id,chr_supplier_type, vchr_airline_numeric_code, vchr_account_code_inv , vchr_account_name_inv FROM tbl_ticket WHERE dat_ticket_issue BETWEEN '2008-04-01' AND '2008-07-10 ' How i can put the dates in single quote in a dynamic query string? 2 problem: next problem is i have a varchar variable vchr_our_lpo how I can check is it containn an empty string or characters in a dynamic query string I tried different methods like, vchr_query :='' (vchr_our_lpo = '' '' OR vchr_our_lpo = "VS") ''; str_temp2:= ''VS''; vchr_query := '' (vchr_our_lpo = '' '' OR vchr_our_lpo = %) '',str_temp2; but all failed How I can solve these problem in a dynamic query string?.pls help me with a suitable example thanks in advance: Anoop G
[SQL] record type
Hi. I need to know whether it's possible for a plpgsql function to accept
record type parameters ? Is there a way to accomplish that ?
I need to use something like ('1','2','3') as a parameter.
regards
mk
Re: [SQL] Problem in dynamic query execution in plpgsql
Hello why you do use dynamic query? your function is little bit ugly a) create or replace function ..(date_from date, date_to date) returns setof record as $$ declare r record; begin for r in select .. from tbl_ticket where dat_ticket_issue between date_from and date_to loop ... b) for single quoting use function quote_literal postgres=# select '>>>'||quote_literal(current_date)||'<<<'; ?column? >>>'2008-07-10'<<< (1 row) Regards Pavel Stehule 2008/7/10 Anoop G <[EMAIL PROTECTED]>: > Hai all, > I Have a problem,I have a plpgsql function > == > > CREATE OR REPLACE FUNCTION > function_to_get_ticket_wise_sales(VARCHAR,VARCHAR) RETURNS SETOF RECORD AS ' > > DECLARE > > dat_from_date ALIAS FOR $1; > > dat_to_date ALIAS FOR $2; > > dat_from DATE; > > dat_to DATE; > > vchr_query VARCHAR(1000); > > r RECORD; > > BEGIN > > dat_from := to_date(dat_from_date, ''DD MM ''); > > dat_to := to_date(dat_to_date,''DD MM ''); > > vchr_query := ''SELECT > vchr_ticket_number,dat_ticket_issue,vchr_pax_name,vchr_sector, > > dbl_market_fare_inv, > dbl_special_fare_inv,dbl_std_commn_percentage_inv,vchr_our_lpo, > > dbl_market_fare_inv AS > flt_claim,dbl_supplier_amount,dbl_service_charge,dbl_selling_price,vchr_inv_document_number,fk_bint_supplier_id,chr_supplier_type, > vchr_airline_numeric_code, vchr_account_code_inv , vchr_account_name_inv > FROM tbl_ticket WHERE dat_ticket_issue BETWEEN ''|| dat_from || '' AND '' || > dat_to || '' ; > > RAISE NOTICE ''Query : % '',vchr_query; > > vchr_query := vchr_query || '' AND (vchr_our_lpo = '' '' OR vchr_our_lpo = > "VS") ''; > > FOR r in EXECUTE vchr_query LOOP > > RETURN NEXT r; > > END LOOP; > > RETURN; > > END > > ' language 'plpgsql'; > > == > > my problems are: > > 1 problem : in RAISE NOTICE query string is print like this, > > SELECT vchr_ticket_number,dat_ticket_issue,vchr_pax_name,vchr_sector, > > dbl_market_fare_inv, > dbl_special_fare_inv,dbl_std_commn_percentage_inv,vchr_our_lpo, > > dbl_market_fare_inv AS > flt_claim,dbl_supplier_amount,dbl_service_charge,dbl_selling_price,vchr_inv_document_number,fk_bint_supplier_id,chr_supplier_type, > vchr_airline_numeric_code, vchr_account_code_inv , vchr_account_name_inv > FROM tbl_ticket WHERE dat_ticket_issue BETWEEN 2008-04-01 AND 2008-07-10 > > when $1 = '2008-04-01' and $2 = '2008-04-10' , but i dont get the required > result. > > I think that i will get the result if my query string will be like this(ie > dates in single quote), > > : > > SELECT vchr_ticket_number,dat_ticket_issue,vchr_pax_name,vchr_sector, > > dbl_market_fare_inv, > dbl_special_fare_inv,dbl_std_commn_percentage_inv,vchr_our_lpo, > > dbl_market_fare_inv AS > flt_claim,dbl_supplier_amount,dbl_service_charge,dbl_selling_price,vchr_inv_document_number,fk_bint_supplier_id,chr_supplier_type, > vchr_airline_numeric_code, vchr_account_code_inv , vchr_account_name_inv > FROM tbl_ticket WHERE dat_ticket_issue BETWEEN '2008-04-01' AND '2008-07-10 > ' > > How i can put the dates in single quote in a dynamic query string? > > 2 problem: > > next problem is i have a varchar variable vchr_our_lpo how I can check is it > containn an empty string or characters in a dynamic query string > > I tried different methods like, > > vchr_query :='' (vchr_our_lpo = '' '' OR vchr_our_lpo = "VS") ''; > > str_temp2:= ''VS''; > > vchr_query := '' (vchr_our_lpo = '' '' OR vchr_our_lpo = %) '',str_temp2; > > but all failed > > How I can solve these problem in a dynamic query string?.pls help me with a > suitable example > > thanks in advance: > > Anoop G > > > > > > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem in dynamic query execution in plpgsql
am Thu, dem 10.07.2008, um 18:25:38 +0530 mailte Anoop G folgendes:
> my problems are:
>
> 1 problem : in RAISE NOTICE query string is print like this,
>
> How i can put the dates in single quote in a dynamic query string?
Use more quotes *g*:
Example:
test=*# create or replace function my_foo(text) returns int as '
declare s text;
begin s:=''select '' || $1 || '' as ...'';
raise notice ''%'',s;
return 1;
end'
language 'plpgsql';
CREATE FUNCTION
test=*# select * from my_foo('2008-01-01');
NOTICE: select '2008-01-01' as ...
my_foo
1
(1 row)
Better solution: use $-Quoting, example:
test=*# create or replace function my_foo(text) returns int as $$
declare s text;
begin s:='select ''' || $1 || ''' as ...';
raise notice '%',s;
return 1;
end$$
language 'plpgsql';
CREATE FUNCTION
test=*# select * from my_foo('2008-01-01');
NOTICE: select '2008-01-01' as ...
my_foo
1
(1 row)
As you can see, same result but easier to read.
>
>
>
> 2 problem:
>
> next problem is i have a varchar variable vchr_our_lpo how I can check is it
> containn an empty string or characters in a dynamic query string
Use coalesce(), example:
test=*# select 'foo' || NULL || 'bar';
?column?
--
(1 row)
test=*# select 'foo' || coalesce(NULL,' empty string ') || 'bar';
?column?
--
foo empty string bar
(1 row)
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] i can't connect after some periode
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 previously open. --- On Thu, 7/10/08, aldy <[EMAIL PROTECTED]> wrote: > From: aldy <[EMAIL PROTECTED]> > 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] i can't connect after some periode
--- On Thu, 7/10/08, aldy <[EMAIL PROTECTED]> wrote: > From: aldy <[EMAIL PROTECTED]> > 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" > <[EMAIL PROTECTED]> > 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] i can't connect after some periode
On Thu, Jul 10, 2008 at 8:22 AM, Lennin Caro <[EMAIL PROTECTED]> wrote: > > > > --- On Thu, 7/10/08, aldy <[EMAIL PROTECTED]> wrote: > >> From: aldy <[EMAIL PROTECTED]> >> 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" >> <[EMAIL PROTECTED]> >> 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Converting Copy to insert statement in backup file
Hello, I have a backup cron job (shown below) but its too big and there are times that I want to just cut out from the text file certain areas to restore data in a specific table. Looking in the file, I notice there is a "copy" command.. Someone told me that there was a parameter that I could use to convert the copy to insert so I could easily cut it out of the file and run it on its own ..Any ideas su -c '/usr/bin/pg_dump -U postgres -o -C ttms |gzip > /home/hrd/ttmsqltrams/sqlbackup.gz' postgres
Re: [SQL] Converting Copy to insert statement in backup file
On Thu, Jul 10, 2008 at 9:40 AM, Chris Preston <[EMAIL PROTECTED]> wrote: > Hello, > > I have a backup cron job (shown below) but its too big and there are times > that I want to just cut out from the text file certain areas to restore data > in a specific table… Looking in the file, I notice there is a "copy" > command.. Someone told me that there was a parameter that I could use to > convert the copy to insert so I could easily cut it out of the file and run > it on its own ..Any ideas pg_dump --help says: -d, --inserts dump data as INSERT commands, rather than COPY -D, --column-insertsdump data as INSERT commands with column names So one of those two commands will output insert commands instead of copy commands. note that insert commands tend to run a bit more slowly, even if you enclose them in a transaction. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Design and Question
On 10 jul 2008, at 14.50, PostgreSQL Admin wrote:
How do I combine the two in a query?
If you're looking for recipes that match *either* criterion (season
*or* diet), you could add the two subqueries generating the ids using
UNION or UNION ALL:
SELECT title FROM recipes WHERE id IN (
SELECT recipe_id FROM recipes_season WHERE season IN ('P', 'W')
UNION [ALL]
SELECT recipe_id FROM recipes_diet WHERE diet IN ('P')
);
or, you could use joins:
SELECT title FROM recipes r
LEFT JOIN recipes_season rs ON r.id=rs.recipe_id
LEFT JOIN recipes_diet rd ON r.id=rd.recipe_id
WHERE rs.season IN ('P', 'W') OR rd.diet IN ('P');
If, on the other hand, you're looking for recipes that match *both*
criteria, use:
SELECT title FROM recipes WHERE id IN (SELECT recipe_id FROM
recipes_season WHERE season IN ('P', 'W'))
AND id IN (SELECT recipe_id FROM recipes_diet WHERE diet IN ('P'));
or:
SELECT title FROM recipes r
INNER JOIN recipes_season rs ON r.id=rs.recipe_id
INNER JOIN recipes_diet rd ON r.id=rd.recipe_id
WHERE rs.season IN ('P', 'W') AND rd.diet IN ('P');
The optimal execution plan will be dependent on the size and
distribution of your data, so you should test the queries with real
data.
Sincerely,
Niklas Johansson
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] record type
Or maybe anyone knows how to work with record types ? How to insert
something like ('1','2','3') into a table, or split it ? Anything ?
regards
mk
