Re: error in the example given for numeric data types
> Page: https://www.postgresql.org/docs/15/datatype-numeric.html "docs/15" means this url pointer to pg version 15. > practice=# create table t1(height numeric(3,5)); > ERROR: NUMERIC scale 5 must be between 0 and precision 3 > LINE 1: create table t1(height numeric(3,5)); > Please look into the matter and kindly revert back to me whatever you find > out about this so that i can correct myself incase i misunderstood what the > document says... it works in pg15, not in pg14. see my test: https://dbfiddle.uk/wgfjCx7j
Re: error in the example given for numeric data types
jian he writes: >> practice=# create table t1(height numeric(3,5)); >> ERROR: NUMERIC scale 5 must be between 0 and precision 3 > it works in pg15, not in pg14. > see my test: https://dbfiddle.uk/wgfjCx7j Indeed. The quoted documentation text is different between v15 and prior versions. Observe also the v15 release notes: https://www.postgresql.org/docs/15/release-15.html E.4.3.4. Data Types Allow the scale of a numeric value to be negative, or greater than its precision (Dean Rasheed, Tom Lane) This allows rounding of values to the left of the decimal point, e.g., '1234'::numeric(4, -2) returns 1200. regards, tom lane
Re: pg_restore mostly idle on restoring a large number of tables
On 7/13/23 02:41, Boris Sagadin wrote: Hi, restoring a 1.5TB database with about 800k tables on i3.4xlarge AWS instace, PgSQL V12.15 on Ubuntu. Running pg_restore with -j 16, I noticed the pg_restore is busy for an hour or so with IO at 80%+ and then most of processes start idling and only a few doing some work, disk IO at 1-2%, pg_stat_activity is mostly idle, same goes for CPU, and this state proceeds for further 6 hours, disk space increases very slowly. I thought because of a lot of small tables, number of workers should be increased to increase parallel efficiency, so I tried with -j 128. The situation was somewhat better, but most of the workers start idling, again disk IO lowers to about 4% util, CPU util goes to about 4%, too. Stracing workers produces the perpetual read call on most pg_restore workers: # strace -p 59567 strace: Process 59567 attached read(3, With only about 10 or so (out of 128) workers doing some actual work: strace -p 59367 -e sendto strace: Process 59367 attached sendto(4, "Q\0\0\0\263ALTER TABLE ONLY raw.space0"..., 180, MSG_NOSIGNAL, NULL, 0) = 180 sendto(4, "Q\0\0\0.TRUNCATE TABLE ONLY raw.spa"..., 47, MSG_NOSIGNAL, NULL, 0) = 47 sendto(4, "Q\0\0\0\205COPY raw.space000448117 (da"..., 134, MSG_NOSIGNAL, NULL, 0) = sendto(4, "Q\0\0\0\vCOMMIT\0", 12, MSG_NOSIGNAL, NULL, 0) = 12 sendto(4, "Q\0\0\0pCREATE INDEX idx_space00044"..., 113, MSG_NOSIGNAL, NULL, 0) = 113 sendto(4, "Q\0\0\0\263ALTER TABLE ONLY raw.space0"..., 180, MSG_NOSIGNAL, NULL, 0) = 180 ... .. some lines omitted I would think that all workers would proceed with creating indexes and doing some useful work until the restore is finished completely? Most of the tables are very small, 2 indexes per table and without any foreign references etc., we have a multi tenant environment. I always run pg_dump and pg_restore with "--verbose", and redirect stdout & stderr to a log file. Then I "tail -f" that log file. It'll tell you what's happening. Also, iotop is quite useful. -- Born in Arizona, moved to Babylonia.
Rocky Linux 9 and postgres10
Good afternoon! I am writing to ask you the following question. Can postgres10 be installed on rocky linux 9? Although I know that it is a version that has already finished its life cycle, we are in migration processes We have a system with centos 7.5 and postgres 10 and the infrastructure group wants to do an installation with rocky linux 9 I have not found anywhere if it is supported Thank you very much for your time
Re: Rocky Linux 9 and postgres10
On Sat, Jul 15, 2023 at 9:37 AM Daniel Gallo wrote: > > Can postgres10 be installed on rocky linux 9? > > PostgreSQL has been running successfully on Linux basically forever so it indeed should work just fine. Whether you will have to build from source or can find an installer is another matter. David J.
Re: Rocky Linux 9 and postgres10
On 7/15/23 09:37, Daniel Gallo wrote: Good afternoon! I am writing to ask you the following question. Can postgres10 be installed on rocky linux 9? https://yum.postgresql.org/packages/#pg10 PostgreSQL 10 This version is now End-Of-Life. Please upgrade to a supported version as soon as possible. RHEL / Rocky Linux 9 - x86_64 ... Although I know that it is a version that has already finished its life cycle, we are in migration processes We have a system with centos 7.5 and postgres 10 and the infrastructure group wants to do an installation with rocky linux 9 I have not found anywhere if it is supported Thank you very much for your time -- Adrian Klaver adrian.kla...@aklaver.com
Re: error in the example given for numeric data types
First of all thanks for your reply.This mean that this was a bug in the previous versions right? Secondly, I would love to connect with you as I am a college student and I want to be a contributor to open source software so can we connect on some platform? I really need someone to guide me Thanking you in advance, Priyank Rajvansh On Sat, 15 Jul, 2023, 12:46 pm jian he, wrote: > > Page: https://www.postgresql.org/docs/15/datatype-numeric.html > > "docs/15" means this url pointer to pg version 15. > > > practice=# create table t1(height numeric(3,5)); > > ERROR: NUMERIC scale 5 must be between 0 and precision 3 > > LINE 1: create table t1(height numeric(3,5)); > > Please look into the matter and kindly revert back to me whatever you > find > > out about this so that i can correct myself incase i misunderstood what > the > > document says... > > it works in pg15, not in pg14. > see my test: https://dbfiddle.uk/wgfjCx7j >
Re: pg_restore mostly idle on restoring a large number of tables
Boris Sagadin writes: > restoring a 1.5TB database with about 800k tables on i3.4xlarge AWS > instace, PgSQL V12.15 on Ubuntu. > Running pg_restore with -j 16, I noticed the pg_restore is busy for an hour > or so with IO at 80%+ and then most of processes start idling and only a > few doing some work, disk IO at 1-2%, pg_stat_activity is mostly idle, same > goes for CPU, and this state proceeds for further 6 hours, disk space > increases very slowly. Yeah, this seems like a performance bug in pg_restore, per analysis at [1]. Until somebody gets around to doing something about that, I'd counsel not bothering with parallelized restore for this many tables. If the tables are mostly small, it'd likely be more useful to use --single-transaction mode; though you'd have to make sure that max_locks_per_transaction is set high enough to allow creating all those tables in one transaction. regards, tom lane [1] https://www.postgresql.org/message-id/3612876.1689443232%40sss.pgh.pa.us
Re: error in the example given for numeric data types
On Sat, Jul 15, 2023 at 10:50 AM Priyank Rajvansh < rajvansh.priy...@gmail.com> wrote: > > First of all thanks for your reply.This mean that this was a bug in the > previous versions right? > No, it means that a prior version limitation has been lifted, so a definition that was previously undefined is now defined. It is not a bug to choose to not implement something. David J.
Re: Toasted column values during replication
Using pgoutput - aws rds 13* version and seeing this behavior when using it with debezium 2.0.0 version. Best. On Friday, 30 June, 2023 at 12:53:20 pm GMT-7, Laurenz Albe wrote: On Fri, 2023-06-30 at 17:06 +, Chandy G wrote: > For instance : Can the postgres internal component (wal-sender / *) > interpret this special > toasted-col-value sitting in the wal-file -> replace it with the actual value > and send it to > the replication slot. Which logical decoding plugin are you using? "pgoutput", which is provided by PostgreSQL, will surely emit properly detoasted values. Yours, Laurenz Albe
Re: Rocky Linux 9 and postgres10
Thank you for your answer. On Sat, Jul 15, 2023, 14:02 Adrian Klaver wrote: > On 7/15/23 09:37, Daniel Gallo wrote: > > Good afternoon! I am writing to ask you the following question. > > Can postgres10 be installed on rocky linux 9? > > https://yum.postgresql.org/packages/#pg10 > > > PostgreSQL 10 > > This version is now End-Of-Life. Please upgrade to a supported version > as soon as possible. > > RHEL / Rocky Linux 9 - x86_64 > > ... > > > Although I know that it is a version that has already finished its life > > cycle, we are in migration processes > > We have a system with centos 7.5 and postgres 10 and the infrastructure > > group wants to do an installation with rocky linux 9 > > I have not found anywhere if it is supported > > Thank you very much for your time > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Re: Fwd: error in the example given for numeric data types
On 2023-07-15 12:08:26 +0530, Priyank Rajvansh wrote: > Page: https://www.postgresql.org/docs/15/datatype-numeric.html > Description: > > article 8.1.2 states the following: > > ''We use the following terms below: The precision of a numeric is the total > count of significant digits in the whole number, that is, the number of > digits to both sides of the decimal point. The scale of a numeric is the > count of decimal digits in the fractional part, to the right of the decimal > point. So the number 23.5141 has a precision of 6 and a scale of 4. Integers > can be considered to have a scale of zero.'' > > however it also states the following towards the end: > > '' For example, a column declared as > > NUMERIC(3, 5) > will round values to 5 decimal places and can store values between -0.00999 > and 0.00999, inclusive.'' > > Now from whatever i could decipher the syntax of the numeric data type is > NUMERIC(precision,scale) and if we write NUMERIC (3,5) it would mean that we > are trying to store a number which has 3 digits in total and 5 of them are > to the right of the decimal point, which doesn't make sense ! It may sound weird but it does make sense. There are three digits in the number and the rightmost of them is five positions to the right of the decimal pointis. So you can store 0.00999 --12345 but not 0.01000 --12345 as that would need a fourth digit and also not 0.000123 --123456 as not the rightmost digit is now six places right of the decimal point. Mathematically you store an integer with 3 digits and multiply it with 10^-5 to get the value. 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
