Does psqlodbc_11_01_0000-x64 support special characters?
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. * , $ %), the application responds with an error below: Number: -2147467259 Description: password authentication failed for user 'testdb' I made an sample as below: VBA - START - Sub dbconnTest() Dim rs As ADODB.Recordset Dim sql As String Dim i As Integer Dim rcnt As Integer Set cnn = New ADODB.Connection cnn.Open "Provider=MSDASQL;Driver=PostgreSQL Unicode;UID=postgres;port=5432;Server=localhost;Database=testdb;PWD=Gd*oB,$3Ln%pQ" Set rs = New ADODB.Recordset sql = "SELECT * FROM testtbl" rs.ActiveConnection = cnn rs.Source = sql rs.Open cnt = rs.Fields.Count rcnt = 2 Do Until rs.EOF For i = 0 To cnt - 1 Cells(rcnt, i + 1).Value = rs.Fields(i) Next rcnt = rcnt + 1 rs.MoveNext Loop Set rs = Nothing Set cnn = Nothing End Sub - END - Thanks for any help!
Re: Weird planner issue on a standby
Le mer. 12 oct. 2022 à 08:56, Guillaume Lelarge a écrit : > Le mar. 11 oct. 2022 à 19:42, Guillaume Lelarge > a écrit : > >> Le mar. 11 oct. 2022 à 18:27, Alvaro Herrera a >> écrit : >> >>> On 2022-Oct-11, Tom Lane wrote: >>> >>> > Are there any tables in this query where extremal values of the join >>> > key are likely to be in recently-added or recently-dead rows? Does >>> > VACUUM'ing on the primary help? >>> >>> I remember having an hypothesis, upon getting a report of this exact >>> problem on a customer system once, that it could be due to killtuple not >>> propagating to standbys except by FPIs. I do not remember if we proved >>> that true or not. I do not remember observing that tables were being >>> read, however. >>> >>> >> Thanks for your answers. >> >> The last predicate is "and c3>='2020-10-10' and c3<='2022-10-10'. I have >> no idea on the actual use of c3 but rows with c3 at '2022-10-10' (which is >> yesterday) is much probably recently-added. I can ask my customer if you >> want but this looks like a pretty safe bet. >> >> On the VACUUM question, I didn't say, but we're kind of wondering if it >> was lacking a recent-enough VACUUM. So they're doing a VACUUM tonight on >> the database (and especially on the 1.6TB table which is part of the >> query). I'm kind of skeptical because if the VACUUM wasn't enough on the >> standby, it should be the same on the primary. >> >> > It appears that I was wrong. I just got an email from my customer saying > they got their performance back after a VACUUM on the two main tables of > the query. I'll have them on the phone in about an hour. I'll probably know > more then. Still wondering why it was an issue on the standby and not on > the primary. VACUUM cleans up tables and indexes, and this activity goes > through WAL, doesn't it? > > Just finished my phone call. So, they definitely have their performance back. All they did was a VACUUM on two tables. If I understand correctly, during "normal" operations, some information is stored on the primary and sent to standbys. For some reason, only the primary take them into account, standbys ignore them. That would explain why, when we promoted a standby without doing anything else, it had much better performance. VACUUM fixes the issue on a standby, probably by storing this information in a different way. After VACUUM, standbys stop ignoring this information, which helps get the performance back. That sounds like a plausible explanation. I still have questions if you don't mind: * what is this information? * where is it stored? my guess would be indexes * why is it ignored on standbys and used on primary? We didn't talk much about releases, so I guess that the "standby-ignores-some-information" part is currently on all available releases? Thank you. -- Guillaume.
Re: Different execution plan between PostgreSQL 8.4 and 12.11
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 don't want to change SQL, is Solution 1 OK?
At 2022-10-12 09:47:17, "David Rowley" wrote:
>On Wed, 12 Oct 2022 at 13:06, Klint Gore wrote:
>> Limit (cost=0.56..28349.28 rows=1 width=4) (actual time=0.039..0.040 rows=1
>> loops=1)
>> -> Unique (cost=0.56..28349.28 rows=1 width=4) (actual time=0.039..0.039
>> rows=1 loops=1)
>> -> Index Only Scan using idx on tbl (cost=0.56..28349.28
>> rows=995241 width=4) (actual time=0.038..0.038 rows=1 loops=1)
>> Index Cond: (fld = 230)
>> Heap Fetches: 0
>> Planning Time: 0.066 ms
>> Execution Time: 0.047 ms
>>
>> With the distinct and the limit, the planner somehow knows to push the
>> either the distinct or the limit into the index only scan so the unique for
>> distinct only had 1 row and the outer limit only had 1 row. Without the
>> limit, the distinct still does the index only scan but has to do the unique
>> on the million rows and execution time goes to about 100ms.
>
>I think that would be very simple to fix. I believe I've done that
>locally but just detecting if needed_pathkeys == NULL in
>create_final_distinct_paths().
>
>i.e.
>
>- if (pathkeys_contained_in(needed_pathkeys,
>path->pathkeys))
>+ if (needed_pathkeys == NIL)
>+ {
>+ Node *limitCount = makeConst(INT8OID,
>-1, InvalidOid,
>+
> sizeof(int64),
>+
> Int64GetDatum(1), false,
>+
> FLOAT8PASSBYVAL);
>+ add_path(distinct_rel, (Path *)
>+
>create_limit_path(root, distinct_rel, path, NULL,
>+
>limitCount, LIMIT_OPTION_COUNT, 0,
>+
>1));
>+ }
>+ else if
>(pathkeys_contained_in(needed_pathkeys, path->pathkeys))
>
>That just adds a Limit Path instead of the Unique Path. i.e:
>
>postgres=# explain (analyze, costs off) select distinct a from t1 where a = 0;
> QUERY PLAN
>--
> Limit (actual time=0.074..0.075 rows=1 loops=1)
> -> Index Only Scan using t1_a_idx on t1 (actual time=0.072..0.073
>rows=1 loops=1)
> Index Cond: (a = 0)
> Heap Fetches: 1
> Planning Time: 0.146 ms
> Execution Time: 0.100 ms
>(6 rows)
>
>However, I might be wrong about that. I've not given it too much thought.
>
>David
Re:Does psqlodbc_11_01_0000-x64 support special characters?
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 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. * , $ %), the application responds with an error below: Number: -2147467259 Description: password authentication failed for user 'testdb' I made an sample as below: VBA - START - Sub dbconnTest() Dim rs As ADODB.Recordset Dim sql As String Dim i As Integer Dim rcnt As Integer Set cnn = New ADODB.Connection cnn.Open "Provider=MSDASQL;Driver=PostgreSQL Unicode;UID=postgres;port=5432;Server=localhost;Database=testdb;PWD=Gd*oB,$3Ln%pQ" Set rs = New ADODB.Recordset sql = "SELECT * FROM testtbl" rs.ActiveConnection = cnn rs.Source = sql rs.Open cnt = rs.Fields.Count rcnt = 2 Do Until rs.EOF For i = 0 To cnt - 1 Cells(rcnt, i + 1).Value = rs.Fields(i) Next rcnt = rcnt + 1 rs.MoveNext Loop Set rs = Nothing Set cnn = Nothing End Sub - END - Thanks for any help!
Re: Weird planner issue on a standby
On Wed, 2022-10-12 at 10:51 +0200, Guillaume Lelarge wrote: > Just finished my phone call. So, they definitely have their performance back. > All they did was a VACUUM on two tables. > > If I understand correctly, during "normal" operations, some information is > stored on the primary > and sent to standbys. For some reason, only the primary take them into > account, standbys ignore them. > That would explain why, when we promoted a standby without doing anything > else, it had much better > performance. VACUUM fixes the issue on a standby, probably by storing this > information in a different > way. After VACUUM, standbys stop ignoring this information, which helps get > the performance back. > > That sounds like a plausible explanation. I still have questions if you don't > mind: > * what is this information? > * where is it stored? my guess would be indexes > * why is it ignored on standbys and used on primary? That sounds indeed like killed (LP_DEAD) index tuples on the primary. Peter says they are ignored on the standby anyway, so on the standby PostgreSQL went through a lot of index entries pointing to dead table tuples, and it took a long time to find the maximal entry in the table, which is done by the optimizer. VACUUM removed those dead tuples and their associated index entries on both primary and standby. Yours, Laurenz Albe
Re: Weird planner issue on a standby
Peter Geoghegan writes: > That's true, but it doesn't matter whether or not there are LP_DEAD > bits set on the standby, since in any case they cannot be trusted when > in Hot Standby mode. IndexScanDescData.ignore_killed_tuples will be > set to false on the standby. Hmm. I think that might break this argument in get_actual_variable_endpoint: * A crucial point here is that SnapshotNonVacuumable, with * GlobalVisTestFor(heapRel) as horizon, yields the inverse of the * condition that the indexscan will use to decide that index entries are * killable (see heap_hot_search_buffer()). Therefore, if the snapshot * rejects a tuple (or more precisely, all tuples of a HOT chain) and we * have to continue scanning past it, we know that the indexscan will mark * that index entry killed. That means that the next * get_actual_variable_endpoint() call will not have to re-consider that * index entry. In this way we avoid repetitive work when this function * is used a lot during planning. However, that doesn't explain the downthread report that a VACUUM on the primary fixed it. What I suspect is that that caused some in-fact-dead index entries to get cleaned out. But ... if the primary is allowed to vacuum away an index entry that it thinks is dead, exactly what is the point of making standbys ignore LP_DEAD bits? There's no additional interlock that guarantees the tuple will be there at all. regards, tom lane
Re: Does psqlodbc_11_01_0000-x64 support special characters?
On Wed, Oct 12, 2022 at 7:16 AM gzh wrote: > > I found that the password can't contain the % character, and the other > special characters (* , $) are no problem. You need to percent-encode the password if you wish to use the % symbol in the password. There are other reserved characters that you should percent-encode. See https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING and https://www.rfc-editor.org/rfc/rfc3986#section-2.1 . Jeff > At 2022-10-12 16:28:51, "gzh" wrote: > > > 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. * , $ %), > > the application responds with an error below: > > > Number: -2147467259 > > Description: password authentication failed for user 'testdb' > > > I made an sample as below: > > > VBA > > - START - > > > Sub dbconnTest() > > Dim rs As ADODB.Recordset > > Dim sql As String > > Dim i As Integer > > Dim rcnt As Integer > > > > Set cnn = New ADODB.Connection > > cnn.Open "Provider=MSDASQL;Driver=PostgreSQL > Unicode;UID=postgres;port=5432;Server=localhost;Database=testdb;PWD=Gd*oB,$3Ln%pQ" > > > > Set rs = New ADODB.Recordset > > sql = "SELECT * FROM testtbl" > > > > rs.ActiveConnection = cnn > > rs.Source = sql > > rs.Open > > > > cnt = rs.Fields.Count > > rcnt = 2 > > > > Do Until rs.EOF > > For i = 0 To cnt - 1 > > Cells(rcnt, i + 1).Value = rs.Fields(i) > > Next > > > > rcnt = rcnt + 1 > > rs.MoveNext > > Loop > > > > Set rs = Nothing > > Set cnn = Nothing > > End Sub > > > - END - > > > > Thanks for any help! >
Re: Weird planner issue on a standby
On Wed, Oct 12, 2022 at 6:47 AM Tom Lane wrote: > However, that doesn't explain the downthread report that a > VACUUM on the primary fixed it. What I suspect is that that > caused some in-fact-dead index entries to get cleaned out. Seems likely. > But ... if the primary is allowed to vacuum away an index > entry that it thinks is dead, exactly what is the point of > making standbys ignore LP_DEAD bits? There's no additional > interlock that guarantees the tuple will be there at all. The interlock doesn't really protect the leaf page or its index tuples so much as the referenced TIDs themselves. In other words it's a TID recycling interlock. That's why we don't need a cleanup lock to perform index tuple deletions, even though the WAL records for those are almost identical to the WAL records used by index vacuuming (in the case of nbtree the only difference is the extra latestRemovedXid field in the deletion variant WAL record). We know that there is no VACUUM process involved, and no question of heap vacuuming going ahead for the same TIDs once index vacuuming is allowed to complete. We can get away with not having the interlock at all in the case of nbtree index scans with MVCC snapshots -- but *not* with index-only scans. See "Making concurrent TID recycling safe" in the nbtree README. I only got around to documenting all of the details here quite recently. The index-only scan thing dates back to 9.5. -- Peter Geoghegan
Re: Different execution plan between PostgreSQL 8.4 and 12.11
From: gzh Sent: Wednesday, 12 October 2022 9:30 PM > 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 don't want to change SQL, is Solution 1 OK? Both solutions are ugly enable_seqscan is a really blunt instrument and may affect the rest of your system as well as just this query. Queries that boil down to "select * from partition" are now encouraged to use the index in a useless manor. A small table (e.g. to hold application settings) now has to do a primary key lookup when all rows fit on the first page anyway. distinct+limit is really just trying to convince the v12 planner that it can bail out after the first row found at all levels. Having both is superfluous as they individually end up at the same result. it may not work in v13/14/15/... or even be needed. Have you tried it on your v12? My data may be different enough to your data that it doesn't work anyway. What it does in the old postgres version is anyone's guess. Solution 1 I'd treat as an emergency stop gap to buy time to find a better solution. The patient is no longer bleeding out and the path forward can be considered. If you're not going to change the app, then the only other choice is play with other system wide settings (like random_page_cost). Not as blunt as enable_seqscan but still affects all queries, not just this one.
Re:Re: Does psqlodbc_11_01_0000-x64 support special characters?
Dear Jeff
I appreciate your reply.
My PostgreSQL is deployed on Amazon RDS, so the password of PostgreSQL is
random and has various reserved characters.
I don't know if the reserved characters below are complete, and there are some
characters (e.g. * , $) I tried without problems.
Could you tell me which characters require percent-encoding for PostgreSQL
password?
space → %20
! → %21
" → %22
# → %23
$ → %24
% → %25
& → %26
' → %27
( → %28
) → %29
* → %2A
+ → %2B
, → %2C
- → %2D
. → %2E
/ → %2F
: → %3A
; → %3B
< → %3C
= → %3D
> → %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 password can't contain the % character, and the other
>> special characters (* , $) are no problem.
>
>You need to percent-encode the password if you wish to use the %
>symbol in the password. There are other reserved characters that you
>should percent-encode. See
>https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING
>and https://www.rfc-editor.org/rfc/rfc3986#section-2.1 .
>
>Jeff
>
>> At 2022-10-12 16:28:51, "gzh" wrote:
>>
>>
>> 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. * , $ %),
>>
>> the application responds with an error below:
>>
>>
>> Number: -2147467259
>>
>> Description: password authentication failed for user 'testdb'
>>
>>
>> I made an sample as below:
>>
>>
>> VBA
>>
>> - START -
>>
>>
>> Sub dbconnTest()
>>
>> Dim rs As ADODB.Recordset
>>
>> Dim sql As String
>>
>> Dim i As Integer
>>
>> Dim rcnt As Integer
>>
>>
>>
>> Set cnn = New ADODB.Connection
>>
>> cnn.Open "Provider=MSDASQL;Driver=PostgreSQL
>> Unicode;UID=postgres;port=5432;Server=localhost;Database=testdb;PWD=Gd*oB,$3Ln%pQ"
>>
>>
>>
>> Set rs = New ADODB.Recordset
>>
>> sql = "SELECT * FROM testtbl"
>>
>>
>>
>> rs.ActiveConnection = cnn
>>
>> rs.Source = sql
>>
>> rs.Open
>>
>>
>>
>> cnt = rs.Fields.Count
>>
>> rcnt = 2
>>
>>
>>
>> Do Until rs.EOF
>>
>> For i = 0 To cnt - 1
>>
>> Cells(rcnt, i + 1).Value = rs.Fields(i)
>>
>> Next
>>
>>
>>
>> rcnt = rcnt + 1
>>
>> rs.MoveNext
>>
>> Loop
>>
>>
>>
>> Set rs = Nothing
>>
>> Set cnn = Nothing
>>
>> End Sub
>>
>>
>> - END -
>>
>>
>>
>> Thanks for any help!
>>
Re: Re: Does psqlodbc_11_01_0000-x64 support special characters?
On Thu, Oct 13, 2022 at 12:13 AM gzh wrote:
>
> My PostgreSQL is deployed on Amazon RDS, so the password of PostgreSQL is
> random and has various reserved characters.
>
> I don't know if the reserved characters below are complete, and there are
> some characters (e.g. * , $) I tried without problems.
>
> Could you tell me which characters require percent-encoding for PostgreSQL
> password?
>
>
> space → %20
>
> ! → %21
>
> " → %22
>
> # → %23
>
> $ → %24
>
> % → %25
>
> & → %26
>
> ' → %27
>
> ( → %28
>
> ) → %29
>
> * → %2A
>
> + → %2B
>
> , → %2C
>
> - → %2D
>
> . → %2E
>
> / → %2F
>
> : → %3A
>
> ; → %3B
>
> < → %3C
>
> = → %3D
>
> > → %3E
>
> ? → %3F
>
> @ → %40
>
> [ → %5B
>
> \ → %5C
>
> ] → %5D
>
> ^ → %5E
>
> _ → %5F
>
> ` → %60
>
> { → %7B
>
> | → %7C
>
> } → %7D
>
> ~ → %7E
https://www.rfc-editor.org/rfc/rfc3986#section-2.2
Jeff
