This project is archived and is in readonly mode.

#2418 ✓resolved
Scott Woods

PostgreSQL schema dumper does not support capitalized table names

Reported by Scott Woods | April 5th, 2009 @ 12:35 AM | in 2.x

The postgresql adapter appears to have a bug that causes a PGError to be raised if there are any tables in the database that start with a capital letter.

This seems to be caused by the fact that in the SQL schema queries, the table name is cast to type regclass without using double quotes, so the case of the table name is not preserved. This causes the cast to fail if the table name starts with or contains capital letters.

For example, if there is a table "CamelCase" in the database, then the schema dumper will fail with the following error:


ActiveRecord::StatementInvalid: PGError: ERROR:  relation "camelcase" does not exist
:             SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull
              FROM pg_attribute a LEFT JOIN pg_attrdef d
                ON a.attrelid = d.adrelid AND a.attnum = d.adnum
             WHERE a.attrelid = 'CamelCase'::regclass
               AND a.attnum > 0 AND NOT a.attisdropped
             ORDER BY a.attnum

The attached patch does the following:

  • Introduces a capitalized table name into the test schema and reads the table name back to verify that the existing code works for MySQL and SQLite, but breaks for PostgreSQL.
  • Adds quotes around the table name when casting to type regclass in the postgresql adapter.
  • Improves postgresql table name quoting so that it quotes properly in the case of a prefixed schema as well (the schema should not be included in the quotes).
  • Adds a test to the PostgreSQL schema test to verify that this works whether or not a schema is specified before the table name, e.g. test_schema.TableName

My environment:

  • ActiveRecord 2.3.2
  • PostgreSQL 8.2.11
  • "pg" gem 0.8.0
  • MacOS 10.5 Leopard

Comments and changes to this ticket

  • Michael Koziarski

    Michael Koziarski April 5th, 2009 @ 03:57 AM

    Can you rebase these down into a single patch, and ideally someone with the postgres gem can verify it. I'm also pg gem on 10.5

  • Scott Woods

    Scott Woods April 6th, 2009 @ 03:23 AM

    I had to revise my patch a bit, since the patch from #390 got committed yesterday, which implemented even more complete table-name quoting than I had.

    So the new patch does the following:

    • Quotes the table name when casting to regclass so that capitalized table names are supported. Does so using the new quote_table_name method from #390.
    • Adds tests for capitalized table names, including when the occur with prefixed schema names (the purpose of #390).

    I've rebased against master and consolidated into a single diff.

  • Scott Woods

    Scott Woods April 6th, 2009 @ 03:28 AM

    Verified against the postgres (0.7.9.2008.01.28) gem.

    • MacOS 10.5 Leopard
    • PostgreSQL 8.2.11
  • clinton

    clinton April 7th, 2009 @ 06:28 PM

    1. I ran all tests successfully, against the master. Code makes sense.

    Mac OS X 1.5, PostgreSQL 8.3.6.

  • Max Lapshin

    Max Lapshin April 20th, 2009 @ 05:16 PM

    • Tag changed from “activerecord, adapters, camelcase, patch, postgresql, schema, tables” to “activerecord, adapters, bug, camelcase, patch, postgresql, schema, tables, verified”
    • Assigned user set to “Tarmo Tänav”

    I was afraid, that this code will fail, when declaring table in other schema, but it worked for me! Checked against post-2.3.2 master

    P.S. Am I right, changing its state to verified?

  • Repository

    Repository April 21st, 2009 @ 11:51 AM

    • State changed from “new” to “resolved”

    (from [64b33b6cf9db508d2c12394cc1a3f36c91fb2eed]) Quote table names when casting to regclass so that capitalized tables are supported. [#2418 state:resolved]

    Signed-off-by: Tarmo Tänav tarmo@itech.ee http://github.com/rails/rails/co...

  • Repository

    Repository April 21st, 2009 @ 11:52 AM

    (from [70ba90b072025b89248606178ee30d2ff12301c4]) Quote table names when casting to regclass so that capitalized tables are supported. [#2418 state:resolved]

    Signed-off-by: Tarmo Tänav tarmo@itech.ee http://github.com/rails/rails/co...

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

People watching this ticket

Attachments

Tags

Referenced by

Pages