Issue with a query while running on a remote host

Поиск
Список
Период
Сортировка
От Ninad Shah
Тема Issue with a query while running on a remote host
Дата
Msg-id CAOFEiBcvCgeX+VOfqQy6ETvwF6a94W3b1KU1m9poRvRnaPvVoQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: Issue with a query while running on a remote host  (Karsten Hilbert <Karsten.Hilbert@gmx.net>)
Список pgsql-general
Дерево обсуждения
Hello All,

While working with a PostgreSQL database, I came across an issue where data is not being fetched over the network.

Version : PostgreSQL 11.10
Operating system : RHEL 8.4

Issue description:

We tried to execute the below query on the database host using psql prompt, it works without any issue.

select regexp_replace(state_cd, E'[\\n\\r]+', '', 'g' ) as  state_cd, off_cd, user_cd, regexp_replace(user_name, E'[\\n\\r]+', '', 'g' ) as  user_name, regexp_replace(desig_cd, E'[\\n\\r]+', '', 'g' ) as desig_cd, regexp_replace(user_id, E'[\\n\\r]+', '', 'g' ) as user_id, regexp_replace(user_pwd, E'[\\n\\r]+', '', 'g' ) as user_pwd, regexp_replace(phone_off, E'[\\n\\r]+', '', 'g' ) as phone_off, mobile_no, regexp_replace(email_id, E'[\\n\\r]+', '', 'g' ) as email_id, regexp_replace(user_catg, E'[\\n\\r]+', '', 'g' ) as user_catg, regexp_replace(status, E'[\\n\\r]+', '', 'g' ) as status, created_by, created_dt, aadhaar, op_dt, regexp_replace(login_ipaddress, E'[\\n\\r]+', '', 'g' ) as login_ipaddress, regexp_replace(forget_password, E'[\\n\\r]+', '', 'g' ) as forget_password, regexp_replace(newuser_change_password, E'[\\n\\r]+', '', 'g' ) as newuser_change_password from  tm_user_info where  ( user_cd >= 1068540014 ) AND ( user_cd <= 2137079303 ) ;

While trying to execute the same query over the network using psql prompt, the execution doesn't finish.

My Analysis:

By digging further, we came to see that a specific record was causing the issue, and by further analysis, we saw that the records that contain a specific string("bash@") in the column user_id are not being fetched over the network.

To confirm that, we also changed some records manually by creating a test table. And, we were able to reproduce the issue.

vow4_xdb=# select * from vahan4.test_tbl where user_cd =2106011301;
.
.

But, this issue doesn't occur if we try to fetch on the database host or via PgAdmin4. In such cases, we get the record in a few milliseconds.

Surprisingly, this table has only one record.

There is no table/row-level lock found here. 


Table definition:-
                                                  Table "test_tbl"
         Column          |            Type             | Collation | Nullable | Default | Storage  | Stats targe
t | Description 
-------------------------+-----------------------------+-----------+----------+---------+----------+------------
--+-------------
 state_cd                | character varying(2)        |           | not null |         | extended |            
  | 
 off_cd                  | numeric(5,0)                |           | not null |         | main     |            
  | 
 user_cd                 | numeric(10,0)               |           | not null |         | main     |            
  | 
 user_name               | character varying(99)       |           | not null |         | extended |            
  | 
 desig_cd                | character varying(10)       |           | not null |         | extended |            
  | 
 user_id                 | character varying(20)       |           | not null |         | extended |            
  | 
 user_pwd                | character varying(100)      |           | not null |         | extended |            
  | 
 phone_off               | character varying(20)       |           |          |         | extended |            
  | 
 mobile_no               | numeric(10,0)               |           | not null |         | main     |            
  | 
 email_id                | character varying(50)       |           |          |         | extended |            
  | 
 user_catg               | character varying(1)        |           | not null |         | extended |            
  | 
 status                  | character varying(1)        |           | not null |         | extended |            
  | 
 created_by              | numeric(10,0)               |           | not null |         | main     |            
  | 
 created_dt              | date                        |           | not null |         | plain    |            
  | 
 aadhaar                 | numeric(12,0)               |           |          |         | main     |            
  | 
 op_dt                   | timestamp without time zone |           | not null | now()   | plain    |            
  | 
 login_ipaddress         | character varying(20)       |           |          |         | extended |            
  | 
 forget_password         | character varying(1)        |           |          |         | extended |            
  | 
 newuser_change_password | character varying(1)        |           |          |         | extended |            
  | 
Indexes:
    "tm_user_info_pkey" PRIMARY KEY, btree (user_cd)
    "idx_tm_user_info_user_id" UNIQUE, btree (user_id)
Replica Identity: FULL


Record with an issue:-
state_cd|off_cd|user_cd|user_name|desig_cd|user_id|user_pwd|phone_off|mobile_no|email_id|user_catg|status|created_by|created_dt|aadhaar|op_dt|login_ipaddress|forget_password|newuser_change_password
HR|31|2106011301|SUBASH AUTO HOUSE|DS|subash@123|c22d1779437117852ffcdeb7e9c689f41c502a1dae2687b8845a33bb892f3b66||9255561872|skpanwar2311@gmail.com|B|D|2006078673|2021-01-04|0|2021-01-04 14:30:27.715728||N|F
(1 row)


Can anyone help me out here?


Regards,
Ninad Shah

В списке pgsql-general по дате отправления:

Предыдущее
От: Ninad Shah
Дата:
Сообщение: Re: Manual failover cluster
Следующее
От: Karsten Hilbert
Дата:
Сообщение: Re: Issue with a query while running on a remote host