- Query access - SELECT, COPY TO including views and SELECT rules
- Cursor commands - DECLARE, FETCH, CLOSE
- Parameters - SHOW, SET, RESET
- Transaction management commands
- BEGIN, END, ABORT, START TRANSACTION
- SAVEPOINT, RELEASE, ROLLBACK TO SAVEPOINT
- EXCEPTION blocks and other internal subtransactions
- LOCK TABLE, though only when explicitly in one of these modes: ACCESS SHARE, ROW SHARE or ROW EXCLUSIVE.
- Plans and resources - PREPARE, EXECUTE, DEALLOCATE, DISCARD
- Plugins and extensions - LOAD
pgpool-II 3.0 (yes, that will be the new version number) will give you small help. Pgpool parses the query and automatically redirect it to the standby server if needed. If you have more than one standby servers, pgpool will choose one of them and send the query to it. The queries not allowed for the standby server, will be sent to the primary server. In short, you do not need to worry about the complex rule above if you use pgpool-II 3.0 + Hot Standby.
Please note that there is a limitation here. Pgpool parses the query by using "raw parser" which understands the syntax of the query but does not understand the "depth psychology" aspect of the query. For example, if a SELECT calls a custom C function which modifies the database, pgpool will send it to the standby server because it does not know that the SELECT actually does write.
It will be great that PostgeSQL's function does have a signature that confess it is going to write...