New DEV FAQ item

I have added more to the last item.

---------------------------------------------------------------------------

9) How do I efficiently access information in tables from the backend code?

You first need to find the tuples(rows) you are interested in. 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 versions 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 the cache entry may be flushed, so you should
use SearchSysCacheTupleCopy() in these cases, and pfree() the 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() returns 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 fetch rows by block number/offset.
While scans automatically lock/unlock rows from the buffer cache, with
heap_fetch(), you must pass a Buffer pointer, and ReleaseBuffer() it
when completed. Once you have the row, you can get data that is common
to all tuples, like t_ctid and t_oid, by mererly accessing the
HeapTuple structure entries. If you need a table-specific column, you
should take the HeapTuple pointer, and use the GETSTRUCT() macro to
access the table-specific start of the tuple. You then cast the
pointer as a Form_pg_proc pointer if you are accessing the pg_proc
table, or TypeTupleForm if you are accessing pg_type. You can then
access the columns by using a structure pointer:

((Form_pg_class) GETSTRUCT(tuple))-&gtrelnatts

You should not directly change live tuples in this way. The best way
is to use heap_tuplemodify() and pass it your palloc'ed tuple, and the
values you want changed. It returns another palloc'ed tuple, which you
pass to heap_replace(). You can delete tuples by passing the tuple's
t_ctid to heap_destroy(). Remember, tuples can be either system cache
versions, which may go away soon after you get them, buffer cache
version, which will go away when you heap_getnext(), heap_endscan, or
ReleaseBuffer(), in the heap_fetch() case. Or it may be a palloc'ed
tuple, that you must pfree() when finished.

--
Bruce Momjian | 830 Blythe Avenue
maillist(at)candle(dot)pha(dot)pa(dot)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)