sev's questions

例如，我可以使用相同的序列在两个单独的表上生成主键吗？

CREATE TABLE IF NOT EXISTS public."user_Registered"
(
    id integer NOT NULL DEFAULT nextval('user_id_seq'::regclass),
    email character varying COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "PK_1" PRIMARY KEY (id),
    CONSTRAINT "UQ_1" UNIQUE (email)
)

CREATE TABLE IF NOT EXISTS public."user_Applicant"
(
    id integer NOT NULL DEFAULT nextval('user_id_seq'::regclass),
    email character varying COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "PK_2" PRIMARY KEY (id),
    CONSTRAINT "UQ_2" UNIQUE (email)
)

在我的示例中，注册用户拥有的数据不仅仅是电子邮件，我无法将其存储在同一个表中并在他们注册时填充。所以我的计划是将它们保存在申请表中，然后将它们移动到注册表中，但我想保留 ID。

我有以下两个表来存储用户及其地址。我有一个与我接收和保存用户数据的顺序有关的问题。起初，用户将只提供地址，然后被定向到外部服务。要打开此服务，我需要提供我的内部用户 ID。该服务会验证他们的电子邮件地址并将回复发送给我。

CREATE TABLE IF NOT EXISTS public."user"
(
    id integer NOT NULL DEFAULT nextval('user_id_seq'::regclass),
    email character varying COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "PK_cace4a159ff9f2512dd42373760" PRIMARY KEY (id),
    CONSTRAINT "UQ_e12875dfb3b1d92d7d7c5377e22" UNIQUE (email)
)

CREATE TABLE IF NOT EXISTS public.adresses
(
    id integer NOT NULL DEFAULT nextval('address_id_seq'::regclass),
    address character varying COLLATE pg_catalog."default" NOT NULL,
    "userId" integer,
    CONSTRAINT "PK_bec464dd8d54c39c54fd32e2334" PRIMARY KEY (id),
    CONSTRAINT "FK_35472b1fe48b6330cd349709564" FOREIGN KEY ("userId")
        REFERENCES public."user" (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

• 除了在表格中输入电子邮件并最终同时输入 ID 和电子邮件之外，我是否可以通过其他方式从序列中生成用户 ID？
• 我可以将地址保存在地址表中引用尚未添加到用户的外键吗？

由于这篇文章https://stackoverflow.com/questions/9984196/postgresql-gapless-sequences中提到的原因，在 PostgreSQL 中序列被设计为有间隙。我的问题是：不能通过定期（比如说每天一次）检查哪些序列号未使用并将它们添加回序列中来使序列几乎没有间隙吗？

我假设当前 postgres 将一个序列实现为一个计数器，每次访问它时它都会递增。相反，它可以用一个自由值列表和一个计数器来实现。然后该序列将按如下方式工作：

# python style pseudo code

class Sequence:
    def __init__(self):
        self.free_numbers_set = []
        self.counter = 0

    def get_next_sequence_value(self):
        if not isempty(self.free_numbers_set):
            id = self.free_numbers_set.pop(0)
        else:
            id = self.counter
            id ++

        return id

    def add_free_numbers(self, column):
        for i in range(0:self.counter):
            if not i in column:
                free_numbers_set.append(i)
        free_numbers_set.sort()

每次我们需要一个新的 id 时，我们都会运行get_next_sequence_value()并定期运行add_free_numbers(column)

更新解决了一些问题

1. 对于标识列，获取当前使用的值将是微不足道的。在其他情况下，由用户提供已在使用的值列表。
2. 需要考虑与 MVCC 的交互。可以潜在地实现它，以便在缺失值更新期间，序列将默认为仅递增计数器 - 这将避免冲突。

我相信这个解决方案可以提供更少的稀疏序列，这将允许用户使用更小的 id 类型（int vs bigint），同时向后兼容当前的实现。

我有一个 PostgreSQL 表，我正在尝试将其转换为 TimescaleDB 超表。

该表如下所示：

CREATE TABLE public.data
(
    event_time timestamp with time zone NOT NULL,
    pair_id integer NOT NULL,
    entry_id bigint NOT NULL,
    event_data int NOT NULL,
    CONSTRAINT con1 UNIQUE (pair_id, entry_id ),
    CONSTRAINT pair_id_fkey FOREIGN KEY (pair_id)
        REFERENCES public.pairs (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

当我尝试使用以下命令将此表转换为 TimescaleDB 超表时：

SELECT create_hypertable(
        'data',
        'event_time',
        chunk_time_interval => INTERVAL '1 hour',
        migrate_data => TRUE
);

我得到错误：ERROR: cannot create a unique index without the column "event_time" (used in partitioning)

问题 1：从这篇文章https://stackoverflow.com/questions/55312365/how-to-convert-a-simple-postgresql-table-to-hypertable-or-timescale-db-table-usi我的理解是这个是因为我指定了一个唯一约束 (con1)，它不包含我要分区的列 - event_time。那是对的吗？

问题 2：我应该如何更改我的表或超表才能进行转换？我已经添加了一些关于我计划如何使用数据和数据结构的数据。

数据属性和用法：

• 可以有多个条目具有相同的 event_time - 这些条目将具有按顺序排列的 entry_id
  • 这意味着如果我有 2 个条目 (event_time 2021-05-18::10:16, id 105, <some_data>) 和 (event_time 2021-05-18::10:16, id 107, <some_data>) 那么id 为 106 的条目也将具有 event_time 2021-05-18::10:16
• entry_id 不是我生成的，我使用唯一约束 con1 来确保我没有插入重复数据
• 我将主要在 event_time 上查询数据，例如创建绘图并执行其他分析
• 此时，数据库包含大约 46 亿行，但很快就会包含更多
• 我想利用 TimescaleDB 的速度和良好的压缩
• 我不太关心插入性能

我一直在考虑的解决方案：

• 以某种方式将所有具有相同时间戳的事件打包到一个数组中，并将它们保持在一行中。我认为这会对压缩产生不利影响，并且在查询数据时提供较少的灵活性。此外，我可能最终不得不解压缩每个查询的数据。
• 删除唯一约束 con1 - 那么我如何确保我不会两次添加同一行？
• 扩展唯一约束 con1 以包含 event_time - 这不会以某种方式降低性能，同时会导致我不小心插入 2 行的 entry_id 和 pair_id 但不同的 event_time 的错误？（我怀疑这可能会发生）