Date: Wed, 4 May 2016 14:39:52 -0400
From: Stephen Frost <sfrost@snowman.net>
To: pgsql-hackers@postgresql.org
Subject: pg_dump vs. TRANSFORMs
Message-ID: <20160504183952.GE10850@tamriel.snowman.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="6wksViTHwOHy2myV"
Content-Disposition: inline
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


--6wksViTHwOHy2myV
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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 FUNCTION int4recv(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))
    {  =20
        fromsqlFuncInfo =3D findFuncByOid(transform->trffromsql);
        if (fromsqlFuncInfo =3D=3D NULL)
            return;
    }
    if (OidIsValid(transform->trftosql))
    {  =20
        tosqlFuncInfo =3D findFuncByOid(transform->trftosql);
        if (tosqlFuncInfo =3D=3D 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

--6wksViTHwOHy2myV
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJXKkH3AAoJEO1sijiDR2RVODIP/RnmVMLEZ8eOyY0XH/dw3cJr
zt8NiKsjc20t7TlR1I/5Ky52Wmlm5ijHKxLci29EoMcGffKokvxDUSX5v+5gjtsN
PTkaLrlEdsgdycOSAHlh6Om7y8qNcA0yUYUxSOwg2erMNz+I3Nqv/iX3fNFLMZ8c
qqA5UmkElA0wxAELq7DbzmxSExYyz1MYLK04dW91NFPzBXv4aK6WYkePao7REzk/
PbvvZsybZPl09HZpxowGrIhpzyOoviA9AQiUM1Oa9THkUew/3dMsNcJZ11XjfkqQ
YVK2WCIy8rVCDJ5oRgGoxvDgbrLyxRfc885CvhjzxwCgLvGb+Y5cZuORP3xZVUN+
P/gG5D/2MLpFqNZYoAlQrSwmf215LD5veDGKpyRSYAPf16dr7CCjKsxn1ib4+FYG
4UMJBaJdYjsCm6Q7j9v8sZp/jpWjjE3Iu4Xi4I9TDscI+MzHdUyiTAB4fYh5BT6l
Ng7rAp4IMz2PmHd2ozJg9d0B+d99KDqPxgg2FnWIAD7PVPQ4Wr/ZOfENF16Ohftk
B+sqZMzbCuMmwJ1CpIbO/nig/6b6Ru8bcYMeHs9KWqzAjtsV5RzTP/do0KUfj25C
QNsBV4u4gKjy4f0GytvJfASUdA5BDc23JefQgdvb/Eqef6lHA3LrRqkPcdVgUYZN
vibvRXSOUVgWCmJ5e8yy
=uaIK
-----END PGP SIGNATURE-----

--6wksViTHwOHy2myV--