Skip to content

2.27.0.0-b167

2.27.0.0-b167: [#27201] YSQL: Make CREATE RULE bump catalog version
@kai-franz kai-franz tagged this 31 May 07:14 
Summary:
Currently, `CREATE RULE` does not bump the catalog version under the assumption that creating a rule can only insert entries into system catalog tables but not update or delete them.

However, this assumption is false; we need to bump the catalog version in order to maintain behavior parity with vanilla PG.

### What are rules?

Rules allow users to specify additional commands that are to be performed automatically whenever an insert/update/delete is executed on a given table. For example:

```
CREATE RULE redirect_to_destination AS ON INSERT TO intermediate_table
    WHERE NEW.id > 25 DO INSTEAD
INSERT INTO destination_table VALUES (NEW.id, NEW.name);
```

This rule redirects inserts from `intermediate_table` to `destination_table` instead when the inserted values fall into the given range.

### Rules internals

PG stores rules in the `pg_rewrite` table. Additionally, there is a column in `pg_class` (`relhasrules`) that specifies whether the relation has rules on it.

The rules and the relhasrules flag are stored in the relcache. When we build the relcache entry for a table, first we check if `relhasrules` is true. If so, we load the rules from `pg_rewrite`; otherwise, we just set the rules to null:

```
lang=c,name=relcache.c
if (relation->rd_rel->relhasrules)
	RelationBuildRuleLock(relation);
else
{
	relation->rd_rules = NULL;
	relation->rd_rulescxt = NULL;
}
```

We read the rules during query planning in `RewriteQuery()`:

```
lang=c,name=rewriteHandler.c
locks = matchLocks(event, rt_entry_relation->rd_rules,
					result_relation, parsetree, &hasUpdate);

product_orig_rt_length = list_length(parsetree->rtable);
product_queries = fireRules(parsetree,
                            result_relation,
                            event,
                            locks,
                            &instead,
                            &returning,
                            &qual_product);
```

### How the issue occurs

The issue comes up during the following scenario:
1. On backend #1, we build the relcache entry for table `t` before there are any rules on `t`. This could be during relcache preloading, when we build the relcache entries for all user tables, or an ad-hoc load of `t`'s relcache entry.
2. Create the rule on `intermediate_table` on backend #2. This create a row in `pg_rewrite` and flips `relhasrules` to `true`, but the relcache entries in backend #1 stay the same because we don't bump the catalog version.
3. On backend #1, do an insert on `intermediate_table` that should trigger the rule. Because the relcache entry still has `rd_rules=NULL`, none of the rules will fire.
Jira: DB-16686

Test Plan:
```
./yb_build.sh release --sj --cxx-test pg_catalog_version-test --gtest-filter "*CreateRule*"
```

Reviewers: myang

Reviewed By: myang

Subscribers: yql

Differential Revision: https://phorge.dev.yugabyte.com/D44279
Assets 2
Loading