Re: Please help - tks

????

This is why I said this:
> > adjust the sleep to your own specs.

If he wants to "batch" them, then all he needs to do is add a condition/count:

--------------------------------
#!/bin/sh

cnt=1
while read line; do

# "batches" of 20 or whatever you *want*
if [ "X$cnt" == "X20" ]; then
sleep 5 # whatever you *want*
cnt=0
fi

psql dbname -c "UPDATE ... $line ..."
let cnt=$cnt+1

done < filename

---------------------------------

I will leave it up to the reader to modify this to his/her *specific* need...

--brett

On Thu, 18 Oct 2001 20:07:32 -0500 (CDT)
Brian McCane <bmccane(at)mccons(dot)net> wrote:

> 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 on what 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(at)yahoo(dot)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(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com