Continuous aggregate: unrecognized parameter namespace "timescaledb"

[kmatt]

Relevant system information:

• OS: Centos 8.2
• PostgreSQL version: 12.5
• TimescaleDB version: 1.7.4
• Installation method: rpm

# select * from timescaledb_information.license;
  edition  | expired | expiration_time
-----------+---------+-----------------
 community | f       | infinity

Describe the bug
https://docs.timescale.com/latest/api#continuous-aggregates suggests that continuous aggregates are a community license feature, but upon attempting to create a materialized view get error unrecognized parameter namespace "timescaledb"

To Reproduce
Steps to reproduce the behavior:

CREATE MATERIALIZED VIEW test.matview
WITH (timescaledb.continuous) AS
SELECT ...

Actual behavior
unrecognized parameter namespace "timescaledb"

Additional context
Similar to #1953 this instance was originally installed from the pgdg12 repo.

With Postgres services stopped, that was uninstalled, and all Timescale objects removed from /usr/pgsql-12.

timescaledb-postgresql-12.x86_64 was reinstalled from the @timescale_timescaledb repo at version 1.7.4-0.el8, and verified as below. Could this process be insufficient?

rpm -q -i timescaledb-postgresql-12-1.7.4-0.el8.x86_64
Name        : timescaledb-postgresql-12
Version     : 1.7.4
Release     : 0.el8
Architecture: x86_64
Install Date: Thu 10 Dec 2020 12:34:25 AM UTC
Group       : Unspecified
Size        : 6021626
License     : Apache-2.0 and Timescale License
Signature   : (none)
Source RPM  : timescaledb-postgresql-12-1.7.4-0.el8.src.rpm
Build Date  : Wed 09 Sep 2020 07:42:37 PM UTC
Build Host  : c8b07ad6ace5
Relocations : (not relocatable)
URL         : https://www.timescale.com

[mfreed]

Hi @kmatt -

The syntax CREATE MATERIALIZED VIEW for continuous aggs was introduced in 2.0. Prior to that, it was just CREATE VIEW. Please try that and let me know if you continue to see any issues.

• 1.7: https://docs.timescale.com/v1.7/api#continuous_aggregate-create_view
• 2.0: https://docs.timescale.com/v2.0/api#continuous_aggregate-create_view

[kmatt]

@mfreed That was indeed the problem. I was referencing the 2.0 docs while using the 1.7 extension.

[sudeepigntion]

SQL Error [22023]: ERROR: unrecognized parameter namespace "timescaledb"
TimeScale DB Version 2.3

[AstrOsu]

Had the same issue as sudeepignition.

CREATE EXTENSION timescaledb SCHEMA public VERSION "2.3.0";

Tried a 'create materialized hypertable' that works on other psql dbs with timescale ver 2.3, but for this particular db I got:

ERROR: unrecognized parameter namespace "timescaledb"
SQL state: 22023

[harin]

I am getting the same error using timescaledb version 2.5.2 from a pre-built container timescale/timescaledb:latest-pg14 with image_id 068da8e48541. I tried checking whether this is a community edition image, but couldn't find any documents on it.

[rollue]

I am getting the same error using timescaledb version 2.5.2 from a pre-built container timescale/timescaledb:latest-pg14 with image_id 068da8e48541. I tried checking whether this is a community edition image, but couldn't find any documents on it.

I'm seeing the same error

[harin]

Upgrading to https://hub.docker.com/layers/timescaledb/timescale/timescaledb/2.6.1-pg14/images/sha256-b4a8bcc7a0fdacc64d10bd72b8d9997c158a0ab4243c8f2b737639550e1155ec?context=explore seems to fix the issue.

[clouedoc]

I'm getting a similar error with a Kubernetes TimescaleDB deployment.
PostgreSQL reports TimescaleDB version 2.8.0 being used.

CREATE MATERIALIZED VIEW account_event_hourly
WITH (timescaledb.continous)
[...]

ERROR:  unrecognized parameter "timescaledb.continous"

Edit: that was just a typo... timescaledb.continuous works

[nodakai]

In my case it was my typo timescaledb.compress_segment**ed**by

So unrecognized parameter namespace "timescaledb" suggests the extension was not set up correctly, whereas unrecognized parameter "timescaledb.blah" suggests there might be a typo!

[Malvineous]

Since this is a top Google result, I got the same error while experimenting with creating and dropping tables. It happened because I re-ran my queries in the wrong order.

Turns out if you try to enable TimescaleDB compression before turning the table into a hypertable with create_hypertable() then you'll get the error SQL Error [22023]: ERROR: unrecognized parameter namespace "timescaledb".