sqlpostgresqlpostgresql-17postgresql-18

Why doesn't index creation work any more in Postgres v17 and v18?


Considering the following SQL code:

create table test (a int, b int) ;
create type tst as (a int, b int) ;
create function tst (a int, b int) returns tst[] language sql immutable as 
  $$ select array( select row(a,b) :: tst) ; $$ ;
create index index_test on test using gin (tst(a,b) array_ops) ;

This SQL code works well with Postgres until v16, see dbfiddle, but it doesn't work any more with Postgres v17 and v18, see dbfiddle.

ERROR: type "tst" does not exist
LINE 1: select array( select row(a,b) :: tst) ;
QUERY: select array( select row(a,b) :: tst) ;
CONTEXT: SQL function "tst" during inlining

What has changed with Postgres v17 & v18, and how to fix this issue?


Solution

  • For security reasons, PostgreSQL commit 2af07e2f7 forces the search_path to be empty during CREATE INDEX. That makes the type lookup fail when the function is inlined.

    One solution is to write

    create function tst (a int, b int) returns tst[] language sql immutable as 
      $$ select array( select row(a,b) :: myschema.tst) ; $$ ;
    

    where myschema is the schema of the tst type.

    The alternative is to define the SQL function using standard-conforming syntax:

    CREATE FUNCTION tst (a int, b int) RETURNS tst[] IMMUTABLE
    RETURN array[row(a,b) :: tst];
    

    That works, because the function body is parsed during CREATE FUNCTION, but has the disadvantage that the function body is not inlined in the index definition.