Querying PQS

Questions
1

Hello, I am using PQS to query for contracts/contract data, and I am running into an issue. I want to be able to use the package id to query all active contracts from that package id, or alternatively use the fully qualified name :<My.Module>:. So far ive been doing something like

select * from summary_active()
where template_fqn like '<My.Module>:<MyTemplate>'

and I have had no issues getting the number of contracts with that template name, however I was wondering how I could adjust the query see the data/pkgId from that contract. Additionally, if I include the package ID that I get from using ‘daml damlc inspect-dar’ the query then returns nothing
Also, as a side note the ‘select * from active()’ query does not work.

2

Hi, @austin. I am glad to hear you are becoming familiar with the PQS!

As you noticed the summary_active() function does not return the package id. The active() function, on the other hand, does include a package_id column.

When you say…

as a side note the ‘select * from active()’ query does not work.

… could you be more specific? Here is what I am seeing from within pgAdmin:

image
image761×589 47.5 KB

Here is a sample query using active() to filter and group by the package_id:

select package_id
	, template_fqn
	, payload_type
	, count(*)
from active()
where package_id = '86f1eb6ff7ddbad5223bd8e85dc66ca5760ecbfc26921d0cac2a2fb08224a04d'
group by package_id, template_fqn, payload_type

image
image761×530 61.4 KB

I am using Canton and SDK versions 2.9.5, Postgres v16, and PQS v. 0.5.4.

3

By the way, as an alternative to querying the PQS with SQL, you might try the Daml Shell too.

image
image553×433 30.3 KB

4

Hi Wally,
Maybe it’s something with how we have PQS set up then, when I run ‘select * from active()’ I get an internal server error. Is there a page that lists what active() returns somewhere? I wasn’t able to find one but at the same time, ‘select * from active()’ probably should work so it would make sense there isn’t a page for it. Let me ask around internally to see what I can find. As for the daml shell, we are still on version 2.9.4 unfortunately so I don’t think we have daml shell just yet. However, alternatively I can use psql right?

5

No. As you stated, it should be working for you. :thinking:

Regarding Daml Shell, it is a separate tool. (It is not part of the Daml SDK or Daml Assistant.) The Daml Shell docs show how to run it. However, it simply pulls data from the PQS database, so you will want to get that working first.

6

Hmm I wonder why it isn’t then. I asked someone internally, hopefully they get back to me soon.
For daml shell access PQS, it would need to have SSL available most likely right? I think that is an issue I am running into right now with trying to use psql from terminal.