Asp Forum - ruby postgresql library: time out error

rgilaard

5/17/2005 3:18:00 PM

Dear all,
I?m using a ruby script to manipulate some database tables in postgresql 7.4.6
running under freebsd.

The following query

create table as select ident,count(ident) as totnrchange from bookhistory group
by ident

Takes almost 20 minutes to accomplish if I issue these commands from the psql
client utility.

However, if I use ruby to do the same as in:

Res=conn.exec(?create table as select ident,count(ident) as totnrchange from
bookhistory group by ident?)

I get the follwing error:

Maketestset.rb : 47: IN ?exec?: could not receive data from server: Operation
timed out (PGError) from maketestset.rb

I also notice when looking at the outcome of top, that the % the cpu is busy
with the postgers process gradually decreases and after a certain time the cpu
stops doing anything and I get this error message.

What could cause this? I?m suspecting ruby because when I normally do this in
psql, the query finishes.
I have to do it like this because this statement is in a for loop and gets
repeated almost 300 times

Can anyone help with this one?
Thanks in advance
Robert

6 Answers

Brian Schröder

5/17/2005 3:26:00 PM

On 17/05/05, rgilaard@few.vu.nl <rgilaard@few.vu.nl> wrote:
> Dear all,
> I'm using a ruby script to manipulate some database tables in postgresql 7.4.6
> running under freebsd.
>
> The following query
>
> create table as select ident,count(ident) as totnrchange from bookhistory group
> by ident
>
> Takes almost 20 minutes to accomplish if I issue these commands from the psql
> client utility.
>
> However, if I use ruby to do the same as in:
>
> Res=conn.exec("create table as select ident,count(ident) as totnrchange from
> bookhistory group by ident")
>
> I get the follwing error:
>
> Maketestset.rb : 47: IN 'exec': could not receive data from server: Operation
> timed out (PGError) from maketestset.rb
>
> I also notice when looking at the outcome of top, that the % the cpu is busy
> with the postgers process gradually decreases and after a certain time the cpu
> stops doing anything and I get this error message.
>
> What could cause this? I'm suspecting ruby because when I normally do this in
> psql, the query finishes.
> I have to do it like this because this statement is in a for loop and gets
> repeated almost 300 times

Just curious, does that mean that you wait > 4 Days creating the table
300 times? Thats a lot of time. Would you care to tell me how many
rows are in this table?

best regards,

Brian

>
> Can anyone help with this one?
> Thanks in advance
> Robert
>
>

--
http://ruby.brian-sch...

Stringed instrument chords: http://chordlist.brian-sch...

rgilaard

5/17/2005 3:38:00 PM

No:-)
In production I will have it accumulate by putting another expression in the
query.

There are a few million records in this table.
I've also created an index on the where clause I was talking about in the
beginning but that didn't help.

The query just times out, only in Ruby.

Thanks,

Quoting Brian Schröder <ruby.brian@gmail.com>:

> On 17/05/05, rgilaard@few.vu.nl <rgilaard@few.vu.nl> wrote:
> > Dear all,
> > I'm using a ruby script to manipulate some database tables in postgresql
> 7.4.6
> > running under freebsd.
> >
> > The following query
> >
> > create table as select ident,count(ident) as totnrchange from bookhistory
> group
> > by ident
> >
> > Takes almost 20 minutes to accomplish if I issue these commands from the
> psql
> > client utility.
> >
> > However, if I use ruby to do the same as in:
> >
> > Res=conn.exec("create table as select ident,count(ident) as totnrchange
> from
> > bookhistory group by ident")
> >
> > I get the follwing error:
> >
> > Maketestset.rb : 47: IN 'exec': could not receive data from server:
> Operation
> > timed out (PGError) from maketestset.rb
> >
> > I also notice when looking at the outcome of top, that the % the cpu is
> busy
> > with the postgers process gradually decreases and after a certain time the
> cpu
> > stops doing anything and I get this error message.
> >
> > What could cause this? I'm suspecting ruby because when I normally do this
> in
> > psql, the query finishes.
> > I have to do it like this because this statement is in a for loop and gets
> > repeated almost 300 times
>
> Just curious, does that mean that you wait > 4 Days creating the table
> 300 times? Thats a lot of time. Would you care to tell me how many
> rows are in this table?
>
> best regards,
>
> Brian
>
> >
> > Can anyone help with this one?
> > Thanks in advance
> > Robert
> >
> >
>
>
>
> --
> http://ruby.brian-sch...
>
> Stringed instrument chords: http://chordlist.brian-sch...
>
>

Pat Maddox

5/17/2005 3:53:00 PM

That's a postgres problem, not a Ruby problem. You said it takes
nearly 20 minutes when using the command line client. Don't you think
that's a little excessive? Actually, let me just inform you right now
that it's a lot excessive. Ruby times out because it says, "Uh,
there's no reason it should be taking this long. I quit." And it's
right, there's no reason it should take that long.

I don't know what the problem is, but it's a problem with your db, not
Ruby. There's probably a way to change the timeout in Ruby, so it
would in fact wait that long. I'd look to fix your db first though.

On 5/17/05, rgilaard@few.vu.nl <rgilaard@few.vu.nl> wrote:
> Dear all,
> I'm using a ruby script to manipulate some database tables in postgresql 7.4.6
> running under freebsd.
>
> The following query
>
> create table as select ident,count(ident) as totnrchange from bookhistory group
> by ident
>
> Takes almost 20 minutes to accomplish if I issue these commands from the psql
> client utility.
>
> However, if I use ruby to do the same as in:
>
> Res=conn.exec("create table as select ident,count(ident) as totnrchange from
> bookhistory group by ident")
>
> I get the follwing error:
>
> Maketestset.rb : 47: IN 'exec': could not receive data from server: Operation
> timed out (PGError) from maketestset.rb
>
> I also notice when looking at the outcome of top, that the % the cpu is busy
> with the postgers process gradually decreases and after a certain time the cpu
> stops doing anything and I get this error message.
>
> What could cause this? I'm suspecting ruby because when I normally do this in
> psql, the query finishes.
> I have to do it like this because this statement is in a for loop and gets
> repeated almost 300 times
>
> Can anyone help with this one?
> Thanks in advance
> Robert
>
>

Dalibor Sramek

5/17/2005 4:29:00 PM

On Wed, May 18, 2005 at 12:17:34AM +0900, rgilaard@few.vu.nl wrote:
> create table as select ident,count(ident) as totnrchange from bookhistory group
> by ident

I don't know whay Ruby timeouts but I would suggest using a rule or a
trigger to keep the aggregates updated.

Regards

Dalibor Sramek

--
Dalibor Sramek http://www.insu... \ In the eyes of cats
/ dalibor.sramek@insula.cz \ all things
/ >H blog http://www.transhumanismus.c... \ belong to cats.

Florian Pflug

5/17/2005 8:41:00 PM

Pat Maddox wrote:
> That's a postgres problem, not a Ruby problem. You said it takes
> nearly 20 minutes when using the command line client. Don't you think
> that's a little excessive? Actually, let me just inform you right now
> that it's a lot excessive. Ruby times out because it says, "Uh,
> there's no reason it should be taking this long. I quit." And it's
> right, there's no reason it should take that long.
Actually, if you copy a few million rows, there is reason enough

> I don't know what the problem is, but it's a problem with your db, not
> Ruby. There's probably a way to change the timeout in Ruby, so it
> would in fact wait that long. I'd look to fix your db first though.
I'd say that its quite reasonable for _some_ statements to take
a long time - I'm running imports in a postgres database that
take a few hours, and don't time out. I'd probably not
want to call this from a webrequest, but it can still make sense ;-))

Which postgres module are you using? I'm using the (quite old)
binding for the pg-c-libs - There is a pure-ruby-implementation
too, which might inherit some ruby-specific socket-timeouts.

greetings, Florian Pflug

rgilaard

5/18/2005 10:33:00 AM

This is definately not a web application.

I'm using the extension library ruby-postgres version 0.7.1.
It did mention that it worked with Posgresql from 6.5 up to 7.2 while I'm
running Postgresql 7.4.2

How can I manipulate the time out settings in this ruby library?

Brgds
Robert

Quoting "Florian G. Pflug" <fgp@phlo.org>:

> Pat Maddox wrote:
> > That's a postgres problem, not a Ruby problem. You said it takes
> > nearly 20 minutes when using the command line client. Don't you think
> > that's a little excessive? Actually, let me just inform you right now
> > that it's a lot excessive. Ruby times out because it says, "Uh,
> > there's no reason it should be taking this long. I quit." And it's
> > right, there's no reason it should take that long.
> Actually, if you copy a few million rows, there is reason enough
>
> > I don't know what the problem is, but it's a problem with your db, not
> > Ruby. There's probably a way to change the timeout in Ruby, so it
> > would in fact wait that long. I'd look to fix your db first though.
> I'd say that its quite reasonable for _some_ statements to take
> a long time - I'm running imports in a postgres database that
> take a few hours, and don't time out. I'd probably not
> want to call this from a webrequest, but it can still make sense ;-))
>
> Which postgres module are you using? I'm using the (quite old)
> binding for the pg-c-libs - There is a pure-ruby-implementation
> too, which might inherit some ruby-specific socket-timeouts.
>
> greetings, Florian Pflug
>
>

comp.lang.ruby

ruby postgresql library: time out error

rgilaard

Brian Schröder

rgilaard

Pat Maddox

Dalibor Sramek

Florian Pflug

rgilaard

x Login to ForumsZone