Edgewall Software
Modify

Opened 13 years ago

Closed 13 years ago

Last modified 3 years ago

#3308 closed defect (fixed)

"relation "system" does not exist" when using multiple Postgres schema in same DB

Reported by: virl@… Owned by: Jonas Borgström
Priority: normal Milestone: 0.10
Component: general Version: devel
Severity: major Keywords: schema, relation, system, feedback, postgresql
Cc: Branch:
Release Notes:
API Changes:

Description (last modified by Christian Boos)

When using one Postgres DB with multiple schemas with Trac projects, there is must be "public" schema containing "system" table. In other words, database_version is wrongly checked against public schema, not schema of project.

I think problem is here:

  File "/usr/lib/python2.4/site-packages/trac/env.py", line 235, in get_version
    cursor.execute("SELECT value FROM system WHERE name='database_version'")

Maybe schema specification before "system" needed?

Attachments (0)

Change History (7)

comment:1 by anonymous, 13 years ago

Wrongly checked, I mean.

comment:2 by Jonas Borgström, 13 years ago

Status: newassigned

As soon as a new postgresql connection is established the search path is set to first look in the specified schema and after that "public".

The most likely explanation for this error message is that an incorrect schema name (or non at all) is specified in the database connection string. Can you please verify that you have a connection string like this in your trac.ini:

database = postgres:///database?schema=yourschemaname
or
database = postgres://user:pass@server/database?schema=yourschemaname

comment:3 by Christian Boos, 13 years ago

Description: modified (diff)
Keywords: feedback added

comment:4 by virl@…, 13 years ago

No, database connection string is correct. It is:

database = postgres://tracdbuser:tracdbpwd@localhost/tracdb?schema=myproj

When there are also public schema (created via trac-admin) in database tracdb - everything is fine: it works with "myproj" schema. But after renaming or deleting public schema, trac.cgi prints following for "myproj" project:

Oops...

Trac detected an internal error:

relation "system" does not exist


Traceback (most recent call last):
  File "/var/www/html/dev/yttrium/trac.cgi", line 20, in ?
    cgi_frontend.run()
  File "/usr/lib/python2.4/site-packages/trac/web/cgi_frontend.py", line 68, in run
    gateway.run(dispatch_request)
  File "/usr/lib/python2.4/site-packages/trac/web/wsgi.py", line 87, in run
    response = application(self.environ, self._start_response)
  File "/usr/lib/python2.4/site-packages/trac/web/main.py", line 303, in dispatch_request
    env = _open_environment(env_path, run_once=environ['wsgi.run_once'])
  File "/usr/lib/python2.4/site-packages/trac/web/main.py", line 50, in _open_environment
    return open_environment(env_path)
  File "/usr/lib/python2.4/site-packages/trac/env.py", line 432, in open_environment
    if env.needs_upgrade():
  File "/usr/lib/python2.4/site-packages/trac/env.py", line 312, in needs_upgrade
    if participant.environment_needs_upgrade(db):
  File "/usr/lib/python2.4/site-packages/trac/env.py", line 366, in environment_needs_upgrade
    dbver = self.env.get_version(db)
  File "/usr/lib/python2.4/site-packages/trac/env.py", line 235, in get_version
    cursor.execute("SELECT value FROM system WHERE name='database_version'")
  File "/usr/lib/python2.4/site-packages/trac/db/util.py", line 48, in execute
    return self.cursor.execute(sql)
  File "/usr/lib/python2.4/site-packages/trac/db/util.py", line 48, in execute
    return self.cursor.execute(sql)
ProgrammingError: relation "system" does not exist

But "system" table exist both in "public" and "myproj" schemas and in both of them have correct database_version value.

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

comment:5 by virl, 13 years ago

yttrium == myproj in previous log.

comment:6 by Jonas Borgström, 13 years ago

Resolution: fixed
Status: assignedclosed

Okay, I now think I understand what happens. This error occured because you removed the "public" schema but Trac unnecessarly assumed that this schema would always exist.

As of [3574] Trac no longer assumes that the public schema always exists.

comment:7 by Ryan J Ollos, 5 years ago

Keywords: postgresql added; postgres removed

Modify Ticket

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