Cannot use `pg_ivm` extension
I can install it ok using
CREATE EXTENSION pg_ivm; and that succeeds.
But when I try to use the extension .
eg
SELECT pg_ivm.create_immv('myview', 'SELECT * FROM Proto');
I get an error
When I run SELECT table_name FROM information_schema.tables
I see a table names pg_ivm_immv
When i run SELECT * FROM pg_extension;
I get a row containing pg_ivm
73728 pg_ivm 16387 11 FALSE 1.9 {73730} {""}
I have also tried restarting the compute endpoint. - It did not help.
Am I just using it wrong, or is it not setup or installed correctly?3 Replies
absent-sapphire•2mo ago
I believe the issue here is that the create_immv function is created in the Postgres public schema, not a pg_ivm schema.
Here's a simple test case that works:
CREATE EXTENSION IF NOT EXISTS pg_ivm;
CREATE TABLE t0 (i int PRIMARY KEY);
INSERT INTO t0 VALUES (1), (2), (3);
-- Create IMMV
SELECT create_immv('m', 'SELECT * FROM t0');
-- Create an index on the IMMV
CREATE INDEX ON m (i);
-- Check contents
SELECT * FROM m;
-- Test incremental update
INSERT INTO t0 VALUES (4);
SELECT * FROM m; -- Expected: 1, 2, 3, 4
Please let us know if the schema issue is a limiting factor for you and I'll take it up withthe Dev team.
wee-brownOP•2mo ago
That was the solution. Thanks!
I guess my problem was that I was trying to use it how it is shown in the example on the pg_ivm GiitHub repo - https://github.com/sraoss/pg_ivm
SELECT pgivm.create_immv('myview', 'SELECT * FROM mytab'); which obviously wasn't working in my Neon db.GitHub
GitHub - sraoss/pg_ivm: IVM (Incremental View Maintenance) implemen...
IVM (Incremental View Maintenance) implementation as a PostgreSQL extension - sraoss/pg_ivm
absent-sapphire•2mo ago
That got me as well. We need to make a note of that in our docs.