Re: Extension table data - Mailing list pgsql-general

On Sat, Jun 9, 2012 at 5:33 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Keith Fiske <keith@omniti.com> writes:
>> Just found something else rather disturbing. If you try to exclude the
>> schema that the extension tables are in, their data is still output.
>
> This is a common misconception: extensions do not live within schemas.
> (An extension might own a schema, not the other way around.)  So a
> dump with a -n switch is never going to select an extension.
>
> By and large, if the current behavior bothers you, ISTM it probably
> means you are using these tables in a way other than what the concept of
> an extension configuration table was meant for: namely, to hold
> configuration data that would be referenced by the functions in that
> extension, but would not normally be considered part of the user's data.
> There has been some talk of trying to cater for a more general notion of
> tables created by extensions, but we do not have a design or even a
> clear idea of a set of requirements for that.  Perhaps it would be good
> if you explained what is your use-case --- why are you concerned about
> being able to manage these tables as if they were regular data?
>
Here's a use case I have been thinking a lot about lately.  I am not
sure that extensions is the right vehicle for it, but it may be a good
starting point.

LedgerSMB is moving towards a more modular structure and LedgerSMB 1.5
will probably require PostgreSQL 9.1 or higher (owing to the writable
CTE's).  I have been looking at how the extension system can be used
to simplify our maintenance and it helps quite a bit.  However, one
key aspect that would be really nice would be managing schema changes
along with changing versions of an extension.  For example, one might
have a fixed asset module, and that module might have a series of
stored procedures.  We would have some tables that store configuration
data regarding the module.  For example we might have a table that
stores info on stored procedures that track depreciation methods.  The
use of tables for configuration data in the current approach fits this
nicely.

However we might have other tables which store data, things like which
fixed assets get depreciated in which ways, and the like.  These may
need to have columns added from time to time, or have other alter
table operations performed.  It would be nice to be able to manage
these schema changes and upgrading the extension in the same
framework.

I am wondering if some sort of "database modules" framework might be
helpful with modules possibly having extensions, but also having data
tables.

Best Wishes,
Chris Travers