Ruby postgresql library: time out error

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

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

···

On 17/05/05, rgilaard@few.vu.nl <rgilaard@few.vu.nl> wrote:

Can anyone help with this one?
Thanks in advance
Robert

--
http://ruby.brian-schroeder.de/

Stringed instrument chords: http://chordlist.brian-schroeder.de/

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

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

···

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

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

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-schroeder.de/

Stringed instrument chords: http://chordlist.brian-schroeder.de/

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

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