I need help to connect Postgres and Ruby on Rails Please

Rails
1

Hello to everyone, I have a problem that is giving me a headache, and
trying to do a project in Ruby on Rails and I need to connect with a
Data Base that is en Postgres, the truth is that I didn’t think that it
was so hard to connect a DB with postgres because I was working with
Msyql and everything was easy. This are the thinks that I have install
in my computer.

  1. Debian GNU/Linux, kernel 2.4.27-2-686.
  2. Ruby 1.8.2
  3. Postgresql 7.4.7-6 sarge1
  4. Gems: actionmailer-1.2.5, actionpack-1.12.5, actionwebservice-1.1.6,
    activerecord-1.14.4, activesupport-1.3.1, postgres-pr-0.2.1,
    rails-1.1.6, rake-0.7.1
  5. ruby-postgres-0.7.1.tar.tar

The error is that after I install everything that I need, I can’t
connect with my DB, in my project in the folder /app/models I made a
archive with the same name off my table, remembering that my real table
the name is in plural bout in my project in singular, the thinks that I
wrote look something like this

ActiveRecord::Base.establish_connection(
:adapter => “postgresql”,
:host => “localhost”,
:port => “5432”,
:database => “mundo”,
:username => “usuario”,
:password => “123456”

)

class Usuario < ActiveRecord::Base

end

When I try to save something in my DB the error that appears is like
this:

TypeError in PruebaController#guardar
can’t convert String into Integer

RAILS_ROOT: script/…/config/…
Application Trace | Framework Trace | Full Trace

/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/postgresql_adapter.rb:24:in
initialize' /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/postgresql_adapter.rb:24:inconnect’
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/postgresql_adapter.rb:24:in
postgresql_connection' /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/abstract/connection_specification.rb:251:insend’
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/abstract/connection_specification.rb:251:in
connection_without_query_cache=' /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/query_cache.rb:54:inconnection=’
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/abstract/connection_specification.rb:220:in
retrieve_connection' /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/abstract/connection_specification.rb:78:inconnection’
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:696:in
columns' /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:1969:inattributes_from_column_definition’
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:1351:in
initialize_without_callbacks' /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/callbacks.rb:236:ininitialize’
app/controllers/prueba_controller.rb:9:in new' app/controllers/prueba_controller.rb:9:inguardar’
-e:4:in load' -e:4 /usr/lib/ruby/1.8/webrick/httpserver.rb:104:inservice’
/usr/lib/ruby/1.8/webrick/httpserver.rb:65:in run' /usr/lib/ruby/1.8/webrick/server.rb:172:instart_thread’
/usr/lib/ruby/1.8/webrick/server.rb:161:in start' /usr/lib/ruby/1.8/webrick/server.rb:161:instart_thread’
/usr/lib/ruby/1.8/webrick/server.rb:95:in `start’

Request
Parameters: {“commit”=>“Guardar”, “usuario”=>{“apellido”=>“GGGGGG”,
“cedula”=>“888888”, “nombre”=>“MMMMM”, “telefono”=>“5555555”}}
Show session dump
flash: !ruby/hash:ActionController::Flash::FlashHash {}

Response
Headers: {“cookie”=>[], “Cache-Control”=>“no-cache”}

Pleas if someone know how to help me I’ll be grateful.
From Venezuela. El_gocho says thank you.
P.S: sorry if I wrote something wrong, my English writhing is not so
good.

2

On 12/4/06, Juan M. [email protected] wrote:

:port     => "5432",
:database => "mundo",
:username => "usuario",
:password => "123456"

In config/database.yml

Host and port are unnecessary if your database is local.

development:
adapter: postgresql
database: mundo
username: usuario
password: 123456

Then run ./script/console and do
class Usuario < ActiveRecord::Base; end
Usuario.find(:all)

You should see “select * from usuarios” in your log/development.log

jeremy

3

Jeremy K. wrote:

On 12/4/06, Juan M. [email protected] wrote:

:port     => "5432",
:database => "mundo",
:username => "usuario",
:password => "123456"

In config/database.yml

Host and port are unnecessary if your database is local.

development:
adapter: postgresql
database: mundo
username: usuario
password: 123456

Then run ./script/console and do
class Usuario < ActiveRecord::Base; end
Usuario.find(:all)

You should see “select * from usuarios” in your log/development.log

jeremy

Hello my friend, well I try to do what you wrote me bout I still have a
problem, I changed my
ActiveRecord::Base.establish_connection(
:adapter => “postgresql”,
:host => “localhost”,
:port => “5432”,
:database => “mundo”,
:username => “usuario”,
:password => “123456”

)

class Usuario < ActiveRecord::Base

end

for something like this

ActiveRecord::Base.establish_connection(
:adapter => “postgresql”,
:database => “mundo”,
:username => “usuario”,
:password => “123456”

)

class Usuario < ActiveRecord::Base

end

and now I have this problem, when I try to save some data I received a
javascript error that says “The connection was refused when attempting
to contact localhost:3000 ”.

Please if you can help I’ll be grateful.
Thank you. El gocho – From Venezuela

4

Juan M. wrote:

  1. Debian GNU/Linux, kernel 2.4.27-2-686.
  2. Ruby 1.8.2
  3. Postgresql 7.4.7-6 sarge1
  4. Gems: actionmailer-1.2.5, actionpack-1.12.5, actionwebservice-1.1.6,
    activerecord-1.14.4, activesupport-1.3.1, postgres-pr-0.2.1,
    rails-1.1.6, rake-0.7.1
  5. ruby-postgres-0.7.1.tar.tar

Juan,

I believe you have the wrong connector for postgresql, for anything at
or above 7.4, you need gem: postgres-pr

I see you have gems installed, so you are all set
gem install postgres-pr

hope this helps

ilan

5

I was wondering if there are any real benchmarks using the pure ruby
postgresql db driver versus the c-bindings one, or if people just
‘believe’
one is faster then the other.

(In Java land, pure Java oracle-JDBC driver is actually faster then the
OCI/native wrapper one (type-4 versus type-2 I think) due to time taken
in
the conversion between native c-structs and Java objects. But I’m not
sure
Ruby has the same problem…)

On 12/5/06, Jeremy K. [email protected] wrote:

This is not true.

postgres-pr is the pure-Ruby postgres client

The other postgres libs are C bindings for the official libpq client.

The C bindings are more robust, fully featured, and faster.

jeremy

–
Virtually, Ned W.

“Settle thy studies, Faustus, and begin…” --Marlowe

Discere docendo…

6

On 12/5/06, Ilan B. [email protected] wrote:

I believe you have the wrong connector for postgresql, for anything at
or above 7.4, you need gem: postgres-pr

I see you have gems installed, so you are all set
gem install postgres-pr

This is not true.

postgres-pr is the pure-Ruby postgres client

The other postgres libs are C bindings for the official libpq client.

The C bindings are more robust, fully featured, and faster.

jeremy

7

2006/12/6, Ned W. [email protected]:

I was wondering if there are any real benchmarks using the pure ruby
postgresql db driver versus the c-bindings one, or if people just ‘believe’
one is faster then the other.

Here follow some really dumb benchmarks:

$ cat bm-c.rb

for ruby-postgres

require ‘postgres’
require ‘benchmark’
require ‘common-bm’
PGconn.translate_results= ARGV[0] ? true : nil
bench_postgres( PGconn.new(‘dbname=template1’) )

$ cat bm.rb

for postgres-pr

require ‘postgres’
require ‘benchmark’
require ‘common-bm’

initialize(host, port, options, tty, database, user, auth

bench_postgres(PGconn.new(‘localhost’, 5432, “”, nil, ‘template1’,
‘nnick’, nil))

$ cat comon-bm.rb
Q_S = “select 1::int, 2.0::float, 1234567.8901234::numeric,
‘text’::text , generate_series(1,100)”
Q_S1 = “select now()::date , generate_series(1,100)”
Q_S2 = “select now()::time , generate_series(1,100)”
Q_S3 = “select now()::timestamptz , generate_series(1,100)”
Q_S4 = “select 2.0::float, 1234567.8901234::numeric, ‘text’::text ,
generate_series(1,100), now()”
N=100

def bench_postgres(c)
Benchmark.bm do |x|
x.report { N.times { c.query(Q_S) }}
x.report { N.times { c.query(Q_S1) }}
x.report { N.times { c.query(Q_S2) }}
x.report { N.times { c.query(Q_S3) }}
x.report { N.times { c.query(Q_S4) }}
end
puts c.query(Q_S4)[0].inspect
end

I put postres-pr to lib/ and postgres.so to libpq/

$ ruby -I lib bm.rb && ruby -I libpq bm-c.rb && ruby -I libpq bm-c.rb
true
user system total real
2.920000 0.020000 2.940000 ( 3.263881)
1.940000 0.020000 1.960000 ( 2.219724)
2.010000 0.030000 2.040000 ( 2.307650)
2.010000 0.020000 2.030000 ( 2.315171)
3.030000 0.020000 3.050000 ( 3.477216)
[“2”, “1234567.8901234”, “text”, “1”, “2006-12-06 17:57:28.13353+02”]
user system total real
0.190000 0.000000 0.190000 ( 0.491127)
0.140000 0.000000 0.140000 ( 0.358521)
0.130000 0.010000 0.140000 ( 0.429801)
0.130000 0.000000 0.130000 ( 0.422406)
0.240000 0.010000 0.250000 ( 0.610914)
[“2”, “1234567.8901234”, “text”, “1”, “2006-12-06 17:57:30.752541+02”]
user system total real
0.250000 0.010000 0.260000 ( 0.535132)
2.880000 0.030000 2.910000 ( 3.146284)
2.840000 0.080000 2.920000 ( 3.209385)
5.200000 0.010000 5.210000 ( 5.500557)
5.410000 0.050000 5.460000 ( 5.851943)
[2.0, #<BigDecimal:b7d5a93c,‘0.1234567890 1234E7’,16(20)>, “text”, 1,
Wed Dec 06 17:57:49 +0200 2006]

So, it looks like if results aren’t converted from text to Ruby types,
ruby-postgres is 10 times faster than postgres-pr.

But if ruby-postgres is set to translate values to Ruby types,
performance drops drastically on Date/Time conversion, but it is
rather fault of Ruby std libs for date parsing :frowning:
Though, if postgres-pr would implement type conversion (and it
doesn’t), it certainly would be slower - even with support of binary
format queries ( see
http://developer.postgresql.org/pgdocs/postgres/protocol-overview.html
) - implementation in C bindings, with direct access to underlying
structures of Ruby VALUE should outperform pure Ruby code, I guess.

Maybe, in ruby-postgres 0.8.0, that was going to be released "real
soon"™ (
http://blade.nagaokaut.ac.jp/cgi-bin/scat.rb/ruby/ruby-talk/175563
) it will be improved :slight_smile:

As for me, I like ruby-postgres better for such features as getting
value from resulting query row by name ( conn.query(…).first[‘id’]
), using placeholders in statements (conn.exec(“INSERT INTO foo VALUES
( $1, $2)”, value_1, value_2) ), better support for large objects
(both lo_* and BYTEA), automatic translation to Ruby types (very handy
when not using magic of ActiveRecord ) etc.

8

Lugovoi N. wrote:

2006/12/6, Ned W. [email protected]:

I was wondering if there are any real benchmarks using the pure ruby
postgresql db driver versus the c-bindings one, or if people just ‘believe’
one is faster then the other.

Here follow some really dumb benchmarks:

$ cat bm-c.rb

for ruby-postgres

require ‘postgres’
require ‘benchmark’
require ‘common-bm’
PGconn.translate_results= ARGV[0] ? true : nil
bench_postgres( PGconn.new(‘dbname=template1’) )

$ cat bm.rb

for postgres-pr

require ‘postgres’
require ‘benchmark’
require ‘common-bm’

initialize(host, port, options, tty, database, user, auth

bench_postgres(PGconn.new(‘localhost’, 5432, “”, nil, ‘template1’,
‘nnick’, nil))

$ cat comon-bm.rb
Q_S = “select 1::int, 2.0::float, 1234567.8901234::numeric,
‘text’::text , generate_series(1,100)”
Q_S1 = “select now()::date , generate_series(1,100)”
Q_S2 = “select now()::time , generate_series(1,100)”
Q_S3 = “select now()::timestamptz , generate_series(1,100)”
Q_S4 = “select 2.0::float, 1234567.8901234::numeric, ‘text’::text ,
generate_series(1,100), now()”
N=100

def bench_postgres(c)
Benchmark.bm do |x|
x.report { N.times { c.query(Q_S) }}
x.report { N.times { c.query(Q_S1) }}
x.report { N.times { c.query(Q_S2) }}
x.report { N.times { c.query(Q_S3) }}
x.report { N.times { c.query(Q_S4) }}
end
puts c.query(Q_S4)[0].inspect
end

I put postres-pr to lib/ and postgres.so to libpq/

$ ruby -I lib bm.rb && ruby -I libpq bm-c.rb && ruby -I libpq bm-c.rb
true
user system total real
2.920000 0.020000 2.940000 ( 3.263881)
1.940000 0.020000 1.960000 ( 2.219724)
2.010000 0.030000 2.040000 ( 2.307650)
2.010000 0.020000 2.030000 ( 2.315171)
3.030000 0.020000 3.050000 ( 3.477216)
[“2”, “1234567.8901234”, “text”, “1”, “2006-12-06 17:57:28.13353+02”]
user system total real
0.190000 0.000000 0.190000 ( 0.491127)
0.140000 0.000000 0.140000 ( 0.358521)
0.130000 0.010000 0.140000 ( 0.429801)
0.130000 0.000000 0.130000 ( 0.422406)
0.240000 0.010000 0.250000 ( 0.610914)
[“2”, “1234567.8901234”, “text”, “1”, “2006-12-06 17:57:30.752541+02”]
user system total real
0.250000 0.010000 0.260000 ( 0.535132)
2.880000 0.030000 2.910000 ( 3.146284)
2.840000 0.080000 2.920000 ( 3.209385)
5.200000 0.010000 5.210000 ( 5.500557)
5.410000 0.050000 5.460000 ( 5.851943)
[2.0, #<BigDecimal:b7d5a93c,‘0.1234567890 1234E7’,16(20)>, “text”, 1,
Wed Dec 06 17:57:49 +0200 2006]

So, it looks like if results aren’t converted from text to Ruby types,
ruby-postgres is 10 times faster than postgres-pr.

But if ruby-postgres is set to translate values to Ruby types,
performance drops drastically on Date/Time conversion, but it is
rather fault of Ruby std libs for date parsing :frowning:
Though, if postgres-pr would implement type conversion (and it
doesn’t), it certainly would be slower - even with support of binary
format queries ( see
http://developer.postgresql.org/pgdocs/postgres/protocol-overview.html
) - implementation in C bindings, with direct access to underlying
structures of Ruby VALUE should outperform pure Ruby code, I guess.

Maybe, in ruby-postgres 0.8.0, that was going to be released "real
soon"™ (
http://blade.nagaokaut.ac.jp/cgi-bin/scat.rb/ruby/ruby-talk/175563
) it will be improved :slight_smile:

As for me, I like ruby-postgres better for such features as getting
value from resulting query row by name ( conn.query(…).first[‘id’]
), using placeholders in statements (conn.exec(“INSERT INTO foo VALUES
( $1, $2)”, value_1, value_2) ), better support for large objects
(both lo_* and BYTEA), automatic translation to Ruby types (very handy
when not using magic of ActiveRecord ) etc.

Hello my friends, well I try all the you gays wrote me bout I don’t know
why I still have the problem, I just want to connect a DB that is in
Postgres with my project Ruby on Rails, bout nothing seems to work, well
all that thinks that I had made are:

  1. install all these:
    o Debian GNU/Linux, kernel 2.4.27-2-686.
    o Ruby 1.8.2
    o Postgresql 7.4.7-6 sarge1
    o Gems: actionmailer-1.2.5, actionpack-1.12.5, actionwebservice-1.1.6,
    activerecord-1.14.4, activesupport-1.3.1, postgres-pr-0.2.1,
    rails-1.1.6, rake-0.7.1
    o ruby-postgres-0.7.1.tar.tar

  2. in my project in the folder /app/models I made a archive with the
    same name off my table, remembering that my real table the name is in
    plural bout in my project in singular, the thinks that I wrote look
    something like this

           ActiveRecord::Base.establish_connection(
       :adapter  => "postgresql",
       :host     => "localhost",
       :port     => "5432",
       :database => "mundo",
        :username => "usuario",
        :password => "123456"
 )

class Usuario < ActiveRecord::Base

end

Ok, the first error that I had was these:

TypeError in PruebaController#guardar
can’t convert String into Integer

RAILS_ROOT: script/…/config/…
Application Trace | Framework Trace | Full Trace

/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/abstract/connection_specification.rb:220:in
retrieve_connection' /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/abstract/connection_specification.rb:78:in connection’
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:696:in
columns' /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:1969:in attributes_from_column_definition’
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:1351:in
initialize_without_callbacks' /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/callbacks.rb:236:in initialize’
app/controllers/prueba_controller.rb:9:in new' app/controllers/prueba_controller.rb:9:in guardar’
-e:4:in load' /usr/lib/ruby/1.8/webrick/httpserver.rb:104:in service’
/usr/lib/ruby/1.8/webrick/httpserver.rb:65:in run' /usr/lib/ruby/1.8/webrick/server.rb:172:in start_thread’
/usr/lib/ruby/1.8/webrick/server.rb:161:in start' /usr/lib/ruby/1.8/webrick/server.rb:161:in start_thread’
/usr/lib/ruby/1.8/webrick/server.rb:95:in `start’

Request
Parameters: {“commit”=>“Guardar”, “usuario”=>{“apellido”=>“GGGGGG”,
“cedula”=>“888888”, “nombre”=>“MMMMM”, “telefono”=>“5555555”}}
Show session dump
flash: !ruby/hash:ActionController::Flash::FlashHash {}

Response
Headers: {“cookie”=>[], “Cache-Control”=>“no-cache”}

Well a friend wrote me that the host and port aren’t necessary if my
data was local and it is, so I changed my connection for something like
these

ActiveRecord::Base.establish_connection(
:adapter => “postgresql”,
:database => “mundo”,
:username => “usuario”,
:password => “123456”
)

class Usuario < ActiveRecord::Base

end

So after that, I have a different error is a javascript error that says
“The connection was refused when attempting to contact localhost:3000 ”,
and the error happens when a try to save something in my DB, my host is
running because I can see all my web pages bout if I what to save
something and I click the save button the javascript error appear.

I don’t know if someone in this community can help me, with a tutorial
or a example the connect Postgres and Ruby on Rails, or maybe tell me
what thinks do I have to install in what order. Please someone help me I
don’t want to give-up with these error.

From Venezuela. El_gocho says thank you.
P.S: sorry if I wrote something wrong, my English writhing is not so
good