Tim Mattison

Hardcore tech

How-To: Fix "ERROR: Function information_schema._pg_keypositions() Does Not Exist" in PostgreSQL When Using Hibernate

| Comments

I’ll keep this one short. If you’re writing and debugging a Hibernate application and you’re using PostgreSQL you may notice that you get the following error message when your application first connects to the database:

ERROR:  function information_schema._pg_keypositions() does not exist

According to a forum I came across this is because the Hibernate team ended up depending on a function in PostgreSQL 8.0 that was dropped in 8.1+. I needed to make some modifications to the code posted there to get it to work so here’s what I came up with:

CREATE FUNCTION information_schema._pg_keypositions() RETURNS SETOF integer
AS $pg_keypositions$
select g.s
from generate_series(1,current_setting('max_index_keys')::int, 1)
as g(s)

Once you run this code in your database you’ll never be bothered with that error message again. Basically this just gets the max_index_keys value and generates a list of integers from 1 to that value, inclusive. Hibernate can live without it but removing a repetitive error message will keep your DBA and sysadmins sane a bit longer.