Re: [SQL] Last insert id
"Is it safe to use "select max(table1_id) from table1" after the insert?" Yes it is safe, but ONLY if you use it inside a transaction.(BEGIN/COMMIT). BR. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of mixo Sent: Wednesday, June 09, 2004 9:24 AM To: [EMAIL PROTECTED] Subject: [SQL] Last insert id I have three tables which are related a serial field, table1_id, in on of the tables. Updating the tables is done through a transaction. My problem is, once I have insert a row in the first tables with table1_id, I need for the other two tables. How can I get this? Is it safe to use "select max(table1_id) from table1" after the insert? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Last insert id
On Tue, 2004-06-15 at 03:05, Andrei Bintintan wrote: > "Is it safe to use "select max(table1_id) from table1" after the insert?" > > Yes it is safe, but ONLY if you use it inside a transaction.(BEGIN/COMMIT). No, this is not safe outside of the serializable isolation. rbt=# begin; BEGIN rbt=# select max(id) from l; max - 1 (1 row) rbt=# insert into l values (2); INSERT 62597 1 rbt=# select max(id) from l; max - 3 (1 row) rbt=# commit; COMMIT I inserted 3 from another connection after 2 was inserted. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Prepare Statement
Tom, Does java.sql.PreparedStatement do the same thing as SQL command prepare/execute ?? Which one should be used while I am using jdbc talking to postgresql? If using both, do them help the performance? Thanks. Jie Liang -Original Message- From: Jie Liang Sent: Monday, June 14, 2004 4:33 PM To: Tom Lane Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: [SQL] Prepare Statement Hi, I have a question about performance, in SQL commands: there is a prepare/execute command, document says it will improve the performance while repeatly execute a statement. In java.sql: there is a PreparedStatement object, which can store precompiled SQL statement, document says it can improve the performance also. If I use java jdbc to connect postgresql database, which one I should use? Can I use both? Thanks. Jie Liang ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Is there a faster way to do this?
pg: 7.4.2 RedHat 7.2 Can I get some advice on a possible faster way of doing this: Scenario: Each day I update a column in a table with an internal percentile value. To arrive at this value, I'll get a count of records with sales > 0 and then divide this count by the total number of tile groups I want. So for example: Total records w/sales > 0 = 730,000 tile# = 100 total percentile groups (730,000 / 100):7300 Now that I have the total number of groups I need, I cycle through my recordset, grab the key field and the current percentile number and stuff the values into a temp table. I mod the current row counter against the total percentile group number. If it is 0 then I add 1 to the current percentile number . After inserting records into the temp file I then update the main table. Using the example above, the first 7300 records would get a percentile number of 1, the next 7300 records would get a percentile number of 2, then 3,4,5,etc. Unfortunately, I am going record by record in a loop and the process takes upwards of 20mins. Is there a faster way to do this? I thought about using limit and offset, but I'm not sure how I would accomplish it. Below is the function I currently use. Thanks for any help provided CREATE OR REPLACE FUNCTION cdm.percentile_calc() RETURNS text AS 'DECLARE v_interval int4; v_tile int4; v_percentile int4; v_check int4; v_count int4; v_rowcount int4; myRec RECORD; BEGIN v_count:=0; v_tile:= 100; v_percentile:=1; v_rowcount :=1; v_check:=0; /* Get count of records with val_purch > 0 */ select into v_count count(*) from cdm.cdm_indiv_mast where val_purch_com >0; /* this number will be used as part of our MOD to tell when to add one to our percentile */ v_interval := v_count / v_tile; CREATE TEMP TABLE cdmperct (f1 int8, f2 int2); FOR myRec IN select indiv_key from cdm.cdm_indiv_mast where val_purch_com >0 order by val_purch_com desc LOOP INSERT INTO cdmperct values (myRec.indiv_key,v_percentile); v_check = mod(v_rowcount,v_interval); IF v_check = 0 THEN v_percentile:=v_percentile+1; END IF; v_rowcount:= v_rowcount+1; END LOOP; UPDATE cdm.cdm_indiv_mast SET percentiler = f2 from cdmperct where indiv_key = f1; DROP TABLE cdmperct; RETURN \'DONE\'; END; ' LANGUAGE 'plpgsql' IMMUTABLE; Patrick Hatcher Macys.Com ---(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] Is there a faster way to do this?
Perhaps something along the lines of
CREATE TEMP SEQUENCE pctile_seq;
SELECT ... , ((nextval('pctile_seq')/(rec_per_group+1) +1)
FROM (SELECT ... WHERE sales > 0 ORDER BY SALES) sales;
DROP TEMP SEQUENCE pctile_seq;
This doesn't add in the 'sales = 0 are in percentile 0, but that
shouldn't be too hard. I think, with some playing around, one might be
able to to an UPDATE from the above SELECT, which will eliminate the
read/update loop.
Patrick Hatcher wrote:
pg: 7.4.2
RedHat 7.2
Can I get some advice on a possible faster way of doing this:
Scenario: Each day I update a column in a table with an internal
percentile value. To arrive at this value, I'll get a count of records
with sales > 0 and then divide this count by the total number of tile
groups I want. So for example:
Total records w/sales > 0 = 730,000
tile# = 100
total percentile groups (730,000 / 100):7300
Now that I have the total number of groups I need, I cycle through my
recordset, grab the key field and the current percentile number and stuff
the values into a temp table. I mod the current row counter against the
total percentile group number. If it is 0 then I add 1 to the current
percentile number . After inserting records into the temp file I then
update the main table.
Using the example above, the first 7300 records would get a percentile
number of 1, the next 7300 records would get a percentile number of 2,
then 3,4,5,etc.
Unfortunately, I am going record by record in a loop and the process takes
upwards of 20mins. Is there a faster way to do this? I thought about
using limit and offset, but I'm not sure how I would accomplish it.
Below is the function I currently use. Thanks for any help provided
CREATE OR REPLACE FUNCTION cdm.percentile_calc()
RETURNS text AS
'DECLARE
v_interval int4;
v_tile int4;
v_percentile int4;
v_check int4;
v_count int4;
v_rowcount int4;
myRec RECORD;
BEGIN
v_count:=0;
v_tile:= 100;
v_percentile:=1;
v_rowcount :=1;
v_check:=0;
/* Get count of records with val_purch > 0 */
select into v_count count(*) from cdm.cdm_indiv_mast where
val_purch_com >0;
/* this number will be used as part of our MOD to tell when to add one
to our percentile */
v_interval := v_count / v_tile;
CREATE TEMP TABLE cdmperct (f1 int8, f2 int2);
FOR myRec IN select indiv_key from cdm.cdm_indiv_mast where
val_purch_com >0 order by val_purch_com desc LOOP
INSERT INTO cdmperct values (myRec.indiv_key,v_percentile);
v_check = mod(v_rowcount,v_interval);
IF v_check = 0 THEN
v_percentile:=v_percentile+1;
END IF;
v_rowcount:= v_rowcount+1;
END LOOP;
UPDATE cdm.cdm_indiv_mast SET percentiler = f2 from cdmperct where
indiv_key = f1;
DROP TABLE cdmperct;
RETURN \'DONE\';
END; '
LANGUAGE 'plpgsql' IMMUTABLE;
Patrick Hatcher
Macys.Com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
--
Edmund Bacon <[EMAIL PROTECTED]>
---(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] Is there a faster way to do this?
Patrick Hatcher wrote: Scenario: Each day I update a column in a table with an internal percentile value. To arrive at this value, I'll get a count of records with sales > 0 and then divide this count by the total number of tile groups I want. So for example: Total records w/sales > 0 = 730,000 tile# = 100 total percentile groups (730,000 / 100):7300 Now that I have the total number of groups I need, I cycle through my recordset, grab the key field and the current percentile number and stuff the values into a temp table. I mod the current row counter against the total percentile group number. If it is 0 then I add 1 to the current percentile number . After inserting records into the temp file I then update the main table. Using the example above, the first 7300 records would get a percentile number of 1, the next 7300 records would get a percentile number of 2, then 3,4,5,etc. Unfortunately, I am going record by record in a loop and the process takes upwards of 20mins. Is there a faster way to do this? I thought about using limit and offset, but I'm not sure how I would accomplish it. Idea #1: Well, if you ordered by something unique, you could use ORDER BY, LIMIT and OFFSET UPDATE my_table SET my_group=1 WHERE val > 0 ORDER BY val, my_id_column OFFSET 0 LIMIT 7300; UPDATE my_table SET my_group=2 WHERE val > 0 ORDER BY val, my_id_column OFFSET 7300 LIMIT 7300; etc. You'll need to make sure the order is guaranteed for all rows when "val" is the same though. Now, the problem with this is that by the time you get to group 99, you've had to scan over group 1 98 times - not good. If you knew how many rows there were for any given "val" then you could restrict it much more though. Idea #2: Turn your function around. Declare a cursor on the sorted SELECT of target-rows. SKIP 7300 rows and read the primary-key. Use that to issue an update of all rows between two values. That's only 100 updates issued rather than one for each target row. Idea #3: Someone might well be able to come up with a clever idea involving a join against a set-returning function, but I'm not sure about guaranteeing the order of the join vs the returned set (and it's getting late here). Any ideas people? Maybe one of those is some use -- Richard Huxton Archonet Ltd ---(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] Is there a faster way to do this?
One way to do it would be to: Not put the percentile in the sales table; Create an percentile table with a foreign key to the sales table primary key and percentile int4: CREATE TABLE percentiles( fkey PRIMARY KEY REFERENCES sales( ), percentile INT4 ); Create a sequence for that ancillary table: CREATE SEQUENCE percent_seq; When ready to create the percentiles, truncate the percentile table and reset the sequence next value: TRUNCATE percentiles; setval( 'percent_seq', 0); Now query your sales with the proper order by: INSERT INTO percentiles SELECT pk_sales, nextval( 'percent_seq') / tile_size FROM sales ORDER BY sales_value; HTH Patrick Hatcher wrote: pg: 7.4.2 RedHat 7.2 Can I get some advice on a possible faster way of doing this: Scenario: Each day I update a column in a table with an internal percentile value. To arrive at this value, I'll get a count of records with sales > 0 and then divide this count by the total number of tile groups I want. So for example: Total records w/sales > 0 = 730,000 tile# = 100 total percentile groups (730,000 / 100):7300 Now that I have the total number of groups I need, I cycle through my recordset, grab the key field and the current percentile number and stuff the values into a temp table. I mod the current row counter against the total percentile group number. If it is 0 then I add 1 to the current percentile number . After inserting records into the temp file I then update the main table. Using the example above, the first 7300 records would get a percentile number of 1, the next 7300 records would get a percentile number of 2, then 3,4,5,etc. Unfortunately, I am going record by record in a loop and the process takes upwards of 20mins. Is there a faster way to do this? I thought about using limit and offset, but I'm not sure how I would accomplish it. Below is the function I currently use. Thanks for any help provided CREATE OR REPLACE FUNCTION cdm.percentile_calc() RETURNS text AS 'DECLARE v_interval int4; v_tile int4; v_percentile int4; v_check int4; v_count int4; v_rowcount int4; myRec RECORD; BEGIN v_count:=0; v_tile:= 100; v_percentile:=1; v_rowcount :=1; v_check:=0; /* Get count of records with val_purch > 0 */ select into v_count count(*) from cdm.cdm_indiv_mast where val_purch_com >0; /* this number will be used as part of our MOD to tell when to add one to our percentile */ v_interval := v_count / v_tile; CREATE TEMP TABLE cdmperct (f1 int8, f2 int2); FOR myRec IN select indiv_key from cdm.cdm_indiv_mast where val_purch_com >0 order by val_purch_com desc LOOP INSERT INTO cdmperct values (myRec.indiv_key,v_percentile); v_check = mod(v_rowcount,v_interval); IF v_check = 0 THEN v_percentile:=v_percentile+1; END IF; v_rowcount:= v_rowcount+1; END LOOP; UPDATE cdm.cdm_indiv_mast SET percentiler = f2 from cdmperct where indiv_key = f1; DROP TABLE cdmperct; RETURN \'DONE\'; END; ' LANGUAGE 'plpgsql' IMMUTABLE; Patrick Hatcher Macys.Com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
