2 -- track when content was delivered as separate from latest content delivered
3 -- content_delivered continues to be the time the content was delivered, but becomes only informational
4 -- latest_content_delivered is date on topic content delivered
5 ALTER TABLE subscription
6 ADD COLUMN latest_content_delivered
TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz
8 CREATE INDEX subscription_latest_content_delivered_idx
ON subscription(latest_content_delivered
);
9 -- migrate existing values
10 UPDATE subscription
SET latest_content_delivered
= content_delivered
;
11 -- no need for this index
12 DROP INDEX subscription_content_delivered_idx
;
13 -- update the view to use latest_cotnent_delivered
14 CREATE OR REPLACE VIEW subscription_delivery_needed
AS
16 FROM subscription s
JOIN topic t
ON s.topic_id
= t.
id
20 s.latest_content_delivered
< t.content_updated
22 s.delivery_next_attempt
< now()
24 s.
id NOT IN (SELECT id FROM subscription_delivery_in_progress_active
)
27 INSERT INTO _meta_schema_version (major
, minor
, patch
) VALUES (1, 0, 2);