Hi team,
Just found another clash with PostgreSQL 10 while running php maintenance/updateStats.php on REL1_37 branch:
SELECT AVG( 1646323491 - UNIX_TIMESTAMP(fp_pending_since) )
FROM "flaggedpages","page"
WHERE (fp_pending_since IS NOT NULL)
AND (fp_page_id = page_id)
AND page_namespace IN (0,6,10)
LIMIT 1;
Fails with error: Error 42883: ERROR: function unix_timestamp(timestamp with time zone) does not exist
Thank you for checking!
I've now added these to functions to PG 10 in order to resolve the error.
CREATE OR REPLACE FUNCTION UNIX_TIMESTAMP(TIMESTAMP
WITHOUT TIME ZONE)
RETURNS BIGINT
LANGUAGE SQL
IMMUTABLE STRICT
AS 'SELECT EXTRACT(EPOCH FROM $1)::bigint;';
CREATE OR REPLACE FUNCTION UNIX_TIMESTAMP(TIMESTAMP
WITH TIME ZONE)
RETURNS BIGINT
LANGUAGE SQL
IMMUTABLE STRICT
AS 'SELECT EXTRACT(EPOCH FROM $1)::bigint;';
However now receive a new error:
Wikimedia\Rdbms\DBQueryError from line 1809 of /mediawiki/includes/libs/rdbms/database/Database.php: Error 23502: ERROR: null value in column "frs_stat_val" violates not-null constraint
DETAIL: Failing row contains (2022-03-04 09:48:17+00, reviewLag-anon-sampleStartTimestamp, null).
Function: FlaggedRevsStats::updateCache
Query: INSERT INTO "flaggedrevs_statistics" (frs_stat_key,frs_stat_val,frs_timestamp) VALUES ('reviewLag-anon-sampleStartTimestamp',NULL,'2022-03-04 09:48:17+00'),('reviewLag-user-sampleStartTimestamp',NULL,'2022-03-04 09:48:17+00'),('reviewLag-anon-sampleEndTimestamp',NULL,'2022-03-04 09:48:17+00'),('reviewLag-user-sampleEndTimestamp',NULL,'2022-03-04 09:48:17+00'),('reviewLag-anon-sampleSize',0,'2022-03-04 09:48:17+00'),('reviewLag-user-sampleSize',0,'2022-03-04 09:48:17+00'),('reviewLag-anon-average',0,'2022-03-04 09:48:17+00'),('reviewLag-user-average',0,'2022-03-04 09:48:17+00'),('reviewLag-anon-median',0,'2022-03-04 09:48:17+00'),('reviewLag-user-median',0,'2022-03-04 09:48:17+00'),('pendingLag-average',0,'2022-03-04 09:48:17+00'),('totalPages-NS:0',2,'2022-03-04 09:48:17+00'),('reviewedPages-NS:0',1,'2022-03-04 09:48:17+00'),('syncedPages-NS:0',1,'2022-03-04 09:48:17+00'),('totalPages-NS:6',0,'2022-03-04 09:48:17+00'),('reviewedPages-NS:6',0,'2022-03-04 09:48:17+00'),('syncedPages-NS:6',0,'2022-03-04 09:48:17+00'),('totalPages-NS:10',0,'2022-03-04 09:48:17+00'),('reviewedPages-NS:10',0,'2022-03-04 09:48:17+00'),('syncedPages-NS:10',0,'2022-03-04 09:48:17+00') ON CONFLICT DO NOTHING