Add support to set search_path in PostgreSQL

[Mao Xizeng <mao.xizeng@…>]

Sometimes PostgreSQL schemas feature is very helpful to developers: we can use this feature to maintain multiple versions of databases, or integrate external PostgreSQL databases by cross-schema view without making database organization confusing. So it seems necessary to add PostgreSQL schemas support to django:)

I implemented this feature over only four lines. First, one additional field, "DATABASE_SCHEMAS" in django setting, is used to control PostgreSQL schemas setting, default is blank; second, when initializaing database cursor, three hook codes will double check DATABASE_ENGINE and DATABASE_SCHEMAS, if DATABASE_SCHEMAS is not blank and DATABASE_ENGINE is postgresql, django will set postgresql to appropriate schemas search order.

For PostgreSQL schemas detail, please refer to this url: ​http://www.postgresql.org/docs/8.0/static/ddl-schemas.html

[Adrian Holovaty]

See also [1208].

[Landon Fuller <landonf@…>]

I updated this patch for trunk. It's still only four lines, and it'd be great to get it in.

[Landon Fuller <landonf@…>]

PostgreSQL SCHEMA support (Updated)

[telenieko@…]

adrian: Maybe you were refering to #1208, not [1208] ;)

What's about this patch? It's stopped since june.

Cheers,
Marc.

[telenieko@…]

Added the same changes on psycopg2

[telenieko@…]

Except it does not issue a CREATE SCHEMA (which is fine) it works perfectly.
Please consider applying the patch to trunk.

[Marc Fargas <telenieko@…>]

All In One, psycop, psycop2 and documentation (settings.txt)

[Marc Fargas <telenieko@…>]

Documentation added on latest patch.

[Marc Fargas <telenieko@…>]

On thing missing on this patch is the unit test, one way to achieve that would be simply setting the search_path variable and if no error raises you're done (it's up to postgresql to complain if the schema does not exist or something).

Another way would be "set search_path, create table, do something on table, set search_path to public, try again against table" but It's a test that should be done by postgresql devs, not us ;) We should trust that if search_path is set without trouble we can assume it went fine.

So the unittest for that should simply:

• check that the database backend is psycopg or psycopg2 << otherwise all other backends would fail
• try to set search_path to 'public'
• succeed!!

Is this okay that way?

PS: maybe the default value for DATABASE_SCHEMAS should be "public" so the test would work even if the setting is not defined on settings.py

[david@…]

Since the other bugs have been marked duplicate of this one, I'll post here.

My ideal use of PostgreSQL schemas would be to have each app live in its own schema by default, but with the option of specifying a different one (i.e., Model._meta.db_schema).

These patches provide the ability to have models in separate schemas, but do not change anything else WRT SQL generation. Until either the QuerySet refactor or the multi-db branch hits trunk, the only way to allow per-app schemas by default in postgres is to change the quote_name functions for the 2 pg wrappers (in base.py). You would need to check for a '.' and quote appropriately. This really isn't compatible with how the position taken where django guarantees that it will quote everything sent to the db literally. However, there's really no other way to split out the table name from the schema name.

If the decision is made to go with the concept of just adding simple schema support (a la DATABASE_SCHEMAS), the setting should be a tuple, and the logic in the pg wrappers (base.py) should be:

        if settings.DATABASE_SCHEMAS:
            cursor.execute("SET search_path TO %s;" % ','.join(settings.DATABASE_SCHEMAS))

[Marc Fargas <telenieko@…>]

Updated to apply with latest trunk

[Marc Fargas <telenieko@…>]

Updated patch to apply cleanly with latest trunk.

In reply to david@… :
There's no way, actually to set "application wide preferences" so setting a schema for an application could get a bit weird, maybe some APPLICATION_SCHEMAS = {'myapp': 'myschema'} setting or something like that, but this is out of the scope if this ticket which attemps to provide someway to use schemas in pgsql. So you should rather fill a new ticket and state on it's description very very very clearly that it's not a duplicate of #1051 as it does not attempt the same.

On the other side, any volunteer to write the tests for this so we can stop patching by hand on every svn up ? ;)))

[Birju Prajapati]

Why not have the ability (in settings) to specify a regular expression of what to unquote?
i.e.
DATABASE_UNQUOTE_REGEXP = '\.'
Or even completely switch quoting off?
i.e
DATABASE_QUOTE_NAMES = False

[Birju Prajapati]

Issues with the search path route:

1. When there are two tables of the same name in different schemas you wont be able to access the table later in the search path.
   
2. If you have many apps in one project, importing a model that is in a separate app into the 'current' one could mean that the entire search path chain may have to be searched.
   

I'm not a fan of the project-wide search path as it seems redundant: you can set up a user in Postgres that Django uses and have that user set up using

'ALTER USER BLAH SET SEARCH PATH = ....'

What are the issues in using the db_table meta options by explicitly specifying 'schema.table' and not quoting the first '.' wrt to some setting?

[Marc Fargas <telenieko@…>]

Supporting schemas on per application/model basis is out of the scope of this ticket. This ticket was originally created to support modifying the search_path to have a whole project inside a different schema than public. And as it states "would be useful to developers" i.e. If you are working in multiple projects you can have them in separate schemas without needing different databases and/or users.

Supporting a more granular control of schemas like the per-app idea should be filled as a different ticket.

To get things more clear I'm changing the summary of the ticket to "Support setting search_path on postgresql" which is exactly what the patch does and what the original submiter asked for.

Also removing "Patch needs improvement" as the patch is now completed, only remaining "needs tests"

Side note: Recalling again for somebody with deep knowledge on tests to take a look at the patch and try to write them :)

[j.states@…]

Will Schemas be supported in Django? I svn'd to version 6023 and it killed my development server - I have not tested in Production. I had to rollback to version 6009. Someone please make a decision on this and add it to the svn.

[roppert <robert@…>]

Patch to apply cleanly against trunk (rev 6098)

[roppert]

Patche done against rev 6669

[Jacob]

I've created #6064 to keep track of a general-purpose way of fixing this problem, so I'm marking this as a duplicate.

[Alexander Ulyanitsky]

Patche done against rev 11651 (version 1.1.1)

[Antti Kaihola]

For anyone looking for a quick work-around, MariusBoo ​noted that you can define your table like this:

db_table = '"django"."company"'

This will fool the quote function to think that your table is properly quoted. This also means that you have to specify the schema for each table manually.

I just verified that this solution works with r10837 and r11802 and PostgreSQL 8.3.8.

[MarioGonzalez <gonzalemario@…>]

Adding the workaround wrote by 'akaihola' breaks syncdb due that postgres does't need the schema name into the index name and Django uses app_name + db_table to build the index name

class SomeClass(models.Model):
    name = models.ForeignKey(Other)
    class Meta:
        db_table = 'my_schema"."my_table'

syncdb will attempt to execute:

 CREATE INDEX "my_schema"."my_table_name" ON other.....

What I did was the output produced by "manage.py sql" and psql

$ python manage.py sql myapp | psql <mydb>

Then $ python manage.py sqlindexes modify the content and pass it out to psql again.