Postgres layer with multi geometry collumns fails to export

[schorschlGEO]

What is the bug or the crash?

Dear community,

since the upgrade to "newer" QGIS versions I have problems exporting postgre layers to vector files. An error message occurs when exporting files (gpkg, shp, etc...) from a postgres database. The database and the data model has not changed.

It looks as if QGIS handles the individual columns and their formatting differently than before.
For example, I want to clip a layer of the database to a mask layer and save it directly as a vector file in my file system. By setting the storage location directly, e.g. in the “Clip” algorithm, the process runs into this error:

Could not create layer C:/Users/user/Desktop/QGIS/DBBugTest/ax_flurstueck_clip_test.gpkg: 
Unsupported type for field objektkoordinaten
Execution failed after 0.39 seconds

This error also occurs with other algorithms (e.g. buffer, merge, etc...). The error occurs with all data types to be exported (.shp, geojson etc...).
The exported file is written to the file system (significantly too small) and cannot be opened or used in any other way.

By saving the output temporarily in “clipped”, the result is displayed correctly and the attribute table is transferred correctly. But the same error occurs when exporting the temporary file.

I discovered a workaround for this:
The temp file can only be saved if “Replace all selected raw field values by displayed values” is checked in the export window (see screenshot). However, this changes the formatting type of the column from geometry to string.
This changes the original data.
In addition, batch processing of the data cannot be executed as the error also occurs here and cancels the processes. In particular, I need the batch processing to perform certain tasks...

I have tested this with several QGIS versions: 3.28.5, 3.28.6, 3.28.8, 3.32.0, 3.34.6 and 3.36.2. In the field "Versions" down here i just copied the specs of QGIS version 3.22.1 and 3.32.0. The database and the data itselfe has not been changed.
The last QGIS version in which this error does not occur is version 3.22.1.

QGIS 3.22.1 automatically changes the formatting of the column to “string”. Batch processing works perfectly in this version
QGIS 3.32.0 only changes the formatting if specified during export. Otherwise the above error occurs.

Besides the conversation in #53892 #52225 and #53546 it is still NOT possible to perform batch processing without the particular error besides using standard algorithms such as clip, buffer, etc...

Is it possible to keep the column formatting “Geometry” when saving? Why does QGIS 3.22.1 change the column formatting to “string”?
And if multi geometrys cannot be safed in vector data is there a possibility that QGIS Versions above 3.22.1 convert it to "sting"?

Thank you all for helping!

Steps to reproduce the issue

1. Get a layer on a database with a multi geometry column (see screenshot above)
2. Create a mask layer (.gpkg, .shp, etc...)
3. Use (e.g.) the clip algorithm and safe the output directly to a file

Versions

QGIS version
3.22.1-Białowieża
QGIS code revision
663dcf8
Qt version
5.15.2
Python version
3.9.5
GDAL/OGR version
3.4.0
PROJ version
8.2.0
EPSG Registry database version
v10.038 (2021-10-21)
GEOS version
3.10.0-CAPI-1.16.0
SQLite version
3.35.2
PDAL version
2.3.0
PostgreSQL client version
13.0
SpatiaLite version
5.0.1
QWT version
6.1.3
QScintilla2 version
2.11.5
OS version
Windows 10 Version 2009

Active Python plugins
changeDataSource
3.1
GeometryShapes
0.7
nominatim
1.4.5
profiletool
4.2.6
qfieldsync
v4.9.1
QuickOSM
2.2.3
db_manager
0.1.20
grassprovider
2.12.99
MetaSearch
0.3.5
processing
2.12.99
sagaprovider
2.12.99

---

QGIS version
3.32.0-Lima
QGIS code revision
311a8cb
Qt version
5.15.3
Python version
3.9.5
GDAL/OGR version
3.7.0
PROJ version
9.2.1
EPSG Registry database version
v10.041 (2021-12-03)
GEOS version
3.11.2-CAPI-1.17.2
SQLite version
3.41.1
PDAL version
2.5.3
PostgreSQL client version
15.2
SpatiaLite version
5.0.1
QWT version
6.1.6
QScintilla2 version
2.13.1
OS version
Windows 10 Version 2009

Active Python plugins
changeDataSource
3.1
GeometryShapes
0.7
nominatim
1.4.5
profiletool
4.2.6
qfieldsync
v4.9.1
QuickOSM
2.2.3
db_manager
0.1.20
grassprovider
2.12.99
MetaSearch
0.3.6
processing
2.12.99

Supported QGIS version

• I'm running a supported QGIS version according to the roadmap.

New profile

• I tried with a new QGIS profile

Additional context

No response

[Djedouas]

Hi, after digging into related issues, it seems that this new behavior is what we want.

SHP and GPKG standards don't allow multi geometry columns ayers.

Previously, saving a layer with multi geometry columns (from PostGIS for instance) to GPKG or SHP file format was converting the "other" geometry columns to string (as a workaround) which was a transparent data modification for the user.

Modifying a data structure transparently is critical and problematic.

Now, every workflow with such needs should take this into account. For example, to batch-process multi geometry layers with a clipping algorithm that saves the output as GPKG, one would need to have previously batch-processed the layers to convert all geometry fields to string.

As a result, this data structure modification is not transparent, and still necessary.

Processing models can be created for easier workflows.

NB: https://www.geopackage.org/guidance/modeling.html proposes use-cases for associating multiple geometries with a single feature.