Advanced Database Features | 115- When using Postgres large objects, you should use a trigger to delete the large
object when the corresponding record (containing the LOB’s OID) is deleted.
Consider this a form of referential integrity. - Extended or non-native types will use stored procedures for access. PostGIS, a
geospatial database for Postgres, uses functions to manage spatial data and
indexes. - The TSearch2 library, integrated into PostgreSQL 8.3 and later, uses functions to
access full-text indexing functions.
Some applications use stored procedures for all data access, in order to enforce
access control. This is definitely not the Rails way. Although it can be made to work,
it will be more difficult than directly accessing tables and views. Views provide suffi-
cient access control for most enterprise applications; only use stored procedures if
you have to. ActiveRecord can transparently use updateable views as if they were
concrete tables.
Examples
Large object deletion
Since PostgreSQL’s large objects are decoupled from their associated record, it is use-
ful to set up a simple rule to delete them when the corresponding record is deleted.
The rule can be implemented as follows:
-- (table name is 'attachments'; LOB OID is 'file_oid')CREATE RULE propagate_deletes_to_lob AS
ON DELETE TO attachments
DO ALSO SELECT lo_unlink(OLD.file_oid) AS lo_unlinkData partitioning
PostgreSQL has a very powerful rule system that can rewrite incoming queries in
many ways. One use for this rule system is to implement partitioning, where data
from one table is federated into one of several tables depending on some condition.
Consider a database of real estate listings. For historical purposes, we may want to
keep listings that have expired, been sold, or been removed from the system. How-
ever, most of the data being used on a day-to-day basis is derived from listings that
are current and for sale.
In addition, the datasets of “current listings” and “all listings” will have differing
data needs; the former is likely to be used transactionally while the latter is proba-
bly used analytically. It makes sense to store these separately, as they may have dif-
ferent characteristics.
First, we assume that we already have listing data in a table calledlistings, and it
has astatuscolumn representing the status of the listing. We create the two tables,