Months ago, I woke up with a fantastic idea: adding an option in
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
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
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!
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
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
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.
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.