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

haki=# select pg_relation_size('ix');

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?

  • 2
    Have you tried without using PERFORM? dbfiddle.uk/… – McNets Jan 19 '19 at 17:04
  • Thanks MacNets, I haven't. How come it's working without perform? – haki Jan 20 '19 at 18:32
  • If you like a traditional approach, here you are begin; create index ix on t1(a); select pg_relation_size('ix'); rollback; – Luan Huynh Jan 20 '19 at 21:19

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?"

  s int;
  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

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.