Re: ERROR: stack depth limit exceeded

2023-09-10 Thread gzh
Thank you all for taking the time to help me with my question and offer your advice. Your responses were greatly appreciated! At 2023-09-08 21:53:33, "Tom Lane" wrote: >gzh writes: >> In the Release Notes for PostgreSQL 12.14, we saw the followi

Re: ERROR: stack depth limit exceeded

2023-09-07 Thread gzh
f this change is correct, we would like to find out which SQL statements will report errors in the new version. Do you have any good methods for doing this? At 2023-09-07 21:29:56, "Tom Lane" wrote: >gzh writes: >> I upgraded the version of PostgreSQL from 12.13

ERROR: stack depth limit exceeded

2023-09-07 Thread gzh
Hi, I upgraded the version of PostgreSQL from 12.13 to 12.16. If multiple subqueries or a large number of UNION ALL were used, the previously unseen error message "ERROR: stack depth limit exceeded" will appear after upgrading the version. I understand that increasing the value of max

Re: How to improve the performance of my SQL query?

2023-07-28 Thread gzh
t tends to get worse with time as the dataset increases. I strongly agree with your viewpoint, but I currently don't have a solution in mind for the problem. At 2023-07-28 04:38:39, "Charly" wrote: Hi "gzh", Based on the info you provided I'm ass

Re: How to improve the performance of my SQL query?

2023-07-27 Thread gzh
ot; wrote: >On Wed, 26 Jul 2023 at 19:46, gzh wrote: >> QUERY PLAN (enable_seqscan=on) > >> Execution Time: 167183.133 ms > >> QUERY PLAN (enable_seqscan=off) > >> Execution Time: 22320.153 ms > >effective_cache_size and random_page_cost are the settings you

Re: How to improve the performance of my SQL query?

2023-07-26 Thread gzh
-> Bitmap Index Scan on index_search_04_mscd_cdate (cost=0.00..91632.06 rows=3402599 width=0) (actual time=249.718..249.718 rows=330 loops=1) Index Cond: (ms_cd = 'MLD009'::bpchar) Planning Time: 0.670 ms Execution Time: 22320.153 ms

Re: How to improve the performance of my SQL query?

2023-07-24 Thread gzh
t of evaluate >something like {1 in (1,1,1,1,1,1,)} Because the primary key of the 'tbl_inf' table only consists of 'ms_cd' and 'ry_cd' columns, the subquery will not return duplicate rows. At 2023-07-24 22:42:01, "jian he" wrote: >On M

Re: How to improve the performance of my SQL query?

2023-07-24 Thread gzh
691 I/O Timings: read=365862.464 Settings: effective_cache_size = '1886088kB', jit = 'off', search_path = '"$user", mdb' Planning: Buffers: shared hit=167 read=7 I/O Timings: read=2.735 Planning Time: 3.733 ms Execution Time

Re: How to improve the performance of my SQL query?

2023-07-24 Thread gzh
Time: 128717.731 ms At 2023-07-24 13:43:46, "Laurenz Albe" wrote: >On Fri, 2023-07-21 at 09:43 +0800, gzh wrote: >> The definitions of the columns used in SQL are as follows. >> >> TBL_SHA >> >> ms_cd character(6) NOT NULL

Re:How to improve the performance of my SQL query?

2023-07-20 Thread gzh
>select (38700325 - 11833442) /38700325.0; >is 0.69 approx. >So I think it says around 69% of rows satisfy the query condition.Thank you >for your reply. I have learned a lot from it. At 2023-07-20 23:20:16, "jian he" wrote: >On Thu, Jul 20, 2023 at 7:36 PM g

Re: How to improve the performance of my SQL query?

2023-07-20 Thread gzh
th=18) (actual time=97264.138..123554.792 rows=320 loops=1) Filter: ((MS_CD = 'MLD009'::bpchar) AND (ETRYS = '0001'::bpchar)) Rows Removed by Filter: 32000325 Planning Time: 0.162 ms Execution Time: 124168.838 ms At 2

Re: How to improve the performance of my SQL query?

2023-07-20 Thread gzh
t;"TBL_SHA" >and "TBL_INF"? For information security reasons, I can't provide the table definition, these columns are defined as "character". At 2023-07-20 19:58:59, "Laurenz Albe" wrote: >On Thu, 2023-07-20 at 15:09 +0800, gzh wrote:

Re:Re: How to improve the performance of my SQL query?

2023-07-20 Thread gzh
rows=1 loops=1) Planning Time: 0.228 ms Execution Time: 0.070 ms At 2023-07-20 16:07:15, "Erik Wienhold" wrote: >> On 20/07/2023 09:09 CEST gzh wrote: >> >> I'm running into some performance issues with my SQL query. >> The followi

How to improve the performance of my SQL query?

2023-07-20 Thread gzh
Hi everyone, I'm running into some performance issues with my SQL query. The following SQL query is taking a long time to execute. explain analyze select COUNT(ET_CD) from TBL_SHA WHERE TBL_SHA.MS_CD = '009' and TBL_SHA.ETRYS in (select TBL_INF.RY_CD from TBL_INF WHERE TB

Re: function to_char(unknown) is not unique at character 8

2023-07-06 Thread gzh
Thank you for the solution you provided to identify these useless usage of this function,I learned a lot from it. At 2023-07-06 22:42:38, "Pavel Stehule" wrote: čt 6. 7. 2023 v 16:28 odesílatel gzh napsal: Thank you for the solution, it works fine. > I ha

Re: function to_char(unknown) is not unique at character 8

2023-07-06 Thread gzh
bugs there are, because it works fine in older version(orafce 3.13). At 2023-07-06 19:54:19, "Pavel Stehule" wrote: čt 6. 7. 2023 v 13:38 odesílatel gzh napsal: Thank you very much for taking the time to reply to my question. There are a lot of TO_CHAR in my applicati

Re: function to_char(unknown) is not unique at character 8

2023-07-06 Thread gzh
19:36:49, "Erik Wienhold" wrote: >> On 06/07/2023 11:19 CEST gzh wrote: >> >> I upgraded the version of PostgreSQL from 12.6 to 12.13, >> when I execute the sql below , the to_char function caused the following >> error. >> >> ---

Re: function to_char(unknown) is not unique at character 8

2023-07-06 Thread gzh
wrote: Hi čt 6. 7. 2023 v 11:19 odesílatel gzh napsal: Hi, I upgraded the version of PostgreSQL from 12.6 to 12.13, when I execute the sql below , the to_char function caused the following error. ---SQL-- select TO_CHAR('100'); ERRO

function to_char(unknown) is not unique at character 8

2023-07-06 Thread gzh
Hi, I upgraded the version of PostgreSQL from 12.6 to 12.13, when I execute the sql below , the to_char function caused the following error. ---SQL-- select TO_CHAR('100'); ERROR: function to_char(unknown) is not unique at character 8 HINT: Could n

Re: Is there any good optimization solution to improve the query efficiency?

2023-06-06 Thread gzh
ord in T_POV? in this case could work better move this join in the first with (using distinct or group by to ensure there will be just a record for rsno and kno) where T_CUST.STSFLG = 'T' and T_CUST.DISPSEQ <> AND T_CUST.KFIX = '0' group by T_CUST.RSNO , T_CUST.KNO ), t_pov

Re: Is there any good optimization solution to improve the query efficiency?

2023-06-05 Thread gzh
'/MM/DD') but if tbl_res contain lessere record a good idea is start from this table and use in join with other Il giorno lun 5 giu 2023 alle ore 08:57 gzh ha scritto: Hi everyone, I'm running into some performance issues with my SQL query. The following SQL que

Re:Re: Is there any good optimization solution to improve the query efficiency?

2023-06-05 Thread gzh
d Rowley" wrote: >On Mon, 5 Jun 2023 at 18:56, gzh wrote: >> I'm running into some performance issues with my SQL query. >> The following SQL query is taking a long time to execute. > >> -> Hash Join (cost=253388.44..394112.07 rows=1 width=56) (ac

Is there any good optimization solution to improve the query efficiency?

2023-06-04 Thread gzh
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

Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist

2023-04-27 Thread gzh
schema in all functions takes the date type of pg_catalog.date first, so setting the search_path as below is also a solution. postgres=# show search_path; search_path - "$user", public, pg_catalog, oracle At 2023-04-27 22:11:22,

Re:Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist

2023-04-27 Thread gzh
result. Why is there such a difference and how to solve it? At 2023-04-25 20:53:09, "Erik Wienhold" wrote: >> On 25/04/2023 13:34 CEST gzh wrote: >> >> >The solution is the same whether you upgrade or not: you need >> >to adjust your searc

Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist

2023-04-25 Thread gzh
le schema at the end of the search_path, the problem was solved. The search_path settings without problems are as follows: "$user", public, pg_catalog, oracle Why does it report an error when i put oracle between public and pg_catalog? At 2023-04-20 01:18:15, "Tom Lane

Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist

2023-04-19 Thread gzh
Thank you for your prompt reply. Is there another solution if the database is not upgraded to 12.14? >Better upgrade to latest release 12.14. At 2023-04-19 22:51:33, "Erik Wienhold" wrote: >> On 19/04/2023 15:24 CEST gzh wrote: >> >> Hi,

psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist

2023-04-19 Thread gzh
Hi, I upgraded the version of PostgreSQL from 12.10 to 12.13, when I insert data into the t_mstr table, the to_char function in the t_mstr's trigger caused the following error. psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist There is no problem before the upgra

Re: An I/O error occured while sending to the backend

2022-11-17 Thread gzh
aurenz Albe" wrote: >On Wed, 2022-11-16 at 13:04 +0800, gzh wrote: >> I have developed an application using the jdbc driver and >> connecting to a postgresql database . >> The application mainly does the following two things: >> ① read data from the CSV file and insert i

Re: An I/O error occured while sending to the backend

2022-11-15 Thread gzh
ed by ① ? Could you tell me why? At 2022-11-16 14:57:41, "Laurenz Albe" wrote: >On Wed, 2022-11-16 at 13:04 +0800, gzh wrote: >> I have developed an application using the jdbc driver and >> connecting to a postgresql database . >> The application

An I/O error occured while sending to the backend

2022-11-15 Thread gzh
Hi, I have developed an application using the jdbc driver and connecting to a postgresql database . The application mainly does the following two things: ① read data from the CSV file and insert it into the database ② perform a database query In my application, the above ① and ② are execu

Re:Re: Re: Does psqlodbc_11_01_0000-x64 support special characters?

2022-10-13 Thread gzh
Thank you for the information. After testing, I found that I only need to escape the following 7 characters. % → %25 " → %22 ' → %27 + → %2B ; → %3B = → %3D { → %7B At 2022-10-13 13:27:16, "Jeffrey Walton" wrote: >On Thu, Oct 13, 2022 at 12:13 AM gzh wro

Re:Re: Does psqlodbc_11_01_0000-x64 support special characters?

2022-10-12 Thread gzh
; → %3E ? → %3F @ → %40 [ → %5B \ → %5C ] → %5D ^ → %5E _ → %5F ` → %60 { → %7B | → %7C } → %7D ~ → %7E Kind regards, gzh At 2022-10-12 22:01:15, "Jeffrey Walton" wrote: >On Wed, Oct 12, 2022 at 7:16 AM gzh wrote: >> >> I found that the pas

Re:Does psqlodbc_11_01_0000-x64 support special characters?

2022-10-12 Thread gzh
I found that the password can't contain the % character, and the other special characters (* , $) are no problem. At 2022-10-12 16:28:51, "gzh" wrote: PostgreSQL version: 13.5 Operating system: windows 10 Description: I wrote a VBA applicatio

Re: Different execution plan between PostgreSQL 8.4 and 12.11

2022-10-12 Thread gzh
Hi everyone, Who can tell me which solution is better below: Solution 1: Change the configuration parameters set enable_seqscan = off Solution 2: Add DISTINCT clause to SQL explain analyze select DISTINCT 2 from analyze_word_reports where (cseid = 94) limit 1; If I

Does psqlodbc_11_01_0000-x64 support special characters?

2022-10-12 Thread gzh
PostgreSQL version: 13.5 Operating system: windows 10 Description: I wrote a VBA application to connect to PostgreSQL database by psqlodbc. The application works fine when there are no special characters in the password. When the password contains special characters (e.g. * , $

Re: Different execution plan between PostgreSQL 8.4 and 12.11

2022-10-11 Thread gzh
Thank you for all your assistance. By communicating with my customer, we have adopted the following solution to fix the problem. set enable_seqscan = off At 2022-10-11 16:21:42, "Pavel Stehule" wrote: út 11. 10. 2022 v 10:01 odesílatel gzh napsal: Tha

Re:Re: Re: Re: Re: Different execution plan between PostgreSQL 8.4 and 12.11

2022-10-11 Thread gzh
clause needs to be analyzed and checked). Is there no other way to solve the problem? At 2022-10-11 13:24:12, "Pavel Stehule" wrote: út 11. 10. 2022 v 7:08 odesílatel gzh napsal: Hi, Pavel > The LIMIT clause changes total cost. This is a very aggressiv

Re:Re: Re: Re: Different execution plan between PostgreSQL 8.4 and 12.11

2022-10-10 Thread gzh
22-10-11 12:13:47, "Pavel Stehule" wrote: út 11. 10. 2022 v 6:05 odesílatel gzh napsal: Hi, Pavel Thank you for your reply. > the LIMIT clause is in this case totally useless and messy, and maybe can > negative impacts optimizer Yes. After removing the LIMIT clause, the

Re:Re: Re: Different execution plan between PostgreSQL 8.4 and 12.11

2022-10-10 Thread gzh
but I don't want to fix the problem by modifying the SQL until I find the cause. At 2022-10-11 11:32:48, "Pavel Stehule" wrote: út 11. 10. 2022 v 5:13 odesílatel gzh napsal: Hi, Tom Thank you for your reply. > When you're asking for help, please don&

Re:Re: Different execution plan between PostgreSQL 8.4 and 12.11

2022-10-10 Thread gzh
t; If not, then there's something else going on besides poor estimates. "set enable_seqscan = off" works, and the performance is greatly improved, which is almost the same as PostgreSQL 8.4. The enable_seqscan(PostgreSQL 8.4) is on, will this change have an unknown effect on other

Re:Re: Different execution plan between PostgreSQL 8.4 and 12.11

2022-10-09 Thread gzh
Hi Tom, Thank you for your prompt response. I've run analyze(not vacuum analyze), but it doesn't seem to work. Is there any other way to optimize the database? At 2022-10-10 00:02:09, "Tom Lane" wrote: >gzh writes: >> I run following sql in Postgr

Different execution plan between PostgreSQL 8.4 and 12.11

2022-10-09 Thread gzh
nd is there any easy way to maintain compatibility? Regards, -- gzh

Re:Re: pg_dump without setting search_path

2022-08-24 Thread gzh
: >On 8/24/22 20:39, gzh wrote: >> Hi Tom, >> >> Thank you for your prompt response. >> >> When I use pg_dump to export schema from an older version of PostgreSQL >> 8.2.3 , it adds the following line at the beginning: >> >> SET search_path =

Re:Re: pg_dump without setting search_path

2022-08-24 Thread gzh
? At 2022-08-25 11:07:46, "Tom Lane" wrote: >gzh writes: >> When I use pg_dump to export schema from a database, it adds the following >> line at the beginning: >> SELECT pg_catalog.set_config('search_path', '', false); >> Is it possible s

pg_dump without setting search_path

2022-08-24 Thread gzh
When I use pg_dump to export schema from a database, it adds the following line at the beginning: SELECT pg_catalog.set_config('search_path', '', false); Is it possible set an option where pg_dump will not add this line? It is causing issues later when I try to execute other SQL commands

Re:Re:Re: Different execution plan between PostgreSQL 8.2 and 12.5

2022-08-19 Thread gzh
Dear Adrian, Sorry, there is an error in the email just replied, the version of PostgreSQL is wrong. PostgreSQL 8.4 → PostgreSQL 8.2 At 2022-08-19 12:42:54, "gzh" wrote: Dear Adrian, I appreciate your reply. Your reply gave me a new idea, it should not be the proble

Re:Re: Different execution plan between PostgreSQL 8.2 and 12.5

2022-08-18 Thread gzh
Dear Adrian, I appreciate your reply. Your reply gave me a new idea, it should not be the problem that the lower() function causes the unique index to fail. I checked the postgresql.conf file and found that shared_buffers, work_mem and maintenance_work_mem are default value, but in the pos

Re:Re: Different execution plan between PostgreSQL 8.2 and 12.5

2022-08-18 Thread gzh
ata rows. Please refer to my previous reply for more information. At 2022-08-18 12:18:31, "Adrian Klaver" wrote: >On 8/17/22 20:01, gzh wrote: >> Hi, >> >> >> I have had a Perl Website working for 7 years and have had no problems >> >>

Re:Re: Different execution plan between PostgreSQL 8.2 and 12.5

2022-08-18 Thread gzh
Dear David, Thanks for your reply. >In addition to that, I couldn't help notice that the quoted SQL does >not seem to belong to the explain. The EXPLAIN has a Limit node, but >the query does not. I'm assuming this isn't due to the relations being >views since we don't pull up subqueries with a L

Re:Re: Different execution plan between PostgreSQL 8.2 and 12.5

2022-08-18 Thread gzh
crew_base.introduced_by=crew_base_introduced.crewid where crew_base.status = '1'; At 2022-08-18 11:32:22, "Tom Lane" wrote: >gzh writes: >> I run following sql in PostgreSQL 8.2 and PostgreSQL 12.5, it returns >> different execution plan. > >8.2 is .

Different execution plan between PostgreSQL 8.2 and 12.5

2022-08-17 Thread gzh
data, but PostgreSQL 12.5 has not responded. I'm guessing that the lower() function of PostgreSQL 12.5 invalidates the index. But I don't understand why PostgreSQL 8.2 is normal. What is the reason for this and is there any easy way to maintain compatibility? Regards, -- gzh

Re:Re: Different sort result between PostgreSQL 8.4 and 12.5

2022-06-27 Thread gzh
d" in PostgreSQL 12.5 database, I got the same result as PostgreSQL 8.4 COLLATE "ja-JP-x-icu" Thank you for being so helpful. 2022-06-27 19:33:01,"Magnus Hagander" : On Mon, Jun 27, 2022 at 1:31 PM gzh wrote: Hi, I have had a Perl Website working for 7

Different sort result between PostgreSQL 8.4 and 12.5

2022-06-27 Thread gzh
-PostgreSQL 12.5 --- pg_db=# select ascid from test_order where oo_m.ascid in ('"! ascid"','"001"') order by ascid; ascid --- "001" "! ascid" (2 rows) What is the reason for this and is there any easy way to maintain compatibility? Regards, -- gzh

TO_DATE function between PostgreSQL 8.2 and 9.4

2022-05-17 Thread gzh
*** Of course, the most reliable way to deal with it is to rewrite the application or SQL to handle types strictly, but the application is large and rewrite is a terrible job. Is there any easy way to maintain compatibility? Regards, -- gzh

after psqlodbc upgrade - Which change solves the error: -2147217887

2021-07-09 Thread gzh
Add an option *Numeric(without precision) as*. 7.Fix a bug that SQLSpecialColumns() returns oid/xmin incorrectly when a table does not exist. Patch by Quan Zongliang. I want to know which change of the psqlODBC solves the error? Regards, -- gzh