0

Recently my team have get a situation in which some records in our shared test database disappear with no clear reason. Because it's a shared database (which is utilized by so many teams), so that we can't track down if it's a programming mistake or someone just run a bad sql script.

So that I'm looking for a way to notify (at database level) when a row of a specific table A get deleted. I have looked at the Postgres TRIGGER, but it failed to give me the specific sql that cause the deletion.

Is there anyway I can log the sql statement which cause the deletion of some rows in table A?

S-Man
  • 22,521
  • 7
  • 40
  • 63
Hoàng Long
  • 10,746
  • 20
  • 75
  • 124

2 Answers2

2

You could use something like this.

It allows you to create a special triggers for PostgreSQL tables, that log all the changes to the chosen tables.

This triggers can log the query, that cause the change (via current_query()).

Using this as a base you can add more fields/information to log.

Ihor Romanchenko
  • 26,995
  • 8
  • 48
  • 44
1

You would do this to the actual postgres config files:

http://www.postgresql.org/docs/9.0/static/runtime-config-logging.html

log_statement (enum)

Controls which SQL statements are logged. Valid values are none (off), ddl, mod, and all (all statements). ddl logs all data

definition statements, such as CREATE, ALTER, and DROP statements. mod logs all ddl statements, plus data-modifying statements such as INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM. PREPARE, EXECUTE, and EXPLAIN ANALYZE statements are also logged if their contained command is of an appropriate type. For clients using extended query protocol, logging occurs when an Execute message is received, and values of the Bind parameters are included (with any embedded single-quote marks doubled).

The default is none. Only superusers can change this setting.

You want either ddl or all to be the selection. This is what you need to alter:
In your data/postgresql.conf file, change the log_statement setting to 'all'. Further the following may also need to be validated: 

1) make sure you have turned on the log_destination variable
2) make sure you turn on the logging_collector
3) also make sure that pg_log actually exists relative to your data directory, and that the postgres user can write to it.

taken from here

Community
  • 1
  • 1
Woot4Moo
  • 23,987
  • 16
  • 94
  • 151
  • 1
    how forgetful I am! yes, it's the right way.. I think I would only need to put it "log_statement=mod' though... – Hoàng Long Dec 27 '12 at 14:01