Connecting to postgres

georgeuoa · February 28, 2007, 2:24am

Hello everyone!

Please forgive my ignorance, am pretty new to rails.

Am creating an application on rails framework that will use a legacy
postgres
database with about 119 tables and 121 sequences that define the primary
key fields.
The database is ready now and i need to hook it to active:record:Base
i have installed these two gems:
postgres-pr
ruby-postgres

My YML file reads like this:

development:
adapter: postgresql
database: DB_NAME
username: george
password: tanzanite
host: localhost
schema_order: public

Warning: The database defined as ‘test’ will be erased and

re-generated from your development database when you run ‘rake’.

Do not set this db to the same as development or production.

test:
adapter: postgresql
database: DB_NAME_test
username: tanzanite
password: password
host: localhost
schema_order: public

production:
adapter: postgresql
database: DB_NAME_production
username: tanzanite
password: password
host: localhost
schema_order: public

i then wrote a configuration file that stops pluralization of the
tables.
i generated a model scaffold to “test the waters” and lol and behold! it
could not connect to through active:record.

Please can someone point me to a useful resource to guide me on how to
accomplish this. All resources seem to use mysql and am only finding no
information on connecting to postgres legacy databases.

How do i map the recquired id field to my user-defined primary key
generically named Tablename_id?

What configuration details am i missing?

Thank you.

rgds
tanzanite

georgeuoa · February 28, 2007, 3:24am

Hello,

database.yml seems to be ok (maybe schema_order… i don’t know i
don’t use this parameter).
are you sure about your postgresql database config?
can you connect from application server to your postgresql database?
if not check :

pg_hba.conf should be configure to allow application server to
connect.
and listen_addresses in postgresql.conf should be correct to.

bye

On 28 fév, 02:24, Tanzanite T. <rails-mailing-l…@andreas-

georgeuoa · February 28, 2007, 3:42am

What exceptions are in logs?
For legacy databases you might find useful class methods
set_primary_key, set_table_name, set_sequence_name, see
ActiveRecord::Base
and primary_key_prefix_type
http://dev.rubyonrails.org/browser/trunk/activerecord/lib/active_record/base.rb?rev=6139#L279

2007/2/28, Tanzanite T. [email protected]:

georgeuoa · February 28, 2007, 3:53am

Lugovoi N. wrote:

What exceptions are in logs?

For legacy databases you might find useful class methods
set_primary_key, set_table_name, set_sequence_name, see
ActiveRecord::Base
and primary_key_prefix_type
http://dev.rubyonrails.org/browser/trunk/activerecord/lib/active_record/base.rb?rev=6139#L279

2007/2/28, Tanzanite T. [email protected]:

Comments appreciated

This is what is in the logs

ActiveRecord::StatementInvalid (RuntimeError: ERROR C42501 Mpermission
denied for relation feature Faclchk.c L1395 Raclcheck_error: SELECT
count(*) AS count_all FROM feature ):
C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/connection_adapters/abstract_adapter.rb:128:in
log' C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/connection_adapters/postgresql_adapter.rb:152:in execute’
C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/connection_adapters/postgresql_adapter.rb:433:in
select' C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/connection_adapters/abstract/database_statements.rb:13:in select_one’
C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/connection_adapters/abstract/database_statements.rb:19:in
select_value' C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/calculations.rb:212:in execute_simple_calculation’
C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/calculations.rb:121:in
calculate' C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/calculations.rb:117:in catch’
C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/calculations.rb:117:in
calculate' C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/calculations.rb:45:in count’
C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/pagination.rb:173:in
count_collection_for_pagination' C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/pagination.rb:197:in paginator_and_collection_for’
C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/pagination.rb:130:in
paginate' /app/controllers/admin_controller.rb:12:in list’
/app/controllers/admin_controller.rb:3:in index' C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/base.rb:1095:in send’
C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/base.rb:1095:in
perform_action_without_filters' C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/filters.rb:632:in call_filter’
C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/filters.rb:634:in
call_filter' C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/filters.rb:619:in perform_action_without_benchmark’
C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/benchmarking.rb:66:in
perform_action_without_rescue' C:/ruby/lib/ruby/1.8/benchmark.rb:293:in measure’
C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/benchmarking.rb:66:in
perform_action_without_rescue' C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/rescue.rb:83:in perform_action’
C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/base.rb:430:in
send' C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/base.rb:430:in process_without_filters’
C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/filters.rb:624:in
process_without_session_management_support' C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/session_management.rb:114:in process’
C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/base.rb:330:in
process' C:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.1/lib/dispatcher.rb:41:in dispatch’
C:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.1/lib/webrick_server.rb:113:in
handle_dispatch' C:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.1/lib/webrick_server.rb:79:in service’
C:/ruby/lib/ruby/1.8/webrick/httpserver.rb:104:in service' C:/ruby/lib/ruby/1.8/webrick/httpserver.rb:65:in run’
C:/ruby/lib/ruby/1.8/webrick/server.rb:173:in start_thread' C:/ruby/lib/ruby/1.8/webrick/server.rb:162:in start’
C:/ruby/lib/ruby/1.8/webrick/server.rb:162:in start_thread' C:/ruby/lib/ruby/1.8/webrick/server.rb:95:in start’
C:/ruby/lib/ruby/1.8/webrick/server.rb:92:in each' C:/ruby/lib/ruby/1.8/webrick/server.rb:92:in start’
C:/ruby/lib/ruby/1.8/webrick/server.rb:23:in start' C:/ruby/lib/ruby/1.8/webrick/server.rb:82:in start’
C:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.1/lib/webrick_server.rb:63:in
dispatch' C:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.1/lib/commands/servers/webrick.rb:59 C:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in gem_original_require’
C:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in
require' C:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.0/lib/active_support/dependencies.rb:496:in require’
C:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.0/lib/active_support/dependencies.rb:343:in
new_constants_in' C:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.0/lib/active_support/dependencies.rb:496:in require’
C:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.1/lib/commands/server.rb:39
C:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in
gem_original_require' C:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in require’
./script/server:3
-e:4:in `load’
-e:4

georgeuoa · February 28, 2007, 4:01am

2007/2/28, Tanzanite T. [email protected]:

This is what is in the logs

ActiveRecord::StatementInvalid (RuntimeError: ERROR C42501 Mpermission
denied for relation feature Faclchk.c L1395 Raclcheck_error: SELECT
count(*) AS count_all FROM feature ):

So, the problem shows out clear: you connect to database with postgres
role that does not have enough privilieges.
Make sure you did GRANT’ed necessary access to database objects.

georgeuoa · February 28, 2007, 4:15am

Lugovoi N. wrote:

2007/2/28, Tanzanite T. [email protected]:

This is what is in the logs

ActiveRecord::StatementInvalid (RuntimeError: ERROR C42501 Mpermission
denied for relation feature Faclchk.c L1395 Raclcheck_error: SELECT
count(*) AS count_all FROM feature ):

So, the problem shows out clear: you connect to database with postgres
role that does not have enough privilieges.
Make sure you did GRANT’ed necessary access to database objects.
PostgreSQL: Documentation: 15: GRANT

Hey!

Thank you!
Thank you!

it worked

These are the additional configurations i have added:
i have manually set the primary key to the actual name of the primary
key in my table. This is not a DRY compliant method and i have to change
that so that i dont have to repeat that for each of the 119 tables!

but otherwise it means it can work and for that am greatful for your
comments!

rgds
tanzanite

georgeuoa · February 28, 2007, 3:56am

Hello,

database.yml seems to be ok (maybe schema_order… i don’t know i
don’t use this parameter).
are you sure about your postgresql database config?
can you connect from application server to your postgresql database?
if not check :

pg_hba.conf should be configure to allow application server to
connect.

and listen_addresses in postgresql.conf should be correct to.

bye

On 28 fï¿½v, 02:24, Tanzanite T. <rails-mailing-l…@andreas-

Thanks i have configured connections to accept localhost
am now getting this

RuntimeError: ERROR C42501 Mpermission denied for relation feature
Faclchk.c L1395 Raclcheck_error: SELECT count(*) AS count_all FROM
feature

georgeuoa · February 28, 2007, 4:55am

2007/2/28, Tanzanite T. [email protected]:

These are the additional configurations i have added:
i have manually set the primary key to the actual name of the primary
key in my table. This is not a DRY compliant method and i have to change
that so that i dont have to repeat that for each of the 119 tables!

Well, using legacy database schema that does not conform to Rails
conventions and cannot be changed, is indeed not very DRY.

e.g. when defining associations one may need to declare foreign key
names and class names explicitly, but from my own experience it’s not
that bad even with dozens of tables

But, you can dig postgres system tables (I mean with some ruby/sql
script) and build first scratch version of ActiveRecord models
automatically.

georgeuoa · February 28, 2007, 11:15am

Hi,
for all those who may be wishing to integrate postgresql into their
rails
framework, I received this from the postgresql list:

=============================================================
Given the recent discussions of applications stacks, PHP & Ruby etc. it
seems an ideal time for me to introduce a project I’ve been working on.

StackBuilder is an extension of the Windows installer for PostgreSQL
that will allow the user to quickly and easily download and install
additional software to build the application stack they desire around
PostgreSQL. The project includes the StackBuilder wizard as well as a
toolkit for building application installers.

Whilst the StackBuilder itself is still in development, the first
application installer, pgRails, is available for manual download and
testing from http://pgfoundry.org/projects/stackbuilder/

pgRails is a distribution of Ruby, Rails, and the Ruby PostgreSQL
connector all preconfigured for use with PostgreSQL on Windows 2000 and
above.

I’d like to invite anyone who is interested to download and try it out,
and report any issues or problems using the project’s trackers on
pgFoundry.

Regards, Dave.

Hope that helps someone…

–
Regards

Andrew