pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How 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