Skip to main content

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.

  1. Start the catalog. Open Connect → Catalogs and click + Catalog. Enter pg_catalog_demo as the Catalog name, keep Relational as the Catalog type, and choose PostgreSQL under Databases and warehouses in Select provider.

  2. 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 in jdbc-driver from the provider.

  3. Test and create it. Click Test connection, then Create catalog.

  4. List the schemas. In Explore, open pg_catalog_demo. Gravitino returns the schemas in that database, with the system schemas filtered out.

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.

Define primary and unique keys
{
"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​

ChangeSupported
Rename tableYes, as the only change in its request
Update table commentYes
Add columnYes, at the default position only
Delete columnYes
Rename columnYes
Update column typeYes
Update column nullableYes
Update column defaultYes
Update column commentYes
Update column auto incrementYes, by adding or dropping the identity
Add or delete indexYes, for primary key and unique key
Update column positionNo, PostgreSQL cannot reorder columns
Set or remove propertyNo

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 typeGravitino type
boolBoolean
int2Short
int4Integer
int8Long
float4Float
float8Double
numeric(p,s)Decimal(p, s), when p is at most 38 and s is 0 to p
dateDate
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 lengthExternal type bpchar
varchar(n)Varchar(n)
varchar with no lengthString
textString
byteaBinary
uuidUUID
smallserial, serial, bigserialShort, Integer, Long, with auto increment
An array of any element typeList of that element type, with nullable elements
numeric with no precisionExternal type numeric
Any other typeExternal 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, timestamp and timestamptz.
  • 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 [] or ARRAY suffix. Multiword standard types such as double precision and timestamp with time zone are 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 SQLSTATEGravitino exception
42P04 duplicate database, 42P06 duplicate schemaSchema already exists
42P07 duplicate tableTable already exists
3D000 invalid catalog name, 3F000 invalid schema nameNo such schema
42P01 undefined tableNo such table
Any code in class 08, connection exceptionConnection failed
No SQLSTATE, with a message containing password authentication failedConnection failed

Any other SQLSTATE produces a generic runtime exception carrying the PostgreSQL message.