[GitHub] [madlib] reductionista commented on a change in pull request #505: DBSCAN: Add indexing optimizations to improve the runtime

[GitBox <gi...@apache.org>]

reductionista commented on a change in pull request #505:
URL: https://github.com/apache/madlib/pull/505#discussion_r456728316



##########
File path: src/ports/postgres/modules/dbscan/dbscan.sql_in
##########
@@ -465,3 +480,119 @@ CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.dbscan_predict(
     PythonFunction(dbscan, dbscan, dbscan_predict_help)
 $$ LANGUAGE plpythonu VOLATILE
 m4_ifdef(`\_\_HAS_FUNCTION_PROPERTIES\_\_', `MODIFIES SQL DATA', `');
+
+CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.sf_merge(
+    state1          INTEGER[][],
+    state2          INTEGER[][]
+) RETURNS INTEGER[][] AS $$
+PythonFunctionBodyOnlyNoSchema(`dbscan', `dbscan')
+    return dbscan.sf_merge(**globals())
+$$ LANGUAGE plpythonu
+m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `NO SQL', `');
+
+CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.sf_final(
+    state INTEGER[][]
+) RETURNS INTEGER[][] AS $$
+PythonFunctionBodyOnlyNoSchema(`dbscan', `dbscan')
+    return dbscan.sf_final(**globals())
+$$ LANGUAGE plpythonu
+m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `NO SQL', `');
+
+CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.sf_transition(
+    state                       INTEGER[],
+    src                         BIGINT,
+    dest                        BIGINT,
+    n_rows                      INTEGER[],
+    gp_segment_id               INTEGER
+
+) RETURNS INTEGER[][] AS $$
+PythonFunctionBodyOnlyNoSchema(`dbscan', `dbscan')
+    return dbscan.sf_transition(**globals())
+$$ LANGUAGE plpythonu
+m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `NO SQL', `');
+
+DROP AGGREGATE IF EXISTS MADLIB_SCHEMA.sf_step(
+    BIGINT,
+    BIGINT,
+    INTEGER[],
+    INTEGER);
+CREATE AGGREGATE MADLIB_SCHEMA.sf_step(
+    BIGINT,
+    BIGINT,
+    INTEGER[],
+    INTEGER
+)(
+    STYPE=INTEGER[][],
+    SFUNC=MADLIB_SCHEMA.sf_transition,
+    m4_ifdef(`__POSTGRESQL__', `', `prefunc=MADLIB_SCHEMA.sf_merge,')
+    FINALFUNC=MADLIB_SCHEMA.sf_final
+);
+
+CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.rtree_transition(
+    state                       INTEGER[],
+    id_in                         BIGINT,
+    expr_points DOUBLE PRECISION[],
+    eps     DOUBLE PRECISION,
+    min_samples INTEGER,
+    metric  VARCHAR,
+    n_rows  INTEGER[],
+    leaf_id     INTEGER
+
+) RETURNS INTEGER[][] AS $$
+PythonFunctionBodyOnlyNoSchema(`dbscan', `dbscan')
+    return dbscan.rtree_transition(**globals())
+$$ LANGUAGE plpythonu
+m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `NO SQL', `');
+
+CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.rtree_merge(
+    state1          INTEGER[][],
+    state2          INTEGER[][]
+) RETURNS INTEGER[][] AS $$
+PythonFunctionBodyOnlyNoSchema(`dbscan', `dbscan')
+    return dbscan.rtree_merge(**globals())
+$$ LANGUAGE plpythonu
+m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `NO SQL', `');
+
+CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.rtree_final(
+    state INTEGER[][]
+) RETURNS INTEGER[] AS $$
+PythonFunctionBodyOnlyNoSchema(`dbscan', `dbscan')
+    return dbscan.rtree_final(**globals())
+$$ LANGUAGE plpythonu
+m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `NO SQL', `');
+
+--id_in, expr_points, eps, min_samples, metric, n_rows, leaf_id,
+DROP AGGREGATE IF EXISTS MADLIB_SCHEMA.rtree_step(
+    BIGINT,
+    DOUBLE PRECISION[],
+    DOUBLE PRECISION,
+    INTEGER,
+    VARCHAR,
+    INTEGER[],
+    INTEGER);
+CREATE AGGREGATE MADLIB_SCHEMA.rtree_step(
+    BIGINT,
+    DOUBLE PRECISION[],
+    DOUBLE PRECISION,
+    INTEGER,
+    VARCHAR,
+    INTEGER[],
+    INTEGER
+)(
+    STYPE=INTEGER[][],
+    SFUNC=MADLIB_SCHEMA.rtree_transition,
+    m4_ifdef(`__POSTGRESQL__', `', `prefunc=MADLIB_SCHEMA.rtree_merge,')
+    FINALFUNC=MADLIB_SCHEMA.rtree_final
+);
+
+DROP TYPE IF EXISTS MADLIB_SCHEMA.unpacked_2d CASCADE;
+CREATE TYPE MADLIB_SCHEMA.unpacked_2d AS (
+    src BIGINT,
+    dest BIGINT);
+
+CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.unpack_2d (packed INTEGER[][])
+  RETURNS SETOF MADLIB_SCHEMA.unpacked_2d
+AS $$
+    return packed
+$$ LANGUAGE plpythonu VOLATILE
+m4_ifdef(`\_\_HAS_FUNCTION_PROPERTIES\_\_', `MODIFIES SQL DATA', `');

Review comment:
       I'm not sure why, but I'm unable to install madlib due to this error:
   ```
   CREATE AGGREGATE
   DROP TYPE IF EXISTS madlib.unpacked_2d CASCADE;
   psql:/tmp/madlib.H_8TXX/madlib_install.sql:16255: NOTICE:  00000: type "madlib.unpacked_2d" does not exist, skipping
   LOCATION:  does_not_exist_skipping, dropcmds.c:424
   DROP TYPE
   CREATE TYPE madlib.unpacked_2d AS (
       src INTEGER,
       dest INTEGER);
   CREATE TYPE
   CREATE OR REPLACE FUNCTION madlib.unpack_2d (packed INTEGER[][])
     RETURNS SETOF unpacked_2d
   AS $$
       return packed
   $$ LANGUAGE plpythonu VOLATILE
   ;
   psql:/tmp/madlib.H_8TXX/madlib_install.sql:16264: ERROR:  42704: type "unpacked_2d" does not exist
   LOCATION:  compute_return_type, functioncmds.c:135
   ```
   




----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org