Re: porting horde to Postgresql 12, dropped pg_attrdef - Mailing list pgsql-general

On Mon, 2019-11-18 at 01:29 +0100, Ivan Sergio Borgonovo wrote:
> On 11/15/19 4:16 PM, Tom Lane wrote:
> > Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> > > Horde (webmail and more) is not anymore compatible with postgresql 12
> > > after pg_attrdef was dropped.
> > 
> > We didn't drop the whole catalog, just the adsrc column, which has been
> > deprecated for ages (because it didn't update to track, eg, column
> > renamings).  The correct substitute for adsrc is
> > 
> > pg_get_expr(adbin,adrelid)
> > 
> > which reconstructs an up-to-date text form from the authoritative
> > data.
> 
> Thanks, that's a good starting.
> Unfortunately even using that function requires a deeper understanding 
> of postgresql internals.
> 
> I've found just examples that use pg_class table.
> I'm guessing I should join information_schema.columns and pg_class, but 
> I don't know from where to start.

The change should be pretty straightforward.
For example, your first query would become:

SELECT attr.attname,
   CASE
     WHEN split_part(def.adsrc, '''', 2) ~ '.' THEN
          substr(split_part(def.adsrc, '''', 2),
          strpos(split_part(def.adsrc, '''', 2), '.')+1)
     ELSE split_part(def.adsrc, '''', 2)
     END AS relname
FROM pg_class       t
JOIN pg_attribute   attr ON (t.oid = attrelid)
JOIN (SELECT adrelid, 
             adnum, 
             pg_get_expr(adbin, adrelid) AS adsrc 
      FROM pg_attrdef
     ) AS def ON (adrelid = attrelid AND adnum = attnum)
JOIN pg_constraint  cons ON (conrelid = adrelid AND adnum = conkey[1])
WHERE t.oid = '<tablename>'::regclass
AND cons.contype = 'p'
AND def.adsrc ~* 'nextval';

It would be *much* better to use the "pg_get_serial_sequence" function.

I think that the limitation to single-column primary keys is, er, unfortunate.

> While I do understand that since every DB may have it's own data type 
> and of course you may have to use a function that may not be part of the 
> standard using pg_get_expr() doesn't seem to help that much in isolating 
> postgresql internals or hiding them as much as possible.

I think that this is not an attempt at hiding internals, on the contrary:
it makes the internals palatable.

> For query 1) if there is no better way I'd prefer to use information 
> schema and just pg_get_serial_sequence(). That will also get rid of 
> messing up with strings. Any better way that completely avoid postgresql 
> specific functions will be appreciated.

Yes, that would certainly be better.
The tables in pg_catalog can change from version to version.

I think that it will be difficult to avoid PostgreSQL-specific constructs
to get the sequence name behind a serial column, since both "serial" and
"nextval" are PostgreSQL specific.

The canonical way from a column to the associated sequence is via
dependencies in "pg_depend", which is a PostgreSQL-specific thing too.

> For query 2) I didn't find enough clues on how to use pg_get_expr().

SELECT a.attname, format_type(a.atttypid, a.atttypmod),
       pg_get_expr(d.adbin, d.adrelid) AS adsrc,
       a.attnotnull
FROM pg_attribute a
LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attrelid = '<tablename>'::regclass
       AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

> If I could avoid using pg_get_expr() I'd have one more concern left 
> since the original query returns
> 
> nextval('public.horde_alarms_id_seq'::regclass)
> 
> while interrogating information_schema.columns returns
> 
> nextval('horde_alarms_id_seq'::regclass)

That is because of your "search_path" setting.
Schemas are only shown if they are *not* on your "search_path".
You can set "search_path" to an empty string to always get the schema.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com