[YSQL] CREATE MATERIALIZED VIEW on a colocated table restored via PITR fails with transaction metadata missing

[archit-rastogi]

Jira Link: DB-11712

Description

Steps to repro:

1. Create colocated database and connect.
2. Enable PITR on the database
3. Create a table tbl1 and insert some rows
4. note current time as t1
5. drop table tbl1 created in step(3)
6. Do pitr restore to time t1 noted in step (4)
7. Table must be recreated awith all rows. check with select query.
8. Create matview fails.

Example:

yugabyte=# create database colo1 with colocation=true;
CREATE DATABASE

>>>>>> Enable PITR on database colo1

yugabyte=# \c colo1

Time: 248.878 ms
colo1=# create table t1 (c1 serial, c2 varchar(20), c3 smallint, primary key(c1));
CREATE TABLE
Time: 814.821 ms

colo1=# insert into t1 values (generate_series(1,10), 'some text', generate_series(11,20));
INSERT 0 10
Time: 547.627 ms

colo1=# select * from t1;
 c1 |    c2     | c3 
----+-----------+----
  1 | some text | 11
  2 | some text | 12
  3 | some text | 13
  4 | some text | 14
  5 | some text | 15
  6 | some text | 16
  7 | some text | 17
  8 | some text | 18
  9 | some text | 19
 10 | some text | 20
(10 rows)

Time: 255.377 ms
colo1=# select current_timestamp;
       current_timestamp       
-------------------------------
 2024-06-11 11:35:01.633145+00
(1 row)

Time: 244.962 ms
colo1=# insert into t1 values (generate_series(11,20), 'some text', generate_series(21,30));
INSERT 0 10
Time: 251.537 ms
colo1=# drop table t1;

DROP TABLE
Time: 450.942 ms

>>>>> Do PITR Restore to time before dropping the table
>>> AFTER restore....

colo1=# \d
            List of relations
 Schema |   Name    |   Type   |  Owner   
--------+-----------+----------+----------
 public | t1        | table    | yugabyte
 public | t1_c1_seq | sequence | yugabyte
(2 rows)

colo1=# select * from t1;
 c1 |    c2     | c3 
----+-----------+----
  1 | some text | 11
  2 | some text | 12
  3 | some text | 13
  4 | some text | 14
  5 | some text | 15
  6 | some text | 16
  7 | some text | 17
  8 | some text | 18
  9 | some text | 19
 10 | some text | 20
(10 rows)

Time: 249.175 ms
colo1=# create materialized view v1 as select * from t1;
ERROR:  [Operation failed. Try again (yb/tablet/tablet.cc:1375): Transaction metadata missing: 3cefbd7b-f805-4af7-98f7-8ba1839b3594, looks like it was just aborted (pgsql error 40001)]
Time: 404.420 ms

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

• [X] I confirm this issue does not contain any sensitive information.

[archit-rastogi]

After some time when we retry, create materialized view is successful.