实时仪表板的Postgres通知

目录

SQL中的观察者模式

观察者示例

在PostgreSQL中观察​

构建工作计划应用程序

架构设计

Postgres通知语法

Postgres LISTEN语法

---

我们将看看我是如何使用Postgres pg_notify功能来为制造公司制定工作计划的。这个特定的产品经历了十几个制造阶段，每次产品进入下一个阶段时，工人都会从他们的工作站记录该进度。我们将在这篇文章中构建的应用程序显示了这个时间表，并让每个人都能看到当天的进度。

SQL中的观察者模式

如果您只习惯于在Postgres中使用SQL的标准，则可能不熟悉NOTIFYaH LISTEN命令。但是使用这两个命令，您可以实现类似于观察者模式的东西，但是是在您的SQL引擎中！

观察者模式允许一类对象“侦听”传入的事件，而另一类则允许向这些侦听器发送事件。这种模式通常用于数据正在更新或更改的情况，并且几个可能不相关的对象需要对这些更改做出反应。

观察者示例

从React组件内部监听Redux存储的状态更改是这种模式的一个常见示例。许多React组件监听Redux存储的单个部分。Android的LiveData 是这种模式的另一个很好的例子，可以创建观察者来观察变化并立即更新应用程序的状态和UI。

在PostgreSQL中观察

使用 pg_notify 作为事件总线的系统架构。

NOTIFY和LISTEN一起工作以允许您在Postgres数据库中实现此设计。通常，将在SQL查询内部调用NOTIFY，通常在触发器中调用。触发器和基于事件的模型可以很好地结合在一起。

从您的Postgres客户端调用LISTEN。当由NOTIFY触发事件时，将通知客户端。该事件包含一个有效负载，因此客户端可以知道触发了什么事件（这也可以包含元数据或来自数据库的实际数据）。您的客户如何接收此通知以及您如何从那里处理它，因客户而异。在我们的示例中，客户端在收到来自pg_notify的信号后将使用WebSockets更新每个连接的调度客户端。

构建工作计划应用程序

架构设计

让我们从Postgres方面开始。我们正在对正在制造的物品进行建模。生产中的项目将由production_item表格表示。每个production_item都有一个相关的product_id  和当前的生产阶段。

我们可以将当前生产阶段存储为production_item表中的一列，但这只会让我们看到项目当前处于哪个阶段。相反，我们将使用一个production_item_wip（正在进行的）表，其中每行将包含一个时间戳，因为项目在生产阶段进行。让我们还创建一个production_stage表来存储所有可能的生产阶段。production_stage将有一个idx整数列来存储阶段发生的顺序。作为示例，下面的查询创建了production_item_wip表。

create table production_item_wip (  
  id serial primary key,  
  insert_time timestamp default NOW(),  
  production_item_id int references production_item(id),  
  production_stage_id int references production_stage(id),  
  employee_id int references employee(id)  
);

小贴士：您可能会注意到我已将其insert_time包含在每张表中。对于这个特定的例子，我们现在不需要在每个表上都使用这个列，但我发现它在未来经常被证明是有用的。我花了大量时间构建查询和提取有用的统计数据，无数次我无法使用数据，因为它缺少insert_time。如果您不确定是否应该在设计数据库架构时添加它，我会犯错误。

Postgres通知语法

使用NOTIFY发送事件非常简单！这是一个向order_progress_event通道发送通知的触发过程。

create
or replace function fn_production_stage_modified() returns trigger as $psql$
begin
  perform pg_notify(
    'order_progress_event',
    'Time to refresh those screens!'
  );return new;
end;$psql$ language plpgsql;

当用于提供实时数据流时，pg_notify非常适合在触发器中使用。但是，您可以轻松地从常规SQL查询中调用pg_notify：select pg_notify('order_progress_event', 'Hello world!');

在PL/pgSQL过程中，您不能SELECT一个函数，如pg_notify，使其返回void.。这样做会导致Postgres错误。这就是为什么在第一个示例中我们使用perform，而在第二个示例中我们可以简单地使用select。

创建该过程后，让我们添加实际的触发器，以便每当项目在生产过程中移动，从而为production_item_wip插入另一行时，就会调用上述过程。

create trigger production_stage before
insert
  on production_item_wip for each row execute procedure fn_production_stage_modified();

就是这样！在本例中，每次的有效载荷都相同。您可以发送实际数据而不仅仅是警报，但在此示例中，我更喜欢发送基本通知，以便客户端应用程序可以接收它，然后反过来，在单独的查询中准确选择它需要的内容。

在通知中编码数据，无论是推送通知还是来自类似pg_notify的通知，都需要您抽象出通知的来源，假设数据通常是通过HTTP API传递的。使用通知作为“提示”让您的软件接触并从HTTP API获取新数据可以简化流程并帮助您减少需要维护的不同数据源的数量。

Postgres LISTEN语法

收听频道更简单： LISTEN order_progress_event;

这真的是全部！

调用此事件时，我们需要选择当天的最新生产数据。这是一个视图，将显示今天有多少产品通过每个生产阶段取得进展：

create view view_daily_production_stats as
select
  count(1) as stage_count,
  ps.name as stage_namefrom production_item_wip piw
  join production_stage ps on ps.id = _
       piw.production_stage_idwhere date(piw.insert_time) = date(now())
group by
  ps.id

现在您的客户端正在监听，您如何对其接收到的事件做出反应？这因客户端而异，因为为异步事件提供服务的编程语言的特性集差异很大。我们在这个例子中使用JavaScript的pg客户端。JavaScript通常用于异步Web编程。

var clients = [];
function eventCallback(event) {
  query('select * from view_daily_production_stats', (data) => {
    clients.map(c => {
      c.send(data);
    });
  });
}
client.connect(function(err, client) {
  var query = client.query("LISTEN order_progress_event");
  client.on("notification", eventCallback);
});
;

每当PostgreSQL客户端接收到新事件时， eventCallback函数将被NOTIFY的有效负载调用。然后回调查询我们之前编写的视图以选择最新的生产阶段数据，并循环将新数据发送到所有侦听客户端（Raspberry Pi）。客户端接收数据并呈现HTML。

https://www.codeproject.com/Articles/5298142/Postgres-Notify-for-Real-Time-Dashboards