4

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?

Improve this question
1
  • Use: execute foo('YYYY-MM-DD', '154971', '130460',...
    – Luuk
    Commented Dec 14, 2019 at 12:21

2 Answers 2

Reset to default
1

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

Improve this answer
1
  • I'm going to mark this as accepted-- I might have some pull requests for you, but at least you have the log parsing part working so I don't have to do that ;)
    – carols10cents
    Commented Dec 16, 2019 at 20:29
-1 
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=#
Improve this answer
1
  • Yes, I know what the execute format is, I was hoping I wouldn't have to transform the log output into the execute format.
    – carols10cents
    Commented Dec 16, 2019 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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