Re: [ADMIN] Please help - tks
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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] Please help - tks
Sorry, my bad. I thought your whole point was in regards to the sleep command (which I
really didn't understand why). However, your point about stressing the server is a
very valid point, and I did not catch that in your earlier post.
My apologies for the misunderstanding...
--brett
p.s. it's been a bad day...
On Fri, 19 Oct 2001 09:42:52 -0500 (CDT)
Brian McCane <[EMAIL PROTECTED]> wrote:
> Brett,
>
> It appears that I may have upset you somehow. I didn't intend to. I
> chose to write the PERL script to place them in a transaction to help
> improve performance a little bit on each pass. By building and tearing
> down a connection to the postmaster everytime you send an update, you will
> be putting a much heavier load on the server than is necessary. If I was
> using the script myself, I would make the connection to the postmaster
> once at the top of the script, and then re-use it with a transaction block
> for each group of 10/20. Then shut it down at the end. This way I do not
> suffer any additional protocol overhead each time I want to use it.
>
> - brian
>
>
> On Thu, 18 Oct 2001, Brett Schwarz wrote:
>
> >
> >
> > 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 <[EMAIL PROTECTED]> 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 = )
> > > {
> > > 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 s
Re: [ADMIN] 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 <[EMAIL PROTECTED]> 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 = )
> {
> 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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
