Edgewall Software
Modify

Opened 2 years ago

Closed 2 years ago

#12838 closed defect (fixed)

Set default schema

Reported by: Ryan J Ollos Owned by: Ryan J Ollos
Priority: normal Milestone: 1.2.3
Component: database backend Version:
Severity: normal Keywords: postgres
Cc: Branch:
Release Notes:

Use public schema by default for the Postgres database.

API Changes:

Description

The issue described in comment:11:ticket:12643 can be easily reproduced by running the tests using a database URI that doesn't specify a schema (postgres://tracuser:tracpassword@localhost:5432/trac), and the following patch:

  • trac/test.py

    diff --git a/trac/test.py b/trac/test.py
    index a37342048..ea5657021 100755
    a b def get_dburi():  
    284284        # Assume the schema 'tractest' for PostgreSQL
    285285        if scheme == 'postgres' and \
    286286                not db_prop.get('params', {}).get('schema'):
    287             dburi += ('&' if '?' in dburi else '?') + 'schema=tractest'
     287            pass
     288            # dburi += ('&' if '?' in dburi else '?') + 'schema=tractest'
    288289        elif scheme == 'sqlite' and db_prop['path'] != ':memory:' and \
    289290                not db_prop.get('params', {}).get('synchronous'):
    290291            # Speed-up tests with SQLite database

Do we want to set the public schema by default?

Attachments (0)

Change History (7)

comment:1 by Ryan J Ollos, 2 years ago

Possible changes: [fcc95dcae/rjollos.git] (log:rjollos.git:t12838_public_schema). Tests pass for me with Postgres 9.6.3. I still need to check that backup works correctly.

Last edited 2 years ago by Ryan J Ollos (previous) (diff)

comment:2 by Jun Omae, 2 years ago

We could use ANY (current_schemas(FALSE)) rather than self.schema.

  • trac/db/postgres_backend.py

    diff --git a/trac/db/postgres_backend.py b/trac/db/postgres_backend.py
    index fa29276c5..85f93c17c 100644
    a b class PostgreSQLConnection(ConnectionBase, ConnectionWrapper):  
    389389    def get_table_names(self):
    390390        rows = self.execute("""
    391391            SELECT table_name FROM information_schema.tables
    392             WHERE table_schema=%s""", (self.schema,))
     392            WHERE table_schema = ANY (current_schemas(FALSE))""")
    393393        return [row[0] for row in rows]
    394394
    395395    def has_table(self, table):
    396396        rows = self.execute("""
    397397            SELECT EXISTS (SELECT * FROM information_schema.columns
    398                            WHERE table_schema=%s AND table_name=%s)
    399             """, (self.schema, table))
     398                           WHERE table_schema = ANY (current_schemas(FALSE))
     399                           AND table_name=%s)
     400            """, (table,))
    400401        return rows[0][0]
    401402
    402403    def like(self):
psql (9.3.16, server 9.1.20)
SSL connection (cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.

trac=> SELECT current_schemas(FALSE);
 current_schemas
-----------------
 {public}
(1 row)

trac=> SET search_path TO trac_1_2;
SET
trac=> SELECT current_schemas(FALSE);
 current_schemas
-----------------
 {trac_1_2}
(1 row)

trac=> SET search_path TO public;
SET
trac=> SELECT current_schemas(FALSE);
 current_schemas
-----------------
 {public}
(1 row)

comment:3 by Ryan J Ollos, 2 years ago

Milestone: 1.0.151.0.16

That looks like a good change to make. If we really want to keep the statement Trac uses the public schema by default (TracEnvironment#PostgreSQLConnectionString), it looks like additional changes will also be needed: tags/trac-1.2.1/trac/db/postgres_backend.py@:121#L115.

$ trac-admin testenv1 initenv "psql without schema" postgres://tracuser:tracpassword@localhost:5432/trac
Creating and Initializing Project
Initenv for '/Users/rjollos/Documents/Workspace/trac-dev/teo-rjollos.git/testenv1' failed.
Failed to create environment.
no schema has been selected to create in
LINE 1: CREATE TABLE "system" (
                     ^

Traceback (most recent call last):
  File "/Users/rjollos/Documents/Workspace/trac-dev/teo-rjollos.git/trac/admin/console.py", line 500, in do_initenv
    options=options)
  File "/Users/rjollos/Documents/Workspace/trac-dev/teo-rjollos.git/trac/core.py", line 136, in __call__
    self.__init__(*args, **kwargs)
  File "/Users/rjollos/Documents/Workspace/trac-dev/teo-rjollos.git/trac/env.py", line 300, in __init__
    self.create(options)
  File "/Users/rjollos/Documents/Workspace/trac-dev/teo-rjollos.git/trac/env.py", line 592, in create
    DatabaseManager(self).init_db()
  File "/Users/rjollos/Documents/Workspace/trac-dev/teo-rjollos.git/trac/db/api.py", line 340, in init_db
    connector.init_db(**args)
  File "/Users/rjollos/Documents/Workspace/trac-dev/teo-rjollos.git/trac/db/postgres_backend.py", line 127, in init_db
    cursor.execute(stmt)
  File "/Users/rjollos/Documents/Workspace/trac-dev/teo-rjollos.git/trac/db/util.py", line 73, in execute
    return self.cursor.execute(sql)
ProgrammingError: no schema has been selected to create in
LINE 1: CREATE TABLE "system" (
                     ^

How about log:rjollos.git:t12838_public_schema.1 (1.2-stable)? (Minus the change to test.py). A subset of the changes would be needed for 1.0-stable, and a superset for trunk.

Last edited 2 years ago by Ryan J Ollos (previous) (diff)

comment:4 by Jun Omae, 2 years ago

Additional changes in [5ffb6716a/jomae.git]. initenv works well with public schema. I've suggested to use current_schemas() but public schema is unexpectedly used when the specified schema is not created yet. I reverted uses of current_schemas() in the changes.

comment:5 by Ryan J Ollos, 2 years ago

Moving this to 1.2-stable unless anyone thinks it's necessary to fix on 1.0-stable. The simple workaround is to just append ?schema=public to the database URI.

comment:6 by Ryan J Ollos, 2 years ago

Milestone: 1.0.161.2.3

comment:7 by Ryan J Ollos, 2 years ago

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

Committed to 1.2-stable in r16130. Merged to trunk in r16131.

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Ryan J Ollos.
The resolution will be deleted. Next status will be 'reopened'.
to as closed The owner will be changed from Ryan J Ollos 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.