Обсуждение: Add CREATE EXTENSION Support
PGAdmin Hackers, I’d like to add code to pgAgent so that its schema could be installed via a CREATE EXTENSION statement. This would greatlysimplify deployment for us. In private correspondence, Dave Page suggested that CMake be updated to handle it. Ata glance, I think I'd have to move things around a bit, perhaps create an SQL directory, and have CMake concatenate files(or wrap them in BEGIN/COMMIT statements) to generate the old file. Does that make sense? Also, is there access to PGXS during the build? I'm pretty familiar with how PostgreSQL extension Makefiles should work,but not the configure/CMake stuff in pgAdmin. Pointers would be appreciated. Thanks, David
On Sat, Jan 11, 2014 at 10:01 PM, David E. Wheeler <david@justatheory.com> wrote: > PGAdmin Hackers, > > I’d like to add code to pgAgent so that its schema could be installed via a CREATE EXTENSION statement. This would greatlysimplify deployment for us. In private correspondence, Dave Page suggested that CMake be updated to handle it. Ata glance, I think I'd have to move things around a bit, perhaps create an SQL directory, and have CMake concatenate files(or wrap them in BEGIN/COMMIT statements) to generate the old file. Does that make sense? Yes. > Also, is there access to PGXS during the build? I'm pretty familiar with how PostgreSQL extension Makefiles should work,but not the configure/CMake stuff in pgAdmin. Pointers would be appreciated. PGXS cannot be used - it doesn't support Windows. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Jan 13, 2014, at 1:30 AM, Dave Page <dpage@pgadmin.org> wrote: > PGXS cannot be used - it doesn't support Windows. Okay. What about pg_config or something equivalent? I think I just need to know where to put the *.sql and .control files. David
On Mon, Jan 13, 2014 at 5:13 PM, David E. Wheeler <david@justatheory.com> wrote: > On Jan 13, 2014, at 1:30 AM, Dave Page <dpage@pgadmin.org> wrote: > >> PGXS cannot be used - it doesn't support Windows. > > Okay. What about pg_config or something equivalent? I think I just need to know where to put the *.sql and .control files. The FindPG cmake module in cmake/ already uses pg_config, so you're fine there. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Jan 13, 2014, at 9:29 AM, Dave Page <dpage@pgadmin.org> wrote: > The FindPG cmake module in cmake/ already uses pg_config, so you're fine there. Thanks. I’ve been working on it the last couple of days over here: https://github.com/theory/pgagent/compare/extension I hope to finish it up tomorrow. One quick question now, though: Do end users ever add records to pga_jobclass? Or are thosefive records supposed to be set in stone? Thanks, David
On Jan 16, 2014, at 5:39 PM, David E. Wheeler <david@justatheory.com> wrote: > I hope to finish it up tomorrow. One quick question now, though: Do end users ever add records to pga_jobclass? Or arethose 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. 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 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, withoutcluttering 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 scriptalso 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 existinginstallations. This file should be renamed to whatever the release version is that ships with this patch. I think this is a relatively clean way to go. I'm wondering, though, whether the extension will be needed on PostgreSQL serverswhere pgAgent itself isn't installed. Might it be worthwhile to also release an extension that just contains the SQLstuff and not pgAgent, so that it can be deployed to any server on which one might need to run CREATE EXTENSION pgagent? Best, David
Вложения
On Jan 17, 2014, at 10:50 AM, David E. Wheeler <david@justatheory.com> wrote: > I think this is a relatively clean way to go. I'm wondering, though, whether the extension will be needed on PostgreSQLservers where pgAgent itself isn't installed. Might it be worthwhile to also release an extension that just containsthe SQL stuff and not pgAgent, so that it can be deployed to any server on which one might need to run CREATE EXTENSIONpgagent? Just nagging here. I didn’t miss a reply, did I? I would like to get any issues worked out soon so I can rejigger our internalRPM to have CREATE EXTENSION backported -- unless an updated version of PGAgent is immanent. Thanks, David
On Tue, Jan 28, 2014 at 8:52 PM, David E. Wheeler <david@justatheory.com> wrote: > On Jan 17, 2014, at 10:50 AM, David E. Wheeler <david@justatheory.com> wrote: > >> I think this is a relatively clean way to go. I'm wondering, though, whether the extension will be needed on PostgreSQLservers where pgAgent itself isn't installed. Might it be worthwhile to also release an extension that just containsthe SQL stuff and not pgAgent, so that it can be deployed to any server on which one might need to run CREATE EXTENSIONpgagent? > > Just nagging here. I didn’t miss a reply, did I? I would like to get any issues worked out soon so I can rejigger our internalRPM to have CREATE EXTENSION backported -- unless an updated version of PGAgent is immanent. No, sorry - looks like I missed your last email. I'll try to look today, but may not be able to as I'm pretty busy. Otherwise it'll probably be next week I'm afraid, due to FOSDEM. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Jan 29, 2014, at 12:30 AM, Dave Page <dpage@pgadmin.org> wrote: > No, sorry - looks like I missed your last email. I'll try to look > today, but may not be able to as I'm pretty busy. Otherwise it'll > probably be next week I'm afraid, due to FOSDEM. Got it, thanks! David
On Fri, Jan 17, 2014 at 6:50 PM, David E. Wheeler <david@justatheory.com> wrote: > On Jan 16, 2014, at 5:39 PM, David E. Wheeler <david@justatheory.com> wrote: > >> I hope to finish it up tomorrow. One quick question now, though: Do end users ever add records to pga_jobclass? Or arethose 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, withoutcluttering 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 scriptalso 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 forexisting 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 PostgreSQLservers where pgAgent itself isn't installed. Might it be worthwhile to also release an extension that just containsthe SQL stuff and not pgAgent, so that it can be deployed to any server on which one might need to run CREATE EXTENSIONpgagent? 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
On Jan 30, 2014, at 5:52 AM, Dave Page <dpage@pgadmin.org> wrote: >> 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. Then I think this is the correct approach. We just want to prevent it from dumping rows that will be created by CREATE EXTENSION.Even if they rename them, if they dump the database, then load into a new one, there will be no conflicts (althoughthey might end up with more rows than they started with before the dump). > There's a missing word in there: > > +If the server *is* 9.1 or later.... Fixed, thanks. > 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. Okay. > Let me know when you're happy with the code and then I'll do a more > complete review with a view to committing. I'm happy with it now. :-) Best, David
Thanks - patches applied, with minor typo fixes. On Thu, Jan 30, 2014 at 5:34 PM, David E. Wheeler <david@justatheory.com> wrote: > On Jan 30, 2014, at 5:52 AM, Dave Page <dpage@pgadmin.org> wrote: > >>> 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. > > Then I think this is the correct approach. We just want to prevent it from dumping rows that will be created by CREATEEXTENSION. Even if they rename them, if they dump the database, then load into a new one, there will be no conflicts(although they might end up with more rows than they started with before the dump). > >> There's a missing word in there: >> >> +If the server *is* 9.1 or later.... > > Fixed, thanks. > >> 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. > > Okay. > >> Let me know when you're happy with the code and then I'll do a more >> complete review with a view to committing. > > I'm happy with it now. :-) > > Best, > > David > > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Feb 11, 2014, at 6:42 AM, Dave Page <dpage@pgadmin.org> wrote: > Thanks - patches applied, with minor typo fixes. Great, thanks. Will this be released as 3.3.1 rather than 3.3.0? If so, sql/pgagent--unpackaged--3.3.0.sql should be renamed. Also, I need to get our internal RPM updated. Do you think a new version will be released soon? If not, I can add a patchto our current RPM. Thanks, David
On Tue, Feb 11, 2014 at 5:07 PM, David E. Wheeler <david@justatheory.com> wrote: > On Feb 11, 2014, at 6:42 AM, Dave Page <dpage@pgadmin.org> wrote: > >> Thanks - patches applied, with minor typo fixes. > > Great, thanks. > > Will this be released as 3.3.1 rather than 3.3.0? If so, sql/pgagent--unpackaged--3.3.0.sql should be renamed. No, it's already been bumped to 3.4.0. > Also, I need to get our internal RPM updated. Do you think a new version will be released soon? If not, I can add a patchto our current RPM. I guess I might have to - there's another fix in there that Regina Obe was asking for the other day. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Feb 11, 2014, at 9:09 AM, Dave Page <dpage@pgadmin.org> wrote: > I guess I might have to - there's another fix in there that Regina Obe > was asking for the other day. Great thank you. Suggested quick patch: add the ".0" to the version comment in sql/pgagent--unpackaged--3.4.0.sql: --- a/sql/pgagent--unpackaged--3.4.0.sql +++ b/sql/pgagent--unpackaged--3.4.0.sql @@ -4,7 +4,7 @@ // Copyright (C) 2002 - 2014 The pgAdmin Development Team // This software is released under the PostgreSQL Licence // -// pgagent--unpackaged--3.4.sql - Convert pgAgent existing tables and functions to an extension +// pgagent--unpackaged--3.4.0.sql - Convert pgAgent existing tables and functions to an extension // */ Best, DAvid
On Tue, Feb 11, 2014 at 5:14 PM, David E. Wheeler <david@justatheory.com> wrote: > On Feb 11, 2014, at 9:09 AM, Dave Page <dpage@pgadmin.org> wrote: > >> I guess I might have to - there's another fix in there that Regina Obe >> was asking for the other day. > > Great thank you. Suggested quick patch: add the ".0" to the version comment in sql/pgagent--unpackaged--3.4.0.sql: > > --- a/sql/pgagent--unpackaged--3.4.0.sql > +++ b/sql/pgagent--unpackaged--3.4.0.sql > @@ -4,7 +4,7 @@ > // Copyright (C) 2002 - 2014 The pgAdmin Development Team > // This software is released under the PostgreSQL Licence > // > -// pgagent--unpackaged--3.4.sql - Convert pgAgent existing tables and functions to an extension > +// pgagent--unpackaged--3.4.0.sql - Convert pgAgent existing tables and functions to an extension > // > */ I actually removed the .0 because there aren't supposed to be any schema changes in a point release (in fact, there aren't even supposed to be in anything but a major release). I thought I removed it from the filename as well, but clearly missed that in the end. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Feb 11, 2014, at 9:16 AM, Dave Page <dpage@pgadmin.org> wrote: > I actually removed the .0 because there aren't supposed to be any > schema changes in a point release (in fact, there aren't even supposed > to be in anything but a major release). I thought I removed it from > the filename as well, but clearly missed that in the end. I don't understand. I would think that versions should always be consistently formatted. David
On Tue, Feb 11, 2014 at 6:50 PM, David E. Wheeler <david@justatheory.com> wrote: > On Feb 11, 2014, at 9:16 AM, Dave Page <dpage@pgadmin.org> wrote: > >> I actually removed the .0 because there aren't supposed to be any >> schema changes in a point release (in fact, there aren't even supposed >> to be in anything but a major release). I thought I removed it from >> the filename as well, but clearly missed that in the end. > > I don't understand. I would think that versions should always be consistently formatted. Much as PostgreSQL only changes catalogs and on disk format in major releases, pgAgent only changes its schema in X releases (in the X.Y.Z numbering scheme). My point is that there is no need to have upgrade scripts from 3.3.0 - 3.4.0 or even 3.3 - 3.4 because there will never be any such changes, and using that numbering is likely to confuse users because the existence of a 3.4.0 script implies that there also should be one for 3.4.1 or 3.5.0 in the future, when in actual fact, there will only be one needed for 4(.x.x). Therefore, I believe the correct option is actually to have "pgagent--unpackaged--3.sql" and later "pgagent--3--4.sql" for upgrades. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Feb 12, 2014, at 2:21 AM, Dave Page <dpage@pgadmin.org> wrote: > Much as PostgreSQL only changes catalogs and on disk format in major > releases, pgAgent only changes its schema in X releases (in the X.Y.Z > numbering scheme). My point is that there is no need to have upgrade > scripts from 3.3.0 - 3.4.0 or even 3.3 - 3.4 because there will never > be any such changes, and using that numbering is likely to confuse > users because the existence of a 3.4.0 script implies that there also > should be one for 3.4.1 or 3.5.0 in the future, when in actual fact, > there will only be one needed for 4(.x.x). > > Therefore, I believe the correct option is actually to have > "pgagent--unpackaged--3.sql" and later "pgagent--3--4.sql" for > upgrades. Yeah, but if the version is 3.4.0, and that's what's in pgagent.spec, then Postgres will never find the upgrade script ifit’s not named "pgagent--unpackaged--3.4.0.sql". The name in the file must be the same as in the spec file. Best, David
On Wed, Feb 12, 2014 at 5:20 PM, David E. Wheeler <david@justatheory.com> wrote: > On Feb 12, 2014, at 2:21 AM, Dave Page <dpage@pgadmin.org> wrote: > >> Much as PostgreSQL only changes catalogs and on disk format in major >> releases, pgAgent only changes its schema in X releases (in the X.Y.Z >> numbering scheme). My point is that there is no need to have upgrade >> scripts from 3.3.0 - 3.4.0 or even 3.3 - 3.4 because there will never >> be any such changes, and using that numbering is likely to confuse >> users because the existence of a 3.4.0 script implies that there also >> should be one for 3.4.1 or 3.5.0 in the future, when in actual fact, >> there will only be one needed for 4(.x.x). >> >> Therefore, I believe the correct option is actually to have >> "pgagent--unpackaged--3.sql" and later "pgagent--3--4.sql" for >> upgrades. > > Yeah, but if the version is 3.4.0, and that's what's in pgagent.spec, then Postgres will never find the upgrade scriptif it’s not named "pgagent--unpackaged--3.4.0.sql". The name in the file must be the same as in the spec file. That's what I didn't look into yet - and really just implies that the spec (by which I assume you mean the control file) should only have "3" as the version number doesn't it? -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Feb 13, 2014, at 2:34 AM, Dave Page <dpage@pgadmin.org> wrote: > That's what I didn't look into yet - and really just implies that the > spec (by which I assume you mean the control file) should only have > "3" as the version number doesn't it? Yes, that would work, too. I personally like to always use the same number of digits everywhere I reference a version, butI guess that’s personal preference. Thanks, David
On Feb 11, 2014, at 9:09 AM, Dave Page <dpage@pgadmin.org> wrote: >> Also, I need to get our internal RPM updated. Do you think a new version will be released soon? If not, I can add a patchto our current RPM. > > I guess I might have to - there's another fix in there that Regina Obe > was asking for the other day. Hey Dave, do you have an ETA for 3.4.0? Thanks, David
On Tue, Feb 25, 2014 at 5:35 PM, David E. Wheeler <david@justatheory.com> wrote: > On Feb 11, 2014, at 9:09 AM, Dave Page <dpage@pgadmin.org> wrote: > >>> Also, I need to get our internal RPM updated. Do you think a new version will be released soon? If not, I can add a patchto our current RPM. >> >> I guess I might have to - there's another fix in there that Regina Obe >> was asking for the other day. > > Hey Dave, do you have an ETA for 3.4.0? Hoping to find time this week... -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Compan
On Feb 25, 2014, at 12:22 PM, Dave Page <dpage@pgadmin.org> wrote: >> Hey Dave, do you have an ETA for 3.4.0? > > Hoping to find time this week... Excellent, thank you! D
On Tue, Feb 25, 2014 at 9:23 PM, David E. Wheeler <david@justatheory.com> wrote: > On Feb 25, 2014, at 12:22 PM, Dave Page <dpage@pgadmin.org> wrote: > >>> Hey Dave, do you have an ETA for 3.4.0? >> >> Hoping to find time this week... > > Excellent, thank you! OK, so I think I got things as I want them. I tweaked the extension code so we use X.Y as the version number - see the commit message for the rationale: http://git.postgresql.org/gitweb/?p=pgagent.git;a=commitdiff;h=82dff599296159f8fb09f955e716b4e1cc5da476 I've created a test tarball for the release - can you take a look and make sure I didn't break anything please? http://developer.pgadmin.org/~dpage/pgAgent-3.4.0-Source.tar.gz Thanks! -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Compan
On Feb 25, 2014, at 2:11 PM, Dave Page <dpage@pgadmin.org> wrote: > I've created a test tarball for the release - can you take a look and > make sure I didn't break anything please? > > http://developer.pgadmin.org/~dpage/pgAgent-3.4.0-Source.tar.gz Looks great, I was able to update our RPM spec pretty easily. Thanks! David
On Thu, Feb 27, 2014 at 7:12 PM, David E. Wheeler <david@justatheory.com> wrote: > On Feb 25, 2014, at 2:11 PM, Dave Page <dpage@pgadmin.org> wrote: > >> I've created a test tarball for the release - can you take a look and >> make sure I didn't break anything please? >> >> http://developer.pgadmin.org/~dpage/pgAgent-3.4.0-Source.tar.gz > > Looks great, I was able to update our RPM spec pretty easily. > > Thanks! Thanks - I've pushed it to the FTP site; it should all sync up within an hour or so. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Feb 27, 2014, at 12:17 PM, Dave Page <dpage@pgadmin.org> wrote: > Thanks - I've pushed it to the FTP site; it should all sync up within > an hour or so. Awesome, thank you! David