Did anybody manage to migrate October on PostgreSQL from 1.x to 2.x? I can migrate but the SQL generates a lot of errors when accessing system_files table. For example, accessing user detail view in the backend crashes on the query

select
    *
from
    "system_files"
where
    ("system_files"."attachment_type" = RainLab\User\Models\User
        and "system_files"."attachment_id" = 146
        and "field" = avatar
        and "system_files"."attachment_id" is not null
        or system_files.id in (
        select
            "slave_id"
        from
            "deferred_bindings"
        where
            "master_field" = avatar
            and "master_type" = RainLab\User\Models\User
            and "session_key" = jEU39ssZygWgFp8wq1nuPuCseU0vTqbifAbgWmjQ
            and "is_bind" = 1))
    and system_files.id not in (
    select
        "slave_id"
    from
        "deferred_bindings"
    where
        "master_field" = avatar
        and "master_type" = RainLab\User\Models\User
        and "session_key" = jEU39ssZygWgFp8wq1nuPuCseU0vTqbifAbgWmjQ
        and "is_bind" = 0
        and id > coalesce((select max(id) from deferred_bindings where slave_id = system_files.id and master_field = avatar and master_type = RainLab\User\Models\User and session_key = jEU39ssZygWgFp8wq1nuPuCseU0vTqbifAbgWmjQ and is_bind = 1 ), 0))
order by
    "sort_order" asc

or, when opening a blog editor in the backend crashes on the query

select
    *
from
    "system_files"
where
    ("system_files"."attachment_type" = RainLab\Blog\Models\Post
        and "system_files"."attachment_id" is null
        and "field" = featured_images
        and "system_files"."attachment_id" is not null
        or system_files.id in (
        select
            "slave_id"
        from
            "deferred_bindings"
        where
            "master_field" = featured_images
            and "master_type" = RainLab\Blog\Models\Post
            and "session_key" = 7 Z6KhizdveeDpwfZhePyJvHwyi3lOcr83Isah0jo
            and "is_bind" = 1))
    and system_files.id not in (
    select
        "slave_id"
    from
        "deferred_bindings"
    where
        "master_field" = featured_images
        and "master_type" = RainLab\Blog\Models\Post
        and "session_key" = 7 Z6KhizdveeDpwfZhePyJvHwyi3lOcr83Isah0jo
        and "is_bind" = 0
        and id > coalesce((select max(id) from deferred_bindings where slave_id = system_files.id and master_field = featured_images and master_type = RainLab\Blog\Models\Post and session_key = 7 Z6KhizdveeDpwfZhePyJvHwyi3lOcr83Isah0jo and is_bind = 1 ), 0))
order by
    "sort_order" asc

IMO the master_type and session_key are varchars and should be enclosed in apostrophes.

Hey nullpointer,

You may need to apply the patch as mentioned in the upgrade guide: https://octobercms.com/support/article/rn-13#database-optimized-keys

You can switch these keys to integers manually (preferred) or run the patch command to try an automated process.

php artisan october:util patch 2.0

I hope this helps

Last updated 3 years ago

Thank you, that helped. One should read the instructions until the end before starting the upgrade.