Ruby on Rails | Screencasts | Download | Documentation | Weblog | Community | Source

Ticket #7688 (new defect)

Opened 2 years ago

Last modified 1 year ago

[PATCH] Postgresql Adaptor Tables method is buggy

Reported by: mezza Assigned to: bitsweat
Priority: normal Milestone: 2.x
Component: ActiveRecord Version: edge
Severity: normal Keywords: postgres schema
Cc:

Description

If your Postgresql database has multiple schema, then functions such as rake db:schema:dump fail.

This seems to be due to a faulty PostgreSQLAdapter.tables method, which doesn't currently strip trailing and leading spaces. Consequently, when multiple schema are present, the query being executed to determine the table names is matching strings which include leading spaces.

Eg: in my case I ended up having an additional 'mediawiki' schema in addition to 'public' after installing mediawiki, and this meant the ConnectionAdapter was calling: 

SELECT tablename
 FROM pg_tables
 WHERE schemaname IN ('mediawiki',' public')

instead of 

SELECT tablename
 FROM pg_tables
 WHERE schemaname IN ('mediawiki','public')

The attached patch corrects this minor but annoying defect.

Attachments

active_record_postgres_adaptor_patch.diff (0.9 kB) - added by mezza on 05/15/07 08:57:15.

Change History

03/06/07 11:22:28 changed by bitsweat

  • keywords set to postgres schema.
  • owner changed from core to bitsweat.

PG requires no spaces between schemas in the search_path, so the schema_search_path setting in database.yml keeps that requirement.

How did you come to have leading or trailing whitespace?

03/06/07 11:43:52 changed by mezza

My database usually just has one schema: 'public', but after doing an install of mediawiki recently, an additional 'mediawiki' schema was created by the mediawiki installer.

The postgres adaptor fetches the schema_search_path, by executing the following query: 

SHOW search_path;

see lines 239-241 in the postgresql_adapter.rb

which, as there are two schemas returns: 

    search_path    
-------------------
 mediawiki, public

So postgres returns the schema separated by a comma and a leading space. The adaptor doesn't strip out this leading space, and hence as far as the connection is concerned, the schemas defined in the database are 'mediawiki' and ' public' .

Subsequently any queries executed by the adapter for the ' public' schema fails to return anything.

The patch I've suggested just strips the leading and trailing spaces from the schemas, although of course it could be implemented in the 'quote' method in the adapter as well.

05/15/07 08:56:56 changed by mezza

Just noticed a bug in my own patch. Apologies, but I forgot that 'strip!' returns nil if nothing was stripped. The need for this patch is still present on my development and production boxes with multiple PGSQL schema. New patch uploaded.

05/15/07 08:57:15 changed by mezza

  • attachment active_record_postgres_adaptor_patch.diff added.

06/18/07 01:51:24 changed by wycats

Can we get this in? I've been wondering about this, as my own app uses multiple schemas, and I'm now wondering if some of the weird schemadumper problems we've encountered may have been related to this bug.

06/26/07 17:27:23 changed by matt

  • priority changed from high to normal.
  • severity changed from major to normal.
  • summary changed from Postgresql Adaptor Tables method is buggy to [PATCH] Postgresql Adaptor Tables method is buggy.

prefix the summary with [PATCH] so TRAC can manage this ticket properly.