thunderdome-planning-poker: Bug: My Games overview has an error

List of /games is and stays empty.

ERR poker/client.go:87 > | ts=1711586159.74078 msg=close error version=3.8.3 error=close tcp 172.17.0.6:8080->172.17.0.1:42525: use of closed network connection poker_id=22fc1095-527c-429d-823b-caa99150cfc1 session_user_id=d3ce7a1d-9bfa-422c-b05d-225380e7cc0e stacktrace=github.com/StevenWeathers/thunderdome-planning-poker/internal/http/poker.subscription.readPump.func1
	/home/runner/work/thunderdome-planning-poker/thunderdome-planning-poker/internal/http/poker/client.go:87
github.com/StevenWeathers/thunderdome-planning-poker/internal/http/poker.subscription.readPump
	/home/runner/work/thunderdome-planning-poker/thunderdome-planning-poker/internal/http/poker/client.go:153 

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Comments: 30 (14 by maintainers)

Commits related to this issue

Most upvoted comments

If you could give this a shot replace USER_ID_HERE and let me know your performance.

WITH user_teams AS (
	SELECT t.id, t.name FROM thunderdome.team_user tu
	LEFT JOIN thunderdome.team t ON t.id = tu.team_id
	WHERE tu.user_id = 'USER_ID_HERE'
),
team_games AS (
	SELECT id FROM thunderdome.poker WHERE team_id IN (SELECT id FROM user_teams)
),
user_games AS (
	SELECT u.poker_id AS id FROM thunderdome.poker_user u
	WHERE u.user_id = 'USER_ID_HERE' AND u.abandoned = false
),
games AS (
	SELECT id from user_games UNION ALL SELECT id FROM team_games
),
stories AS (
	SELECT poker_id, points FROM thunderdome.poker_story WHERE poker_id IN (SELECT poker_id FROM games)
)
SELECT p.id, p.name, p.voting_locked, COALESCE(p.active_story_id::text, ''), p.point_values_allowed, p.auto_finish_voting,
		  p.point_average_rounding, p.created_date, p.updated_date,
		  CASE WHEN COUNT(s) = 0 THEN '[]'::json ELSE array_to_json(array_agg(row_to_json(s))) END AS stories,
		  CASE WHEN COUNT(bl) = 0 THEN '[]'::json ELSE array_to_json(array_agg(bl.user_id)) END AS facilitators,
		  min(COALESCE(t.name, '')) as team_name
		FROM thunderdome.poker p
		LEFT JOIN stories AS s ON s.poker_id = p.id
		LEFT JOIN (
			SELECT poker_id, user_id FROM thunderdome.poker_facilitator WHERE poker_id IN (SELECT poker_id FROM games)
		) AS bl ON bl.poker_id = p.id
		LEFT JOIN user_teams t ON t.id = p.team_id
		WHERE p.id IN (SELECT id FROM games)
		GROUP BY p.id ORDER BY p.created_date DESC;
image I think I've got the query to a super optimal state...

I’m playing with the Query and have the number of rows way down, still trying to optimize further.

Ok I see what’s going on, its the poker_user join thats actually increasing the query scans.

Good news is you can increase the http timeouts in the short term, currently the default is 5 seconds.

viper.SetDefault("http.write_timeout", 5)
viper.SetDefault("http.read_timeout", 5)

Ok so that the team_user table does not have an index on the team_id column, that could improve performance for a larger table of users.