Sql

昨天设计了一个quota表，后端代码这样一个初始化的逻辑： 如果quota表为空，则插入一条默认的记录。 上线调试的时候，发现会报错quotas_pkey duplicated，而且只会报一次， 重启后端应用不会再报错。 但是清空table之后，重启后端会再报同样的错。 下面是表结构： 1 2 3 4 5 6 7 8 CREATE TABLE IF NOT EXISTS quotas ( id SERIAL PRIMARY KEY NOT NULL, concurrent smallint NOT NULL DEFAULT 15, total smallint NOT NULL DEFAULT 1000, speed smallint NOT NULL DEFAULT 200, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); 排查一段时间后发生，是下面一段sql代码的问题： 1 2 -- name: InitQuota :exec INSERT INTO quotas (id) VALUES (1); 这段代码想实现的是插入一条id为1的默认记录。后端是做了判断的，只有quotas表为空的时候才执行这条sql语句。 ...

昨天同事调试前端页面分页功能时， 发现了一个分页的问题。 问题简要描述如下： 前端选择一些过滤条件（a&&b&&c||d …）向后端请求数据，过一会发现用同样的过滤条件去查询，数据变少了，前端看上去第一页和最后一页是一样的。 初步怀疑是分页出了问题。 这个分页的问题比较麻烦，不能稳定复现，一会出现一会又不出现。 分析了很长一段时间后，发现是后台的定时任务更新了db数据使得很多数据不再符合前面的过滤条件，后端框架返回的总页码数，和data的数量不符。 也就是说后端返回的总页码数是脏/旧数据。 脏数据 查看后端框架代码时候，发现后端查询db的执行了count 和 select 两条query： 1 2 3 4 5 6 select count(*) form table_name where condition_a -- meamwhile other workers update table_a -- A LOT in short time -- or they(the workers) MIGHT lock -- the WHOLE table for READ select * form table_name where condition_a 这就是是造成脏数据的原因。 解决方案 window function 可以用 window function 做到上述两条query的同时查询（其实就是一条查询）： 1 2 3 4 5 6 SELECT *, count(*) OVER() AS full_count FROM tbl WHERE -- /* whatever */ ORDER BY col1 LIMIT ? OFFSET ? Common Table Expressions (cte) However, as Dani pointed out, when OFFSET is at least as great as the number of rows returned from the base query, no rows are returned. So we also don’t get full_count. ...