annotate db/sat_pubsub_update_7_8.sql @ 451:0e6e176cb572

pgsql: fix items order: Due to a misinterpretation, items where returned in wrong order when RSM was used. Pubsub vanilla `max_items` is reversing the items order to get last items, but when this attribute is not used (RSM being used or not), the items must be in chronological order. This patch fixes it, so RSM returns oldest items by defaut, and empty `<before/>` must be used to get last page (and thus most recent items). Items are always finally ordered chronologically inside the returned page (default by `update` time, but this can be modified when a specific order is requested). Reference field is also fixed to use `updated` instead of `item_id` when item are ordered by `update`.
author Goffi <goffi@goffi.org>
date Sun, 06 Jun 2021 19:42:57 +0200
parents b5e1e8d93dd4
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
438
b5e1e8d93dd4 backend, pgsql: new `overwrite_policy` node setting:
Goffi <goffi@goffi.org>
parents:
diff changeset
1 -- we check version of the database before doing anything
b5e1e8d93dd4 backend, pgsql: new `overwrite_policy` node setting:
Goffi <goffi@goffi.org>
parents:
diff changeset
2 -- and stop execution if not good
b5e1e8d93dd4 backend, pgsql: new `overwrite_policy` node setting:
Goffi <goffi@goffi.org>
parents:
diff changeset
3 \set ON_ERROR_STOP
b5e1e8d93dd4 backend, pgsql: new `overwrite_policy` node setting:
Goffi <goffi@goffi.org>
parents:
diff changeset
4 DO $$
b5e1e8d93dd4 backend, pgsql: new `overwrite_policy` node setting:
Goffi <goffi@goffi.org>
parents:
diff changeset
5 DECLARE ver text;
b5e1e8d93dd4 backend, pgsql: new `overwrite_policy` node setting:
Goffi <goffi@goffi.org>
parents:
diff changeset
6 BEGIN
b5e1e8d93dd4 backend, pgsql: new `overwrite_policy` node setting:
Goffi <goffi@goffi.org>
parents:
diff changeset
7 SELECT value INTO ver FROM metadata WHERE key='version';
b5e1e8d93dd4 backend, pgsql: new `overwrite_policy` node setting:
Goffi <goffi@goffi.org>
parents:
diff changeset
8 IF NOT FOUND OR ver!='7' THEN
b5e1e8d93dd4 backend, pgsql: new `overwrite_policy` node setting:
Goffi <goffi@goffi.org>
parents:
diff changeset
9 RAISE EXCEPTION 'This update file needs to be applied on database schema version 7, you use version %',ver;
b5e1e8d93dd4 backend, pgsql: new `overwrite_policy` node setting:
Goffi <goffi@goffi.org>
parents:
diff changeset
10 END IF;
b5e1e8d93dd4 backend, pgsql: new `overwrite_policy` node setting:
Goffi <goffi@goffi.org>
parents:
diff changeset
11 END$$;
b5e1e8d93dd4 backend, pgsql: new `overwrite_policy` node setting:
Goffi <goffi@goffi.org>
parents:
diff changeset
12 \unset ON_ERROR_STOP
b5e1e8d93dd4 backend, pgsql: new `overwrite_policy` node setting:
Goffi <goffi@goffi.org>
parents:
diff changeset
13 -- end of version check
b5e1e8d93dd4 backend, pgsql: new `overwrite_policy` node setting:
Goffi <goffi@goffi.org>
parents:
diff changeset
14
b5e1e8d93dd4 backend, pgsql: new `overwrite_policy` node setting:
Goffi <goffi@goffi.org>
parents:
diff changeset
15 /* new "overwrite_policy" option */
b5e1e8d93dd4 backend, pgsql: new `overwrite_policy` node setting:
Goffi <goffi@goffi.org>
parents:
diff changeset
16 ALTER TABLE nodes ADD COLUMN overwrite_policy text NOT NULL DEFAULT 'original_publisher'
b5e1e8d93dd4 backend, pgsql: new `overwrite_policy` node setting:
Goffi <goffi@goffi.org>
parents:
diff changeset
17 CHECK (overwrite_policy IN ('original_publisher', 'any_publisher'));
b5e1e8d93dd4 backend, pgsql: new `overwrite_policy` node setting:
Goffi <goffi@goffi.org>
parents:
diff changeset
18
b5e1e8d93dd4 backend, pgsql: new `overwrite_policy` node setting:
Goffi <goffi@goffi.org>
parents:
diff changeset
19 UPDATE metadata SET value='8' WHERE key='version';