Participant Query Store: return type mismatch error when starting scribe

Hi, I’m attempting to set up PQS locally, using Docker hosted Canton and Postgres. I’m following the PQS instructions at Participant Query Store User Guide — Daml SDK 2.7.1 documentation. When I start scribe with the following command:

./scribe.jar pipeline ledger postgres-document \
--pipeline-party=alice \
--pipeline-datasource=TransactionTreeStream \
--source-ledger-host=localhost \
--source-ledger-port=6865 \
--target-postgres-host=localhost \
--target-postgres-port=5432 \
--target-postgres-database=pqs \
--target-postgres-username=canton \

I get the following:

`ATTN! OpenTelemetry Java Agent is not found.
Please provide opentelemetry Java Agent using environment variable JAVA_TOOL_OPTIONS=-javaagent:/path/to/otel.jar to process observability signals.`

This seems to be a warning as the application continues, until I get the following error:

09:27:21.560 I [zio-fiber-90] com.digitalasset.scribe.postgres.document.DocumentPostgres.Service:36 Applying schema  
09:27:21.766 E [zio-fiber-0]  Exception in thread "zio-fiber-" org.postgresql.util.PSQLException: ERROR: return type mismatch in function declared to return record
  Detail: Function's final statement must be SELECT or INSERT/UPDATE/DELETE RETURNING.
  Where: SQL function "payloads"

I appreciate this is in Early Access, but would appreciate any pointers in getting beyond this point, thanks!

UPDATE: This works if I use the sandbox ledger locally with the create-daml-app sample application. Not sure why it should fail with the above message with Canton in Docker.

Hi @Ianw1 , considering you have a failing and a passing example let’s explore this a bit further. Are there any Postgres version differences between them?

Also, could you please paste/attach the output of

./scribe.jar datastore postgres-document schema show <connectivity-args>

for both cases?

No, I have tried Postgres 11, 14.6 and 15. The behaviour is the same in all cases, ie works with create-daml-app, not with the Canton ledger in Docker.

Fixed it. TLDR is that deploying a Daml model to the ledger fixes this error. I deployed create-daml-app-0.1.0.dar to the Canton instance in Docker and scribe now works no problem.

The ./scribe.jar datastore postgres-document schema show <connectivity-args> put me onto this so thanks for this guidance @vladgangan. At the very end of the working one there are 5 insert into _mappings lines that seem to correspond to the templates and choices in the daml model of create-daml-app. Further up the working one there are selects from tables such as Alias.hbbj5n in definitions of functions payloads and choices. There are no selects in the failing function definitions, hence the error message.

1 Like

Glad it’s sorted out now. Not sure if the docs mention it explicitly but Scribe does require an already deployed Daml model to function correctly at the moment - the reason for this (as you have experienced it) is that it generates necessary Postgres schema constructs (tables, some functions’ contents) based on the metadata of the Daml package(s) on the ledger. By consequence, if a new Daml model package gets deployed, Scribe won’t see it until restart.
Our team will address the no-Daml-model-on-ledger with a friendly message in a future release as pre-requisite startup validation.