Nocache Regions incompatible with Postgres

[macaws]

Bug description

Hello, I've been working to migrate our project from MySQL to Postgres and we hit an issue with nocache_regions - the data gets truncated every time, essentially corrupting the region.

After a bit of time playing around with it, I've discovered that it is getting truncated where there are null byte characters (\x00). This causes the frontend to error when loading the region as it has corrupted.

There are two possible solutions:

• Use the "binary" field for Postgres - I was able to get this working locally with the following:

//src/StaticCaching/NoCache/DatabaseSession.php
// ...
    public function region(string $key): Region
    {
        $region = DatabaseRegion::where('key', $key)->first();

        if (! $region) {
            throw new RegionNotFound($key);
        }

        return $region->region;
    }

    protected function cacheRegion(Region $region)
    {
        DatabaseRegion::updateOrCreate([
            'key' => $region->key(),
        ], [
            'url' => $this->url,
            'region' => $region,
        ]);
    }
//src/StaticCaching/NoCache/DatabaseRegion.php

    public function getRegionAttribute($value)
    {
        if (is_resource($value)) {
            $value = stream_get_contents($value);
        }
        return unserialize($value);
    }

    public function setRegionAttribute($value): void
    {
        // For PostgreSQL, we need to properly format binary data
        $this->attributes['region'] = match ($this->getConnection()->getDriverName()) {
            'pgsql' => '\\x'.bin2hex(serialize($value)),
            default => serialize($value)
        };
    }

• Base64 encode/decode the region before accessing or storing in the DB - I was able to successfully get this working locally with the following:

//src/StaticCaching/NoCache/DatabaseSession.php
    public function region(string $key): Region
    {
        $region = DatabaseRegion::where('key', $key)->first();

        if (! $region) {
            throw new RegionNotFound($key);
        }

        return unserialize(base64_decode($region->region));
    }

    protected function cacheRegion(Region $region)
    {
        DatabaseRegion::updateOrCreate([
            'key' => $region->key(),
        ], [
            'url' => $this->url,
            'region' => base64_encode(serialize($region)),
        ]);
    }

The drawback of base64 may be a small performance cost - though I have yet to confirm if it's big enough to matter.

Ultimately, this is a large structural decision that I'm hoping the Statamic team can input on - happy to PR something from above in with guidance/preference.

How to reproduce

Set up a nocache_regions table on a postgres database

Logs

Environment

Application Name: <>
Laravel Version: 12.21.0
PHP Version: 8.4.10
Composer Version: 2.8.10
Environment: local
Debug Mode: ENABLED
URL: <>.test
Maintenance Mode: OFF
Timezone: UTC
Locale: en

Cache
Config: CACHED
Events: CACHED
Routes: CACHED
Views: CACHED

Drivers
Broadcasting: log
Cache: redis
Database: pgsql
Logs: stack / single, flare
Mail: smtp
Queue: redis
Scout: meilisearch
Session: file

Storage
public/icons: NOT LINKED
public/storage: NOT LINKED

Pulse
Enabled: ENABLED
Version: v1.4.3

Livewire
Livewire: v3.6.4

Spatie Permissions
Features Enabled: Default
Version: 6.21.0

Statamic
Addons: 12
Sites: 1
Stache Watcher: Disabled
Static Caching: Disabled
Version: 5.61.0 PRO

Statamic Addons
aryehraber/statamic-uuid: 2.3.0
edge/powerbi-embed: dev-master
eminos/statamic-set-header-toggle: 1.1.0
eminos/statamic-tabs: 1.2.0
jacksleight/statamic-bard-mutator: 3.0.3
mitydigital/iconamic: 2.2.3
pecotamic/sitemap: 1.4.9
rias/statamic-redirect: 3.12.0
statamic-rad-pack/meilisearch: 3.4.0
statamic-rad-pack/runway: 8.5.2
statamic/eloquent-driver: 4.30.0

Statamic Eloquent Driver
Asset Containers: file
Assets: eloquent
Blueprints: eloquent
Collection Trees: eloquent
Collections: eloquent
Entries: eloquent
Fieldsets: file
Form Submissions: eloquent
Forms: eloquent
Global Sets: eloquent
Global Variables: eloquent
Navigation Trees: eloquent
Navigations: eloquent
Revisions: eloquent
Sites: file
Taxonomies: eloquent
Terms: eloquent
Tokens: eloquent

Installation

Fresh statamic/statamic site via CLI

Additional details

No response

[ryanmitchell]

Isnt the solution to remove null byte characters before inserting? It seems to be postgres specific as it doesnt handle them in string fields, but as its a hidden character it wont make a difference to other engines.

[macaws]

I suppose so but no clue what happens when you remove characters from the string, hence why I have not PRed any of these.

[macaws]

Hey @jasonvarga if you could provide technical direction here, I'd be happy to PR!

We're needing this for a project now and the nocache_regions has become quite problematic for us outside of DB :)