django-pgtrigger

django-pgtrigger copied to clipboard

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                                                      ^

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

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