Doing something at commit/rollback

As of this writing, there is no possibility to define a trigger function which is executed ON COMMIT or ON ROLLBACK. However, if you really need to have some code executed on these database events, you have a possibility to register a C-language function to be called on these events. Unfortunately, this registration cannot be done in a permanent way like triggers, but the registration function has to be called each time a new connection starts using.

RegisterXactCallback(my_xact_callback, NULL); 

Use grep -r RegisterXactCallback in the contrib/ directory of PostgreSQL's source code to find files with examples of actual callback functions.