STABLE functions used in UPDATE queries not compatible with PSQL Citus version

[vinnielima]

Certain periodic activities run by MC will run into this error if back end PSQL is of Citus release (cluster version of PSQL). This is a known issue with Citus, would be interested to find alternate work arounds to allow for one of the only pathways for highly scalable MC deployments.

[2022-10-16 01:34:06.5450] [E] [StatsRecorder] Npgsql.PostgresException (0x80004005): 0A000: STABLE functions used in UPDATE queries cannot be called with column references at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|211_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage) at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken) at Dapper.SqlMapper.ExecuteImplAsync(IDbConnection cnn, CommandDefinition command, Object param) in //Dapper/SqlMapper.Async.cs:line 654 at Miningcore.Mining.StatsRecorder.<>c__DisplayClass20_0.<<StatsGcAsync>b__1>d.MoveNext() in /tmp/miningcore/src/Miningcore/Mining/StatsRecorder.cs:line 304 --- End of stack trace from previous location --- at Miningcore.Extensions.ConnectionFactoryExtensions.Run(IConnectionFactory factory, Func2 action) in /tmp/miningcore/src/Miningcore/Extensions/ConnectionFactoryExtensions.cs:line 19 at Miningcore.Mining.StatsRecorder.StatsGcAsync(CancellationToken ct) in /tmp/miningcore/src/Miningcore/Mining/StatsRecorder.cs:line 307 at Miningcore.Mining.StatsRecorder.GcAsync(CancellationToken ct) in /tmp/miningcore/src/Miningcore/Mining/StatsRecorder.cs:line 340 Exception data: Severity: ERROR SqlState: 0A000 MessageText: STABLE functions used in UPDATE queries cannot be called with column references File: multi_router_planner.c Line: 739 Routine: ModifyPartialQuerySupported Npgsql.PostgresException (0x80004005): 0A000: STABLE functions used in UPDATE queries cannot be called with column references at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|211_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage) at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken) at Dapper.SqlMapper.ExecuteImplAsync(IDbConnection cnn, CommandDefinition command, Object param) in /_/Dapper/SqlMapper.Async.cs:line 654 at Miningcore.Mining.StatsRecorder.<>c__DisplayClass20_0.<<StatsGcAsync>b__1>d.MoveNext() in /tmp/miningcore/src/Miningcore/Mining/StatsRecorder.cs:line 304 --- End of stack trace from previous location --- at Miningcore.Extensions.ConnectionFactoryExtensions.Run(IConnectionFactory factory, Func2 action) in /tmp/miningcore/src/Miningcore/Extensions/ConnectionFactoryExtensions.cs:line 19 at Miningcore.Mining.StatsRecorder.StatsGcAsync(CancellationToken ct) in /tmp/miningcore/src/Miningcore/Mining/StatsRecorder.cs:line 307 at Miningcore.Mining.StatsRecorder.GcAsync(CancellationToken ct) in /tmp/miningcore/src/Miningcore/Mining/StatsRecorder.cs:line 340 Exception data: Severity: ERROR SqlState: 0A000 MessageText: STABLE functions used in UPDATE queries cannot be called with column references File: multi_router_planner.c Line: 739 Routine: ModifyPartialQuerySupported at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|211_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage) at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken) at Dapper.SqlMapper.ExecuteImplAsync(IDbConnection cnn, CommandDefinition command, Object param) in //Dapper/SqlMapper.Async.cs:line 654 at Miningcore.Mining.StatsRecorder.<>c__DisplayClass20_0.<<StatsGcAsync>b__1>d.MoveNext() in /tmp/miningcore/src/Miningcore/Mining/StatsRecorder.cs:line 304 --- End of stack trace from previous location --- at Miningcore.Extensions.ConnectionFactoryExtensions.Run(IConnectionFactory factory, Func`2 action) in /tmp/miningcore/src/Miningcore/Extensions/ConnectionFactoryExtensions.cs:line 19 at Miningcore.Mining.StatsRecorder.StatsGcAsync(CancellationToken ct) in /tmp/miningcore/src/Miningcore/Mining/StatsRecorder.cs:line 307 at Miningcore.Mining.StatsRecorder.GcAsync(CancellationToken ct) in /tmp/miningcore/src/Miningcore/Mining/StatsRecorder.cs:line 340

The probable cause of this error is the use of timestamp versus timestampz column types. Per Citus known error documentation:

STABLE functions used in UPDATE queries cannot be called with column references Each PostgreSQL function is marked with a volatility, which indicates whether the function can update the database, and whether the function’s return value can vary over time given the same inputs. A STABLE function is guaranteed to return the same results given the same arguments for all rows within a single statement, while an IMMUTABLE function is guaranteed to return the same results given the same arguments forever.

Non-immutable functions can be inconvenient in distributed systems because they can introduce subtle changes when run at slightly different times across shard replicas. Differences in database configuration across nodes can also interact harmfully with non-immutable functions.

One of the most common ways this can happen is using the timestamp type in Postgres, which unlike timestamptz does not keep a record of time zone. Interpreting a timestamp column makes reference to the database timezone, which can be changed between queries, hence functions operating on timestamps are not immutable.

Citus forbids running distributed queries that filter results using stable functions on columns. For instance:

-- foo_timestamp is timestamp, not timestamptz UPDATE foo SET ... WHERE foo_timestamp < now(); ERROR: STABLE functions used in UPDATE queries cannot be called with column references In this case the comparison operator < between timestamp and timestamptz is not immutable.

Resolution Avoid stable functions on columns in a distributed UPDATE statement. In particular, whenever working with times use timestamptz rather than timestamp. Having a time zone in timestamptz makes calculations immutable. `

Avoid stable functions on columns in a distributed UPDATE statement. In particular, whenever working with times use timestamptz rather than timestamp. Having a time zone in timestamptz makes calculations immutable.Avoid stable functions on columns in a distributed UPDATE statement. In particular, whenever working with times use timestamptz rather than timestamp. Having a time zone in timestamptz makes calculations immutable. Resolution Resolution

In this case the comparison operator < between timestamp and timestamptz is not immutable. In this case the comparison operator < between timestamp and timestamptz is not immutable. ERROR: STABLE functions used in UPDATE queries cannot be called with column references ERROR: STABLE functions used in UPDATE queries cannot be called with column references UPDATE foo SET ... WHERE foo_timestamp < now(); UPDATE foo SET ... WHERE foo_timestamp < now(); -- foo_timestamp is timestamp, not timestamptz -- foo_timestamp is timestamp, not timestamptz

Citus forbids running distributed queries that filter results using stable functions on columns. For instance: Citus forbids running distributed queries that filter results using stable functions on columns. For instance:

One of the most common ways this can happen is using the timestamp type in Postgres, which unlike timestamptz does not keep a record of time zone. Interpreting a timestamp column makes reference to the database timezone, which can be changed between queries, hence functions operating on timestamps are not immutable. One of the most common ways this can happen is using the timestamp type in Postgres, which unlike timestamptz does not keep a record of time zone. Interpreting a timestamp column makes reference to the database timezone, which can be changed between queries, hence functions operating on timestamps are not immutable.

Non-immutable functions can be inconvenient in distributed systems because they can introduce subtle changes when run at slightly different times across shard replicas. Differences in database configuration across nodes can also interact harmfully with non-immutable functions. Non-immutable functions can be inconvenient in distributed systems because they can introduce subtle changes when run at slightly different times across shard replicas. Differences in database configuration across nodes can also interact harmfully with non-immutable functions.

Each PostgreSQL function is marked with a volatility, which indicates whether the function can update the database, and whether the function’s return value can vary over time given the same inputs. A STABLE function is guaranteed to return the same results given the same arguments for all rows within a single statement, while an IMMUTABLE function is guaranteed to return the same results given the same arguments forever. Each PostgreSQL function is marked with a volatility, which indicates whether the function can update the database, and whether the function’s return value can vary over time given the same inputs. A STABLE function is guaranteed to return the same results given the same arguments for all rows within a single statement, while an IMMUTABLE function is guaranteed to return the same results given the same arguments forever.