Postgres Bug with FindConstraints (?)


(Jamesmbowler) #1

This is the error I get when trying to connect to the database, having set up table relations in my AR models.


CDbCommand failed to execute the SQL statement: SQLSTATE[42846]: Cannot coerce: 7 ERROR: UNION/INTERSECT/EXCEPT could not convert type character varying to "char". The SQL statement executed was: SELECT conname, consrc, contype, indkey FROM (

SELECT

conname,

CASE WHEN contype='f' THEN

pg_catalog.pg_get_constraintdef(oid)

ELSE

'CHECK (' || consrc || ')'

END 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

WHERE relid = (SELECT oid FROM pg_catalog.pg_class WHERE relname=:table

AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace

WHERE nspname=:schema)). Bound with :table='tbl_individual', :schema='public'

This sql is from CPgsqlSchema.php, in the findConstraints function. All of my constraints are integers.

It’s a Postgres error, as running the query in postgres gives the same error. The problem is with constructing the sql (I believe, I have almost no experience with Postgres).

Using latest version of Yii 1.*.

My OS is ubuntu 12.04, Postgres version is 9.1.15 (aws Redshift), PHP 5.4.39


(Jamesmbowler) #2

Fixed it. It’s a problem with the sql, line 8 should read


CAST(contype AS CHAR),

instead of just


contype

That was a fun couple hours.