[YSQL] Handle backfill index inside transaction block

[jaki]

Jira Link: DB-2063 Index backfill inside transaction blocks may have issues. For example,

CREATE TABLE t (i int);
BEGIN;
INSERT INTO t VALUES (2);
CREATE INDEX ON t (i);
COMMIT;

ERROR:  Operation expired: Transaction expired or aborted by a conflict: 40001

Running TestPgRegressTrigger java test with YSQL index backfill enabled also fails because CREATE INDEX is in a trigger function. That inspired this example:

do $$begin
  create index on t (i);
  select 1;
end$$;

WARNING:  plancache reference leak: plan 0x30b1280 not closed
WARNING:  transaction left non-empty SPI stack
HINT:  Check for missing "SPI_finish" calls.
WARNING:  snapshot 0x1d6b440 still active
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Also, vanilla postgres's CREATE INDEX CONCURRENTLY disallowed being inside a transaction block (see utility.c). Figure out whether it is possible to use index backfill for indexes inside transaction blocks for Yugabyte tables.

In the meantime, an upcoming commit will transparently convert CREATE INDEX to be nonconcurrent for these cases. This fixes the two examples above (since they then avoid backfill). That's why this issue will be labelled as an enhancement and not a bug.

[jaki]

My previous comment references the code that blocks this. Documentation from upstream postgres also states that it doesn't support this:

Another difference is that a regular CREATE INDEX command can be performed within a transaction block, but CREATE INDEX CONCURRENTLY cannot.

https://www.postgresql.org/docs/14/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY