Repository navigation
django.db.utils.ProgrammingError: unterminated dollar-quoted string at or near "$$ #115
Description
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 ^
Activity
wesleykendall commentedon Jun 8, 2023
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
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 commentedon Aug 26, 2024
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 commentedon Aug 4, 2025
@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
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?