unfrotunatelly i can't point you to message in archives, as there is some problem with them, and i dont see posts newer than “Fri Apr 04 12:00:08 2008".
this patch was written by tomas doran, and commited by bruce momjian:
Log Message: ----------- Implement current_query(), that shows the currently executing query. At the same time remove dblink/dblink_current_query() as it is no longer necessary *BACKWARD COMPATIBILITY ISSUE* for dblink
to be honest, at first i didn't see why it was commited. then i recomplied postgresql with this new functionality and tested it.
i still dont quite get why it was added. as i see it, it's a wrapper around:
SELECT current_query FROM pg_stat_activity WHERE procpid = pg_backend_pid();
it's nice to have it wrapped in single function, but to be honest – i dont really see the point.
on the other hand – one important benefit is that current_query() works even if i have track_activities set to off. which is nice.
manual say, that this function returns “text of the currently executing query (might contain more than one statement)".
i dont see (at the moment) when there might be more than one currently executing query. i guess it has to be something with spi, but in my (short) tests i haven't found a case when i would have 2 queries running.
maybe you can show me such cases?
I originally provided the patch for this. It was mangled a bit before being committed, but is essentially the same as something I wrote for work.
‘one important benefit is that current_query() works even if i have track_activities set to off’ – that was the big one for me.
Some tables (e.g. encrypted credit card numbers) were un-accessible to the normal application user (with gated access through stored procedures). Said stored procedure(s) would get / set 1 CC number at a time, and logged their own invocation. The application emitted an ‘audit comment’ (a comment of the form /* userid/line & file of application causing decryption/unique web hit id */) was prepended to each query, and so we logged the access event, and it’s cause in the postgres logs.
Turning query logging, or statement collection on on our production database platform at the time was **just not an option**, and we needed to be 100% confident of the audit trail.
I found that the feature had already been implemented in dblink, and was in the list of things on the postgres site which ‘needed implementing’ – so I fiddled around for a while, got it working and threw a patch at the mailing list 😉
I also thought about querying the pg_stat_activity table, but I wasn’t sure about the behavior that I’d see querying that from inside a transaction which had been started within a stored procedure.. To be honest, I didn’t test this scenarion, as I was doing work for PCI compliance, and so getting the text of *the exact query run by the application* for auditing purposes was the aim.
Any idea why, when I call current_query() from a trigger function I don’t get actual values, but instead $1, $2, etc for the values in an insert statement. I believe the query is from a jdbc prepared statement. The regular jdbc statement does have the values.
ex return from current_query():
insert into blah (id, type) values ($1,$2)
Any way around this? Thx.
@Housty:
that’s precisely because of prepared statements. And the solution is: DON’T USE PREPARED STATEMENTS. Reasons:
1. it breaks pgbouncer in very useful “transaction pooling” mode
2. https://www.depesz.com/2008/05/10/prepared-statements-gotcha/
@DEPESZ
Is there a way to get rid of this $1? (Yes, I have to/want to use prepared statement)
Rgds,
M
@Meehaw:
not that I know of.
And get rid of prepared statements 🙂
Well,
Asked it on stachoverflow… there is a workaround 🙂
http://stackoverflow.com/questions/7107237/how-to-get-value-of-1-parameter-from-executed-prepared-statement-inside-a-trigg
Cheers,
Michał
@Meehaw:
faaaar from acceptable.
Think about what will happen, when I’ll issue DELETE FROM table WHERE non_pk_column = $1 ($1 = ‘depesz’)