Re: Please help - tks - Mailing list pgsql-admin

Much too quick and dirty.  With 300,000 updates this will take 1.5million
seconds, roughly 200 days.  Even at a rate of 1 a second it will take
nearly four days.  All this aside, I would probably use a perl script and
do batches of about 10-20, with a sleep.  You can try something like the
following and feed it using standard input.  Please NOTE, I didn't test
this, and even if I did, you shouldn't trust me :).

- brian

Wm. Brian McCane                    | Life is full of doors that won't open
Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"

#! /usr/bin/perl

use strict ;

my @updates ;

while (my $line = <STDIN>)
{
  my $cmd = "UPDATE table SET ......." ;    # Use the line we just read
  push(@updates, $cmd) ;
  if (@updates == 10)
  {
    do_update() ;
    sleep 5 ;
    @updates = () ;                # Redundant
  }
}
do_update if (@updates) ;

sub do_update
{
  open PSQL, "| psql dbname" ;
  print PSQL "begin transaction ;\n" ;
  while (my $cmd = shift @updates)
  {
    print PSQL $cmd . "\n" ;
  }
  print PSQL "commit ;\n" ;
  close PSQL ;
}

__END__


On Thu, 18 Oct 2001, Brett Schwarz wrote:

> A quick and dirty solution would be something like (untested):
>
> while read line; do
>     psql dbname -c "update table  ... use 'line' as your id and data"
>     sleep 5
> done < filename
>
> where "filename" is the file with the ids in it. "dbname" is the name of your DB (of course!)
>
> I am not sure what your file looks like, but you can do something like this. It could be easy, or harder, depending
onwhat your file has. 
>
> adjust the sleep to your own specs.
>
> --brett
>
>
> P.S.
>
> You could test this out by doing:
>
> while read line; do
>     psql dbname -c "select * from table where id=$line"
>     sleep 5
> done < filename
>
>
>
> On Thu, 18 Oct 2001 16:35:01 +1000 (EST)
> Mr OCP <mr_ocp@yahoo.com> wrote:
>
> > Hi Folks
> >
> > We have about 300,000 records to update, I have
> > created a file with the ids of the records to be
> > updated, since the number of records to be updated is
> > very large, the update statement takes forever and
> > server slows down dramatically, therefore I want to do
> > one at a time.
> >
> > Do you guys have any script which may update records
> > one by one using the file that lists these records. I
> > am running postgresql sever under Unix, any kind of
> > script would do, whether its a perl, bash or sql
> > whatever script
> >
> > Thanks for your help
> > Mike
> >
> > http://briefcase.yahoo.com.au - Yahoo! Briefcase
> > - Manage your files online.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
>
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>