Code

Opened 9 years ago

Closed 7 years ago

Last modified 4 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: master
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: UI/UX:

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@…> 9 years ago.
django-postgresql-schemas-v2.diff (1.3 KB) - added by Landon Fuller <landonf@…> 8 years ago.
PostgreSQL SCHEMA support (Updated)
django_pgsql_schemas_rev4201.patch (2.0 KB) - added by telenieko@… 8 years ago.
Added the same changes on psycopg2
pg_schemas_full_rev4347.diff (2.7 KB) - added by Marc Fargas <telenieko@…> 7 years ago.
All In One, psycop, psycop2 and documentation (settings.txt)
1051.diff (2.4 KB) - added by Marc Fargas <telenieko@…> 7 years ago.
Updated to apply with latest trunk
ticket_1051_rev6098.diff (3.7 KB) - added by roppert <robert@…> 7 years ago.
Patch to apply cleanly against trunk (rev 6098)
ticket_1051_rev6669.diff (4.4 KB) - added by roppert 7 years ago.
Patche done against rev 6669
postgresql_schemas_r11651.diff (4.7 KB) - added by asux 5 years ago.
Patche done against rev 11651 (version 1.1.1)

Download all attachments as: .zip

Change History (28)

Changed 9 years ago by Mao Xizeng <mao.xizeng@…>

comment:1 Changed 8 years ago by adrian

See also [1208].

comment:2 Changed 8 years ago by Landon Fuller <landonf@…>

  • Cc landonf@… added

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

Changed 8 years ago by Landon Fuller <landonf@…>

PostgreSQL SCHEMA support (Updated)

comment:3 Changed 8 years ago by telenieko@…

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

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

Cheers,
Marc.

Changed 8 years ago by telenieko@…

Added the same changes on psycopg2

comment:4 Changed 8 years ago by telenieko@…

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

comment:5 Changed 7 years ago by jacob

  • Needs documentation set
  • Needs tests set
  • Patch needs improvement set
  • Triage Stage changed from Unreviewed to Design decision needed

Changed 7 years ago by Marc Fargas <telenieko@…>

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

comment:6 Changed 7 years ago by Marc Fargas <telenieko@…>

  • Needs documentation unset

Documentation added on latest patch.

comment:7 Changed 7 years ago by 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

comment:8 Changed 7 years ago by anonymous

  • Cc RahmCoff@… added

comment:9 Changed 7 years ago by david@…

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

Changed 7 years ago by Marc Fargas <telenieko@…>

Updated to apply with latest trunk

comment:10 Changed 7 years ago by 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 ? ;)))

comment:11 Changed 7 years ago by 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

comment:12 Changed 7 years ago by 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?

comment:13 Changed 7 years ago by Marc Fargas <telenieko@…>

  • Patch needs improvement unset
  • Summary changed from [patch] Add support for PostgreSQL Schemas to Add 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 Changed 7 years ago by anonymous

  • Cc sam@… added

comment:15 Changed 7 years ago by 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.

Changed 7 years ago by roppert <robert@…>

Patch to apply cleanly against trunk (rev 6098)

Changed 7 years ago by roppert

Patche done against rev 6669

comment:16 Changed 7 years ago by jacob

  • Resolution set to duplicate
  • Status changed from new to closed

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

Changed 5 years ago by asux

Patche done against rev 11651 (version 1.1.1)

comment:17 Changed 5 years ago by akaihola

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 Changed 5 years ago by hlecuanda

  • Cc hector@… added

comment:19 Changed 4 years ago by drdee

  • Cc dvanliere@… added

comment:20 Changed 4 years ago by 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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.