Thread: Array detection in pg_dump
Is there a reason we don't use pg_type.typcategory to detect arrays in Postgres 8.4? Right now I see this in pg_dump.c: if (g_fout->remoteVersion >= 80300) {appendPQExpBuffer(query, "SELECT tableoid, oid, typname, " "typnamespace," "(%s typowner) AS rolname, " "typinput::oid AS typinput, " "typoutput::oid AStypoutput, typelem, typrelid, " "CASE WHEN typrelid = 0 THEN ' '::\"char\" " "ELSE (SELECT relkind FROMpg_class WHERE oid = typrelid) END AS typrelkind, " "typtype, typisdefined, " "typname[0] = '_' ANDtypelem != 0 AND " --> "(SELECT typarray FROM pg_type te WHERE oid = pg_type.typelem) = oid AS isarray " "FROM pg_type", username_subquery); } It seems the appropriate 8.4+ test would be: t.typtype = 'b' AND t.typcategory = 'A' -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes: > Is there a reason we don't use pg_type.typcategory to detect arrays in > Postgres 8.4? Right now I see this in pg_dump.c: typcategory is user-assignable and thus not too reliable; furthermore it wouldn't prove that the type is the array type for its typelem. (Consider things like point and name --- there can be multiple types with the same typelem, but only one is the real array type for that typelem.) The typelem back-link check is much safer. regards, tom lane
Bruce Momjian wrote: > Is there a reason we don't use pg_type.typcategory to detect arrays in > Postgres 8.4? Right now I see this in pg_dump.c: > > if (g_fout->remoteVersion >= 80300) > { > appendPQExpBuffer(query, "SELECT tableoid, oid, typname, " > "typnamespace, " > "(%s typowner) AS rolname, " > "typinput::oid AS typinput, " > "typoutput::oid AS typoutput, typelem, typrelid, " > "CASE WHEN typrelid = 0 THEN ' '::\"char\" " > "ELSE (SELECT relkind FROM pg_class WHERE oid = typrelid) END AS typrelkind, " > "typtype, typisdefined, " > "typname[0] = '_' AND typelem != 0 AND " > --> "(SELECT typarray FROM pg_type te WHERE oid = pg_type.typelem) = oid AS isarray " ^^ Oh, and what does that 'te' do? Seems useless. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Is there a reason we don't use pg_type.typcategory to detect arrays in > > Postgres 8.4? Right now I see this in pg_dump.c: > > typcategory is user-assignable and thus not too reliable; furthermore > it wouldn't prove that the type is the array type for its typelem. > (Consider things like point and name --- there can be multiple types > with the same typelem, but only one is the real array type for that > typelem.) The typelem back-link check is much safer. Thanks; I will use the pg_dump code in pg_migrator then. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > Bruce Momjian wrote: > > Is there a reason we don't use pg_type.typcategory to detect arrays in > > Postgres 8.4? Right now I see this in pg_dump.c: > > > > if (g_fout->remoteVersion >= 80300) > > { > > appendPQExpBuffer(query, "SELECT tableoid, oid, typname, " > > "typnamespace, " > > "(%s typowner) AS rolname, " > > "typinput::oid AS typinput, " > > "typoutput::oid AS typoutput, typelem, typrelid, " > > "CASE WHEN typrelid = 0 THEN ' '::\"char\" " > > "ELSE (SELECT relkind FROM pg_class WHERE oid = typrelid) END AS typrelkind, " > > "typtype, typisdefined, " > > "typname[0] = '_' AND typelem != 0 AND " > > --> "(SELECT typarray FROM pg_type te WHERE oid = pg_type.typelem) = oid AS isarray " > > ^^ > > Oh, and what does that 'te' do? Seems useless. Oh, I see it now; 'te' makes the pg_type reference in the subquery reference the outer pg_type; Wow that is confusing. I will clear that up in my pg_migrator version. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +