Re:Re: Re: Does psqlodbc_11_01_0000-x64 support special characters?
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 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
Re: Exponentiation confusion
> On 13/10/2022 19:16 CEST Tom Lane wrote: > > Erik Wienhold writes: > > On 13/10/2022 18:20 CEST Adrian Klaver wrote: > >> select power(10, -18::numeric); > >> power > >> > >> 0. > >> > >> Why is the cast throwing off the result? > > > Calling power(numeric, numeric) is what I expect in that case instead of > > downcasting the exponent argument to double precision, thus losing > > precision. > > An inexact result isn't surprising, but it shouldn't be *that* inexact. Ah, now I see the problem. I saw a bunch of zeros but not that it's *all* zeros. Nevermind. -- Erik
Re: pg_upgrade to 15 fails on Windows because of xml_is_well_formed()
On 10/13/22 12:45, Thomas Kellerer wrote: Tom Lane schrieb am 13.10.2022 um 21:01: When trying pg_upgrade to upgrade Postgres 14 to 15 on Windows 10 Hmm, the xml2 extension is not installed in any of those databases. Most databases were probably migrated over time from 8.4 and I can't rule out that I did install xml2 there at some time in the past. I checked pg_proc and it the function was there twice: one in pg_catalog and one in the public schema. The one in the public schema would seem to be the smoking gun that points at xml2 having been installed from contrib in pre-extension days. Regards Thomas -- Adrian Klaver adrian.kla...@aklaver.com
Re: pg_upgrade to 15 fails on Windows because of xml_is_well_formed()
Tom Lane schrieb am 13.10.2022 um 21:01: When trying pg_upgrade to upgrade Postgres 14 to 15 on Windows 10 this fails with: pg_restore: error: could not execute query: ERROR: could not find function "xml_is_well_formed" in file "c:/Program Files/PostgreSQL/15/lib/pgxml.dll" I don't understand why this functions is included in the dump in the first place. Indeed. No such function should be present in any version of contrib/xml2 that was ever converted to extension style. Have you never done that in this DB? Hmm, the xml2 extension is not installed in any of those databases. Most databases were probably migrated over time from 8.4 and I can't rule out that I did install xml2 there at some time in the past. I checked pg_proc and it the function was there twice: one in pg_catalog and one in the public schema. After manually dropping the function from the public schema, pg_upgrade went through. It's probably not worth investigating how it got there. I tried to create and drop the xml2 extension to see if that maybe leaves the "orphaned" function in the public schema, but I couldn't reproduce it. Regards Thomas
Re: Exponentiation confusion
Dean Rasheed writes: > The most obvious thing to do is to try to make power_var_int() choose > the same result rscale as power_var() so that the results are > consistent regardless of whether the exponent is an integer. Yeah, I think we should try to end up with that. > It's worth noting, however, that that will cause in a *reduction* in > the output rscale rather than an increase in some cases, since the > power_var_int() code path currently always chooses an rscale of at > least 16, whereas the other code path in power_var() uses the rscales > of the 2 inputs, and produces a minimum of 16 significant digits, > rather than 16 digits after the decimal point. Right. I think this is not bad though. In a lot of cases (such as the example here) the current behavior is just plastering on useless zeroes. regards, tom lane
Re: Exponentiation confusion
On Thu, 13 Oct 2022 at 18:17, Tom Lane wrote: > > I'm inclined to think that we should push the responsibility for choosing > its rscale into power_var_int(), because internally that already does > estimate the result weight, so with a little code re-ordering we won't > need duplicative estimates. Don't have time to work on that right now > though ... Dean, are you interested in fixing this? > OK, I'll take a look. The most obvious thing to do is to try to make power_var_int() choose the same result rscale as power_var() so that the results are consistent regardless of whether the exponent is an integer. It's worth noting, however, that that will cause in a *reduction* in the output rscale rather than an increase in some cases, since the power_var_int() code path currently always chooses an rscale of at least 16, whereas the other code path in power_var() uses the rscales of the 2 inputs, and produces a minimum of 16 significant digits, rather than 16 digits after the decimal point. For example: select power(5.678, 18.0001::numeric); power - 37628507689498.14987457 (1 row) select power(5.678, 18::numeric); power - 37628507036041.8454541428979479 (1 row) Regards, Dean
Re: pg_upgrade to 15 fails on Windows because of xml_is_well_formed()
Thomas Kellerer writes: > When trying pg_upgrade to upgrade Postgres 14 to 15 on Windows 10 this fails > with: > pg_restore: error: could not execute query: ERROR: could not find function > "xml_is_well_formed" in file "c:/Program Files/PostgreSQL/15/lib/pgxml.dll" > I don't understand why this functions is included in the dump in the first > place. Indeed. No such function should be present in any version of contrib/xml2 that was ever converted to extension style. Have you never done that in this DB? regards, tom lane
pg_upgrade to 15 fails on Windows because of xml_is_well_formed()
When trying pg_upgrade to upgrade Postgres 14 to 15 on Windows 10 this fails
with:
pg_restore: creating FUNCTION "public.xml_is_well_formed("text")"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 647; 1255 23216 FUNCTION xml_is_well_formed("text")
postgres
pg_restore: error: could not execute query: ERROR: could not find function
"xml_is_well_formed" in file "c:/Program Files/PostgreSQL/15/lib/pgxml.dll"
Command was: CREATE FUNCTION "public"."xml_is_well_formed"("text") RETURNS
boolean
LANGUAGE "c" IMMUTABLE STRICT
AS '$libdir/pgxml', 'xml_is_well_formed';
The same error occurs when trying pg_dump/pg_restore, but the restore continues
without further errors and the database is usable after that.
I don't understand why this functions is included in the dump in the first
place.
A pristine 15 cluster already contains that function.
Any ideas?
Thomas
Re: Exponentiation confusion
Erik Wienhold writes: > On 13/10/2022 18:20 CEST Adrian Klaver wrote: >> select power(10, -18::numeric); >> power >> >> 0. >> >> Why is the cast throwing off the result? > Calling power(numeric, numeric) is what I expect in that case instead of > downcasting the exponent argument to double precision, thus losing precision. An inexact result isn't surprising, but it shouldn't be *that* inexact. It looks to me like numeric.c's power_var_int() code path is setting the result rscale without considering the possibility that the result will have negative weight (i.e. be less than one). The main code path in power_var() does adjust for that, so for example regression=# select power(10, -18.0001::numeric); power - 0.0099976974149 (1 row) but with an exact-integer exponent, not so much --- you just get 16 digits which isn't enough. I'm inclined to think that we should push the responsibility for choosing its rscale into power_var_int(), because internally that already does estimate the result weight, so with a little code re-ordering we won't need duplicative estimates. Don't have time to work on that right now though ... Dean, are you interested in fixing this? regards, tom lane
Re: Exponentiation confusion
On 2022-10-13 09:20:51 -0700, Adrian Klaver wrote: > In trying to answer an SO question I ran across this: > > Postgres version 14.5 > Same for 11.17. So it's been like that for some time, maybe forever. > select power(10, -18); > power > --- > 1e-18 > (1 row) > > select power(10, -18::numeric); >power > > 0. > > > Why is the cast throwing off the result? It seems that the number of decimals depends only on the first argument: hjp=> select power(10::numeric, -2::numeric); ╔╗ ║ power║ ╟╢ ║ 0.0100 ║ ╚╝ (1 row) hjp=> select power(10::numeric, -16::numeric); ╔╗ ║ power║ ╟╢ ║ 0.0001 ║ ╚╝ (1 row) hjp=> select power(10::numeric, -18::numeric); ╔╗ ║ power║ ╟╢ ║ 0. ║ ╚╝ (1 row) hjp=> select power(10::numeric, 18::numeric); ╔══╗ ║power ║ ╟──╢ ║ 100. ║ ╚══╝ (1 row) hjp=> select power(10::numeric(32,30), 18::numeric); ╔╗ ║ power║ ╟╢ ║ 100.00 ║ ╚╝ (1 row) hjp=> select power(10::numeric(32,30), -16::numeric); ╔══╗ ║ power ║ ╟──╢ ║ 0.000100 ║ ╚══╝ (1 row) So the number of decimals by default isn't sufficient to represent 10^-18. You have to explicitely increase it. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Exponentiation confusion
> On 13/10/2022 18:20 CEST Adrian Klaver wrote: > > In trying to answer an SO question I ran across this: > > Postgres version 14.5 > > select 10^(-1 * 18); > ?column? > -- > 1e-18 > > select 10^(-1 * 18::numeric); >?column? > > 0. > > > Same for power: > > select power(10, -18); > power > --- > 1e-18 > (1 row) > > select power(10, -18::numeric); > power > > 0. > > > Why is the cast throwing off the result? power has two overloads: https://www.postgresql.org/docs/14/functions-math.html#id-1.5.8.9.6.2.2.19.1.1.1 Calling power(numeric, numeric) is what I expect in that case instead of downcasting the exponent argument to double precision, thus losing precision. select pg_typeof(power(10, -18)), pg_typeof(power(10, -18::numeric)); pg_typeof | pg_typeof --+--- double precision | numeric (1 row) Determining the right function is described in https://www.postgresql.org/docs/14/typeconv-func.html -- Erik
Exponentiation confusion
In trying to answer an SO question I ran across this: Postgres version 14.5 select 10^(-1 * 18); ?column? -- 1e-18 select 10^(-1 * 18::numeric); ?column? 0. Same for power: select power(10, -18); power --- 1e-18 (1 row) select power(10, -18::numeric); power 0. Why is the cast throwing off the result? -- Adrian Klaver adrian.kla...@aklaver.com
Re: recovery.conf and archive files
The primary's recovery.conf looks like this listen_address='*' wal_level=replica synchronous_commit=local archive_move = on archive_command = 'cp %p /var/lib/pgsql/11/data/archive/%f' max_wal_senders = 10 wal_keep_segments=10 synchronous_standby_names='standby0' wal_log_hints=on On Sun, Oct 9, 2022 at 8:45 AM Guillaume Lelarge wrote: > Hi, > > Le dim. 9 oct. 2022 à 13:54, Rita a écrit : > >> I have primary and standby replication setup. >> >> On my primary the archive directory is rather large (30GB) and growing. >> On my standby I have recovery.conf which has >> archive_cleanup_command = 'pg_archivecleanup -d >> /var/lib/pgsql/11/data/archive %r' >> >> I was under the impression this line would remove data from my primary >> AND standby. Is that not the case? >> >> > pg_archivecleanup will clean up the *local* directory. It won't clean up > the archive directory if it's stored on the primary. > > If I misunderstood your issue, it would be great to send us the > postgresql.conf file from your primary. > > > -- > Guillaume. > -- --- Get your facts first, then you can distort them as you please.--
Re: Problem with LATERAL
On Thu, Oct 13, 2022 at 08:04:03AM +, Eagna wrote: > > > > ERROR: syntax error at or near "WHERE" > > > LINE 10: WHERE o.total_price > ISNULL(sub.paid, 0); > > > > There error here is because a JOIN clause requires a join condition. Adding > > an > > "ON true" is probably what you want. You would also need to change isnull() > > with coalesce(). > > > The final query should be: > > ... > ... > ... > > ) AS sub ON true > ... > ... > > OK - I see that it works now - which is great! > > However, it's unclear to me what, exactly, it is that is "TRUE"? > > What am I joining to what? > > The syntax is unclear to me - if I'm joining, I should be joining on > tab_A.field_x = tab_B.field_y - no? Well, yes but the join condition in that case is already in the WHERE clause in the sub select, so trying to put an actual join clause would be unnecessary and add extra cost. But I'm not sure why you want a LATERAL clause in the first place, wouldn't this query have the same meaning? SELECT o.order_id, o.total_price - coalesce(sum(p.amount), 0) FROM _order o LEFT JOIN payment p ON p.order_id = o.order_id GROUP BY o.order_id, o.total_price HAVING o.total_price > coalesce(sum(p.amount), 0); It should perform better if you have a lot of orders, as it can be executed with something better than a nested loop. > Why does SQL Server's OUTER APPLY not require this? I don't know much about sql server, I'm assuming CROSS APPLY is an alias for LEFT JOIN LATERAL () ON TRUE.
Re: Problem with LATERAL
> > ERROR: syntax error at or near "WHERE" > > LINE 10: WHERE o.total_price > ISNULL(sub.paid, 0); > There error here is because a JOIN clause requires a join condition. Adding an > "ON true" is probably what you want. You would also need to change isnull() > with coalesce(). > The final query should be: ... ... ... > ) AS sub ON true ... ... OK - I see that it works now - which is great! However, it's unclear to me what, exactly, it is that is "TRUE"? What am I joining to what? The syntax is unclear to me - if I'm joining, I should be joining on tab_A.field_x = tab_B.field_y - no? Why does SQL Server's OUTER APPLY not require this? Thanks for any input - Merci, À+ Rgs, E.
Re: Problem with LATERAL
Hi, On Thu, Oct 13, 2022 at 07:05:48AM +, Eagna wrote: > > relatively simple one would have thought! I tried to convert this into a > Postgres query as follows: > > SELECT o.order_id, > o.total_price - COALESCE(sub.paid, 0) > FROM _order o > LEFT JOIN LATERAL ( > SELECT SUM(p.amount) AS paid > FROM payment p > WHERE p.order_id = o.order_id > ) AS sub > WHERE o.total_price > ISNULL(sub.paid, 0); -- << line 10 - Error occurs! > > but I receive the error: > > ERROR: syntax error at or near "WHERE" > LINE 10: WHERE o.total_price > ISNULL(sub.paid, 0); There error here is because a JOIN clause requires a join condition. Adding an "ON true" is probably what you want. You would also need to change isnull() with coalesce(). The final query should be: SELECT o.order_id, o.total_price - COALESCE(sub.paid, 0) FROM _order o LEFT JOIN LATERAL ( SELECT SUM(p.amount) AS paid FROM payment p WHERE p.order_id = o.order_id ) AS sub ON true WHERE o.total_price > coalesce(sub.paid, 0);
Problem with LATERAL
Good Morning all, I am having a problem understanding a simple LATERAL join - I'm working on grasping them. All tables and data are at the bottom of this question and on the fiddles, SQL Server (working) and Postgres (not working). SQL Server fiddle - https://dbfiddle.uk/hjBBd87B Postgres fiddle - https://dbfiddle.uk/PihnqTwG I have the following - create table scripts and sample data are at end of this question and on the fiddles. The query in question which works on SQL Server is the following: SELECT o.order_id, o.total_price - COALESCE(p.paid, 0) AS remaining FROM _order o CROSS APPLY ( SELECT SUM(p.amount) AS paid FROM payment p WHERE p.order_id = o.order_id ) AS p WHERE o.total_price > ISNULL(p.paid, 0); relatively simple one would have thought! I tried to convert this into a Postgres query as follows: SELECT o.order_id, o.total_price - COALESCE(sub.paid, 0) FROM _order o LEFT JOIN LATERAL ( SELECT SUM(p.amount) AS paid FROM payment p WHERE p.order_id = o.order_id ) AS sub WHERE o.total_price > ISNULL(sub.paid, 0); -- << line 10 - Error occurs! but I receive the error: ERROR: syntax error at or near "WHERE" LINE 10: WHERE o.total_price > ISNULL(sub.paid, 0); I would be grateful if I could get a working query and also for an explanation as to what it is I'm doing incorrectly? The payment table has no primary key. Any input on the suitability of creating a surrogate one would also be appreciated. Please let me know if there's any important information missing. Rgs and TIA, E. CREATE TABLE _order ( order_idINT NOT NULL PRIMARY KEY, total_price INT NOT NULL ); INSERT INTO _order VALUES (1, 1000), (2, 2000), (3, 3000), (4, 4000); CREATE TABLE payment ( order_id INT NOT NULL, amount INT NOT NULL, CONSTRAINT payment_order_id_fk FOREIGN KEY (order_id) REFERENCES _order (order_id) ); CREATE INDEX pt_order_id_ix ON payment (order_id); -- normal indexing of foreign key field INSERT INTO payment VALUES (1, 500), (2, 2000), (3, 1000), (3, 500), (3, 750); -- note - no payment for order_id = 4
