Opened 18 years ago

Closed 16 years ago

Last modified 13 years ago

#1051 closed enhancement (duplicate)

Add support to set search_path in PostgreSQL

Reported by: Mao Xizeng <mao.xizeng@…> Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: normal Keywords: PostgreSQL Schemas
Cc: landonf@…, RahmCoff@…, david@…, sam@…, hector@…, dvanliere@… Triage Stage: Design decision needed
Has patch: yes Needs documentation: no
Needs tests: yes Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Attachments (8)

django-postgresql-schemas.diff (1.9 KB ) - added by Mao Xizeng <mao.xizeng@…> 18 years ago.
django-postgresql-schemas-v2.diff (1.3 KB ) - added by Landon Fuller <landonf@…> 18 years ago.
PostgreSQL SCHEMA support (Updated)
django_pgsql_schemas_rev4201.patch (2.0 KB ) - added by telenieko@… 17 years ago.
Added the same changes on psycopg2
pg_schemas_full_rev4347.diff (2.7 KB ) - added by Marc Fargas <telenieko@…> 17 years ago.
All In One, psycop, psycop2 and documentation (settings.txt)
1051.diff (2.4 KB ) - added by Marc Fargas <telenieko@…> 17 years ago.
Updated to apply with latest trunk
ticket_1051_rev6098.diff (3.7 KB ) - added by roppert <robert@…> 17 years ago.
Patch to apply cleanly against trunk (rev 6098)
ticket_1051_rev6669.diff (4.4 KB ) - added by roppert 16 years ago.
Patche done against rev 6669
postgresql_schemas_r11651.diff (4.7 KB ) - added by Alexander Ulyanitsky 14 years ago.
Patche done against rev 11651 (version 1.1.1)

Download all attachments as: .zip

Change History (28)

by Mao Xizeng <mao.xizeng@…>, 18 years ago

comment:1 by Adrian Holovaty, 18 years ago

See also [1208].

comment:2 by Landon Fuller <landonf@…>, 18 years ago

Cc: landonf@… added

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

by Landon Fuller <landonf@…>, 18 years ago

PostgreSQL SCHEMA support (Updated)

comment:3 by telenieko@…, 17 years ago

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

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

Cheers,
Marc.

by telenieko@…, 17 years ago

Added the same changes on psycopg2

comment:4 by telenieko@…, 17 years ago

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

comment:5 by Jacob, 17 years ago

Needs documentation: set
Needs tests: set
Patch needs improvement: set
Triage Stage: UnreviewedDesign decision needed

by Marc Fargas <telenieko@…>, 17 years ago

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

comment:6 by Marc Fargas <telenieko@…>, 17 years ago

Needs documentation: unset

Documentation added on latest patch.

comment:7 by Marc Fargas <telenieko@…>, 17 years ago

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

comment:8 by anonymous, 17 years ago

Cc: RahmCoff@… added

comment:9 by david@…, 17 years ago

Cc: david@… added

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

by Marc Fargas <telenieko@…>, 17 years ago

Attachment: 1051.diff added

Updated to apply with latest trunk

comment:10 by Marc Fargas <telenieko@…>, 17 years ago

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

comment:11 by Birju Prajapati, 17 years ago

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

comment:12 by Birju Prajapati, 17 years ago

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?

comment:13 by Marc Fargas <telenieko@…>, 17 years ago

Patch needs improvement: unset
Summary: [patch] Add support for PostgreSQL SchemasAdd support to set search_path in PostgreSQL

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

comment:14 by anonymous, 17 years ago

Cc: sam@… added

comment:15 by j.states@…, 17 years ago

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.

by roppert <robert@…>, 17 years ago

Attachment: ticket_1051_rev6098.diff added

Patch to apply cleanly against trunk (rev 6098)

by roppert, 16 years ago

Attachment: ticket_1051_rev6669.diff added

Patche done against rev 6669

comment:16 by Jacob, 16 years ago

Resolution: duplicate
Status: newclosed

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

by Alexander Ulyanitsky, 14 years ago

Patche done against rev 11651 (version 1.1.1)

comment:17 by Antti Kaihola, 14 years ago

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.

comment:18 by Hector Lecuanda, 14 years ago

Cc: hector@… added

comment:19 by drdee, 14 years ago

Cc: dvanliere@… added

comment:20 by MarioGonzalez <gonzalemario@…>, 13 years ago

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.

Note: See TracTickets for help on using tickets.
Back to Top