Edgewall Software

#13140 closed defect (fixed)

get_table_names() and get_column_names() wrongly return an empty list when schema is not used on PostgreSQL

Reported by: Jun Omae Owned by: Jun Omae
Priority: normal Milestone: 1.0.18
Component: database backend Version: 1.0.17
Severity: normal Keywords: postgresql
Cc: Branch:
Release Notes:

Fix get_table_names() and get_column_names() when no schema parameter for PostgreSQL.

API Changes:
Internal Changes:

Description (last modified by Jun Omae)

I noticed the issue while investigating th:comment:3:ticket:13321.

Python 2.5.6 (r256:88840, Oct 21 2014, 22:49:55)
[GCC 4.8.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from trac.env import Environment
>>> from trac.db.api import DatabaseManager
>>> env = Environment('/dev/shm/postgres-without-schema')
>>> dbm = DatabaseManager(env)
>>> dbm.connection_uri
>>> with env.db_query as db:
...   db.get_table_names()
>>> with env.db_query as db:
...   db.get_column_names('wiki')
>>> env.db_query("""
...   SELECT table_name FROM information_schema.tables
...   WHERE table_schema=current_schema()
...   ORDER BY table_name
... """)
[(u'attachment',), (u'auth_cookie',), (u'cache',), (u'component',), (u'enum',), (u'milestone',), (u'node_change',), (u'permission',), (u'report',), (u'repository',), (u'revision',), (u'session',), (u'session_attribute',), (u'system',), (u'ticket',), (u'ticket_change',), (u'ticket_custom',), (u'version',), (u'wiki',)]
>>> env.db_query("""
...   SELECT column_name FROM information_schema.columns
...   WHERE table_schema=current_schema() AND table_name=%s
...   """, ('wiki',))
[(u'readonly',), (u'comment',), (u'text',), (u'ipnr',), (u'author',), (u'time',), (u'version',), (u'name',)]

Attachments (0)

Change History (5)

comment:1 by Jun Omae, 18 months ago

Owner: set to Jun Omae
Status: newassigned
Version: 1.0.17

We should use current_schema() function in PostgreSQL, the function is used in drop_table()….

  • trac/db/postgres_backend.py

    diff --git a/trac/db/postgres_backend.py b/trac/db/postgres_backend.py
    index 403ceaba9..cf22a10ff 100644
    a b class PostgreSQLConnection(ConnectionWrapper):  
    268268    def get_column_names(self, table):
    269269        rows = self.execute("""
    270270            SELECT column_name FROM information_schema.columns
    271             WHERE table_schema=%s AND table_name=%s
    272             """, (self.schema, table))
     271            WHERE table_schema=current_schema() AND table_name=%s
     272            """, (table,))
    273273        return [row[0] for row in rows]
    275275    def get_last_id(self, cursor, table, column='id'):
    class PostgreSQLConnection(ConnectionWrapper):  
    280280    def get_table_names(self):
    281281        rows = self.execute("""
    282282            SELECT table_name FROM information_schema.tables
    283             WHERE table_schema=%s""", (self.schema,))
     283            WHERE table_schema=current_schema()""")
    284284        return [row[0] for row in rows]

comment:2 by Jun Omae, 18 months ago

Description: modified (diff)

1.0-stable after the patch:

>>> from trac.env import Environment
>>> env = Environment('/dev/shm/postgres-without-schema')
>>> from trac.db.api import DatabaseManager
>>> DatabaseManager(env).connection_uri
>>> with env.db_query as db: db.get_table_names()
[u'system', u'permission', u'auth_cookie', u'session', u'session_attribute', u'cache', u'attachment', u'wiki', u'repository', u'revision', u'node_change', u'ticket', u'ticket_change', u'ticket_custom', u'enum', u'component', u'milestone', u'version', u'report']
>>> with env.db_query as db: db.get_column_names('wiki')
[u'readonly', u'comment', u'text', u'ipnr', u'author', u'time', u'version', u'name']

comment:3 by Jun Omae, 18 months ago

Proposed changes in [cf470268a/jomae.git]. I'll push the changes by today.

comment:4 by Ryan J Ollos, 16 months ago

I'm going to try and create a release tomorrow. Do you have time to push the changes? Or else I would be happy to push them.

comment:5 by Jun Omae, 16 months ago

Release Notes: modified (diff)
Resolution: fixed
Status: assignedclosed

Committed in [16919] and merged in [16920,16921].

Modify Ticket

Change Properties
Set your email in Preferences
as closed The owner will remain Jun Omae.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from Jun Omae to the specified user.

Add Comment

E-mail address and name can be saved in the Preferences .
Note: See TracTickets for help on using tickets.