Обсуждение: porting horde to Postgresql 12, dropped pg_attrdef
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
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
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
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