maxtepkeev / django-db-parti Goto Github PK

It is a common practice to partition by ID. Instagram does so as well.

First of all I think this is not an issue but a problem integrating this scripts with our application.

python 2.7
Django 1.4.3
django-db-parti==0.3.1

Postgresql 9.1.11

I have django project with postgres as a backend. T1 table partitioned by calldate field (timestamp with time zone) like this:

models.py:

class T1(Partitionable):
calldate = models.DateTimeField(verbose_name=_('Calldate'))
...
class Meta(Partitionable.Meta):
...
partition_type = 'range'
partition_subtype = 'date'
partition_range = 'day'
partition_column = 'calldate'

settings.py:

TIME_ZONE = 'Etc/GMT-1'

Postgres have same timezone: GMT+1

The problem is the connection between django and postges allways use GMT+00 time zone. In runtime at postgres, when function international_cdrtint_insert_child() try to find 'tablename' from 'calldate' field, fetch a previous day partition.

Eg. When I try to insert a row with this calldate: '2014-02-18 00:00:24+01' Inside postgres appear '2014-02-17 23:00:24+00' and postgres check day 17 instead day 18:
tablename := 'T1' || to_char(NEW.calldate, '"y"YYYY"d"DDD');  ---> T1_y2014d048 instead T1_y2014d049

When I try to insert this kind of rows postgres trough this error (this is logic):

IntegrityError: new row for relation "T1_y2014d048" violates check constraint "T1_y2014d048_calldate_check"
CONTEXT:  SQL statement "INSERT INTO T1_y2014d048 VALUES (($1).*);"
PL/pgSQL function "T1_insert_child" line 27 at EXECUTE statement

Also, if I insert this row with the INSERT statement everything works great. Even if the INSERT statement have '2014-02-17 23:00:24+00' in calldate field, postgres fetch correct partition table.

I couldnt convert or cast 'calldate' field to GMT+1 timezone at runtime and no idea how to change django connection properties (if it exists) to use GMT+1. I try changing TIMEZONE var in settings.py but does not work. Do you deal with time zones in partitioned postgres tables?

Sorry for report this like an issue but I think it is important to show to other people who have same problems.

Regards

from dbparti.admin import PartitionableAdmin
from dbparti.models import Partitionable
from django.contrib.auth.models import User
from django.contrib.admin import site

class Some(Partitionable):
    date = models.DateField()
    user = models.ForeignKey(User)

    class Meta(Partitionable.Meta):
        partition_type = 'range'
        partition_subtype = 'date'
        partition_range = 'month'
        partition_column = 'date'


class SomeAdmin(PartitionableAdmin):
    partition_show = 'all'


site.register(Some, SomeAdmin)

I'm got the following error: Table has no partition for value 735568
What does it mean?

Generated SQL:

ALTER TABLE app_some PARTITION BY RANGE (TO_DAYS(`date`))(
    PARTITION app_some_y0000m00 VALUES LESS THAN (0)
);

Env: MySQL 5.6.15, OS X

I try create partition manually. And all working fine. SQL:

ALTER TABLE app_some PARTITION BY RANGE COLUMNS (`date`) (
    PARTITION p2013 VALUES LESS THAN ('2013-01-01'),
    PARTITION p2014 VALUES LESS THAN ('2014-01-01')
);

What do you think about this?

Hello my friend,

First of all, your plugin is awesome. But...

I'm getting this error when executing "partition" command:

(projecttest_env)macbookemmanuel:projecttest emmanuel$ python manage.py partition machines
Traceback (most recent call last):
File "manage.py", line 10, in
execute_from_command_line(sys.argv)
File "/users/emmanuel/Envs/projecttest_env/lib/python2.7/site-packages/django/core/management/init.py", line 399, in execute_from_command_line
utility.execute()
File "/users/emmanuel/Envs/projecttest_env/lib/python2.7/site-packages/django/core/management/init.py", line 392, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/users/emmanuel/Envs/projecttest_env/lib/python2.7/site-packages/django/core/management/base.py", line 242, in run_from_argv
self.execute(_args, *_options.dict)
File "/users/emmanuel/Envs/projecttest_env/lib/python2.7/site-packages/django/core/management/base.py", line 285, in execute
output = self.handle(_args, *_options)
File "/users/emmanuel/Envs/projecttest_env/lib/python2.7/site-packages/django/core/management/base.py", line 348, in handle
app_output = self.handle_app(app, **options)
File "/users/emmanuel/Envs/projecttest_env/lib/python2.7/site-packages/dbparti/management/commands/partition.py", line 18, in handle_app
model_instance.get_partition().prepare()
File "/users/emmanuel/Envs/projecttest_env/lib/python2.7/site-packages/dbparti/backends/mysql/partition.py", line 52, in prepare
super(RangePartition, self).prepare()
File "/users/emmanuel/Envs/projecttest_env/lib/python2.7/site-packages/dbparti/backends/mysql/partition.py", line 22, in prepare
partition_column=self.partition_column,
KeyError: 'pk'

I have a several models, which I want to add to partitioning. I extend this models from abstract partitionable model and got follow exception:

AttributeError: 'Options' object has no attribute 'partition_column'

Code example for reproduce:

class PartitionableAbstract(Partitionable):
    class Meta:
        abstract = True

        partition_type = 'range'
        partition_subtype = 'date'
        partition_range = 'month'
        partition_column = 'date'


class User(PartitionableAbstract):
    date = models.DateField(db_index=True)
    num = models.PositiveIntegerField(default=0)


class Logs(PartitionableAbstract):
    date = models.DateField(db_index=True)
    num = models.PositiveIntegerField(default=0)


class Some(PartitionableAbstract):
    date = models.DateField(db_index=True)
    num = models.PositiveIntegerField(default=0)

Hi Max,

Thank you to correct my last issue. Now i have other problem:

File "/users/emmanuel/Envs/project_env/lib/python2.7/site-packages/django/db/backends/util.py", line 51, in execute
return self.cursor.execute(sql)
File "/users/emmanuel/Envs/project_env/lib/python2.7/site-packages/django/db/backends/mysql/base.py", line 124, in execute
return self.cursor.execute(query, args)
File "/users/emmanuel/Envs/project_env/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
self.errorhandler(self, exc, value)
File "/users/emmanuel/Envs/project_env/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorclass, errorvalue
django.db.utils.IntegrityError: (1217, 'Cannot delete or update a parent row: a foreign key constraint fails')

I know that mysql doesn't support table partitions with foreign keys, but do you have some alternative to solve my problem?

Here's my schema:

class Report(Partitionable) :
machine = models.ForeignKey(Machine)
created_date = models.DateTimeField(auto_now_add=True, db_index=True)
report_data = models.TextField(null=True, default=None)

Thanks!

how about adding dummy sqlite backend from here:
https://github.com/whop/django-db-parti/commit/33b78b720454abd68d138bd721ffdbf2a419679e

this will allow to run tests easier

strptime function used in utilities.py can't handle datetime strings with timezone.

For example: '2013-08-14 05:14:12.456361+00:00'

This is the error reported:

File "/lib/python2.7/site-packages/dbparti/utilities.py", line 32, in init
if self.partition_column_val is not None else datetime.now()
File "/usr/lib/python2.7/_strptime.py", line 328, in _strptime
data_string[found.end():])
ValueError: unconverted data remains: +00:00

Hi Max,

This is a pretty cool app. I have been playing with it. I am using postgres. I noticed that child tables don't have indexes set up like the parent table. We probably want them by default, right? I see in the postgres manual that you use the "like" clause when creating a child table.

ex.
CREATE TABLE foo_1 (LIKE foo including indexes ) inherits (foo);

What are your thoughts on this?

Django has a database router. nydus-django uses it in order to shard by a field's name.
The project seems outdated but it has some potential to be turned into something useable especially if you are going to use nydus internally.