Mercurial > libervia-pubsub
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 |
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'; |