PostgreSQL 15.0 forward compatibility issue

[sternix]

Hi,

i upgrade my limesurvey database to PostgreSQL 14.3 -> 15.0 and have this error

SQLSTATE[42804]: Datatype mismatch: 7 ERROR: UNION types "char" and text cannot be matched LINE 21: CASE WHEN indisprimary THEN"

in this file,
yiisoft/yii/framework/db/schema/pgsql/CPgsqlSchema.php

i fix my error with casting conntype and values in CASE block to ::char
i have this error with PostgreSQL 15.0, PostgreSQL 14.3 is ok,

my working version,

SELECT
    conname,
    consrc,
    contype,
    indkey
FROM (
    SELECT
        conname,
        pg_catalog.pg_get_constraintdef(oid) AS consrc,
        contype::char,
        conrelid AS relid,
        NULL AS indkey
    FROM
        pg_catalog.pg_constraint
    WHERE
        contype IN ('f', 'c')
    UNION ALL
    SELECT
        pc.relname,
        NULL,
        CASE WHEN indisprimary THEN
                'p'
        ELSE
                'u'
        END,
        pi.indrelid,
        indkey
    FROM
        pg_catalog.pg_class pc,
        pg_catalog.pg_index pi
    WHERE
        pc.oid=pi.indexrelid
        AND EXISTS (
            SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c
            ON (d.refclassid = c.tableoid AND d.refobjid = c.oid)
            WHERE d.classid = pc.tableoid AND d.objid = pc.oid AND d.deptype = 'i' AND c.contype IN ('u', 'p')
    )
) AS sub
WHERE relid = (SELECT oid FROM pg_catalog.pg_class WHERE relname=:table
    AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace
    WHERE nspname=:schema))

Regards,

[marcovtwout]

@sternix Could be an issue with forward compatibility for Postgres 15, but could you please first edit your post and properly fill out the issue template (including simple code to reproduce)?

Is there anything in https://www.postgresql.org/docs/release/15.0/ that you can link to this particular issue?

[sternix]

What steps will reproduce the problem?

run script belove with psql

What is the expected result?

Success

What do you get instead?

Failure with

psql:a.sql:38: ERROR:  UNION types "char" and text cannot be matched
LINE 21:         CASE WHEN indisprimary THEN

message

Additional info

Q	A
Yii version	master
PHP version	8.0
Operating system	FreeBSD 13.1, Debian Linux 11

SELECT
    conname,
    consrc,
    contype,
    indkey
FROM (
    SELECT
        conname,
        pg_catalog.pg_get_constraintdef(oid) AS consrc,
        contype,
        conrelid AS relid,
        NULL AS indkey
    FROM
        pg_catalog.pg_constraint
    WHERE
        contype IN ('f', 'c')
    UNION ALL
    SELECT
        pc.relname,
        NULL,
        CASE WHEN indisprimary THEN
                'p'
        ELSE
                'u'
        END,
        pi.indrelid,
        indkey
    FROM
        pg_catalog.pg_class pc,
        pg_catalog.pg_index pi
    WHERE
        pc.oid=pi.indexrelid
        AND EXISTS (
            SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c
            ON (d.refclassid = c.tableoid AND d.refobjid = c.oid)
            WHERE d.classid = pc.tableoid AND d.objid = pc.oid AND d.deptype = 'i' AND c.contype IN ('u', 'p')
    )
) AS sub;

[sternix]

Hi @marcovtwout
i copied the sql below
https://github.com/yiisoft/yii/blob/master/framework/db/schema/pgsql/CPgsqlSchema.php#L233
for testing,
i read the release notes can't see directly related for this issue,

[sternix]

Hi,

my script run with

contype::char,

cast contype from "char" to char
https://github.com/yiisoft/yii/blob/master/framework/db/schema/pgsql/CPgsqlSchema.php#L242

it worked PostgreSQL 14.3, 14.5 and 15.0 versions.

[sternix]

Hi,
from https://www.postgresql.org/docs/current/datatype-character.html

The type "char" (note the quotes) is different from char(1) in that it only uses one byte of storage, and therefore can store only a single ASCII character. It is used in the system catalogs as a simplistic enumeration type.

for PostgreSQL
pg_constraint.contype is "char"
'p' and 'u' is text
if we want "char" type

'p'::"char"
'u'::"char"

is equivalent to pg_constraint.contype,

[sternix]

Hi, can be tested with,

create table tbla
(
    a "char"
);

create table tblb
(
    b char(1)
);

insert into tbla values ('a');
insert into tbla values ('b');

insert into tblb values ('c');
insert into tblb values ('d');

/*
fails both on 14.5 and 15.0
select a from tbla
union all
select b from tblb;
*/

-- works with 14.5 fails on 15.0
select a from tbla
union all
select
case when true then
    'p'
else
    'u'
end from tblb;

[sternix]

Hi, mailed this error to PostgreSQL mailing list, i have reply below,

https://www.postgresql.org/message-id/8bd63f844a2319a0199f09cfb424a7089587e264.camel%40cybertec.at

Yes, it is. See this paragraph from the release notes
(https://www.postgresql.org/docs/current/release-15.html#id-1.11.6.5.5.13):

• Create a new pg_type.typcategory value for "char" (Tom Lane)
  Some other internal-use-only types have also been assigned to this category.

Perhaps that should have been listed as a potential compatibility break, but
the documentation explicity says that "char" is not intended for use by the
end user (https://www.postgresql.org/docs/current/datatype-character.html):

These are not intended for general-purpose use, only for use in the internal system catalogs.

You will have to add an explicit type cast.

Yours,
Laurenz Albe

FYI,

[marcovtwout]

@sternix Thanks for the additional info from the mailing group above, that clearly demonstrates the issue. Casting to ::char seems like the best solution and backwards compatible.

Could you verify if #4511 solves your issue?

[sternix]

Hi @marcovtwout , thank you for reply, yes #4511 solved my issue,