django.db.utils.ProgrammingError: unterminated dollar-quoted string at or near "$$

[kwongtn98]

Hi.
When using django-pgtrigger, I'm faced with the following issue
django.db.utils.ProgrammingError: unterminated dollar-quoted string at or near "$$

I've tested using the SQL statement in dbeaver and it works. Then, I tried RunSQL-ing the statement but it does not work.

Help? 😂

---

Migration:

        pgtrigger.migrations.AddTrigger(
            model_name="model",
            trigger=pgtrigger.compiler.Trigger(
                name="model_field_type_read_only",
                sql=pgtrigger.compiler.UpsertTriggerSql(
                    condition='WHEN (OLD."type" IS DISTINCT FROM NEW."type")',
                    func="RAISE EXCEPTION 'pgtrigger: Cannot update rows from % table', TG_TABLE_NAME;",
                    hash="127a9d448a1e1b88696b39448d040280668f9ece",
                    operation="UPDATE",
                    pgid="pgtrigger_model_field_type_read_only_38ed7",
                    table="model",
                    when="BEFORE",
                ),
            ),
        ),

---

List of presumably related packages
Postgres : 15
psycopg : 2.9.5
django-pgtrigger : 4.6.0

---

Error log

[gw2] linux -- Python 3.10.8 /root/.local/share/virtualenvs/code-_Py8Si6I/bin/python

self = <django.db.backends.utils.CursorWrapper object at 0x7f405e118fd0>
sql = '\n            CREATE OR REPLACE FUNCTION "public"._pgtrigger_should_ignore(\n                trigger_name NAME\n            )\n            RETURNS BOOLEAN AS $$\n                DECLARE\n                    _pgtrigger_ignore TEXT[]'
params = ()
ignored_wrapper_args = (False, {'connection': <message.custom_db_engine.base.DatabaseWrapper object at 0x7f4076c8a320>, 'cursor': <django.db.backends.utils.CursorWrapper object at 0x7f405e118fd0>})

    def _execute(self, sql, params, *ignored_wrapper_args):
        self.db.validate_no_broken_transaction()
        with self.db.wrap_database_errors:
            if params is None:
                # params default might be backend specific.
                return self.cursor.execute(sql)
            else:
>               return self.cursor.execute(sql, params)
E               psycopg2.errors.SyntaxError: unterminated dollar-quoted string at or near "$$
E                               DECLARE
E                                   _pgtrigger_ignore TEXT[]"
E               LINE 5:             RETURNS BOOLEAN AS $$
E                                                      ^

/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/django/db/backends/utils.py:84: SyntaxError

The above exception was the direct cause of the following exception:

request = <SubRequest '_django_setup_unittest' for <TestCaseFunction test_datetimerange_no_overlap>>
django_db_blocker = <pytest_django.plugin._DatabaseBlocker object at 0x7f40925718d0>

    @pytest.fixture(autouse=True, scope="class")
    def _django_setup_unittest(
        request,
        django_db_blocker: "_DatabaseBlocker",
    ) -> Generator[None, None, None]:
        """Setup a django unittest, internal to pytest-django."""
        if not django_settings_is_configured() or not is_django_unittest(request):
            yield
            return
    
        # Fix/patch pytest.
        # Before pytest 5.4: https://github.com/pytest-dev/pytest/issues/5991
        # After pytest 5.4: https://github.com/pytest-dev/pytest-django/issues/824
        from _pytest.unittest import TestCaseFunction
        original_runtest = TestCaseFunction.runtest
    
        def non_debugging_runtest(self) -> None:
            self._testcase(result=self)
    
        try:
            TestCaseFunction.runtest = non_debugging_runtest  # type: ignore[assignment]
    
>           request.getfixturevalue("django_db_setup")

/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/pytest_django/plugin.py:490: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/pytest_django/fixtures.py:122: in django_db_setup
    db_cfg = setup_databases(
/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/django/test/utils.py:179: in setup_databases
    connection.creation.create_test_db(
/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/django/db/backends/base/creation.py:74: in create_test_db
    call_command(
/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/django/core/management/__init__.py:181: in call_command
    return command.execute(*args, **defaults)
/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/django/core/management/base.py:398: in execute
    output = self.handle(*args, **options)
/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/django/core/management/base.py:89: in wrapped
    res = handle_func(*args, **kwargs)
/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/django/core/management/commands/migrate.py:244: in handle
    post_migrate_state = executor.migrate(
/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/django/db/migrations/executor.py:117: in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/django/db/migrations/executor.py:147: in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/django/db/migrations/executor.py:227: in apply_migration
    state = migration.apply(state, schema_editor)
/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/django/db/migrations/migration.py:126: in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/pgtrigger/migrations.py:59: in database_forwards
    _add_trigger(schema_editor, model, self.trigger)
/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/pgtrigger/migrations.py:21: in _add_trigger
    schema_editor.execute(trigger.install_sql, params=None)
/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/pgtrigger/migrations.py:405: in execute
    return super().execute(*args, **kwargs)
message/custom_db_engine/base.py:20: in execute
    super(DatabaseSchemaEditor, self).execute(sql, params)
/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/django_multitenant/backends/postgresql/base.py:118: in execute
    super(DatabaseSchemaEditor, self).execute(statement)
/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/django/db/backends/base/schema.py:145: in execute
    cursor.execute(sql, params)
/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/django/db/backends/utils.py:66: in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/django/db/backends/utils.py:75: in _execute_with_wrappers
    return executor(sql, params, many, context)
/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/django/db/backends/utils.py:79: in _execute
    with self.db.wrap_database_errors:
/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.10/site-packages/django/db/utils.py:90: in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <django.db.backends.utils.CursorWrapper object at 0x7f405e118fd0>
sql = '\n            CREATE OR REPLACE FUNCTION "public"._pgtrigger_should_ignore(\n                trigger_name NAME\n            )\n            RETURNS BOOLEAN AS $$\n                DECLARE\n                    _pgtrigger_ignore TEXT[]'
params = ()
ignored_wrapper_args = (False, {'connection': <message.custom_db_engine.base.DatabaseWrapper object at 0x7f4076c8a320>, 'cursor': <django.db.backends.utils.CursorWrapper object at 0x7f405e118fd0>})

    def _execute(self, sql, params, *ignored_wrapper_args):
        self.db.validate_no_broken_transaction()
        with self.db.wrap_database_errors:
            if params is None:
                # params default might be backend specific.
                return self.cursor.execute(sql)
            else:
>               return self.cursor.execute(sql, params)
E               django.db.utils.ProgrammingError: unterminated dollar-quoted string at or near "$$
E                               DECLARE
E                                   _pgtrigger_ignore TEXT[]"
E               LINE 5:             RETURNS BOOLEAN AS $$
E                                                      ^

[wesleykendall]

The only thing I see out of the ordinary here is that you're using django-multitenant. Doesn't totally explain the unquoted dollar sign thing, but I haven't been able to reproduce this.

A few follow up questions

• Would you be able to try the latest Django pgtrigger (4.7)?
• How are you running your test suite? Is it with pytest and pytest-django or the default Django test runner?
• When you installed django-multitenant, did you override your database engine setting to use 'ENGINE': 'django_multitenant.backends.postgresql'?

I believe the custom engine may be the main difference here. It's something I can try testing out to recreate this. Would love to get to the bottom of this

[wesleykendall]

If you have any other information, let me know! I'm unable to reproduce this on my end and haven't heard others hitting this. I will plan on closing this in a month if I don't hear back

[Mapiarz]

@wesleykendall This issue is 100% caused by django-multitenant. Check this out:

https://github.com/citusdata/django-multitenant/blob/895a28fe930992eaf0aab58a2d1fdc4f7a49804a/django_multitenant/backends/postgresql/base.py#L117

It's splitting the statement meant to create the trigger. It's trying to execute something like this

CREATE OR REPLACE FUNCTION "public"._pgtrigger_should_ignore(TRIGGER_NAME NAME) RETURNS BOOLEAN AS $$ DECLARE _pgtrigger_ignore TEXT[]

Clearly this won't work. Many users of django-multitenant are actually not using Citus, so it's ok to remove this hack as a workaround.

@wesleykendall any other ideas how to make django-multitenant and django-pgtrigger play nice?