Re: REVIEW: Extensions support for pg_dump - Mailing list pgsql-hackers

Anssi Kääriäinen <anssi.kaariainen@thl.fi> writes:
> Is this supposed to be used mainly by contrib and PGXN extensions? When I
> saw the documentation, I immediately thought that this is a nice way to
> package my application's stored procedures. If this is not one of the
> intended usages, it should be documented. I can see that this could be
> problematic when updating PostgreSQL and when recovering from backups.

Sure, private application's stored procedure are meant to be fully
supported by the extension's facility.

> When recovering from backup, you need to have the locally created extension
> available. But it might be that the extension files are lost when the system
> went down in flames. Now, the backup is unusable (right?) until extension
> files can be recovered from source control or where ever they might be
> stored. This is why I suggested having multiple locations for the
> extensions. It would be easy to backup locally created extensions if those
> were in a single directory. All in all, I have a nervous feeling that
> somebody someday will have an unusable dump because they used this feature,
> but do not have the extension files available...

Well, as said in the documentation, extensions are to be used for
objects you are *not* maintaining in your database, but elsewhere.
Typically you are maintaining your stored procedure code in some VCS,
and you have some "packaging" (cat *.sql > my-ext.sql in the Makefile
would be the simpler to imagine).

So yes if you tell PostgreSQL that your procedures are managed elsewhere
so that their code won't be part of your dumps, and then fail to manage
them anywhere else, you're hosed.

My viewpoint here is that when you want to use extensions, you want to
package them for your OS of choice (mine is debian, and I've been
working on easing things on this side too with pg_buildext to be found
in the postgresql-server-dev-all package).  If you're an occasional user
just wanting to use new shining facilities… well, think twice…

> Also, this part of documentation:
>
> The goal of using extensions is so that <application>pg_dump</> knows
> not to dump all the object definitions into your backups, but rather
> issue a single <xref linkend="SQL-CREATEEXTENSION"> command.

So maybe we want to extend this little sentence to add the warnings
around it, that if you're not packaging your extension's delivery to
your servers, you're likely shooting yourself in the foot?

> From that, it is not entirely clear to me why this is actually wanted in
> PostgreSQL. I suppose this will make dump/restore easier to use. But from
> that paragraph, I get the feeling the only advantage is that your dump will
> be smaller. And I still have a feeling this implements more. Not that it is
> a bad thing at all.

Well try to upgrade from 8.4 to 9.0 with some "extensions" installed in
there and used in your tables.  Pick any contrib, such as hstore or
ltree or cube, or some external code, such as ip4r or prefix or such.
Then compare to upgrade with the extension facility, and tell me what's
best :)

Hint: the dump contains the extension's script, but does not contain the shared object file.  If you're upgrading the
OSand the contribs, as you often do when upgrading major versions, you're hosed.  You would think that pg_upgrade
alleviatethe concerns here, but you still have to upgrade manually the extension's .so. 
 All in all, those extensions (contribs, ip4r, etc) are *not* maintained in your database and pretending they are by
puttingtheir scripts in your dumps is only building problems.  This patch aims to offer a solution here. 

>> It used to work this way with \i, obviously.  Should the extension patch
>> care about that and how?  Do we want to RESET search_path or to manually
>> manage it like we do for the client_min_messages and log_min_messages?
> It was unintuitive to me to have search_path changed by SQL command as a
> side effect. When using \i, not so much.

Agreed.  Will code the manual management way (that is already used for
log settings) later today, unless told to see RESET and how to do that
at the statement level rather than the transaction one.

> It is easy for me to continue from the Git repo. I will next continue doing
> the pg_dump part of the review. I hope I have time to complete that today.

Excellent, will try to continue following your pace :)

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support