[Bug]: XX000 - ERROR: unrecognized lock mode: 0

[mwahlhuetter]

What type of bug is this?

Crash, Unexpected error

What subsystems and features are affected?

Continuous aggregate

What happened?

When I tried to use the new join functionality for continuous aggregates, I got an error message when trying to query the cagg.

ERROR:  attribute 2 of relation "trade" does not exist
SQL state: XX000

I then created a minimal reproduction, but this resulted in a different error. My PgAdmin connection crashes instantly, IntelliJ IDEA though gave me this error message. Reproduction for this error is below.
[XX000] ERROR: unrecognized lock mode: 0

Should I create a separate issue for the first error? I have not been able to reproduce the first one yet.

TimescaleDB version affected

2.10.1

PostgreSQL version used

Debian 14.7-1.pgdg110+1

What operating system did you use?

Debian GNU/Linux 11 Kernel: Linux 5.10.0-21-amd64

What installation method did you use?

Deb/Apt

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

No response

How can we reproduce the bug?

CREATE TABLE normal_table_bug
(
    normal_id smallint PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE hypertable_bug (
    time timestamptz NOT NULL,
	amount numeric NOT NULL,
	normal_id smallint NOT NULL REFERENCES normal_table_bug (normal_id)
);

SELECT create_hypertable('hypertable_bug', 'time');

INSERT INTO normal_table_bug (normal_id, name)
VALUES (1, 'normal_row_1');

INSERT INTO hypertable_bug (time, amount, normal_id)
VALUES
(now(), 30, 1),
(now(), 20, 1),
(now() - interval '1 day', -40, 1),
(now() - interval '1 day', 60, 1);

CREATE MATERIALIZED VIEW cagg_join_bug
WITH (timescaledb.continuous) AS
SELECT
	time_bucket(interval '1 day', h.time) AS "time",
	n.name,
	SUM(h.amount)
FROM hypertable_bug h
	INNER JOIN normal_table_bug n ON n.normal_id = h.normal_id
GROUP BY 1, n.name;

-- this throws an error or completely loses connection
SELECT * FROM cagg_join_bug;

--DROP MATERIALIZED VIEW cagg_join_bug;
--DROP TABLE hypertable_bug;
--DROP TABLE normal_table_bug;

[lkshminarayanan]

Hi @mwahlhuetter!

Thank you for bringing this to our notice! I was able to reproduce the second issue (i.e) the [XX000] ERROR: unrecognized lock mode: 0 case with the queries you mentioned but I'm not sure what could have caused the first error.

Should I create a separate issue for the first error? I have not been able to reproduce the first one yet.

It will be really helpful if you can file a new issue for that error with reproducible queries, so that we can look into this.

Thank you!

[lkshminarayanan]

In debug build, postgres hits an Assert when the given queries are executed.

Relevant stack trace :

Program received signal SIGABRT, Aborted.
0x00007eff1d7b88ec in ?? () from /usr/lib/libc.so.6
(gdb) bt
#0  0x00007eff1d7b88ec in ?? () from /usr/lib/libc.so.6
#1  0x00007eff1d769ea8 in raise () from /usr/lib/libc.so.6
#2  0x00007eff1d75353d in abort () from /usr/lib/libc.so.6
#3  0x0000564a895827c7 in ExceptionalCondition (conditionName=0x564a896612f0 "lockmode != NoLock || IsBootstrapProcessingMode() || CheckRelationLockedByMe(r, AccessShareLock, true)", 
    errorType=0x564a89661286 "FailedAssertion", fileName=0x564a89661230 "/postgres/sources/15.2/src/backend/access/common/relation.c", lineNumber=68)
    at /postgres/sources/15.2/src/backend/utils/error/assert.c:69
#4  0x0000564a889b7ead in relation_open (relationId=17108, lockmode=0) at /postgres/sources/15.2/src/backend/access/common/relation.c:68
#5  0x0000564a88b0f1f0 in table_open (relationId=17108, lockmode=0) at /postgres/sources/15.2/src/backend/access/table/table.c:43
#6  0x0000564a891ce05c in AcquireRewriteLocks (parsetree=0x564a8a86eed0, forExecute=true, forUpdatePushedDown=false)
    at /postgres/sources/15.2/src/backend/rewrite/rewriteHandler.c:190
#7  0x0000564a891ce459 in AcquireRewriteLocks (parsetree=0x564a8a61bf80, forExecute=true, forUpdatePushedDown=false)
    at /postgres/sources/15.2/src/backend/rewrite/rewriteHandler.c:269
#8  0x0000564a891d3141 in ApplyRetrieveRule (parsetree=0x564a8a61c098, rule=0x564a8a719870, rt_index=1, relation=0x7eff1d727a98, activeRIRs=0x564a8a8707f8)
    at /postgres/sources/15.2/src/backend/rewrite/rewriteHandler.c:1815
#9  0x0000564a891d3eea in fireRIRrules (parsetree=0x564a8a61c098, activeRIRs=0x564a8a8707f8) at /postgres/sources/15.2/src/backend/rewrite/rewriteHandler.c:2120
#10 0x0000564a891da9dd in QueryRewrite (parsetree=0x564a8a61c098) at /postgres/sources/15.2/src/backend/rewrite/rewriteHandler.c:4182
#11 0x0000564a8928a368 in pg_rewrite_query (query=0x564a8a61c098) at /postgres/sources/15.2/src/backend/tcop/postgres.c:792
#12 0x0000564a89289f1c in pg_analyze_and_rewrite_fixedparams (parsetree=0x564a8a61bef0, query_string=0x564a8a61b180 "SELECT * FROM cagg_join_bug;", paramTypes=0x0, numParams=0, 
    queryEnv=0x0) at /postgres/sources/15.2/src/backend/tcop/postgres.c:666
#13 0x0000564a8928ae9b in exec_simple_query (query_string=0x564a8a61b180 "SELECT * FROM cagg_join_bug;") at /postgres/sources/15.2/src/backend/tcop/postgres.c:1166
#14 0x0000564a89294c1d in PostgresMain (dbname=0x564a8a647bc0 "postgres", username=0x564a8a647b98 "postgres")
    at /postgres/sources/15.2/src/backend/tcop/postgres.c:4593
#15 0x0000564a8912dd9b in BackendRun (port=0x564a8a63d480) at /postgres/sources/15.2/src/backend/postmaster/postmaster.c:4511
#16 0x0000564a8912d0eb in BackendStartup (port=0x564a8a63d480) at /postgres/sources/15.2/src/backend/postmaster/postmaster.c:4239
#17 0x0000564a891251c5 in ServerLoop () at /postgres/sources/15.2/src/backend/postmaster/postmaster.c:1806
#18 0x0000564a89124374 in PostmasterMain (argc=8, argv=0x564a8a5d4a40) at /postgres/sources/15.2/src/backend/postmaster/postmaster.c:1478
#19 0x0000564a88f6f701 in main (argc=8, argv=0x564a8a5d4a40) at /postgres/sources/15.2/src/backend/main/main.c:202

[lkshminarayanan]

Hi @mwahlhuetter!

Our team is looking into this issue now. Meanwhile as a workaround, you can rewrite the query using FROM and WHERE instead of JOIN to get the expected result. i.e., the following query should give you the expected result :

CREATE MATERIALIZED VIEW cagg_join_bug
WITH (timescaledb.continuous) AS
SELECT
	time_bucket(interval '1 day', h.time) AS "time",
	n.name,
	SUM(h.amount)
FROM hypertable_bug h, normal_table_bug n WHERE n.normal_id = h.normal_id
GROUP BY 1, n.name;

[JamesGuthrie]

@lkshminarayanan the suggested workaround only works when there are no other where clauses than those on the join.

e.g. the following query cannot be rewritten in the way that you suggested (purely hypothetical query):

SELECT
  time_bucket('1 minute', m.time) as bucket
, avg(value)
, max(s.id)
FROM metric m
JOIN series s ON s.id = m.series_id
WHERE s.id = 1
GROUP BY bucket
ORDER BY bucket ASC

rewriting it as an implicit cross join gives the following error:

ERROR:  invalid continuous aggregate view
DETAIL:  unsupported expression in join clause
HINT:  only equality condition is supported