Re: pg_upgrade issues

> Alvaro Herrera <alvherre(at)commandprompt(dot)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).
>
> > 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.
>
> > Note that you can use TG_SCHEMANAME and TG_RELNAME in plpgsql
> triggers
> > anyway.
>
> 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 instruments. The data has a hierarchical structure. For instance, we have the following 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 different actions depending on the type of asset. One way to implement this would 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 tables turns out to be very inefficient in some situations. Some queries, such as joins, result in the materialization of a huge aggregate table, followed by an inefficient scan of that table.

An alternative implementation is to have a set of registry tables, which parallel 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 whenever 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 tables that are higher in the hierarchy. (This is where TG_RELID comes into play.) Thus, an American option is registered in the tables reg_asset_option_american, reg_asset_option and reg_asset. If I wanted to know whether an asset with a given ID is an option, I would only have to search the index of the reg_asset_option table. I can also write dynamic queries, using the table 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 it is just a single integer number, which takes up much less space and is much faster when performing comparisons than the qualified table name. And the reason regclass is a natural choice is that that is what trigger procedures make available through the TG_RELID special variable.

Regards,
Dmitry