#35036 closed Bug (duplicate)
sqlsequencereset needs set search_path
Reported by: | Ramon | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 4.2 |
Severity: | Normal | Keywords: | sqlsequencereset postgresql15 search_path schema |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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
Change History (4)
comment:1 by , 12 months ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
follow-up: 3 comment:2 by , 12 months ago
Resolution: | duplicate |
---|---|
Status: | closed → new |
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.
comment:3 by , 12 months ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
Replying to 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.
comment:4 by , 12 months ago
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.
Hello Ramon, thank you for your report.
This seems to be a duplicate of #6148, particularly the latest comment from Simon says: