How to solve memory limit issue in Laravel migration?

Question

\Rpn\Services\Onv\Models\OnvForm\EmissionsStationarySource::select("onvos_request_emissions_stationary_sources.*")
            ->join('onvs', function ($join) {
                $join->on('onvs.service_request_id', '=', 'onvos_request_emissions_stationary_sources.service_request_id');
            })
            ->whereNotNull('geometry')
            ->chunk(1000, function ($stationaries) {
                \DB::transaction(function () use ($stationaries) {
                    $layer = \Rpn\Services\Map\Models\MapLayer::MAP_LAYER_STATIONARY;
                    $type = \Rpn\Services\Onv\Models\OnvForm\EmissionsStationarySource::class;
                    /** @var \Rpn\Services\Onv\Models\OnvForm\EmissionsStationarySource $stationary */
                    foreach ($stationaries as $stationary) {
                        $id = $stationary->id;

                        if (empty($stationary->geometry)) {
                            continue;
                        }

                        $geo = json_encode($stationary->geometry);

                        try {
                            $point = \GeoJson\GeoJson::jsonUnserialize($stationary->geometry);
                        } catch (\Throwable $e) {
                            continue;
                        }

                        \DB::statement("
                            insert into map_objects(map_layer_id, model_type, model_id, geometry, created_at, updated_at)
                            values(${layer}, '${type}', ${id}, ST_MakeValid(ST_GeomFromGeoJSON('${geo}')), now(), now())
                            on conflict do nothing;
                        ");
                    }
                });
            });

The following code gives me a memory limit error (the allowed memory size of 2147483648 bytes has been exhausted). Why does it work this way even though I'm using a cursor and a block? How can I fix it?

Answer 1

If you want to know more about how to fix memory limits, it's half-answered in this answer . Depending on the operating system it's running on, you just need to adjust the position accordingly.

If you ask what is going on internally, there could be several scenarios. Yes, you are chunking the data, but it's hard to tell just from the code without debugging (I personally would patch the problem).

Maybe it’s something like you

if (empty($stationary->geometry)) {
  continue;
}

When you have previously checked where geometry is not null. Honestly, it can transform it into anything. Loops in SQL are slow because SQL is setup based, however, it's also possible to just get the results and process them in memory.

Also need to remember that you are running an insert statement every iteration, which can also be laborious.