Add support to set search_path in PostgreSQL

See also [1208].

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

PostgreSQL SCHEMA support (Updated)

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

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

Cheers, Marc.

Added the same changes on psycopg2

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

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

Documentation added on latest patch.

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

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))

Updated to apply with latest trunk

Updated patch to apply cleanly with latest trunk.

In reply to david@dawninglight.net : 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 ? ;)))

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

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?

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 :)

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.

Patch to apply cleanly against trunk (rev 6098)

Patche done against rev 6669

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