Re: sort order for UTF-8 char column with Japanese UTF-8
El día Donnerstag, Februar 03, 2022 a las 10:00:37 -0500, Tom Lane escribió: > Matthias Apitz writes: > > El día jueves, febrero 03, 2022 a las 11:14:55 +0100, Matthias Apitz > > escribió: > >> With ESQL/C on a PostgreSQL 13.1 server I see the result of this query: > >> select katkey,normform from swd_anzeige where normform >= 'A' ORDER BY ASC; > >> coming out in this order: > >> ... > >> I loaded the same table in my server, but can't get the same order with > >> psql: > > Do the two machines produce the same results if you sort the data in > question with sort(1)? (Being careful to set LANG=de_DE.UTF-8 of > course.) I rather doubt this has anything to do with Postgres as such; > there are lots of inter-system and inter-release discrepancies in > collation behavior. No, they do not. I gathered from the admin of the remote (customer) server the output of sort(1) in dependency of LANG and LC_ALL (see below). Only when I let her unset the UTF-8 env vars, the result is with the Japanese lines at the end, with UTF-8 env they're sorted at the beginning. On my own server (the only difference is that my server is SUSE Linux Enterprise Server 15 SP3, while the customer still runs SP2) I never get the Japanese lines on top with the same commands as used by the remote admin. I requested now in addition the output of ls -l /lib64/libc.* /usr/lib/locale/de_DE.utf8 to see if the libc version is different, mine is libc-2.31.so Said that, does the SORT done by the server depends on the environment (LANG, LC_*) in which the PostgreSQL server is started or only of the sp_catalog information of the database in question? Thanks matthias LC_ALL=de_DE.UTF-8 sort swd A ゲアハルト・A・リッター ゲルハルト・A・リッター チャールズ・A・ビアード A010STRUKTUR A010STRUKTUR A010STRUKTUR A0150SUPRALEITER LANG=de_DE.UTF-8 sort swd A ゲアハルト・A・リッター ゲルハルト・A・リッター チャールズ・A・ビアード A010STRUKTUR A010STRUKTUR A010STRUKTUR A0150SUPRALEITER sort swd A ゲアハルト・A・リッター ゲルハルト・A・リッター チャールズ・A・ビアード A010STRUKTUR A010STRUKTUR A010STRUKTUR A0150SUPRALEITER env | grep LC LC_ALL=de_DE.UTF-8 env | grep LANG LANG=de_DE.UTF-8 unset LC_ALL LC_COLLATE LANG sort swd A A010STRUKTUR A010STRUKTUR A010STRUKTUR A0150SUPRALEITER ゲアハルト・A・リッター ゲルハルト・A・リッター チャールズ・A・ビアード -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
Re: sort order for UTF-8 char column with Japanese UTF-8
Matthias Apitz writes: > Said that, does the SORT done by the server depends on the environment > (LANG, LC_*) in which the PostgreSQL server is started or only of the > sp_catalog information of the database in question? We set LC_COLLATE and LC_CTYPE from the database's relevant pg_database fields. The remaining LC environment variables shouldn't affect this (and I think we force them to "C" anyway). regards, tom lane
Re: increasing effective_cache_size slows down join queries by a factor of 4000x
On 2/4/22 05:21, A Shaposhnikov wrote: Tomas, thank you! The query: select 1 from data as d, data_class as dc where dc.data_id = d.id and d.id > 205284974 and dc.data_id > 205284974 -- new condition order by d.id limit 1000; totally solved it - it is now fast under all conditions! I thought that the optimizer would be able to infer it itself. Unfortunately, the optimizer is not that smart - we can do that for equality conditions, but not for other operators. There was actually a thread [1] exploring a possibility to extend this to inequalities, but it went nowhere so far. It also explains why it's done only for equality operators. In short, it's fairly expensive, makes costing of joins more difficult, and most queries can't benefit from it (because conditions on join keys are not that common). BTW how does the final query plan look like? Is it using the merge sort of nested loop? I wonder if this might be formulated as a costing issue, pushing the planner to use the nested loop. [1] https://www.postgresql.org/message-id/flat/CAFQUnFhqkWuPCwQ1NmHYrisHJhYx4DoJak-dV%2BFcjyY6scooYA%40mail.gmail.com regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Postgres Version Upgrade to 14.1 error
Thanks Pavel. Do you know pgq is compatible with postgresql-server-dev-14 packages? I couldn't see any requirements or something documented for pgq and postgres 14. Pavel Stehule , 4 Şub 2022 Cum, 01:17 tarihinde şunu yazdı: > Hi > > pá 4. 2. 2022 v 7:02 odesílatel rob stan napsal: > >> Hello, >> >> We have "pgq" extensions on our clusters when I am trying to upgrade from >> 9.6.24 to 14.1 with pg_upgrade --link method, i am getting error; >> >> >> First i was getting this error ; >> >> could not load library "$libdir/pgq_lowlevel": ERROR: could not access >> file "$libdir/pgq_lowlevel": No such file or directory >> could not load library "$libdir/pgq_triggers": ERROR: could not access >> file "$libdir/pgq_triggers": No such file or directory >> >> After i copied pgq files to extension directory; >> /usr/lib/postgresql/9.6/lib# cp pgq_lowlevel.so >> /usr/lib/postgresql/14.1/lib/ >> /usr/lib/postgresql/9.6/lib# cp pgq_triggers.so >> /usr/lib/postgresql/14.1/lib/ >> >> I am getting this error message; >> could not load library "$libdir/pgq_triggers": ERROR: could not load >> library "/usr/lib/postgresql/14.1/lib/pgq_triggers.so": >> /usr/lib/postgresql/14.1/lib/pgq_triggers.so: undefined symbol: elog_finish >> >> >> Do you have any ideas on how to solve this problem? >> > > PostgreSQL extensions are compiled against one specific major release - so > you cannot use pgq for 9.6 in Postgres 14. You should install pgq for > PostgreSQL 14. > > Regards > > Pavel > > >> Thanks! >> >
Re: Postgres Version Upgrade to 14.1 error
On 2/4/22 12:17, rob stan wrote: Thanks Pavel. Do you know pgq is compatible with postgresql-server-dev-14 packages? I couldn't see any requirements or something documented for pgq and postgres 14. From here: https://github.com/pgq/pgq/releases it looks like it only officially goes to version 13. -- Adrian Klaver adrian.kla...@aklaver.com
Re. Backup of postgresql database
Hello,Got few (currently 3 will increase as days go) database in A2 hosting unmanaged vps server, running Ubuntu server 18 lts Planning to have an additional backup of all files, including pg database, to another cloud provider like Amazon S3 (any other suggestions?) Size won't be crossing 5GB per database for the next few years. How this could be done? Happiness AlwaysBKR Sivaprakash Sent from Yahoo Mail on Android
Re: Re. Backup of postgresql database
On 2/4/22 9:14 PM, sivapostg...@yahoo.com wrote: Hello, Got few (currently 3 will increase as days go) database in A2 hosting unmanaged vps server, running Ubuntu server 18 lts Planning to have an additional backup of all files, including pg database, to another cloud provider like Amazon S3 (any other suggestions?) Size won't be crossing 5GB per database for the next few years. How are you *currently* backing up the databases? -- Angular momentum makes the world go 'round.
Re: Re. Backup of postgresql database
Pg_dump or do a wal archive. Thanks, Ben On Fri, Feb 4, 2022, 10:49 PM Ron wrote: > On 2/4/22 9:14 PM, sivapostg...@yahoo.com wrote: > > Hello, > Got few (currently 3 will increase as days go) database in A2 hosting > unmanaged vps server, running Ubuntu server 18 lts > > Planning to have an additional backup of all files, including pg database, > to another cloud provider like Amazon S3 (any other suggestions?) > > Size won't be crossing 5GB per database for the next few years. > > > How are you *currently* backing up the databases? > > -- > Angular momentum makes the world go 'round. >
