sqlsequencereset needs set search_path

[Ramon]

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

[Natalia Bidart]

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.

[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.

[Mariusz Felisiak]

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.

[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.