LinuxQuestions.org
Go Job Hunting at the LQ Job Marketplace
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices



Reply
 
Search this Thread
Old 08-26-2011, 01:14 AM   #1
mpapet
Member
 
Registered: Nov 2003
Location: Los Angeles
Distribution: debian
Posts: 453

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


Hi,

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 01:23 AM.
 
  


Reply


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 02:23 PM
Can't View PostgreSQL Table Data carlosinfl Linux - Server 1 12-11-2010 04:41 PM
Million dollar maths puzzle sparks row Jeebizz Linux - News 0 08-11-2010 09:42 PM
SQL statement to get the last row in a table oulevon Programming 11 04-03-2009 09:30 PM
display a row of data from a database in a table format 3vra Programming 2 03-13-2009 11:25 AM


All times are GMT -5. The time now is 11:53 AM.

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