Some of our queries are hanging

[hugbubby]

One of our queries regularly hangs inside a transaction on certain datasets. When I check postgres it looks like this:

 3292628 | doadmin | idle in transaction | SELECT "public"."AstFunction"."id", "public"."AstFunction"."functionHash", "public"."AstFunction"."functionName", "public"."AstFunction"."language", "public"."AstFunction"."functionSummary", "public"."AstFunction"."astCodeRangeId", "public"."AstFunction"."scaPackageId", "public"."AstFunction"."isExternalFunction", "public"."AstFunction"."libraryName", "public"."AstFunction"."isFromStdlib", "public"."AstFunction"."isBuiltinFunction", "public"."AstFunction"."createdAt", "public"."AstFunction"."updatedAt", "public"."AstFunction"."sastScanId", "public"."AstFunction"."astFileName" FROM "public"."AstFunction" WHERE "public"."AstFunction"."id" IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80,$81,$82,$83,$84,$85,$86,$87,$88,$89,$90,$91,$92,$93,$94,$95,$96 | 2025-02-22 06:53:43.228899+00 | 00:06:30.402136

The query looks like this:

res, err := dbclient.SastScan.FindUnique(
	db.SastScan.CodeScanID.Equals(s.job.ID),
).With(
	db.SastScan.Applications.Fetch().With(
		db.SastApplication.SourceHandlers.Fetch().With(
			db.SourceHandler.EncapsulatingFile.Fetch(),
			db.SourceHandler.EncapsulatingFunction.Fetch(),
			db.SourceHandler.ThreatScenarios.Fetch().With(
				db.BusinessLogicThreatScenario.ScenarioLocation.Fetch(),
				db.BusinessLogicThreatScenario.SourceHandler.Fetch(),
				db.BusinessLogicThreatScenario.Validation.Fetch(),
			),
			db.SourceHandler.AccessibleSinks.Fetch().With(
				db.AccessibleSink.SinkLocation.Fetch(),
				db.AccessibleSink.SourceHandler.Fetch(),
				db.AccessibleSink.Validation.Fetch(),
			),
			db.SourceHandler.NaturalLanguageRuleEvaluations.Fetch().With(
				db.NaturalLanguageRuleEvaluation.Rule.Fetch(),
				db.NaturalLanguageRuleEvaluation.SourceHandler.Fetch(),
				db.NaturalLanguageRuleEvaluation.EvaluationLocation.Fetch(),
			),
		),
	),
).Exec(ctx)

In this particular case, this query hangs forever unless ctx is given a deadline.

Other queries return fine.

Can add more details in a DM

[steebchen]

Hmm, can you try to query for active SQL queries to figure out that they might be just taking long? https://www.perplexity.ai/search/postgres-list-all-active-sql-q-c.B7ksJMRcea_2L4ENDqAg

also running an explain with the query would be useful to see if there are full database scans, and then you could add an index to potentially solve the issue

[hugbubby]

The query I used to get the first codeblock was:

SELECT pid,
       usename,
       state,
       query,
       query_start,
       now() - query_start AS duration
FROM pg_stat_activity
WHERE state <> 'idle'

The transaction appears idle, as you can see with the value of the 'state' column. There are no active queries. I think this is a problem with the transaction not actually being committed or the library itself hanging or something.

This is the result of running your query

${dbname}-dev=> SELECT pid, query, state 
FROM pg_stat_activity 
WHERE state = 'active';
   pid   |           query           | state  
---------+---------------------------+--------
    2574 | <insufficient privilege>  | active
 3311554 | SELECT pid, query, state +| active
         | FROM pg_stat_activity    +| 
         | WHERE state = 'active';   | 
(2 rows)

[hugbubby]

I don't think this is a timing/query performance problem because the query has been active for seven hours now and this is a development database with very few rows. I have enabled query logging on the service, and tried every query I saw using PSQL, and they all returned quickly.

[steebchen]

Hmm. Please try upgrading to https://github.com/steebchen/prisma-client-go/releases/tag/v0.47.0 for now which I've just released which includes the latest Prisma engine. If this still gives you the same issue, it would be worth to check whether the same query gives you issues locally using the JS client.