Description
Environment details:
Botpress 12.30.6
Storage: PostgreSQL
I just added Increment Custom Metric to my environment and upon executing the action the system throws an error:
Sun Dec 11 2022 11:14:49 GMT+0000 (Coordinated Universal Time) ActionService (error) (bot: pruebas) An error occurred while executing the action "increment-custom-metric [error, SELECT * FROM bot_analytics WHERE
botId='pruebas' AND
date = '2022-12-11' AND
channel = 'api' AND
metric = 'found' AND
subMetric = 'n/a' - column "botid" does not exist]
STACK TRACE
error: column "botid" does not exist
at Parser.parseErrorMessage (/snapshot/botpress/node_modules/pg-protocol/dist/parser.js:287:98)
at Parser.handlePacket (/snapshot/botpress/node_modules/pg-protocol/dist/parser.js:126:29)
at Parser.parse (/snapshot/botpress/node_modules/pg-protocol/dist/parser.js:39:38)
at Socket.<anonymous> (/snapshot/botpress/node_modules/pg-protocol/dist/index.js:11:42)
at Socket.emit (events.js:315:20)
at Socket.EventEmitter.emit (domain.js:482:12)
at addChunk (_stream_readable.js:295:12)
at readableAddChunk (_stream_readable.js:271:9)
at Socket.Readable.push (_stream_readable.js:212:10)
at TCP.onStreamRead (internal/stream_base_commons.js:186:23)
This has to do with using PostgreSQL as storage since every column name without quotes is interpreted as lowercase:
Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other.
https://www.postgresql.org/docs/current/sql-syntax-lexical.html
Also, after fixing it a new error comes up:
Sun Dec 11 2022 11:17:28 GMT+0000 (Coordinated Universal Time) ActionService (error) (bot: pruebas) An error occurred while executing the action "increment-custom-metric [error, insert into "bot_analytics" ("botId", "channel", "date", "metric", "subMetric", "value") values ($1, $2, $3, $4, $5, $6) returning "id" - column "id" does not exist]
STACK TRACE
error: column "id" does not exist
at Parser.parseErrorMessage (/snapshot/botpress/node_modules/pg-protocol/dist/parser.js:287:98)
at Parser.handlePacket (/snapshot/botpress/node_modules/pg-protocol/dist/parser.js:126:29)
at Parser.parse (/snapshot/botpress/node_modules/pg-protocol/dist/parser.js:39:38)
at Socket.<anonymous> (/snapshot/botpress/node_modules/pg-protocol/dist/index.js:11:42)
at Socket.emit (events.js:315:20)
at Socket.EventEmitter.emit (domain.js:482:12)
at addChunk (_stream_readable.js:295:12)
at readableAddChunk (_stream_readable.js:271:9)
at Socket.Readable.push (_stream_readable.js:212:10)
at TCP.onStreamRead (internal/stream_base_commons.js:186:23)
The code omits the returnColumn or idColumn at insertAndRetrieve, so id is used as default - but there's no id column for this table.