Opened 8 months ago

Closed 7 months ago

#5170 closed enhancement (fixed)

Make postgis raster copy rows configurable

Reported by: robe Owned by: robe
Priority: medium Milestone: PostGIS 3.3.0
Component: raster Version: master
Keywords: Cc:

Description

A client asked me why does copy mode, always result in

COPY 11

11 rows per copy command.

I checked in the code, and it appears to be a hard-coded setting.

https://git.osgeo.org/gitea/postgis/postgis/src/branch/master/raster/loader/raster2pgsql.c#L1518 https://git.osgeo.org/gitea/postgis/postgis/src/branch/master/raster/loader/raster2pgsql.c#L1845

	if (tileset->length > 10) {
				if (!insert_records(
					config->schema, ovtable, config->raster_column,
					(config->file_column ? config->rt_filename[idx] : NULL), config->file_column_name,
					config->copy_statements, config->out_srid,
					tileset, buffer

I'm assuming this was put in place to get around some copy size limit. Can we get better load by increasing or making this configurable.

I was going to test this out to see. Given that the size of each row would be dependent on tile size, doesn't seem right it should always be 11.

Change History (11)

comment:1 by Regina Obe <lr@…>, 8 months ago

Resolution: fixed
Status: newclosed

In 2ff0afa/git:

Add Z option to raster2pgsql that allows for specifying number of rows
per SQL COPY. Closes #5170

Add credits for ST_SimplifyPolygonHull to news, References #5169

comment:2 by Regina Obe <lr@…>, 8 months ago

In a64af1f/git:

Fix copy paste error, should have been atoi instead of atof. References #5170

comment:3 by komzpa, 8 months ago

Resolution: fixed
Status: closedreopened
echo 'COMMIT;' >> rtpostgis_upgrade.sql
raster2pgsql.c: In function 'build_overview':
raster2pgsql.c:1523:24: error: comparison of integer expressions of different signedness: 'uint32_t' {aka 'unsigned int'} and 'int' [-Werror=sign-compare]
 1523 |    if (tileset->length >= config->max_tiles_per_copy) {
      |                        ^~
raster2pgsql.c: In function 'convert_raster':
raster2pgsql.c:1850:25: error: comparison of integer expressions of different signedness: 'uint32_t' {aka 'unsigned int'} and 'int' [-Werror=sign-compare]
 1850 |     if (tileset->length >= config->max_tiles_per_copy ) {
      |                         ^~
raster2pgsql.c:1973:25: error: comparison of integer expressions of different signedness: 'uint32_t' {aka 'unsigned int'} and 'int' [-Werror=sign-compare]
 1973 |     if (tileset->length >= config->max_tiles_per_copy ) {
      |                         ^~

comment:4 by robe, 8 months ago

@kompza thanks for catching. I should have looked at the bots.

Thinking about it more, wondering if maybe I should have not introduced a new arg, and just reuse the -Y since -Y takes no args.

so a -Y would be what it was before default to 11. But a -Y <number> would then change the default. I'm going to revise to do that and see how that goes.

comment:5 by Regina Obe <lr@…>, 8 months ago

In f45fd93/git:

Fix data type of max_tiles_per_copy. References #5170

comment:6 by robe, 8 months ago

Definitely faster with larger chunks.

Timing for a 2g tif I was testing with 128x128 tiles

# took 6 hrs

raster2pgsql -e -I -Y -t 128x128

# took 1.5 hrs

raster2pgsql -e -I -Y -Z 50000 -t 128x128

# took 1 hrs

raster2pgsql -e -I -Y -Z 100000 -t 128x128

@kompza preferred just ingesting the whole thing as default and using longer switches like —max-tiles-per-copy.

I think switching to long aside from deviating from before is a much larger lift

comment:7 by Regina Obe <lr@…>, 8 months ago

In ec0ff33/git:

Document new -Z switch. Clarify use of -k. References #5170

comment:8 by Regina Obe <lr@…>, 8 months ago

In fea02ef/git:

Replace -Z option as optional argument to -Y option.
Increase default of copy to 50 rows from 11. References #5170

comment:9 by Paul Ramsey <pramsey@…>, 8 months ago

In 6bbcbef1/git:

Replace -Z option as optional argument to -Y option.
Increase default of copy to 50 rows from 11. References #5170

comment:10 by Regina Obe <lr@…>, 7 months ago

In 7c69026/git:

Update Help screen. Minor adjustments to doc.
References #5170

comment:11 by robe, 7 months ago

Resolution: fixed
Status: reopenedclosed
Note: See TracTickets for help on using tickets.