I think the problem here is that the code treats a raster with no SRS
differently than a raster with SRS where reprojection will NOT take place
(essentially, same Cartesian plane for source raster and destination
raster).
The key offending(?) line is
https://github.com/postgis/postgis/blob/svn-tru
The comment that an unset SRID forcing a transformation to happen makes sense.
You stated that the following would fail because
GDALCreateGenImgProjTransformer2 would just return null, and null can’t be
transformed into a raster.
So the following fails, we have seen that.
SELECT
ST_Resample(