Opened 12 months ago

Closed 12 months ago

Last modified 12 months ago

#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 Natalia Bidart, 12 months ago

Resolution: duplicate
Status: newclosed

Hello Ramon, thank you for your report.

This seems to be a duplicate of #6148, particularly the 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.

comment:2 by Ramon, 12 months ago

Resolution: duplicate
Status: closednew

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.

Version 0, edited 12 months ago by Ramon (next)

in reply to:  2 comment:3 by Mariusz Felisiak, 12 months ago

Resolution: duplicate
Status: newclosed

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 Ramon, 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.

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