branch: elpa/pg
commit 8d3434b6698eec2d6882ebedc6c4c84700083c50
Author: Eric Marsden <eric.mars...@risk-engineering.org>
Commit: Eric Marsden <eric.mars...@risk-engineering.org>
Add support for the Vertica column-oriented database
---
CHANGELOG.md | 8 ++++++++
README.md | 8 +++++++-
pg-lo.el | 2 +-
pg.el | 51 +++++++++++++++++++++++++++++++++++++++++++++++----
test/Makefile | 15 +++++++++++++++
5 files changed, 78 insertions(+), 6 deletions(-)
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 59f08e7d94..37b44376c5 100755
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -1,6 +1,14 @@
# Changelog
+## [0.56] - Unreleased
+
+- Allow for two successive messages of type `ErrorMessage` (which is unusual,
but used by the OctoDB
+ variant).
+
+- Detect and implement workarounds for the semi-compatible PostgreSQL variant
Vertica.
+
+
## [0.55] - 2025-06-29
- New error class `pg-duplicate-prepared-statement`.
diff --git a/README.md b/README.md
index a90d9aec8b..c96d64091b 100644
--- a/README.md
+++ b/README.md
@@ -145,7 +145,7 @@ The following PostgreSQL-compatible databases have been
tested:
- The [Materialize](https://materialize.com/) operational database (a
proprietary differential
dataflow database) has many limitations in its PostgreSQL compatibility: no
support for primary
keys, unique constraints, check constraints, for the `bit` type for example.
It works with these
- limitations with pg-el (last tested 2025-07 with Materialize v0.150).
+ limitations with pg-el (last tested 2025-07 with Materialize v0.151).
- The [QuestDB](https://questdb.io/) time series database (Apache licensed)
has very limited
PostgreSQL support, and does not support the `integer` type for example.
Last tested 2025-06 with
@@ -158,6 +158,12 @@ The following PostgreSQL-compatible databases have been
tested:
recognize basic PostgreSQL types such as `INT2`. It also does not for
example support the `CHR`
and `MD5` functions, row expressions, and `WHERE` clauses without a `FROM`
clause.
+- The [Vertica](https://www.vertica.com/) distributed database (a propriety
column-oriented database
+ targeting analytics workloads). Its PostgreSQL compatibility is limited: it
does not support
+ certain datatypes such as `int2`, `int4` and `text`, the parsing of `timetz`
strings is not
+ compatible with PostgreSQL, the serialization of arrays is not
PostgreSQL-compatible. Last tested
+ 2025-07 with the community edition, version 25.3.
+
- [YottaDB Octo](https://gitlab.com/YottaDB/DBMS/YDBOcto), which is built on
the YottaDB key-value
store (which is historically based on the MUMPS programming language). GNU
AGPL v3 licence. There
are many limitations in the PostgreSQL compatibility: no user
metainformation, no cursors, no
diff --git a/pg-lo.el b/pg-lo.el
index efb69b3593..7ffdf686d7 100644
--- a/pg-lo.el
+++ b/pg-lo.el
@@ -69,7 +69,7 @@
;; fn is either an integer, in which case it is the OID of an element
;; in the pg_proc table, and otherwise it is a string which we look up
;; in the alist `pg-lo-functions' to find the corresponding OID.
-(defun pg-fn (con fn integer-result &rest args)
+(cl-defun pg-fn (con fn integer-result &rest args)
(pg-connection-set-busy con t)
(unless pg-lo-initialized
(pg-lo-init con))
diff --git a/pg.el b/pg.el
index 1a2ef68f51..6da4375d44 100644
--- a/pg.el
+++ b/pg.el
@@ -34,7 +34,7 @@
;; Supported features:
;;
;; - SCRAM-SHA-256 authentication (the default method since PostgreSQL
version 14) and MD5
-;; - authentication.
+;; authentication.
;;
;; - Encrypted (TLS) connections between Emacs and the PostgreSQL backend.
;;
@@ -501,6 +501,8 @@ Uses connection CON. The variant can be accessed by
`pgcon-server-variant'."
(setf (pgcon-server-variant con) 'greenplum))
((cl-search "(Materialize " version)
(setf (pgcon-server-variant con) 'materialize))
+ ((cl-search "Vertica Analytic" version)
+ (setf (pgcon-server-variant con) 'vertica))
;; A more expensive test is needed for Google AlloyDB. If this
parameter is defined,
;; the query will return "on" or "off" as a string, and if the
parameter is not defined
;; the query (second argument meaning no-error) will return
'((nil)).
@@ -1485,6 +1487,7 @@ Uses PostgreSQL connection CON."
(let ((msg (format "Unknown PostgreSQL oid %d" oid)))
(signal 'pg-error (list msg))))))))
+;; TODO: this function does not work with the Vertica variant, where the
pg_type table is not implemented.
(defun pg-lookup-type-name (con oid)
"Return the PostgreSQL type name associated with OID.
Uses PostgreSQL connection CON."
@@ -3282,6 +3285,21 @@ Uses database connection CON."
(res (pg-exec con sql))
(row (pg-result res :tuple 0)))
(cl-first row)))
+ ('vertica
+ (let* ((schema (when (pg-qualified-name-p table)
+ (pg-qualified-name-schema table)))
+ (table-name (if (pg-qualified-name-p table)
+ (pg-qualified-name-name table)
+ table))
+ (schema-sql (if schema " AND table_schema=$2" ""))
+ ;; FIXME is this returning a name/string or a referene to the
v_catalog.users table?
+ ;; https://dataedo.com/kb/query/vertica/list-schemas-in-database
+ (sql (concat "SELECT owner_name FROM v_catalog.tables WHERE
table_name=$1" schema-sql))
+ (args (if schema
+ `((,table-name . "text") (,schema . "text"))
+ `((,table-name . "text"))))
+ (res (pg-exec-prepared con sql args)))
+ (cl-first (pg-result res :tuple 0))))
(_
(let* ((schema (when (pg-qualified-name-p table)
(pg-qualified-name-schema table)))
@@ -3405,6 +3423,11 @@ Uses database connection CON."
(rows (pg-result res :tuples)))
(not (null rows))))
;; (cl-position name rows :key #'cl-first :test #'string=)))
+ ('vertica
+ ;; Vertica provides the v_catalog.user_functions and
v_catalog.user_procedures tables that list
+ ;; all user-defined functions and procedures, but current versions do not
have any information
+ ;; on builtin functions or procedures.
+ (signal 'pg-user-error (list "pg-function-p not implemented for
Vertica")))
(_
(let* ((sql "SELECT * FROM pg_catalog.pg_proc WHERE proname = $1")
(res (pg-exec-prepared con sql `((,name . "text")))))
@@ -3423,8 +3446,14 @@ Uses database connection CON."
;; =====================================================================
(defun pg-databases (con)
"List of the databases in the PostgreSQL server we are connected to via CON."
- (let ((res (pg-exec con "SELECT datname FROM pg_catalog.pg_database")))
- (apply #'append (pg-result res :tuples))))
+ (pcase (pgcon-server-variant con)
+ ('vertica
+ (let* ((sql "SELECT database_name FROM v_catalog.databases")
+ (res (pg-exec con sql)))
+ (apply #'append (pg-result res :tuples))))
+ (_
+ (let ((res (pg-exec con "SELECT datname FROM pg_catalog.pg_database")))
+ (apply #'append (pg-result res :tuples))))))
(defun pg-current-schema (con)
"Return the current schema in the PostgreSQL server we are connected to via
CON."
@@ -3448,6 +3477,9 @@ Uses database connection CON."
((or 'risingwave 'octodb)
(let ((res (pg-exec con "SELECT DISTINCT table_schema FROM
information_schema.tables")))
(apply #'append (pg-result res :tuples))))
+ ('vertica
+ (let ((res (pg-exec con "SELECT DISTINCT schema_name FROM
v_catalog.schemata")))
+ (apply #'append (pg-result res :tuples))))
(_
(let ((res (pg-exec con "SELECT schema_name FROM
information_schema.schemata")))
(apply #'append (pg-result res :tuples))))))
@@ -3532,6 +3564,15 @@ Queries legacy internal PostgreSQL tables."
for row in rows
collect (make-pg-qualified-name :schema (cl-first row) :name (cl-second
row)))))
+;;
https://docs.vertica.com/24.2.x/en/sql-reference/system-tables/v-catalog-schema/
+(defun pg--tables-vertica (con)
+ (let* ((sql "SELECT table_schema,table_name FROM v_catalog.tables")
+ (res (pg-exec con sql))
+ (rows (pg-result res :tuples)))
+ (cl-loop
+ for row in rows
+ collect (make-pg-qualified-name :schema (cl-first row) :name (cl-second
row)))))
+
(defun pg-tables (con)
"List of the tables present in the database we are connected to via CON.
Only tables to which the current user has access are listed."
@@ -3545,6 +3586,8 @@ Only tables to which the current user has access are
listed."
(pg--tables-materialize con))
((eq (pgcon-server-variant con) 'clickhouse)
(pg--tables-clickhouse con))
+ ((eq (pgcon-server-variant con) 'vertica)
+ (pg--tables-vertica con))
((eq (pgcon-server-variant con) 'octodb)
(pg--tables-legacy con))
((> (pgcon-server-version-major con) 11)
@@ -3574,7 +3617,7 @@ Only tables to which the current user has access are
listed."
(defun pg-columns (con table)
"List of the columns present in TABLE over PostgreSQL connection CON."
- (cond ((eq (pgcon-server-variant con) 'ydb)
+ (cond ((member (pgcon-server-variant con) '(ydb vertica))
(pg--columns-legacy con table))
((> (pgcon-server-version-major con) 7)
(pg--columns-information-schema con table))
diff --git a/test/Makefile b/test/Makefile
index 0eae5ee62d..cc2f5ecffb 100644
--- a/test/Makefile
+++ b/test/Makefile
@@ -1001,6 +1001,21 @@ test-octodb: test-pg.el
${DOCKER} stop octodb
+# https://hub.docker.com/r/opentext/vertica-ce
+#
https://github.com/vertica/vertica-containers/blob/main/one-node-ce/Dockerfile_Ubuntu
+test-vertica: test-pg.el
+ ${DOCKER} run --rm --name vertica \
+ --pull=newer \
+ --publish 127.0.0.1:5433:5433 --publish 127.0.0.1:5444:5444 \
+ -e VERTICA_DB_NAME=pgeltestdb \
+ -e VERTICA_DB_USER=pgeltestuser \
+ -e VERTICA_DB_PASSWORD=pgeltest \
+ -d docker.io/opentext/vertica-ce:latest
+ sleep 10
+ PGURI=postgresql://dbadmin:pgeltest@127.0.0.1:5433/pgeltestdb $(MAKE)
test
+ ${DOCKER} stop vertica
+
+
# https://github.com/risinglightdb/risinglight
#
# This educational database implementation is not very PostgreSQL compatible;
it doesn't implement
