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
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
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
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
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
-> 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
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
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
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
>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
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
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:
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
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
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
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
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.
>>
>> ---
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
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
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
'/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
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
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
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,
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
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
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,
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
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
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
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
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
; → %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
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
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
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. * , $
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
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
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
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&
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
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
nd is there any easy way to maintain compatibility?
Regards,
--
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 =
?
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
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
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
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
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
>>
>>
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
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 .
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
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
-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
***
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
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
56 matches
Mail list logo