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';