在 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

时态表扩展可能会有所帮助，如果您不想推出自己的......

https://github.com/arkhipov/temporal_tables

Answer 3

由于并非所有文本集都可以以相同的速率获得新版本，因此没有真正的方法可以对版本号进行断言，而无需同时涉及 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 的唯一行是最新的行。请注意，父代和子代都是来自同一个表的别名。这就是所谓的“自连接”，当您将分层数据（如版本控制机制）存储在单个表中时，可以很方便地执行此操作。

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

希望这有帮助。