Unable to migrate OctoberCMS on Postgres database

[envas]

• Winter CMS Build: 1.1.3 (currently latest)
• PHP Version: 7.3.18
• Database Engine: PostgreSQL server version 11.6

Description:

I performed the migration from OctoberCMS build 472 using composer update. On the development system with MySQL everything was ok. On the stage system with Posgres database the upgrade process was ok (no errors), however, I cannot open any page. The exception I get is

SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: character varying = integer LINE 1: ...ystem_files" where "system_files"."attachment_id" in (1, 2) ... ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts. (SQL: select * from "system_files" where "system_files"."attachment_id" in (1, 2) and "system_files"."attachment_type" = RainLab\Blog\Models\Post and "field" = featured_images order by "sort_order" asc)

Looking at the table the reason is: on the database the attribute system_files.attachment_id is of type varchar and this type cannot be directly compared to integer:

select * from "system_files" 
 where "system_files"."attachment_id" in (1, 2) 
       and "system_files"."attachment_type" = "RainLab\Blog\Models\Post" 
       and "field" = featured_images 
order by "sort_order" asc

returns on the plain Postgres database

ERROR:  operator does not exist: character varying = integer
LINE 2:  where "system_files"."attachment_id" in (1, 2)

Probably Winter issue because with October everything worked fine.

[envas]

A note to the error - it occurs whenever blog entries are listed. So it looks like a bug related to the RainLab.Blog plugin. Anyway, it occurs while accessing the system_files table, which belongs IMO to the Winter core.

[envas]

Looks like a duplicate of wn-blog-plugin issue

[RomainMazB]

Hi @envas,
Yes, I can confirm this issue is a duplicate of wintercms/wn-blog-plugin#16 .
As said in the other thread, I fixed it in the past but somebody mentioned a side effects.

You can confirm this issue is related by disabling the eager loading of featured images into the Posts component. This will be properly fixed soon.

This line is causing the trouble: https://github.com/wintercms/wn-blog-plugin/blob/1eb285add38159365c522d7124c3719fe553c926/components/Posts.php#L205

[bennothommo]

Duplicate of wintercms/wn-blog-plugin#16.

Will be fixed by #216 when it's merged.