0

I am currently trying to update/modify a number of SQL queries on a system that does not have any internal Documentation (Developer left on short notice and on bad terms).

I have enabled Query logging in Postgres and have tracked down the Queries in question to be in a number of approximately 30 queries. From there, I plan on running them and seeing what the output is and then determining which ones need the updates.

The problem is that all them in the log are in this format (Simplified)

Select t.Col1, t2.Col2, t3.Col3 from Table t
join table2 t2 on t2.Col1 = t.Col1
join table3 t3 on t3.Col1 = t2.Col3
where t.Col1 = $1
and t2.Col3 = $2
...
and t3.Col20 = $15

etc.

For some of the parameters, I've been able to guesstimate what the most likely values are (based on the info in the column it looks up), however there are a few that I've got no idea on what they could be.

I'm not super-familiar with Postgres (more of an MSSQL man myself) nor am I familiar with using parameters/variables in queries. It seems to me these values must be either generated somewhere or held somewhere and most be evaluated at some point in order to run a valid query. As such, either logging the values or logging the input or something similar should be possible so I can then finish what is required.

Hoepfully someone can advise how to get the 'real' values of the variables $1,$2, $3 etc.

0

If you set log_min_duration_statement = 0 in postgresql.conf and reload, all statements and the parameter values will be written to the log file.

Warning: on a busy system, logging all statements can be a bottleneck.

3
  • Cool - thanks for that, will try tonight - on the system, we've fortunately got a Prod and Stage environment which share an identical code base - so all the testing is done in Stage (which has virtually no traffic on it atm) – TheDemonLord Apr 3 '20 at 22:03
  • Tried setting that value - but I still don't see anywhere in the log that any of the parameters are set or pulled from. I've grepped the output log file for the highest numbered paramater (47) and there is no Query that I can see that either sets the value or pulls it from somewhere - are there any other config options that need to be set? – TheDemonLord Apr 4 '20 at 8:42
  • You have to look into the PostgreSQL log file. The parameters are in the DETAIL message after the message with the slow statement. – Laurenz Albe Apr 4 '20 at 20:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.