Re: [HACKERS] Dumping an Extension's Script
On 20.11.2012 21:25, Simon Riggs wrote: On 19 November 2012 16:25, Robert Haasrobertmh...@gmail.com wrote: Beyond that, I think much of the appeal of the extension feature is that it dumps as CREATE EXTENSION hstore; and nothing more. That allows you to migrate a dump between systems with different but compatible versions of the hstore and have things work as intended. I'm not opposed to the idea of being able to make extensions without files on disk work ... but I consider it a niche use case; the behavior we have right now works well for me and hopefully for others most of the time. Distributing software should only happen by files? So why does Stackbuilder exist on the Windows binary? Why does yum exist? What's wrong with ftp huh? Why does CPAN? I've a feeling this case might be a sensible way forwards, not a niche at all. I have to join Robert in scratching my head over this. I don't understand what the use case is. Can you explain? I don't understand the comparison with stackbuilder, yum, ftp and CPAN. CPAN seems close to pgxn, but what does that have to do with this patch? On 20.11.2012 11:08, Dimitri Fontaine wrote: Apparently I'm not the only one doing extensions without anything to compile, all SQL: http://keithf4.com/extension_tips_3 No doubt about that. I'm sure extensions written in pure SQL or PL/pgSQL are very common. But what does that have to do with this patch? - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Heikki Linnakangas hlinnakan...@vmware.com writes: No doubt about that. I'm sure extensions written in pure SQL or PL/pgSQL are very common. But what does that have to do with this patch? This patch is all about enabling users to create extension without having to ship them as root on the file system of the database(s) server(s) first. When you're having to code your extension in C, you know you're in for shipping an executable binary (.so, .dylib or .dll), and for security reasons it's well understood that you will have to get root privileges on the server's file system to ship your binary before to be able to ask PostgreSQL to please load it and execute the code in there. When you can code your extension using dynamic code such as SQL or PL/pgSQL, PL/pythonu or PL/perl, there's absolutely no good reason to have to do the ship on the server's file system first that I can see. Technically creating an extension inline (sending its definition in the CREATE EXTENSION query itself) solves the problem of having to access the server's file system as root. Then, next pg_dump will include CREATE EXTENSION foo; as usual and at pg_restore time that access files on the file systems. But maybe you still are not granted access to the server's file system as root on the pg_restore target, right? So now you need to be able to include the extension's script into the dump. Now, we don't want to have more than one kind of extensions. That's what we learnt all together when reviewing my proposal from last year. Having more than one way to ship an extension is good, having two different animals with two different incompatible behaviors named the same thing is bad. The solution we found is then to be able to include an extension's script into pg_dump's output, and that's what my current patch implements, per last year review. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On 05.12.2012 12:22, Dimitri Fontaine wrote: Heikki Linnakangashlinnakan...@vmware.com writes: No doubt about that. I'm sure extensions written in pure SQL or PL/pgSQL are very common. But what does that have to do with this patch? This patch is all about enabling users to create extension without having to ship them as root on the file system of the database(s) server(s) first. ... When you can code your extension using dynamic code such as SQL or PL/pgSQL, PL/pythonu or PL/perl, there's absolutely no good reason to have to do the ship on the server's file system first that I can see. Technically creating an extension inline (sending its definition in the CREATE EXTENSION query itself) solves the problem of having to access the server's file system as root. Ok, I'm with you this far. Then, next pg_dump will include CREATE EXTENSION foo; as usual and at pg_restore time that access files on the file systems. But maybe you still are not granted access to the server's file system as root on the pg_restore target, right? So now you need to be able to include the extension's script into the dump. Now you lost me. I can see the need to install an extension without access to the filesystem - but it does not follow that you need to be able to dump an extension script. In general, I think you're confusing three things: 1. The way an extension is deployed. It could be by copying the files to the file system, by sending them over libpq, or shipped in .rpms by the OS, or something else. 2. The way an extension's files are laid out before it's deployed. Typically, you want to keep an extension's source code (whether it's C or SQL or plpython) in a version control system. 3. Being able to deploy extensions to the server without superuser or root access I think it would make this discussion a lot clearer if we keep those concerns separate. It's useful to have a mechanism to deploy an extension over libpq. It's not clear to me if you're envisioning to change 2. I don't think we should; having a .sql file and a .control file seems perfectly fine to me. I'd suggest that we just need a way to upload an extension to the server via libpq. Something like UPLOAD EXTENSION foo, which goes into COPY mode and you can stream over a zip file containing the .sql and .control file that make up the extension. The server would unzip the file into the right directory. Now, point 3 is yet another issue. If you need to copy the extension files to /usr/share/, you need root (or similar) access on the filesystem. We could allow extensions to be located somewhere in the data directory instead. Like $PGDATA/extensions. But again, that would be an independent change from 1 and 2. And I still don't understand why pg_dump needs to know about any of this... - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On 2012-12-05 19:13:10 +0200, Heikki Linnakangas wrote: On 05.12.2012 12:22, Dimitri Fontaine wrote: Heikki Linnakangashlinnakan...@vmware.com writes: No doubt about that. I'm sure extensions written in pure SQL or PL/pgSQL are very common. But what does that have to do with this patch? This patch is all about enabling users to create extension without having to ship them as root on the file system of the database(s) server(s) first. ... When you can code your extension using dynamic code such as SQL or PL/pgSQL, PL/pythonu or PL/perl, there's absolutely no good reason to have to do the ship on the server's file system first that I can see. Technically creating an extension inline (sending its definition in the CREATE EXTENSION query itself) solves the problem of having to access the server's file system as root. Ok, I'm with you this far. Then, next pg_dump will include CREATE EXTENSION foo; as usual and at pg_restore time that access files on the file systems. But maybe you still are not granted access to the server's file system as root on the pg_restore target, right? So now you need to be able to include the extension's script into the dump. Now you lost me. I can see the need to install an extension without access to the filesystem - but it does not follow that you need to be able to dump an extension script. In general, I think you're confusing three things: 1. The way an extension is deployed. It could be by copying the files to the file system, by sending them over libpq, or shipped in .rpms by the OS, or something else. 2. The way an extension's files are laid out before it's deployed. Typically, you want to keep an extension's source code (whether it's C or SQL or plpython) in a version control system. 3. Being able to deploy extensions to the server without superuser or root access I think it would make this discussion a lot clearer if we keep those concerns separate. It's useful to have a mechanism to deploy an extension over libpq. It's not clear to me if you're envisioning to change 2. I don't think we should; having a .sql file and a .control file seems perfectly fine to me. I'd suggest that we just need a way to upload an extension to the server via libpq. Something like UPLOAD EXTENSION foo, which goes into COPY mode and you can stream over a zip file containing the .sql and .control file that make up the extension. The server would unzip the file into the right directory. Not sure what is better here. Dimitri's way seems to be easier to manage for people who maintain their database in update scripts and such and your's seems to be a bit simpler from the backend perspective. Now, point 3 is yet another issue. If you need to copy the extension files to /usr/share/, you need root (or similar) access on the filesystem. We could allow extensions to be located somewhere in the data directory instead. Like $PGDATA/extensions. But again, that would be an independent change from 1 and 2. I think installing them into some global space is not a sensible interim-step. Having a UPLOAD EXTENSION in one database affect all other databases or even clusters (because you e.g. updated the version) would be really confusing. Which leads to: And I still don't understand why pg_dump needs to know about any of this... Extensions should be fully per-database and we want pg_dump backups to be restorable into another database/clusters/servers. So having a mode for pg_dump that actually makes dumps that are usable for recovering after a disaster seems sensible to me. Otherwise you need to redeploy from the VCS or whatever, which isn't really what you want when restoring a database backup. Comparing the situation to the one where you have extensions provided by the packaging system or by /contrib or whatever doesn't seem to be all that valid to me. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On 05.12.2012 19:27, Andres Freund wrote: And I still don't understand why pg_dump needs to know about any of this... Extensions should be fully per-database and we want pg_dump backups to be restorable into another database/clusters/servers. So having a mode for pg_dump that actually makes dumps that are usable for recovering after a disaster seems sensible to me. Otherwise you need to redeploy from the VCS or whatever, which isn't really what you want when restoring a database backup. Ok - but that it yet another issue, not to be confused with how you deploy extensions. If we are to have such a mode in pg_dump, it should be able to dump *all* extensions, regardless of how they were deployed. (ok, might be difficult for extensions that include .so files or similar, but certainly for an extension that only contains a .sql file and a .control file, it shouldn't matter how it was deployed). And whether extension control files (or the same information stored in a table or wherever) should be per-database or per cluster - that's *yet* another separate issue. You could argue for either behavior. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On 2012-12-05 19:40:58 +0200, Heikki Linnakangas wrote: On 05.12.2012 19:27, Andres Freund wrote: And I still don't understand why pg_dump needs to know about any of this... Extensions should be fully per-database and we want pg_dump backups to be restorable into another database/clusters/servers. So having a mode for pg_dump that actually makes dumps that are usable for recovering after a disaster seems sensible to me. Otherwise you need to redeploy from the VCS or whatever, which isn't really what you want when restoring a database backup. Ok - but that it yet another issue, not to be confused with how you deploy extensions. If we are to have such a mode in pg_dump, it should be able to dump *all* extensions, regardless of how they were deployed. (ok, might be difficult for extensions that include .so files or similar, but certainly for an extension that only contains a .sql file and a .control file, it shouldn't matter how it was deployed). For me it seems pretty natural to support dumping extension the way they got created. I.e. a plain CREATE EXTENSION ...; if the extension was preinstalled and some form that includes the extension source if you installed it via the connection. Extensions that were installed in some global manner *should* not be dumped with ones installed over the connection. E.g. dumping /contrib or packaged modules seems to be a bad idea to me. That would possibly be useful as a debugging tool, but I don't see much point besides that. And whether extension control files (or the same information stored in a table or wherever) should be per-database or per cluster - that's *yet* another separate issue. You could argue for either behavior. What would be the case for the per-cluster in the case of uploaded extensions? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Heikki Linnakangas hlinnakan...@vmware.com writes: Ok - but that it yet another issue, not to be confused with how you deploy extensions. If we are to have such a mode in pg_dump, it should be able to dump *all* extensions, regardless of how they were deployed. (ok, might be difficult for extensions that include .so files or similar, but certainly for an extension that only contains a .sql file and a .control file, it shouldn't matter how it was deployed). That's what you have in the current patch. Try = create extension 'hstore'; $ pg_dump --extension-script hstore It works as far as the script is concerned, and the control file is not needed any more because the script as dumped does not need it, except for the two parameters 'require' and 'relocatable', that are added in the SQL command. The binary file is not taken care of by this mechanism. Remember that in most cases pg_restore will not be granted to deploy it at the right place anyway, for security reasons. And whether extension control files (or the same information stored in a table or wherever) should be per-database or per cluster - that's *yet* another separate issue. You could argue for either behavior. At the SQL level, extensions do live in a database. The only reason why we currently have them on the file system is binary executables (.so, .dylib, .dll). And those are not per database, not even per cluster, not even per major version, they are *per server*. It's something that makes me very sad, and that I want to have the chance to fix later, but that won't happen in 9.3, and certainly not in that very patch… Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Andres Freund and...@2ndquadrant.com writes: On 2012-12-05 19:13:10 +0200, Heikki Linnakangas wrote: And I still don't understand why pg_dump needs to know about any of this... Extensions should be fully per-database and we want pg_dump backups to be restorable into another database/clusters/servers. Wait a minute. I haven't bought into either of those statements, and most particularly not the first one. Upthread, Dimitri claimed that he wasn't creating two different kinds of extensions with this patch, but the more I read about it the more it seems that he *is* making a fundamentally different kind of animal. And I don't think it's necessarily a good idea, especially not if we still call it an extension. I kind of like Heikki's idea of leaving CREATE EXTENSION alone and inventing a separate UPLOAD EXTENSION operation, but there's a problem with that: in many, probably most, installations, the server does not and should not have permission to scribble on the directories where the extension scripts are stored. Possibly we could circumvent that by creating an auxiliary extensions directory under $PGDATA. (But then it starts to seem like pg_dumpall --- not pg_dump --- ought to include those files in its output...) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Heikki Linnakangas hlinnakan...@vmware.com writes: And whether extension control files (or the same information stored in a table or wherever) should be per-database or per cluster - that's *yet* another separate issue. You could argue for either behavior. I think anyone arguing for the former is confusing an installed extension with a not-installed one. Maybe it would help if we adopted different terminologies. Perhaps call the control+sql files a template, while using extension for the installed entity? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On 2012-12-05 12:55:42 -0500, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2012-12-05 19:13:10 +0200, Heikki Linnakangas wrote: And I still don't understand why pg_dump needs to know about any of this... Extensions should be fully per-database and we want pg_dump backups to be restorable into another database/clusters/servers. Wait a minute. I haven't bought into either of those statements, and most particularly not the first one. Ok. Upthread, Dimitri claimed that he wasn't creating two different kinds of extensions with this patch, but the more I read about it the more it seems that he *is* making a fundamentally different kind of animal. And I don't think it's necessarily a good idea, especially not if we still call it an extension. I have to admit I haven't read the whole discussion about this. And I also have to say that I have no idea yet whether I like the current implementation because I haven't looked at it yet. I just wanted to give input to the separate problems Heikki listed. Because I wished for something roughly like this for years... To me it seems to be sensible that extensions which are preinstalled on the system are global and extensions which a single user inside a single database created are per database. Imo that doesn't make them all that fundamentally different. I kind of like Heikki's idea of leaving CREATE EXTENSION alone and inventing a separate UPLOAD EXTENSION operation, but there's a problem with that: in many, probably most, installations, the server does not and should not have permission to scribble on the directories where the extension scripts are stored. Possibly we could circumvent that by creating an auxiliary extensions directory under $PGDATA. (But then it starts to seem like pg_dumpall --- not pg_dump --- ought to include those files in its output...) UPLOAD EXTENSION seems to be a good idea. But I really really would like them to go to a per-database directory not a per-cluster one. Otherwise the coordination between different database owners inside a cluster will get really hairy. I want to be able to install different versions of an application into different databases. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On 05.12.2012 20:07, Tom Lane wrote: Heikki Linnakangashlinnakan...@vmware.com writes: And whether extension control files (or the same information stored in a table or wherever) should be per-database or per cluster - that's *yet* another separate issue. You could argue for either behavior. I think anyone arguing for the former is confusing an installed extension with a not-installed one. Maybe it would help if we adopted different terminologies. Perhaps call the control+sql files a template, while using extension for the installed entity? +1 on the naming. You could still argue that templates should be per-database. It would make life easier for someone who is database owner but not superuser, for example, allowing you to install an extension that only affects your own database (assuming we set up the permissions so that that's possible, of course). - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Dimitri Fontaine dimi...@2ndquadrant.fr writes: At the SQL level, extensions do live in a database. The only reason why we currently have them on the file system is binary executables (.so, .dylib, .dll). And those are not per database, not even per cluster, not even per major version, they are *per server*. It's something that makes me very sad, and that I want to have the chance to fix later, but that won't happen in 9.3, and certainly not in that very patch⦠I think you're wasting your time to imagine that that case will ever be fixed. Allowing the server to scribble on executable files would set off all kinds of security alarm bells, and rightly so. If Postgres ever did ship with such a thing, I rather imagine that I'd be required to patch it out of Red Hat releases (not that SELinux wouldn't prevent it from happening anyway). I do see an argument for allowing SQL-only extensions to be installed this way, since that doesn't allow the execution of anything the user couldn't execute anyway. There's no need to worry about anything except control and script files though. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On 05.12.2012 20:13, Andres Freund wrote: But I really really would like them to go to a per-database directory not a per-cluster one. Otherwise the coordination between different database owners inside a cluster will get really hairy. I want to be able to install different versions of an application into different databases. Extension authors should be careful to maintain backwards-compatibility, so that it would be enough to have the latest version installed. If you break compatibility, you probably should rename the extension. That said, I can understand that in practice you'd want to have different versions installed at the same time, so that you don't need to re-test everything when upgrading an extension, and don't need to trust that the extension author didn't accidentally break backwards-compatibility anyway. If you really meant different versions of an application, and not different versions of an extension, then it seems to me that you're abusing the extension infrastructure for something else. If you have some functions that you consider part of the application, even if those functions might be useful in other applications too, you probably don't want to treat them as an extension. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On 2012-12-05 20:15:42 +0200, Heikki Linnakangas wrote: On 05.12.2012 20:07, Tom Lane wrote: Heikki Linnakangashlinnakan...@vmware.com writes: And whether extension control files (or the same information stored in a table or wherever) should be per-database or per cluster - that's *yet* another separate issue. You could argue for either behavior. I think anyone arguing for the former is confusing an installed extension with a not-installed one. Not sure whether it would be the best design, but having something like UPLOAD EXTENSION which can only exist in the installed form would be enough for nearly all the use-cases I experienced. Maybe it would help if we adopted different terminologies. Perhaps call the control+sql files a template, while using extension for the installed entity? +1 on the naming. +1 on the idea of naming them separately, I am not happy with template, but then I don't have a better suggestion. You could still argue that templates should be per-database. It would make life easier for someone who is database owner but not superuser, for example, allowing you to install an extension that only affects your own database (assuming we set up the permissions so that that's possible, of course). +1. We could even have two variants, UPLOAD [GLOBAL] EXTENSION/TEMPLATE. ISTM that we would need some kind of search path anyway so adding that separation seems to be a minimal amount of additional effort. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On 2012-12-05 20:23:29 +0200, Heikki Linnakangas wrote: On 05.12.2012 20:13, Andres Freund wrote: But I really really would like them to go to a per-database directory not a per-cluster one. Otherwise the coordination between different database owners inside a cluster will get really hairy. I want to be able to install different versions of an application into different databases. Extension authors should be careful to maintain backwards-compatibility, so that it would be enough to have the latest version installed. If you break compatibility, you probably should rename the extension. In theory yes. In practice: That said, I can understand that in practice you'd want to have different versions installed at the same time, so that you don't need to re-test everything when upgrading an extension, and don't need to trust that the extension author didn't accidentally break backwards-compatibility anyway. ;) If you really meant different versions of an application, and not different versions of an extension, then it seems to me that you're abusing the extension infrastructure for something else. If you have some functions that you consider part of the application, even if those functions might be useful in other applications too, you probably don't want to treat them as an extension. I was thinking of reusable parts of applications that might be used in more than one application. *But* I think this also is a good basis to encapsulate individual non-shared parts of an application. Why not? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On 2012-12-05 13:18:16 -0500, Tom Lane wrote: Dimitri Fontaine dimi...@2ndquadrant.fr writes: At the SQL level, extensions do live in a database. The only reason why we currently have them on the file system is binary executables (.so, .dylib, .dll). And those are not per database, not even per cluster, not even per major version, they are *per server*. It's something that makes me very sad, and that I want to have the chance to fix later, but that won't happen in 9.3, and certainly not in that very patch… Maybe I am missing something, but you already can separate them per major version. You co-wrote the debian infrastructure to do so for some debian packages, so I am not sure what you mean here. Adding some *NON WRITABLE* per-cluster library directory doesn't seem to be as controversion as other suggestions. I think you're wasting your time to imagine that that case will ever be fixed. Allowing the server to scribble on executable files would set off all kinds of security alarm bells, and rightly so. If Postgres ever did ship with such a thing, I rather imagine that I'd be required to patch it out of Red Hat releases (not that SELinux wouldn't prevent it from happening anyway). +1 Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On Wed, Dec 5, 2012 at 5:22 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: This patch is all about enabling users to create extension without having to ship them as root on the file system of the database(s) server(s) first. Right, but it changes the way that existing extensions *dump*, which seems to me to undo the use case that works now. I mean, the advantage of dumping an extension as CREATE EXTENSION hstore is that you can reload that dump on a different server with a newer version of hstore installed, and it'll still work. If we go back to dumping all of the SQL commands that compose that extension, then it'll break again, in exactly the way things were broken before we had extensions in the first place. Back in the bad old days, you'd dump your old database (which had all of the SQL commands for say hstore) and then reload it on your new database - and it would fail, because the old SQL commands didn't match the new binaries. Oops. With the extension mechanism, it all works just fine: the old database emits CREATE EXTENSION hstore and the new database can execute that just fine. You still have a problem if the extension has meanwhile been changed in a backwards-incompatible way that doesn't work for your application (i.e. you're using the = operator which has since been removed) but hopefully that doesn't happen too often, and in any event it seems relatively unavoidable. And it takes nothing away from the problem that extensions DO solve, which is incompatibilities between the SQL file and the shared library. When you can code your extension using dynamic code such as SQL or PL/pgSQL, PL/pythonu or PL/perl, there's absolutely no good reason to have to do the ship on the server's file system first that I can see. Technically creating an extension inline (sending its definition in the CREATE EXTENSION query itself) solves the problem of having to access the server's file system as root. True, but so does not putting the code into an extension at all. You can just create loose functions and operators. It's unclear to me what advantage the extension mechanism provides if there's no shared library and no on-disk files involved. Then, next pg_dump will include CREATE EXTENSION foo; as usual and at pg_restore time that access files on the file systems. But maybe you still are not granted access to the server's file system as root on the pg_restore target, right? So now you need to be able to include the extension's script into the dump. Granting for the moment that there's a reason to call this an extension at all, rather than a schema or just a bunch of random CREATE commands, which is not obvious to me, yes, you need to include it in the dump. But sure then the extension needs to be marked as being, somehow, a different flavor of extension that can only use SQL (not shlibs) and needs to be dumped in-line, because otherwise, as noted above, we break things for the flavor of extensions we've already got. Also, even there, it seems to me that it ought to work something like this: CREATE EXTENSION inline_extension NULL; -- create an extension with no members CREATE FUNCTION blahblahblah ... ALTER EXTENSION inline_extension ADD FUNCTION blahblab ... and so on for all the other members That is, the extension members should just become dumpable objects. This seems quite bizarre since the whole point of extensions AIUI is to avoid dumping the members, but it's better than what the patch implements. In the patch, IIRC, you emit all the members as a separate dump that gets enclosed by dollar quotes. This strikes me as ugly, and I think you can construct circular dependency situations in which it will fail outright. Now, we don't want to have more than one kind of extensions. That's what we learnt all together when reviewing my proposal from last year. Having more than one way to ship an extension is good, having two different animals with two different incompatible behaviors named the same thing is bad. The solution we found is then to be able to include an extension's script into pg_dump's output, and that's what my current patch implements, per last year review. I don't think I agree. I don't see a problem having more than one kind of extensions, but I'm worried that you're trying to shoehorn something that isn't really an extension into an extension-sized box. And I sure don't want that to mean let's break stuff that works right now. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On Wed, Dec 5, 2012 at 12:47 PM, Andres Freund and...@2ndquadrant.com wrote: For me it seems pretty natural to support dumping extension the way they got created. I.e. a plain CREATE EXTENSION ...; if the extension was preinstalled and some form that includes the extension source if you installed it via the connection. Extensions that were installed in some global manner *should* not be dumped with ones installed over the connection. E.g. dumping /contrib or packaged modules seems to be a bad idea to me. That would possibly be useful as a debugging tool, but I don't see much point besides that. I agree with all of that. What I can't quite figure out is - AIUI, extensions are a way of bundling shared libraries with SQL scripts, and a way of managing the dump and restore process. If you just have SQL, there's no bundling to do, and if you reverse out the pg_dump changes (which is more or less what's being proposed here), then what do you have left other than the good feeling of being part of an extension? At that point, it seems to me that you've gone to a lot of work to add a layer of packaging that serves no real purpose. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On 2012-12-05 13:50:27 -0500, Robert Haas wrote: On Wed, Dec 5, 2012 at 12:47 PM, Andres Freund and...@2ndquadrant.com wrote: For me it seems pretty natural to support dumping extension the way they got created. I.e. a plain CREATE EXTENSION ...; if the extension was preinstalled and some form that includes the extension source if you installed it via the connection. Extensions that were installed in some global manner *should* not be dumped with ones installed over the connection. E.g. dumping /contrib or packaged modules seems to be a bad idea to me. That would possibly be useful as a debugging tool, but I don't see much point besides that. I agree with all of that. What I can't quite figure out is - AIUI, extensions are a way of bundling shared libraries with SQL scripts, and a way of managing the dump and restore process. If you just have SQL, there's no bundling to do, and if you reverse out the pg_dump changes (which is more or less what's being proposed here), then what do you have left other than the good feeling of being part of an extension? At that point, it seems to me that you've gone to a lot of work to add a layer of packaging that serves no real purpose. Manageability. E.g. for years I had a set of (trigger) functions to counted the number of rows in a table in a lockless manner. That's used in 10+ applications of former clients of mine. All (plpg)sql. Imagine I want to ship an updated version that 1. removes some *internal* functions, 2. adds some internal function. 3. adds a new *external* function. Now most of the clients use completely different development models and completely different ways of manageing upgrades. I needed to integrate my teensy module into all of them. If we had a way to package it nicely they could just upload the extension inside their own workflows and I (or they) would be freed from integrating foreign update scripts into their workflow. Imagine embedding a PGXN module into your application which is used on many servers and doesn't need superuser privileges or anything. Same thing. That's not something all that uncommon is it? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On Wed, Dec 5, 2012 at 2:01 PM, Andres Freund and...@2ndquadrant.com wrote: E.g. for years I had a set of (trigger) functions to counted the number of rows in a table in a lockless manner. That's used in 10+ applications of former clients of mine. All (plpg)sql. Imagine I want to ship an updated version that 1. removes some *internal* functions, 2. adds some internal function. 3. adds a new *external* function. Now most of the clients use completely different development models and completely different ways of manageing upgrades. I needed to integrate my teensy module into all of them. If we had a way to package it nicely they could just upload the extension inside their own workflows and I (or they) would be freed from integrating foreign update scripts into their workflow. OK, but let me play devil's advocate here. Under the status quo, if they used loose database objects, they would need to execute some database code that does this: DROP FUNCTION internalfunc1(int); CREATE FUNCTION internalfunc2(int); CREATE FUNCTION externalfunc3(int); IIUC, under this proposal, the client would instead need to execute some SQL code that looks something this (I'm faking the syntax here, forgive me, but the patch doesn't seem to contemplate ALTER): ALTER EXTENSION myextension UPDATE TO 1.1 USING SCRIPT $$ ALTER EXTENSION myextension DROP FUNCTION internalfunc1(int); DROP FUNCTION internalfunc1(int); CREATE FUNCTION internalfunc2(int); ALTER EXTENSION myextension ADD FUNCTION internalfunc2(int); CREATE FUNCTION externalfunc3(int); ALTER FUNCTION myextension ADD FUNCTION externalfunc3(int); $$; That doesn't really look like an improvement to me. What am I missing? Imagine embedding a PGXN module into your application which is used on many servers and doesn't need superuser privileges or anything. Same thing. That's not something all that uncommon is it? Not at all. I'm not questioning the use case at all; I'm questioning whether extensions are the right tool for addressing it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On 2012-12-05 14:10:34 -0500, Robert Haas wrote: On Wed, Dec 5, 2012 at 2:01 PM, Andres Freund and...@2ndquadrant.com wrote: E.g. for years I had a set of (trigger) functions to counted the number of rows in a table in a lockless manner. That's used in 10+ applications of former clients of mine. All (plpg)sql. Imagine I want to ship an updated version that 1. removes some *internal* functions, 2. adds some internal function. 3. adds a new *external* function. Now most of the clients use completely different development models and completely different ways of manageing upgrades. I needed to integrate my teensy module into all of them. If we had a way to package it nicely they could just upload the extension inside their own workflows and I (or they) would be freed from integrating foreign update scripts into their workflow. OK, but let me play devil's advocate here. Under the status quo, if they used loose database objects, they would need to execute some database code that does this: DROP FUNCTION internalfunc1(int); CREATE FUNCTION internalfunc2(int); CREATE FUNCTION externalfunc3(int); They would need to do exactly that if their database had version 1.1 and they upgrade to 1.3 but not if they already had 1.2... IIUC, under this proposal, the client would instead need to execute some SQL code that looks something this (I'm faking the syntax here, forgive me, but the patch doesn't seem to contemplate ALTER): ALTER EXTENSION myextension UPDATE TO 1.1 USING SCRIPT $$ ALTER EXTENSION myextension DROP FUNCTION internalfunc1(int); DROP FUNCTION internalfunc1(int); CREATE FUNCTION internalfunc2(int); ALTER EXTENSION myextension ADD FUNCTION internalfunc2(int); CREATE FUNCTION externalfunc3(int); ALTER FUNCTION myextension ADD FUNCTION externalfunc3(int); $$; That doesn't really look like an improvement to me. What am I missing? They should be able to simply slurp the extension from a file, possibly even install it outside their own update mechanism. Given that you don't know which version was installed beforehand thats not really possible without some infrastructure. And they should be able to drop the extension again afterwards without it leaving a trace. Nearly all I have seen out there fails at that, and the extension mechanism provides tracking of that. Imagine embedding a PGXN module into your application which is used on many servers and doesn't need superuser privileges or anything. Same thing. That's not something all that uncommon is it? Not at all. I'm not questioning the use case at all; I'm questioning whether extensions are the right tool for addressing it. Do you have some alterantive suggestion? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On Wed, Dec 5, 2012 at 2:19 PM, Andres Freund and...@2ndquadrant.com wrote: IIUC, under this proposal, the client would instead need to execute some SQL code that looks something this (I'm faking the syntax here, forgive me, but the patch doesn't seem to contemplate ALTER): ALTER EXTENSION myextension UPDATE TO 1.1 USING SCRIPT $$ ALTER EXTENSION myextension DROP FUNCTION internalfunc1(int); DROP FUNCTION internalfunc1(int); CREATE FUNCTION internalfunc2(int); ALTER EXTENSION myextension ADD FUNCTION internalfunc2(int); CREATE FUNCTION externalfunc3(int); ALTER FUNCTION myextension ADD FUNCTION externalfunc3(int); $$; That doesn't really look like an improvement to me. What am I missing? They should be able to simply slurp the extension from a file, possibly even install it outside their own update mechanism. Given that you don't know which version was installed beforehand thats not really possible without some infrastructure. And they should be able to drop the extension again afterwards without it leaving a trace. Nearly all I have seen out there fails at that, and the extension mechanism provides tracking of that. Ah, OK. Well, it sounds like this might be a decent fit for the TEMPLATE concept proposed upthread, then. I have no objection whatsoever to the concept of storing the SQL and control files somewhere that doesn't need access to the server filesystem - in fact, I think I previously proposed allowing those to be stored in a database table. You could do that with something like: CREATE TEMPLATE yadda; ALTER TEMPLATE yadda ADD FILE 'yadda--1.0.sql' CONTENT $$...$$; ...or whatever. And that'd be 100% fine with me, and it could dump and restore just that way, and life would be good. Or at least, it sounds to me like that would meet the requirements you are articulating without breaking anything that works today. In fact, it sounds pretty cool. The root of my objections upthread, I think, is that the design this patch puts on the table seems to me to conflate the extension (which is always a database object) with the template (which is *currently* always a filesystem object). I think that's bound to create some problems. In the patch as it exists today, I think those problems are going to leak out in the form of breaking some of the things for which extensions can currently be used, but even if we address those points I have a sneaking suspicion that there will be others. For example, your point (in the portion of your email I'm not quoting here) about an upgrade across multiple version is well-taken - you need a different script depending on the version that's currently installed. Fixing that, though, seems to require a catalog of upgrade scripts, so that the server can look at the installed version and the available scripts and decide how to proceed. That catalog currently takes the form of separate files in the filesystem, but I don't see any reason why we can't store it somewhere else. What I'm not sold on is the idea of shuttling it across as part of CREATE/ALTER EXTENSION statements. I'm willing to be persuaded, but right now I can't see how that's ever going either robust or convenient. Making it part of a separate SQL object type gets around that problem rather nicely, IMHO. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Robert Haas escribió: I have no objection whatsoever to the concept of storing the SQL and control files somewhere that doesn't need access to the server filesystem - in fact, I think I previously proposed allowing those to be stored in a database table. You could do that with something like: CREATE TEMPLATE yadda; ALTER TEMPLATE yadda ADD FILE 'yadda--1.0.sql' CONTENT $$...$$; ...or whatever. This seems unnecessary to me. What the patch at hand does is take the file (actually, the contents of the file) and execute it directly, without installing anything on disk. The precise contents of the extension is still tracked through pg_depend, so you can drop it without having previously saved neither the control file or the SQL script. (In fact, that's how DROP EXTENSION works currently.) There's also the pg_dump side of things; with your proposal we would be forced to move over the yadda--1.0.sql file from the old server to the new one; or, equivalently, put the whole ALTER TEMPLATE .. CONTENT command in the dump, which is equivalent to what Dimitri's patch does; so there doesn't seem to be a point. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Andres Freund and...@2ndquadrant.com writes: Maybe I am missing something, but you already can separate them per major version. You co-wrote the debian infrastructure to do so for some debian packages, so I am not sure what you mean here. The debian infrastructure I've help building is all about compiling an extension source package once and having as many binary artefacts as you have major versions of PostgreSQL lying around. So yes in debian you can actually install such extensions at different on disk locations per major version. Sorry for the confusion. Adding some *NON WRITABLE* per-cluster library directory doesn't seem to be as controversion as other suggestions. Well, it means a per-initdb (user driven) location where to store binary files, ask Tom what he thinks about that with his Red Hat Packager… Hat. On 2012-12-05 13:18:16 -0500, Tom Lane wrote: I think you're wasting your time to imagine that that case will ever be fixed. Allowing the server to scribble on executable files would set off all kinds of security alarm bells, and rightly so. If Postgres ever did ship with such a thing, I rather imagine that I'd be required to patch it out of Red Hat releases (not that SELinux wouldn't prevent it from happening anyway). That part I did understand. I still can't be happy about it, but I won't get back with any proposal where that's put into questions. That said, while you're talking about it, what if it's an opt-in GUC? I do see an argument for allowing SQL-only extensions to be installed this way, since that doesn't allow the execution of anything the user couldn't execute anyway. There's no need to worry about anything except control and script files though. […please make sure you're not drinking (coffee) before reading further…] Now if we can't fix the executable files situation, what about making the C coded extensions not require an executable anymore? I'm thinking about studying what it would take exactly to write a PL/C where the PostgreSQL backend would basically compile the embedded C code at CREATE FUNCTION time and store bytecode or binary in the probin column. I've stumbled accross more than one dynamic code or retargetable compiler thing already, and several of those even have compatible licences. Maybe the most promising ones are PL/LLVM or embeding the QEMU code transformation code (a fork of the tcc compiler). So, we're talking about a PL/C language, in-core or extension, where you could define say hstore without shipping any executable binary. Yeah, I'm crazy that way. Now I'll get back to the main thread… Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On 2012-12-05 21:16:52 +0100, Dimitri Fontaine wrote: Andres Freund and...@2ndquadrant.com writes: Adding some *NON WRITABLE* per-cluster library directory doesn't seem to be as controversion as other suggestions. Well, it means a per-initdb (user driven) location where to store binary files, ask Tom what he thinks about that with his Red Hat Packager… Hat. I think it might be different if the directory is non-writable and connot be made writable by user running postgres. I do see an argument for allowing SQL-only extensions to be installed this way, since that doesn't allow the execution of anything the user couldn't execute anyway. There's no need to worry about anything except control and script files though. […please make sure you're not drinking (coffee) before reading further…] Now if we can't fix the executable files situation, what about making the C coded extensions not require an executable anymore? I'm thinking about studying what it would take exactly to write a PL/C where the PostgreSQL backend would basically compile the embedded C code at CREATE FUNCTION time and store bytecode or binary in the probin column. So, we're talking about a PL/C language, in-core or extension, where you could define say hstore without shipping any executable binary. Yeah, I'm crazy that way. Now I'll get back to the main thread… Imo thats not a sensible thing to pursue. It would seriously shorten the effort needed to run user-provided code. Yes, you can execute unrestricted perl, python whatever already but with selinux and similar things in place that won't allow you to run your own binaries. And currently execute-or-write protection will prevent you from executing compiled code. So you would have to disable that as well... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
So, Lots of things are being said, most of them are really interesting and some of them are just re-hashing what we said about a year ago in the Inline Extensions thread, whose conclusion was that the key not to have two different beasts (inline, file based) was for pg_dump to process them all the same way. Meanwhile, I think I need to address that reaction first: Robert Haas robertmh...@gmail.com writes: What I can't quite figure out is - AIUI, extensions are a way of bundling shared libraries with SQL scripts, and a way of managing the dump and restore process. Not quite. Extensions are user defined CASCADE support. It's all about pg_depend extensibility. Extensions are a way to manage dependencies of SQL objects in a way that allow you to manage them as a single entity. Now you can CREATE EXTENSION ALTER EXTENSION DROP EXTENSION and all you're doing is managing a bunch of SQL objects at once. The fact that it allows to implement a working dumprestore of aborigen extensions called contribs has been the first step, not the whole goal. You will notice that there's nothing in the whole extension patch and framework that refers to a module, those little executable binary files whose name depend on the OS PostgreSQL is running on, and that you manage at the file system level, in postgresql.conf with some GUCs, and with the LOAD command. You will also notice that we have been *very* careful not to taint any extension related SQL command with the notion of files. That part is well separated away and only meant to be known by extension authors and packagers, not by mere mortals such as DBAs or users. The current patch is willing to push that a little further away, making it optional even to extension authors. Those two facts didn't just happen. And I was not alone in designing the system that way. Let's continue the design and its implementation! :) Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Andres Freund escribió: On 2012-12-05 21:16:52 +0100, Dimitri Fontaine wrote: Now if we can't fix the executable files situation, what about making the C coded extensions not require an executable anymore? I'm thinking about studying what it would take exactly to write a PL/C where the PostgreSQL backend would basically compile the embedded C code at CREATE FUNCTION time and store bytecode or binary in the probin column. Imo thats not a sensible thing to pursue. +1. Certainly a pg_dump patch's thread is not the place to propose it. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Alvaro Herrera alvhe...@2ndquadrant.com writes: +1. Certainly a pg_dump patch's thread is not the place to propose it. Sure. Sorry about that, the goal of that previous message was to let people come to understand better my whole vision of what is an Extension, a contrib, and where we are what I wanted us to build. I refined those ideas in another email though, so you can safely ignore this sub-thread. I'll get back to the question of storing .so in a per database location with an opt-in GUC later, when appropriate. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Andres Freund and...@2ndquadrant.com writes: On 2012-12-05 21:16:52 +0100, Dimitri Fontaine wrote: Now if we can't fix the executable files situation, what about making the C coded extensions not require an executable anymore? I'm thinking about studying what it would take exactly to write a PL/C where the PostgreSQL backend would basically compile the embedded C code at CREATE FUNCTION time and store bytecode or binary in the probin column. So, we're talking about a PL/C language, in-core or extension, where you could define say hstore without shipping any executable binary. Yeah, I'm crazy that way. Now I'll get back to the main thread⦠Imo thats not a sensible thing to pursue. That would be another thing that Red Hat would refuse to ship, as would any other distro with an ounce of concern about security. But in any case there's no way that we could implement it portably. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Dimitri Fontaine dimi...@2ndquadrant.fr writes: On 2012-12-05 13:18:16 -0500, Tom Lane wrote: I think you're wasting your time to imagine that that case will ever be fixed. Allowing the server to scribble on executable files would set off all kinds of security alarm bells, and rightly so. If Postgres ever did ship with such a thing, I rather imagine that I'd be required to patch it out of Red Hat releases (not that SELinux wouldn't prevent it from happening anyway). That part I did understand. I still can't be happy about it, but I won't get back with any proposal where that's put into questions. That said, while you're talking about it, what if it's an opt-in GUC? GUC or no GUC, it'd still be letting an unprivileged network-exposed application (PG) do something that's against any sane system-level security policy. Lipstick is not gonna help this pig. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On 2012-12-05 16:20:41 -0500, Tom Lane wrote: Dimitri Fontaine dimi...@2ndquadrant.fr writes: On 2012-12-05 13:18:16 -0500, Tom Lane wrote: I think you're wasting your time to imagine that that case will ever be fixed. Allowing the server to scribble on executable files would set off all kinds of security alarm bells, and rightly so. If Postgres ever did ship with such a thing, I rather imagine that I'd be required to patch it out of Red Hat releases (not that SELinux wouldn't prevent it from happening anyway). That part I did understand. I still can't be happy about it, but I won't get back with any proposal where that's put into questions. That said, while you're talking about it, what if it's an opt-in GUC? GUC or no GUC, it'd still be letting an unprivileged network-exposed application (PG) do something that's against any sane system-level security policy. Lipstick is not gonna help this pig. What about the non-writable per cluster directory? Thats something I've actively wished for in the past when developing a C module thats also used in other clusters. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Andres Freund and...@anarazel.de writes: On 2012-12-05 16:20:41 -0500, Tom Lane wrote: GUC or no GUC, it'd still be letting an unprivileged network-exposed application (PG) do something that's against any sane system-level security policy. Lipstick is not gonna help this pig. What about the non-writable per cluster directory? Thats something I've actively wished for in the past when developing a C module thats also used in other clusters. I see no security objection to either per-cluster or per-database script+control-file directories, as long as they can only contain SQL scripts and not executable files. If we allow such things to be installed by less-than-superusers, we'll have to think carefully about what privileges are given when running the script. I forget at the moment how much of that we already worked out back in the 9.1 era; I remember it was discussed but not whether we had a bulletproof solution. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On 2012-12-05 16:42:38 -0500, Tom Lane wrote: Andres Freund and...@anarazel.de writes: On 2012-12-05 16:20:41 -0500, Tom Lane wrote: GUC or no GUC, it'd still be letting an unprivileged network-exposed application (PG) do something that's against any sane system-level security policy. Lipstick is not gonna help this pig. What about the non-writable per cluster directory? Thats something I've actively wished for in the past when developing a C module thats also used in other clusters. I see no security objection to either per-cluster or per-database script+control-file directories, as long as they can only contain SQL scripts and not executable files. Well, I was explicitly talking about C code above. The question doesn't really have to do too much with this thread, sorry. Given I am proposing the directory to be explicitly read-only and under permission that don't allow postgres to change that its not really suitable for this topic... Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On Wed, Dec 5, 2012 at 2:54 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Robert Haas escribió: I have no objection whatsoever to the concept of storing the SQL and control files somewhere that doesn't need access to the server filesystem - in fact, I think I previously proposed allowing those to be stored in a database table. You could do that with something like: CREATE TEMPLATE yadda; ALTER TEMPLATE yadda ADD FILE 'yadda--1.0.sql' CONTENT $$...$$; ...or whatever. This seems unnecessary to me. What the patch at hand does is take the file (actually, the contents of the file) and execute it directly, without installing anything on disk. The precise contents of the extension is still tracked through pg_depend, so you can drop it without having previously saved neither the control file or the SQL script. (In fact, that's how DROP EXTENSION works currently.) Yeah, DROP will work. But what about ALTER .. UPDATE? There's also the pg_dump side of things; with your proposal we would be forced to move over the yadda--1.0.sql file from the old server to the new one; or, equivalently, put the whole ALTER TEMPLATE .. CONTENT command in the dump, which is equivalent to what Dimitri's patch does; so there doesn't seem to be a point. Well, there's certainly a point, because IIUC Dimitri's patch dumps the file into the pg_dump output no matter whether the file originally came from an SQL command or the filesystem. IMHO, anyone who thinks that isn't going to break things rather badly isn't thinking hard enough. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Robert Haas robertmh...@gmail.com writes: Yeah, DROP will work. But what about ALTER .. UPDATE? What about it? Well, there's certainly a point, because IIUC Dimitri's patch dumps the file into the pg_dump output no matter whether the file originally came from an SQL command or the filesystem. IMHO, anyone who thinks that isn't going to break things rather badly isn't thinking hard enough. Only if you ask for it using --extension-script. The default behaviour didn't change, whether you decide to install your extension from the file system or the PostgreSQL port. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Dimitri Fontaine escribió: Robert Haas robertmh...@gmail.com writes: Well, there's certainly a point, because IIUC Dimitri's patch dumps the file into the pg_dump output no matter whether the file originally came from an SQL command or the filesystem. IMHO, anyone who thinks that isn't going to break things rather badly isn't thinking hard enough. Only if you ask for it using --extension-script. The default behaviour didn't change, whether you decide to install your extension from the file system or the PostgreSQL port. What happens on a normal pg_dump of the complete database? For extensions that were installed using strings instead of files, do I get a string back? Because if not, the restore is clearly going to fail anyway. I mean, clearly the user doesn't want to list the extensions, figure which ones were installed by strings, and then do pg_dump --extension-script on them. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Alvaro Herrera alvhe...@2ndquadrant.com writes: What happens on a normal pg_dump of the complete database? For extensions that were installed using strings instead of files, do I get a string back? Because if not, the restore is clearly going to fail anyway. The argument here is that the user would then have packaged its extension as files in the meantime. If not, that's operational error. A backup you didn't restore successfully isn't a backup anyway. I mean, clearly the user doesn't want to list the extensions, figure which ones were installed by strings, and then do pg_dump --extension-script on them. The idea is that the user did install the extensions that came by strings. Last year the consensus was clearly for pg_dump not to distinguish in between file based and string based extensions that are exactly the same thing once installed in a database. That's the current design. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On 2012-12-05 23:28:45 +0100, Dimitri Fontaine wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: What happens on a normal pg_dump of the complete database? For extensions that were installed using strings instead of files, do I get a string back? Because if not, the restore is clearly going to fail anyway. The argument here is that the user would then have packaged its extension as files in the meantime. If not, that's operational error. A backup you didn't restore successfully isn't a backup anyway. Uh. Wait. What? If that argument is valid, we don't need anything but file based extensions. I mean, clearly the user doesn't want to list the extensions, figure which ones were installed by strings, and then do pg_dump --extension-script on them. The idea is that the user did install the extensions that came by strings. Last year the consensus was clearly for pg_dump not to distinguish in between file based and string based extensions that are exactly the same thing once installed in a database. That's the current design. I don't find that argument convincing in the slightest. Could I perhaps convince you to dig up a reference? I would be interested in the arguments for that design back then. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Andres Freund and...@2ndquadrant.com writes: The argument here is that the user would then have packaged its extension as files in the meantime. If not, that's operational error. A backup you didn't restore successfully isn't a backup anyway. Uh. Wait. What? If that argument is valid, we don't need anything but file based extensions. Well, I've been trying to understand the consensus, and to implement it in the simplest possible way. Maybe the default should be to activate automatically --extension-script for extensions without control files? The idea is that the user did install the extensions that came by strings. Last year the consensus was clearly for pg_dump not to distinguish in between file based and string based extensions that are exactly the same thing once installed in a database. That's the current design. I don't find that argument convincing in the slightest. Could I perhaps convince you to dig up a reference? I would be interested in the arguments for that design back then. I think here it is: http://archives.postgresql.org/pgsql-hackers/2012-01/msg01307.php -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On Wed, Dec 5, 2012 at 5:08 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Robert Haas robertmh...@gmail.com writes: Yeah, DROP will work. But what about ALTER .. UPDATE? What about it? Well, with the design you have proposed, unless you have access to the filesystem, it ain't gonna work. And if you have access to the filesystem, then this whole discussion is moot. Well, there's certainly a point, because IIUC Dimitri's patch dumps the file into the pg_dump output no matter whether the file originally came from an SQL command or the filesystem. IMHO, anyone who thinks that isn't going to break things rather badly isn't thinking hard enough. Only if you ask for it using --extension-script. The default behaviour didn't change, whether you decide to install your extension from the file system or the PostgreSQL port. That doesn't impress me in the slightest. Suppose you have two identically configured machines A and B on which you install hstore (from the filesystem) and a hypothetical extension istore (via the inline extension mechanism). Now, you take regular backups of machine A, and one day it dies, so you want to restore onto machine B. Well, if you didn't dump with --extension-script, then you've got an incomplete backup, so you are hosed. And if you did dump with --extension-script, then you're OK in that scenario, but the wheels come off if you try to dump and restore onto machine C, which is running a newer version of PostgreSQL with an updated hstore. To do it right, you have to remember which extensions you installed which way and dump exactly the right thing for each one. That can't be good. Like Andres, I'd like to see a reference to the thread where we supposedly had consensus on this behavior. I don't really recall us achieving consensus on anything, but if we did I have a hard time believing it was this. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On Wed, Dec 5, 2012 at 5:43 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: I don't find that argument convincing in the slightest. Could I perhaps convince you to dig up a reference? I would be interested in the arguments for that design back then. I think here it is: http://archives.postgresql.org/pgsql-hackers/2012-01/msg01307.php Ah ha! I had to read that twice to remember what I meant by it, so that may be a sign that the original email wasn't any too clear. That having been said, I think that the confusion is this: the second paragraph of that email was intended to be interpreted *in the context* of the proposal made in the first paragraph of the email, NOT as a separate proposal. In other words, the first paragraph is arguing for something like the notion of an extension template - the ability to store the extension files inside the server, in cases where you don't want them to appear in the file system. But perhaps implemented using functions rather than dedicated SQL syntax. But regardless of the concrete syntax, the first paragraph is proposing that we have something conceptually similar to: CREATE TEMPLATE yadda; ALTER TEMPLATE yadda ADD FILE 'yadda--1.0.sql' CONTENT $$...$$; Given that context, the second paragraph is intended as a suggestion that we should have something like pg_dump --no-templates -- which would still emit any CREATE EXTENSION commands, but not any CREATE/ALTER TEMPLATE commands - so if you relied on any templates in setting up the old cluster, the new cluster would need to have the files installed in the usual place. It was not a suggestion that we shoehorn the file management into CREATE / ALTER EXTENSION as you are proposing here; the first paragraph expresses my opinion, which hasn't changed between then and now, that that's a bad design. Ugh. Is that any more clear than what I said before? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Robert Haas robertmh...@gmail.com writes: Well, with the design you have proposed, unless you have access to the filesystem, it ain't gonna work. And if you have access to the filesystem, then this whole discussion is moot. I did mention that this version of the patch is only ready to host the current design talk we have now. I intend to amend it with some inline ALTER EXTENSION facility. In the worked out example you gave in another mail of this thread, you would have to remove any explicit ALTER EXTENSION … ADD … of course, as in a classic script here. You would have to fill in both the current and next version of the extension I guess, as a defensive check, too. That doesn't impress me in the slightest. Suppose you have two identically configured machines A and B on which you install hstore (from the filesystem) and a hypothetical extension istore (via the inline extension mechanism). Now, you take regular backups of machine A, and one day it dies, so you want to restore onto machine B. Well, if you didn't dump with --extension-script, then you've got an incomplete backup, so you are hosed. And if you did dump with You didn't ever restore your backup? So you didn't know for sure you had one. More seriously… --extension-script, then you're OK in that scenario, but the wheels come off if you try to dump and restore onto machine C, which is running a newer version of PostgreSQL with an updated hstore. To do it right, you have to remember which extensions you installed which way and dump exactly the right thing for each one. That can't be good. In the patch we're talking about, the --extension-script is an accumulative option that needs an argument, so you do pg_dump --extension-script istore --extension-script foo or if you're into short options pg_dump -X istore -X foo -X bar I'm not saying that design is perfect nor definitive, it's just what happens to be in the patch, and it allows you to solve your problem. We could default the --extension-script to any installed extension for which we don't have a control file? Like Andres, I'd like to see a reference to the thread where we supposedly had consensus on this behavior. I don't really recall us achieving consensus on anything, but if we did I have a hard time believing it was this. What I remember about the consensus from last year is: - http://archives.postgresql.org/pgsql-hackers/2012-01/msg01307.php - inline and file based extensions must be the same beast once in the database - pg_dump options should work the same against either kind - it all boils down to designing a consistent dump behavior Which is the angle I've been working on reaching this round. The other thing we said is more about how to get the dump's content, and I realised that it could be so much simpler than relying on any file anywhere: pg_extension and pg_depend have all the information we need. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Robert Haas robertmh...@gmail.com writes: In other words, the first paragraph is arguing for something like the notion of an extension template - the ability to store the extension files inside the server, in cases where you don't want them to appear in the file system. But perhaps implemented using functions rather than dedicated SQL syntax. But regardless of the concrete syntax, the first paragraph is proposing that we have something conceptually similar to: CREATE TEMPLATE yadda; ALTER TEMPLATE yadda ADD FILE 'yadda--1.0.sql' CONTENT $$...$$; Given that context, the second paragraph is intended as a suggestion that we should have something like pg_dump --no-templates -- which would still emit any CREATE EXTENSION commands, but not any CREATE/ALTER TEMPLATE commands - so if you relied on any templates in setting up the old cluster, the new cluster would need to have the files installed in the usual place. It was not a suggestion that we shoehorn the file management into CREATE / ALTER EXTENSION as you are proposing here; the first paragraph expresses my opinion, which hasn't changed between then and now, that that's a bad design. FWIW, the more I think about it the more I like the notion of treating extension templates as a separate kind of object. I do see value in storing them inside the database system: transactional safety, the ability to identify an owner, etc etc. But conflating this functionality with installed extensions is just going to create headaches. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Dimitri Fontaine dimi...@2ndquadrant.fr writes: Robert Haas robertmh...@gmail.com writes: Well, there's certainly a point, because IIUC Dimitri's patch dumps the file into the pg_dump output no matter whether the file originally came from an SQL command or the filesystem. IMHO, anyone who thinks that isn't going to break things rather badly isn't thinking hard enough. Only if you ask for it using --extension-script. The default behaviour didn't change, whether you decide to install your extension from the file system or the PostgreSQL port. A dump-level option for that seems completely wrong in any case: it breaks one of the fundamental design objectives for extensions, or at least for extensions as originally conceived. It might be necessary to do it this way for these new critters, but that just reinforces the point that you're designing a new kind of object. I think a separate kind of extension template object would make a lot more sense. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Dimitri Fontaine dimi...@2ndquadrant.fr writes: In the patch we're talking about, the --extension-script is an accumulative option that needs an argument, so you do pg_dump --extension-script istore --extension-script foo or if you're into short options pg_dump -X istore -X foo -X bar My reaction to this is you've *got* to be kidding. You're going to put it on the user to remember which extensions are which, or else he gets an unusable dump? I don't think we should have a switch like this at all. pg_dump should do the right thing for each extension without being told. And, once more, I think keeping the dump behavior for extensions as-is and inventing a different concept for the script-file-substitutes would be better than conflating the cases. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Robert Haas robertmh...@gmail.com writes: I'm not opposed to the idea of being able to make extensions without files on disk work ... but I consider it a niche use case; the behavior we have right now works well for me and hopefully for others most of the time. Apparently I'm not the only one doing extensions without anything to compile, all SQL: http://keithf4.com/extension_tips_3 Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On 19 November 2012 16:25, Robert Haas robertmh...@gmail.com wrote: Beyond that, I think much of the appeal of the extension feature is that it dumps as CREATE EXTENSION hstore; and nothing more. That allows you to migrate a dump between systems with different but compatible versions of the hstore and have things work as intended. I'm not opposed to the idea of being able to make extensions without files on disk work ... but I consider it a niche use case; the behavior we have right now works well for me and hopefully for others most of the time. Distributing software should only happen by files? So why does Stackbuilder exist on the Windows binary? Why does yum exist? What's wrong with ftp huh? Why does CPAN? I've a feeling this case might be a sensible way forwards, not a niche at all. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On Thu, Nov 15, 2012 at 3:09 PM, Dimitri Fontaine
dimi...@2ndquadrant.fr wrote:
Robert Haas robertmh...@gmail.com writes:
Please find attached to this email an RFC patch implementing the basics
of the pg_dump --extension-script option. After much discussion around
the concept of an inline extension, we decided last year that a good
first step would be pg_dump support for an extension's script.
Do you have a link to the original thread? I have to confess I don't
remember what the purpose of this was and, heh heh, there are no
documentation changes in the patch itself either.
My notes include those links to the original thread:
http://archives.postgresql.org/message-id/3157.1327298...@sss.pgh.pa.us
http://archives.postgresql.org/pgsql-hackers/2012-01/msg01311.php
https://commitfest.postgresql.org/action/patch_view?id=746
I could of course work on documenting the changes prior to the
reviewing, the thing is that I've been taking a different implementation
route towards the pg_dump --extension-script idea we talked about, that
I think is much simpler than anything else.
So I'd like to know if that approach is deemed acceptable by the
Guardians Of The Code before expanding any more hour on this…
It basically boils down to this hunk in dumpExtension():
output CREATE EXTENSION x WITH … AS $x$
/*
* Have another archive for this extension: this allows us to simply
* walk the extension's dependencies and use the existing pg_dump code
* to get the object create statement to be added in the script.
*
*/
eout = CreateArchive(NULL, archNull, 0, archModeAppend);
EH = (ArchiveHandle *) eout;
/* grab existing connection and remote version information */
EH-connection = ((ArchiveHandle *)fout)-connection;
eout-remoteVersion = fout-remoteVersion;
/* dump all objects for this extension, that have been sorted out in
* the right order following dependencies etc */
...
/* restore the eout Archive into the local buffer */
for (te = EH-toc-next; te != EH-toc; te = te-next)
{
if (strlen(te-defn) 0)
appendPQExpBuffer(q, %s, te-defn);
}
CloseArchive(eout);
output $x$;
What do you think?
That approach seems likely to break things for the hoped-for parallel
pg_dump feature, though I'm not sure exactly in what way.
Beyond that, I think much of the appeal of the extension feature is
that it dumps as CREATE EXTENSION hstore; and nothing more. That
allows you to migrate a dump between systems with different but
compatible versions of the hstore and have things work as intended.
I'm not opposed to the idea of being able to make extensions without
files on disk work ... but I consider it a niche use case; the
behavior we have right now works well for me and hopefully for others
most of the time.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Robert Haas robertmh...@gmail.com writes: That approach seems likely to break things for the hoped-for parallel pg_dump feature, though I'm not sure exactly in what way. Will the parallel dump solve the dependencies and extension membership properties in parallel too? Beyond that, I think much of the appeal of the extension feature is that it dumps as CREATE EXTENSION hstore; and nothing more. That allows you to migrate a dump between systems with different but compatible versions of the hstore and have things work as intended. Yes. That's the only use case supported so far. The contrib/ use case. I'm not opposed to the idea of being able to make extensions without files on disk work ... but I consider it a niche use case; the behavior we have right now works well for me and hopefully for others most of the time. I hear you. I'm not doing that on my free time, it's not a hobby, I have customers that want it bad enough to be willing to sponsor my work here. I hope that helps you figuring about the use case being a niche or not. The current extension support has been targeted at a single use case, because that's how you bootstrap that kind of feature. We have request for extensions that will not include a part written in C. We've been around the topic last year, we spent much energy trying to come up with something easy enough to accept as a first step in that direction, and the conclusion at the time was that we want to be able to dump an extension's script. That's what my current patch is all about. More about use cases. Consider PL/Proxy. By the way, that should really really get in core and be called a FOREIGN FUNCTION, but let's get back on topic. So I have customers with between 8 and 256 plproxy partitions, that means each database upgrade has to reach that many databases. Now, I've built a automatic system that will fetch the PL function code from the staging database area, put them into files depending on the schema they live in, package those files into a single one that can be used by the CREATE EXTENSION command, automatically create an upgrade file to be able to ALTER EXTENSION … TO VERSION …, and create a bunch of debian packages out of that (a single debian source package that will build as many binary packages as we have extensions). Then, the system will push those packages to an internal repository, run apt-get update on all the database hosts, then connect to each partition and run the upgrade command. All of that could get simplified to getting the PL code into a single SQL command then running it on all the members of the cluster by using a plproxy RUN ON ALL command, now that it's a self-contained single SQL command. Of course that's only one use case, but that email is already only too long for what it does: rehashing a story we already ran last year. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
On Mon, Nov 12, 2012 at 11:00 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Please find attached to this email an RFC patch implementing the basics of the pg_dump --extension-script option. After much discussion around the concept of an inline extension, we decided last year that a good first step would be pg_dump support for an extension's script. Do you have a link to the original thread? I have to confess I don't remember what the purpose of this was and, heh heh, there are no documentation changes in the patch itself either. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping an Extension's Script
Robert Haas robertmh...@gmail.com writes:
Please find attached to this email an RFC patch implementing the basics
of the pg_dump --extension-script option. After much discussion around
the concept of an inline extension, we decided last year that a good
first step would be pg_dump support for an extension's script.
Do you have a link to the original thread? I have to confess I don't
remember what the purpose of this was and, heh heh, there are no
documentation changes in the patch itself either.
My notes include those links to the original thread:
http://archives.postgresql.org/message-id/3157.1327298...@sss.pgh.pa.us
http://archives.postgresql.org/pgsql-hackers/2012-01/msg01311.php
https://commitfest.postgresql.org/action/patch_view?id=746
I could of course work on documenting the changes prior to the
reviewing, the thing is that I've been taking a different implementation
route towards the pg_dump --extension-script idea we talked about, that
I think is much simpler than anything else.
So I'd like to know if that approach is deemed acceptable by the
Guardians Of The Code before expanding any more hour on this…
It basically boils down to this hunk in dumpExtension():
output CREATE EXTENSION x WITH … AS $x$
/*
* Have another archive for this extension: this allows us to simply
* walk the extension's dependencies and use the existing pg_dump code
* to get the object create statement to be added in the script.
*
*/
eout = CreateArchive(NULL, archNull, 0, archModeAppend);
EH = (ArchiveHandle *) eout;
/* grab existing connection and remote version information */
EH-connection = ((ArchiveHandle *)fout)-connection;
eout-remoteVersion = fout-remoteVersion;
/* dump all objects for this extension, that have been sorted out in
* the right order following dependencies etc */
...
/* restore the eout Archive into the local buffer */
for (te = EH-toc-next; te != EH-toc; te = te-next)
{
if (strlen(te-defn) 0)
appendPQExpBuffer(q, %s, te-defn);
}
CloseArchive(eout);
output $x$;
What do you think?
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Dumping an Extension's Script
Hi,
Please find attached to this email an RFC patch implementing the basics
of the pg_dump --extension-script option. After much discussion around
the concept of an inline extension, we decided last year that a good
first step would be pg_dump support for an extension's script.
The approach I've been using here is to dump the script from the catalog
current dependencies, which mean that a sequence of CREATE EXTENSION
followed by a number of ALTER EXTENSION … UPDATE … will be consolidated
into a single CREATE EXTENSION command in the dump, much the same as
with CREATE TABLE then ALTER TABLE … ADD COLUMN and the like.
Currently the option behavior is the following, that looks sane to me,
and is open for discussion: the dump's schema always include the CREATE
EXTENSION commands you need. The extensions listed in the -X option
(that you can use more than once) will get dumped with their's current
member objects in a script, inline.
To try the attached patch, you could do as following:
createdb foo
psql -c create extension hstore -d foo
pg_dump -X hstore -f /tmp/foo.sql foo
createdb bar
psql -1 -f /tmp/foo.sql -d bar
To be able to restore the dump, I've been adding some basic support to
the CREATE EXTENSION command so that it will find the data it needs from
the SQL command rather than the control file.
Note that the extension control file only contains information about how
to install an extension from a script file on disk. That's something we
don't need at all when installing the extension from a dump, using
either pg_restore or psql. We have some exceptions to that principle,
namely: requires (sets the search_path) and relocatable (found in the
catalogs, needs to survive dump/restore).
Given positive feedback on that way to attack the problem, the TODO list
includes:
- document the new pg_dump --extension-script switch
- add support for ALTER EXTENSION … WITH $$ script here $$;
The ALTER EXTENSION support is optional as far as pg_dump support goes,
it would be good to have it to make the User Interface complete.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
*** a/src/backend/commands/extension.c
--- b/src/backend/commands/extension.c
***
*** 75,80 typedef struct ExtensionControlFile
--- 75,81
bool superuser; /* must be superuser to install? */
int encoding; /* encoding of the script file, or -1 */
List *requires; /* names of prerequisite extensions */
+ char *script;
} ExtensionControlFile;
/*
***
*** 577,586 parse_extension_control_file(ExtensionControlFile *control,
}
/*
! * Read the primary control file for the specified extension.
*/
static ExtensionControlFile *
! read_extension_control_file(const char *extname)
{
ExtensionControlFile *control;
--- 578,587
}
/*
! * Create an ExtensionControlFile with default values.
*/
static ExtensionControlFile *
! default_extension_control_file(const char *extname)
{
ExtensionControlFile *control;
***
*** 593,598 read_extension_control_file(const char *extname)
--- 594,610
control-superuser = true;
control-encoding = -1;
+ return control;
+ }
+
+ /*
+ * Read the primary control file for the specified extension.
+ */
+ static ExtensionControlFile *
+ read_extension_control_file(const char *extname)
+ {
+ ExtensionControlFile *control = default_extension_control_file(extname);
+
/*
* Parse the primary control file.
*/
***
*** 858,866 execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
CurrentExtensionObject = extensionOid;
PG_TRY();
{
! char *c_sql = read_extension_script_file(control, filename);
Datum t_sql;
/* We use various functions that want to operate on text datums */
t_sql = CStringGetTextDatum(c_sql);
--- 870,883
CurrentExtensionObject = extensionOid;
PG_TRY();
{
! char *c_sql;
Datum t_sql;
+ if (control-script)
+ c_sql = control-script;
+ else
+ c_sql = read_extension_script_file(control, filename);
+
/* We use various functions that want to operate on text datums */
t_sql = CStringGetTextDatum(c_sql);
***
*** 1178,1183 void
--- 1195,1203
CreateExtension(CreateExtensionStmt *stmt)
{
DefElem*d_schema = NULL;
+ DefElem*d_script = NULL;
+ DefElem*d_requires = NULL;
+ DefElem*d_relocatable = NULL;
DefElem*d_new_version = NULL;
DefElem*d_old_version = NULL;
char *schemaName;
***
*** 1229,1248 CreateExtension(CreateExtensionStmt *stmt)
errmsg(nested CREATE EXTENSION is not supported)));
/*
! * Read the primary control file. Note we assume that it does not contain
! * any non-ASCII data, so there is no need to worry about encoding at this
! * point.
! */
! pcontrol =
