agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH v10 6/7] Row pattern recognition patch (tests). 7+ messages / 2 participants [nested] [flat]
* [PATCH v10 6/7] Row pattern recognition patch (tests). @ 2023-10-22 02:22 Tatsuo Ishii <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Tatsuo Ishii @ 2023-10-22 02:22 UTC (permalink / raw) --- src/test/regress/expected/rpr.out | 709 +++++++++++++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/rpr.sql | 338 ++++++++++++++ 3 files changed, 1048 insertions(+), 1 deletion(-) create mode 100644 src/test/regress/expected/rpr.out create mode 100644 src/test/regress/sql/rpr.sql diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out new file mode 100644 index 0000000000..8f8254d3b2 --- /dev/null +++ b/src/test/regress/expected/rpr.out @@ -0,0 +1,709 @@ +-- +-- Test for row pattern definition clause +-- +CREATE TEMP TABLE stock ( + company TEXT, + tdate DATE, + price INTEGER +); +INSERT INTO stock VALUES ('company1', '2023-07-01', 100); +INSERT INTO stock VALUES ('company1', '2023-07-02', 200); +INSERT INTO stock VALUES ('company1', '2023-07-03', 150); +INSERT INTO stock VALUES ('company1', '2023-07-04', 140); +INSERT INTO stock VALUES ('company1', '2023-07-05', 150); +INSERT INTO stock VALUES ('company1', '2023-07-06', 90); +INSERT INTO stock VALUES ('company1', '2023-07-07', 110); +INSERT INTO stock VALUES ('company1', '2023-07-08', 130); +INSERT INTO stock VALUES ('company1', '2023-07-09', 120); +INSERT INTO stock VALUES ('company1', '2023-07-10', 130); +INSERT INTO stock VALUES ('company2', '2023-07-01', 50); +INSERT INTO stock VALUES ('company2', '2023-07-02', 2000); +INSERT INTO stock VALUES ('company2', '2023-07-03', 1500); +INSERT INTO stock VALUES ('company2', '2023-07-04', 1400); +INSERT INTO stock VALUES ('company2', '2023-07-05', 1500); +INSERT INTO stock VALUES ('company2', '2023-07-06', 60); +INSERT INTO stock VALUES ('company2', '2023-07-07', 1100); +INSERT INTO stock VALUES ('company2', '2023-07-08', 1300); +INSERT INTO stock VALUES ('company2', '2023-07-09', 1200); +INSERT INTO stock VALUES ('company2', '2023-07-10', 1300); +SELECT * FROM stock; + company | tdate | price +----------+------------+------- + company1 | 07-01-2023 | 100 + company1 | 07-02-2023 | 200 + company1 | 07-03-2023 | 150 + company1 | 07-04-2023 | 140 + company1 | 07-05-2023 | 150 + company1 | 07-06-2023 | 90 + company1 | 07-07-2023 | 110 + company1 | 07-08-2023 | 130 + company1 | 07-09-2023 | 120 + company1 | 07-10-2023 | 130 + company2 | 07-01-2023 | 50 + company2 | 07-02-2023 | 2000 + company2 | 07-03-2023 | 1500 + company2 | 07-04-2023 | 1400 + company2 | 07-05-2023 | 1500 + company2 | 07-06-2023 | 60 + company2 | 07-07-2023 | 1100 + company2 | 07-08-2023 | 1300 + company2 | 07-09-2023 | 1200 + company2 | 07-10-2023 | 1300 +(20 rows) + +-- basic test using PREV +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, + nth_value(tdate, 2) OVER w AS nth_second + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value | nth_second +----------+------------+-------+-------------+------------+------------ + company1 | 07-01-2023 | 100 | 100 | 140 | 07-02-2023 + company1 | 07-02-2023 | 200 | | | + company1 | 07-03-2023 | 150 | | | + company1 | 07-04-2023 | 140 | | | + company1 | 07-05-2023 | 150 | | | + company1 | 07-06-2023 | 90 | 90 | 120 | 07-07-2023 + company1 | 07-07-2023 | 110 | | | + company1 | 07-08-2023 | 130 | | | + company1 | 07-09-2023 | 120 | | | + company1 | 07-10-2023 | 130 | | | + company2 | 07-01-2023 | 50 | 50 | 1400 | 07-02-2023 + company2 | 07-02-2023 | 2000 | | | + company2 | 07-03-2023 | 1500 | | | + company2 | 07-04-2023 | 1400 | | | + company2 | 07-05-2023 | 1500 | | | + company2 | 07-06-2023 | 60 | 60 | 1200 | 07-07-2023 + company2 | 07-07-2023 | 1100 | | | + company2 | 07-08-2023 | 1300 | | | + company2 | 07-09-2023 | 1200 | | | + company2 | 07-10-2023 | 1300 | | | +(20 rows) + +-- basic test using PREV. UP appears twice +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, + nth_value(tdate, 2) OVER w AS nth_second + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+ UP+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value | nth_second +----------+------------+-------+-------------+------------+------------ + company1 | 07-01-2023 | 100 | 100 | 150 | 07-02-2023 + company1 | 07-02-2023 | 200 | | | + company1 | 07-03-2023 | 150 | | | + company1 | 07-04-2023 | 140 | | | + company1 | 07-05-2023 | 150 | | | + company1 | 07-06-2023 | 90 | 90 | 130 | 07-07-2023 + company1 | 07-07-2023 | 110 | | | + company1 | 07-08-2023 | 130 | | | + company1 | 07-09-2023 | 120 | | | + company1 | 07-10-2023 | 130 | | | + company2 | 07-01-2023 | 50 | 50 | 1500 | 07-02-2023 + company2 | 07-02-2023 | 2000 | | | + company2 | 07-03-2023 | 1500 | | | + company2 | 07-04-2023 | 1400 | | | + company2 | 07-05-2023 | 1500 | | | + company2 | 07-06-2023 | 60 | 60 | 1300 | 07-07-2023 + company2 | 07-07-2023 | 1100 | | | + company2 | 07-08-2023 | 1300 | | | + company2 | 07-09-2023 | 1200 | | | + company2 | 07-10-2023 | 1300 | | | +(20 rows) + +-- basic test using PREV. Use '*' +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, + nth_value(tdate, 2) OVER w AS nth_second + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP* DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value | nth_second +----------+------------+-------+-------------+------------+------------ + company1 | 07-01-2023 | 100 | 100 | 140 | 07-02-2023 + company1 | 07-02-2023 | 200 | | | + company1 | 07-03-2023 | 150 | | | + company1 | 07-04-2023 | 140 | | | + company1 | 07-05-2023 | 150 | 150 | 90 | 07-06-2023 + company1 | 07-06-2023 | 90 | | | + company1 | 07-07-2023 | 110 | 110 | 120 | 07-08-2023 + company1 | 07-08-2023 | 130 | | | + company1 | 07-09-2023 | 120 | | | + company1 | 07-10-2023 | 130 | | | + company2 | 07-01-2023 | 50 | 50 | 1400 | 07-02-2023 + company2 | 07-02-2023 | 2000 | | | + company2 | 07-03-2023 | 1500 | | | + company2 | 07-04-2023 | 1400 | | | + company2 | 07-05-2023 | 1500 | 1500 | 60 | 07-06-2023 + company2 | 07-06-2023 | 60 | | | + company2 | 07-07-2023 | 1100 | 1100 | 1200 | 07-08-2023 + company2 | 07-08-2023 | 1300 | | | + company2 | 07-09-2023 | 1200 | | | + company2 | 07-10-2023 | 1300 | | | +(20 rows) + +-- last_value() should remain consistent +SELECT company, tdate, price, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + company | tdate | price | last_value +----------+------------+-------+------------ + company1 | 07-01-2023 | 100 | 140 + company1 | 07-02-2023 | 200 | + company1 | 07-03-2023 | 150 | + company1 | 07-04-2023 | 140 | + company1 | 07-05-2023 | 150 | + company1 | 07-06-2023 | 90 | 120 + company1 | 07-07-2023 | 110 | + company1 | 07-08-2023 | 130 | + company1 | 07-09-2023 | 120 | + company1 | 07-10-2023 | 130 | + company2 | 07-01-2023 | 50 | 1400 + company2 | 07-02-2023 | 2000 | + company2 | 07-03-2023 | 1500 | + company2 | 07-04-2023 | 1400 | + company2 | 07-05-2023 | 1500 | + company2 | 07-06-2023 | 60 | 1200 + company2 | 07-07-2023 | 1100 | + company2 | 07-08-2023 | 1300 | + company2 | 07-09-2023 | 1200 | + company2 | 07-10-2023 | 1300 | +(20 rows) + +-- omit "START" in DEFINE but it is ok because "START AS TRUE" is +-- implicitly defined. per spec. +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, + nth_value(tdate, 2) OVER w AS nth_second + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value | nth_second +----------+------------+-------+-------------+------------+------------ + company1 | 07-01-2023 | 100 | 100 | 140 | 07-02-2023 + company1 | 07-02-2023 | 200 | | | + company1 | 07-03-2023 | 150 | | | + company1 | 07-04-2023 | 140 | | | + company1 | 07-05-2023 | 150 | | | + company1 | 07-06-2023 | 90 | 90 | 120 | 07-07-2023 + company1 | 07-07-2023 | 110 | | | + company1 | 07-08-2023 | 130 | | | + company1 | 07-09-2023 | 120 | | | + company1 | 07-10-2023 | 130 | | | + company2 | 07-01-2023 | 50 | 50 | 1400 | 07-02-2023 + company2 | 07-02-2023 | 2000 | | | + company2 | 07-03-2023 | 1500 | | | + company2 | 07-04-2023 | 1400 | | | + company2 | 07-05-2023 | 1500 | | | + company2 | 07-06-2023 | 60 | 60 | 1200 | 07-07-2023 + company2 | 07-07-2023 | 1100 | | | + company2 | 07-08-2023 | 1300 | | | + company2 | 07-09-2023 | 1200 | | | + company2 | 07-10-2023 | 1300 | | | +(20 rows) + +-- the first row start with less than or equal to 100 +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (LOWPRICE UP+ DOWN+) + DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value +----------+------------+-------+-------------+------------ + company1 | 07-01-2023 | 100 | 100 | 140 + company1 | 07-02-2023 | 200 | | + company1 | 07-03-2023 | 150 | | + company1 | 07-04-2023 | 140 | | + company1 | 07-05-2023 | 150 | | + company1 | 07-06-2023 | 90 | 90 | 120 + company1 | 07-07-2023 | 110 | | + company1 | 07-08-2023 | 130 | | + company1 | 07-09-2023 | 120 | | + company1 | 07-10-2023 | 130 | | + company2 | 07-01-2023 | 50 | 50 | 1400 + company2 | 07-02-2023 | 2000 | | + company2 | 07-03-2023 | 1500 | | + company2 | 07-04-2023 | 1400 | | + company2 | 07-05-2023 | 1500 | | + company2 | 07-06-2023 | 60 | 60 | 1200 + company2 | 07-07-2023 | 1100 | | + company2 | 07-08-2023 | 1300 | | + company2 | 07-09-2023 | 1200 | | + company2 | 07-10-2023 | 1300 | | +(20 rows) + +-- second row raises 120% +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (LOWPRICE UP+ DOWN+) + DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price) * 1.2, + DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value +----------+------------+-------+-------------+------------ + company1 | 07-01-2023 | 100 | 100 | 140 + company1 | 07-02-2023 | 200 | | + company1 | 07-03-2023 | 150 | | + company1 | 07-04-2023 | 140 | | + company1 | 07-05-2023 | 150 | | + company1 | 07-06-2023 | 90 | | + company1 | 07-07-2023 | 110 | | + company1 | 07-08-2023 | 130 | | + company1 | 07-09-2023 | 120 | | + company1 | 07-10-2023 | 130 | | + company2 | 07-01-2023 | 50 | 50 | 1400 + company2 | 07-02-2023 | 2000 | | + company2 | 07-03-2023 | 1500 | | + company2 | 07-04-2023 | 1400 | | + company2 | 07-05-2023 | 1500 | | + company2 | 07-06-2023 | 60 | | + company2 | 07-07-2023 | 1100 | | + company2 | 07-08-2023 | 1300 | | + company2 | 07-09-2023 | 1200 | | + company2 | 07-10-2023 | 1300 | | +(20 rows) + +-- using NEXT +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UPDOWN) + DEFINE + START AS TRUE, + UPDOWN AS price > PREV(price) AND price > NEXT(price) +); + company | tdate | price | first_value | last_value +----------+------------+-------+-------------+------------ + company1 | 07-01-2023 | 100 | 100 | 200 + company1 | 07-02-2023 | 200 | | + company1 | 07-03-2023 | 150 | | + company1 | 07-04-2023 | 140 | 140 | 150 + company1 | 07-05-2023 | 150 | | + company1 | 07-06-2023 | 90 | | + company1 | 07-07-2023 | 110 | 110 | 130 + company1 | 07-08-2023 | 130 | | + company1 | 07-09-2023 | 120 | | + company1 | 07-10-2023 | 130 | | + company2 | 07-01-2023 | 50 | 50 | 2000 + company2 | 07-02-2023 | 2000 | | + company2 | 07-03-2023 | 1500 | | + company2 | 07-04-2023 | 1400 | 1400 | 1500 + company2 | 07-05-2023 | 1500 | | + company2 | 07-06-2023 | 60 | | + company2 | 07-07-2023 | 1100 | 1100 | 1300 + company2 | 07-08-2023 | 1300 | | + company2 | 07-09-2023 | 1200 | | + company2 | 07-10-2023 | 1300 | | +(20 rows) + +-- using AFTER MATCH SKIP TO NEXT ROW +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + INITIAL + PATTERN (START UPDOWN) + DEFINE + START AS TRUE, + UPDOWN AS price > PREV(price) AND price > NEXT(price) +); + company | tdate | price | first_value | last_value +----------+------------+-------+-------------+------------ + company1 | 07-01-2023 | 100 | 100 | 200 + company1 | 07-02-2023 | 200 | | + company1 | 07-03-2023 | 150 | | + company1 | 07-04-2023 | 140 | 140 | 150 + company1 | 07-05-2023 | 150 | | + company1 | 07-06-2023 | 90 | | + company1 | 07-07-2023 | 110 | 110 | 130 + company1 | 07-08-2023 | 130 | | + company1 | 07-09-2023 | 120 | | + company1 | 07-10-2023 | 130 | | + company2 | 07-01-2023 | 50 | 50 | 2000 + company2 | 07-02-2023 | 2000 | | + company2 | 07-03-2023 | 1500 | | + company2 | 07-04-2023 | 1400 | 1400 | 1500 + company2 | 07-05-2023 | 1500 | | + company2 | 07-06-2023 | 60 | | + company2 | 07-07-2023 | 1100 | 1100 | 1300 + company2 | 07-08-2023 | 1300 | | + company2 | 07-09-2023 | 1200 | | + company2 | 07-10-2023 | 1300 | | +(20 rows) + +-- match everything +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (A+) + DEFINE + A AS TRUE +); + company | tdate | price | first_value | last_value +----------+------------+-------+-------------+------------ + company1 | 07-01-2023 | 100 | 100 | 130 + company1 | 07-02-2023 | 200 | | + company1 | 07-03-2023 | 150 | | + company1 | 07-04-2023 | 140 | | + company1 | 07-05-2023 | 150 | | + company1 | 07-06-2023 | 90 | | + company1 | 07-07-2023 | 110 | | + company1 | 07-08-2023 | 130 | | + company1 | 07-09-2023 | 120 | | + company1 | 07-10-2023 | 130 | | + company2 | 07-01-2023 | 50 | 50 | 1300 + company2 | 07-02-2023 | 2000 | | + company2 | 07-03-2023 | 1500 | | + company2 | 07-04-2023 | 1400 | | + company2 | 07-05-2023 | 1500 | | + company2 | 07-06-2023 | 60 | | + company2 | 07-07-2023 | 1100 | | + company2 | 07-08-2023 | 1300 | | + company2 | 07-09-2023 | 1200 | | + company2 | 07-10-2023 | 1300 | | +(20 rows) + +-- backtracking with reclassification of rows +-- using AFTER MATCH SKIP PAST LAST ROW +SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (A+ B+) + DEFINE + A AS price > 100, + B AS price > 100 +); + company | tdate | price | first_value | last_value +----------+------------+-------+-------------+------------ + company1 | 07-01-2023 | 100 | | + company1 | 07-02-2023 | 200 | 07-02-2023 | 07-05-2023 + company1 | 07-03-2023 | 150 | | + company1 | 07-04-2023 | 140 | | + company1 | 07-05-2023 | 150 | | + company1 | 07-06-2023 | 90 | | + company1 | 07-07-2023 | 110 | 07-07-2023 | 07-10-2023 + company1 | 07-08-2023 | 130 | | + company1 | 07-09-2023 | 120 | | + company1 | 07-10-2023 | 130 | | + company2 | 07-01-2023 | 50 | | + company2 | 07-02-2023 | 2000 | 07-02-2023 | 07-05-2023 + company2 | 07-03-2023 | 1500 | | + company2 | 07-04-2023 | 1400 | | + company2 | 07-05-2023 | 1500 | | + company2 | 07-06-2023 | 60 | | + company2 | 07-07-2023 | 1100 | 07-07-2023 | 07-10-2023 + company2 | 07-08-2023 | 1300 | | + company2 | 07-09-2023 | 1200 | | + company2 | 07-10-2023 | 1300 | | +(20 rows) + +-- backtracking with reclassification of rows +-- using AFTER MATCH SKIP TO NEXT ROW +SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + INITIAL + PATTERN (A+ B+) + DEFINE + A AS price > 100, + B AS price > 100 +); + company | tdate | price | first_value | last_value +----------+------------+-------+-------------+------------ + company1 | 07-01-2023 | 100 | | + company1 | 07-02-2023 | 200 | 07-02-2023 | 07-05-2023 + company1 | 07-03-2023 | 150 | 07-03-2023 | 07-05-2023 + company1 | 07-04-2023 | 140 | 07-04-2023 | 07-05-2023 + company1 | 07-05-2023 | 150 | | + company1 | 07-06-2023 | 90 | | + company1 | 07-07-2023 | 110 | 07-07-2023 | 07-10-2023 + company1 | 07-08-2023 | 130 | 07-08-2023 | 07-10-2023 + company1 | 07-09-2023 | 120 | 07-09-2023 | 07-10-2023 + company1 | 07-10-2023 | 130 | | + company2 | 07-01-2023 | 50 | | + company2 | 07-02-2023 | 2000 | 07-02-2023 | 07-05-2023 + company2 | 07-03-2023 | 1500 | 07-03-2023 | 07-05-2023 + company2 | 07-04-2023 | 1400 | 07-04-2023 | 07-05-2023 + company2 | 07-05-2023 | 1500 | | + company2 | 07-06-2023 | 60 | | + company2 | 07-07-2023 | 1100 | 07-07-2023 | 07-10-2023 + company2 | 07-08-2023 | 1300 | 07-08-2023 | 07-10-2023 + company2 | 07-09-2023 | 1200 | 07-09-2023 | 07-10-2023 + company2 | 07-10-2023 | 1300 | | +(20 rows) + +-- ROWS BETWEEN CURRENT ROW AND offset FOLLOWING +SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w, + count(*) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value | count +----------+------------+-------+-------------+------------+------- + company1 | 07-01-2023 | 100 | 07-01-2023 | 07-03-2023 | 3 + company1 | 07-02-2023 | 200 | | | + company1 | 07-03-2023 | 150 | | | + company1 | 07-04-2023 | 140 | 07-04-2023 | 07-06-2023 | 3 + company1 | 07-05-2023 | 150 | | | + company1 | 07-06-2023 | 90 | | | + company1 | 07-07-2023 | 110 | 07-07-2023 | 07-09-2023 | 3 + company1 | 07-08-2023 | 130 | | | + company1 | 07-09-2023 | 120 | | | + company1 | 07-10-2023 | 130 | | | 0 + company2 | 07-01-2023 | 50 | 07-01-2023 | 07-03-2023 | 3 + company2 | 07-02-2023 | 2000 | | | + company2 | 07-03-2023 | 1500 | | | + company2 | 07-04-2023 | 1400 | 07-04-2023 | 07-06-2023 | 3 + company2 | 07-05-2023 | 1500 | | | + company2 | 07-06-2023 | 60 | | | + company2 | 07-07-2023 | 1100 | 07-07-2023 | 07-09-2023 | 3 + company2 | 07-08-2023 | 1300 | | | + company2 | 07-09-2023 | 1200 | | | + company2 | 07-10-2023 | 1300 | | | 0 +(20 rows) + +-- +-- Aggregates +-- +-- using AFTER MATCH SKIP PAST LAST ROW +SELECT company, tdate, price, + first_value(price) OVER w, + last_value(price) OVER w, + max(price) OVER w, + min(price) OVER w, + sum(price) OVER w, + avg(price) OVER w, + count(price) OVER w +FROM stock +WINDOW w AS ( +PARTITION BY company +ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING +AFTER MATCH SKIP PAST LAST ROW +INITIAL +PATTERN (START UP+ DOWN+) +DEFINE +START AS TRUE, +UP AS price > PREV(price), +DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value | max | min | sum | avg | count +----------+------------+-------+-------------+------------+------+-----+------+-----------------------+------- + company1 | 07-01-2023 | 100 | 100 | 140 | 200 | 100 | 590 | 147.5000000000000000 | 4 + company1 | 07-02-2023 | 200 | | | | | | | + company1 | 07-03-2023 | 150 | | | | | | | + company1 | 07-04-2023 | 140 | | | | | | | + company1 | 07-05-2023 | 150 | | | | | | | 0 + company1 | 07-06-2023 | 90 | 90 | 120 | 130 | 90 | 450 | 112.5000000000000000 | 4 + company1 | 07-07-2023 | 110 | | | | | | | + company1 | 07-08-2023 | 130 | | | | | | | + company1 | 07-09-2023 | 120 | | | | | | | + company1 | 07-10-2023 | 130 | | | | | | | 0 + company2 | 07-01-2023 | 50 | 50 | 1400 | 2000 | 50 | 4950 | 1237.5000000000000000 | 4 + company2 | 07-02-2023 | 2000 | | | | | | | + company2 | 07-03-2023 | 1500 | | | | | | | + company2 | 07-04-2023 | 1400 | | | | | | | + company2 | 07-05-2023 | 1500 | | | | | | | 0 + company2 | 07-06-2023 | 60 | 60 | 1200 | 1300 | 60 | 3660 | 915.0000000000000000 | 4 + company2 | 07-07-2023 | 1100 | | | | | | | + company2 | 07-08-2023 | 1300 | | | | | | | + company2 | 07-09-2023 | 1200 | | | | | | | + company2 | 07-10-2023 | 1300 | | | | | | | 0 +(20 rows) + +-- using AFTER MATCH SKIP TO NEXT ROW +SELECT company, tdate, price, + first_value(price) OVER w, + last_value(price) OVER w, + max(price) OVER w, + min(price) OVER w, + sum(price) OVER w, + avg(price) OVER w, + count(price) OVER w +FROM stock +WINDOW w AS ( +PARTITION BY company +ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING +AFTER MATCH SKIP TO NEXT ROW +INITIAL +PATTERN (START UP+ DOWN+) +DEFINE +START AS TRUE, +UP AS price > PREV(price), +DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value | max | min | sum | avg | count +----------+------------+-------+-------------+------------+------+------+------+-----------------------+------- + company1 | 07-01-2023 | 100 | 100 | 140 | 200 | 100 | 590 | 147.5000000000000000 | 4 + company1 | 07-02-2023 | 200 | | | | | | | 0 + company1 | 07-03-2023 | 150 | | | | | | | 0 + company1 | 07-04-2023 | 140 | 140 | 90 | 150 | 90 | 380 | 126.6666666666666667 | 3 + company1 | 07-05-2023 | 150 | | | | | | | 0 + company1 | 07-06-2023 | 90 | 90 | 120 | 130 | 90 | 450 | 112.5000000000000000 | 4 + company1 | 07-07-2023 | 110 | 110 | 120 | 130 | 110 | 360 | 120.0000000000000000 | 3 + company1 | 07-08-2023 | 130 | | | | | | | 0 + company1 | 07-09-2023 | 120 | | | | | | | 0 + company1 | 07-10-2023 | 130 | | | | | | | 0 + company2 | 07-01-2023 | 50 | 50 | 1400 | 2000 | 50 | 4950 | 1237.5000000000000000 | 4 + company2 | 07-02-2023 | 2000 | | | | | | | 0 + company2 | 07-03-2023 | 1500 | | | | | | | 0 + company2 | 07-04-2023 | 1400 | 1400 | 60 | 1500 | 60 | 2960 | 986.6666666666666667 | 3 + company2 | 07-05-2023 | 1500 | | | | | | | 0 + company2 | 07-06-2023 | 60 | 60 | 1200 | 1300 | 60 | 3660 | 915.0000000000000000 | 4 + company2 | 07-07-2023 | 1100 | 1100 | 1200 | 1300 | 1100 | 3600 | 1200.0000000000000000 | 3 + company2 | 07-08-2023 | 1300 | | | | | | | 0 + company2 | 07-09-2023 | 1200 | | | | | | | 0 + company2 | 07-10-2023 | 1300 | | | | | | | 0 +(20 rows) + +-- +-- Error cases +-- +-- row pattern definition variable name must not appear more than once +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + ORDER BY tdate + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price), + UP AS price > PREV(price) +); +ERROR: syntax error at or near "ORDER" +LINE 6: ORDER BY tdate + ^ +-- pattern variable name must appear in DEFINE +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+ END) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); +ERROR: syntax error at or near "END" +LINE 8: PATTERN (START UP+ DOWN+ END) + ^ +-- FRAME must start at current row when row patttern recognition is used +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); +ERROR: FRAME must start at current row when row patttern recognition is used +-- SEEK is not supported +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + SEEK + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); +ERROR: SEEK is not supported +LINE 8: SEEK + ^ +HINT: Use INITIAL. diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 4df9d8503b..896531002b 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -98,7 +98,7 @@ test: publication subscription # Another group of parallel tests # select_views depends on create_view # ---------- -test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock indirect_toast equivclass +test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock indirect_toast equivclass rpr # ---------- # Another group of parallel tests (JSON related) diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql new file mode 100644 index 0000000000..38309652f9 --- /dev/null +++ b/src/test/regress/sql/rpr.sql @@ -0,0 +1,338 @@ +-- +-- Test for row pattern definition clause +-- + +CREATE TEMP TABLE stock ( + company TEXT, + tdate DATE, + price INTEGER +); +INSERT INTO stock VALUES ('company1', '2023-07-01', 100); +INSERT INTO stock VALUES ('company1', '2023-07-02', 200); +INSERT INTO stock VALUES ('company1', '2023-07-03', 150); +INSERT INTO stock VALUES ('company1', '2023-07-04', 140); +INSERT INTO stock VALUES ('company1', '2023-07-05', 150); +INSERT INTO stock VALUES ('company1', '2023-07-06', 90); +INSERT INTO stock VALUES ('company1', '2023-07-07', 110); +INSERT INTO stock VALUES ('company1', '2023-07-08', 130); +INSERT INTO stock VALUES ('company1', '2023-07-09', 120); +INSERT INTO stock VALUES ('company1', '2023-07-10', 130); +INSERT INTO stock VALUES ('company2', '2023-07-01', 50); +INSERT INTO stock VALUES ('company2', '2023-07-02', 2000); +INSERT INTO stock VALUES ('company2', '2023-07-03', 1500); +INSERT INTO stock VALUES ('company2', '2023-07-04', 1400); +INSERT INTO stock VALUES ('company2', '2023-07-05', 1500); +INSERT INTO stock VALUES ('company2', '2023-07-06', 60); +INSERT INTO stock VALUES ('company2', '2023-07-07', 1100); +INSERT INTO stock VALUES ('company2', '2023-07-08', 1300); +INSERT INTO stock VALUES ('company2', '2023-07-09', 1200); +INSERT INTO stock VALUES ('company2', '2023-07-10', 1300); + +SELECT * FROM stock; + +-- basic test using PREV +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, + nth_value(tdate, 2) OVER w AS nth_second + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- basic test using PREV. UP appears twice +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, + nth_value(tdate, 2) OVER w AS nth_second + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+ UP+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- basic test using PREV. Use '*' +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, + nth_value(tdate, 2) OVER w AS nth_second + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP* DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- last_value() should remain consistent +SELECT company, tdate, price, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- omit "START" in DEFINE but it is ok because "START AS TRUE" is +-- implicitly defined. per spec. +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, + nth_value(tdate, 2) OVER w AS nth_second + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- the first row start with less than or equal to 100 +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (LOWPRICE UP+ DOWN+) + DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- second row raises 120% +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (LOWPRICE UP+ DOWN+) + DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price) * 1.2, + DOWN AS price < PREV(price) +); + +-- using NEXT +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UPDOWN) + DEFINE + START AS TRUE, + UPDOWN AS price > PREV(price) AND price > NEXT(price) +); + +-- using AFTER MATCH SKIP TO NEXT ROW +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + INITIAL + PATTERN (START UPDOWN) + DEFINE + START AS TRUE, + UPDOWN AS price > PREV(price) AND price > NEXT(price) +); + +-- match everything + +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (A+) + DEFINE + A AS TRUE +); + +-- backtracking with reclassification of rows +-- using AFTER MATCH SKIP PAST LAST ROW +SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (A+ B+) + DEFINE + A AS price > 100, + B AS price > 100 +); + +-- backtracking with reclassification of rows +-- using AFTER MATCH SKIP TO NEXT ROW +SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + INITIAL + PATTERN (A+ B+) + DEFINE + A AS price > 100, + B AS price > 100 +); + +-- ROWS BETWEEN CURRENT ROW AND offset FOLLOWING +SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w, + count(*) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- +-- Aggregates +-- + +-- using AFTER MATCH SKIP PAST LAST ROW +SELECT company, tdate, price, + first_value(price) OVER w, + last_value(price) OVER w, + max(price) OVER w, + min(price) OVER w, + sum(price) OVER w, + avg(price) OVER w, + count(price) OVER w +FROM stock +WINDOW w AS ( +PARTITION BY company +ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING +AFTER MATCH SKIP PAST LAST ROW +INITIAL +PATTERN (START UP+ DOWN+) +DEFINE +START AS TRUE, +UP AS price > PREV(price), +DOWN AS price < PREV(price) +); + +-- using AFTER MATCH SKIP TO NEXT ROW +SELECT company, tdate, price, + first_value(price) OVER w, + last_value(price) OVER w, + max(price) OVER w, + min(price) OVER w, + sum(price) OVER w, + avg(price) OVER w, + count(price) OVER w +FROM stock +WINDOW w AS ( +PARTITION BY company +ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING +AFTER MATCH SKIP TO NEXT ROW +INITIAL +PATTERN (START UP+ DOWN+) +DEFINE +START AS TRUE, +UP AS price > PREV(price), +DOWN AS price < PREV(price) +); + +-- +-- Error cases +-- + +-- row pattern definition variable name must not appear more than once +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + ORDER BY tdate + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price), + UP AS price > PREV(price) +); + +-- pattern variable name must appear in DEFINE +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+ END) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- FRAME must start at current row when row patttern recognition is used +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- SEEK is not supported +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + SEEK + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); -- 2.25.1 ----Next_Part(Sun_Oct_22_11_39_20_2023_140)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v10-0007-Allow-to-print-raw-parse-tree.patch" ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH v6 2/5] Add anytime flush tests for custom stats @ 2026-02-05 05:54 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Bertrand Drouvot @ 2026-02-05 05:54 UTC (permalink / raw) --- .../test_custom_stats/t/001_custom_stats.pl | 41 ++++++++++++ .../test_custom_fixed_stats--1.0.sql | 10 +++ .../test_custom_fixed_stats.c | 66 +++++++++++++++++++ .../test_custom_var_stats--1.0.sql | 5 ++ .../test_custom_stats/test_custom_var_stats.c | 27 ++++++++ 5 files changed, 149 insertions(+) 31.4% src/test/modules/test_custom_stats/t/ 68.5% src/test/modules/test_custom_stats/ diff --git a/src/test/modules/test_custom_stats/t/001_custom_stats.pl b/src/test/modules/test_custom_stats/t/001_custom_stats.pl index 9e6a7a38577..36d9fc3fde1 100644 --- a/src/test/modules/test_custom_stats/t/001_custom_stats.pl +++ b/src/test/modules/test_custom_stats/t/001_custom_stats.pl @@ -156,5 +156,46 @@ $result = $node->safe_psql('postgres', ); is($result, "0", "report of fixed-sized after manual reset"); +# Test FLUSH_ANYTIME mechanism with custom fixed stats +# This verifies that custom stats can be flushed during a transaction + +# Reset stats first +$node->safe_psql('postgres', q(select test_custom_stats_fixed_reset())); +$node->safe_psql('postgres', q(select pg_stat_force_next_flush())); + +my $anytime_test = q[ + BEGIN; + -- Accumulate stats + select test_custom_stats_fixed_anytime_update() from generate_series(1, 2); + -- Force anytime flush (inside transaction!) + select pg_stat_force_anytime_flush(); + -- Check + select 'anytime:'||numcalls from test_custom_stats_fixed_report(); +]; + +$result = $node->safe_psql('postgres', $anytime_test); +like($result, qr/^anytime:2/m, + "anytime fixed stats flushed during transaction"); + +# Test FLUSH_ANYTIME mechanism with custom variable stats +# This verifies that custom stats can be flushed during a transaction + +$node->safe_psql('postgres', q(select pg_stat_force_next_flush())); + +$anytime_test = q[ + BEGIN; + -- Accumulate stats + select test_custom_stats_var_anytime_update('entry2'); + select test_custom_stats_var_anytime_update('entry2'); + -- Force anytime flush (inside transaction!) + select pg_stat_force_anytime_flush(); + -- Check + select * from test_custom_stats_var_report('entry2'); +]; + +$result = $node->safe_psql('postgres', $anytime_test); +like($result, qr/^entry2|2|/m, + "anytime var stats flushed during transaction"); + # Test completed successfully done_testing(); diff --git a/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql b/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql index 69a93b5241f..c0a418c3ae3 100644 --- a/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql +++ b/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql @@ -18,3 +18,13 @@ CREATE FUNCTION test_custom_stats_fixed_reset() RETURNS void AS 'MODULE_PATHNAME', 'test_custom_stats_fixed_reset' LANGUAGE C STRICT PARALLEL UNSAFE; + +CREATE FUNCTION test_custom_stats_fixed_anytime_update() +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT PARALLEL UNSAFE; + +CREATE FUNCTION pg_stat_force_anytime_flush() +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT PARALLEL UNSAFE; diff --git a/src/test/modules/test_custom_stats/test_custom_fixed_stats.c b/src/test/modules/test_custom_stats/test_custom_fixed_stats.c index 908bd18a7c7..6b3bc3257ab 100644 --- a/src/test/modules/test_custom_stats/test_custom_fixed_stats.c +++ b/src/test/modules/test_custom_stats/test_custom_fixed_stats.c @@ -18,6 +18,7 @@ #include "pgstat.h" #include "utils/builtins.h" #include "utils/pgstat_internal.h" +#include "utils/timeout.h" PG_MODULE_MAGIC_EXT( .name = "test_custom_fixed_stats", @@ -43,11 +44,13 @@ typedef struct PgStatShared_CustomFixedEntry static void test_custom_stats_fixed_init_shmem_cb(void *stats); static void test_custom_stats_fixed_reset_all_cb(TimestampTz ts); static void test_custom_stats_fixed_snapshot_cb(void); +static bool test_custom_stats_fixed_flush_cb(bool nowait, bool anytime_only); static const PgStat_KindInfo custom_stats = { .name = "test_custom_fixed_stats", .fixed_amount = true, /* exactly one entry */ .write_to_file = true, /* persist to stats file */ + .flush_mode = FLUSH_ANYTIME, /* can be flushed anytime */ .shared_size = sizeof(PgStat_StatCustomFixedEntry), .shared_data_off = offsetof(PgStatShared_CustomFixedEntry, stats), @@ -56,8 +59,12 @@ static const PgStat_KindInfo custom_stats = { .init_shmem_cb = test_custom_stats_fixed_init_shmem_cb, .reset_all_cb = test_custom_stats_fixed_reset_all_cb, .snapshot_cb = test_custom_stats_fixed_snapshot_cb, + .flush_static_cb = test_custom_stats_fixed_flush_cb, }; +/* Pending statistics */ +static PgStat_StatCustomFixedEntry PendingCustomStats = {0}; + /* * Kind ID for test_custom_fixed_stats. */ @@ -141,6 +148,38 @@ test_custom_stats_fixed_snapshot_cb(void) #undef FIXED_COMP } +/* + * test_custom_stats_fixed_flush_cb + * Flush pending stats to shared memory + */ +static bool +test_custom_stats_fixed_flush_cb(bool nowait, bool anytime_only) +{ + PgStatShared_CustomFixedEntry *stats_shmem; + + /* Nothing to flush if no calls were made */ + if (PendingCustomStats.numcalls == 0) + return false; + + stats_shmem = pgstat_get_custom_shmem_data(PGSTAT_KIND_TEST_CUSTOM_FIXED_STATS); + + if (nowait && !LWLockConditionalAcquire(&stats_shmem->lock, LW_EXCLUSIVE)) + return true; /* failed to flush */ + + LWLockAcquire(&stats_shmem->lock, LW_EXCLUSIVE); + + pgstat_begin_changecount_write(&stats_shmem->changecount); + stats_shmem->stats.numcalls += PendingCustomStats.numcalls; + pgstat_end_changecount_write(&stats_shmem->changecount); + + LWLockRelease(&stats_shmem->lock); + + /* Reset pending stats */ + PendingCustomStats.numcalls = 0; + + return false; /* successfully flushed */ +} + /*-------------------------------------------------------------------------- * SQL-callable functions *-------------------------------------------------------------------------- @@ -222,3 +261,30 @@ test_custom_stats_fixed_report(PG_FUNCTION_ARGS) /* Return as tuple */ PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls))); } + +/* + * test_custom_stats_fixed_anytime_update + * Increment call counter and schedule anytime flush + */ +PG_FUNCTION_INFO_V1(test_custom_stats_fixed_anytime_update); +Datum +test_custom_stats_fixed_anytime_update(PG_FUNCTION_ARGS) +{ + /* Accumulate in pending stats */ + PendingCustomStats.numcalls++; + + /* Schedule anytime stats update */ + pgstat_schedule_anytime_update(); + pgstat_report_fixed = true; + + PG_RETURN_VOID(); +} + +/* Helper function for testing ANYTIME flush */ +PG_FUNCTION_INFO_V1(pg_stat_force_anytime_flush); +Datum +pg_stat_force_anytime_flush(PG_FUNCTION_ARGS) +{ + pgstat_report_anytime_stat(true); + PG_RETURN_VOID(); +} diff --git a/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql b/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql index 5ed8cfc2dcf..ed66d38981e 100644 --- a/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql +++ b/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql @@ -24,3 +24,8 @@ CREATE FUNCTION test_custom_stats_var_report(INOUT name TEXT, RETURNS SETOF record AS 'MODULE_PATHNAME', 'test_custom_stats_var_report' LANGUAGE C STRICT PARALLEL UNSAFE; + +CREATE FUNCTION test_custom_stats_var_anytime_update(IN name TEXT) +RETURNS void +AS 'MODULE_PATHNAME', 'test_custom_stats_var_anytime_update' +LANGUAGE C STRICT PARALLEL UNSAFE; diff --git a/src/test/modules/test_custom_stats/test_custom_var_stats.c b/src/test/modules/test_custom_stats/test_custom_var_stats.c index bc0b5d6e0eb..207e841911b 100644 --- a/src/test/modules/test_custom_stats/test_custom_var_stats.c +++ b/src/test/modules/test_custom_stats/test_custom_var_stats.c @@ -17,6 +17,7 @@ #include "storage/dsm_registry.h" #include "utils/builtins.h" #include "utils/pgstat_internal.h" +#include "utils/timeout.h" PG_MODULE_MAGIC_EXT( .name = "test_custom_var_stats", @@ -107,6 +108,7 @@ static const PgStat_KindInfo custom_stats = { .name = "test_custom_var_stats", .fixed_amount = false, /* variable number of entries */ .write_to_file = true, /* persist across restarts */ + .flush_mode = FLUSH_ANYTIME, /* can be flushed anytime */ .track_entry_count = true, /* count active entries */ .accessed_across_databases = true, /* global statistics */ .shared_size = sizeof(PgStatShared_CustomVarEntry), @@ -689,3 +691,28 @@ test_custom_stats_var_report(PG_FUNCTION_ARGS) SRF_RETURN_DONE(funcctx); } + +/* + * test_custom_stats_var_anytime_update + * Increment custom statistic counter and schedule anytime flush + */ +PG_FUNCTION_INFO_V1(test_custom_stats_var_anytime_update); +Datum +test_custom_stats_var_anytime_update(PG_FUNCTION_ARGS) +{ + char *stat_name = text_to_cstring(PG_GETARG_TEXT_PP(0)); + PgStat_EntryRef *entry_ref; + PgStat_StatCustomVarEntry *pending_entry; + + /* Get pending entry in local memory */ + entry_ref = pgstat_prep_pending_entry(PGSTAT_KIND_TEST_CUSTOM_VAR_STATS, InvalidOid, + PGSTAT_CUSTOM_VAR_STATS_IDX(stat_name), NULL); + + pending_entry = (PgStat_StatCustomVarEntry *) entry_ref->pending; + pending_entry->numcalls++; + + /* Schedule anytime stats update */ + pgstat_schedule_anytime_update(); + + PG_RETURN_VOID(); +} -- 2.34.1 --DxqFthphbM+ha9Dy Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v6-0003-Add-GUC-to-specify-non-transactional-statistics-f.patch" ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH v7 2/5] Add anytime flush tests for custom stats @ 2026-02-05 05:54 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Bertrand Drouvot @ 2026-02-05 05:54 UTC (permalink / raw) --- .../test_custom_stats/t/001_custom_stats.pl | 41 +++++++++++++ .../test_custom_fixed_stats--1.0.sql | 5 ++ .../test_custom_fixed_stats.c | 57 +++++++++++++++++++ .../test_custom_var_stats--1.0.sql | 5 ++ .../test_custom_stats/test_custom_var_stats.c | 27 +++++++++ 5 files changed, 135 insertions(+) 33.8% src/test/modules/test_custom_stats/t/ 66.1% src/test/modules/test_custom_stats/ diff --git a/src/test/modules/test_custom_stats/t/001_custom_stats.pl b/src/test/modules/test_custom_stats/t/001_custom_stats.pl index 9e6a7a38577..7be1b281776 100644 --- a/src/test/modules/test_custom_stats/t/001_custom_stats.pl +++ b/src/test/modules/test_custom_stats/t/001_custom_stats.pl @@ -156,5 +156,46 @@ $result = $node->safe_psql('postgres', ); is($result, "0", "report of fixed-sized after manual reset"); +# Test FLUSH_ANYTIME mechanism with custom fixed stats +# This verifies that custom stats can be flushed during a transaction + +# Reset stats first +$node->safe_psql('postgres', q(select test_custom_stats_fixed_reset())); +$node->safe_psql('postgres', q(select pg_stat_force_next_flush())); + +my $anytime_test = q[ + BEGIN; + -- Accumulate stats + select test_custom_stats_fixed_anytime_update() from generate_series(1, 2); + -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + select pg_sleep(1.5); + -- Check + select 'anytime:'||numcalls from test_custom_stats_fixed_report(); +]; + +$result = $node->safe_psql('postgres', $anytime_test); +like($result, qr/^anytime:2/m, + "anytime fixed stats flushed during transaction"); + +# Test FLUSH_ANYTIME mechanism with custom variable stats +# This verifies that custom stats can be flushed during a transaction + +$node->safe_psql('postgres', q(select pg_stat_force_next_flush())); + +$anytime_test = q[ + BEGIN; + -- Accumulate stats + select test_custom_stats_var_anytime_update('entry2'); + select test_custom_stats_var_anytime_update('entry2'); + -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + select pg_sleep(1.5); + -- Check + select * from test_custom_stats_var_report('entry2'); +]; + +$result = $node->safe_psql('postgres', $anytime_test); +like($result, qr/^entry2|2|/m, + "anytime var stats flushed during transaction"); + # Test completed successfully done_testing(); diff --git a/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql b/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql index 69a93b5241f..da3a798f289 100644 --- a/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql +++ b/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql @@ -18,3 +18,8 @@ CREATE FUNCTION test_custom_stats_fixed_reset() RETURNS void AS 'MODULE_PATHNAME', 'test_custom_stats_fixed_reset' LANGUAGE C STRICT PARALLEL UNSAFE; + +CREATE FUNCTION test_custom_stats_fixed_anytime_update() +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT PARALLEL UNSAFE; diff --git a/src/test/modules/test_custom_stats/test_custom_fixed_stats.c b/src/test/modules/test_custom_stats/test_custom_fixed_stats.c index 908bd18a7c7..30b0fbcbdc7 100644 --- a/src/test/modules/test_custom_stats/test_custom_fixed_stats.c +++ b/src/test/modules/test_custom_stats/test_custom_fixed_stats.c @@ -18,6 +18,7 @@ #include "pgstat.h" #include "utils/builtins.h" #include "utils/pgstat_internal.h" +#include "utils/timeout.h" PG_MODULE_MAGIC_EXT( .name = "test_custom_fixed_stats", @@ -43,11 +44,13 @@ typedef struct PgStatShared_CustomFixedEntry static void test_custom_stats_fixed_init_shmem_cb(void *stats); static void test_custom_stats_fixed_reset_all_cb(TimestampTz ts); static void test_custom_stats_fixed_snapshot_cb(void); +static bool test_custom_stats_fixed_flush_cb(bool nowait, bool anytime_only); static const PgStat_KindInfo custom_stats = { .name = "test_custom_fixed_stats", .fixed_amount = true, /* exactly one entry */ .write_to_file = true, /* persist to stats file */ + .flush_mode = FLUSH_ANYTIME, /* can be flushed anytime */ .shared_size = sizeof(PgStat_StatCustomFixedEntry), .shared_data_off = offsetof(PgStatShared_CustomFixedEntry, stats), @@ -56,8 +59,12 @@ static const PgStat_KindInfo custom_stats = { .init_shmem_cb = test_custom_stats_fixed_init_shmem_cb, .reset_all_cb = test_custom_stats_fixed_reset_all_cb, .snapshot_cb = test_custom_stats_fixed_snapshot_cb, + .flush_static_cb = test_custom_stats_fixed_flush_cb, }; +/* Pending statistics */ +static PgStat_StatCustomFixedEntry PendingCustomStats = {0}; + /* * Kind ID for test_custom_fixed_stats. */ @@ -141,6 +148,38 @@ test_custom_stats_fixed_snapshot_cb(void) #undef FIXED_COMP } +/* + * test_custom_stats_fixed_flush_cb + * Flush pending stats to shared memory + */ +static bool +test_custom_stats_fixed_flush_cb(bool nowait, bool anytime_only) +{ + PgStatShared_CustomFixedEntry *stats_shmem; + + /* Nothing to flush if no calls were made */ + if (PendingCustomStats.numcalls == 0) + return false; + + stats_shmem = pgstat_get_custom_shmem_data(PGSTAT_KIND_TEST_CUSTOM_FIXED_STATS); + + if (!nowait) + LWLockAcquire(&stats_shmem->lock, LW_EXCLUSIVE); + else if (!LWLockConditionalAcquire(&stats_shmem->lock, LW_EXCLUSIVE)) + return true; + + pgstat_begin_changecount_write(&stats_shmem->changecount); + stats_shmem->stats.numcalls += PendingCustomStats.numcalls; + pgstat_end_changecount_write(&stats_shmem->changecount); + + LWLockRelease(&stats_shmem->lock); + + /* Reset pending stats */ + PendingCustomStats.numcalls = 0; + + return false; /* successfully flushed */ +} + /*-------------------------------------------------------------------------- * SQL-callable functions *-------------------------------------------------------------------------- @@ -222,3 +261,21 @@ test_custom_stats_fixed_report(PG_FUNCTION_ARGS) /* Return as tuple */ PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls))); } + +/* + * test_custom_stats_fixed_anytime_update + * Increment call counter and schedule anytime flush + */ +PG_FUNCTION_INFO_V1(test_custom_stats_fixed_anytime_update); +Datum +test_custom_stats_fixed_anytime_update(PG_FUNCTION_ARGS) +{ + /* Accumulate in pending stats */ + PendingCustomStats.numcalls++; + + /* Schedule anytime stats update */ + pgstat_schedule_anytime_update(); + pgstat_report_fixed = true; + + PG_RETURN_VOID(); +} diff --git a/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql b/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql index 5ed8cfc2dcf..ed66d38981e 100644 --- a/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql +++ b/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql @@ -24,3 +24,8 @@ CREATE FUNCTION test_custom_stats_var_report(INOUT name TEXT, RETURNS SETOF record AS 'MODULE_PATHNAME', 'test_custom_stats_var_report' LANGUAGE C STRICT PARALLEL UNSAFE; + +CREATE FUNCTION test_custom_stats_var_anytime_update(IN name TEXT) +RETURNS void +AS 'MODULE_PATHNAME', 'test_custom_stats_var_anytime_update' +LANGUAGE C STRICT PARALLEL UNSAFE; diff --git a/src/test/modules/test_custom_stats/test_custom_var_stats.c b/src/test/modules/test_custom_stats/test_custom_var_stats.c index bc0b5d6e0eb..207e841911b 100644 --- a/src/test/modules/test_custom_stats/test_custom_var_stats.c +++ b/src/test/modules/test_custom_stats/test_custom_var_stats.c @@ -17,6 +17,7 @@ #include "storage/dsm_registry.h" #include "utils/builtins.h" #include "utils/pgstat_internal.h" +#include "utils/timeout.h" PG_MODULE_MAGIC_EXT( .name = "test_custom_var_stats", @@ -107,6 +108,7 @@ static const PgStat_KindInfo custom_stats = { .name = "test_custom_var_stats", .fixed_amount = false, /* variable number of entries */ .write_to_file = true, /* persist across restarts */ + .flush_mode = FLUSH_ANYTIME, /* can be flushed anytime */ .track_entry_count = true, /* count active entries */ .accessed_across_databases = true, /* global statistics */ .shared_size = sizeof(PgStatShared_CustomVarEntry), @@ -689,3 +691,28 @@ test_custom_stats_var_report(PG_FUNCTION_ARGS) SRF_RETURN_DONE(funcctx); } + +/* + * test_custom_stats_var_anytime_update + * Increment custom statistic counter and schedule anytime flush + */ +PG_FUNCTION_INFO_V1(test_custom_stats_var_anytime_update); +Datum +test_custom_stats_var_anytime_update(PG_FUNCTION_ARGS) +{ + char *stat_name = text_to_cstring(PG_GETARG_TEXT_PP(0)); + PgStat_EntryRef *entry_ref; + PgStat_StatCustomVarEntry *pending_entry; + + /* Get pending entry in local memory */ + entry_ref = pgstat_prep_pending_entry(PGSTAT_KIND_TEST_CUSTOM_VAR_STATS, InvalidOid, + PGSTAT_CUSTOM_VAR_STATS_IDX(stat_name), NULL); + + pending_entry = (PgStat_StatCustomVarEntry *) entry_ref->pending; + pending_entry->numcalls++; + + /* Schedule anytime stats update */ + pgstat_schedule_anytime_update(); + + PG_RETURN_VOID(); +} -- 2.34.1 --C2xzVbxFmVrP7k6O Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v7-0003-Add-GUC-to-specify-non-transactional-statistics-f.patch" ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH v8 2/5] Add anytime flush tests for custom stats @ 2026-02-05 05:54 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Bertrand Drouvot @ 2026-02-05 05:54 UTC (permalink / raw) --- .../test_custom_stats/t/001_custom_stats.pl | 41 +++++++++++++ .../test_custom_fixed_stats--1.0.sql | 5 ++ .../test_custom_fixed_stats.c | 57 +++++++++++++++++++ .../test_custom_var_stats--1.0.sql | 5 ++ .../test_custom_stats/test_custom_var_stats.c | 27 +++++++++ 5 files changed, 135 insertions(+) 33.8% src/test/modules/test_custom_stats/t/ 66.1% src/test/modules/test_custom_stats/ diff --git a/src/test/modules/test_custom_stats/t/001_custom_stats.pl b/src/test/modules/test_custom_stats/t/001_custom_stats.pl index 9e6a7a38577..7be1b281776 100644 --- a/src/test/modules/test_custom_stats/t/001_custom_stats.pl +++ b/src/test/modules/test_custom_stats/t/001_custom_stats.pl @@ -156,5 +156,46 @@ $result = $node->safe_psql('postgres', ); is($result, "0", "report of fixed-sized after manual reset"); +# Test FLUSH_ANYTIME mechanism with custom fixed stats +# This verifies that custom stats can be flushed during a transaction + +# Reset stats first +$node->safe_psql('postgres', q(select test_custom_stats_fixed_reset())); +$node->safe_psql('postgres', q(select pg_stat_force_next_flush())); + +my $anytime_test = q[ + BEGIN; + -- Accumulate stats + select test_custom_stats_fixed_anytime_update() from generate_series(1, 2); + -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + select pg_sleep(1.5); + -- Check + select 'anytime:'||numcalls from test_custom_stats_fixed_report(); +]; + +$result = $node->safe_psql('postgres', $anytime_test); +like($result, qr/^anytime:2/m, + "anytime fixed stats flushed during transaction"); + +# Test FLUSH_ANYTIME mechanism with custom variable stats +# This verifies that custom stats can be flushed during a transaction + +$node->safe_psql('postgres', q(select pg_stat_force_next_flush())); + +$anytime_test = q[ + BEGIN; + -- Accumulate stats + select test_custom_stats_var_anytime_update('entry2'); + select test_custom_stats_var_anytime_update('entry2'); + -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + select pg_sleep(1.5); + -- Check + select * from test_custom_stats_var_report('entry2'); +]; + +$result = $node->safe_psql('postgres', $anytime_test); +like($result, qr/^entry2|2|/m, + "anytime var stats flushed during transaction"); + # Test completed successfully done_testing(); diff --git a/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql b/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql index 69a93b5241f..da3a798f289 100644 --- a/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql +++ b/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql @@ -18,3 +18,8 @@ CREATE FUNCTION test_custom_stats_fixed_reset() RETURNS void AS 'MODULE_PATHNAME', 'test_custom_stats_fixed_reset' LANGUAGE C STRICT PARALLEL UNSAFE; + +CREATE FUNCTION test_custom_stats_fixed_anytime_update() +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT PARALLEL UNSAFE; diff --git a/src/test/modules/test_custom_stats/test_custom_fixed_stats.c b/src/test/modules/test_custom_stats/test_custom_fixed_stats.c index 908bd18a7c7..30b0fbcbdc7 100644 --- a/src/test/modules/test_custom_stats/test_custom_fixed_stats.c +++ b/src/test/modules/test_custom_stats/test_custom_fixed_stats.c @@ -18,6 +18,7 @@ #include "pgstat.h" #include "utils/builtins.h" #include "utils/pgstat_internal.h" +#include "utils/timeout.h" PG_MODULE_MAGIC_EXT( .name = "test_custom_fixed_stats", @@ -43,11 +44,13 @@ typedef struct PgStatShared_CustomFixedEntry static void test_custom_stats_fixed_init_shmem_cb(void *stats); static void test_custom_stats_fixed_reset_all_cb(TimestampTz ts); static void test_custom_stats_fixed_snapshot_cb(void); +static bool test_custom_stats_fixed_flush_cb(bool nowait, bool anytime_only); static const PgStat_KindInfo custom_stats = { .name = "test_custom_fixed_stats", .fixed_amount = true, /* exactly one entry */ .write_to_file = true, /* persist to stats file */ + .flush_mode = FLUSH_ANYTIME, /* can be flushed anytime */ .shared_size = sizeof(PgStat_StatCustomFixedEntry), .shared_data_off = offsetof(PgStatShared_CustomFixedEntry, stats), @@ -56,8 +59,12 @@ static const PgStat_KindInfo custom_stats = { .init_shmem_cb = test_custom_stats_fixed_init_shmem_cb, .reset_all_cb = test_custom_stats_fixed_reset_all_cb, .snapshot_cb = test_custom_stats_fixed_snapshot_cb, + .flush_static_cb = test_custom_stats_fixed_flush_cb, }; +/* Pending statistics */ +static PgStat_StatCustomFixedEntry PendingCustomStats = {0}; + /* * Kind ID for test_custom_fixed_stats. */ @@ -141,6 +148,38 @@ test_custom_stats_fixed_snapshot_cb(void) #undef FIXED_COMP } +/* + * test_custom_stats_fixed_flush_cb + * Flush pending stats to shared memory + */ +static bool +test_custom_stats_fixed_flush_cb(bool nowait, bool anytime_only) +{ + PgStatShared_CustomFixedEntry *stats_shmem; + + /* Nothing to flush if no calls were made */ + if (PendingCustomStats.numcalls == 0) + return false; + + stats_shmem = pgstat_get_custom_shmem_data(PGSTAT_KIND_TEST_CUSTOM_FIXED_STATS); + + if (!nowait) + LWLockAcquire(&stats_shmem->lock, LW_EXCLUSIVE); + else if (!LWLockConditionalAcquire(&stats_shmem->lock, LW_EXCLUSIVE)) + return true; + + pgstat_begin_changecount_write(&stats_shmem->changecount); + stats_shmem->stats.numcalls += PendingCustomStats.numcalls; + pgstat_end_changecount_write(&stats_shmem->changecount); + + LWLockRelease(&stats_shmem->lock); + + /* Reset pending stats */ + PendingCustomStats.numcalls = 0; + + return false; /* successfully flushed */ +} + /*-------------------------------------------------------------------------- * SQL-callable functions *-------------------------------------------------------------------------- @@ -222,3 +261,21 @@ test_custom_stats_fixed_report(PG_FUNCTION_ARGS) /* Return as tuple */ PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls))); } + +/* + * test_custom_stats_fixed_anytime_update + * Increment call counter and schedule anytime flush + */ +PG_FUNCTION_INFO_V1(test_custom_stats_fixed_anytime_update); +Datum +test_custom_stats_fixed_anytime_update(PG_FUNCTION_ARGS) +{ + /* Accumulate in pending stats */ + PendingCustomStats.numcalls++; + + /* Schedule anytime stats update */ + pgstat_schedule_anytime_update(); + pgstat_report_fixed = true; + + PG_RETURN_VOID(); +} diff --git a/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql b/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql index 5ed8cfc2dcf..ed66d38981e 100644 --- a/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql +++ b/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql @@ -24,3 +24,8 @@ CREATE FUNCTION test_custom_stats_var_report(INOUT name TEXT, RETURNS SETOF record AS 'MODULE_PATHNAME', 'test_custom_stats_var_report' LANGUAGE C STRICT PARALLEL UNSAFE; + +CREATE FUNCTION test_custom_stats_var_anytime_update(IN name TEXT) +RETURNS void +AS 'MODULE_PATHNAME', 'test_custom_stats_var_anytime_update' +LANGUAGE C STRICT PARALLEL UNSAFE; diff --git a/src/test/modules/test_custom_stats/test_custom_var_stats.c b/src/test/modules/test_custom_stats/test_custom_var_stats.c index bc0b5d6e0eb..207e841911b 100644 --- a/src/test/modules/test_custom_stats/test_custom_var_stats.c +++ b/src/test/modules/test_custom_stats/test_custom_var_stats.c @@ -17,6 +17,7 @@ #include "storage/dsm_registry.h" #include "utils/builtins.h" #include "utils/pgstat_internal.h" +#include "utils/timeout.h" PG_MODULE_MAGIC_EXT( .name = "test_custom_var_stats", @@ -107,6 +108,7 @@ static const PgStat_KindInfo custom_stats = { .name = "test_custom_var_stats", .fixed_amount = false, /* variable number of entries */ .write_to_file = true, /* persist across restarts */ + .flush_mode = FLUSH_ANYTIME, /* can be flushed anytime */ .track_entry_count = true, /* count active entries */ .accessed_across_databases = true, /* global statistics */ .shared_size = sizeof(PgStatShared_CustomVarEntry), @@ -689,3 +691,28 @@ test_custom_stats_var_report(PG_FUNCTION_ARGS) SRF_RETURN_DONE(funcctx); } + +/* + * test_custom_stats_var_anytime_update + * Increment custom statistic counter and schedule anytime flush + */ +PG_FUNCTION_INFO_V1(test_custom_stats_var_anytime_update); +Datum +test_custom_stats_var_anytime_update(PG_FUNCTION_ARGS) +{ + char *stat_name = text_to_cstring(PG_GETARG_TEXT_PP(0)); + PgStat_EntryRef *entry_ref; + PgStat_StatCustomVarEntry *pending_entry; + + /* Get pending entry in local memory */ + entry_ref = pgstat_prep_pending_entry(PGSTAT_KIND_TEST_CUSTOM_VAR_STATS, InvalidOid, + PGSTAT_CUSTOM_VAR_STATS_IDX(stat_name), NULL); + + pending_entry = (PgStat_StatCustomVarEntry *) entry_ref->pending; + pending_entry->numcalls++; + + /* Schedule anytime stats update */ + pgstat_schedule_anytime_update(); + + PG_RETURN_VOID(); +} -- 2.34.1 --OI5irqZsxWxBW9nT Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v8-0003-Add-GUC-to-specify-non-transactional-statistics-f.patch" ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH v9 2/5] Add anytime flush tests for custom stats @ 2026-02-05 05:54 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Bertrand Drouvot @ 2026-02-05 05:54 UTC (permalink / raw) --- .../test_custom_stats/t/001_custom_stats.pl | 41 +++++++++++++ .../test_custom_fixed_stats--1.0.sql | 5 ++ .../test_custom_fixed_stats.c | 57 +++++++++++++++++++ .../test_custom_var_stats--1.0.sql | 5 ++ .../test_custom_stats/test_custom_var_stats.c | 27 +++++++++ 5 files changed, 135 insertions(+) 33.8% src/test/modules/test_custom_stats/t/ 66.1% src/test/modules/test_custom_stats/ diff --git a/src/test/modules/test_custom_stats/t/001_custom_stats.pl b/src/test/modules/test_custom_stats/t/001_custom_stats.pl index 9e6a7a38577..7be1b281776 100644 --- a/src/test/modules/test_custom_stats/t/001_custom_stats.pl +++ b/src/test/modules/test_custom_stats/t/001_custom_stats.pl @@ -156,5 +156,46 @@ $result = $node->safe_psql('postgres', ); is($result, "0", "report of fixed-sized after manual reset"); +# Test FLUSH_ANYTIME mechanism with custom fixed stats +# This verifies that custom stats can be flushed during a transaction + +# Reset stats first +$node->safe_psql('postgres', q(select test_custom_stats_fixed_reset())); +$node->safe_psql('postgres', q(select pg_stat_force_next_flush())); + +my $anytime_test = q[ + BEGIN; + -- Accumulate stats + select test_custom_stats_fixed_anytime_update() from generate_series(1, 2); + -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + select pg_sleep(1.5); + -- Check + select 'anytime:'||numcalls from test_custom_stats_fixed_report(); +]; + +$result = $node->safe_psql('postgres', $anytime_test); +like($result, qr/^anytime:2/m, + "anytime fixed stats flushed during transaction"); + +# Test FLUSH_ANYTIME mechanism with custom variable stats +# This verifies that custom stats can be flushed during a transaction + +$node->safe_psql('postgres', q(select pg_stat_force_next_flush())); + +$anytime_test = q[ + BEGIN; + -- Accumulate stats + select test_custom_stats_var_anytime_update('entry2'); + select test_custom_stats_var_anytime_update('entry2'); + -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + select pg_sleep(1.5); + -- Check + select * from test_custom_stats_var_report('entry2'); +]; + +$result = $node->safe_psql('postgres', $anytime_test); +like($result, qr/^entry2|2|/m, + "anytime var stats flushed during transaction"); + # Test completed successfully done_testing(); diff --git a/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql b/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql index 69a93b5241f..da3a798f289 100644 --- a/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql +++ b/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql @@ -18,3 +18,8 @@ CREATE FUNCTION test_custom_stats_fixed_reset() RETURNS void AS 'MODULE_PATHNAME', 'test_custom_stats_fixed_reset' LANGUAGE C STRICT PARALLEL UNSAFE; + +CREATE FUNCTION test_custom_stats_fixed_anytime_update() +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT PARALLEL UNSAFE; diff --git a/src/test/modules/test_custom_stats/test_custom_fixed_stats.c b/src/test/modules/test_custom_stats/test_custom_fixed_stats.c index 908bd18a7c7..30b0fbcbdc7 100644 --- a/src/test/modules/test_custom_stats/test_custom_fixed_stats.c +++ b/src/test/modules/test_custom_stats/test_custom_fixed_stats.c @@ -18,6 +18,7 @@ #include "pgstat.h" #include "utils/builtins.h" #include "utils/pgstat_internal.h" +#include "utils/timeout.h" PG_MODULE_MAGIC_EXT( .name = "test_custom_fixed_stats", @@ -43,11 +44,13 @@ typedef struct PgStatShared_CustomFixedEntry static void test_custom_stats_fixed_init_shmem_cb(void *stats); static void test_custom_stats_fixed_reset_all_cb(TimestampTz ts); static void test_custom_stats_fixed_snapshot_cb(void); +static bool test_custom_stats_fixed_flush_cb(bool nowait, bool anytime_only); static const PgStat_KindInfo custom_stats = { .name = "test_custom_fixed_stats", .fixed_amount = true, /* exactly one entry */ .write_to_file = true, /* persist to stats file */ + .flush_mode = FLUSH_ANYTIME, /* can be flushed anytime */ .shared_size = sizeof(PgStat_StatCustomFixedEntry), .shared_data_off = offsetof(PgStatShared_CustomFixedEntry, stats), @@ -56,8 +59,12 @@ static const PgStat_KindInfo custom_stats = { .init_shmem_cb = test_custom_stats_fixed_init_shmem_cb, .reset_all_cb = test_custom_stats_fixed_reset_all_cb, .snapshot_cb = test_custom_stats_fixed_snapshot_cb, + .flush_static_cb = test_custom_stats_fixed_flush_cb, }; +/* Pending statistics */ +static PgStat_StatCustomFixedEntry PendingCustomStats = {0}; + /* * Kind ID for test_custom_fixed_stats. */ @@ -141,6 +148,38 @@ test_custom_stats_fixed_snapshot_cb(void) #undef FIXED_COMP } +/* + * test_custom_stats_fixed_flush_cb + * Flush pending stats to shared memory + */ +static bool +test_custom_stats_fixed_flush_cb(bool nowait, bool anytime_only) +{ + PgStatShared_CustomFixedEntry *stats_shmem; + + /* Nothing to flush if no calls were made */ + if (PendingCustomStats.numcalls == 0) + return false; + + stats_shmem = pgstat_get_custom_shmem_data(PGSTAT_KIND_TEST_CUSTOM_FIXED_STATS); + + if (!nowait) + LWLockAcquire(&stats_shmem->lock, LW_EXCLUSIVE); + else if (!LWLockConditionalAcquire(&stats_shmem->lock, LW_EXCLUSIVE)) + return true; + + pgstat_begin_changecount_write(&stats_shmem->changecount); + stats_shmem->stats.numcalls += PendingCustomStats.numcalls; + pgstat_end_changecount_write(&stats_shmem->changecount); + + LWLockRelease(&stats_shmem->lock); + + /* Reset pending stats */ + PendingCustomStats.numcalls = 0; + + return false; /* successfully flushed */ +} + /*-------------------------------------------------------------------------- * SQL-callable functions *-------------------------------------------------------------------------- @@ -222,3 +261,21 @@ test_custom_stats_fixed_report(PG_FUNCTION_ARGS) /* Return as tuple */ PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls))); } + +/* + * test_custom_stats_fixed_anytime_update + * Increment call counter and schedule anytime flush + */ +PG_FUNCTION_INFO_V1(test_custom_stats_fixed_anytime_update); +Datum +test_custom_stats_fixed_anytime_update(PG_FUNCTION_ARGS) +{ + /* Accumulate in pending stats */ + PendingCustomStats.numcalls++; + + /* Schedule anytime stats update */ + pgstat_schedule_anytime_update(); + pgstat_report_fixed = true; + + PG_RETURN_VOID(); +} diff --git a/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql b/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql index 5ed8cfc2dcf..ed66d38981e 100644 --- a/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql +++ b/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql @@ -24,3 +24,8 @@ CREATE FUNCTION test_custom_stats_var_report(INOUT name TEXT, RETURNS SETOF record AS 'MODULE_PATHNAME', 'test_custom_stats_var_report' LANGUAGE C STRICT PARALLEL UNSAFE; + +CREATE FUNCTION test_custom_stats_var_anytime_update(IN name TEXT) +RETURNS void +AS 'MODULE_PATHNAME', 'test_custom_stats_var_anytime_update' +LANGUAGE C STRICT PARALLEL UNSAFE; diff --git a/src/test/modules/test_custom_stats/test_custom_var_stats.c b/src/test/modules/test_custom_stats/test_custom_var_stats.c index bc0b5d6e0eb..207e841911b 100644 --- a/src/test/modules/test_custom_stats/test_custom_var_stats.c +++ b/src/test/modules/test_custom_stats/test_custom_var_stats.c @@ -17,6 +17,7 @@ #include "storage/dsm_registry.h" #include "utils/builtins.h" #include "utils/pgstat_internal.h" +#include "utils/timeout.h" PG_MODULE_MAGIC_EXT( .name = "test_custom_var_stats", @@ -107,6 +108,7 @@ static const PgStat_KindInfo custom_stats = { .name = "test_custom_var_stats", .fixed_amount = false, /* variable number of entries */ .write_to_file = true, /* persist across restarts */ + .flush_mode = FLUSH_ANYTIME, /* can be flushed anytime */ .track_entry_count = true, /* count active entries */ .accessed_across_databases = true, /* global statistics */ .shared_size = sizeof(PgStatShared_CustomVarEntry), @@ -689,3 +691,28 @@ test_custom_stats_var_report(PG_FUNCTION_ARGS) SRF_RETURN_DONE(funcctx); } + +/* + * test_custom_stats_var_anytime_update + * Increment custom statistic counter and schedule anytime flush + */ +PG_FUNCTION_INFO_V1(test_custom_stats_var_anytime_update); +Datum +test_custom_stats_var_anytime_update(PG_FUNCTION_ARGS) +{ + char *stat_name = text_to_cstring(PG_GETARG_TEXT_PP(0)); + PgStat_EntryRef *entry_ref; + PgStat_StatCustomVarEntry *pending_entry; + + /* Get pending entry in local memory */ + entry_ref = pgstat_prep_pending_entry(PGSTAT_KIND_TEST_CUSTOM_VAR_STATS, InvalidOid, + PGSTAT_CUSTOM_VAR_STATS_IDX(stat_name), NULL); + + pending_entry = (PgStat_StatCustomVarEntry *) entry_ref->pending; + pending_entry->numcalls++; + + /* Schedule anytime stats update */ + pgstat_schedule_anytime_update(); + + PG_RETURN_VOID(); +} -- 2.34.1 --aq/6bi8L6WORnI+9 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v9-0003-Add-GUC-to-specify-non-transactional-statistics-f.patch" ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH v10 2/5] Add anytime flush tests for custom stats @ 2026-02-05 05:54 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Bertrand Drouvot @ 2026-02-05 05:54 UTC (permalink / raw) --- .../test_custom_stats/t/001_custom_stats.pl | 41 +++++++++++++ .../test_custom_fixed_stats--1.0.sql | 5 ++ .../test_custom_fixed_stats.c | 57 +++++++++++++++++++ .../test_custom_var_stats--1.0.sql | 5 ++ .../test_custom_stats/test_custom_var_stats.c | 27 +++++++++ 5 files changed, 135 insertions(+) 33.8% src/test/modules/test_custom_stats/t/ 66.1% src/test/modules/test_custom_stats/ diff --git a/src/test/modules/test_custom_stats/t/001_custom_stats.pl b/src/test/modules/test_custom_stats/t/001_custom_stats.pl index 9e6a7a38577..7be1b281776 100644 --- a/src/test/modules/test_custom_stats/t/001_custom_stats.pl +++ b/src/test/modules/test_custom_stats/t/001_custom_stats.pl @@ -156,5 +156,46 @@ $result = $node->safe_psql('postgres', ); is($result, "0", "report of fixed-sized after manual reset"); +# Test FLUSH_ANYTIME mechanism with custom fixed stats +# This verifies that custom stats can be flushed during a transaction + +# Reset stats first +$node->safe_psql('postgres', q(select test_custom_stats_fixed_reset())); +$node->safe_psql('postgres', q(select pg_stat_force_next_flush())); + +my $anytime_test = q[ + BEGIN; + -- Accumulate stats + select test_custom_stats_fixed_anytime_update() from generate_series(1, 2); + -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + select pg_sleep(1.5); + -- Check + select 'anytime:'||numcalls from test_custom_stats_fixed_report(); +]; + +$result = $node->safe_psql('postgres', $anytime_test); +like($result, qr/^anytime:2/m, + "anytime fixed stats flushed during transaction"); + +# Test FLUSH_ANYTIME mechanism with custom variable stats +# This verifies that custom stats can be flushed during a transaction + +$node->safe_psql('postgres', q(select pg_stat_force_next_flush())); + +$anytime_test = q[ + BEGIN; + -- Accumulate stats + select test_custom_stats_var_anytime_update('entry2'); + select test_custom_stats_var_anytime_update('entry2'); + -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + select pg_sleep(1.5); + -- Check + select * from test_custom_stats_var_report('entry2'); +]; + +$result = $node->safe_psql('postgres', $anytime_test); +like($result, qr/^entry2|2|/m, + "anytime var stats flushed during transaction"); + # Test completed successfully done_testing(); diff --git a/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql b/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql index 69a93b5241f..da3a798f289 100644 --- a/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql +++ b/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql @@ -18,3 +18,8 @@ CREATE FUNCTION test_custom_stats_fixed_reset() RETURNS void AS 'MODULE_PATHNAME', 'test_custom_stats_fixed_reset' LANGUAGE C STRICT PARALLEL UNSAFE; + +CREATE FUNCTION test_custom_stats_fixed_anytime_update() +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT PARALLEL UNSAFE; diff --git a/src/test/modules/test_custom_stats/test_custom_fixed_stats.c b/src/test/modules/test_custom_stats/test_custom_fixed_stats.c index 908bd18a7c7..30b0fbcbdc7 100644 --- a/src/test/modules/test_custom_stats/test_custom_fixed_stats.c +++ b/src/test/modules/test_custom_stats/test_custom_fixed_stats.c @@ -18,6 +18,7 @@ #include "pgstat.h" #include "utils/builtins.h" #include "utils/pgstat_internal.h" +#include "utils/timeout.h" PG_MODULE_MAGIC_EXT( .name = "test_custom_fixed_stats", @@ -43,11 +44,13 @@ typedef struct PgStatShared_CustomFixedEntry static void test_custom_stats_fixed_init_shmem_cb(void *stats); static void test_custom_stats_fixed_reset_all_cb(TimestampTz ts); static void test_custom_stats_fixed_snapshot_cb(void); +static bool test_custom_stats_fixed_flush_cb(bool nowait, bool anytime_only); static const PgStat_KindInfo custom_stats = { .name = "test_custom_fixed_stats", .fixed_amount = true, /* exactly one entry */ .write_to_file = true, /* persist to stats file */ + .flush_mode = FLUSH_ANYTIME, /* can be flushed anytime */ .shared_size = sizeof(PgStat_StatCustomFixedEntry), .shared_data_off = offsetof(PgStatShared_CustomFixedEntry, stats), @@ -56,8 +59,12 @@ static const PgStat_KindInfo custom_stats = { .init_shmem_cb = test_custom_stats_fixed_init_shmem_cb, .reset_all_cb = test_custom_stats_fixed_reset_all_cb, .snapshot_cb = test_custom_stats_fixed_snapshot_cb, + .flush_static_cb = test_custom_stats_fixed_flush_cb, }; +/* Pending statistics */ +static PgStat_StatCustomFixedEntry PendingCustomStats = {0}; + /* * Kind ID for test_custom_fixed_stats. */ @@ -141,6 +148,38 @@ test_custom_stats_fixed_snapshot_cb(void) #undef FIXED_COMP } +/* + * test_custom_stats_fixed_flush_cb + * Flush pending stats to shared memory + */ +static bool +test_custom_stats_fixed_flush_cb(bool nowait, bool anytime_only) +{ + PgStatShared_CustomFixedEntry *stats_shmem; + + /* Nothing to flush if no calls were made */ + if (PendingCustomStats.numcalls == 0) + return false; + + stats_shmem = pgstat_get_custom_shmem_data(PGSTAT_KIND_TEST_CUSTOM_FIXED_STATS); + + if (!nowait) + LWLockAcquire(&stats_shmem->lock, LW_EXCLUSIVE); + else if (!LWLockConditionalAcquire(&stats_shmem->lock, LW_EXCLUSIVE)) + return true; + + pgstat_begin_changecount_write(&stats_shmem->changecount); + stats_shmem->stats.numcalls += PendingCustomStats.numcalls; + pgstat_end_changecount_write(&stats_shmem->changecount); + + LWLockRelease(&stats_shmem->lock); + + /* Reset pending stats */ + PendingCustomStats.numcalls = 0; + + return false; /* successfully flushed */ +} + /*-------------------------------------------------------------------------- * SQL-callable functions *-------------------------------------------------------------------------- @@ -222,3 +261,21 @@ test_custom_stats_fixed_report(PG_FUNCTION_ARGS) /* Return as tuple */ PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls))); } + +/* + * test_custom_stats_fixed_anytime_update + * Increment call counter and schedule anytime flush + */ +PG_FUNCTION_INFO_V1(test_custom_stats_fixed_anytime_update); +Datum +test_custom_stats_fixed_anytime_update(PG_FUNCTION_ARGS) +{ + /* Accumulate in pending stats */ + PendingCustomStats.numcalls++; + + /* Schedule anytime stats update */ + pgstat_schedule_anytime_update(); + pgstat_report_fixed = true; + + PG_RETURN_VOID(); +} diff --git a/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql b/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql index 5ed8cfc2dcf..ed66d38981e 100644 --- a/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql +++ b/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql @@ -24,3 +24,8 @@ CREATE FUNCTION test_custom_stats_var_report(INOUT name TEXT, RETURNS SETOF record AS 'MODULE_PATHNAME', 'test_custom_stats_var_report' LANGUAGE C STRICT PARALLEL UNSAFE; + +CREATE FUNCTION test_custom_stats_var_anytime_update(IN name TEXT) +RETURNS void +AS 'MODULE_PATHNAME', 'test_custom_stats_var_anytime_update' +LANGUAGE C STRICT PARALLEL UNSAFE; diff --git a/src/test/modules/test_custom_stats/test_custom_var_stats.c b/src/test/modules/test_custom_stats/test_custom_var_stats.c index 4c207611236..e9f1bda6b32 100644 --- a/src/test/modules/test_custom_stats/test_custom_var_stats.c +++ b/src/test/modules/test_custom_stats/test_custom_var_stats.c @@ -18,6 +18,7 @@ #include "storage/dsm_registry.h" #include "utils/builtins.h" #include "utils/pgstat_internal.h" +#include "utils/timeout.h" PG_MODULE_MAGIC_EXT( .name = "test_custom_var_stats", @@ -108,6 +109,7 @@ static const PgStat_KindInfo custom_stats = { .name = "test_custom_var_stats", .fixed_amount = false, /* variable number of entries */ .write_to_file = true, /* persist across restarts */ + .flush_mode = FLUSH_ANYTIME, /* can be flushed anytime */ .track_entry_count = true, /* count active entries */ .accessed_across_databases = true, /* global statistics */ .shared_size = sizeof(PgStatShared_CustomVarEntry), @@ -690,3 +692,28 @@ test_custom_stats_var_report(PG_FUNCTION_ARGS) SRF_RETURN_DONE(funcctx); } + +/* + * test_custom_stats_var_anytime_update + * Increment custom statistic counter and schedule anytime flush + */ +PG_FUNCTION_INFO_V1(test_custom_stats_var_anytime_update); +Datum +test_custom_stats_var_anytime_update(PG_FUNCTION_ARGS) +{ + char *stat_name = text_to_cstring(PG_GETARG_TEXT_PP(0)); + PgStat_EntryRef *entry_ref; + PgStat_StatCustomVarEntry *pending_entry; + + /* Get pending entry in local memory */ + entry_ref = pgstat_prep_pending_entry(PGSTAT_KIND_TEST_CUSTOM_VAR_STATS, InvalidOid, + PGSTAT_CUSTOM_VAR_STATS_IDX(stat_name), NULL); + + pending_entry = (PgStat_StatCustomVarEntry *) entry_ref->pending; + pending_entry->numcalls++; + + /* Schedule anytime stats update */ + pgstat_schedule_anytime_update(); + + PG_RETURN_VOID(); +} -- 2.34.1 --NVvBxFuyV/R+1/8/ Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v10-0003-Add-GUC-to-specify-non-transactional-statistics-.patch" ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH v11 2/5] Add anytime flush tests for custom stats @ 2026-02-05 05:54 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Bertrand Drouvot @ 2026-02-05 05:54 UTC (permalink / raw) --- .../test_custom_stats/t/001_custom_stats.pl | 43 ++++++++++++++ .../test_custom_fixed_stats--1.0.sql | 5 ++ .../test_custom_fixed_stats.c | 57 +++++++++++++++++++ .../test_custom_var_stats--1.0.sql | 5 ++ .../test_custom_stats/test_custom_var_stats.c | 27 +++++++++ 5 files changed, 137 insertions(+) 35.8% src/test/modules/test_custom_stats/t/ 64.1% src/test/modules/test_custom_stats/ diff --git a/src/test/modules/test_custom_stats/t/001_custom_stats.pl b/src/test/modules/test_custom_stats/t/001_custom_stats.pl index 9e6a7a38577..6ba4022418f 100644 --- a/src/test/modules/test_custom_stats/t/001_custom_stats.pl +++ b/src/test/modules/test_custom_stats/t/001_custom_stats.pl @@ -156,5 +156,48 @@ $result = $node->safe_psql('postgres', ); is($result, "0", "report of fixed-sized after manual reset"); +# Test FLUSH_ANYTIME mechanism with custom fixed stats +# This verifies that custom stats can be flushed during a transaction + +# Reset stats first +$node->safe_psql('postgres', q(select test_custom_stats_fixed_reset())); +$node->safe_psql('postgres', q(select pg_stat_force_next_flush())); + +my $anytime_test = q[ + BEGIN; + SET LOCAL stats_fetch_consistency = none; + -- Accumulate stats + select test_custom_stats_fixed_anytime_update() from generate_series(1, 2); + -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + select pg_sleep(1.5); + -- Check + select 'fixed_anytime:'||numcalls from test_custom_stats_fixed_report(); +]; + +$result = $node->safe_psql('postgres', $anytime_test); +like($result, qr/^fixed_anytime:2/m, + "anytime fixed stats flushed during transaction"); + +# Test FLUSH_ANYTIME mechanism with custom variable stats +# This verifies that custom stats can be flushed during a transaction + +$node->safe_psql('postgres', q(select pg_stat_force_next_flush())); + +$anytime_test = q[ + BEGIN; + SET LOCAL stats_fetch_consistency = none; + -- Accumulate stats + select test_custom_stats_var_anytime_update('entry2'); + select test_custom_stats_var_anytime_update('entry2'); + -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + select pg_sleep(1.5); + -- Check + select 'var_anytime:'||calls from test_custom_stats_var_report('entry2'); +]; + +$result = $node->safe_psql('postgres', $anytime_test); +like($result, qr/^var_anytime:2/m, + "anytime var stats flushed during transaction"); + # Test completed successfully done_testing(); diff --git a/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql b/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql index 69a93b5241f..da3a798f289 100644 --- a/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql +++ b/src/test/modules/test_custom_stats/test_custom_fixed_stats--1.0.sql @@ -18,3 +18,8 @@ CREATE FUNCTION test_custom_stats_fixed_reset() RETURNS void AS 'MODULE_PATHNAME', 'test_custom_stats_fixed_reset' LANGUAGE C STRICT PARALLEL UNSAFE; + +CREATE FUNCTION test_custom_stats_fixed_anytime_update() +RETURNS void +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT PARALLEL UNSAFE; diff --git a/src/test/modules/test_custom_stats/test_custom_fixed_stats.c b/src/test/modules/test_custom_stats/test_custom_fixed_stats.c index 485e08e5c19..e7fbb2737ef 100644 --- a/src/test/modules/test_custom_stats/test_custom_fixed_stats.c +++ b/src/test/modules/test_custom_stats/test_custom_fixed_stats.c @@ -18,6 +18,7 @@ #include "pgstat.h" #include "utils/builtins.h" #include "utils/pgstat_internal.h" +#include "utils/timeout.h" #include "utils/timestamp.h" PG_MODULE_MAGIC_EXT( @@ -44,11 +45,13 @@ typedef struct PgStatShared_CustomFixedEntry static void test_custom_stats_fixed_init_shmem_cb(void *stats); static void test_custom_stats_fixed_reset_all_cb(TimestampTz ts); static void test_custom_stats_fixed_snapshot_cb(void); +static bool test_custom_stats_fixed_flush_cb(bool nowait, bool anytime_only); static const PgStat_KindInfo custom_stats = { .name = "test_custom_fixed_stats", .fixed_amount = true, /* exactly one entry */ .write_to_file = true, /* persist to stats file */ + .flush_mode = FLUSH_ANYTIME, /* can be flushed anytime */ .shared_size = sizeof(PgStat_StatCustomFixedEntry), .shared_data_off = offsetof(PgStatShared_CustomFixedEntry, stats), @@ -57,8 +60,12 @@ static const PgStat_KindInfo custom_stats = { .init_shmem_cb = test_custom_stats_fixed_init_shmem_cb, .reset_all_cb = test_custom_stats_fixed_reset_all_cb, .snapshot_cb = test_custom_stats_fixed_snapshot_cb, + .flush_static_cb = test_custom_stats_fixed_flush_cb, }; +/* Pending statistics */ +static PgStat_StatCustomFixedEntry PendingCustomStats = {0}; + /* * Kind ID for test_custom_fixed_stats. */ @@ -142,6 +149,38 @@ test_custom_stats_fixed_snapshot_cb(void) #undef FIXED_COMP } +/* + * test_custom_stats_fixed_flush_cb + * Flush pending stats to shared memory + */ +static bool +test_custom_stats_fixed_flush_cb(bool nowait, bool anytime_only) +{ + PgStatShared_CustomFixedEntry *stats_shmem; + + /* Nothing to flush if no calls were made */ + if (PendingCustomStats.numcalls == 0) + return false; + + stats_shmem = pgstat_get_custom_shmem_data(PGSTAT_KIND_TEST_CUSTOM_FIXED_STATS); + + if (!nowait) + LWLockAcquire(&stats_shmem->lock, LW_EXCLUSIVE); + else if (!LWLockConditionalAcquire(&stats_shmem->lock, LW_EXCLUSIVE)) + return true; + + pgstat_begin_changecount_write(&stats_shmem->changecount); + stats_shmem->stats.numcalls += PendingCustomStats.numcalls; + pgstat_end_changecount_write(&stats_shmem->changecount); + + LWLockRelease(&stats_shmem->lock); + + /* Reset pending stats */ + PendingCustomStats.numcalls = 0; + + return false; /* successfully flushed */ +} + /*-------------------------------------------------------------------------- * SQL-callable functions *-------------------------------------------------------------------------- @@ -223,3 +262,21 @@ test_custom_stats_fixed_report(PG_FUNCTION_ARGS) /* Return as tuple */ PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls))); } + +/* + * test_custom_stats_fixed_anytime_update + * Increment call counter and schedule anytime flush + */ +PG_FUNCTION_INFO_V1(test_custom_stats_fixed_anytime_update); +Datum +test_custom_stats_fixed_anytime_update(PG_FUNCTION_ARGS) +{ + /* Accumulate in pending stats */ + PendingCustomStats.numcalls++; + + /* Schedule anytime stats update */ + pgstat_schedule_anytime_update(); + pgstat_report_fixed = true; + + PG_RETURN_VOID(); +} diff --git a/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql b/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql index 5ed8cfc2dcf..ed66d38981e 100644 --- a/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql +++ b/src/test/modules/test_custom_stats/test_custom_var_stats--1.0.sql @@ -24,3 +24,8 @@ CREATE FUNCTION test_custom_stats_var_report(INOUT name TEXT, RETURNS SETOF record AS 'MODULE_PATHNAME', 'test_custom_stats_var_report' LANGUAGE C STRICT PARALLEL UNSAFE; + +CREATE FUNCTION test_custom_stats_var_anytime_update(IN name TEXT) +RETURNS void +AS 'MODULE_PATHNAME', 'test_custom_stats_var_anytime_update' +LANGUAGE C STRICT PARALLEL UNSAFE; diff --git a/src/test/modules/test_custom_stats/test_custom_var_stats.c b/src/test/modules/test_custom_stats/test_custom_var_stats.c index 4c207611236..e9f1bda6b32 100644 --- a/src/test/modules/test_custom_stats/test_custom_var_stats.c +++ b/src/test/modules/test_custom_stats/test_custom_var_stats.c @@ -18,6 +18,7 @@ #include "storage/dsm_registry.h" #include "utils/builtins.h" #include "utils/pgstat_internal.h" +#include "utils/timeout.h" PG_MODULE_MAGIC_EXT( .name = "test_custom_var_stats", @@ -108,6 +109,7 @@ static const PgStat_KindInfo custom_stats = { .name = "test_custom_var_stats", .fixed_amount = false, /* variable number of entries */ .write_to_file = true, /* persist across restarts */ + .flush_mode = FLUSH_ANYTIME, /* can be flushed anytime */ .track_entry_count = true, /* count active entries */ .accessed_across_databases = true, /* global statistics */ .shared_size = sizeof(PgStatShared_CustomVarEntry), @@ -690,3 +692,28 @@ test_custom_stats_var_report(PG_FUNCTION_ARGS) SRF_RETURN_DONE(funcctx); } + +/* + * test_custom_stats_var_anytime_update + * Increment custom statistic counter and schedule anytime flush + */ +PG_FUNCTION_INFO_V1(test_custom_stats_var_anytime_update); +Datum +test_custom_stats_var_anytime_update(PG_FUNCTION_ARGS) +{ + char *stat_name = text_to_cstring(PG_GETARG_TEXT_PP(0)); + PgStat_EntryRef *entry_ref; + PgStat_StatCustomVarEntry *pending_entry; + + /* Get pending entry in local memory */ + entry_ref = pgstat_prep_pending_entry(PGSTAT_KIND_TEST_CUSTOM_VAR_STATS, InvalidOid, + PGSTAT_CUSTOM_VAR_STATS_IDX(stat_name), NULL); + + pending_entry = (PgStat_StatCustomVarEntry *) entry_ref->pending; + pending_entry->numcalls++; + + /* Schedule anytime stats update */ + pgstat_schedule_anytime_update(); + + PG_RETURN_VOID(); +} -- 2.34.1 --2MEBAGW8+kohXisi Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v11-0003-Add-GUC-to-specify-non-transactional-statistics-.patch" ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-02-05 05:54 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-10-22 02:22 [PATCH v10 6/7] Row pattern recognition patch (tests). Tatsuo Ishii <[email protected]> 2026-02-05 05:54 [PATCH v9 2/5] Add anytime flush tests for custom stats Bertrand Drouvot <[email protected]> 2026-02-05 05:54 [PATCH v10 2/5] Add anytime flush tests for custom stats Bertrand Drouvot <[email protected]> 2026-02-05 05:54 [PATCH v11 2/5] Add anytime flush tests for custom stats Bertrand Drouvot <[email protected]> 2026-02-05 05:54 [PATCH v6 2/5] Add anytime flush tests for custom stats Bertrand Drouvot <[email protected]> 2026-02-05 05:54 [PATCH v7 2/5] Add anytime flush tests for custom stats Bertrand Drouvot <[email protected]> 2026-02-05 05:54 [PATCH v8 2/5] Add anytime flush tests for custom stats Bertrand Drouvot <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox