Greetings all,
While testing pg_dump, I discovered that there seems to be an issue when
it comes to TRANSFORMs. I'll be the first to admit that I'm not
terribly familiar with transforms, but I do know that if you create one
using functions from pg_catalog (as our regression tests do), the CREATE
TRANSFORM statement isn't dumped out and therefore we don't test the
dump/restore of transforms, even with our current pg_upgrade test
process.
In the regression tests, we do:
CREATE TRANSFORM FOR int LANGUAGE SQL ( FROM SQL WITH FUNCTION varchar_transform(internal), TO SQL WITH
FUNCTIONint4recv(internal));
but if you do that and then run pg_dump, you don't see any mention of
any transforms in the dump file.
This is because there's a couple of checks towards the top of
dumpTransform():
/* Cannot dump if we don't have the transform functions' info */ if (OidIsValid(transform->trffromsql)) {
fromsqlFuncInfo = findFuncByOid(transform->trffromsql); if (fromsqlFuncInfo == NULL) return; } if
(OidIsValid(transform->trftosql)) { tosqlFuncInfo = findFuncByOid(transform->trftosql); if
(tosqlFuncInfo== NULL) return; }
These checks are looking for the functions used by the transform in the
list of functions that pg_dump has loaded, but in 9.5, we don't load any
of the function in pg_catalog, and even with my patches, we only dump
the functions in pg_catalog that have an ACL which has been changed from
the default.
Given that there are lots of functions in pg_catalog, we probably don't
want to just load them all, all the time, but what we should probably do
is grab any functions which are depended on by any transforms. Or we
could change dumpTransform() to actually issue a query to get the
function information which it needs when we've decided to dump a given
transform.
For my 2c, this is a bug that needs to be fixed and back-patched to 9.5.
I'm going to continue working on my pg_dump test suite for now, but I
can look at fixing this after PGCon if no one else fixes it first.
Thanks!
Stephen