Re: How to show current schema of running queries in postgresql 13

Hi

po 26. 6. 2023 v 8:39 odesílatel 陈锡汉 <cavonchen(at)163(dot)com> napsal:

> Hello,I use multi-schemas in one database in Postgres,such as
>
> ```
> Postgres(instance)
> MyDB
> public
> MySchema1
> table1
> table2
> MySchema2
> table1
> table2
> MySchema3
> table1
> table2
> ```
>
> And It's open to my users,my users will run queries,
> such as
> User1:
> ```
> set search_path=MySchema1;
> select * from table1,table2;
> ```
>
> User2:
> ```
> set search_path=MySchema2;
> select * from table1,table2;
> ```
>
> User3:
> ```
> set search_path=MySchema3;
> insert into table3 select * from MySchema1.table1,MySchema2.table2;
> select * from table3;
> ```
>
> I want to show current schema of running queries,But pg_stat_activity can
> only show database name, not schema name.
>
> I want current schema (search_path ) as
>
> | datname | username | schema | query |
> | -------- | -------- | -------- | -------- |
> | MyDB | User1 | MySchema1 | select * from table1,table2; |
> | MyDB | User2 | MySchema2 | select * from table1,table2; |
> | MyDB | User3 | MySchema3 | insert into table3 select * from
> MySchema1.table1,MySchema2.table2; |
>
> Is there any sys views can do it?
>

no, there is nothing for this purpose.

you can use application_name

so user can do

SET search_path=MySchema;
SET application_name = 'MySchema';
SELECT * FROM ...

>
> Thank you!
> Best regards,
> CavonChen
>
>
>
>