基于PK和另一个字段Django保存对象 [英] Django Save Object based on PK and another field

问题描述

从Google的主题中，我发现Django不支持分区，本身，所以我自己做了表的划分。我根据另一个表上的外键的第二个字段分区我的表。基本的模型设置是这样的：

  class Event（models.Model）：
 id = models.AutoField primary_key = True）
 device = models.ForeignKey（Device）
 ...（更多字段）
  

我已经通过device_id对表进行了分区，生成了诸如event_1，event_2等子表。
我的所有查询都包含设备ID，所以查询现在要快得多，但是插入django生成一个UPDATE语句，如：

 更新事件SET device = X，... = X，... WHERE id = XXX 
  

这将导致数据库遍历表的所有分区，以查找指定的ID。由于device_id从不改变，我现在想要将device_id = XXX语句添加到UPDATE语句的WHERE部分，这样可以使数据库只能遍历一个分区。

我认为我的问题只是问题的结果，我的数据库主键中没有分区键，但是由于django只支持一个字段作为PK，分区键不是唯一的，我不能使用它作为PK。

我可以想到我的问题的两个解决方案：

1. 使用主键字段，以某种方式自动构造
   ，从而增加与device_id（如1_234）组合。
   然后，数据库将必须拆分主键，以了解此事件所用的
   设备以及需要搜索的分区。


2. 更改UPDATE语句，以便将device_id添加到WHERE语句。

我认为最优雅的方式是将device_id包含在首要的关键。因此，Django将不需要任何更改，只能在Django透明的数据库中进行分区。但是我不知道是否可以在数据库中创建一个主键。

感谢您的帮助

解决方案

我同意你可能最好离开Django。虽然我从来没有做过这样的事情，但我认为只需创建一个插入触发器来构建新的连接的主键就很容易了。这样做：

  CREATE TABLE foo（
 id TEXT NOT NULL，
 device_id INT NOT NULL， 
 CONSTRAINT foo_pkey PRIMARY KEY（id）
）; 
 
 CREATE SEQUENCE foo_id_seq 
 INCREMENT 1 
 MINVALUE 1 
 MAXVALUE 9223372036854775807 
 START 1 
 CACHE 1; 
 
 CREATE OR REPLACE FUNCTION generate_foo_id（）
 RETURNS trigger AS 
 $ BODY $ 
 BEGIN 
 NEW.id：= NEW.device_id || '_'|| NEXTVAL（ foo_id_seq）; 
返回新; 
 END; 
 $ BODY $ 
 LANGUAGE plpgsql VOLATILE 
 COST 100; 
 
 CREATE TRIGGER trigger_generate_foo_id 
 BEFORE INSERT 
 ON foo 
 FOR EACH ROW 
 EXECUTE PROCEDURE generate_foo_id（）; 
  

你是对的，你的分区函数将不得不拆分它。我从来没有创建过这样的分区函数，但是我不知道为什么它不起作用。但是，您应该测试并将结果添加到此答案中，以便将来的用户将从您的测试中获益。

I am trying to use a partitioned table in postgresql together with a Django installation.

From Googleing the subject, I found out, that Django does not support partitioning by itself, so I did the partitioning of the table myself. I partition my table based on a second field which is a foreign key on another table. The basic model setup is like so:

class Event(models.Model):
    id = models.AutoField(primary_key=True)
    device = models.ForeignKey("Device")
    ... (More Fields)

I have Partitioned the table by the device_id, generating subtables like event_1, event_2 etc. All my querys contain the device id, so the querys are much faster now, but for inserting, django generates an UPDATE Statement like:

UPDATE event SET device=X, ...=X, ... WHERE id=XXX

This results in the database traversing all the partitions of the table to look for the specified ID. Since the device_id never changes, I now want to add the device_id=XXX statement into the WHERE part of the UPDATE statement, which would allow the database to only traverse the one partition.

I think my problem is only a result of the problem, that I don't have the partitioning key in the primary key of my database, but since django only supports one field to be the PK and the partitioning key is not unique, I can't use it as PK.

I can think of two solutions to my problem:

1. Use a primary key field, that is somehow automatically constructed from an increasing number combined with the device_id (like 1_234). The database would then have to split the primary key to know which device this event is for and which partition it needs to search in.
2. Change the UPDATE Statement, such that it adds the device_id to the WHERE statement.

The most elegant way in my opinion would be to include the device_id in the primary key. Therefore no changes in Django would be needed and the partitioning would happen only in the database transparent for Django. However I'm not sure if it is possible to create such a primary key in the database.

Thank you for your Help

解决方案

I agree that you would probably be best to leave Django out of it. While I've never done something exactly like this, I think it would be pretty easy to just create a before insert trigger to construct your new, concatenated primary key. Something like this:

CREATE TABLE foo(
  id TEXT NOT NULL,
  device_id INT NOT NULL,
  CONSTRAINT foo_pkey PRIMARY KEY (id)
);

CREATE SEQUENCE foo_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE OR REPLACE FUNCTION generate_foo_id()
  RETURNS trigger AS
$BODY$
BEGIN
  NEW.id :=  NEW.device_id || '_' || nextval('foo_id_seq');
  RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE TRIGGER trigger_generate_foo_id
  BEFORE INSERT
  ON foo
  FOR EACH ROW
  EXECUTE PROCEDURE generate_foo_id();

And you are right, your partition function will have to split it. I've never created a partition function that does this, but I don't know why it would not work. However, you should test and add a comment to this 'answer' with your results (so that future users will benefit from your testing).

这篇关于基于PK和另一个字段Django保存对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！