Adding constructors for path and polygon geometric types

From: Esteban Zimanyi <ezimanyi(at)ulb(dot)ac(dot)be>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Adding constructors for path and polygon geometric types
Date: 2020-09-28 13:10:36
Message-ID: CAPqRbE5kfKzLmWEZV21_DdfyaeyJDO7Js8XsT0rPVeXkLPZbMA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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(at)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(at)ESTEBAN-WORKSTATION:~/src/postgresql-13.0-geo$

Any idea how to solve this ?

Esteban

Browse pgsql-hackers by date

  From Date Subject
Next Message torikoshia 2020-09-28 13:14:01 Re: Is it useful to record whether plans are generic or custom?
Previous Message Ashutosh Sharma 2020-09-28 13:06:47 Re: Parallel copy