cockroachdb / django-cockroachdb Goto Github PK

View Code? Open in Web Editor NEW

158.0 48.0 28.0 330 KB

CockroachDB Backend for Django

Home Page: http://cockroachlabs.com

License: Apache License 2.0

Python 96.85% Shell 3.15%

django-cockroachdb's Introduction

CockroachDB backend for Django

Prerequisites

You must install:

psycopg, which may have some prerequisites depending on which version you use.

You can also use either:

psycopg2, which has some prerequisites of its own.
psycopg2-binary

The binary package is a practical choice for development and testing but in production it is advised to use the package built from sources.

Install and usage

Use the version of django-cockroachdb that corresponds to your version of Django. For example, to get the latest compatible release for Django 5.0.x:

pip install django-cockroachdb==5.0.*

The minor release number of Django doesn't correspond to the minor release number of django-cockroachdb. Use the latest minor release of each.

Configure the Django DATABASES setting similar to this:

DATABASES = {
    'default': {
        'ENGINE': 'django_cockroachdb',
        'NAME': 'django',
        'USER': 'myprojectuser',
        'PASSWORD': '',
        'HOST': 'localhost',
        'PORT': '26257',
        # If connecting with SSL, include the section below, replacing the
        # file paths as appropriate.
        'OPTIONS': {
            'sslmode': 'verify-full',
            'sslrootcert': '/certs/ca.crt',
            # Either sslcert and sslkey (below) or PASSWORD (above) is
            # required.
            'sslcert': '/certs/client.myprojectuser.crt',
            'sslkey': '/certs/client.myprojectuser.key',
            # If applicable
            'options': '--cluster={routing-id}',
        },
    },
}

If using Kerberos authentication, you can specify a custom service name in 'OPTIONS' using the key 'krbsrvname'.

Notes on Django fields

IntegerField uses the same storage as BigIntegerField so IntegerField is introspected by inspectdb as BigIntegerField.
AutoField and BigAutoField are both stored as integer (64-bit) with DEFAULT unique_rowid().

Notes on Django QuerySets

QuerySet.explain() accepts verbose, types, opt, vec, and distsql options which correspond to CockroachDB's parameters. For example:

>>> Choice.objects.explain(opt=True, verbose=True)
'scan polls_choice\n ├── columns: id:1 question_id:4 choice_text:2 votes:3\n ├── stats: [rows=1]\n ├── cost: 1.1\n ├── key: (1)\n ├── fd: (1)-->(2-4)\n └── prune: (1-4)'

FAQ

GIS support

To use django.contrib.gis with CockroachDB, use 'ENGINE': 'django_cockroachdb_gis' in Django's DATABASES setting.

Disabling CockroachDB telemetry

By default, CockroachDB sends the version of django-cockroachdb that you're using back to Cockroach Labs. To disable this, set DISABLE_COCKROACHDB_TELEMETRY = True in your Django settings.

Known issues and limitations in CockroachDB 23.2.x and earlier

CockroachDB can't disable constraint checking, which means certain things in Django like forward references in fixtures aren't supported.
Migrations have some limitations. CockroachDB doesn't support:
- changing column type if it's part of an index
- dropping or changing a table's primary key
- CockroachDB executes ALTER COLUMN queries asynchronously which is at odds with Django's assumption that the database is altered before the next migration operation begins. CockroachDB will give an error like unimplemented: table <...> is currently undergoing a schema change if a later operation tries to modify the table before the asynchronous query finishes. A future version of CockroachDB may fix this.
The Field.db_comment and Meta.db_table_comment options aren't supported due to poor performance.
Unsupported queries:
- Mixed type addition in SELECT: unsupported binary operator: <int> + <float>
- Division that yields a different type: unsupported binary operator: <int> / <int> (desired <int>)
- The power() database function doesn't accept negative exponents: power(): integer out of range
- sum() doesn't support arguments of different types: sum(): unsupported binary operator: <float> + <int>
- greatest() doesn't support arguments of different types: greatest(): expected <arg> to be of type <type>, found type <other type>
- SmallAutoField generates values that are too large for any corresponding foreign keys.
GIS:
- Some database functions aren't supported: AsGML, AsKML, AsSVG, and GeometryDistance.
- Some 3D functions or signatures aren't supported: ST_3DPerimeter, ST_3DExtent, ST_Scale, and ST_LengthSpheroid.
- The Length database function isn't supported on geodetic fields: st_lengthspheroid(): unimplemented.
- Union may crash with unknown signature: st_union(geometry, geometry).
- The spheroid argument of ST_DistanceSpheroid isn't supported: unknown signature: st_distancespheroid(geometry, geometry, string).
- These lookups aren't supported:

Known issues and limitations in CockroachDB 23.1.x and earlier

CockroachDB doesn't support by ordering by JSON.

django-cockroachdb's People

Contributors

Stargazers

Watchers

django-cockroachdb's Issues

Add retry logic into django-cockroachdb adapter

Provide a decorator allowing users to retry transactions. Handle nesting appropriately.

Interval math across dst works differently from PostgreSQL (and other databases)

(Note that daylight saving time "fall back" is Nov. 7 in 2010.)

On PostgreSQL (no time zone active):

> CREATE TABLE "example" ("id" serial NOT NULL PRIMARY KEY, "a" timestamp with time zone NOT NULL); 
> INSERT INTO "example" ("a") VALUES ('2010-11-03T12:42:10.234567-05:00'::timestamp); 
> SELECT * FROM "example";
"2010-11-03 12:42:10.234567-04"

> UPDATE "example" SET "a" = ("a" + '15 days 0.000000 seconds'::interval);
> SELECT "a" FROM "example";
"2010-11-18 12:42:10.234567-05"
> TRUNCATE "example";

Time zone changes from -04 to -05 but hour remains the same at 12.

With time zone active:

> SET TIME ZONE "America/Chicago";
> INSERT INTO "example" ("a") VALUES ('2010-11-03T12:42:10.234567-05:00'::timestamp); 
> SELECT * FROM "example";
"2010-11-03 12:42:10.234567-05"

> UPDATE "example" SET "a" = ("a" + '15 days 0.000000 seconds'::interval);
> SELECT "a" FROM "example";
"2010-11-18 12:42:10.234567-06"

Time zone changes from -5 to -6 but no hour change.

On cockroachdb:

> CREATE TABLE "example" ("id" integer NOT NULL PRIMARY KEY DEFAULT unique_rowid(), "a" timestamptz NOT NULL);
> INSERT INTO "example" ("a") VALUES ('2010-11-03T12:42:10.234567-05:00'::timestamptz);
> SELECT * FROM "example";
          id         |                a                  
+--------------------+----------------------------------+
  497354447372025857 | 2010-11-03 17:42:10.234567+00:00  
> UPDATE "example" SET "a" = ("a" + '15 days 0.000000 seconds'::interval) WHERE id=497354447372025857;
> SELECT * FROM "example";
          id         |                a                  
+--------------------+----------------------------------+
  497354447372025857 | 2010-11-18 17:42:10.234567+00:00

> TRUNCATE "example";

Fine, but with a time zone active...

> SET TIME ZONE "America/Chicago";
> INSERT INTO "example" ("a") VALUES ('2010-11-03T12:42:10.234567-05:00'::timestamptz);
> SELECT * FROM "example";
          id         |                a                  
+--------------------+----------------------------------+
  497355106025603073 | 2010-11-03 12:42:10.234567-05:00 

>  UPDATE "example" SET "a" = ("a" + '15 days 0.000000 seconds'::interval) WHERE id=497355106025603073;

> SELECT * FROM "example";
          id         |                a                  
+--------------------+----------------------------------+
  497355106025603073 | 2010-11-18 11:42:10.234567-06:00

Time zone changes from -5 to -6 AND hour changes from 12 to 11.

Failing Django test:

======================================================================
FAIL: test_delta_update (expressions.tests.FTimeDeltaTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 59, in testPartExecutor
    yield
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 615, in run
    testMethod()
  File "/home/tim/code/django/tests/expressions/tests.py", line 1232, in test_delta_update
    self.assertEqual(expected_ends, new_ends)
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 839, in assertEqual
    assertion_func(first, second, msg=msg)
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 1045, in assertListEqual
    self.assertSequenceEqual(list1, list2, msg, seq_type=list)
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 1027, in assertSequenceEqual
    self.fail(msg)
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 680, in fail
    raise self.failureException(msg)
AssertionError: Lists differ: [date[198 chars], 10, 14, 9, 24, 15), datetime.datetime(2011, 2, 1, 9, 24, 15)] != [date[198 chars], 10, 14, 9, 24, 15), datetime.datetime(2011, 2, 1, 8, 24, 15)]

First differing element 5:
datetime.datetime(2011, 2, 1, 9, 24, 15)
datetime.datetime(2011, 2, 1, 8, 24, 15)

  [datetime.datetime(2010, 10, 4, 9, 24, 15),
   datetime.datetime(2010, 10, 5, 9, 24, 15, 253000),
   datetime.datetime(2010, 10, 4, 9, 24, 59),
   datetime.datetime(2010, 10, 9, 6, 32, 15),
   datetime.datetime(2010, 10, 14, 9, 24, 15),
-  datetime.datetime(2011, 2, 1, 9, 24, 15)]
?                                ^

+  datetime.datetime(2011, 2, 1, 8, 24, 15)]
?                                ^

Determine if it's possible to support transactions without savepoints

Some discussion at https://code.djangoproject.com/ticket/28263.

Possibly cockroach will add support for savepoints which may obsolete this issue...
cockroachdb/cockroach#10735

Unsupported query: unsupported binary operator: <int> / <int> (desired <int>)

Dividing an integer column by another integer in an UPDATE query crashes:

======================================================================
ERROR: test_lefthand_division (expressions.tests.ExpressionOperatorTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 59, in testPartExecutor
    yield
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 615, in run
    testMethod()
  File "/home/tim/code/django/tests/expressions/tests.py", line 973, in test_lefthand_division
    Number.objects.filter(pk=self.n.pk).update(integer=F('integer') / 2, float=F('float') / 42.7)
  File "/home/tim/code/django/django/db/models/query.py", line 741, in update
    rows = query.get_compiler(self.db).execute_sql(CURSOR)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1429, in execute_sql
    cursor = super().execute_sql(result_type)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1100, in execute_sql
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.DataError: unsupported binary operator: <int> / <int> (desired <int>)

Is there a plan to add support?

SQL:

UPDATE "expressions_number" SET "the_integer" = ("expressions_number"."the_integer" / 2), ... 
WHERE "expressions_number"."id" = 1;

extract() function doesn't support isoyear

PostgreSQL supports an isoyear timespan for the extract() lookup but it looks like cockroachdb doesn't.

======================================================================
ERROR: test_extract_iso_year_func_boundaries (db_functions.datetime.test_extract_trunc.DateFunctionTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.InvalidParameterValue: extract(): unsupported timespan: isoyear


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

Traceback (most recent call last):
  File "/home/tim/code/django/tests/db_functions/datetime/test_extract_trunc.py", line 320, in test_extract_iso_year_func_boundaries
    ], lambda m: (m.start_datetime, m.extracted))
  File "/home/tim/code/django/django/test/testcases.py", line 1047, in assertQuerysetEqual
    items = map(transform, qs)
  File "/home/tim/code/django/django/db/models/query.py", line 274, in __iter__
    self._fetch_all()
  File "/home/tim/code/django/django/db/models/query.py", line 1242, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/tim/code/django/django/db/models/query.py", line 55, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1100, in execute_sql
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.DataError: extract(): unsupported timespan: isoyear

Unsupported queries: column must appear in the GROUP BY clause or be used in an aggregate function

The query runs on PostgreSQL but cockroachdb v19.2.0-beta.20190930 gives an error:

SELECT "defer_simpleitem"."id", "defer_simpleitem"."value", COUNT("defer_feature"."id") AS "feature__count"
FROM "defer_simpleitem" LEFT OUTER JOIN "defer_feature" ON ("defer_simpleitem"."id" = "defer_feature"."item_id")
GROUP BY "defer_simpleitem"."id";

column "value" must appear in the GROUP BY clause or be used in an aggregate function

Schema:

CREATE TABLE public.defer_feature
(
  id integer NOT NULL DEFAULT nextval('defer_feature_id_seq'::regclass),
  item_id integer NOT NULL,
  CONSTRAINT defer_feature_pkey PRIMARY KEY (id),
  CONSTRAINT defer_feature_item_id_ea27b723_fk_defer_simpleitem_id FOREIGN KEY (item_id)
      REFERENCES public.defer_simpleitem (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
CREATE TABLE public.defer_simpleitem
(
  id integer NOT NULL DEFAULT nextval('defer_simpleitem_id_seq'::regclass),
  name character varying(15) NOT NULL,
  value integer NOT NULL,
  CONSTRAINT defer_simpleitem_pkey PRIMARY KEY (id)
)

There are a number of failing queries like this in Django's test suite but I suspect the root cause is the same.

Unsupported query: unknown signature: avg(interval)

Use the AVG database function on an interval column isn't supported by cockroachdb:

SELECT AVG("aggregation_publisher"."duration") AS "duration__avg"
FROM "aggregation_publisher";

It might be possible to workaround this as done for Oracle.

Django test failure:

======================================================================
ERROR: test_avg_duration_field (aggregation.tests.AggregateTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedFunction: unknown signature: avg(interval)


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

Traceback (most recent call last):
  File "/home/tim/code/django/tests/aggregation/tests.py", line 465, in test_avg_duration_field
    Publisher.objects.aggregate(Avg('duration', output_field=DurationField())),
  File "/home/tim/code/django/django/db/models/manager.py", line 82, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/tim/code/django/django/db/models/query.py", line 379, in aggregate
    return query.get_aggregation(self.db, kwargs)
  File "/home/tim/code/django/django/db/models/sql/query.py", line 489, in get_aggregation
    result = compiler.execute_sql(SINGLE)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1100, in execute_sql
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 99, in execute
    return super().execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: unknown signature: avg(interval)

Add a Django feature flag for "only supports UNBOUNDED together with PRECEDING and FOLLOWING"

Django assumes that PostgreSQL is the only database that has the restriction "only supports UNBOUNDED together with PRECEDING and FOLLOWING." See expressions_window.tests.WindowFunctionTests.test_range_n_preceding_and_following, for example. A feature flag could be added to Django and used in its tests. cockroach users will currently see a error message that "PostgreSQL only supports UNBOUNDED together with PRECEDING and FOLLOWING." which could be confusing.

add SSL support

on a secure cluster, getting error if settings.py is missing 'sslmode':'require' option. The error message is

Attaching to lb, cockroach-django_web_1, roach-2, roach-1, roach-0, roach-cert
web_1         | Watching for file changes with StatReloader
web_1         | Performing system checks...
web_1         |
web_1         | System check identified no issues (0 silenced).
web_1         | Exception in thread django-main-thread:
web_1         | Traceback (most recent call last):
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/db/backends/base/base.py", line 220, in ensure_connection
web_1         |     self.connect()
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/utils/asyncio.py", line 24, in inner
web_1         |     return func(*args, **kwargs)
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/db/backends/base/base.py", line 197, in connect
web_1         |     self.connection = self.get_new_connection(conn_params)
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/utils/asyncio.py", line 24, in inner
web_1         |     return func(*args, **kwargs)
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/db/backends/postgresql/base.py", line 185, in get_new_connection
web_1         |     connection = Database.connect(**conn_params)
web_1         |   File "/usr/local/lib/python3.7/site-packages/psycopg2/__init__.py", line 126, in connect
web_1         |     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
web_1         | psycopg2.OperationalError: ERROR:  password authentication failed for user myprojectuser
web_1         | ERROR:  node is running secure mode, SSL connection required
web_1         |
web_1         |
web_1         | The above exception was the direct cause of the following exception:
web_1         |
web_1         | Traceback (most recent call last):
web_1         |   File "/usr/local/lib/python3.7/threading.py", line 926, in _bootstrap_inner
web_1         |     self.run()
web_1         |   File "/usr/local/lib/python3.7/threading.py", line 870, in run
web_1         |     self._target(*self._args, **self._kwargs)
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/utils/autoreload.py", line 53, in wrapper
web_1         |     fn(*args, **kwargs)
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/core/management/commands/runserver.py", line 120, in inner_run
web_1         |     self.check_migrations()
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/core/management/base.py", line 458, in check_migrations
web_1         |     executor = MigrationExecutor(connections[DEFAULT_DB_ALIAS])
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/db/migrations/executor.py", line 18, in __init__
web_1         |     self.loader = MigrationLoader(self.connection)
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/db/migrations/loader.py", line 49, in __init__
web_1         |     self.build_graph()
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/db/migrations/loader.py", line 212, in build_graph
web_1         |     self.applied_migrations = recorder.applied_migrations()
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/db/migrations/recorder.py", line 76, in applied_migrations
web_1         |     if self.has_table():
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/db/migrations/recorder.py", line 56, in has_table
web_1         |     return self.Migration._meta.db_table in self.connection.introspection.table_names(self.connection.cursor())
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/utils/asyncio.py", line 24, in inner
web_1         |     return func(*args, **kwargs)
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/db/backends/base/base.py", line 260, in cursor
web_1         |     return self._cursor()
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/db/backends/base/base.py", line 236, in _cursor
web_1         |     self.ensure_connection()
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/utils/asyncio.py", line 24, in inner
web_1         |     return func(*args, **kwargs)
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/db/backends/base/base.py", line 220, in ensure_connection
web_1         |     self.connect()
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/db/utils.py", line 90, in __exit__
web_1         |     raise dj_exc_value.with_traceback(traceback) from exc_value
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/db/backends/base/base.py", line 220, in ensure_connection
web_1         |     self.connect()
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/utils/asyncio.py", line 24, in inner
web_1         |     return func(*args, **kwargs)
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/db/backends/base/base.py", line 197, in connect
web_1         |     self.connection = self.get_new_connection(conn_params)
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/utils/asyncio.py", line 24, in inner
web_1         |     return func(*args, **kwargs)
web_1         |   File "/usr/local/lib/python3.7/site-packages/django/db/backends/postgresql/base.py", line 185, in get_new_connection
web_1         |     connection = Database.connect(**conn_params)
web_1         |   File "/usr/local/lib/python3.7/site-packages/psycopg2/__init__.py", line 126, in connect
web_1         |     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
web_1         | django.db.utils.OperationalError: ERROR:  password authentication failed for user myprojectuser
web_1         | ERROR:  node is running secure mode, SSL connection required

the message is muted if the following is introduced into settings.py

 'OPTIONS': {
            'sslmode': 'require',
        },

Incorrect interval math on dates in DST

This issue is present on today's build of CockroachDB master that is NOT present in v20.1.0-alpha.20191216 (December 16 build):

When no timezone is active:

> CREATE TABLE "example2" ("id" integer NOT NULL PRIMARY KEY DEFAULT unique_rowid(), "a" date NOT NULL, "b" date NOT NULL);
> INSERT INTO "example2" ("a", "b") VALUES ('2011-03-13'::date, '2011-03-13'::date);
> INSERT INTO "example2" ("a", "b") VALUES ('2011-03-14'::date, '2011-03-14'::date);
> SELECT * FROM "example2" WHERE "a" < ("b" + '0 days 0.000000 seconds'::interval);
  id | a | b
-----+---+----
(0 rows)

But when a timezone is active, any dates after daylight saving time (March 13, 2011) are returned (which is unexpected):

> SET TIME ZONE 'America/Chicago';
> SELECT * FROM "example2" WHERE "a" < ("b" + '0 days 0.000000 seconds'::interval);
          id         |             a             |             b
---------------------+---------------------------+----------------------------
  525452323391143937 | 2011-03-14 00:00:00+00:00 | 2011-03-14 00:00:00+00:00

The relevant Django test:

======================================================================
FAIL: test_date_comparison (expressions.tests.FTimeDeltaTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/tests/expressions/tests.py", line 1334, in test_date_comparison
    self.assertEqual(test_set, self.expnames[:i])
AssertionError: Lists differ: ['e0'] != []

First list contains 1 additional elements.
First extra element 0:
'e0'

- ['e0']
+ []

Add support for StdDev and Variance aggregates

Django and PostgreSQL support both population and sample versions of standard deviation and variance. It looks like cockroachdb only supports one version (population or sample)? The docs don't specify which.

No changes would be needed to Django if the functions are called stddev_samp(), stddev_pop, var_samp(), and var_pop() (matching PostgresSQL).

Correct time zone handling

There are a number of test failures which suggest the current approach to time zones isn't correct.

For example:

======================================================================
FAIL: test_dates_trunc_datetime_fields (dates.tests.DatesTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/test/utils.py", line 373, in inner
    return func(*args, **kwargs)
  File "/home/tim/code/django/tests/dates/tests.py", line 124, in test_dates_trunc_datetime_fields
    datetime.date(2015, 10, 22),
AssertionError: Sequences differ: <QuerySet [datetime.date(2015, 10, 20), datetime.date(2015, 10, 21)]> != [datetime.date(2015, 10, 21), datetime.date(2015, 10, 22)]

First differing element 0:
datetime.date(2015, 10, 20)
datetime.date(2015, 10, 21)

- <QuerySet [datetime.date(2015, 10, 20), datetime.date(2015, 10, 21)]>
? ----------                          ^                            ^  -

+ [datetime.date(2015, 10, 21), datetime.date(2015, 10, 22)]
?                           ^                            ^


======================================================================
FAIL: test_related_model_traverse (dates.tests.DatesTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/tests/dates/tests.py", line 49, in test_related_model_traverse
    datetime.date(2010, 1, 1),
AssertionError: Sequences differ: <QuerySet [datetime.date(2004, 12, 31), datetime.date(2009, 12, 31)]> != [datetime.date(2005, 1, 1), datetime.date(2010, 1, 1)]

First differing element 0:
datetime.date(2004, 12, 31)
datetime.date(2005, 1, 1)

- <QuerySet [datetime.date(2004, 12, 31), datetime.date(2009, 12, 31)]>
? ----------                  ^   -  -                     -   -  -   -

+ [datetime.date(2005, 1, 1), datetime.date(2010, 1, 1)]
?                   ^                         +

Unsupported query: mixed type addition in SELECT

For example:

======================================================================
ERROR: test_mixed_type_annotation_numbers (annotations.tests.NonAggregateAnnotationTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.InvalidParameterValue: unsupported binary operator: <int> + <float>

Is there a plan to add support?

The SQL adds integer and float columns in a SELECT clause:

SELECT ... ("annotations_book"."pages" + "annotations_book"."rating") AS "combined"
FROM "annotations_book"

Django tests have data persisting across tests

It seems like the current client does not clean up the database correctly in between tests, leading to tests that assert output equality of different queries to fail.

Reproduction:

Using the django test suite --

./runtests.py postgres_tests.test_array.TestSaveLoad.test_tuples --parallel 1

succeeds, while running the entire test class.

./runtests.py postgres_tests.test_array.TestSaveLoad --parallel 1

fails. With more inspection, you can see that the IntegerArrayModel's table has rows in it at the beginning of the test when running the full test class.

DATE_TRUNC result is incorrectly localized when a timezone is set

When no timezone is active, hour is zero as expected:

> CREATE TABLE "timezones_event" ("id" integer NOT NULL PRIMARY KEY DEFAULT unique_rowid(), "dt" timestamptz NOT NULL);
> INSERT INTO "timezones_event" ("dt") VALUES ('2011-01-01T01:30:00+00:00'::timestamptz);
> SELECT DISTINCT DATE_TRUNC('year', "timezones_event"."dt") AS "datetimefield" FROM "timezones_event" WHERE "timezones_event"."dt" IS NOT NULL ORDER BY "datetimefield" ASC;
        datetimefield        
+---------------------------+
  2011-01-01 00:00:00+00:00

But when a timezone is active ('Africa/Nairobi' is UTC+3), the hour of the result is localized:

> SET TIME ZONE 'Africa/Nairobi';
> SELECT DISTINCT DATE_TRUNC('year', "timezones_event"."dt") AS "datetimefield" FROM "timezones_event" WHERE "timezones_event"."dt" IS NOT NULL ORDER BY "datetimefield" ASC;
        datetimefield        
+---------------------------+
  2011-01-01 03:00:00+03:00

I believe this is a bug in cockroachdb.

The relevant Django test:

======================================================================
FAIL: test_query_datetimes (timezones.tests.LegacyDatabaseTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/tests/timezones/tests.py", line 180, in test_query_datetimes
    self.assertSequenceEqual(Event.objects.datetimes('dt', 'year'), [datetime.datetime(2011, 1, 1, 0, 0, 0)])
AssertionError: Sequences differ: <QuerySet [datetime.datetime(2010, 1, 1, 3, 0)]> != [datetime.datetime(2011, 1, 1, 0, 0)]

First differing element 0:
datetime.datetime(2010, 1, 1, 3, 0)
datetime.datetime(2011, 1, 1, 0, 0)

- <QuerySet [datetime.datetime(2010, 1, 1, 3, 0)]>
+ [datetime.datetime(2011, 1, 1, 0, 0)]

Incorrect interval math on date columns when a time zone is set

When no timezone is active:

> CREATE TABLE "example" ("id" integer NOT NULL PRIMARY KEY DEFAULT unique_rowid(), "a" date NOT NULL, "b" date NOT NULL);
> INSERT INTO "example" ("a", "b") VALUES ('2011-01-01'::date, '2011-01-01'::date);
> SELECT * FROM "example" WHERE "a" <= ("b" + '0 days 0.000000 seconds'::interval);
          id         |             a             |             b              
+--------------------+---------------------------+---------------------------+
  497332820171915265 | 2011-01-01 00:00:00+00:00 | 2011-01-01 00:00:00+00:00

But when a timezone is active, no results are returned:

> SET TIME ZONE 'America/Chicago';
> SELECT * FROM "example" WHERE "a" <= ("b" + '0 days 0.000000 seconds'::interval);
  id | a | b  
+----+---+---+
(0 rows)

I believe this is a bug in cockroachdb.

The relevant Django test:

======================================================================
FAIL: test_date_comparison (expressions.tests.FTimeDeltaTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 59, in testPartExecutor
    yield
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 615, in run
    testMethod()
  File "/home/tim/code/django/tests/expressions/tests.py", line 1197, in test_date_comparison
    self.assertEqual(test_set, self.expnames[:i + 1])
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 839, in assertEqual
    assertion_func(first, second, msg=msg)
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 1045, in assertListEqual
    self.assertSequenceEqual(list1, list2, msg, seq_type=list)
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 1027, in assertSequenceEqual
    self.fail(msg)
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 680, in fail
    raise self.failureException(msg)
AssertionError: Lists differ: [] != ['e0']

Second list contains 1 additional elements.
First extra element 0:
'e0'

- []
+ ['e0']

Test failures due to cockroachdb ordering differences

Some tests fail because cockroackdb orders query results differently than other databases. This issue will keep track of those tests and I'll submit a patch to Django to relax the test assertions.

======================================================================
FAIL: test_union (filtered_relation.tests.FilteredRelationTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/test/testcases.py", line 1229, in skip_wrapper
    return test_func(*args, **kwargs)
  File "/home/tim/code/django/tests/filtered_relation/tests.py", line 200, in test_union
    self.assertSequenceEqual(qs1.union(qs2), [self.author1, self.author2])
AssertionError: Sequences differ: <QuerySet [<Author: Jane>, <Author: Alice>]> != [<Author: Alice>, <Author: Jane>]

First differing element 0:
<Author: Jane>
<Author: Alice>

- <QuerySet [<Author: Jane>, <Author: Alice>]>
+ [<Author: Alice>, <Author: Jane>]

No support for order by ... nulls first / last

Cockroachdb doesn't support ORDER BY with NULLS FIRST or NULLS LAST (cockroachdb/cockroach#6224). If the feature won't be added, we could add a feature flag to Django to skip these tests and raise a helpful error message if the programmer tries to use this feature in Django.

Example traceback:

======================================================================
ERROR: test_order_by_nulls_first (ordering.tests.OrderingTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.SyntaxError: at or near "nulls": syntax error
DETAIL:  source SQL:
SELECT "ordering_article"."id", "ordering_article"."author_id", "ordering_article"."second_author_id", "ordering_article"."headline", "ordering_article"."pub_date" FROM "ordering_article" ORDER BY "ordering_article"."author_id" ASC NULLS FIRST, "ordering_article"."headline" ASC
                                                                                                                                                                                                                                        ^


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

Traceback (most recent call last):
  File "/home/tim/code/django/tests/ordering/tests.py", line 127, in test_order_by_nulls_first
    [self.a1, self.a2, self.a3, self.a4],
  File "/home/tim/code/django/tests/ordering/tests.py", line 97, in assertQuerysetEqualReversible
    self.assertSequenceEqual(queryset, sequence)
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 957, in assertSequenceEqual
    len1 = len(seq1)
  File "/home/tim/code/django/django/db/models/query.py", line 256, in __len__
    self._fetch_all()
  File "/home/tim/code/django/django/db/models/query.py", line 1242, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/tim/code/django/django/db/models/query.py", line 55, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1100, in execute_sql
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: at or near "nulls": syntax error
DETAIL:  source SQL:
SELECT "ordering_article"."id", "ordering_article"."author_id", "ordering_article"."second_author_id", "ordering_article"."headline", "ordering_article"."pub_date" FROM "ordering_article" ORDER BY "ordering_article"."author_id" ASC NULLS FIRST, "ordering_article"."headline" ASC
                                                                                                                                                                                                                                        ^

Unsupported query in DatabaseIntrospection.get_constraints()

The query that Django uses to introspect indexes is unsupported:

SELECT
    indexname, array_agg(attname ORDER BY arridx), indisunique, indisprimary,
    array_agg(ordering ORDER BY arridx), amname, exprdef, s2.attoptions
FROM (
    SELECT
        c2.relname as indexname, idx.*, attr.attname, am.amname,
        CASE
            WHEN idx.indexprs IS NOT NULL THEN
                pg_get_indexdef(idx.indexrelid)
        END AS exprdef,
        CASE am.amname
            WHEN 'btree' THEN
                CASE (option & 1)
                    WHEN 1 THEN 'DESC' ELSE 'ASC'
                END
        END as ordering,
        c2.reloptions as attoptions
    FROM (
        SELECT *
        FROM pg_index i, unnest(i.indkey, i.indoption) WITH ORDINALITY koi(key, option, arridx)
    ) idx
    LEFT JOIN pg_class c ON idx.indrelid = c.oid
    LEFT JOIN pg_class c2 ON idx.indexrelid = c2.oid
    LEFT JOIN pg_am am ON c2.relam = am.oid
    LEFT JOIN pg_attribute attr ON attr.attrelid = c.oid AND attr.attnum = idx.key
    WHERE c.relname = %s
) s2
GROUP BY indexname, indisunique, indisprimary, amname, exprdef, attoptions;

Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.FeatureNotSupported: syntax error: unimplemented: unimplemented at or near ")"
DETAIL:  source SQL:
SELECT
                indexname, array_agg(attname ORDER BY arridx), indisunique, indisprimary,
                                                            ^
HINT:  See: https://github.com/cockroachdb/cockroach/issues/23620

It's unclear to me if there's an outstanding cockroachdb issue that'll allow this or if an alternate query must be written. The hint issue (cockroachdb/cockroach#23620) is closed.

Unsupported query: unknown signature: extract(string, interval)

When using extract() on a DurationField:

======================================================================
ERROR: test_extract_duration (db_functions.datetime.test_extract_trunc.DateFunctionTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedFunction: unknown signature: extract(string, interval)


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

Traceback (most recent call last):
  File "/home/tim/code/django/django/test/testcases.py", line 1229, in skip_wrapper
    return test_func(*args, **kwargs)
  File "/home/tim/code/django/tests/db_functions/datetime/test_extract_trunc.py", line 242, in test_extract_duration
    lambda m: (m.start_datetime, m.extracted)
  File "/home/tim/code/django/django/test/testcases.py", line 1047, in assertQuerysetEqual
    items = map(transform, qs)
  File "/home/tim/code/django/django/db/models/query.py", line 274, in __iter__
    self._fetch_all()
  File "/home/tim/code/django/django/db/models/query.py", line 1242, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/tim/code/django/django/db/models/query.py", line 55, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1100, in execute_sql
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: unknown signature: extract(string, interval)

Is this a missing feature of cockroachdb or is it required (correct?) to use the extract_duration() function instead of extract()?

Sample SQL:

SELECT ... EXTRACT('second' FROM "db_functions_dtmodel"."duration") AS "extracted"
FROM "db_functions_dtmodel"

an active time zone incorrectly changes interval math in UPDATE

On CockroachDB master (a regression after cockroachdb/cockroach@917005b, perhaps):

> CREATE TABLE "example" ("id" integer NOT NULL PRIMARY KEY DEFAULT unique_rowid(), "start" timestamptz NOT NULL, "end" timestamptz NOT NULL);
> INSERT INTO "example" ("start", "end") VALUES ('2010-06-25T12:15:30.747000-05:00'::timestamptz, '2010-06-25T12:15:30.747000-05:00'::timestamptz);
> UPDATE "example" SET "end" = ("end" + '104166 days 57599.999999 seconds'::interval) WHERE true; 
> SELECT ("end" - "start") AS "delta" FROM "example";
2499999:59:59.999999

That's the expected result. Now setting a time zone before the update:

> TRUNCATE "example";
> INSERT INTO "example" ("start", "end") VALUES ('2010-06-25T12:15:30.747000-05:00'::timestamptz, '2010-06-25T12:15:30.747000-05:00'::timestamptz);
> SET TIME ZONE 'America/Chicago';
> UPDATE "example" SET "end" = ("end" + '104166 days 57599.999999 seconds'::interval) WHERE true;
> SELECT ("end" - "start") AS "delta" FROM "example";
         delta
------------------------
  2500000:59:59.999999

The result is expected to stay the same.

The failing Django test:

======================================================================
FAIL: test_datetime_subtraction_microseconds (expressions.tests.FTimeDeltaTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/test/testcases.py", line 1247, in skip_wrapper
    return test_func(*args, **kwargs)
  File "/home/tim/code/django/tests/expressions/tests.py", line 1516, in test_datetime_subtraction_microseconds
    self.assertEqual(e.delta, delta)
AssertionError: datetime.timedelta(days=104166, seconds=61199, microseconds=999999) != datetime.timedelta(days=104166, seconds=57599, microseconds=999999)

extract() function doesn't respect active time zone

When no timezone is active:

> CREATE TABLE "timezones_event" ("id" integer NOT NULL PRIMARY KEY DEFAULT unique_rowid(), "dt" timestamptz NOT NULL);
> INSERT INTO "timezones_event" ("dt") VALUES ('2011-01-01T01:30:00+00:00'::timestamptz);
> SELECT dt, EXTRACT(hour FROM dt) FROM timezones_event;
             dt             | extract  
+---------------------------+---------+
  2011-01-01 01:30:00+00:00 |       1

But when a timezone is active ('Africa/Nairobi' is UTC+3), the extracted value isn't localized:

> SET TIME ZONE 'Africa/Nairobi';
> SELECT dt, EXTRACT(hour FROM dt) FROM timezones_event;
             dt             | extract  
+---------------------------+---------+
  2011-01-01 04:30:00+03:00 |       1  
(1 row)

I believe this is a bug in cockroachdb.

The relevant Django test:

======================================================================
FAIL: test_extract_func (db_functions.datetime.test_extract_trunc.DateFunctionTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/tests/db_functions/datetime/test_extract_trunc.py", line 204, in test_extract_func
    lambda m: (m.start_datetime, m.extracted)
  File "/home/tim/code/django/django/test/testcases.py", line 1056, in assertQuerysetEqual
    return self.assertEqual(list(items), values, msg=msg)
AssertionError: Lists differ: [(dat[42 chars]1), 19), (datetime.datetime(2016, 6, 15, 14, 10, 50, 123), 19)] != [(dat[42 chars]1), 14), (datetime.datetime(2016, 6, 15, 14, 10, 50, 123), 14)]

First differing element 0:
(datetime.datetime(2015, 6, 15, 14, 30, 50, 321), 19)
(datetime.datetime(2015, 6, 15, 14, 30, 50, 321), 14)

- [(datetime.datetime(2015, 6, 15, 14, 30, 50, 321), 19),
?                                                     ^

+ [(datetime.datetime(2015, 6, 15, 14, 30, 50, 321), 14),
?                                                     ^

-  (datetime.datetime(2016, 6, 15, 14, 10, 50, 123), 19)]
?                                                     ^

+  (datetime.datetime(2016, 6, 15, 14, 10, 50, 123), 14)]
?                                                     ^

bug: model def str(self): return self.id

from django.db import models


class Blog(models.Model):
    title = models.CharField(max_length=200)
    content = models.TextField()

    def __str__(self):
        return self.id

In django admin click blog, show error:


Request Method: | GET
-- | --
http://127.0.0.1:8000/admin/blog/blog/513102869275475969/change/
3.0
TypeError
__str__ returned non-string (type int)
C:\Users\jet\.virtualenvs\djcockroachdbtest-XqUQjpw2\lib\site-packages\django\template\defaultfilters.py in _dec, line 42
C:\Users\jet\.virtualenvs\djcockroachdbtest-XqUQjpw2\Scripts\python.exe
3.7.2
['D:\\webprojects\\djcockroachdbtest',  'D:\\webprojects\\djcockroachdbtest',  'C:\\Program Files\\JetBrains\\PyCharm '  '2019.2.1\\plugins\\python\\helpers\\pycharm_display',  'C:\\Users\\jet\\.virtualenvs\\djcockroachdbtest-XqUQjpw2\\Scripts\\python37.zip',  'C:\\Users\\jet\\.virtualenvs\\djcockroachdbtest-XqUQjpw2\\DLLs',  'C:\\Users\\jet\\.virtualenvs\\djcockroachdbtest-XqUQjpw2\\lib',  'C:\\Users\\jet\\.virtualenvs\\djcockroachdbtest-XqUQjpw2\\Scripts',  'C:\\Users\\jet\\AppData\\Local\\Programs\\Python\\Python37\\Lib',  'C:\\Users\\jet\\AppData\\Local\\Programs\\Python\\Python37\\DLLs',  'C:\\Users\\jet\\.virtualenvs\\djcockroachdbtest-XqUQjpw2',  'C:\\Users\\jet\\.virtualenvs\\djcockroachdbtest-XqUQjpw2\\lib\\site-packages',  'C:\\Program Files\\JetBrains\\PyCharm '  '2019.2.1\\plugins\\python\\helpers\\pycharm_matplotlib_backend']
Wed, 18 Dec 2019 08:58:03 +0000

Add support for EXPLAIN options

Django's QuerySet.explain() allows options like Tag.objects.filter(name='test').explain(verbose=True) but this doesn't generate the correct SQL for cockroachdb.

For example, EXPLAIN (VERBOSE true) ... should be EXPLAIN (VERBOSE) ....

The cockroach adapter needs to override DatabaseOperations.explain_query_prefix().

Relevant Django commit: django/django@c1c163b

Nondeterministic query: test_slice_subquery_and_query

This query seems nondeterministic, sometimes returning 2, sometimes returning 3.

> CREATE TABLE "id_only" ("id" integer NOT NULL PRIMARY KEY DEFAULT unique_rowid());
> INSERT INTO "id_only" ("id") VALUES (1);
> INSERT INTO "id_only" ("id") VALUES (2);
> INSERT INTO "id_only" ("id") VALUES (3);
> INSERT INTO "id_only" ("id") VALUES (4);
> SELECT "id_only"."id" FROM "id_only" WHERE "id_only"."id" IN (SELECT "id_only"."id" FROM "id_only" ORDER BY "id_only"."id" DESC  LIMIT 2 OFFSET 1)  LIMIT 2 OFFSET 1;
  id  
+----+
   2

Failing Django test:

======================================================================
FAIL: test_slice_subquery_and_query (queries.tests.SubqueryTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/tests/queries/tests.py", line 2085, in test_slice_subquery_and_query
    self.assertEqual({x.id for x in query}, {3})
AssertionError: Items in the first set but not the second:
2
Items in the second set but not the first:
3

Rename engine

Currently the Python package as structured as 'cockroach.django', however, there's unlikely to be any other sub-modules, so I wonder if we should drop .django and simply have cockroach. This affects the value the user puts in their DATABASES setting under ENGINE. Currently:

DATABASES = {
    'default': {
        'ENGINE': 'cockroach.django',
        ....
    },
}

Other third-party engine names: sql_server.pyodbc, firebird, django_pyodbc, sqlserver_ado

Wrong URL path syntax in django tutorial

The URL syntax in the tutorial uses from django.conf.urls import url which is the old import for regex based paths on old versions of Django, but it uses the new syntax. The django.urls.path function was probably intended.

Misleading error message for nonexistent database

A FAQ to be documented or an error message to be improved in cockroachdb. When accessing cockroachdb from Django, if the database doesn't exist, we get an error message like this:

$ python manage.py makemigrations
Traceback (most recent call last):
  File "/home/ubuntu/myproject/myprojectenv/lib/python3.5/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
psycopg2.errors.InvalidName: no database specified

To resolve this, use cockroach sql --insecure and run CREATE DATABASE ...;

I'm not the only who was misled.

Unsupported query: greatest(): expected avg() to be of type float, found type decimal

It looks like the GREATEST function can't compare mixed number types:

SELECT ... GREATEST(AVG("aggregation_book"."rating"), AVG("aggregation_book"."price")) AS "price_or_median"
FROM "aggregation_publisher"
LEFT OUTER JOIN "aggregation_book" ON ("aggregation_publisher"."id" = "aggregation_book"."publisher_id")
GROUP BY "aggregation_publisher"."id"
HAVING GREATEST(AVG("aggregation_book"."rating"), AVG("aggregation_book"."price")) >= ("aggregation_publisher"."num_awards")
ORDER BY "aggregation_publisher"."num_awards" ASC;

rating is FloatField
price is DecimalField

Django test failure:

======================================================================
ERROR: test_expression_on_aggregation (aggregation.tests.AggregateTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.InvalidParameterValue: greatest(): expected avg(price) to be of type float, found type decimal


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

Traceback (most recent call last):
  File "/home/tim/code/django/tests/aggregation/tests.py", line 1100, in test_expression_on_aggregation
    qs, [1, 3, 7, 9], lambda v: v.num_awards)
  File "/home/tim/code/django/django/test/testcases.py", line 1047, in assertQuerysetEqual
    items = map(transform, qs)
  File "/home/tim/code/django/django/db/models/query.py", line 274, in __iter__
    self._fetch_all()
  File "/home/tim/code/django/django/db/models/query.py", line 1242, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/tim/code/django/django/db/models/query.py", line 55, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1100, in execute_sql
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 99, in execute
    return super().execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.DataError: greatest(): expected avg(price) to be of type float, found type decimal

[bug] Django admin user detail

When I try to look at User detail in admin interface I get SQL error.

syntax error at or near "close" DETAIL: source SQL: CLOSE "_django_curs_139917681047296_1" ^ 


The above exception (syntax error at or near "declare" DETAIL: source SQL: DECLARE "_django_curs_139917681047296_1" NO SCROLL CURSOR WITH HOLD FOR SELECT "auth_group"."id", "auth_group"."name" FROM "auth_group" ORDER BY "auth_group"."name" ASC ^ ) was the direct cause of the following exception:

When I remove from fields groups and user_permissions it all goes away. I use Django 2.2.2 on Python 3.7.3, Cockroach 19.1.2 and latest psycopg2 2.8.3. This is how my model looks like. This is not build in user model but substituted custom one.

class UserAccount(AbstractBaseUser, PermissionsMixin):
    email = models.EmailField(
        max_length=254, unique=True, error_messages={"unique": "Account with this email already exists."}
    )
    email_secret_link = models.CharField(max_length=50)

    is_staff = models.BooleanField(default=False)
    is_admin = property(lambda self: self.is_superuser)
    is_active = models.BooleanField(default=True)

    date_email_verified = models.DateTimeField(null=True, blank=True)
    date_created = models.DateTimeField(auto_now_add=True)

    organization = models.ForeignKey("account.Organization", null=True, blank=True, on_delete=models.CASCADE)

    objects = BaseAccountManager()

    USERNAME_FIELD = "email"

This is the admin.

@admin.register(UserAccount)
class UserAdmin(BaseUserAdmin):
    # The forms to add and change user instances
    form = UserChangeForm
    add_form = CreateUserForm

    # The fields to be used in displaying the User model.
    # These override the definitions on the base UserAdmin
    # that reference specific fields on auth.User.
    list_display = ("email", "is_active", "is_superuser", "date_created")
    list_filter = ("is_active", "groups")
    fieldsets = (
        (None, {"fields": ("email", "password")}),
        (_("Permissions"), {"fields": ("is_active", "is_superuser", "groups", "user_permissions")}),
        (_("Important dates"), {"fields": ("last_login", "date_created", "date_email_verified")}),
    )

    readonly_fields = ("date_created", "date_email_verified")
    # add_fieldsets is not a standard ModelAdmin attribute. UserAdmin
    # overrides get_fieldsets to use this attribute when creating a user.
    add_fieldsets = ((None, {"classes": ("wide",), "fields": ("email", "password1", "password2")}),)
    search_fields = ("email",)
    ordering = ("email",)
    filter_horizontal = ("user_permissions",)

Unknown signatures for math database functions

There are a number of errors similar to this:

======================================================================
ERROR: test_null (db_functions.math.test_sqrt.SqrtTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedFunction: unknown signature: sqrt(int)


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

Traceback (most recent call last):
  File "/home/tim/code/django/tests/db_functions/math/test_sqrt.py", line 16, in test_null
    obj = IntegerModel.objects.annotate(null_sqrt=Sqrt('normal')).first()
  File "/home/tim/code/django/django/db/models/query.py", line 653, in first
    for obj in (self if self.ordered else self.order_by('pk'))[:1]:
  File "/home/tim/code/django/django/db/models/query.py", line 274, in __iter__
    self._fetch_all()
  File "/home/tim/code/django/django/db/models/query.py", line 1242, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/tim/code/django/django/db/models/query.py", line 55, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1100, in execute_sql
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: unknown signature: sqrt(int)

Should I prepare a list of types that cockroachdb's math and numberic functions should add support for or should the Django adapter take care of casting based on the currently supported types?

For example, In the latter case, since tan supports flat but not decimal or integer, the code would look like this:

from django.db.models import DecimalField, FloatField, IntegerField
from django.db.models.functions import Cast, Length, Tan

def tan(self, compiler, connection, **extra_context):
    clone = self.copy()
    clone.set_source_expressions([
        Cast(expression, FloatField()) if isinstance(expression.output_field, (DecimalField, IntegerField))
        else expression for expression in self.get_source_expressions()[::-1]
    ])
    return clone.as_sql(compiler, connection, **extra_context)

def register_expressions():
    Tan.as_cockroachdb = tan

CAST timestamptz to time doesn't respect active time zone

Consider a table with a single timestamptz column:

INSERT INTO "greatest_article" ("written") VALUES ('2018-09-28T12:42:10.234567-05:00'::timestamptz);


SELECT * FROM greatest_article;
          id         |             written              
+--------------------+----------------------------------+
  494784570481606657 | 2018-09-28 17:42:10.234567+00:00
(1 row)

When casting to time without a time zone active::

SELECT CAST("greatest_article"."written" AS time) FROM greatest_article;
              written               
+----------------------------------+
  0000-01-01 17:42:10.234567+00:00

But when setting a time zone, CAST does not respect it:

SET TIME ZONE 'America/Chicago';

SELECT * FROM greatest_article;
          id         |             written              
+--------------------+----------------------------------+
  494784570481606657 | 2018-09-28 12:42:10.234567-05:00


SELECT CAST("greatest_article"."written" AS time) FROM greatest_article;
              written               
+----------------------------------+
  0000-01-01 17:42:10.234567+00:00

In the second query, the expected hour is twelve rather than seventeen.

The Django test failure:

======================================================================
FAIL: test_cast_from_db_datetime_to_time (db_functions.comparison.test_cast.CastTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/tests/db_functions/comparison/test_cast.py", line 83, in test_cast_from_db_datetime_to_time
    self.assertEqual(dtm.start_datetime_as_time, datetime.time(12, 42, 10, rounded_ms))
AssertionError: datetime.time(17, 42, 10, 234567) != datetime.time(12, 42, 10, 234567)

in_retry decorator not threadsafe

The in_retry example on the tutorial uses a global variable in_retry. This won't work when deployed under a threaded WSGI server, since the variable will be shared between threads. contextvars are the newest Python solution to this problem, but probably a bigger rewrite to a loop or recursion would be better.

Run Django tests

It would be nice to run Django tests to see the current level of compatibility with the Djanto test suite and see what works and what does not work: https://docs.djangoproject.com/en/dev/internals/contributing/writing-code/unit-tests/#running-the-unit-tests

Some tests assume AutoField yields sequential integers starting at 1

This adapter uses the unique_rowid() function for Django's AutoField. The function doesn't generate sequential integers starting from one like Django's tests assume. This issue documents those tests.

DATE_TRUNC('week', <value>) truncates to midnight Sunday rather than Monday

DATE_TRUNC('week', <value>) truncates to midnight Sunday on cockroachdb but midnight Monday on PostgreSQL (and other databases)... probably a bug?

The Django feature commit shows some affected tests: django/django@a455e73

PostgreSQL:

> SELECT DATE_TRUNC('week', '2019-11-13'::timestamp);
"2019-11-11 00:00:00"

cockroach:

> SELECT DATE_TRUNC('week', '2019-11-13'::timestamptz);
         date_trunc          
+---------------------------+
  2019-11-10 00:00:00+00:00

unsupported queries: aliasing EXISTS() or subquery fails with "column does not exist"

These queries fail:

SELECT 
    EXISTS(
        SELECT U0."id", U0."isbn", U0."name", U0."pages", U0."rating", U0."price", U0."contact_id", U0."publisher_id", U0."pubdate" FROM "aggregation_book" U0
        WHERE (U0."pages" > 800 AND U0."publisher_id" = "aggregation_publisher"."id")
    ) AS "exists1",
    COUNT(*) AS "total" 
FROM "aggregation_publisher"
GROUP BY "exists1";

Django test failure:

======================================================================
ERROR: test_group_by_exists_annotation (aggregation.tests.AggregateTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedColumn: column "exists1" does not exist


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

Traceback (most recent call last):
  File "/home/tim/code/django/django/test/testcases.py", line 1247, in skip_wrapper
    return test_func(*args, **kwargs)
  File "/home/tim/code/django/tests/aggregation/tests.py", line 1172, in test_group_by_exists_annotation
    self.assertEqual(dict(has_long_books_breakdown), {True: 2, False: 3})
  File "/home/tim/code/django/django/db/models/query.py", line 276, in __iter__
    self._fetch_all()
  File "/home/tim/code/django/django/db/models/query.py", line 1261, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/tim/code/django/django/db/models/query.py", line 146, in __iter__
    return compiler.results_iter(tuple_expected=True, chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1056, in results_iter
    results = self.execute_sql(MULTI, chunked_fetch=chunked_fetch, chunk_size=chunk_size)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1104, in execute_sql
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "exists1" does not exist

Second failure:

SELECT 
    (
        SELECT COUNT(U0."id") AS "count" FROM "aggregation_book" U0
        WHERE (U0."pages" > 400 AND U0."publisher_id" = "aggregation_publisher"."id")
        GROUP BY U0."publisher_id"
    ) AS "subquery1",
    COUNT(*) AS "total"
FROM "aggregation_publisher"
GROUP BY "subquery1";

Django test failure:

======================================================================
ERROR: test_group_by_subquery_annotation (aggregation.tests.AggregateTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedColumn: column "subquery1" does not exist


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

Traceback (most recent call last):
  File "/home/tim/code/django/django/test/testcases.py", line 1247, in skip_wrapper
    return test_func(*args, **kwargs)
  File "/home/tim/code/django/tests/aggregation/tests.py", line 1157, in test_group_by_subquery_annotation
    self.assertEqual(dict(long_books_count_breakdown), {None: 1, 1: 4})
  File "/home/tim/code/django/django/db/models/query.py", line 276, in __iter__
    self._fetch_all()
  File "/home/tim/code/django/django/db/models/query.py", line 1261, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/tim/code/django/django/db/models/query.py", line 146, in __iter__
    return compiler.results_iter(tuple_expected=True, chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1056, in results_iter
    results = self.execute_sql(MULTI, chunked_fetch=chunked_fetch, chunk_size=chunk_size)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1104, in execute_sql
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "subquery1" does not exist

Rename project

Many of the other third-party database adapters have django in the name before the vendor. For example:

https://github.com/maxirobaina/django-firebird
https://github.com/lionheart/django-pyodbc/
https://pypi.org/project/django-mssql/

Would it be okay to rename this repo from cockroach-django to django-cockroach?

unsupported query: ALTER SEQUENCE ... OWNED BY ...

Django 3.0 includes a bug fix to preserve the sequence owner when altering an AutoField/BigAutoField on PostgreSQL.

However, this SQL isn't supported by cockroachdb...

======================================================================
ERROR: test_alter_autofield_pk_to_bigautofield_pk_sequence_owner (schema.tests.SchemaTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.FeatureNotSupported: at or near "EOF": syntax error: unimplemented: this syntax
DETAIL:  source SQL:
ALTER SEQUENCE "schema_author_id_seq" OWNED BY "schema_author"."id"
                                                                   ^
HINT:  You have attempted to use a feature that is not yet implemented.
See: https://github.com/cockroachdb/cockroach/issues/26382


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

Traceback (most recent call last):
  File "/home/tim/code/django/tests/schema/tests.py", line 1194, in test_alter_autofield_pk_to_bigautofield_pk_sequence_owner
    editor.alter_field(Author, old_field, new_field, strict=True)
  File "/home/tim/code/django/django/db/backends/base/schema.py", line 565, in alter_field
    old_db_params, new_db_params, strict)
  File "/home/tim/code/cockroach-django/cockroach/django/schema.py", line 37, in _alter_field
    new_db_params, strict,
  File "/home/tim/code/django/django/db/backends/base/schema.py", line 739, in _alter_field
    self.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/base/schema.py", line 142, in execute
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.NotSupportedError: at or near "EOF": syntax error: unimplemented: this syntax
DETAIL:  source SQL:
ALTER SEQUENCE "schema_author_id_seq" OWNED BY "schema_author"."id"
                                                                   ^
HINT:  You have attempted to use a feature that is not yet implemented.
See: https://github.com/cockroachdb/cockroach/issues/26382

That said, I don't think Django needs to manage any sequences because unique_rowid() is used for AutoField. Is that correct?

Unsupported query: UPDATE float column with integer column

For example:

======================================================================
ERROR: test_increment_value (expressions.tests.ExpressionsNumericTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/test/testcases.py", line 267, in __call__
    self._pre_setup()
  File "/home/tim/code/django/django/test/testcases.py", line 938, in _pre_setup
    self._fixture_setup()
  File "/home/tim/code/django/django/test/testcases.py", line 1165, in _fixture_setup
    self.setUpTestData()
  File "/home/tim/code/django/tests/expressions/tests.py", line 893, in setUpTestData
    Number.objects.update(float=F('integer'))
  File "/home/tim/code/django/django/db/models/manager.py", line 82, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/tim/code/django/django/db/models/query.py", line 741, in update
    rows = query.get_compiler(self.db).execute_sql(CURSOR)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1429, in execute_sql
    cursor = super().execute_sql(result_type)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1100, in execute_sql
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: value type int doesn't match type FLOAT8 of column "the_float"

Is there a plan to add support?

The SQL assigns values from an integer column to a float column:

UPDATE "expressions_number" SET "the_float" = "expressions_number"."the_integer";

unsupported database functions: sha224 and sha384

Django 3.0 adds support for various SHA* database functions.

They work out of the box on CockroachDB except for SHA224 and SHA384.

Django tests: db_functions.text.test_sha224 and db_functions.text.test_sha384

add SmallAutoField support

Django 3.0 adds support for SmallAutoField, however, cockroachdb's unique_rowid() generates 8-byte values that are too large for int2 (2-byte). I'm not sure how to proceed.

Test failure:

======================================================================
ERROR: test_fk_to_smallautofield (many_to_one.tests.ManyToOneTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.NumericValueOutOfRange: integer out of range for type int2 (column "country_id")


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

Traceback (most recent call last):
  File "/home/tim/code/django/tests/many_to_one/tests.py", line 583, in test_fk_to_smallautofield
    City.objects.create(country=us, name='Chicago')
  File "/home/tim/code/django/django/db/models/manager.py", line 82, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/tim/code/django/django/db/models/query.py", line 433, in create
    obj.save(force_insert=True, using=self.db)
  File "/home/tim/code/django/django/db/models/base.py", line 746, in save
    force_update=force_update, update_fields=update_fields)
  File "/home/tim/code/django/django/db/models/base.py", line 784, in save_base
    force_update, using, update_fields,
  File "/home/tim/code/django/django/db/models/base.py", line 886, in _save_table
    results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)
  File "/home/tim/code/django/django/db/models/base.py", line 925, in _do_insert
    using=using, raw=raw,
  File "/home/tim/code/django/django/db/models/manager.py", line 82, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/tim/code/django/django/db/models/query.py", line 1204, in _insert
    return query.get_compiler(using=using).execute_sql(returning_fields)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1344, in execute_sql
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.DataError: integer out of range for type int2 (column "country_id")

Unsupported query: timezone() doesn't support UTC offsets

The timezone() function (AT TIME ZONE) doesn't seem to accept UTC offsets like PostgreSQL does:

Sample query:

SELECT EXTRACT(hour FROM "db_functions_dtmodel"."start_datetime" AT TIME ZONE 'UTC-05:00') AS "hour_with_delta_pos"
FROM "db_functions_dtmodel"

Django test failure:

ERROR: test_extract_func_with_timezone (db_functions.datetime.test_extract_trunc.DateFunctionWithTimeZoneTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.InternalError: timezone(): unknown time zone UTC-05:00


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

Traceback (most recent call last):
  File "/home/tim/code/django/tests/db_functions/datetime/test_extract_trunc.py", line 1016, in test_extract_func_with_timezone
    utc_model = qs.get()
  File "/home/tim/code/django/django/db/models/query.py", line 411, in get
    num = len(clone)
  File "/home/tim/code/django/django/db/models/query.py", line 258, in __len__
    self._fetch_all()
  File "/home/tim/code/django/django/db/models/query.py", line 1261, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/tim/code/django/django/db/models/query.py", line 57, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1104, in execute_sql
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.InternalError: timezone(): unknown time zone UTC-05:00

Serialization test failures when truncating tables with CockroachDB 20.1 beta 3+

These test failures started with 20.1 beta 3:

auth_tests.test_handlers.ModWsgiHandlerTestCase.test_check_password
fixtures_regress.tests.TestTicket11101.test_ticket_11101
serializers.test_json.JsonSerializerTransactionTestCase.test_forward_refs
serializers.test_xml.XmlSerializerTransactionTestCase.test_forward_refs 
serializers.test_yaml.YamlSerializerTransactionTestCase.test_forward_refs

The failures don't happen fairly regularly but not all the time. They all use Django's TransactionTestCase (which doesn't use transactions to speed them up and therefore runs TRUNCATE <tables> after each test. I thought maybe the order of the tables in the truncate might have an effect but it doesn't seem to be the case.

Example failure:

======================================================================
ERROR: test_check_password (auth_tests.test_handlers.ModWsgiHandlerTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/base/base.py", line 243, in _commit
    return self.connection.commit()
psycopg2.errors.SerializationFailure: restart transaction: TransactionRetryWithProtoRefreshError: TransactionRetryError: retry txn (RETRY_SERIALIZABLE): meta={id=c0060d45 key=/Table/SystemConfigSpan/Start pri=0.02621217 epo=0 ts=1586995678.166989672,2 min=1586995677.687666051,0 seq=631} lock=true stat=PENDING rts=1586995677.687666051,0 wto=false max=1586995677.687666051,0


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

Traceback (most recent call last):
  File "/home/tim/code/django/django/core/management/commands/flush.py", line 63, in handle
    connection.ops.execute_sql_flush(database, sql_list)
  File "/home/tim/code/django/django/db/backends/base/operations.py", line 405, in execute_sql_flush
    cursor.execute(sql)
  File "/home/tim/code/django/django/db/transaction.py", line 233, in __exit__
    connection.commit()
  File "/home/tim/code/django/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "/home/tim/code/django/django/db/backends/base/base.py", line 267, in commit
    self._commit()
  File "/home/tim/code/django/django/db/backends/base/base.py", line 243, in _commit
    return self.connection.commit()
  File "/home/tim/code/django/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/base/base.py", line 243, in _commit
    return self.connection.commit()
django.db.utils.OperationalError: restart transaction: TransactionRetryWithProtoRefreshError: TransactionRetryError: retry txn (RETRY_SERIALIZABLE): meta={id=c0060d45 key=/Table/SystemConfigSpan/Start pri=0.02621217 epo=0 ts=1586995678.166989672,2 min=1586995677.687666051,0 seq=631} lock=true stat=PENDING rts=1586995677.687666051,0 wto=false max=1586995677.687666051,0

And the failing query:

TRUNCATE "auth_tests_customuser", "auth_tests_customuserwithfk", "auth_group", "auth_tests_uuiduser", "auth_tests_extensionuser_groups", "auth_user_groups", "auth_tests_custompermissionsuser_user_permissions", "auth_tests_nopassworduser", "auth_tests_uuiduser_groups", "auth_group_permissions", "auth_tests_customuserwithoutisactivefield", "auth_tests_uuiduser_user_permissions", "auth_tests_concrete", "auth_tests_customuserwithm2mthrough", "auth_tests_integerusernameuser", "auth_tests_organization", "django_content_type", "auth_tests_membership", "auth_tests_email", "auth_tests_extensionuser", "auth_tests_custompermissionsuser", "auth_tests_customusernonuniqueusername", "auth_tests_extensionuser_user_permissions", "auth_tests_custompermissionsuser_groups", "auth_tests_customuserwithm2m_orgs", "auth_tests_minimaluser", "auth_tests_userwithdisabledlastloginfield", "auth_tests_isactivetestuser1", "auth_user_user_permissions", "auth_permission", "auth_tests_customuserwithm2m", "auth_user" CASCADE

There are 3 Django apps for this test.

auth_tests (auth_tests-prefixed tables)
django.contrib.contenttypes (django_content_type table)
django.contrib.auth (other auth-prefixed (besides auth_tests prefixed)

I've only seen the issue occur when auth_tests tables and django.contrib.auth's tables are both included in the TRUNCATE statement.

Separate pull request checks

Currently flake8, isort, and tests are in a single status check. It would be nice to have separate checks, particularly once we add a build for cockroachdb 20.1. Running the tests currently takes about 3 hours so if we have to test with cockroachdb 19.2 and 20.1, the build will take around 6 hours if we can't run them in parallel.

Non-ASCII file tests fail on CI

See https://docs.djangoproject.com/en/dev/ref/unicode/#files

I tried adding export LANG="en_US.UTF-8" to build-teamcity-sh but it didn't help.

Example failure:

 ======================================================================
[23:08:19]
[Step 3/3] ERROR: test_content_saving (file_storage.tests.ContentFileStorageTestCase)
[23:08:19]
[Step 3/3] ----------------------------------------------------------------------
[23:08:19]
[Step 3/3] Traceback (most recent call last):
[23:08:19]
[Step 3/3]   File "/home/agent/work/391b6e30ae43aafa/_django_repo/tests/file_storage/tests.py", line 968, in test_content_saving
[23:08:19]
[Step 3/3]     self.storage.save('unicode.txt', ContentFile("espa\xf1ol"))
[23:08:19]
[Step 3/3]   File "/home/agent/work/391b6e30ae43aafa/_django_repo/django/core/files/storage.py", line 52, in save
[23:08:19]
[Step 3/3]     return self._save(name, content)
[23:08:19]
[Step 3/3]   File "/home/agent/work/391b6e30ae43aafa/_django_repo/django/core/files/storage.py", line 274, in _save
[23:08:19]
[Step 3/3]     _file.write(chunk)
[23:08:19]
[Step 3/3] UnicodeEncodeError: 'ascii' codec can't encode character '\xf1' in position 4: ordinal not in range(128)

Unsupported query: power() database function doesn't accept negative exponents

======================================================================
ERROR: test_integer (db_functions.math.test_power.PowerTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.NumericValueOutOfRange: power(): integer out of range


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

Traceback (most recent call last):
  File "/home/tim/code/django/tests/db_functions/math/test_power.py", line 37, in test_integer
    big_power=Power('big', 'small'),
  File "/home/tim/code/django/django/db/models/query.py", line 653, in first
    for obj in (self if self.ordered else self.order_by('pk'))[:1]:
  File "/home/tim/code/django/django/db/models/query.py", line 274, in __iter__
    self._fetch_all()
  File "/home/tim/code/django/django/db/models/query.py", line 1242, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/tim/code/django/django/db/models/query.py", line 55, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1100, in execute_sql
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.DataError: power(): integer out of range

Is it intentional?

Example SQL:

SELECT ... power("db_functions_integermodel"."big", "db_functions_integermodel"."small") AS "big_power"
FROM "db_functions_integermodel"

where big = 3 and small = -1.

log() function doesn't support custom bases

cockroachdb only supports log(x) for a base 10 logarithm. Most databases support log(b, x) for a base b logarithm.

Impossible to use django-cockroachdb with psycopg2-binary

pipenv install django-cockroachdb==3.0
Warning: the environment variable LANG is not set!
We recommend setting this in ~/.profile (or equivalent) for proper expected behavior.
Creating a virtualenv for this project…
Pipfile: /Users/me/Desktop/djtest/Pipfile
Using /usr/local/Cellar/pipenv/2018.11.26_3/libexec/bin/python3.8 (3.8.1) to create virtualenv…
⠹ Creating virtual environment...Already using interpreter /usr/local/Cellar/pipenv/2018.11.26_3/libexec/bin/python3.8
Using real prefix '/usr/local/Cellar/[email protected]/3.8.1/Frameworks/Python.framework/Versions/3.8'
New python executable in /Users/me/.local/share/virtualenvs/djtest-QZ45VGf8/bin/python3.8
Also creating executable in /Users/me/.local/share/virtualenvs/djtest-QZ45VGf8/bin/python
Installing setuptools, pip, wheel...
done.

✔ Successfully created virtual environment! 
Virtualenv location: /Users/me/.local/share/virtualenvs/djtest-QZ45VGf8
Creating a Pipfile for this project…
Installing django-cockroachdb==3.0…
Adding django-cockroachdb to Pipfile's [packages]…
✔ Installation Succeeded 
Pipfile.lock not found, creating…
Locking [dev-packages] dependencies…
Locking [packages] dependencies…
✔ Success! 
Updated Pipfile.lock (676edb)!
Installing dependencies from Pipfile.lock (676edb)…
An error occurred while installing psycopg2==2.8.4 --hash=sha256:4212ca404c4445dc5746c0d68db27d2cbfb87b523fe233dc84ecd24062e35677 --hash=sha256:47fc642bf6f427805daf52d6e52619fe0637648fe27017062d898f3bf891419d --hash=sha256:72772181d9bad1fa349792a1e7384dde56742c14af2b9986013eb94a240f005b --hash=sha256:8396be6e5ff844282d4d49b81631772f80dabae5658d432202faf101f5283b7c --hash=sha256:893c11064b347b24ecdd277a094413e1954f8a4e8cdaf7ffbe7ca3db87c103f0 --hash=sha256:92a07dfd4d7c325dd177548c4134052d4842222833576c8391aab6f74038fc3f --hash=sha256:965c4c93e33e6984d8031f74e51227bd755376a9df6993774fd5b6fb3288b1f4 --hash=sha256:9ab75e0b2820880ae24b7136c4d230383e07db014456a476d096591172569c38 --hash=sha256:b0845e3bdd4aa18dc2f9b6fb78fbd3d9d371ad167fd6d1b7ad01c0a6cdad4fc6 --hash=sha256:dca2d7203f0dfce8ea4b3efd668f8ea65cd2b35112638e488a4c12594015f67b --hash=sha256:ed686e5926929887e2c7ae0a700e32c6129abb798b4ad2b846e933de21508151 --hash=sha256:ef6df7e14698e79c59c7ee7cf94cd62e5b869db369ed4b1b8f7b729ea825712a --hash=sha256:f898e5cc0a662a9e12bde6f931263a1bbd350cfb18e1d5336a12927851825bb6! Will try again.
  🐍   ▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉ 2/2 — 00:00:01
Installing initially failed dependencies…
[pipenv.exceptions.InstallError]:   File "/usr/local/Cellar/pipenv/2018.11.26_3/libexec/lib/python3.8/site-packages/pipenv/core.py", line 1983, in do_install
[pipenv.exceptions.InstallError]:       do_init(
[pipenv.exceptions.InstallError]:   File "/usr/local/Cellar/pipenv/2018.11.26_3/libexec/lib/python3.8/site-packages/pipenv/core.py", line 1246, in do_init
[pipenv.exceptions.InstallError]:       do_install_dependencies(
[pipenv.exceptions.InstallError]:   File "/usr/local/Cellar/pipenv/2018.11.26_3/libexec/lib/python3.8/site-packages/pipenv/core.py", line 858, in do_install_dependencies
[pipenv.exceptions.InstallError]:       batch_install(
[pipenv.exceptions.InstallError]:   File "/usr/local/Cellar/pipenv/2018.11.26_3/libexec/lib/python3.8/site-packages/pipenv/core.py", line 763, in batch_install
[pipenv.exceptions.InstallError]:       _cleanup_procs(procs, not blocking, failed_deps_queue, retry=retry)
[pipenv.exceptions.InstallError]:   File "/usr/local/Cellar/pipenv/2018.11.26_3/libexec/lib/python3.8/site-packages/pipenv/core.py", line 681, in _cleanup_procs
[pipenv.exceptions.InstallError]:       raise exceptions.InstallError(c.dep.name, extra=err_lines)
[pipenv.exceptions.InstallError]: ['Collecting psycopg2==2.8.4', '  Using cached psycopg2-2.8.4.tar.gz (377 kB)']
[pipenv.exceptions.InstallError]: ['ERROR: Command errored out with exit status 1:', '     command: /Users/me/.local/share/virtualenvs/djtest-QZ45VGf8/bin/python3.8 -c \'import sys, setuptools, tokenize; sys.argv[0] = \'"\'"\'/private/var/folders/_d/pcm157vn4kg3mv8wvb37vy2w0000gn/T/pip-install-v1m062jh/psycopg2/setup.py\'"\'"\'; __file__=\'"\'"\'/private/var/folders/_d/pcm157vn4kg3mv8wvb37vy2w0000gn/T/pip-install-v1m062jh/psycopg2/setup.py\'"\'"\';f=getattr(tokenize, \'"\'"\'open\'"\'"\', open)(__file__);code=f.read().replace(\'"\'"\'\\r\\n\'"\'"\', \'"\'"\'\\n\'"\'"\');f.close();exec(compile(code, __file__, \'"\'"\'exec\'"\'"\'))\' egg_info --egg-base /private/var/folders/_d/pcm157vn4kg3mv8wvb37vy2w0000gn/T/pip-install-v1m062jh/psycopg2/pip-egg-info', '         cwd: /private/var/folders/_d/pcm157vn4kg3mv8wvb37vy2w0000gn/T/pip-install-v1m062jh/psycopg2/', '    Complete output (23 lines):', '    running egg_info', '    creating /private/var/folders/_d/pcm157vn4kg3mv8wvb37vy2w0000gn/T/pip-install-v1m062jh/psycopg2/pip-egg-info/psycopg2.egg-info', '    writing /private/var/folders/_d/pcm157vn4kg3mv8wvb37vy2w0000gn/T/pip-install-v1m062jh/psycopg2/pip-egg-info/psycopg2.egg-info/PKG-INFO', '    writing dependency_links to /private/var/folders/_d/pcm157vn4kg3mv8wvb37vy2w0000gn/T/pip-install-v1m062jh/psycopg2/pip-egg-info/psycopg2.egg-info/dependency_links.txt', '    writing top-level names to /private/var/folders/_d/pcm157vn4kg3mv8wvb37vy2w0000gn/T/pip-install-v1m062jh/psycopg2/pip-egg-info/psycopg2.egg-info/top_level.txt', "    writing manifest file '/private/var/folders/_d/pcm157vn4kg3mv8wvb37vy2w0000gn/T/pip-install-v1m062jh/psycopg2/pip-egg-info/psycopg2.egg-info/SOURCES.txt'", '    ', '    Error: pg_config executable not found.', '    ', '    pg_config is required to build psycopg2 from source.  Please add the directory', '    containing pg_config to the $PATH or specify the full executable path with the', '    option:', '    ', '        python setup.py build_ext --pg-config /path/to/pg_config build ...', '    ', "    or with the pg_config option in 'setup.cfg'.", '    ', '    If you prefer to avoid building psycopg2 from source, please install the PyPI', "    'psycopg2-binary' package instead.", '    ', "    For further information please check the 'doc/src/install.rst' file (also at", '    <http://initd.org/psycopg/docs/install.html>).', '    ', '    ----------------------------------------', 'ERROR: Command errored out with exit status 1: python setup.py egg_info Check the logs for full command output.']
ERROR: ERROR: Package installation failed...
  ☤  ▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉ 0/1 — 00:00:01

Unsupported query: sum(): unsupported binary operator: <float> + <int>

Example query:

SELECT ... sum((("aggregation_book"."rating" + "aggregation_book"."pages") + "aggregation_book"."price")) AS "sums"
FROM "aggregation_book"
WHERE "aggregation_book"."id" = 501322346762469377
GROUP BY "aggregation_book"."id";

Where rating is FloatField and pages is IntegerField.

Django test failure:

======================================================================
ERROR: test_add_implementation (aggregation.tests.AggregateTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.InvalidParameterValue: sum(): unsupported binary operator: <float> + <int>


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

Traceback (most recent call last):
  File "/home/tim/code/django/tests/aggregation/tests.py", line 1045, in test_add_implementation
    b1 = qs.get(pk=self.b4.pk)
  File "/home/tim/code/django/django/db/models/query.py", line 402, in get
    num = len(clone)
  File "/home/tim/code/django/django/db/models/query.py", line 256, in __len__
    self._fetch_all()
  File "/home/tim/code/django/django/db/models/query.py", line 1242, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/tim/code/django/django/db/models/query.py", line 55, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1100, in execute_sql
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 99, in execute
    return super().execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.DataError: sum(): unsupported binary operator: <float> + <int>

cockroachdb / django-cockroachdb Goto Github PK

django-cockroachdb's Introduction

CockroachDB backend for Django

Prerequisites

Install and usage

Notes on Django fields

Notes on Django QuerySets

FAQ

GIS support

Disabling CockroachDB telemetry

Known issues and limitations in CockroachDB 23.2.x and earlier

Known issues and limitations in CockroachDB 23.1.x and earlier

django-cockroachdb's People

Contributors

Stargazers

Watchers

Forkers

django-cockroachdb's Issues

Recommend Projects

Recommend Topics

Recommend Org