主页 / user-242165

Pragy Agarwal's questions

Martin Hope
Pragy Agarwal
Asked: 2022-07-31 12:34:59 +0800 CST
  • 4

Postgres 12+ 支持生成的列：https ://www.postgresql.org/docs/current/ddl-generated-columns.html

从文档来看，语法似乎是有限的——它强制人们显式地命名生成列所依赖的列。

CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

有没有办法将整行传递给生成函数？就像是

CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS generator_function(current_row) STORED
);
postgresql functions
Martin Hope
Pragy Agarwal
Asked: 2021-11-24 06:25:24 +0800 CST
  • 2
  1. 使用以下方法有什么缺点？
  2. 可以采取哪些措施来防止有人错误地更新计数？
  3. 以下方法是反模式吗？如果是，有什么更好的方法存在？

假设：

应用程序是一个公共 wiki/论坛（如 stackexchange）

  1. 该应用程序读取量很大。选择将是最终用户提出的 99% 的查询
  2. 最终用户不执行批量插入。它们可能仅用于开发/维护工作
  3. 需要同时写入表
  4. 计数估计已经足够好了。确切的数量将是最重要的

方法

考虑两个实体，Students每个实体Courses都有对应的表。我们还有一个student_courses存储多对多映射 b/w 学生和课程的表。

create table students (
  id bigserial primary key,
  name text
);

create table courses (
  id bigserial primary key,
  content text
);

create table student_courses (
  student_id bigint not null references students,
  course_id bigint not null references courses,
  primary key (student_id, course_id)
);
create index on student_courses (course_id);

要找到给定学生的课程数量，我们可以执行

select count(1)
from student_courses
where student_id = 123;

如果这些计数查询很频繁（假设您希望始终显示学生姓名和课程计数），优化的一种方法是在两个实体中维护一个计数变量，然后放置插入和删除触发器。

alter table students add column course_count bigint default 0;
alter table courses add column student_count bigint default 0;

create function increment_student_course_count() returns trigger as
$$begin
  update students
    set course_count := course_count + 1
    where student_id = new.student_id;
  update courses
    set student_count := student_count + 1
    where course_id = new.course_id;
  return new;
end;$$

create trigger after_insert_update_counts
after insert
on student_courses
for each row execute procedure increment_student_course_count();


create function decrement_student_course_count() returns trigger as
$$begin
  update students
    set course_count := course_count - 1
    where student_id = new.student_id;
  update courses
    set student_count := student_count - 1
    where course_id = new.course_id;
  return new;
end;$$

create trigger after_delete_update_counts
after delete
on student_courses
for each row execute procedure decrement_student_course_count();

一旦到位，查找学生的课程数量很简单

select course_count
from students where student_id = 123;
database-design postgresql