[Tablet Splitting] Upgrade test with Colocation(50-100 tables) + non colocated(2-4) tables + Collation + PITR + Backup/Restore + MV + tablet Splitting + indexes +RPC + DDLs

[agsh-yb]

Jira Link: DB-5619

Description

Scenario attempted on build 2.17.2.0-b151 This scenario is automated and it is reproducible 100% of the time

Steps to reproduce issue:

1. Create universe with automatic splitting enabled + RPC(algo=2)
2. Create Databases(colocated) and Create colocated(50 tables) and Create two complex tables (1 Hashed 1 Range) (same schema of colocated and complex schema)
3. Load data to tables(100 rows in colocated table) (10000 rows in complex tables) and collect tablets info
4. Create Unique, Secondary and Partial indexes for both tables and colocated table.
5. Create Snapshot Schedule
6. Create a temporary table(with same schema) and load data (2500 rows)
7. Copy data to a file using COPY TO query: COPY temp TO '/tmp/data'
8. Copy data from files to colocated table and complex tables using COPY FROM query: COPY tab_hash FROM '/tmp/data'
9. Collect time(t1)
10. Drop colocated tables and validate
11. Load data to complex tables(100 rows) and validate
12. PITR to time(1)

Issue: PITR at time(1) fails

Error running restore_snapshot_schedule: Not implemented (yb/master/restore_sys_catalog_state.cc:747): Unable to restore as Pg sequences were updated since the Restore time.

Attached log files

2.17.2.0_testtabletpitrcolocation-aws-rf3-upgrade-2.12.11.0-b34_20230220_181904.zip

G-Flags

MASTER VALUE & T-SERVER VALUE

flags = {
                "enable_stream_compression": "true",
                "stream_compression_algo": "2",
                "enable_automatic_tablet_splitting": "true"
            }

Schema used:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TYPE complex AS (re float8, im float8);
CREATE TYPE e_details AS ENUM ('Email', 'Sms', 'Phone');
CREATE DOMAIN postal_code AS TEXT CHECK(VALUE ~ '^\d{5}$'OR VALUE ~ '^\d{5}-\d{4}$');

Range Sharded table-

CREATE TABLE tab_range (
  id TEXT, 
  uuid_col uuid DEFAULT uuid_generate_v4(), 
  name text COLLATE "en_US.utf8", 
  c complex, 
  info json, 
  contact JSONB, 
  arr smallint[], 
  cash money, 
  i inet, 
  m macaddr, 
  i2 serial, 
  i3 bigserial, 
  val smallint, 
  details e_details, 
  age int, 
  collated_data text collate "POSIX", 
  date DATE, 
  n NUMERIC (3, 2), 
  r real, 
  c1 CHAR(1), 
  created_at timestamptz, 
  uuid0 uuid DEFAULT uuid_nil(), 
  uuid1 uuid DEFAULT uuid_generate_v1(), 
  p1 POINT, 
  t1 TIME, 
  ts1 TIMESTAMP, 
  i4 INTERVAL, 
  p2 path, 
  p3 polygon, 
  b box, 
  c2 circle, 
  l line, 
  l1 lseg, 
  a2 text[][], 
  zip postal_code, 
  PRIMARY KEY (id ASC)
);

Hash Sharded table-

CREATE TABLE tab_hash (
  id TEXT, 
  uuid_col uuid DEFAULT uuid_generate_v4(), 
  name text COLLATE "en_US.utf8", 
  c complex, 
  info json, 
  contact JSONB, 
  arr smallint[], 
  cash money, 
  i inet, 
  m macaddr, 
  i2 serial, 
  i3 bigserial, 
  val smallint, 
  details e_details, 
  age int, 
  collated_data text collate "POSIX", 
  date DATE, 
  n NUMERIC (3, 2), 
  r real, 
  c1 CHAR(1), 
  created_at timestamptz, 
  uuid0 uuid DEFAULT uuid_nil(), 
  uuid1 uuid DEFAULT uuid_generate_v1(), 
  p1 POINT, 
  t1 TIME, 
  ts1 TIMESTAMP, 
  i4 INTERVAL, 
  p2 path, 
  p3 polygon, 
  b box, 
  c2 circle, 
  l line, 
  l1 lseg, 
  a2 text[][], 
  zip postal_code,
  PRIMARY KEY (id)
);

Colocated table-
CREATE TABLE co_tab (
  id TEXT PRIMARY KEY, 
  uuid_col uuid DEFAULT uuid_generate_v4(), 
  name text COLLATE "en_US.utf8", 
  c complex, 
  info json, 
  contact JSONB, 
  arr smallint[], 
  cash money, 
  i inet, 
  m macaddr, 
  i2 serial, 
  i3 bigserial, 
  val smallint, 
  details e_details, 
  age int, 
  collated_data text collate "POSIX", 
  date DATE, 
  n NUMERIC (3, 2), 
  r real, 
  c1 CHAR(1), 
  created_at timestamptz, 
  uuid0 uuid DEFAULT uuid_nil(), 
  uuid1 uuid DEFAULT uuid_generate_v1(), 
  p1 POINT, 
  t1 TIME, 
  ts1 TIMESTAMP, 
  i4 INTERVAL, 
  p2 path, 
  p3 polygon, 
  b box, 
  c2 circle, 
  l line, 
  l1 lseg, 
  a2 text[][], 
  zip postal_code
) WITH (COLOCATED = true);

Load data --

DO $do$ BEGIN FOR i IN 1..10000 LOOP INSERT INTO tab_hash(
  id, name, c, info, contact, arr, cash, 
  i, m, i2, i3, val, details, age, collated_data, 
  date, n, r, c1, created_at, p1, t1, ts1, 
  i4, p2, p3, c2, l, l1, a2, zip
) 
VALUES 
  (
    i, 
    'Hermione', 
    (1, 2), 
    '{"customer": "John Doe", "items": {"product": "Laptop","qty": 6}}', 
    '{"phones":[ {"type": "mobile", "phone": "001001"}, {"type": "fix", "phone": "002002"}]}', 
    '{1, 2, 3}', 
    '$99.99', 
    '1.1.1.1', 
    '00:00:00:00:00:00', 
    i, 
    i + 1, 
    666, 
    'Sms', 
    15, 
    md5(
      random():: text
    ), 
    current_timestamp, 
    5.36, 
    2147483647, 
    'G', 
    now(), 
    point(3, 4), 
    LOCALTIME(0), 
    current_timestamp(0), 
    '00:25:00', 
    '(1,3), (4,12)', 
    '(1,3),(4,12), (2,4)', 
    '10, 4, 10', 
    '{1,2,3}', 
    '(0, 4), (2,8)', 
    '{{"b1", "c"}, {"m1", "l"}}', 
    89000
  );
END LOOP;
END $do$;

CC @kripasreenivasan

[tverona1]

Based on the error message, this looks like a dup of https://github.com/yugabyte/yugabyte-db/issues/11875.

[agsh-yb]

design on 2.12 but fixed on 2.14+