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 ispg_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 ispg_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 returnsnextval('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