Re: findoidjoins

Tom Lane wrote:
> For what we want it for (viz, regenerating the oidjoins test every so
> often), this is really a step backwards. It requires more work to run
> than the original program, and it modifies the database under test,
> which is undesirable because it's commonly run against template1.
>
> I was thinking of keeping it as a client program, but recasting it to
> use libpq since libpgeasy isn't in the standard distribution anymore.

OK. I'll take another shot using that approach. A couple questions:

Is it useful to have the reference count and unreferenced counts like
currently written, or should I just faithfully reproduce the original
behavior (except schema aware queries) using libpq in place of libpgeasy?

Is the oidjoins.sql test just the output of the make_oidjoins_check
script? It is probably easier to produce that output while looping
through the first time versus running a script -- should I do that?

> I've looked through my notes and I can't find why I thought findoidjoins
> was broken for 7.3. Did you come across anything obviously wrong with
> its queries?

As written the queries did not know anything about schemas or the newer
reg* data types, e.g. this:

SELECT typname, relname, a.attname
FROM pg_class c, pg_attribute a, pg_type t
WHERE a.attnum > 0 AND
relkind = 'r' AND
(typname = 'oid' OR
typname = 'regproc' OR
typname = 'regclass' OR
typname = 'regtype') AND
a.attrelid = c.oid AND
a.atttypid = t.oid
ORDER BY 2, a.attnum ;

became this:

SELECT c.relname,
(SELECT nspname FROM pg_catalog.pg_namespace n
WHERE n.oid = c.relnamespace) AS nspname,
a.attname,
t.typname
FROM pg_catalog.pg_class c,
pg_catalog.pg_attribute a,
pg_catalog.pg_type t
WHERE a.attnum > 0 AND c.relkind = 'r'
AND t.typnamespace IN
(SELECT n.oid FROM pg_catalog.pg_namespace n
WHERE nspname LIKE 'pg\\_%')
AND (t.typname = 'oid' OR t.typname LIKE 'reg%')
AND a.attrelid = c.oid
AND a.atttypid = t.oid
ORDER BY nspname, c.relname, a.attnum

Does the latter produce the desired result?

Joe