Wednesday, December 28, 2011

Postgresql - System Administration Functions 

With postgresql you've got functions for :
  • Configuration Settings
  • Server Signalling
  • Backup Control
  • Recovery Information
  • Recovery Control
  • Database Object Size
  • Database Object Location
  • Generic File Access
  • Advisory Lock

The ones that I like the most:
pg_cancel_backend(pid int) Cancel a backend's current query
pg_reload_conf() Cause server processes to reload their configuration files
pg_is_in_recovery() True if recovery is still in progress.
pg_database_size(name) Disk space used by the database with the specified name
pg_indexes_size(regclass) Total disk space used by indexes attached to the specified table
pg_size_pretty(bigint) Converts a size in bytes into a human-readable format with size units
pg_table_size(regclass) Disk space used by the specified table, excluding indexes (but including TOAST, free space map, and visibility map)
pg_tablespace_size(oid) Disk space used by the tablespace with the specified OID
pg_tablespace_size(name) Disk space used by the tablespace with the specified name
pg_total_relation_size(regclass) Total disk space used by the specified table, including all indexes and TOAST data

One concept that caught my eye is "Advisory Lock"

Advisory locks are 'long-term, cooperative' locks. They are long-term in the sense that they do not follow mvcc rules, and are in fact completely orthogonal to transactional locks. This is a slightly long-winded way of saying that advisory locks are not released upon transaction commit/rollback and can be held outside of a transaction. This is noteworthy because advisory locks are one of the very few things that can be used to maintain session state without using persistent storage (prepared statements, temporary tables, and listen/notify also qualify here), and especially noteworthy because they are the only mechanism in PostgreSQL that I know that can affect the behavior of another session without consideration of either session's transactional state. Advisory locks are cooperative in that all users of them are on the honor system -- this is quite different from standard mvcc locks which follow very strict rules. Anything can be locked at any time. Advisory locks can be thought of as a low level interface to the internal locking engine and will generally need some scaffolding to make them useful.

I'll study them and I'll make some tests using them later.

No comments:

Post a Comment