[SQL] How can I to solute this problem?
I use tomcat+linux_postgresql+jsp to develop system. I start postgresql with 1024 processes. FE: postmaster -i -S -N 1024 -B 2048 -D /var/lib/pgsql/data My jsp doesn't implement connection pool.It directly connects postgresql by jdbc. After I run the Ui some times, the UI(jsp) would report "ieSorry,too many clientslg" .The exception is SQLException. I must restart tomcat or postgresql I can continue to running my UI. May this problem be soluted? Best regards. leon 2003-07-29
Re: [SQL] How can I to solute this problem?
Hi,Thank your help yesterday.
Now I met a new question.
When I continued to clicking UI(jsp) some time, I will get a error in page.It is "Http
404" and Exception is "NullPoinerExcaption".
I checked the log of tomcat. It recorded a message"connected database failed. The user
perporty is missing. It is mandoatory".
netstat | grep postgres |wc -l
The connection number is 278
lsof -i :5432 | wc -l
60
How can I solute this problem?
Is there bug in postgresql's jdbc?
Could you give me help?
Best regards
leon
2003-07-30
- Original Message -
From: "Paul Thomas" <[EMAIL PROTECTED]>
To: "Achilleus Mantzios" <[EMAIL PROTECTED]>
Cc: "LEON" <[EMAIL PROTECTED]>; "pgsql-sql @ postgresql . org" <[EMAIL PROTECTED]>
Sent: Tuesday, July 29, 2003 8:37 PM
Subject: Re: [SQL] How can I to solute this problem?
> On 29/07/2003 17:09 Achilleus Mantzios wrote:
> >
> > You may take a look at jboss connection pool
> > mechanism. (postgresql.xml)
>
> He doesn't mention JBoss, just Tomcat so I don't believe the
> postgresql.xml will help him much. He could use Tomcat's built-in
> connection pooling
> (http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html)
>
> but should note that there is an error in the web.xml fragment:
> jdbc/mydb should be
> jdbc/postgres. Leon, if you need more help
> please feel free to contact me off-list.
>
> >
> > Also a good way of ensuring that your app will
> > not leave open stale postgresql connections
> > is to initially configure your pool to only have a small
> > number of connections (e.g. 2,3).
>
> Good advice for any form of connection pooling.
>
> >
> > If your J2EE components (e.g. jsps) are fast enough
> > you should start facing connection shortages
> > only after the load on your machine increases
> > significantly. (and then safely increase the maximum
> > number of connections in your configuration (both postgresql wise
> > and app server (conn pool) wise).
> >
> >
> > On Tue, 29 Jul 2003, Paul Thomas wrote:
> >
> > >
> > > On 29/07/2003 07:18 LEON wrote:
> > > > I use tomcat+linux_postgresql+jsp to develop system.
> > > >
> > > > I start postgresql with 1024 processes.
> > > > FE:
> > > > postmaster -i -S -N 1024 -B 2048 -D /var/lib/pgsql/data
> > > >
> > > > My jsp doesn't implement connection pool.It directly connects
> > postgresql
> > > > by jdbc.
> > >
> > > IME, that is not a good way to do it. Use a connection pool.
> > >
> > > > After I run the Ui some times, the UI(jsp) would report "ieSorry,too
> > many
> > > > clientslg" .The exception is SQLException.
> > > >
> > > > I must restart tomcat or postgresql I can continue to running my UI.
> > >
> > > My guess would be that you have a bug in your application which is not
> > > always closing the connection so eventually you exceed max_connections.
> >
> > > You should always close the connection in a finally{} block so that is
> > > guaranteed that it will be closed regardless of any earlier exceptions
> > > which are thrown.
> > >
> > > HTH
> > >
> > >
> >
> > --
> > ==
> > Achilleus Mantzios
> > S/W Engineer
> > IT dept
> > Dynacom Tankers Mngmt
> > Nikis 4, Glyfada
> > Athens 16610
> > Greece
> > tel:+30-210-8981112
> > fax:+30-210-8981877
> > email: achill at matrix dot gatewaynet dot com
> > mantzios at softlab dot ece dot ntua dot gr
> >
> >
> > ---(end of broadcast)---
> > TIP 6: Have you searched our list archives?
> >
> >http://archives.postgresql.org
> >
>
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] JDBC encoding problem
I have met the same problem when I deal with chinese.
I guess tomcat and jdbc produced this bug.
But you may solute the charset display problem.
You change the database's charset is useless when you connected DB with JDBC. You
should convert Input Text with UTF-8 before saved in database. When you fetch data
from DATABASE ,you should convert output data with some charset (F.E:GBK) before you
display the data in page.
There is some java code I used.Hope they will help you.
//Output:
OutputStream fileOut = new FileOutputStream(outputFileName);
fileOut.write(data.getBytes("GBK")); //GBK、GB2312、BIG5、UTF8
//Input:
InputStream fileIn = new FileInputStream(inputFileName);
int i = fileIn.read(buff);
String data = new String(buff,"UTF8");
Best regards.
leon
- Original Message -
From: "Kurt Overberg" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, August 06, 2003 1:11 AM
Subject: [SQL] JDBC encoding problem
>
> I'm having a rather strange problem that I'm hoping someone can help me
> with. I'm using Struts 1.0/jsp on Debian linux under Tomcat 4.1.x and
> the blackdown JVM . I'm attempting to convert my current SQL_ASCII
> database to UNICODE. I'm new to this, so am most likely making a few
> mistakes. Here's what I've done so far:
>
> o Converted database encoding to be UNICODE. I'm pretty sure this part
> worked okay. (did a pg_dump, then iconv -f 8859_1 -t UTF-8, then
> created new db with encoding UNICODE and reloaded- no errors upon reload)
>
> sparky:~$ psql -l
> List of databases
> Name| Owner | Encoding
> ---+--+---
> unitest | kurt | UNICODE
> template1 | postgres | SQL_ASCII
> (2 rows)
>
>
> o set client_encoding to 'UTF8';
>
> o In my JSP files, I set the following at the top of each:
>
> <%@ page lanuage="java" pageEncoding="UTF-8" %>
>
>
> Now, to test this, I go to a japanese page, copy some text, then paste
> it into a form, that gets submitted to the server and saved into the DB.
> Then I try to display what I got back from the database. It comes out
> garbled. HOWEVER- if I leave the 'pageEncoding' out of my display .jsp
> file it still comes out garbled, UNTIL I set UTF-8 manually in my
> browsers Character Encoding settings (both mozilla and IE). Then the
> japanese characters render fine (just like I entered them).
>
> Very strange. What's confusing is that when I set the pageEncoding to
> 'UTF-8', the characters don't render properly, and as far as I can tell,
> thats the same as manually setting the browser manually. I must be
> doing something wrong because I get the same results in IE and mozilla
> (recent build).
>
> What may be the problem- I don't do anything differently when getting
> the data out of the database, just standard
> resultset.getString("column"); Do I need to change that call, to handle
> the potentially UTF-8 encoded strings? I can't find anything on that at
> all with google/usenet.
>
> Any and all help, suggestions or pointers would be greatly appreciated.
>
> Thanks!
>
> /kurt
>
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] Recommendation on bytea or blob for binary data like images
Hi, I'd like to know what the official recommendation is on which binary datatype to use for common small-binary size use. I'm working with the Open For Business (www.ofbiz.org) framework, which by default maps binary data, such as shipping label images, into OID field types. In general, the data is far less than a gigabyte in size. Is it strongly recommended by the postgresql community to store things like this in bytea format as of 8.0? Thanks, Leon Torres [EMAIL PROTECTED] Open Source Strategies http://opensourcestrategies.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Consecutive row count query
Hi, I wondered if anyone could answer the following question: If I have a table such as the one below: col1 col_order --- Apple 1 Apple 2 Orange 3 Banana 4 Apple 5 Is there a way I can get the following results: Apple 2 Orange 1 Banana 1 Apple 1 i.e. Each row is printed ordered by col_order but consecutive appearances of the same col1 result in only a single line in the result with the number of consecutive appearances. Obviously I could store the table as: col1 col_order col_count -- Apple 1 2 Orange 2 1 Banana 3 1 Apple 4 1 But since (in my intended table) most rows will have col_count = 1, this seems like unnecessary normalization (and semantically "wrong"). Thanks in advance for any help, Leon Stringer ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Consecutive row count query
> I wondered if anyone could answer the following question: Thanks for your responses, I think I'll just add the extra column in as Greg suggests. (BTW: I mean "unnecessary *denormalization*" which I hope is less odd!). Leon... ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Recommendation on bytea or blob for binary data like images
Thanks for the quick response. We will be using bytea from now on. :-) - Leon Tom Lane wrote: [EMAIL PROTECTED] writes: Hi, I'd like to know what the official recommendation is on which binary datatype to use for common small-binary size use. If bytea will work for you, it's definitely the thing to use. The only real drawback to bytea is that there's currently no API to read and write bytea values in a streaming fashion. If your objects are small enough that you can load and store them as units, bytea is fine. BLOBs, on the other hand, have a number of drawbacks --- hard to dump, impossible to secure, etc. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] week ending
On 7/5/06, Keith Worthington <[EMAIL PROTECTED]> wrote: Hi All, I just finished writing a query that groups data based on the week number. SELECT EXTRACT(week FROM col_a) AS week_number, sum(col_b) AS col_b_total FROM foo WHERE foobar GROUP BY EXTRACT(week FROM col_a) ORDER BY EXTRACT(week FROM col_a); I would like to generate the starting date or ending date based on this number. IOW instead of telling the user "week number" which they won't understand I would like to provide either Friday's date for "week ending" or Monday's date for "week beginning". SELECT AS week_ending, sum(col_b) AS col_b_total FROM foo WHERE foobar GROUP BY EXTRACT(week FROM col_a) ORDER BY EXTRACT(week FROM col_a); -- Kind Regards, Keith select max(case when (to_char(col_a,'d') between 2 and 6) then col_a end) as week_ending, sum(col_b) as col_b_total from foo where foobar group by extract(year from col_a), extract(week from col_a) order by extract(year from col_a), extract(week from col_a); Regards, Rodrigo ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Select Maths
On 7/7/06, Phillip Smith <[EMAIL PROTECTED]> wrote: Hi again, G'day (it's 03:21 on a friday here). Same SELECT query as before, different area of it… I have a function that calculates the recommended purchase order quantity for a stock item based off various other values and functions: pqty(stock.code) AS "pqty" This needs to be rounded up / down to the nearest multiple of the purchase unit quantity for that product – It's Friday afternoon and my head has refused to help me work out the maths all afternoon! Example: Pqty = 60 Purchase Unit = 25 Pqty needs to be rounded down to 50. create or replace function roundupdown(pqty int, punit int) returns int as $$ select ((case when $1<$2 then $2 else $1 end)/$2::float)::int*$2; $$ language 'sql'; -- test select roundupdown(s.x,25) as pqty , (s.x/25::float) as near from generate_series(1,100) s(x); I guess I'm also asking if I should do this in the Pqty function or in the SELECT query to optimize the result? Whatever suits your usage pattern, I guess. Thanks all – Enjoy your weekends I hope! Cheers, -p Same to you. Regards, Rodrigo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] SELECT substring with regex
On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote: I would like to split the contents of a column using substring with a regular expression: SELECT substring (NAME, '^\\d+mm') as BASE_NAME, substring (NAME, ??? ) as SUFFIX FROM MODEL The column contains something like "150mm LD AD Asp XR Macro" I want to split this into "150mm", "LD AD Asp XR Macro" How can I extract the bit following the matching substring? -- Regards, Tarlika Elisabeth Schmitz I'm sure there's a cleaner, regexp based approach, but how about: SELECT substring (NAME, '^\\d+mm') AS BASE_NAME , substr( NAME , char_length( substring (NAME, '^\\d+mm') ) + 2 ) AS SUFFIX FROM MODEL Regards, Rodrigo ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] SELECT substring with regex
On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote:
But that takes me to the next problem:
For the sake of the example I simplified the regular pattern.
In reality, BASE_NAME might be:
28mm
28-70mm
So the reg. expr. requires brackets:
substring (NAME, '^(\\d+(-\\d+)?mm)' ) as BASE_NAME
Actually, the pattern is more complex than that and I cannot see how I
can express it without brackets.
Maybe:
select
substring ('150mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME
, substring('150mm LD AD Asp XR Macro','^[\\d-]*mm (.*)$') as SUFFIX;
select
substring ('28-70mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME
, substring('28-70mm LD AD Asp XR Macro','^[\\d-]*mm (.*)$') as SUFFIX;
etc...
Regards,
Rodrigo
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] SELECT substring with regex
On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote: Sorry, but that would also capture something like 10-30-59mm The pattern describes either a single length (120 millimeters) or a range (30 to 70 millimetres), hence: \\d+(-\\d+)?mm The ? quantifier refers to the combination of '-' and digits and has to be bracketed. ... Still, I'd be interested to know whether there is a 'more elegant' solution. OK, last try using regex: SELECT NAME , substring(NAME, '^(\\d+(-\\d+)?mm)') AS BASE_NAME , regexp_replace(NAME, '^\\d+(-\\d+)?mm (.*)', '\\2') AS SUFFIX FROM MODEL Regards, Rodrigo ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SELECT substring with regex
On 7/8/06, T E Schmitz <[EMAIL PROTECTED]> wrote: Is regexp_replace a new feature? I am running v 7.4. Given the patch history: http://archives.postgresql.org/pgsql-patches/2004-07/msg00471.php http://archives.postgresql.org/pgsql-patches/2005-06/msg00515.php http://archives.postgresql.org/pgsql-patches/2005-07/msg00260.php And the release notes: http://www.postgresql.org/docs/8.1/static/release.html I'd say at least: 7.4.9 8.0.4 8.1 Anyone care to verify this? Regards, Rodrigo ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] CREATE TABLE AS inside of a function
On 7/21/06, Kevin Nikiforuk <[EMAIL PROTECTED]> wrote: So now that I've got my loops working, on to my next newbie question. I've created my function and in it, I want to loop through the results of a select and for each value of my loop counter, I want to create a new table, but I can't figure out how to use a variable in the name of the new table, see below. CREATE OR REPLACE FUNCTION rgio() RETURNS INTEGER as $$ DECLARE lv RECORD; BEGIN FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP CREATE TABLE rgio_$lv AS SELECT ldev FROM ldevrg WHERE rg='$lv'; END LOOP; RETURN 1; END; $$ LANGUAGE plpgsql; Thanks, Kevin See: http://www.postgresql.org/docs/8.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Regards, Rodrigo ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] viewing the description of tables from python DB-API
On 8/1/06, Daniel Joo <[EMAIL PROTECTED]> wrote: Hi all, Is there a way to view the list of all tables from python (or any other languages for that matter) DB-API? What I'm looking for is a command similar to the meta-command '\d' that works with the psql client. Thanks very much! Dan There's also the INFORMATION_SCHEMA: http://www.postgresql.org/docs/8.1/static/information-schema.html Regards, Rodrigo ---(end of broadcast)--- TIP 6: explain analyze is your friend
