I have added this to the developer's FAQ. Any comments or corrections?
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
9) How do I efficiently access information in tables from the backend code?
There are two ways. First, SearchSysCacheTuple() and related functions allow
you to query the system catalogs. This is the preferred way to access system
tables, because the first call to the cache loads the needed rows, and
future requests can return the results without accessing the base table.
Some of the caches use system table indexes to look up tuples. A list of
available caches is located in src/backend/utils/cache/syscache.c.
src/backend/utils/cache/lsyscache.c contains many column-specific cache
lookup functions.
The rows returned are cached-owned copies of the heap rows. They are
invalidated when the base table changes. Because the cache is local to each
backend, you may use the pointer returned from the cache for short periods
without making a copy of the tuple. If you send the pointer into a large
function that will be doing its own cache lookups, it is possible your entry
may be flushed, so you should use SearchSysCacheTupleCopy() in these cases,
and pfree() the resulting tuple when you are done.
If you can't use the system cache, you will need to retrieve the data
directly from the heap table, using the buffer cache that is shared by all
backends. The backend automatically takes care of loading the rows into the
buffer cache.
Open the table with heap_open(). You can then start a table scan with
heap_beginscan(), then use heap_getnext() and continue as long as
HeapTupleIsValid is true. Then do a heap_endscan(). Keys can be assigned to
the scan. No indexes are used, so all rows are going to be compared to the
keys, and only the valid rows returned.
You can also use heap_fetch() to sequentially fetch rows from the table.
Scans automatically lock/unlock rows from the buffer cache, so when using
heap_fetch(), you must retrieve the Buffer pointer, and ReleaseBuffer it
when completed.