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

[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
u'postgres://tracuser:password@127.1/tractest'
>>> 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',)]

[Jun Omae]

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

trac/db/postgres_backend.py

@@ -268 +268 @@
     def get_column_names(self, table):
         rows = self.execute("""
             SELECT column_name FROM information_schema.columns
-            WHERE table_schema=%s AND table_name=%s
-            """, (self.schema, table))
+            WHERE table_schema=current_schema() AND table_name=%s
+            """, (table,))
         return [row[0] for row in rows]
 
     def get_last_id(self, cursor, table, column='id'):
@@ -280 +280 @@
     def get_table_names(self):
         rows = self.execute("""
             SELECT table_name FROM information_schema.tables
-            WHERE table_schema=%s""", (self.schema,))
+            WHERE table_schema=current_schema()""")
         return [row[0] for row in rows]

[Jun Omae]

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
u'postgres://tracuser:password@127.1/tractest'
>>> 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']
>>>

[Jun Omae]

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

[Ryan J Ollos]

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.

[Jun Omae]

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