Re: Extension table data

Along with Chris, the need for having tables defined in the extension
is about keeping control of the table structure so the extension
functions are guaranteed to work as intended and to make upgrading
versions easier. As an example, the fact that I can specifically name
constraints makes it easier to write an extension upgrade script if
that constraint needs to change because I know exactly what I called
it. Trying to support extensions and requiring that we write long,
explicit instructions for creating and maintaining the associated
tables is just asking for trouble. Especially when we can have the
control we need to avoid these issues.

Honestly, the big issue I have right now is that it is dumping data
with the schema-only option and only dumping that data if you do a
complete database dump. That is making it very difficult to manage
extensions even using them as they're documented now.

I think its time to recognize the extension system is more widely
usable than it was originally intended. And that's a good thing! We
just need to try to find ways to make the existing tools work in a
more predictable manner now.

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
443.325.1357 x251


On Sat, Jun 9, 2012 at 9:56 AM, Chris Travers <chris.travers@gmail.com> wrote:
> 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