dlangille
commented
2 months ago I saw this problem yesterday. @rsmith-nl today it seems OK.
Closed rsmith-nl closed 1 month ago
dlangille
commented
2 months ago I saw this problem yesterday. @rsmith-nl today it seems OK.
rsmith-nl
commented
2 months ago Still looks the same to me.
To exclude that this might be a browser issue on my side, I also opened the quarterly branch with links, and it also shows the most recent post on the quarterly branch being from the 11th of July.
dlangille
commented
2 months ago I now see the difference:
dlangille
commented
2 months ago When logged in:
-- /usr/local/www/freshports/classes/commits.php::FetchLimit
with recent_commits AS
(WITH CL AS
(select * from commit_log order by commit_date DESC limit 5000)
select distinct CL.*, CLPE.element_id AS clpe_element_id from CL join commit_log_branches clb on clb.branch_id =
(select id from system_branch where branch_name = $1) and CL.id = CLB.commit_log_id
JOIN commit_log_ports_elements CLPE on CLPE.commit_log_id = CL.id
LIMIT $2)
SELECT DISTINCT
RC.commit_date - SystemTimeAdjust() AS commit_date_raw,
RC.id AS commit_log_id,
RC.encoding_losses AS encoding_losses,
RC.message_id AS message_id,
RC.commit_hash_short AS commit_hash_short,
RC.committer AS committer,
RC.committer_name AS committer_name,
RC.committer_email AS committer_email,
RC.author_name AS author_name,
RC.author_email AS author_email,
RC.description AS commit_description,
to_char(RC.commit_date - SystemTimeAdjust(), 'DD Mon YYYY') AS commit_date,
to_char(RC.commit_date - SystemTimeAdjust(), 'HH24:MI') AS commit_time,
CLP.port_id AS port_id,
C.name AS category,
C.id AS category_id,
E.name AS port,
element_pathname(CLPE.element_id) AS element_pathname,
CLPE.element_id AS element_id,
CASE when CLP.port_version IS NULL then P.version else CLP.port_version END AS version,
CASE when CLP.port_version is NULL then P.revision else CLP.port_revision END AS revision,
CASE when CLP.port_epoch is NULL then P.portepoch else CLP.port_epoch END AS epoch,
E.status AS status,
CLP.needs_refresh AS needs_refresh,
P.forbidden AS forbidden,
P.broken AS broken,
P.deprecated AS deprecated,
P.ignore AS ignore,
P.expiration_date AS expiration_date,
date_part('epoch', P.date_added) AS date_added,
P.short_description AS short_description,
RC.svn_revision AS svn_revision,
R.name AS repo_name,
R.repo_hostname AS repo_hostname,
R.repository AS repository,
R.path_to_repo AS path_to_repo,
PV.current AS vulnerable_current,
PV.past AS vulnerable_past,
STF.message AS stf_message,
P.is_interactive AS is_interactive,
P.no_cdrom AS no_cdrom,
P.restricted AS restricted,
SB.branch_name AS branch,
onwatchlist
FROM recent_commits RC
JOIN commit_log_ports_elements CLPE ON CLPE.commit_log_id = RC.id
LEFT OUTER JOIN commit_log_branches CLB ON CLPE.commit_log_id= CLB.commit_log_id
JOIN system_branch SB ON SB.id = CLB.branch_id
LEFT OUTER JOIN ports P ON P.element_id = CLPE.element_id
LEFT OUTER JOIN commit_log_ports CLP ON P.id = CLP.port_id and CLP.commit_log_id = RC.id
LEFT OUTER JOIN element E ON E.id = P.element_id
LEFT OUTER JOIN categories C ON C.id = P.category_id
LEFT OUTER JOIN repo R on RC.repo_id = R.id
LEFT OUTER JOIN sanity_test_failures STF ON STF.commit_log_id = RC.id
LEFT OUTER JOIN ports_vulnerable PV ON P.id = PV.port_id
LEFT OUTER JOIN
(SELECT element_id as wle_element_id, COUNT(watch_list_id) as onwatchlist
FROM watch_list JOIN watch_list_element
ON watch_list.id = watch_list_element.watch_list_id
AND watch_list.user_id = $3
AND watch_list.in_service
GROUP BY wle_element_id) AS TEMP
ON TEMP.wle_element_id = E.id
ORDER BY 1 desc,
RC.id DESC,
category,
portNot logged in:
-- /usr/local/www/freshports/classes/commits.php::FetchLimit
with recent_commits AS
(WITH CL AS
(select * from commit_log order by commit_date DESC limit 5000)
select distinct CL.*, CLPE.element_id AS clpe_element_id from CL join commit_log_branches clb on clb.branch_id =
(select id from system_branch where branch_name = $1) and CL.id = CLB.commit_log_id
JOIN commit_log_ports_elements CLPE on CLPE.commit_log_id = CL.id
LIMIT $2)
SELECT DISTINCT
RC.commit_date - SystemTimeAdjust() AS commit_date_raw,
RC.id AS commit_log_id,
RC.encoding_losses AS encoding_losses,
RC.message_id AS message_id,
RC.commit_hash_short AS commit_hash_short,
RC.committer AS committer,
RC.committer_name AS committer_name,
RC.committer_email AS committer_email,
RC.author_name AS author_name,
RC.author_email AS author_email,
RC.description AS commit_description,
to_char(RC.commit_date - SystemTimeAdjust(), 'DD Mon YYYY') AS commit_date,
to_char(RC.commit_date - SystemTimeAdjust(), 'HH24:MI') AS commit_time,
CLP.port_id AS port_id,
C.name AS category,
C.id AS category_id,
E.name AS port,
element_pathname(CLPE.element_id) AS element_pathname,
CLPE.element_id AS element_id,
CASE when CLP.port_version IS NULL then P.version else CLP.port_version END AS version,
CASE when CLP.port_version is NULL then P.revision else CLP.port_revision END AS revision,
CASE when CLP.port_epoch is NULL then P.portepoch else CLP.port_epoch END AS epoch,
E.status AS status,
CLP.needs_refresh AS needs_refresh,
P.forbidden AS forbidden,
P.broken AS broken,
P.deprecated AS deprecated,
P.ignore AS ignore,
P.expiration_date AS expiration_date,
date_part('epoch', P.date_added) AS date_added,
P.short_description AS short_description,
RC.svn_revision AS svn_revision,
R.name AS repo_name,
R.repo_hostname AS repo_hostname,
R.repository AS repository,
R.path_to_repo AS path_to_repo,
PV.current AS vulnerable_current,
PV.past AS vulnerable_past,
STF.message AS stf_message,
P.is_interactive AS is_interactive,
P.no_cdrom AS no_cdrom,
P.restricted AS restricted,
SB.branch_name AS branch,
NULL AS onwatchlist
FROM recent_commits RC
JOIN commit_log_ports_elements CLPE ON CLPE.commit_log_id = RC.id
LEFT OUTER JOIN commit_log_branches CLB ON CLPE.commit_log_id= CLB.commit_log_id
JOIN system_branch SB ON SB.id = CLB.branch_id
LEFT OUTER JOIN ports P ON P.element_id = CLPE.element_id
LEFT OUTER JOIN commit_log_ports CLP ON P.id = CLP.port_id and CLP.commit_log_id = RC.id
LEFT OUTER JOIN element E ON E.id = P.element_id
LEFT OUTER JOIN categories C ON C.id = P.category_id
LEFT OUTER JOIN repo R on RC.repo_id = R.id
LEFT OUTER JOIN sanity_test_failures STF ON STF.commit_log_id = RC.id
LEFT OUTER JOIN ports_vulnerable PV ON P.id = PV.port_id
ORDER BY 1 desc,
RC.id DESC,
category,
port[15:26 dvl-nginx01 dvl ~/tmp] % diff -ruN logged-out logged-in
--- logged-out 2024-08-04 15:26:21.594169000 +0000
+++ logged-in 2024-08-04 15:26:23.096851000 +0000
@@ -50,7 +50,7 @@
P.no_cdrom AS no_cdrom,
P.restricted AS restricted,
SB.branch_name AS branch,
- NULL AS onwatchlist
+ onwatchlist
FROM recent_commits RC
JOIN commit_log_ports_elements CLPE ON CLPE.commit_log_id = RC.id
LEFT OUTER JOIN commit_log_branches CLB ON CLPE.commit_log_id= CLB.commit_log_id
@@ -62,6 +62,14 @@
LEFT OUTER JOIN repo R on RC.repo_id = R.id
LEFT OUTER JOIN sanity_test_failures STF ON STF.commit_log_id = RC.id
LEFT OUTER JOIN ports_vulnerable PV ON P.id = PV.port_id
+ LEFT OUTER JOIN
+ (SELECT element_id as wle_element_id, COUNT(watch_list_id) as onwatchlist
+ FROM watch_list JOIN watch_list_element
+ ON watch_list.id = watch_list_element.watch_list_id
+ AND watch_list.user_id = $3
+ AND watch_list.in_service
+ GROUP BY wle_element_id) AS TEMP
+ ON TEMP.wle_element_id = E.id
ORDER BY 1 desc,
RC.id DESC,
category,I found an SQL issue. Please try again @rsmith-nl
rsmith-nl
commented
2 months ago It's fixed. Thanks!
dlangille
commented
1 month ago The original query: https://explain.depesz.com/s/1dd0 - 141.734 ms
The fixed query: https://explain.depesz.com/s/224A - 62.735 ms
An additional 80ms is deemed acceptable. ;)
dlangille
commented
1 month ago Well, that got committed to head. :/
dlangille
commented
1 month ago New freshports-www package created and installed on prod.
@rsmith-nl thank you for reporting this.
If I open https://www.freshports.org/?branch=quarterly, the top modification is dated "Thursday, 11 Jul 2024".
However, if I then click on "yesterday's commit", e.g. https://www.freshports.org/date.php?date=2024/08/02&branch=2024Q3 it does show entries for august 2nd.