Re: porting horde to Postgresql 12, dropped pg_attrdef

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(at)webthatworks(dot)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