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.

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)
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)
varchar(n)Varchar(n)
textString
byteaBinary
uuidUUID
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.

An array's element type is mapped by its name alone, so an element's length or precision is not kept: a varchar(10)[] column reads back as a list of String.

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, so any PostgreSQL type can be created by name.
  • 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.

Any other default, such as the nextval(...) default of a serial column, and any generated column expression, is preserved as an unparsed expression.

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.

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.

Last updated on