[YSQL] Support CREATE INDEX CONCURRENTLY #10799
Assignees
Labels
Comments
|
We don't support the grammar "CONCURRENTLY" but we do concurrently by default. |
|
CONCURRENTLY is optional, can we do this as no-op syntax for PostgreSQL compatibility, since all indexes are created concurrently in YugabyteDB. |
lnguyen-yugabyte
added a commit
that referenced
this issue
May 6, 2022
Summary: During the development of D5955, the index created is not populated nor used to serve DML statements yet. At that time, `CREATE INDEX CONCURRENTLY` was not supported. Now we add support for such feature. A difference between YB and vanilla postgres is that YB supports for `CREATE INDEX NONCONCURRENTLY`, https://docs.yugabyte.com/preview/api/ysql/the-sql-language/statements/ddl_create_index/ and hence in the grammar we need to handle three cases: CONCURRENTLY, NONCONCURRENTLY, and /* EMPTY */, as opposed to postgres where it only needs to handle CONCURRENTLY and /* EMPTY */. In addition, in YB's grammar, /* EMPTY */ can either be mapped to concurrency being enabled or disabled depending on whether online backfill is enabled. As such, we deviate from postgres by introducing `YbConcurrencyContext` to capture the value for `concurrent`, which can take the value of explicitly enabled, implicitly enabled or disabled. When CONCURRENTLY is explicitly specified, we can throw error, for example, in the following cases (reference https://www.postgresql.org/docs/current/sql-createindex.html): * A concurrent index build is called in a transaction block * A concurrent index build is called on a partitioned table. Otherwise, when concurrency is implicitly defined, we can downgrade it when the operation does not allow for concurrent index build. Test Plan: ``` ybd --java-test org.yb.pgsql.TestPgRegressIndex ybd --java-test org.yb.pgsql.TestYsqlUpgrade ybd --java-test org.yb.pgsql.TestPgRegressTypesUDT ``` Reviewers: smishra, mihnea, jason Reviewed By: jason Subscribers: jason, yql Differential Revision: https://phabricator.dev.yugabyte.com/D16574
@ymahajan : There are certain cases that one needs to error (as Postgres don't support it):
I added a change to address these differences. |
lnguyen-yugabyte
added a commit
that referenced
this issue
May 6, 2022
…ENTLY Summary: Original diff / commit: D16574 / baf1b47 During the development of D5955, the index created is not populated nor used to serve DML statements yet. At that time, `CREATE INDEX CONCURRENTLY` was not supported. Now we add support for such feature. A difference between YB and vanilla postgres is that YB supports for `CREATE INDEX NONCONCURRENTLY`, https://docs.yugabyte.com/preview/api/ysql/the-sql-language/statements/ddl_create_index/ and hence in the grammar we need to handle three cases: CONCURRENTLY, NONCONCURRENTLY, and /* EMPTY */, as opposed to postgres where it only needs to handle CONCURRENTLY and /* EMPTY */. In addition, in YB's grammar, /* EMPTY */ can either be mapped to concurrency being enabled or disabled depending on whether online backfill is enabled. As such, we deviate from postgres by introducing `YbConcurrencyContext` to capture the value for `concurrent`, which can take the value of explicitly enabled, implicitly enabled or disabled. When CONCURRENTLY is explicitly specified, we can throw error, for example, in the following cases (reference https://www.postgresql.org/docs/current/sql-createindex.html): * A concurrent index build is called in a transaction block * A concurrent index build is called on a partitioned table. Otherwise, when concurrency is implicitly defined, we can downgrade it when the operation does not allow for concurrent index build. Test Plan: ``` ybd --java-test org.yb.pgsql.TestPgRegressIndex ybd --java-test org.yb.pgsql.TestYsqlUpgrade ybd --java-test org.yb.pgsql.TestPgRegressTypesUDT ``` Reviewers: smishra, jason, mihnea Reviewed By: mihnea Subscribers: yql, jason Differential Revision: https://phabricator.dev.yugabyte.com/D16844
paullee-yb
added a commit
that referenced
this issue
Sep 26, 2022
…ions be security-restricted operations. Summary: **Upstream commit:** a117cebd638dd02e5c2e791c25e43745f233111b **Original commit summary** ``` Make relation-enumerating operations be security-restricted operations. When a feature enumerates relations and runs functions associated with all found relations, the feature's user shall not need to trust every user having permission to create objects. BRIN-specific functionality in autovacuum neglected to account for this, as did pg_amcheck and CLUSTER. An attacker having permission to create non-temp objects in at least one schema could execute arbitrary SQL functions under the identity of the bootstrap superuser. CREATE INDEX (not a relation-enumerating operation) and REINDEX protected themselves too late. This change extends to the non-enumerating amcheck interface. Back-patch to v10 (all supported versions). Sergey Shinderuk, reviewed (in earlier versions) by Alexander Lakhin. Reported by Alexander Lakhin. Security: CVE-2022-1552 ``` **Notes** When users run `CREATE INDEX` and `REINDEX` with a predicate, the security context of the operation switches from the relation's owner to a superuser. This is a security vulnerability as an attacker could execute arbitrary SQL functions under the identity of a superuser. This commit resolves this issue by making the predicate clause to run under the identity of the index owner, not a superuser. In the context of YB, since index creation involves a backfill codepath, we will have to set the security context in an additional location compared to the original commit. In addition, changes in `index_concurrently_build()`in the original patch have been translated to YbBackfill(). Lastly, we currently don't support `REINDEX CONCURRENTLY`, `CLUSTER`, nor BRIN indexes, so we will not include related tests in our regression test suite. The `CREATE ROLE regress_sro_user;` added in the test is related to the PG port made in #10266. **Backport-specific notes** `CREATE INDEX CONCURRENTLY` isn't supported in 2.12, so related logic + tests has been removed from this change. Original GH issue for `CREATE INDEX CONCURRENTLY`: #10799 Test Plan: ybd --java-test 'org.yb.pgsql.TestPgRegressAuthorization' Reviewers: jason, zyu Reviewed By: zyu Subscribers: smishra Differential Revision: https://phabricator.dev.yugabyte.com/D19764
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Description
Currently we don't support creating index concurrently.
This issue tracks adding support for concurrent index creation.
The text was updated successfully, but these errors were encountered: