yii: Updated postgres 12 query for findColumns function returns wrong sequence name in the result

Postgres 12 updated the findColumns function and the following query when run for a database table whose name and sequence name were updated after it was created returns old sequence name when this query gets executed.

SELECT a.attname, LOWER(format_type(a.atttypid, a.atttypmod)) AS type, d.adsrc AS column_def_value, a.attnotnull, a.atthasdef, pg_catalog.col_description(a.attrelid, a.attnum) AS comment FROM pg_attribute a LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum WHERE a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname=:table AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = :schema)) ORDER BY a.attnum

Old query that gets generated by findColumns function before postgres12 upgrade code was added gives the right sequence name for the same table

SELECT a.attname, LOWER(format_type(a.atttypid, a.atttypmod)) AS type, CAST(pg_get_expr(d.adbin, d.adrelid) AS varchar) AS adsrc, a.attnotnull, a.atthasdef, pg_catalog.col_description(a.attrelid, a.attnum) AS comment FROM pg_attribute a LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum WHERE a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname=:table AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = :schema)) ORDER BY a.attnum

PHP version being used 7.2.31 Postgres 11.8 OS: CentOS Linux release 7.7.1908 (Core), Ubuntu 20.04 LTS

https://github.com/yiisoft/yii/blob/09672fe0754eb939bafaf28a13b9dcad4bfaca0e/framework/db/schema/pgsql/CPgsqlSchema.php#L172

About this issue

  • Original URL
  • State: closed
  • Created 4 years ago
  • Comments: 18 (11 by maintainers)

Most upvoted comments

I know this issue is closed, but I’ve tested 5 Yii 1.1 applications in our organization with PostgreSQL 13 as a database backend and they all work fine. Thanks!

I will try to understand both queries and come up with a solution.

On Fri, Jul 3, 2020 at 7:31 AM Alexander Makarov notifications@github.com wrote:

Do you have time to prepare a fix?

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/yiisoft/yii/issues/4302#issuecomment-653575194, or unsubscribe https://github.com/notifications/unsubscribe-auth/AC4LNUHFIJSTWYYS6PHQ7JDRZXTUNANCNFSM4OOLV2EQ .