Setting up pytest-postgresql with pytest-django leads to unreset sequence number

[RemiDesgrange]

I have setup pytest-postgresql with pytest django but sequence (or identity)

ex:

def test1(db):
    m = MyModel.objects.create()
    assert m.id == 1 # => true

def test2(db):
    m = MyModel.objects.create()
    assert m.id == 1 # true ONLY if I run the test alone.

I have set up the db in my conftest.py like this:

import psycopg2
import pytest
from django.db import connections
from pytest_postgresql import factories


def create_postgis_ext(host, port, user, dbname, password):
    with (
        psycopg2.connect(
            dbname=dbname,
            user=user,
            password=password,
            host=host,
            port=port,
        ) as con,
        con.cursor() as con,
    ):
        con.execute("CREATE EXTENSION postgis;")



  postgres_options = (
      "-c wal_level=minimal "
      "-c max_wal_senders=0 "
      "-c fsync=off "
      "-c synchronous_commit=off "
      "-c full_page_writes=off "
      "-c checkpoint_timeout=1d "  # Long timeout to avoid checkpoints
      "-c max_wal_size=10GB "  # Large max WAL size to avoid forced checkpoints
      "-c shared_buffers=256MB "  # Increased shared buffers for better caching
      "-c work_mem=64MB"  # Larger work memory for faster operations
  )
  postgresql_proc_general = factories.postgresql_proc(postgres_options=postgres_options, dbname="general")
  postgresql_proc_gis = factories.postgresql_proc(
      postgres_options=postgres_options, load=[create_postgis_ext], dbname="gis"
  )
  postgresql_proc_othergis = factories.postgresql_proc(
      postgres_options=postgres_options, load=[create_postgis_ext], dbname="othergis"
  )

  @pytest.fixture(scope="session")
  def django_db_setup(request, postgresql_proc_general, postgresql_proc_gis, postgresql_proc_othergis):
      from django.conf import settings

      # remove cached_property of connections.settings from the cache
      del connections.__dict__["settings"]
      db_fixture_mapping = {
          "default": postgresql_proc_general,
          "gis": postgresql_proc_gis,
          "othergis": postgresql_proc_othergis,
      }
      for db, fixture in db_fixture_mapping.items():
          settings.DATABASES[db]["ENGINE"] = "django.contrib.gis.db.backends.postgis"
          settings.DATABASES[db]["NAME"] = fixture.dbname
          settings.DATABASES[db]["USER"] = fixture.user
          settings.DATABASES[db]["PASSWORD"] = fixture.password
          settings.DATABASES[db]["HOST"] = fixture.host
          settings.DATABASES[db]["PORT"] = fixture.port
          settings.DATABASES[db]["OPTIONS"] = {"connect_timeout": 10}
          settings.DATABASES[db]["TEST"]["NAME"] = fixture.dbname

      # re-configure the settings given the changed database config
      connections.settings = connections.configure_settings(settings.DATABASES)
      # open a connection to the database with the new database config
      connections["default"] = connections.create_connection("default")
      yield request.getfixturevalue("django_db_setup")

For this setup, I have read existing issues about setting up custom db (like #1183), as well as a pending PR #1190). I may be missing something. Note that removing the del connections.__dict__["settings"] and connections["default"] = connections.create_connection("default") would break my setup (but I was unable to understand why)

[kingbuzzman]

unreset sequence

? If you run --create-db it will drop the db, and create a fresh one, but I am not aware we reset sequences between tests. Quite frankly, i'm confused by this issue.

seems to me that you make make your life a lot easier by relying on your normal dev postgres connection; django will create test_<name of your db> and then inside django_db_setup just make a cursor call to add gist. Or.. you can rely on your migrations. The point i'm trying to make is that pytest-postgresql seems like a lot of control that 99% of django web app tests dont need.

[RemiDesgrange]

I think I read the doc wrongly, I'm now using django_db_reset_sequence fixture, which is terribly slow, but we shouldn't rely on id anyway 😄 .

The setup is a bit complex that my repro here, some dbs need to be feed with initial data (and django fixture sure doesn't scale), some dbs are provided as external in some tests, etc... What I provided was just a simplified repo.

Thanks for taking time to look into that 🙏