This is an automated email from the ASF dual-hosted git repository. okislal pushed a commit to branch madlib2-master in repository https://gitbox.apache.org/repos/asf/madlib.git
commit 7a16084c03343e1f7ee8aa22708ac94d8b52cd68 Author: Orhan Kislal <[email protected]> AuthorDate: Wed Jul 12 08:56:01 2023 -0400 Various: Reduce SERIAL usage Since SERIAL columns do not guarantee a sequence of numbers with no gaps, this commit removes its usage from load_keras_model. The issue was unearthed by a change in the GPDB code setting the cache for sequences to 20 and causing the numbers to jump unexpectedly. A similar issue is found in assoc_rules. There we wanted to avoid using row_number so we set the cache to 1 to get the previous behavior. --- .../postgres/modules/assoc_rules/assoc_rules.py_in | 1 + .../deep_learning/keras_model_arch_table.py_in | 12 ++++-- .../test/madlib_keras_iris.setup.sql_in | 47 +++++++++++++++++----- .../test/madlib_keras_transfer_learning.sql_in | 30 +------------- 4 files changed, 48 insertions(+), 42 deletions(-) diff --git a/src/ports/postgres/modules/assoc_rules/assoc_rules.py_in b/src/ports/postgres/modules/assoc_rules/assoc_rules.py_in index 1d7d8314..ada27c71 100644 --- a/src/ports/postgres/modules/assoc_rules/assoc_rules.py_in +++ b/src/ports/postgres/modules/assoc_rules/assoc_rules.py_in @@ -322,6 +322,7 @@ def assoc_rules(madlib_schema, support, confidence, tid_col, ) m4_ifdef(`__POSTGRESQL__', `', `DISTRIBUTED BY (id)') """.format(**locals())); + plpy.execute("ALTER SEQUENCE {assoc_loop_aux}_id_seq CACHE 1".format(**locals())); if verbose : plpy.info("{0} Frequent itemsets found in this iteration".format( diff --git a/src/ports/postgres/modules/deep_learning/keras_model_arch_table.py_in b/src/ports/postgres/modules/deep_learning/keras_model_arch_table.py_in index 5fc6c35a..286f68bf 100644 --- a/src/ports/postgres/modules/deep_learning/keras_model_arch_table.py_in +++ b/src/ports/postgres/modules/deep_learning/keras_model_arch_table.py_in @@ -56,7 +56,7 @@ class ModelArchSchema: """ col_names = ('model_id', 'model_arch', 'model_weights', 'name', 'description', '__internal_madlib_id__') - col_types = ('SERIAL PRIMARY KEY', 'JSON', 'bytea', 'TEXT', 'TEXT', 'TEXT') + col_types = ('INTEGER', 'JSON', 'bytea', 'TEXT', 'TEXT', 'TEXT') (MODEL_ID, MODEL_ARCH, MODEL_WEIGHTS, NAME, DESCRIPTION, __INTERNAL_MADLIB_ID__) = col_names @@ -75,6 +75,7 @@ def load_keras_model(keras_model_arch_table, model_arch, model_weights, plpy.execute(sql, 0) plpy.info("Keras Model Arch: Created new keras model architecture table {0}." \ .format(model_arch_table)) + last_id = 0 else: missing_cols = columns_missing_from_table(model_arch_table, ModelArchSchema.col_names) @@ -82,12 +83,15 @@ def load_keras_model(keras_model_arch_table, model_arch, model_weights, plpy.error("Keras Model Arch: Invalid keras model architecture table {0}," " missing columns: {1}".format(model_arch_table, missing_cols)) + last_id = plpy.execute("SELECT max({0}) FROM {1}".format( + ModelArchSchema.MODEL_ID, model_arch_table))[0]['max'] + last_id = last_id if last_id is not None else 0 unique_str = unique_string(prefix_has_temp=False) insert_query = plpy.prepare("INSERT INTO {model_arch_table} " - "VALUES(DEFAULT, $1, $2, $3, $4, $5);".format(**locals()), - ModelArchSchema.col_types[1:]) - insert_res = plpy.execute(insert_query,[model_arch, model_weights, name, description, + "VALUES($1, $2, $3, $4, $5, $6);".format(**locals()), + ModelArchSchema.col_types) + insert_res = plpy.execute(insert_query, [last_id+1, model_arch, model_weights, name, description, unique_str], 0) select_query = """SELECT {model_id_col}, {model_arch_col} FROM {model_arch_table} diff --git a/src/ports/postgres/modules/deep_learning/test/madlib_keras_iris.setup.sql_in b/src/ports/postgres/modules/deep_learning/test/madlib_keras_iris.setup.sql_in index 7f68268d..e87930d1 100644 --- a/src/ports/postgres/modules/deep_learning/test/madlib_keras_iris.setup.sql_in +++ b/src/ports/postgres/modules/deep_learning/test/madlib_keras_iris.setup.sql_in @@ -23,7 +23,7 @@ DROP TABLE IF EXISTS iris_data; CREATE TABLE iris_data( - id serial, + id integer, attributes numeric[], class_text varchar ); @@ -281,6 +281,43 @@ $$ $$ ); +SELECT load_keras_model('iris_model_arch', -- Output table, +$$ +{"class_name": "Model", "keras_version": "2.2.4-tf", "config": {"layers": [{"class_name": "InputLayer", "config": {"dtype": "float32", "batch_input_shape": [null, 4], "name": "input_1", "sparse": false}, "inbound_nodes": [], "name": "input_1"}, {"class_name": "InputLayer", "config": {"dtype": "float32", "batch_input_shape": [null, 4], "name": "input_2", "sparse": false}, "inbound_nodes": [], "name": "input_2"}, {"class_name": "Dense", "config": {"kernel_initializer": {"class_name": "Vari [...] +$$::json, NULL, + 'Sophie', + 'A simple model' +); + +-- Load the same arch again so that we can compare transfer learning results +SELECT load_keras_model('iris_model_arch', -- Output table, +$$ +{ +"class_name": "Sequential", +"keras_version": "2.1.6", +"config": + [{"class_name": "Dense", "config": {"kernel_initializer": {"class_name": "VarianceScaling", + "config": {"distribution": "uniform", "scale": 1.0, "seed": 0, "mode": "fan_avg"}}, + "name": "dense_1", "kernel_constraint": null, "bias_regularizer": null, + "bias_constraint": null, "dtype": "float32", "activation": "relu", "trainable": true, + "kernel_regularizer": null, "bias_initializer": {"class_name": "Zeros", + "config": {}}, "units": 10, "batch_input_shape": [null, 4], "use_bias": true, + "activity_regularizer": null}}, {"class_name": "Dense", + "config": {"kernel_initializer": {"class_name": "VarianceScaling", + "config": {"distribution": "uniform", "scale": 1.0, "seed": 0, "mode": "fan_avg"}}, + "name": "dense_2", "kernel_constraint": null, "bias_regularizer": null, + "bias_constraint": null, "activation": "relu", "trainable": true, "kernel_regularizer": null, + "bias_initializer": {"class_name": "Zeros", "config": {}}, "units": 10, "use_bias": true, + "activity_regularizer": null}}, {"class_name": "Dense", "config": {"kernel_initializer": + {"class_name": "VarianceScaling", "config": {"distribution": "uniform", "scale": 1.0, + "seed": 0, "mode": "fan_avg"}}, "name": "dense_3", "kernel_constraint": null, + "bias_regularizer": null, "bias_constraint": null, "activation": "softmax", + "trainable": true, "kernel_regularizer": null, "bias_initializer": {"class_name": "Zeros", + "config": {}}, "units": 3, "use_bias": true, "activity_regularizer": null}}], + "backend": "tensorflow"} +$$ +); + DROP TABLE IF EXISTS iris_train, iris_test; -- Set seed so results are reproducible SELECT setseed(0); @@ -328,14 +365,6 @@ CREATE TABLE iris_mult AS class_text AS class_text, class_text AS class_text2 FROM iris_data; -SELECT load_keras_model('iris_model_arch', -- Output table, -$$ -{"class_name": "Model", "keras_version": "2.2.4-tf", "config": {"layers": [{"class_name": "InputLayer", "config": {"dtype": "float32", "batch_input_shape": [null, 4], "name": "input_1", "sparse": false}, "inbound_nodes": [], "name": "input_1"}, {"class_name": "InputLayer", "config": {"dtype": "float32", "batch_input_shape": [null, 4], "name": "input_2", "sparse": false}, "inbound_nodes": [], "name": "input_2"}, {"class_name": "Dense", "config": {"kernel_initializer": {"class_name": "Vari [...] -$$::json, NULL, - 'Sophie', - 'A simple model' -); - DROP TABLE IF EXISTS iris_mult_packed, iris_mult_packed_summary; SELECT training_preprocessor_dl('iris_mult', 'iris_mult_packed', diff --git a/src/ports/postgres/modules/deep_learning/test/madlib_keras_transfer_learning.sql_in b/src/ports/postgres/modules/deep_learning/test/madlib_keras_transfer_learning.sql_in index 884efd21..12605340 100644 --- a/src/ports/postgres/modules/deep_learning/test/madlib_keras_transfer_learning.sql_in +++ b/src/ports/postgres/modules/deep_learning/test/madlib_keras_transfer_learning.sql_in @@ -29,6 +29,7 @@ m4_include(`SQLCommon.m4') `\(.*\)/lib', `\1/../modules/deep_learning/test/madlib_keras_custom_function.setup.sql_in' ) + -------------- Warm start test (along with schema qualified output table) ------------------------- CREATE SCHEMA __MADLIB__DEEP_LEARNING_SCHEMA__MADLIB__; DROP TABLE IF EXISTS iris_model, iris_model_summary; @@ -342,35 +343,6 @@ WHERE first.mst_key = second.mst_key AND first.model_id = 2; -- Transfer learning tests --- Load the same arch again so that we can compare transfer learning results -SELECT load_keras_model('iris_model_arch', -- Output table, -$$ -{ -"class_name": "Sequential", -"keras_version": "2.1.6", -"config": - [{"class_name": "Dense", "config": {"kernel_initializer": {"class_name": "VarianceScaling", - "config": {"distribution": "uniform", "scale": 1.0, "seed": 0, "mode": "fan_avg"}}, - "name": "dense_1", "kernel_constraint": null, "bias_regularizer": null, - "bias_constraint": null, "dtype": "float32", "activation": "relu", "trainable": true, - "kernel_regularizer": null, "bias_initializer": {"class_name": "Zeros", - "config": {}}, "units": 10, "batch_input_shape": [null, 4], "use_bias": true, - "activity_regularizer": null}}, {"class_name": "Dense", - "config": {"kernel_initializer": {"class_name": "VarianceScaling", - "config": {"distribution": "uniform", "scale": 1.0, "seed": 0, "mode": "fan_avg"}}, - "name": "dense_2", "kernel_constraint": null, "bias_regularizer": null, - "bias_constraint": null, "activation": "relu", "trainable": true, "kernel_regularizer": null, - "bias_initializer": {"class_name": "Zeros", "config": {}}, "units": 10, "use_bias": true, - "activity_regularizer": null}}, {"class_name": "Dense", "config": {"kernel_initializer": - {"class_name": "VarianceScaling", "config": {"distribution": "uniform", "scale": 1.0, - "seed": 0, "mode": "fan_avg"}}, "name": "dense_3", "kernel_constraint": null, - "bias_regularizer": null, "bias_constraint": null, "activation": "softmax", - "trainable": true, "kernel_regularizer": null, "bias_initializer": {"class_name": "Zeros", - "config": {}}, "units": 3, "use_bias": true, "activity_regularizer": null}}], - "backend": "tensorflow"} -$$ -); - DROP TABLE IF EXISTS mst_table, mst_table_summary; SELECT load_model_selection_table( 'iris_model_arch',
