Thread

Commits

Doc: in v12 release notes, explain how to replace uses of consrc and adsrc.
- d61e7f174db9 12.2 landed

porting horde to Postgresql 12, dropped pg_attrdef

Ivan Sergio Borgonovo <mail@webthatworks.it> — 2019-11-15T14:56:27Z

Horde (webmail and more) is not anymore compatible with postgresql 12 
after pg_attrdef was dropped.

Since I'm a Horde user and I've always liked PostgreSQL I'm trying to 
update these queries

1)

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 pg_attrdef     def  ON (adrelid = attrelid AND adnum = attnum)
JOIN pg_constraint  cons ON (conrelid = adrelid AND adnum = conkey[1])
WHERE t.oid = '$table'::regclass
AND cons.contype = 'p'
AND def.adsrc ~* 'nextval';

This result eg in

  attname |       relname
---------+---------------------
  id      | horde_alarms_id_seq


2)

SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.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 = ' . $this->quote($tableName) . '::regclass
       AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum

This result eg in

attname | format_type | adsrc | attnotnull
id | integer | nextval('public.horde_alarms_id_seq'::regclass) | t

As for 1)

I think I can replicate the first query mixing up:

SELECT c.column_name, c.ordinal_position
FROM information_schema.key_column_usage AS c
LEFT JOIN information_schema.table_constraints AS t
   ON t.constraint_name = c.constraint_name
WHERE
   t.table_name = '<table_name>' AND t.constraint_type = 'PRIMARY KEY';

select pg_get_serial_sequence('<table_name>', '<column_name>')

But it would be nice to avoid something postgres specific to retrieve 
the sequence name. Is there any alternative to pg_get_serial_sequence()?


The 2) query is used to fill a PHP object that seems to be used to 
"recreate" the query to create the table.
I can get most of what I need from information_schema.columns but I was 
wondering if there is a way to come up with actual "type definition" as 
format_type() or pg_typeof().

Furthermore querying the information_schema.columns return
nextval('horde_alarms_id_seq'::regclass)

in spite of

nextval('public.horde_alarms_id_seq'::regclass)

and I wonder if it is going to make a difference and if there is a way 
to totally mimic the result of the original query.

Thanks

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

Re: porting horde to Postgresql 12, dropped pg_attrdef

Tom Lane <tgl@sss.pgh.pa.us> — 2019-11-15T15:16:59Z

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.

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.

			regards, tom lane

Re: porting horde to Postgresql 12, dropped pg_attrdef

Ivan Sergio Borgonovo <mail@webthatworks.it> — 2019-11-18T00:29:44Z

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.

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

Re: porting horde to Postgresql 12, dropped pg_attrdef

Laurenz Albe <laurenz.albe@cybertec.at> — 2019-11-18T10:22:42Z

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