Re: [GENERAL] Is there any method to limit resource usage in PG?

2013-08-25 Thread John R Pierce
On 8/25/2013 11:08 PM, 高健 wrote: That program is written by Java. It is to use JDBC to pull out data from DB, while the query joined some table together, It will return about 3000,000 records. Then the program will use JDBC again to write the records row by row , to inert into another table

[GENERAL] Is there any method to limit resource usage in PG?

2013-08-25 Thread 高健
Hello: Sorry for disturbing. I am now encountering a serious problem： memory is not enough. My customer reported that when they run a program they found the totall memory and disk i/o usage all reached to threshold value(80%). That program is written by Java. It is to use JDBC to pull out data

Re: [GENERAL] What is the relationship between checkpoint and wal

2013-08-25 Thread 高健
Hi : Thanks to Alvaro! Sorry for replying lately. I have understood a little about it. But the description of full_page_write made me even confused. Sorry that maybe I go to another problem: It is said: http://www.postgresql.org/docs/9.2/static/runtime-config-wal.html#GUC-FULL-PAGE-WRITES -

Re: [GENERAL] how to use aggregate functions in this case

2013-08-25 Thread BladeOfLight16
On Sun, Aug 25, 2013 at 8:36 PM, BladeOfLight16 wrote: > This appears to be some kind of equal interval problem. > > SELECT v_rec1.user, > WIDTH_BUCKET(v_rec_fts.lev, 0, 100, 4) AS bucket > COUNT(*) as count, > FROM v_rec2 > GROUP BY user, bucket; > > (Untested, but this should be

Re: [GENERAL] batch insertion

2013-08-25 Thread Merlin Moncure
On Sat, Aug 24, 2013 at 7:15 PM, Korisk wrote: > Hi! > I want quick insert into db a lot of data (in form of triplets). Data is > formed dynamical so "COPY" is not suitable. > I tried batch insert like this: > > insert into triplets values (1,1,1); > insert into triplets values (1,1,1), (3,2,5),

Re: [GENERAL] how to use aggregate functions in this case

2013-08-25 Thread BladeOfLight16
On Sun, Aug 25, 2013 at 5:59 PM, Janek Sendrowski wrote: > SELECT v_rec1.user, > sum(CASE WHEN v_rec_fts.lev BETWEEN 0 AND 25 THEN 1 ELSE 0 END) as > "0 to 25", > sum(CASE WHEN v_rec_fts.lev BETWEEN 25 AND 50 THEN 1 ELSE 0 END) > as "25 to 50", > sum(CASE WHEN v_rec_fts.l

Re: [GENERAL] how to use aggregate functions in this case

2013-08-25 Thread David Johnston
Janek Sendrowski wrote > Sorry, I formulated it wrong. > > My problem is, that I want to count the ranges for every user, but if I > use count(range), it counts the ranges of all users. Assuming your example output is indeed what you desire: SELECT user, '0 to 25'::varchar AS percentage, count

Re: [GENERAL] how to use aggregate functions in this case

2013-08-25 Thread Janek Sendrowski
Sorry, I formulated it wrong. My problem is, that I want to count the ranges for every user, but if I use count(range), it counts the ranges of all users.   Janek Sendrowski

Re: [GENERAL] how to use aggregate functions in this case

2013-08-25 Thread David Johnston
Janek Sendrowski wrote > Hi, > > Thats my code snipped: >   > SELECT  v_rec1.user, >        sum(CASE WHEN v_rec_fts.lev BETWEEN 0 AND 25 THEN 1 ELSE 0 END) as > "0 to 25", >         sum(CASE WHEN v_rec_fts.lev BETWEEN 25 AND 50 THEN 1 ELSE 0 END) > as "25 to 50", > sum(CASE WHEN v_rec_fts

[GENERAL] how to use aggregate functions in this case

2013-08-25 Thread Janek Sendrowski
Hi, Thats my code snipped:   SELECT  v_rec1.user,        sum(CASE WHEN v_rec_fts.lev BETWEEN 0 AND 25 THEN 1 ELSE 0 END) as "0 to 25",         sum(CASE WHEN v_rec_fts.lev BETWEEN 25 AND 50 THEN 1 ELSE 0 END) as "25 to 50", sum(CASE WHEN v_rec_fts.lev BETWEEN 50 AND 100 THEN 1 ELSE 0 END