Re: Extension Templates S03E11 - Mailing list pgsql-hackers

* Dimitri Fontaine (dimitri@2ndQuadrant.fr) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > *That doesn't make this approach the right one*.  If anything, I'm
> > afraid we've ended up building ourselves a rube goldberg machine because
> > of this constant struggle to fit a square peg into a round hole.
>
> This duplication you're talking about only applies to CREATE EXTENSION.
>
> I don't know of any ways to implement ALTER EXTENSION … UPDATE …
> behaviour without a separate set of scripts to apply in a certain order
> depending on the current and target versions of the extension.

We've already got it in the form of how filesystem extensions work
today..

> If you know how to enable a DBA to update a set of objects in a database
> only with information already found in the database, and in a way that
> this information is actually *not* an SQL script, I'm all ears.

Clearly we need the information from the extension package (the scripts
which are on the PG server's filesystem today, but need not be in the
future) but that doesn't mean we need to keep those text blobs in the
catalog.

> > That's basically what we already do with schemas today and hence is
> > pretty darn close to what I'm proposing.  Perhaps it'd be a way to
> > simply version schemas themselves- heck, with that, we could even
> > provide that oft-asked-for schema delta tool in-core by being able to
> > deduce the differences between schema at version X and schema at
> > version Y.
>
> Given that at any moment you have a single version of the schema
> installed, I don't know how you're supposed to be able to do that?

*I am not trying to rebuild the entire extension package from the PG
catalog*.  I do not see the need to do so either.  Perhaps that's
short-sighted of me, but I don't think so; to go back to my dpkg
example, we don't store the source package in dpkg's database nor do
people generally feel the need to rebuild .deb's from the files which
are out on the filesystem (a non-trivial task though I suppose it might
be possible to do- but not for *every version* of the package..).

> Maybe you mean by tracking the changes at update time? Well that at
> least would be a good incentive to have Command String access in event
> triggers, I guess.

I don't see the need to track the changes at all.  We don't actually
track them in the database anywhere today...  We happen to have scripts
available on the filesystem which allow us to move between versions, but
they're entirely outside the catalog and that's where they belong.

> > Having a management system for sets of objects is a *great* idea- and
> > one which we already have through schemas.  What we don't have is any
> > kind of versioning system built-in or other metadata about it, nor do we
> > have good tooling which leverages such a versioning or similar system.
>
> Exactly.
>
> How can we implement ALTER <OBJECT> … UPDATE TO <VERSION> without having
> access to some SQL scripts?
>
> The current patch offers a way to manage those scripts and apply them,
> with the idea that the people managing the scripts (extension authors)
> and the people applying them (DBAs) are not going to be the same people,
> and that it's then possible to have to apply more than a single script
> for a single UPDATE command.

Extension authors are not going to be issuing updates to everyone's
catalogs directly to update their templates..  That's still going to be
the DBA, or some tool the DBA runs, job.  I'm argueing that such a tool
could actually do a lot more and work outside of the PG backend but
communicate through libpq.  As I see it, you're trying to build that
tool *into* the backend and while 'extension templates' might end up
there, I don't think you're going to get your wish when it comes to
having a PG backend reach out over the internet at the request of a
normal user, ever.

> As soon as you question that, then you might come to realise the only
> difference in between file-system templates and catalog templates is our
> ability to deal with the problem, rather than the problem itself.

I really think there's a good deal more to my concerns than that. :)
Thanks,
    Stephen