Months ago, I woke up with a fantastic idea: adding an option in pg_dump to export stored functions and procedures only. It happened that, as a consultant, I sometimes need weird tools like that to better understand what my customers did.

So I wrote it down in my todo list (46 items, 7 checked) and I left it there waiting for a time when I’ll be free enough to take care of it.

Today, I finally thought that that time had come.

My patch process

Normally, when I want to write a patch I do the following:

• Inspect globally the code to find out what I’ll change and where
• Write the documentation
• Write my patch
• Write the test
• Test the patch

So I was inspecting the code (mainly pg_dump.c and common.c under src/bin/pg_dump), when I found out that the code was first listing all the objects to dump then exploring the dependencies between them.

     /*
     * Now scan the database and create DumpableObject structs for all the
     * objects we intend to dump.
     */
     tblinfo = getSchemaData(fout, &numTables);
[...]
     /*
     * Collect dependency data to assist in ordering the objects.
     */
     getDependencies(fout);

That ringed a bell!

Function’s dependencies

I helped a colleague write a documentation patch some years ago because using a function in a check constraint could lead to pg_dump generating a nonrestorable file, should the function refer to another table. (See the whole discussion thread here).

That’s when I found out that pg_dump could take care of well declared dependencies in SQL (like a table being created inside a schema or a foreign key linking two tables) but that it could not take care of a dependency like the one written inside a stored function (like a function using SQL code inside its own code to select inside a table, for example).

And that’s how I found out my idea of extracting stored functions and stored procedures was a bad idea.

As pg_dump does not check the code of functions and procedures to find out if it needs other objects, my pg_dump exporting only those objects will likely create a file that will create invalid objects when restored. It does not feel like a good idea!

So, this whole idea I thought was brilliant was a bad idea that could have made Postgres worse instead of better.

The next logical thing to do is to get rid of this todo list item and move on to the next one.