vkuzel.com

Spring Boot + PostgreSQL + Custom Type = exception!

2016-06-12

I do have demo application that shows features of various ORM frameworks. Application performs several basic operation on a database. To have clean database for each application's run I wanted to recreate whole database at its start. This means dropping whole schema and then re-initialising it by executing following script.

DROP SCHEMA IF EXISTS public CASCADE;
CREATE SCHEMA public;

CREATE TYPE plane_dimensions AS (
  length_meters   DECIMAL,
  wingspan_meters DECIMAL,
  height_meters   DECIMAL
);

CREATE TABLE planes (
  id           BIGSERIAL PRIMARY KEY,
  name         VARCHAR(250)     NOT NULL,
  dimensions   plane_dimensions NOT NULL
);

...

Unfortunately this doesn't work.

When the application connects to the database it loads existing types and stores it to a cache. Then my type is dropped and immediately re-created by the script. But re-created type does have different OID than the former (cached). So if you try to use it from the application you will get an exception.

Caused by: org.postgresql.util.PSQLException: ERROR: cache lookup failed for type 1234567

Solution is to re-initialise connection or not to modify the type.

Query to find type's OID:

SELECT oid FROM pg_type WHERE typname = 'plane_dimensions';