Dear all Since always I have used geometric types as a great pedagogical example in order to understand how indexes work in PostgreSQL so that I can program our indexes in MobilityDB https://github.com/MobilityDB/MobilityDB However, this requires to create tables with a big number of geometries. Currently there are SQL constructors for all types with the exception of path and polygon. The current input format with strings is definitely not ideal for this purpose. I created constructors for these types, which are exemplified next.
SELECT path(ARRAY[point(1,2),point(3,4),point(5,6)]); SELECT polygon(ARRAY[point(1,2),point(3,4),point(5,6),point(1,2)]); >From this I can create random generators such as CREATE OR REPLACE FUNCTION random_polygon(low float, high float, maxcard int) RETURNS polygon AS $$ DECLARE ptarr point[]; BEGIN SELECT array_agg(random_point(low, high)) INTO ptarr FROM generate_series (1, random_int(1, maxcard)) AS x; ptarr = array_append(ptarr, ptarr[1]); RETURN polygon(ptarr); END; $$ LANGUAGE 'plpgsql' STRICT; which allows me to generate random tables of arbitrary number of rows such as DROP TABLE IF EXISTS tbl_polygon; CREATE TABLE tbl_polygon AS SELECT k, random_polygon(1, 100, 10) AS poly FROM generate_series(1, 1e5) AS k; Now I can analyze, e.g., how indexes work for KNN queries CREATE INDEX tbl_polygon_spgist_idx ON tbl_polygon USING spgist(poly); EXPLAIN SELECT t1.k FROM tbl_polygon t1 ORDER BY t1.poly <-> point(1, 1) LIMIT 3; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Limit (cost=0.28..0.70 rows=3 width=14) -> Index Scan using tbl_polygon_spgist_idx on tbl_polygon t1 (cost=0.28..13932.28 rows=100000 width=14) Order By: (poly <-> '(1,1)'::point) (3 rows) I wanted to create a patch with these constructors but I am unable to do it due to a problem in the bootstrap phase that I do not understand. I have simply added the definition of the path constructor in the file geo_ops.c as follows /** * PATH Constructor */ Datum path_constructor(PG_FUNCTION_ARGS) { ArrayType *array = PG_GETARG_ARRAYTYPE_P(0); ... PG_RETURN_PATH_P(path); } and added to the pg_proc.dat file the following # additional constructors for geometric types { oid => '4226', descr => 'convert array of points to path', proname => 'path', prorettype => 'path', proargtypes => '_point', prosrc => 'path_constructor' }, It compiled and installed without any problem but when executing initdb I get the following error. esteban@ESTEBAN-WORKSTATION:~/src/postgresql-13.0-geo$ /usr/local/pgsql/13/bin/initdb -D /usr/local/pgsql/13/data The files belonging to this database system will be owned by user "esteban". This user must also own the server process. The database cluster will be initialized with locale "C.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /usr/local/pgsql/13/data ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... Europe/Brussels creating configuration files ... ok running bootstrap script ... 2020-09-28 15:00:57.961 CEST [647] FATAL: invalid input syntax for type oid: "path" 2020-09-28 15:00:57.961 CEST [647] PANIC: cannot abort transaction 1, it was already committed Aborted child process exited with exit code 134 initdb: removing contents of data directory "/usr/local/pgsql/13/data" esteban@ESTEBAN-WORKSTATION:~/src/postgresql-13.0-geo$ Any idea how to solve this ? Esteban