PostgreSQL Logs – How to Rerun a Statement from Server Logs Without Manual Editing

postgresqlpsql

I have some statements in my logs like this:

SELECT to_char("version_downloads"."date", $1), SUM(version_downloads.downloads) FROM "version_downloads" WHERE "version_downloads"."version_id" IN ($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) GROUP BY "version_downloads"."date" ORDER BY "version_downloads"."date" ASC
DETAIL:  parameters: $1 = 'YYYY-MM-DD', $2 = '154971', $3 = '130460', $4 = '127212', $5 = '125555', $6 = '125521', $7 = '119212', $8 = '117808', $9 = '115893', $10 = '113343', $11 = '130165', $12 = '102932', $13 = '99538', $14 = '98022', $15 = '97193', $16 = '95831', $17 = '93664', $18 = '92908', $19 = '89893', $20 = '86584', $21 = '130166', $22 = '127355', $23 = '127151', $24 = '104133', $25 = '76564', $26 = '74803', $27 = '73853', $28 = '130167', $29 = '79903', $30 = '78200', $31 = '76563', $32 = '75606', $33 = '70485', $34 = '67607', $35 = '60891', $36 = '38564', $37 = '22300', $38 = '20439', $39 = '17884', $40 = '15268', $41 = '14687', $42 = '13851', $43 = '9238', $44 = '7843', $45 = '7771', $46 = '7710', $47 = '7648', $48 = '7248', $49 = '7239', $50 = '7237', $51 = '6964', $52 = '6704', $53 = '6029', $54 = '5937', $55 = '5252', $56 = '4371', $57 = '4362'

These statements were generated by an ORM. I would like to rerun this query in psql so I can optionally do EXPLAIN ANALYZE and change parts of the query and so forth. Ideally, I would like to be able to copy queries like this one and paste it into psql without any modification. Small modifications are acceptable, like copying only parts or adding text to the beginning or the end.

When I try copy-pasting directly, the SELECT isn't valid:

# SELECT to_char("version_downloads"."date", $1), SUM(version_downloads.downloads) FROM "version_downloads" WHERE "version_downloads"."version_id" IN ($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) GROUP BY "version_downloads"."date" ORDER BY "version_downloads"."date" ASC;
ERROR:  there is no parameter $1
LINE 1: SELECT to_char("version_downloads"."date", $1), SUM(version_...

Trying to run this as a prepared statement by adding PREPARE [name] AS to the beginning of the select and execute foo() around the parameters also doesn't work:

# PREPARE foo AS SELECT to_char("version_downloads"."date", $1), SUM(version_downloads.downloads) FROM "version_downloads" WHERE "version_downloads"."version_id" IN ($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) GROUP BY "version_downloads"."date" ORDER BY "version_downloads"."date" ASC;
PREPARE

# execute foo($1 = 'YYYY-MM-DD', $2 = '154971', $3 = '130460', $4 = '127212', $5 = '125555', $6 = '125521', $7 = '119212', $8 = '117808', $9 = '115893', $10 = '113343', $11 = '130165', $12 = '102932', $13 = '99538', $14 = '98022', $15 = '97193', $16 = '95831', $17 = '93664', $18 = '92908', $19 = '89893', $20 = '86584', $21 = '130166', $22 = '127355', $23 = '127151', $24 = '104133', $25 = '76564', $26 = '74803', $27 = '73853', $28 = '130167', $29 = '79903', $30 = '78200', $31 = '76563', $32 = '75606', $33 = '70485', $34 = '67607', $35 = '60891', $36 = '38564', $37 = '22300', $38 = '20439', $39 = '17884', $40 = '15268', $41 = '14687', $42 = '13851', $43 = '9238', $44 = '7843', $45 = '7771', $46 = '7710', $47 = '7648', $48 = '7248', $49 = '7239', $50 = '7237', $51 = '6964', $52 = '6704', $53 = '6029', $54 = '5937', $55 = '5252', $56 = '4371', $57 = '4362');
ERROR:  there is no parameter $1
LINE 1: execute foo($1 = 'YYYY-MM-DD', $2 = '154971', $3 = '130460',...
                    ^

Is there a setting I can change for the log format so that the queries are copy-pasteable? Is there an alternate way of calling the prepared statement? Do I need to write a script to turn the logs into a well-formatted query?

Best Answer

I would use pgreplay to replay statements from the log, but then that's not surprising, since I wrote that tool.