update_or_create Tortoise call fails with TransactionManagementError

[andronov04]

Steps to reproduce:

1. Run cli - dipdup -l src/zindexer/logger-local.yml -c src/zindexer/dipdup.yml -c src/zindexer/dipdup-local.yml run

What did you expect to happen:
Successful entry to database

What actually happened:
Got error tortoise.exceptions.TransactionManagementError: current transaction is aborted, commands ignored until end of transaction block

Environment

• Python version:
  3.8.1
• DipDup version or commit hash:
  4.2.3
• Database engine version:
  Postgres 13
• Hasura version:
  v2.1.1-cloud.1
• Are you in docker?
  No
• Can you reproduce this issue on master?
  Yes

Attachment checklist

• General: DipDup logs (~100 lines above stacktrace)

2022-02-09 12:27:35,699 - dipdup.http - DEBUG - HTTP request attempt 1/3
2022-02-09 12:27:35,699 - dipdup.http - DEBUG - Calling `https://crxatorz-test.hasura.app/v1/graphql`
2022-02-09 12:27:35,860 - dipdup.http - DEBUG - Closing gateway session (https://crxatorz-test.hasura.app)
2022-02-09 12:27:35,861 - apscheduler.scheduler - INFO - Scheduler has been shut down
2022-02-09 12:27:35,861 - dipdup.http - DEBUG - Closing gateway session (https://api.hangzhou2net.tzkt.io)
Traceback (most recent call last):
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/tortoise/backends/asyncpg/client.py", line 36, in translate_exceptions_
    return await func(self, *args)
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/tortoise/backends/asyncpg/client.py", line 185, in execute_query
    rows = await connection.fetch(*params)
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/asyncpg/connection.py", line 601, in fetch
    return await self._execute(
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/asyncpg/connection.py", line 1639, in _execute
    result, _ = await self.__execute(
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/asyncpg/connection.py", line 1664, in __execute
    return await self._do_execute(
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/asyncpg/connection.py", line 1711, in _do_execute
    result = await executor(stmt, None)
  File "asyncpg/protocol/protocol.pyx", line 201, in bind_execute
asyncpg.exceptions.InFailedSQLTransactionError: current transaction is aborted, commands ignored until end of transaction block

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/dipdup/context.py", line 385, in _callback_wrapper
    yield
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/dipdup/context.py", line 335, in fire_handler
    await handler_config.callback_fn(new_ctx, *args, **kwargs)
  File "/Users/andronov04/crxatorz/projects/crxatorz-indexer/src/zindexer/handlers/on_update_profile.py", line 27, in on_update_profile
    user, _ = await models.Users.update_or_create(
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/tortoise/models.py", line 1094, in update_or_create
    return await cls.get_or_create(defaults, db, **kwargs)
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/tortoise/models.py", line 1058, in get_or_create
    return await cls.filter(**kwargs).using_db(connection).get(), False
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/tortoise/queryset.py", line 1006, in _execute
    instance_list = await self._db.executor_class(
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/tortoise/backends/base/executor.py", line 130, in execute_select
    _, raw_results = await self.db.execute_query(query.get_sql())
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/tortoise/backends/asyncpg/client.py", line 42, in translate_exceptions_
    raise TransactionManagementError(exc)
tortoise.exceptions.TransactionManagementError: current transaction is aborted, commands ignored until end of transaction block

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

Traceback (most recent call last):
  File "/Users/andronov04/venvs/crxatorz-indexer/bin/dipdup", line 5, in <module>
    cli()
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/asyncclick/core.py", line 1150, in __call__
    return anyio.run(self._main, main, args, kwargs, **({"backend":_anyio_backend} if _anyio_backend is not None else {}))
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/anyio/_core/_eventloop.py", line 56, in run
    return asynclib.run(func, *args, **backend_options)
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/anyio/_backends/_asyncio.py", line 233, in run
    return native_run(wrapper(), debug=debug)
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/asyncio/runners.py", line 43, in run
    return loop.run_until_complete(main)
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/asyncio/base_events.py", line 612, in run_until_complete
    return future.result()
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/anyio/_backends/_asyncio.py", line 228, in wrapper
    return await func(*args)
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/asyncclick/core.py", line 1153, in _main
    return await main(*args, **kwargs)
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/asyncclick/core.py", line 1074, in main
    rv = await self.invoke(ctx)
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/asyncclick/core.py", line 1684, in invoke
    return await _process_result(await sub_ctx.command.invoke(sub_ctx))
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/asyncclick/core.py", line 1420, in invoke
    return await ctx.invoke(self.callback, **ctx.params)
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/asyncclick/core.py", line 774, in invoke
    rv = await rv
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/dipdup/cli.py", line 94, in wrapper
    await fn(*args, **kwargs)
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/dipdup/cli.py", line 215, in run
    await dipdup.run()
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/dipdup/dipdup.py", line 371, in run
    await gather(*tasks)
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/dipdup/dipdup.py", line 123, in run
    await gather(*tasks)
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/dipdup/index.py", line 206, in process
    await self._synchronize(sync_level)
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/dipdup/index.py", line 327, in _synchronize
    await self._process_level_operations(operation_subgroups)
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/dipdup/index.py", line 378, in _process_level_operations
    await self._call_matched_handler(handler_config, operation_subgroup, args)
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/dipdup/index.py", line 517, in _call_matched_handler
    await self._ctx.fire_handler(
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/dipdup/context.py", line 104, in fire_handler
    await self.callbacks.fire_handler(self, name, index, datasource, fmt, *args, **kwargs)
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/dipdup/context.py", line 335, in fire_handler
    await handler_config.callback_fn(new_ctx, *args, **kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/contextlib.py", line 131, in __exit__
    self.gen.throw(type, value, traceback)
  File "/Users/andronov04/venvs/crxatorz-indexer/lib/python3.8/site-packages/dipdup/context.py", line 392, in _callback_wrapper
    raise CallbackError(kind, name) from e
dipdup.exceptions.CallbackError: ('handler', 'on_update_profile')
________________________________________________________________________________

`on_update_profile` handler callback execution failed.

• General: config export output

advanced:
  early_realtime: true
  merge_subscriptions: false
  oneshot: false
  postpone_jobs: false
  reindex: {}
contracts:
  tzc_profile:
    address: ${TZC_PROFILE:-KT1XZ9rgxb2PcNxRgX2CEA9ZiAfiVT6GUB61}
    typename: tzc_profile
database:
  kind: postgres
  host: ....amazonaws.com
  port: 5432
  ...
datasources:
  tzkt_mainnet:
    kind: tzkt
    url: ${TZKT_URL:-https://api.hangzhou2net.tzkt.io}
hooks: {}
indexes:
  z_mainnet:
    contracts:
    - tzc_profile
    datasource: tzkt_mainnet
    first_level: 0
    handlers:
    - callback: on_update_profile
      pattern:
      - destination: tzc_profile
        entrypoint: update
        optional: false
        type: transaction
    kind: operation
    last_level: 0
    types:
    - transaction
jobs: {}
package: zindexer
spec_version: '1.2'
templates: {}
  
  

• General: models.py

from enum import IntEnum

from tortoise import Model
from tortoise import fields


class SwapStatus(IntEnum):
    ACTIVE = 0
    FINISHED = 1
    CANCELED = 2


class Users(Model):
    address = fields.CharField(36, pk=True)
    username = fields.CharField(max_length=36)

    timestamp = fields.DatetimeField()

• SQL issues: schema export output

CREATE TABLE "dipdup_contract" (
    "name" VARCHAR(256) NOT NULL  PRIMARY KEY,
    "address" VARCHAR(256) NOT NULL,
    "typename" VARCHAR(256),
    "created_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE "dipdup_head" (
    "name" VARCHAR(256) NOT NULL  PRIMARY KEY,
    "level" INT NOT NULL,
    "hash" VARCHAR(64) NOT NULL,
    "timestamp" TIMESTAMP NOT NULL,
    "created_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE "dipdup_index" (
    "name" VARCHAR(256) NOT NULL  PRIMARY KEY,
    "type" VARCHAR(9) NOT NULL  /* operation: operation\nbig_map: big_map\nhead: head */,
    "status" VARCHAR(8) NOT NULL  DEFAULT 'NEW' /* NEW: NEW\nSYNCING: SYNCING\nREALTIME: REALTIME\nROLLBACK: ROLLBACK\nONESHOT: ONESHOT */,
    "config_hash" VARCHAR(256) NOT NULL,
    "template" VARCHAR(256),
    "template_values" JSON,
    "level" INT NOT NULL  DEFAULT 0,
    "created_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE "dipdup_schema" (
    "name" VARCHAR(256) NOT NULL  PRIMARY KEY,
    "hash" VARCHAR(256) NOT NULL,
    "reindex" VARCHAR(40)   /* MANUAL: triggered manually from callback\nMIGRATION: applied migration requires reindexing\nROLLBACK: reorg message received and can't be processed\nCONFIG_HASH_MISMATCH: index config has been modified\nSCHEMA_HASH_MISMATCH: database schema has been modified\nBLOCK_HASH_MISMATCH: block hash mismatch, missed rollback when DipDup was stopped\nMISSING_INDEX_TEMPLATE: index template is missing, can't restore index state */,
    "created_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE "users" (
    "address" VARCHAR(36) NOT NULL  PRIMARY KEY,
    "username" VARCHAR(36) NOT NULL,
    "timestamp" TIMESTAMP NOT NULL
);
CREATE TABLE "script" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "timestamp" TIMESTAMP NOT NULL,
    "creator_id" VARCHAR(36) NOT NULL REFERENCES "users" ("address") ON DELETE CASCADE
);
CREATE TABLE "token" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "timestamp" TIMESTAMP NOT NULL,
    "creator_id" VARCHAR(36) NOT NULL REFERENCES "users" ("address") ON DELETE CASCADE,
    "script_id" BIGINT NOT NULL REFERENCES "script" ("id") ON DELETE CASCADE
);
CREATE
OR REPLACE VIEW dipdup_head_status AS
SELECT
    name,
    CASE
        WHEN timestamp < NOW() - interval '3 minutes' THEN 'OUTDATED'
        ELSE 'OK'
    END AS status
FROM
    dipdup_head;

• Hasura issues: Hasura container logs

[droserasprout]

@andronov04, thanks for the report and sorry for the late reply.

Yeah, it's a known Tortoise ORM bug. I tried to fix it by myself, but this issue appeared pretty tricky. The only solution I can suggest for now is not to use update_or_create method 👀

[droserasprout]

Reproducible in 0.19.1, waiting for an upstream.