Function APPROXIMATE_MEDIAN(int) does not exist, or permission is denied for APPROXIMATE_MEDIAN(int)

Pieter_Sheth-Vo

According to the docs, APPROXIMATE_MEDIAN and APPROXIMATE_PERCENTILE were added in Vertica 8.1. However, in Vertica 9.3, this yields the error
ERROR 3457: Function APPROXIMATE_MEDIAN(int) does not exist, or permission is denied for APPROXIMATE_MEDIAN(int)

It fails for integers and floats, both as a regular db user as well as when signed in as dbadmin. Does this method need to be compiled or added before it can be used?

Jim_Knicely

You can also use admintools to reinstall the any package:

Example:

[dbadmin@localhost ~]$ admintools -t list_packages
             Package Description
                 AWS Amazon Web Services Package
        ComplexTypes Functions for Complex Types
     MachineLearning In-dataBase Machine Learning package.
       ParquetExport Functions that support Parquet Export
      SparkConnector Spark connector for reading from and writing into Vertica
       TFIntegration Using Tensorflow models in Vertica
          VFunctions Vertica functions
         approximate Probabilistic/Approximate Aggregation Functions
           flextable Flexible Tables Data Load and Query
                hcat Java/HCatalog data load and query
               kafka Kafka streaming load and export
           logsearch Common Advanced text search functions
               place Geospatial Data Query
            txtindex Common string-manipulation functions
       voltagesecure Voltage SecureData functions

[dbadmin@localhost ~]$ admintools -t install_package -d verticademos -P approximate --force-reinstall
Installing package approximate...
...Success!

Documenation:
https://www.vertica.com/docs/latest/HTML/Content/Authoring/InstallationGuide/Upgrade/PostUpgrade/UpgradingandReinstallingPackages.htm

Pieter_Sheth-Vo

I found this script in the directory, and running the following command as dbadmin successfully installed it.

\i /opt/vertica/packages/approximate/ddl/install.sql

Out of curiosity, where might I find the documentation for these? I didn't see a link from the main page https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/APPROXIMATE_PERCENTILE.htm

Hibiki

Approximate package is installed when creating the database. Did you see any error when installing that package? You can see the messages for this installation in /opt/vertica/log/adminTools.log file. For example:

2020-09-28 10:48:18.511 admintools/4125:0x7f268d1cb700 [at_command.to_subprocess_invocation] <INFO> Command: [/opt/vertica/oss/python3/bin/python3 -m vertica.engine.api.at_runner --no-prompt --module vertica.engine.api.vsql_script.module]; input: [#{"vsql_path": "/opt/vertica/bin/vsql", "user": "dbadmin", "port": 5433, "db_name": "trial", "tmp_dir": "/tmp", "options": ["TUPLES_ONLY", "UNALIGNED"], "cwd": "/opt/vertica/packages/approximate", "__rollback__": false, "__protocol__": "1.0"}]
.....
2020-09-28 10:48:19.137 admintools/4125:0x7f2698759740 [adminExec._install_default_packages] <INFO> Success: package approximate installed