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?

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

2 Answers 2

1

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

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 ;) 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=#
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. 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.