How can I rerun a statement from server logs without manual editing?

Question

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?

Answer 1

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

Answer 2

test=# prepare test as select to_char(current_date,$1);
LOG:  statement: prepare test as select to_char(current_date,$1);
LOG:  duration: 0.089 ms
PREPARE
test=# execute test($1='YYYY-MM-DD');
LOG:  statement: execute test($1='YYYY-MM-DD');
DETAIL:  prepare: prepare test as select to_char(current_date,$1);
ERROR:  there is no parameter $1
LINE 1: execute test($1='YYYY-MM-DD');
                     ^
test=# execute test('YYYY-MM-DD');
LOG:  statement: execute test('YYYY-MM-DD');
DETAIL:  prepare: prepare test as select to_char(current_date,$1);
LOG:  duration: 0.129 ms
  to_char
------------
 2019-12-14
(1 row)

test=#