CrateDB as timeseries database for Django

Question

I am trying to use CrateDB as timeseries database for Django. I am deploying both services on docker for development.

I started by deploying the following versions:

Django: 4.1.3
CrateDB: 5.1.0

However, I was getting compatibility error: django.db.utils.NotSupportedError: PostgreSQL 11 or later is required (found 10.5).

I have downgraded the Django version to 3.0 where I don't get that error, however now I get this one:

System check identified no issues (0 silenced).
Exception in thread django-main-thread:
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
psycopg2.errors.InternalError_: Unknown function: pg_catalog.pg_table_is_visible(c.oid)
CONTEXT:  io.crate.exceptions.SQLExceptions.esToCrateException(SQLExceptions.java:164)
io.crate.exceptions.SQLExceptions.prepareForClientTransmission(SQLExceptions.java:151)
io.crate.protocols.postgres.Messages.sendErrorResponse(Messages.java:190)
io.crate.protocols.postgres.PostgresWireProtocol.handleSingleQuery(PostgresWireProtocol.java:795)
io.crate.protocols.postgres.PostgresWireProtocol.lambda$handleSimpleQuery$3(PostgresWireProtocol.java:748)
java.base/java.util.concurrent.CompletableFuture.uniComposeStage(CompletableFuture.java:1187)
java.base/java.util.concurrent.CompletableFuture.thenCompose(CompletableFuture.java:2309)
io.crate.protocols.postgres.PostgresWireProtocol.handleSimpleQuery(PostgresWireProtocol.java:748)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.dispatchMessage(PostgresWireProtocol.java:335)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.dispatchState(PostgresWireProtocol.java:325)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.channelRead0(PostgresWireProtocol.java:293)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.channelRead0(PostgresWireProtocol.java:277)
io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:336)
io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:308)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)



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

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/threading.py", line 980, in _bootstrap_inner
    self.run()
  File "/usr/local/lib/python3.9/threading.py", line 917, in run
    self._target(*self._args, **self._kwargs)
  File "/usr/local/lib/python3.9/site-packages/django/utils/autoreload.py", line 64, in wrapper
    fn(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/django/core/management/commands/runserver.py", line 121, in inner_run
    self.check_migrations()
  File "/usr/local/lib/python3.9/site-packages/django/core/management/base.py", line 486, in check_migrations
    executor = MigrationExecutor(connections[DEFAULT_DB_ALIAS])
  File "/usr/local/lib/python3.9/site-packages/django/db/migrations/executor.py", line 18, in __init__
    self.loader = MigrationLoader(self.connection)
  File "/usr/local/lib/python3.9/site-packages/django/db/migrations/loader.py", line 53, in __init__
    self.build_graph()
  File "/usr/local/lib/python3.9/site-packages/django/db/migrations/loader.py", line 220, in build_graph
    self.applied_migrations = recorder.applied_migrations()
  File "/usr/local/lib/python3.9/site-packages/django/db/migrations/recorder.py", line 77, in applied_migrations
    if self.has_table():
  File "/usr/local/lib/python3.9/site-packages/django/db/migrations/recorder.py", line 56, in has_table
    tables = self.connection.introspection.table_names(cursor)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/base/introspection.py", line 52, in table_names
    return get_names(cursor)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/base/introspection.py", line 47, in get_names
    return sorted(ti.name for ti in self.get_table_list(cursor)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/postgresql/introspection.py", line 49, in get_table_list
    cursor.execute("""
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 98, in execute
    return super().execute(sql, params)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.9/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
django.db.utils.InternalError: Unknown function: pg_catalog.pg_table_is_visible(c.oid)
CONTEXT:  io.crate.exceptions.SQLExceptions.esToCrateException(SQLExceptions.java:164)
io.crate.exceptions.SQLExceptions.prepareForClientTransmission(SQLExceptions.java:151)
io.crate.protocols.postgres.Messages.sendErrorResponse(Messages.java:190)
io.crate.protocols.postgres.PostgresWireProtocol.handleSingleQuery(PostgresWireProtocol.java:795)
io.crate.protocols.postgres.PostgresWireProtocol.lambda$handleSimpleQuery$3(PostgresWireProtocol.java:748)
java.base/java.util.concurrent.CompletableFuture.uniComposeStage(CompletableFuture.java:1187)
java.base/java.util.concurrent.CompletableFuture.thenCompose(CompletableFuture.java:2309)
io.crate.protocols.postgres.PostgresWireProtocol.handleSimpleQuery(PostgresWireProtocol.java:748)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.dispatchMessage(PostgresWireProtocol.java:335)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.dispatchState(PostgresWireProtocol.java:325)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.channelRead0(PostgresWireProtocol.java:293)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.channelRead0(PostgresWireProtocol.java:277)
io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:336)
io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:308)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)

Any suggestion?

score 0 · Answer 1 · answered Nov 07 '22 at 16:57

A change has just been made in CrateDB ( https://github.com/crate/crate/pull/13223 ) which will allow returning a different PostgreSQL version number, and this would get us past the first issue.

But even with the latest version of Django we would still see this issue with the call to pg_table_is_visible, this is a function that is not currently implemented in CrateDB, this is tracked under https://github.com/crate/crate/issues/7988 . This particular problem can be bypassed by editing /usr/lib/python3/dist-packages/django/db/backends/postgresql/introspection.py and editing the SQL statements there removing the checks on this function, this should be ok if you are using a single schema and a user with sufficient permissions.

I hope that helps, but depending on what you are trying to do another issue you are likely to encounter is with auto-increment columns, by default Django uses data types such as serial (e.g. in django_migrations) and bigserial (for auto fields), CrateDB is a distributed system and it currently does not have a concept of sequences like that. UUIDs could be used instead (see https://code.djangoproject.com/ticket/32577 ).

CrateDB as timeseries database for Django

1 Answers1