[Django] #35036: sqlsequencereset needs set search_path
Django
-------------------------------------+-------------------------------------
Reporter: Ramon | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 4.2
layer (models, ORM) | Keywords: sqlsequencereset
Severity: Normal | postgresql15 search_path schema
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
These management commands fail:
{{{
./manage.py sqlsequencereset | ./manage.py dbshell
}}}
Generated SQL from sqlsequencereset (Partial4x4 is from my application):
{{{
BEGIN;
SELECT setval(pg_get_serial_sequence('"Partial4x4_partial4x4"','id'),
coalesce(max("id"), 1), max("id") IS NOT null) FROM
"Partial4x4_partial4x4";
COMMIT;
}}}
Example of error:
{{{
BEGIN
ERROR: relation "Partial4x4_partial4x4" does not exist
LINE 1: ...alesce(max("id"), 1), max("id") IS NOT null) FROM "Partial4x...
^
ROLLBACK
}}}
PostgreSQL 15 blocks use of the public schema. This requires use of
database connection options to set the search path.
settings:
{{{
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql',
'OPTIONS': {'options': '-c search_path=your_db_schema'}, #
critical
'NAME': 'your_db_name',
'USER': 'your_db_user',
'PASSWORD': 'your_db_password',
'HOST': 'localhost',
'PORT': '5432'
}
}
}}}
The SQL generated by sqlsequencereset fails because of not inserting a
command to set the search path.
Corrected SQL:
{{{
set search_path="your_db_schema";
BEGIN;
SELECT setval(pg_get_serial_sequence('"Partial4x4_partial4x4"','id'),
coalesce(max("id"), 1), max("id") IS NOT null) FROM
"Partial4x4_partial4x4";
COMMIT;
}}}
Containment that works for me:
{{{
(echo 'set search_path="your_db_schema";'; /manage.py sqlsequencereset
Partial4x4) | ./manage.py dbshell
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35036>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: Ramon | Owner: nobody
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: sqlsequencereset | Triage Stage:
postgresql15 search_path schema | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => duplicate
Comment:
Hello Ramon, thank you for your report.
This seems to be a duplicate of #6148, particularly the
[https://code.djangoproject.com/ticket/6148#comment:188 latest comment
from Simon] says:
> If your project was created on `PostgreSQL < 15` you should not run into
any issues even when upgrading but for projects created on a fresh 15+
install you might have to issue a `GRANT CREATE ON SCHEMA public TO
$your_django_user` until support for `DATABASES['SCHEMA']` is added.
--
Ticket URL: <https://code.djangoproject.com/ticket/35036#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Ramon | Owner: nobody
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
postgresql15 search_path schema | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* resolution: duplicate =>
Comment:
Database was created on PostgreSQL 15 and has a private schema, which used
fine when running the application.
It is only the sqlsequencereset command output piped to dbshell that
fails.
--
Ticket URL: <https://code.djangoproject.com/ticket/35036#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: Ramon | Owner: nobody
Component: Database layer | Version: 4.2
(models, ORM) |
Keywords: sqlsequencereset | Triage Stage:
postgresql15 search_path schema | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => duplicate
Comment:
Replying to [comment:2 Ramon]:
> Database was created on PostgreSQL 15 and has a private schema, which
works fine when running the application.
> It is only the sqlsequencereset command output piped to dbshell that
fails.
It works because you set `-c search_path=your_db_schema`, nonetheless
Django doesn't do anything specific to support it. If you made the effort
to set `search_path` in your `DATABASES` configuration, you can add the
same to the generated script. `sqlsequencereset` is only of things that
may or may not work with `search_path`, that's why it was marked as a
duplicate of #6148.
--
Ticket URL: <https://code.djangoproject.com/ticket/35036#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Ramon | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: sqlsequencereset | Triage Stage:
postgresql15 search_path schema | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Ramon):
Thanks for the explanation. I am not an expert in the field of database
schemas - I am just trying to get it to work and appreciate advise. Based
on the policy change in PostgreSQL 15 my expectation was that Django would
embrace it and add full support for the private schemas, instead of
explaining how to work around it and stick to the public schemas. Consider
this a poke to discuss that strategy internally and feel free to close
this ticket.
--
Ticket URL: <https://code.djangoproject.com/ticket/35036#comment:4>