Re: Add CREATE EXTENSION Support

On Fri, Jan 17, 2014 at 6:50 PM, David E. Wheeler <david(at)justatheory(dot)com> wrote:
> On Jan 16, 2014, at 5:39 PM, David E. Wheeler <david(at)justatheory(dot)com> wrote:
>
>> I hope to finish it up tomorrow. One quick question now, though: Do end users ever add records to pga_jobclass? Or are those five records supposed to be set in stone?
>
> I changed it to exclude the five default classes by name rather than ID, which should be cleaner.

Those are really just defaults - users may well want to edit them to
their own requirements.

> Attached is a patch implementing $subject. It's also viewable here:
>
> https://github.com/theory/pgagent/compare/extension
>
> I've also attached a patch to the pgAdminIII docs, viewable here:
>
> https://github.com/theory/pgadmin3/compare/pgagent-extension

There's a missing word in there:

+If the server *is* 9.1 or later....

> Please let me know how these look, of if you’d like any changes. The approach I took, BTW, was:
>
> * Move the *.sql files to a sql/ directory. This is so that upgrade scripts can be created there in the future, too, without cluttering up the root of the distribution.
>
> * Add a cmake script to copy sql/pgagent.sql to pgagent--${VERSION}.sql, remove the BEGIN, COMMIT, and CREATE schema lines, and uncomment lines that call pg_catalog.pg_extension_config_dump() to allow pgAgent table rows to be dumped. The script also replaces ${VERSION} in the control file.
>
> * If Postgres supports extensions, add a target to call that script, and install commands for pgagent--${VERSION}.sql, pgagent.control, and sql/*--*.sql in the server share/extensions directory.
>
> * I also added sql/pgagent--unpackaged--3.3.0.sql so one can create the extension from unpackaged, which is useful for existing installations. This file should be renamed to whatever the release version is that ships with this patch.

Looks like a good approach to me. I haven't tested mind you - just
eyeballed it at this stage.

> I think this is a relatively clean way to go. I'm wondering, though, whether the extension will be needed on PostgreSQL servers where pgAgent itself isn't installed. Might it be worthwhile to also release an extension that just contains the SQL stuff and not pgAgent, so that it can be deployed to any server on which one might need to run CREATE EXTENSION pgagent?

I think that's such a narrow use case, it's probably not worth doing.
You're more likely to have things the other way round - multiple
servers running the agent, all using a single database, which likely
also has a local agent instance.

Let me know when you're happy with the code and then I'll do a more
complete review with a view to committing.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company