pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to show current schema of running queries in postgresql 13
5+ messages / 2 participants
[nested] [flat]
* How to show current schema of running queries in postgresql 13
@ 2023-06-26 06:39 陈锡汉 <cavonchen@163.com>
2023-06-26 07:19 ` Re: How to show current schema of running queries in postgresql 13 Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: 陈锡汉 @ 2023-06-26 06:39 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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?
Thank you!
Best regards,
CavonChen
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to show current schema of running queries in postgresql 13
2023-06-26 06:39 How to show current schema of running queries in postgresql 13 陈锡汉 <cavonchen@163.com>
@ 2023-06-26 07:19 ` Pavel Stehule <pavel.stehule@gmail.com>
2023-06-26 09:51 ` Re: How to show current schema of running queries in postgresql 13 Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Pavel Stehule @ 2023-06-26 07:19 UTC (permalink / raw)
To: 陈锡汉 <cavonchen@163.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Hi
po 26. 6. 2023 v 8:39 odesílatel 陈锡汉 <cavonchen@163.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
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to show current schema of running queries in postgresql 13
2023-06-26 06:39 How to show current schema of running queries in postgresql 13 陈锡汉 <cavonchen@163.com>
2023-06-26 07:19 ` Re: How to show current schema of running queries in postgresql 13 Pavel Stehule <pavel.stehule@gmail.com>
@ 2023-06-26 09:51 ` Pavel Stehule <pavel.stehule@gmail.com>
2023-06-26 10:22 ` 回复:How to show current schema of running queries in postgresql 13 陈锡汉 <cavonchen@163.com>
0 siblings, 1 reply; 5+ messages in thread
From: Pavel Stehule @ 2023-06-26 09:51 UTC (permalink / raw)
To: 陈锡汉 <cavonchen@163.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
po 26. 6. 2023 v 9:19 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:
> Hi
>
>
>
> 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 ...
>
>
and application name is visible from pg_stat_activity
regards
Pavel Stehule
>
>
>
>
>>
>> Thank you!
>> Best regards,
>> CavonChen
>>
>>
>>
>>
^ permalink raw reply [nested|flat] 5+ messages in thread
* 回复:How to show current schema of running queries in postgresql 13
2023-06-26 06:39 How to show current schema of running queries in postgresql 13 陈锡汉 <cavonchen@163.com>
2023-06-26 07:19 ` Re: How to show current schema of running queries in postgresql 13 Pavel Stehule <pavel.stehule@gmail.com>
2023-06-26 09:51 ` Re: How to show current schema of running queries in postgresql 13 Pavel Stehule <pavel.stehule@gmail.com>
@ 2023-06-26 10:22 ` 陈锡汉 <cavonchen@163.com>
2023-06-26 10:31 ` Re: How to show current schema of running queries in postgresql 13 Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: 陈锡汉 @ 2023-06-26 10:22 UTC (permalink / raw)
To: pavel.stehule <pavel.stehule@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
I have no way to force users doing this…
---- 回复的原邮件 ----
| 发件人 | Pavel Stehule<pavel.stehule@gmail.com> |
| 日期 | 2023年06月26日 17:51 |
| 收件人 | 陈锡汉<cavonchen@163.com> |
| 抄送至 | pgsql-general@lists.postgresql.org |
| 主题 | Re: How to show current schema of running queries in postgresql 13 |
po 26. 6. 2023 v 9:19 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
Hi
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 ...
and application name is visible from pg_stat_activity
regards
Pavel Stehule
Thank you!
Best regards,
CavonChen
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to show current schema of running queries in postgresql 13
2023-06-26 06:39 How to show current schema of running queries in postgresql 13 陈锡汉 <cavonchen@163.com>
2023-06-26 07:19 ` Re: How to show current schema of running queries in postgresql 13 Pavel Stehule <pavel.stehule@gmail.com>
2023-06-26 09:51 ` Re: How to show current schema of running queries in postgresql 13 Pavel Stehule <pavel.stehule@gmail.com>
2023-06-26 10:22 ` 回复:How to show current schema of running queries in postgresql 13 陈锡汉 <cavonchen@163.com>
@ 2023-06-26 10:31 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Pavel Stehule @ 2023-06-26 10:31 UTC (permalink / raw)
To: 陈锡汉 <cavonchen@163.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
po 26. 6. 2023 v 12:23 odesílatel 陈锡汉 <cavonchen@163.com> napsal:
> I have no way to force users doing this…
>
Then there is only one possible way - via custom extension you can catch
the SET SEARCH_PATH statement, and you can do this. If you cannot use own
extension, then there is not any way.
>
>
> ---- 回复的原邮件 ----
> 发件人 Pavel Stehule<pavel.stehule@gmail.com> <pavel.stehule@gmail.com>
> 日期 2023年06月26日 17:51
> 收件人 陈锡汉<cavonchen@163.com> <cavonchen@163.com>
> 抄送至 pgsql-general@lists.postgresql.org
> 主题 Re: How to show current schema of running queries in postgresql 13
>
>
> po 26. 6. 2023 v 9:19 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
> napsal:
>
>> Hi
>>
>>
>>
>> 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 ...
>>
>>
> and application name is visible from pg_stat_activity
>
> regards
>
> Pavel Stehule
>
>
>>
>>
>>
>>
>>>
>>> Thank you!
>>> Best regards,
>>> CavonChen
>>>
>>>
>>>
>>>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-06-26 10:31 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-06-26 06:39 How to show current schema of running queries in postgresql 13 陈锡汉 <cavonchen@163.com>
2023-06-26 07:19 ` Pavel Stehule <pavel.stehule@gmail.com>
2023-06-26 09:51 ` Pavel Stehule <pavel.stehule@gmail.com>
2023-06-26 10:22 ` 回复:How to show current schema of running queries in postgresql 13 陈锡汉 <cavonchen@163.com>
2023-06-26 10:31 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox