Re: pg_upgrade issues - Mailing list pgsql-bugs

> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Excerpts from depstein's message of lun jul 26 08:05:24 -0400 2010:
> >> I just want to note that one reason regclass may be used in user
> tables (as opposed to, say, regtype) is that in PL/pgSQL trigger
> procedures there is a special variable TG_RELID, which provides a
> convenient reference to the table that pulled the trigger (this is the
> case for some of our uses).
>=20
> > I've wanted to use regclass (and regproc too, for that matter) in
> some
> > db designs, but I've refrained precisely because of the movability
> issues.
>=20
> > Note that you can use TG_SCHEMANAME and TG_RELNAME in plpgsql
> triggers
> > anyway.
>=20
> How does TG_RELID lead to wanting to store regclass columns, exactly?
> I've always supposed that was a legacy parameter rather than something
> anyone would actually use.

Here is one use case from our database. We store data for financial instrum=
ents. The data has a hierarchical structure. For instance, we have the foll=
owing tables:

asset
    asset_option
        asset_option_american
        asset_option_european

Suppose we have a procedure that takes a list of asset ID's and performs di=
fferent actions depending on the type of asset. One way to implement this w=
ould be through table inheritance (which we do have). In order to find the =
subset of assets that are options, we could search the table a_asset_option=
, from which all option tables are derived. However, working with parent ta=
bles turns out to be very inefficient in some situations. Some queries, suc=
h as joins, result in the materialization of a huge aggregate table, follow=
ed by an inefficient scan of that table.

An alternative implementation is to have a set of registry tables, which pa=
rallel the asset tables. Thus, we have the following tables:

reg_asset
    reg_asset_option
        reg_asset_option_american
        reg_asset_option_european

Each of these tables has two columns: one with an asset ID, the other is a =
regclass column that refers to an asset table. Triggers ensure that wheneve=
r a new asset is added to an asset table, its ID and table OID are added to=
 the registry table for that type of asset, as well as to all registry tabl=
es that are higher in the hierarchy. (This is where TG_RELID comes into pla=
y.) Thus, an American option is registered in the tables reg_asset_option_a=
merican, reg_asset_option and reg_asset. If I wanted to know whether an ass=
et with a given ID is an option, I would only have to search the index of t=
he reg_asset_option table. I can also write dynamic queries, using the tabl=
e OID field of the registry tables as a proxy for the table name.

What makes the table OID an attractive choice for registry tables is that i=
t is just a single integer number, which takes up much less space and is mu=
ch faster when performing comparisons than the qualified table name. And th=
e reason regclass is a natural choice is that that is what trigger procedur=
es make available through the TG_RELID special variable.

Regards,
Dmitry