Welcome to the most active Linux Forum on the web.
Go Back > Forums > Linux Forums > Linux - Server
User Name
Linux - Server This forum is for the discussion of Linux Software used in a server related context.


  Search this Thread
Old 08-26-2011, 12:14 AM   #1
Registered: Nov 2003
Location: Los Angeles
Distribution: debian
Posts: 538

Rep: Reputation: 71
postgresql 8.4 Commit in Function on 3 Million Row Table


I have a function that needs to update two columns in a table with 3 million rows. I thought a cursor would break the job into chunks and most importantly COMMIT the chunk of data in the UPDATE command at the end of the loop.

The function commits all at once on all 3 million rows. I know this because I can start the function, count the number of nulls on the field the cursor should be updating. I come back 30 minutes later and the number of nulls on the updated field hasn't changed. If I set a limit to test the whole function it works as specified. It's updating all at once. Or something like that.

I just don't have the memory to do an update on 3 million rows. When the table gets to 30 million rows it's definitely not working.

The following simplified psuedo code is what I've got now.

I defined a cursor cursor_name and a query something like select gid, (col1 *2), (col2 *4) from big_giant_table
I opened the cursor
Used fetch cursor_name and dump the col1 and col2 values into variables and then LOOP
if logic to break the LOOP when it gets to the last record.
perform update big_giant_table set (my_first_calc, my_next_calc) values (co1_sum, col2_sum) where gid = gid_val
--I tried a commit here with Postgresql 8.4 and the function doesn't compile.
End Loop

Do I need to start another cursor for the update to force the commit?

Last edited by mpapet; 08-26-2011 at 12:23 AM.


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
40 million row PostgreSQL problem mpapet Linux - Server 4 08-22-2011 01:23 PM
Can't View PostgreSQL Table Data carlosinfl Linux - Server 1 12-11-2010 03:41 PM
Million dollar maths puzzle sparks row Jeebizz Linux - News 0 08-11-2010 08:42 PM
SQL statement to get the last row in a table oulevon Programming 11 04-03-2009 08:30 PM
display a row of data from a database in a table format 3vra Programming 2 03-13-2009 10:25 AM > Forums > Linux Forums > Linux - Server

All times are GMT -5. The time now is 08:49 PM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration