Re: How to remove user specific grant and revoke
Hi! >From your first message I was under the impression that pgAdmin shows one REVOKE ALL for every GRANT, i.e. all REVOKE commands at once. If that is not the case you may have found a bug in pgAdmin. Please ask on the pgadmin-support list or open a GitHub issue. Speaking of which, I foundhttps://github.com/pgadmin-org/pgadmin4/issues/5926 which looks like the behavior you're describing. But this was already fixed in 7.2 and your original post says that you're using 7.2. Please check if your version is correct. In pgAdmin 7.2 right clicking in table name and selecting Refresh does nothing. Smells like a bug. Right clicking in Tables and selecting Refresh worked. Andrus.
Re: Connection error to new pg15 instance
It turns out RDS by default forces ssl connections for pg15 instances. I
have turned this off for now while I work out how that impacts my code and
I can now connect to the pg15 instance.
Thanks,
Steve
On Sun, Jun 4, 2023 at 3:10 PM Steve Baldwin
wrote:
> I suspect it may have something to do with ssl. The FATAL error in the log
> said "no encryption". I'm not sure what that means. When I look at a
> connection to one of our pg14 instances from the same API server, I see
> this in the logs:
>
> 2023-06-04 00:03:06.210 UTC,"b2bc_api","b2bcreditonline",16024,"
> 10.120.141.112:49228",647bd4ba.3e98,2,"authentication",2023-06-04
> 00:03:06 UTC,25/2682741,0,LOG,0,"connection authenticated:
> identity=""b2bc_api"" method=md5
> (/rdsdbdata/config/pg_hba.conf:13)","","client backend",,0
>
> Here are the hba rules for that instance:
>
> b2bcreditonline=> select * from pg_hba_file_rules;
> line_number | type | database | user_name | address | netmask
> | auth_method | options | error
>
> -+---+---++--+-+---+-+---
>4 | local | {all} | {all} | |
> | scram-sha-256 | |
> 10 | host | {all} | {rdsadmin} | samehost |
> | scram-sha-256 | |
> 11 | host | {all} | {rdsadmin} | all |
> | reject| |
> 12 | host | {rdsadmin}| {all} | all |
> | reject| |
> 13 | host | {all} | {all} | all |
> | md5 | |
> 14 | host | {replication} | {all} | samehost |
> | scram-sha-256 | |
> 17 | host | {rds_replication} | {all} | all |
> | md5 | |
> (7 rows)
>
> So line 13 is type 'host'.
>
> When I look at the hba rules for the pg15 instance, there is no
> corresponding entry:
>
> b2bcreditonline=> select * from pg_hba_file_rules;
> line_number | type | database | user_name | address |
> netmask | auth_method | options | error
>
> -+-+---++--+-+---+---+---
>2 | local | {all} | {rdsadmin} | |
> | peer | {map=rds} |
>6 | local | {all} | {all} | |
> | scram-sha-256 | |
> 12 | host| {all} | {rdsadmin} | samehost |
> | scram-sha-256 | |
> 13 | host| {all} | {rdsadmin} | all |
> | reject| |
> 14 | host| {rdsadmin}| {all} | all |
> | reject| |
> 15 | hostssl | {all} | {all} | all |
> | md5 | |
> 16 | host| {replication} | {all} | samehost |
> | scram-sha-256 | |
> 21 | hostssl | {rds_replication} | {all} | all |
> | md5 | |
> (8 rows)
>
> The entry that was used when I made a psql connection was line 15 which
> has a type of 'hostssl'.
>
> I'm not sure what this means in terms of what I need to change. Maybe I
> need to raise this with AWS support?
>
> Cheers,
>
> Steve
>
> On Sun, Jun 4, 2023 at 11:11 AM Steve Baldwin
> wrote:
>
>> Hi all,
>>
>> I'm in the process of migrating from an RDS pg14 instance to pg15.3. As
>> part of the migration process, the application code makes a test connection
>> to the new instance. This failed. I tried manually connecting to the kube
>> pod where the test query was submitted from, and from there was able to
>> manually connect to the new instance (using psql) just fine.
>>
>> Here are the (hopefully) relevant chunks from the database log:
>>
>> :
>> 2023-06-04 00:29:11.890
>> UTC,,,2764,"10.120.80.80:46914",647bdad7.acc,1,"",2023-06-04
>> 00:29:11 UTC,,0,LOG,0,"connection received: host=10.120.80.80
>> port=46914","","not initialized",,0
>> 2023-06-04 00:29:11.891 UTC,"b2bc_owner","b2bcreditonline",2764,"
>> 10.120.80.80:46914",647bdad7.acc,2,"authentication",2023-06-04 00:29:11
>> UTC,7/1009,0,FATAL,28000,"no pg_hba.conf entry for host ""10.120.80.80"",
>> user ""b2bc_owner"", database ""b2bcreditonline"", no
>> encryption","","client backend",,0
>> :
>> 2023-06-04 00:43:56.114
>> UTC,,,4046,"10.120.80.80:56356",647bde4c.fce,1,"",2023-06-04
>> 00:43:56 UTC,,0,LOG,0,"connection received: host=10.120.80.80
>> port=56356","","not initialized",,0
>> 2023-06-04 00:43:56.127 UTC,"b2bc_owner","b2bcreditonline",4046,"
>> 10.120.80.80:56356",647bde4c.fce,2,"authentication",2023-06-04 00:43:56
>> UTC,7/1626,0,LOG,0,"connection authenticated: identity=""b2bc_owner""
>> method=md5 (/rdsdbdata/config/pg_hba.conf:15)","","client
>> backend",,0
>> 2023-06-04 00:43:56.127 UTC,"b2bc_owner","b2bcreditonline",4046,"
>> 10.120.80.8
"pgsql-zh-general" can't send chinese mail.
Hi team, I want to use the list "pgsql-zh-general" to communicate with chinese user. But it can't send chinese mail, because he recognizes all Chinese emails as spam. Can someone provide some solution? Thanks in advance! Yours, Wen Yi
Re: "pgsql-zh-general" can't send chinese mail.
Hi, Am 04.06.23 um 11:15 schrieb Wen Yi: Hi team, I want to use the list "pgsql-zh-general" to communicate with chinese user. But it can't send chinese mail, because he recognizes all Chinese emails as spam. before this message your mail was listed at https://www.postgresql.org/list/pgsql-zh-general/since/202306040910 Can someone provide some solution? Thanks in advance! Yours, Wen Yi
Re: "pgsql-zh-general" can't send chinese mail.
Sorry, it's all my fault, I use error mail-box settings. Yours, Wen Yi -- Original -- From: "postgresql439848" https://www.postgresql.org/list/pgsql-zh-general/since/202306040910 > Can someone provide some solution? > Thanks in advance! > > > Yours, > Wen Yi
[Question]What will happen if the server active close the connection?
Hi team, when I study the tcp connection, I found that if the server active close the connection, the server will send a fin package to the client and the client will reply a ack package. As this: Server >(FIN) Client Server <(ACK) Client My question is, in so many articles, the following step is, the client will write some data(send some data) to the server and then cause the signal SIGNALPIPE. But what will happen if the client died?(Example: was killed by the user) Can someone provide some advice? Thanks in advance! Yours, Wen Yi
Re: Pg 16: will pg_dump & pg_restore be faster?
On Sat, 3 Jun 2023 at 00:14, Jonathan S. Katz wrote: > Typically once a release announcement is out, we'll only edit it if it's > inaccurate. I don't think the statement in the release announcement is > inaccurate, as it specifies that concurrent bulk loading is faster. Understood. I had thought that the policy might be that if there's room for and reason enough to make improvements, then we probably should. We do aim to still make improvements to fix any problem with the software that's the topic of the announcement, maybe it's strange that we want to lock down what we write about that software just before the beta1 release. > I'm -0.5 for revising the announcement, but I also don't want people to > miss out on testing this. I'd be OK with this: > > "PostgreSQL 16 can also improve the performance of bulk loading of data, > with some tests showing using up to 300% improvement when concurrently > executing `COPY` commands." I might have just misunderstood the release notes based on my misunderstanding of Andres's work that it only improved things when multiple backends were extending the relation at the same time. The release announcement did seem to confirm that there had to be concurrency, so it might be good to not lead anyone else down into thinking that only concurrent cases are faster. I certainly understand that's where the big wins are. I'm fine with your proposed wording. David
Is there any good optimization solution to improve the query efficiency?
Hi everyone,
I'm running into some performance issues with my SQL query.
The following SQL query is taking a long time to execute.
Execution Plan：
explain analyse
select * from TBL_RES
left outer join(select T_CUST.RSNO RSNO2 ,
T_CUST.KNO ,
T_CUST.AGE ,
T_CUST.GST
from TBL_CUST T_CUST ,
(select T_CUST.RSNO ,
T_CUST.KNO ,
MIN(T_CUST.GSTSEQ) GSTSEQ
from TBL_CUST T_CUST ,
TBL_POV T_POV ,
TBL_RES T_RES
where T_CUST.STSFLG = 'T'
and T_CUST.DISPSEQ <>
AND T_CUST.KFIX = '0'
and T_POV.CRSNO = T_RES.CRSNO
and T_RES.RSNO = T_CUST.RSNO
group by T_CUST.RSNO , T_CUST.KNO) T_POV2
where T_POV2.RSNO = T_CUST.RSNO
and T_POV2.KNO = T_CUST.KNO
and T_POV2.GSTSEQ = T_CUST.GSTSEQ) T_POV3 on TBL_RES.RSNO = T_POV3.RSNO2
and TBL_RES.KNO = T_POV3.KNO
where TBL_RES.CID >= to_date('2022/07/01', '/MM/DD')
and TBL_RES.CID <= to_date('2022/07/31', '/MM/DD')
and TBL_RES.COD >= to_date('2022/07/01', '/MM/DD')
and TBL_RES.COD <= to_date('2022/07/31', '/MM/DD')
- Execution Plan -
Nested Loop Left Join (cost=254388.44..452544.70 rows=473 width=3545) (actual
time=3077.312..996048.714 rows=15123 loops=1)
Join Filter: ((TBL_RES.RSNO = T_CUST.RSNO) AND ((TBL_RES.KNO)::text =
(T_CUST.KNO)::text))
Rows Removed by Join Filter: 4992268642
-> Gather (cost=1000.00..58424.35 rows=473 width=3489) (actual
time=0.684..14.158 rows=15123 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on TBL_RES (cost=0.00..57377.05 rows=197
width=3489) (actual time=0.096..279.504 rows=5041 loops=3)
Filter: ((CID >= to_date('2022/07/01'::text, '/MM/DD'::text))
AND (CID <= to_date('2022/07/31'::text, '/MM/DD'::text)) AND (COD >=
to_date('2022/07/01'::text, '/MM/DD'::text)) AND (COD <=
to_date('2022/07/31'::text, '/MM/DD'::text)))
Rows Removed by Filter: 161714
-> Materialize (cost=253388.44..394112.08 rows=1 width=56) (actual
time=0.081..26.426 rows=330111 loops=15123)
-> Hash Join (cost=253388.44..394112.07 rows=1 width=56) (actual
time=1197.484..2954.084 rows=330111 loops=1)
Hash Cond: ((T_CUST.RSNO = T_CUST_1.RSNO) AND ((T_CUST.KNO)::text
= (T_CUST_1.KNO)::text) AND (T_CUST.gstseq = (min(T_CUST_1.gstseq
-> Seq Scan on TBL_CUST T_CUST (cost=0.00..79431.15
rows=2000315 width=61) (actual time=0.015..561.005 rows=2000752 loops=1)
-> Hash (cost=246230.90..246230.90 rows=262488 width=50)
(actual time=1197.025..1209.957 rows=330111 loops=1)
Buckets: 65536 Batches: 8 Memory Usage: 2773kB
-> Finalize GroupAggregate (cost=205244.84..243606.02
rows=262488 width=50) (actual time=788.552..1116.074 rows=330111 loops=1)
Group Key: T_CUST_1.RSNO, T_CUST_1.KNO
-> Gather Merge (cost=205244.84..238964.80
rows=268846 width=50) (actual time=788.547..982.479 rows=330111 loops=1)
Workers Planned: 2
Workers Launched: 1
-> Partial GroupAggregate
(cost=204244.81..206933.27 rows=134423 width=50) (actual time=784.032..900.979
rows=165056 loops=2)
Group Key: T_CUST_1.RSNO, T_CUST_1.KNO
-> Sort (cost=204244.81..204580.87
rows=134423 width=23) (actual time=784.019..833.791 rows=165061 loops=2)
Sort Key: T_CUST_1.RSNO,
T_CUST_1.KNO
Sort Method: external merge Disk:
5480kB
Worker 0: Sort Method: external
merge Disk: 5520kB
-> Parallel Hash Join
(cost=111758.80..190036.38 rows=134423 width=23) (actual time=645.302..716.247
rows=165061 loops=2)
Hash Cond: (T_CUST_1.RSNO =
T_RES.RSNO)
-> Parallel Seq Scan on
TBL_CUST T_CUST_1 (cost=0.00..74013.63 rows=204760 width=23) (actual
time=0.018..264.390 rows=165058 loops=2)
Filter: ((dispseq <>
''::numeric) AND ((stsflg)::text = 'T'::text) AND ((KFIX)::text =
'0'::text))
Rows Removed by Filter:
835318
-> Parallel Hash
(cost=109508.52..109508.52 rows=137142 width=8) (actual time=343.593..343.896
rows=165058 loops=2)
Buckets: 131072
Batches: 8 Memory Usage: 3008kB
-> Parallel Hash Join
(cost=51834.70..109508.52 rows=137142 width=8) (actual time=256.732..314.368
rows=165058 loops=2)
Hash Cond:
((T_RES.crsno)::text = (T_POV.crsno)::text)
