Re: porting horde to Postgresql 12, dropped pg_attrdef

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.

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.

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.

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

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)

Thanks

> I see that the v12 release notes fail to suggest this replacement,
> which seems unhelpful ... I'll go add that.
>
> Having said that, it looks like query (1) should never have been
> written like this in the first place --- trying to pull bits out
> of the text representation of a default expression seems horribly
> fragile. I think what it's trying to do is identify serial columns?
> The right way to do that is to look for pg_depend entries that link
> a serial sequence to its owning column. I don't have an example
> right to hand, but I'm sure there is an instance in pg_dump, and
> probably elsewhere.

--
Ivan Sergio Borgonovo
https://www.webthatworks.it https://www.borgonovo.net