在PostgreSQL中提供版本控制行的好方法是什么？如何查询它们？

Question

我想在一个表中存储不同版本的不同文本和其他数据。对于文本，我的表格如下：

id BigSerial, PRIMARY KEY  
version Integer  
text Text  
origin BigInt

现在我想在这个表中存储不同版本的文本，如下所示：

1,0,"My Text, first Version",null  
2,1,"My Text, second Version",1  
3,0,"My 2nd Text v1",null  
4,1,"My 2nd Text v2",3

我还不知道如何查询每组文本版本号最高的行。

Answer 1

Bigserial id号没有任何用处。

create temp table my_table (
  id integer not null,
  version integer not null check(version > 0),
  -- Give a lot of thought to whether text should also be unique. *I* think
  -- it probably should, but it's really application-dependent.
  text Text not null unique,
  primary key (id, version)
);
insert into my_table values 
(1, 1, 'My Text, first Version'),
(1, 2, 'My Text, second Version'),
(2, 1, 'My 2nd text v1'),
(2, 2, 'My 2nd text v2')

每个id的版本数。

select id, count(*)
from my_table
group by id;

每个id的当前版本。

with current_ver as (
  select id, max(version) as version
  from my_table
  group by id
)
select m.* from my_table m
inner join current_ver c on c.id = m.id and c.version = m.version

尽管我是用一个通用的表表达式写的，但您可能想要创建一个当前版本的视图。我认为大多数访问这些数据的应用程序都需要当前版本。

Answer 2

由于并非所有文本集都可以以相同的速度获得新版本，因此没有真正的方法来断言版本号，而不必同时涉及id和原点。

例如，要知道"5"是特定文本集的最新版本，您必须确定没有版本"6"。这样做的方法是查看是否有版本为"6"的行与版本为"5"的行有原点。但这只会简化为找到一行，而没有其他行声称它是原点；您不需要版本号。

所以，你可以把"我有最高版本号吗"的问题改为"没有其他行以我的id为原点"。如果没有其他行，则这是您的最新行，您可以返回结果。您可以通过以下查询来实现这一点：

select t.id 
from table parent
left join table descendants on parent.id = descendants.origin 
where descendants.id is null;

在生成的联接表中，descendant.id为null的行只有最新的行。请注意，父对象和子对象都来自同一个表。这就是所谓的"自联接"，当您将分层数据（如版本控制机制）存储在单个表中时，这样做很方便。

不过，值得注意的是，这只会为您找到最新版本。如果你想知道这是哪个版本，那么你肯定会从你的版本栏中受益。否则，您将不得不进行递归查询，因为您不会预先知道版本列表的深度。没有人喜欢写这些。

希望这能有所帮助。

Answer 3

Temporal tables扩展可能会有所帮助，如果你不想自己滚动，那就是…

https://github.com/arkhipov/temporal_tables