citusdata / citusdata/citus

Updatable view's trigger can't UPDATE / DELETE from the sharded table: "stack depth limit exceeded" or "relation ... is not distributed"

Open
#5,148 2 comments 0 reactions 0 assignees View on GitHub
bug user_reported
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

Hello!

We're trying to migrate a huge database to Citus. Due to its size, we can't really do `pg_dump` or something like that, so we're trying to create a duplicate sharded table, join the unsharded and sharded tables via an updatable view, and let the application use the view while we take our time moving the rows between the underlying tables.

Unfortunately, the updatable view's trigger doesn't seem to be able UPDATE / DELETE from the sharded table even though INSERTs work fine.

Example schema:

```sql
CREATE SCHEMA unsharded;

CREATE TABLE unsharded.test (
id BIGSERIAL PRIMARY KEY,
test TEXT
);

CREATE INDEX unsharded_test_test ON unsharded.test (test);

INSERT INTO unsharded.test (test) VALUES ('test');

CREATE SCHEMA sharded;

CREATE TABLE sharded.test (
id BIGSERIAL PRIMARY KEY,
test TEXT
);

SELECT create_distributed_table('sharded.test', 'id');

CREATE INDEX sharded_test_test ON sharded.test (test);

-- Make sharded_test's primary key start where unsharded_test left off
SELECT setval(
pg_get_serial_sequence('sharded.test', 'id'),
nextval(pg_get_serial_sequence('unsharded.test', 'id')),
false
);
```

Updatable view:

```sql
-- Create view which will UNION between unsharded and sharded tables
CREATE OR REPLACE VIEW public.test AS

SELECT id, test
FROM unsharded.test

UNION

SELECT id, test
FROM sharded.test
;

-- Make INSERT ... RETURNING work
ALTER VIEW public.test
ALTER COLUMN id
SET DEFAULT nextval(pg_get_serial_sequence('sharded.test', 'id'));
```

Updatable view's trigger to do INSERTs / UPDATEs / DELETEs:

```sql
CREATE OR REPLACE FUNCTION public.test_view_insert_update_delete() RETURNS trigger AS $$
BEGIN

IF (TG_OP = 'INSERT') THEN

-- Insert only into sharded table

INSERT INTO sharded.test SELECT NEW.*;

RETURN NEW;

ELSIF (TG_OP = 'UPDATE') THEN

-- Update both tables

UPDATE unsharded.test SET
id = NEW.id,
test = NEW.test
WHERE id = OLD.id;

UPDATE sharded.test SET
id = NEW.id,
test = NEW.test
WHERE id = OLD.id;

RETURN NEW;

ELSIF (TG_OP = 'DELETE') THEN

-- Delete from both tables

DELETE FROM unsharded.test
WHERE id = OLD.id;

DELETE FROM sharded.test
WHERE id = OLD.id;

-- Return deleted rows
RETURN OLD;

ELSE
RAISE EXCEPTION 'Unconfigured operation: %', TG_OP;

END IF;

END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_view_insert_update_delete
INSTEAD OF INSERT OR UPDATE OR DELETE ON public.test
FOR EACH ROW EXECUTE PROCEDURE public.test_view_insert_update_delete();
```

With all that in place, SELECTs work fine:

```sql
# SELECT * FROM test;
id | test
----+------
1 | test
(1 row)
```

So do INSERTs:

```sql
# INSERT INTO test (test) VALUES ('test2') RETURNING *;
id | test
----+-------
2 | test2
(1 row)

INSERT 0 1

# SELECT * FROM test;
id | test
----+-------
1 | test
2 | test2
(2 rows)
```

However UPDATEs / DELETEs fail with either *stack depth limit exceeded* (when the condition includes a primary key):

```sql
# UPDATE test SET test = 'test3' WHERE id = 2;
ERROR: stack depth limit exceeded
HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.

# DELETE FROM test WHERE id = 2;
ERROR: stack depth limit exceeded
HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
```

or *relation ... is not distributed* if it doesn't:

```sql
# UPDATE test SET test = 'test3' WHERE test = 'test2';
ERROR: relation test is not distributed

# UPDATE test SET test = 'test3';
ERROR: relation test is not distributed

# DELETE FROM test WHERE test = 'test2';
ERROR: relation test is not distributed

# DELETE FROM test;
ERROR: relation test is not distributed
```

Possibly related: #2046, #2216

Is there a workaround to make it work?

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.