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 sufficient
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 useful
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_unlink
Data 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. However,
most of the data being used on a day-to-day basis is derived from listings that
are current and for sale.
Pages:
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183