How to get the size of an index created in a plpgsql block

Question

I'm trying to benchmark something. I want to create an index and get it's size in a plpgsql block.

This works:

haki=# create index ix on t(n);
CREATE INDEX

haki=# select pg_relation_size('ix');
 pg_relation_size 
------------------
         22487040

When I try the same in a plpgsql block, I'm getting ERROR: relation "ix" does not exist:

haki=# DO $$DECLARE
haki$#     s int;
haki$# BEGIN
haki$#     PERFORM 'create index ix on t(n)';
haki$#     SELECT pg_relation_size('ix') INTO s;
haki$#     RAISE NOTICE 'size is %', s;
haki$# END$$;
ERROR:  relation "ix" does not exist
LINE 1: SELECT pg_relation_size('ix')

I'm guessing it has something to do with the fact that DDL is transactional and the block is not committed (I might also be wrong!). However, I am unable to find a way around that.

Is it possible to get the size of an index created in a plpsql block?

Answer 1

My idea is to use execute and rollback then. I would suggest you to read it "what are the difference between perform and execute on PL/pgSQL?"

DO 
$$
declare
  s int;
begin
  execute 'create index ix on t(n)';
  select pg_relation_size('ix') into s;
  raise notice 'size is %', s;
  select 1/0; -- This is for ROLLBACK
END 
$$;

NOTICE:  size is 8192
ERROR:  division by zero
CONTEXT:  SQL statement "select 1/0"
PL/pgSQL function inline_code_block line 8 at SQL statement