ANALYZE, pg_class.xmin && pg_class.reltuples
Hi all, I've a doubt about running ANALYZE and inspecting pg_class.xmin and pg_class.reltuples. Let's create a simple table (note, I'm the only user on this instance): testdb=> CREATE TABLE fizz AS SELECT v FROM generate_series( 1, 100 ) v; SELECT 100 testdb=> SELECT relpages, reltuples, xmin, age( xmin ) FROM pg_class WHERE relname = 'fizz'; -[ RECORD 1 ]- relpages | 0 reltuples | 0 xmin | 2359180435 age | 1 Therefore transaction 435 created the table. Now analyze the table: testdb=> analyze fizz; ANALYZE testdb=> SELECT relpages, reltuples, xmin, age( xmin ) FROM pg_class WHERE relname = 'fizz'; -[ RECORD 1 ]- relpages | 1 reltuples | 100 xmin | 2359180435 age | 2 So the pg_class record has changed its content, and in fact we are now 2 transaction away the table creation, but the pg_class.xmin is the same. I would have expected that pg_class.xmin was updated as per user-tables.Now, clearly ANALYZE hit the pg_statistic table and the xmin "update" is there, but this would make me thing pg_class.reltuples is a generated column based on some aggregation of the latter pg_statistic, that apparently is not. Am I missing something? Thanks, Luca
Re: Error message while trying to connect from PGAdmin 4
Corrupt windows user was the reason that we found. When tried from other windows users, it's working. So switched to a new windows user and everything seems perfect.Thanks. On Thursday, 25 November, 2021, 11:11:56 am IST, sivapostg...@yahoo.com wrote: Hello PostgreSQL 11.11, PGAdmin 4.27, Windows 10 Pro 20H2 Working fine till yesterday (24.11.2021). When we tried to connect from PGAdmin, it refused to list server list. Thought of some corruption happened, we tried to add a server we got the following error message What could be the reason for this error? Any steps to diagnose this issue? When we try to connect the same PG Server from other machines, it's working fine. This issue happens when we try to connect from the same machine where PG is installed. Happiness Always BKR Sivaprakash
Re: ANALYZE, pg_class.xmin && pg_class.reltuples
Luca Ferrari writes: > I've a doubt about running ANALYZE and inspecting pg_class.xmin and > pg_class.reltuples. > ... > So the pg_class record has changed its content, and in fact we are now > 2 transaction away the table creation, but the pg_class.xmin is the > same. ANALYZE (and VACUUM) update the table's pg_class row non-transactionally. See the comments for vac_update_relstats(): https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/commands/vacuum.c;h=5c4bc15b441b36da99b0703500a3268086b40d9e;hb=HEAD#l1266 regards, tom lane
function difference not found
Function difference not found. Should an extension created for finding it? Regards, David
Re: function difference not found
On 11/25/21 08:12, Shaozhong SHI wrote: Function difference not found. This means what? Should an extension created for finding it? Finding what? Questions without substance will not get answers. Regards, David -- Adrian Klaver adrian.kla...@aklaver.com
Re: function difference not found
On 25/11/21 6:12 μ.μ., Shaozhong SHI wrote: Function difference not found. Should an extension created for finding it? fuzzystrmatch Regards, David -- Achilleas Mantzios DBA, Analyst, IT Lead IT DEPT Dynacom Tankers Mgmt
As a Linux distro, how to package multiple postgres major versions?
Hi, How to provide Postgres for a binary rolling release Linux distribution? Currently 9.6, 12 and 13 major versions are packaged in Void by me in way described below. No one reported practical problems with that, but some concerns arised, mainly around shared libraries. Constraints: - Want to allow to migrate data to new version of server. - Other software (postfix, qt5...) is linked dynamically to libpq.so, and providing variants of those per postgres version is not desired. - There are extensions provided (currently only postgis, more planned). Current model is: - Provide different major versions as packages installable at same time, except from postgresql-libs (libpq, libpgtypes, libecpg.so). Build every major version _N_ with different prefix: usr/lib/psqlN. This allows usage of pg_upgrade to migrate data. - Have one user-installable shared libraries package, always from newest available version. - Provide extensions for every version as different package built against target postgres version (e.g. postgis-postgresql12). - Do not rebuild packages depending on libpq.so when library is updated. - Rebuild packages depending on libpq.so against newest version when they are updated. - Upgrade of postgresql-libs does not force people to install and use newer server, this is done independently whenever they decide to. Now, my questions: - Is loading new major version library from old postgresql server, client, extension valid? - Is loading new major version library from package build against *old* major version to talk to old server valid? - Is loading new major version library from package build against *new* major version to talk to old server valid? - If any of above is wrong, what could be better model?
Re: function difference not found
On Thu, Nov 25, 2021 at 9:13 AM Shaozhong SHI wrote: > Function difference not found. > > Should an extension created for finding it? > > It's an operator (there may be a backing function that isn't documented)... SELECT 5 - 2; -- yields 3, the difference of 5 and 2. David J.
Re: function difference not found
2021-11-25 13:12 GMT-03:00, Shaozhong SHI : > Function difference not found. > > Should an extension created for finding it? > Perhaps you are talking about EXCEPT set operator? https://www.postgresql.org/docs/current/queries-union.html
Re: As a Linux distro, how to package multiple postgres major versions?
On 11/25/21 10:43 AM, Chocimier wrote: Hi, How to provide Postgres for a binary rolling release Linux distribution? Currently 9.6, 12 and 13 major versions are packaged in Void by me in way described below. No one reported practical problems with that, but some concerns arised, mainly around shared libraries. Constraints: - Want to allow to migrate data to new version of server. - Other software (postfix, qt5...) is linked dynamically to libpq.so, and providing variants of those per postgres version is not desired. - There are extensions provided (currently only postgis, more planned). Current model is: - Provide different major versions as packages installable at same time, except from postgresql-libs (libpq, libpgtypes, libecpg.so). Build every major version _N_ with different prefix: usr/lib/psqlN. The Debian PostgreSQL Maintainers has a separate tree for each major version. They *do not* rename the binaries. $ dir /usr/lib/postgresql/ total 8 drwxr-xr-x 4 root root 4096 2020-11-18 09:22:00 12/ drwxr-xr-x 4 root root 4096 2019-05-24 13:46:12 9.6/ postgres@haggis:~$ dir /usr/lib/postgresql/12 total 16 drwxr-xr-x 2 root root 4096 2021-09-08 00:43:11 bin/ drwxr-xr-x 4 root root 12288 2021-09-08 00:43:11 lib/ $ dir /usr/lib/postgresql/9.6 total 16 drwxr-xr-x 2 root root 4096 2021-09-08 00:43:13 bin/ drwxr-xr-x 3 root root 12288 2021-09-08 00:43:13 lib/ This allows usage of pg_upgrade to migrate data. - Have one user-installable shared libraries package, always from newest available version. - Provide extensions for every version as different package built against target postgres version (e.g. postgis-postgresql12). - Do not rebuild packages depending on libpq.so when library is updated. - Rebuild packages depending on libpq.so against newest version when they are updated. - Upgrade of postgresql-libs does not force people to install and use newer server, this is done independently whenever they decide to. Now, my questions: - Is loading new major version library from old postgresql server, client, extension valid? - Is loading new major version library from package build against *old* major version to talk to old server valid? - Is loading new major version library from package build against *new* major version to talk to old server valid? - If any of above is wrong, what could be better model? -- Angular momentum makes the world go 'round.
Re: As a Linux distro, how to package multiple postgres major versions?
Hello, Which distribution are you speaking about ? I'm using Gentoo and postgresql is versioned : you install the versions you're looking for and switch form one to another using eselect laurent@torchwood ~ $ eselect postgresql list Available PostgreSQL Slots 11 11.7 12 * 12.2 (I know my system is outdated : i'm blocked by regressions in PHP so don't updated this part for a while :) ) When you're upgrading your system, all supported version are rebuilt every time a dependencies change : should be long if you're running a large number of version but you keep a stable system. Bye Laurent Le jeudi 25 novembre 2021, 17:41:35 UTC+1, Chocimier a écrit : Hi, How to provide Postgres for a binary rolling release Linux distribution? Currently 9.6, 12 and 13 major versions are packaged in Void by me in way described below. No one reported practical problems with that, but some concerns arised, mainly around shared libraries. Constraints: - Want to allow to migrate data to new version of server. - Other software (postfix, qt5...) is linked dynamically to libpq.so, and providing variants of those per postgres version is not desired. - There are extensions provided (currently only postgis, more planned). Current model is: - Provide different major versions as packages installable at same time, except from postgresql-libs (libpq, libpgtypes, libecpg.so). Build every major version _N_ with different prefix: usr/lib/psqlN. This allows usage of pg_upgrade to migrate data. - Have one user-installable shared libraries package, always from newest available version. - Provide extensions for every version as different package built against target postgres version (e.g. postgis-postgresql12). - Do not rebuild packages depending on libpq.so when library is updated. - Rebuild packages depending on libpq.so against newest version when they are updated. - Upgrade of postgresql-libs does not force people to install and use newer server, this is done independently whenever they decide to. Now, my questions: - Is loading new major version library from old postgresql server, client, extension valid? - Is loading new major version library from package build against *old* major version to talk to old server valid? - Is loading new major version library from package build against *new* major version to talk to old server valid? - If any of above is wrong, what could be better model?
Best examples of cardinality check and associated functions
I wonder whether the Postgres community has got the best examples of cardinality check and associated functions. Regards, David
Re: Best examples of cardinality check and associated functions
> On Nov 25, 2021, at 3:16 PM, Shaozhong SHI wrote: > > > I wonder whether the Postgres community has got the best examples of > cardinality check and associated functions. > Are you looking for examples or opinions on the examples? > Regards, > > David
Re: Best examples of cardinality check and associated functions
Hi, Rob, I am reviewing robust automation to do so and promote the best practice. Regards, David D On Thu, 25 Nov 2021 at 22:24, Rob Sargent wrote: > > > > On Nov 25, 2021, at 3:16 PM, Shaozhong SHI > wrote: > > > > > > I wonder whether the Postgres community has got the best examples of > cardinality check and associated functions. > > > > Are you looking for examples or opinions on the examples? > > > Regards, > > > > David >
Re: Best examples of cardinality check and associated functions
> On Nov 25, 2021, at 4:17 PM, Shaozhong SHI wrote: > > > Hi, Rob, > > I am reviewing robust automation to do so and promote the best practice. Sorry I still don’t know what you’re after. Relationship cardinality, schema diagrams? > > Regards, > David > D > >> On Thu, 25 Nov 2021 at 22:24, Rob Sargent wrote: >> >> >> > On Nov 25, 2021, at 3:16 PM, Shaozhong SHI wrote: >> > >> > >> > I wonder whether the Postgres community has got the best examples of >> > cardinality check and associated functions. >> > >> >> Are you looking for examples or opinions on the examples? >> >> > Regards, >> > >> > David
Re: Best examples of cardinality check and associated functions
On Thu, Nov 25, 2021, 16:17 Shaozhong SHI wrote: > Hi, Rob, > > I am reviewing robust automation to do so and promote the best practice. > > Maybe show what you've come up with so far, regardless of whether it's best practice or not, so to help overcome the apparent communication barrier. David J.
case insensitive collation of Greek's sigma
Hello, during our tests of Postgres with ICU we found an issue with ILIKE of upper and lowercase sigma (Σ). The letter has two lowercase variants σ and ς (at the end of a word). I'm working with en_US and en-US-x-icu collations and results are a bit unexpected - they are inverted: postgres=# SELECT postgres-# 'ΣΣ' ILIKE 'σσ' COLLATE "en_US", postgres-# 'ΣΣ' ILIKE 'σς' COLLATE "en_US" postgres-# ; ?column? | ?column? --+-- t| f (1 row) postgres=# SELECT postgres-# 'ΣΣ' ILIKE 'σσ' COLLATE "en-US-x-icu", postgres-# 'ΣΣ' ILIKE 'σς' COLLATE "en-US-x-icu"; ?column? | ?column? --+-- f| t (1 row) I run those commands on the latest (14.1) official docker image. Is it possible to unify the behaviour?And which one is correct from the community point of view? If I could start, I think both results are wrong as both should return True. If I got it right, in the background there is a lower() function running to compare strings, which is not enough for such cases (until the left side isn't taken as a standalone word). Thanks, - jj
