PostgreSQL catalog
The PostgreSQL catalog connects Gravitino to one PostgreSQL database over JDBC, exposing the schemas in that database as schemas and their tables as tables. Schemas and tables are both read-write.
One catalog reaches exactly one database, named in the catalog properties, so reaching a second database means creating a second catalog.
Gravitino appends an identifier to the comment of each schema and table it creates, in the form
(From Gravitino, DO NOT EDIT: gravitino.v1.uid{id}). The UI and the API strip it from the comment
they show. Leave it in place when you edit a comment directly in PostgreSQL.
Quick start
The example connects the PostgreSQL database {database} as the catalog pg_catalog_demo and lists
its schemas. The PostgreSQL JDBC driver ships in the image, so there is no driver to supply.
- UI
- REST
-
Start the catalog. Open Connect → Catalogs and click + Catalog. Enter
pg_catalog_demoas the Catalog name, keep Relational as the Catalog type, and choose PostgreSQL under Databases and warehouses in Select provider. -
Enter the connection. Under Connection, enter
jdbc:postgresql://{postgres_host}:5432/{database}as the URL,{database}as the Database, and the User and Password of the PostgreSQL role Gravitino connects as. The database in the URL and in Database must agree. The UI fills injdbc-driverfrom the provider. -
Test and create it. Click Test connection, then Create catalog.
-
List the schemas. In Explore, open
pg_catalog_demo. Gravitino returns the schemas in that database, with the system schemas filtered out.
-
Create the catalog. The public REST endpoint does not fill in
jdbc-driver, so the request names it. The request omitsjdbc-database, which the catalog then takes from the URL.Create a PostgreSQL catalogcurl -sS -X POST "https://{gravitino_host}/api/metalakes/{metalake}/catalogs" \
-H "Accept: application/vnd.gravitino.v1+json" \
-H "Content-Type: application/json" \
-H "Authorization: Bearer $TOKEN" \
-d '{
"name": "pg_catalog_demo",
"type": "RELATIONAL",
"provider": "jdbc-postgresql",
"comment": "PostgreSQL catalog",
"properties": {
"jdbc-url": "jdbc:postgresql://{postgres_host}:5432/{database}",
"jdbc-user": "{postgres_user}",
"jdbc-password": "{postgres_password}",
"jdbc-driver": "org.postgresql.Driver"
}
}' -
List the schemas. Gravitino returns the schemas in that database, with the system schemas filtered out.
List the schemas in the catalogcurl -sS "https://{gravitino_host}/api/metalakes/{metalake}/catalogs/pg_catalog_demo/schemas" \
-H "Accept: application/vnd.gravitino.v1+json" \
-H "Authorization: Bearer $TOKEN"
Catalog properties
The PostgreSQL catalog defines no properties of its own. It takes the jdbc-url, jdbc-driver,
jdbc-user, jdbc-password, jdbc-database, and pool properties described in
Shared properties.
Use org.postgresql.Driver as jdbc-driver. The catalog needs a database. It takes it from
jdbc-database or from the path of jdbc-url, such as {database} in
jdbc:postgresql://{postgres_host}:5432/{database}, and creation fails when neither names one or
when the two differ. The UI asks for Database as a required field.
Schemas
A schema in this catalog is a PostgreSQL schema inside the catalog's database. Create, list, load,
and drop are supported; alter is not. A schema comment is written with COMMENT ON SCHEMA, and
schema properties are rejected.
Drop with cascade issues DROP SCHEMA ... CASCADE, which removes every object in the schema. Drop
without cascade leaves the check to PostgreSQL, which refuses to drop a schema that still holds
objects.
pg_catalog and information_schema are reserved and cannot be used for a schema, compared without
regard to case. Listing filters out those two and pg_toast.
Tables
Table create, load, list, rename, alter, and drop are supported. Purge is not: a purge request is rejected, so use drop. Listing returns base tables only, not views.
Partitioning, distribution, and sort orders are not supported, and a create that carries any of them
is rejected. Table and column comments are written with COMMENT ON TABLE and COMMENT ON COLUMN
after the CREATE TABLE statement.
Properties
Table properties are not supported. A create that carries properties is rejected, and setting or removing a property on an existing table is rejected as well.
Indexes
Primary key and unique key are supported, at creation and through an alter, and both are read back from PostgreSQL. An index given a name at creation becomes a constraint of that name; an unnamed one is named by PostgreSQL. An index added through an alter is added as a named constraint. An index on a nested field is rejected.
{
"indexes": [
{
"indexType": "primary_key",
"name": "id_pk",
"fieldNames": [["id"]]
},
{
"indexType": "unique_key",
"name": "id_name_uk",
"fieldNames": [["id"], ["name"]]
}
]
}
Deleting an index drops the constraint of that name and then its index. Only primary-key and unique
constraints can be deleted. A unique index created directly with CREATE UNIQUE INDEX shows in the
table's indexes, but a request to delete it fails before any change in the request is applied, with or
without ifExists. Drop such an index in PostgreSQL.
Auto increment
An auto-increment column is created as an identity column, GENERATED BY DEFAULT AS IDENTITY. A
column declared auto-increment at creation or added through an alter must be Integer or Long. Turning auto increment on or off through an alter adds or drops the
identity on an existing column.
Supported changes
| Change | Supported |
|---|---|
| Rename table | Yes, as the only change in its request |
| Update table comment | Yes |
| Add column | Yes, at the default position only |
| Delete column | Yes |
| Rename column | Yes |
| Update column type | Yes |
| Update column nullable | Yes |
| Update column default | Yes |
| Update column comment | Yes |
| Update column auto increment | Yes, by adding or dropping the identity |
| Add or delete index | Yes, for primary key and unique key |
| Update column position | No, PostgreSQL cannot reorder columns |
| Set or remove property | No |
Updating a column's type keeps a not-null constraint on the column.
Changing a column from nullable to not null is rejected when its default is NULL. A nullable
column created without a default reads back with a NULL default, so give it a non-null default
first.
Nested column names are rejected by every column change.
Identifier case and quoting
Gravitino does not fold the case of PostgreSQL names. Every name is sent in double quotes, so
PostgreSQL keeps it exactly as given: Orders and orders are different tables. A table created
directly in PostgreSQL without quotes is stored in lowercase, so refer to it in lowercase through
Gravitino.
Schema, table, and column names start with a letter from any script, an underscore, or /, and
continue with up to 62 letters, digits, underscores, hyphens, $, /, or =, for a maximum of 63
characters. A double quote cannot appear in a name, so no escaping is needed inside the quotes.
Type mapping
Gravitino reads each PostgreSQL type as the Gravitino type in its row, and creates each Gravitino type as the PostgreSQL type in its row, except where the list under the table says otherwise.
| PostgreSQL type | Gravitino type |
|---|---|
bool | Boolean |
int2 | Short |
int4 | Integer |
int8 | Long |
float4 | Float |
float8 | Double |
numeric(p,s) | Decimal(p, s), when p is at most 38 and s is 0 to p |
date | Date |
time(p) | Time, precision p |
timestamp(p) | Timestamp without time zone, precision p |
timestamptz(p) | Timestamp with time zone, precision p |
bpchar(n) | Char(n) |
bpchar with no length | External type bpchar |
varchar(n) | Varchar(n) |
varchar with no length | String |
text | String |
bytea | Binary |
uuid | UUID |
smallserial, serial, bigserial | Short, Integer, Long, with auto increment |
| An array of any element type | List of that element type, with nullable elements |
numeric with no precision | External type numeric |
| Any other type | External type carrying the PostgreSQL type name |
An unconstrained numeric accepts values whose precision and scale vary from row to row, which no
Gravitino decimal type can describe, so it maps to an external type. A numeric(p,s) outside the
decimal range, such as numeric(39,0), numeric(2,-3) or numeric(3,5), maps to an external type
that keeps its precision and scale. Gravitino does not round or clamp it to fit a decimal.
Spark, Trino and Flink read every external type as a string: Spark StringType, Trino unbounded
VARCHAR and Flink STRING. A query needing numeric behaviour from such a column must cast it.
An unbounded bpchar maps to an external type because it compares values ignoring trailing spaces,
which neither String nor Char describes. An engine reading it as a string does not keep that
comparison.
An array keeps its element's length, precision and scale: a varchar(10)[] column reads back as a
list of Varchar(10).
A serial column's own sequence default is read as auto increment, not as a default. Recreating the
column from that metadata creates an identity column with a new sequence, which keeps value generation
but not the sequence's current value, its options or GENERATED ALWAYS. A serial column that shares a
sequence, has a custom default, or was altered to accept NULL keeps its default as an unparsed
expression and cannot be recreated.
When Gravitino creates a column:
- Time and both timestamp types without a precision become
time,timestampandtimestamptz. - Short, Integer and Long map to the same PostgreSQL type whether or not they are unsigned, because PostgreSQL has no unsigned integers.
- A list becomes a one-dimensional array of its element type. A list of lists is rejected, since
PostgreSQL does not enforce array dimensions. A list declared with non-nullable elements is created
as an ordinary array whose elements accept
NULL, because PostgreSQL array elements cannot be declared otherwise. - An external type becomes the PostgreSQL type it carries. It must be one type declaration: a type
name, optionally schema-qualified or double-quoted, with type modifiers such as
geometry(Point,4326)and an optional[]orARRAYsuffix. Multiword standard types such asdouble precisionandtimestamp with time zoneare accepted. Anything more, such as a constraint, a default or a second statement, is refused before any DDL runs. - Byte, Fixed, Map, Struct, Union and the interval types have no mapping and are rejected.
Column default values
PostgreSQL reports a literal default with a type cast, such as '2021-10-01 00:00:00'::timestamp without time zone. Gravitino removes the cast and reads the value back as a literal for bool, the integer and floating-point types, numeric, date, time, timestamp,
timestamptz, varchar, bpchar, and text. A default of NULL::{type} is read back as a null
literal, and CURRENT_TIMESTAMP as the current timestamp default.
Only complete constants are read back as literals. A plain number or true on a string column reads
back as a string, so varchar(20) DEFAULT 10 reads as 10. Functions and other expressions, and any
generated column expression, are preserved as unparsed expressions. An unparsed default can be read
but cannot be written back to PostgreSQL.
When Gravitino writes a default, the current timestamp is written as CURRENT_TIMESTAMP, any other
function expression is written inside parentheses, numeric literals are written bare, and all other literals are written in single quotes; a string containing a
backslash is written as a PostgreSQL escape string, so standard_conforming_strings cannot change its
meaning. A function default must be a function name, optionally qualified, with literal or function
arguments. Any other SQL in a default is refused. A NULL default in an alter is treated as in a
create, and the string 'NULL' stays a string.
Error mapping
PostgreSQL SQLSTATE codes are translated into Gravitino exceptions so that a caller sees the same failure types it would from any other catalog.
| PostgreSQL SQLSTATE | Gravitino exception |
|---|---|
| 42P04 duplicate database, 42P06 duplicate schema | Schema already exists |
| 42P07 duplicate table | Table already exists |
| 3D000 invalid catalog name, 3F000 invalid schema name | No such schema |
| 42P01 undefined table | No such table |
| Any code in class 08, connection exception | Connection failed |
No SQLSTATE, with a message containing password authentication failed | Connection failed |
Any other SQLSTATE produces a generic runtime exception carrying the PostgreSQL message.