Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

docdb: [PITR-YSQL] PITR not working with Drop Index; table not getting restored pre-index drop #10256

Closed
kripasreenivasan opened this issue Oct 12, 2021 · 2 comments
Assignees
Projects

Comments

@kripasreenivasan
Copy link
Contributor

kripasreenivasan commented Oct 12, 2021

Observed in builds: 2.9.1.0 build 132, 2.8.0.0-b5

  • Create a table with an index and note the absolute time post table creation as this is the point we would like to revert to.
  • Drop the index with the equivalent of the ysql command: drop index order_index;
  • Execute \d+ <table_name> and check if the index is dropped.
  • Do a Point in time restore to the point after step 1.
    Expected result:
    The dropped index should be restored and the index should be present in the table.
    Actual Result:
    The index continues to remain dropped even after the PITR making PITR ineffective with the drop index command.
@kripasreenivasan kripasreenivasan added this to To do in PITR via automation Oct 12, 2021
@kripasreenivasan kripasreenivasan removed their assignment Oct 12, 2021
@Arjun-yb
Copy link
Contributor

DB Version: 2.9.1.0-b133
Additional observations:

  1. restore is not working as mentioned above ( index name is not being displayed if user describe table) but if user creates index with same name again it errors out - ex: ERROR: relation "dept_idx_1" already exists
  2. UI shows the index name even after it failed to restore the deleted index.
  3. scan is not using the index
demo=# explain select * from employees where department='Sales';
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on employees  (cost=0.00..102.50 rows=1000 width=72)
   Filter: (department = 'Sales'::text)

then I created one more with different name and output is

demo=# create index dept_idx_2 on employees(department);
CREATE INDEX
demo=# explain select * from employees where department='Sales';
                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using dept_idx_2 on employees  (cost=0.00..5.22 rows=10 width=72)
   Index Cond: (department = 'Sales'::text)

@sanketkedia sanketkedia changed the title PITR: PITR not working with Drop Index; table not getting restored pre-index drop docdb: [PITR-YSQL] PITR not working with Drop Index; table not getting restored pre-index drop Oct 17, 2021
sanketkedia added a commit that referenced this issue Nov 11, 2021
…ables

Summary:
Our current logic of iterating over the pg_catalog and computing the difference between the
state of the tables at present v/s at the restoration time is incorrect. In particular, it skips
over the actual row of the table on encountering a tombstoned entry for the same row irrespective of
when they were written.

Fix is to not skip entries whose subkeys are prefixes of one another. Also, if we don't skip them
then we need to properly handle tombstoned entries and their effects on other entries. For instance
an entry could be tombstoned after inserting and while computing the venn diagram of differences we
should take into account that this entry has been deleted.

In terms of time taken for restores, there's not much increase in restore time after this change. It is still the same number of seconds approximately.

Test Plan: Jenkins

Reviewers: bogdan, sergei

Reviewed By: sergei

Subscribers: ybase

Differential Revision: https://phabricator.dev.yugabyte.com/D13705
sanketkedia added a commit that referenced this issue Nov 22, 2021
…ystem Catalog tables

Summary:
Our current logic of iterating over the pg_catalog and computing the difference between the
state of the tables at present v/s at the restoration time is incorrect. In particular, it skips
over the actual row of the table on encountering a tombstoned entry for the same row irrespective of
when they were written.

Fix is to not skip entries whose subkeys are prefixes of one another. Also, if we don't skip them
then we need to properly handle tombstoned entries and their effects on other entries. For instance
an entry could be tombstoned after inserting and while computing the venn diagram of differences we
should take into account that this entry has been deleted.

In terms of time taken for restores, there's not much increase in restore time after this change. It is still the same number of seconds approximately.

Original commit: D13705 / ac351ab

Test Plan: Jenkins: rebase: 2.8

Reviewers: bogdan, sergei

Reviewed By: sergei

Subscribers: ybase

Differential Revision: https://phabricator.dev.yugabyte.com/D13982
@Arjun-yb
Copy link
Contributor

Arjun-yb commented Dec 9, 2021

Validated in 2.11.1.0-b300 and works as expected, closing this issue @sanketkedia

@Arjun-yb Arjun-yb closed this as completed Dec 9, 2021
PITR automation moved this from To do to Done Dec 9, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
PITR
Done
Development

No branches or pull requests

3 participants