Re: Help. The database was created using collation version 2.17, but the operating system provides version 2.34.
Hello,
Thank you to all who responded.
There is a follow up question.
Our admin tried the following:
A host that wad been running postgresql11 was upgraded to Alma9 (from SL7) and
postgresql15. They then built postgresql11 on that host from sources.
Then they run pg_upgrade from 11 to 15. It worked and psql to db is not
accompanied by "collation version" warning.
This was unexpected to me based on my experience that I related on this thread.
Is this a legit procedure?
To remind, what did no work:
- upgrade to 15 on SL7 host, setup stream, replication to Alma9 host. psql
top replica
complains about "The database was created using collation ..."
Advice is appreciated
P.S.: where I can still find postgresql11 RPMs for Alma9? Buiding from sourcres
is OK, but a bit of a hassle.
From: Daniel Verite
Sent: Monday, June 24, 2024 3:48 AM
To: Dmitry O Litvintsev
Cc: pgsql-generallists.postgresql.org
Subject: Re: Help. The database was created using collation version 2.17, but
the operating system provides version 2.34.
[EXTERNAL] – This message is from an external sender
Dmitry O Litvintsev wrote:
> Just want to make clear (sorry I am slow on uptake). I should first
> REINDEX and then ALTER DATABASE xxx REFRESH COLLATION VERSION, or
> first ALTER and then REINDEX or does the order of these action
> matter at all?
The order does not matter. The ALTER DATABASE command will simply
update the pg_database.datcollversion field with the current version
of libc. That will stop the warning being issued, but it doesn't have
any other concrete effect.
Best regards,
--
Daniel Vérité
https://urldefense.proofpoint.com/v2/url?u=https-3A__postgresql.verite.pro_&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=F7VKeBFcE7ctVYy8fHvYvWPu4XkawA0hCuQOkYZk28e1uHpd_pb21GOrRMy9JB7a&s=M6qlhocjLWWgy8tVbTGTDEewC5JWHAfVztgV_XTx8Lg&e=
Twitter: @DanielVerite
Re: Help. The database was created using collation version 2.17, but the operating system provides version 2.34.
Thank you very much for help and pointers to useful information.
Just want to make clear (sorry I am slow on uptake). I should first REINDEX and
then ALTER DATABASE xxx REFRESH COLLATION VERSION, or first ALTER and then
REINDEX or does the order of these action matter at all?
Thank you,
Dmitry
From: Daniel Verite
Sent: Thursday, June 20, 2024 7:02 AM
To: Dmitry O Litvintsev
Cc: pgsql-generallists.postgresql.org
Subject: Re: Help. The database was created using collation version 2.17, but
the operating system provides version 2.34.
[EXTERNAL] – This message is from an external sender
Dmitry O Litvintsev wrote:
> WARNING: database "xxx" has a collation version mismatch
> DETAIL: The database was created using collation version 2.17, but the
> operating system provides version 2.34.
> HINT: Rebuild all objects in this database that use the default collation
> and run ALTER DATABASE xxx REFRESH COLLATION VERSION, or build PostgreSQL
> with the right library version.
This upgrade comprises the major change in GNU libc 2.28,
so indeed text indexes created by 2.17 are very likely unsafe to use
on your new server.
See
https://urldefense.proofpoint.com/v2/url?u=https-3A__wiki.postgresql.org_wiki_Locale-5Fdata-5Fchanges&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=NhYMQQwCXq5TXHFVjyJeB1eB9KkyHC8FU3lyswgVpUMpAzADq2cffu-xjSUeNCND&s=WKdD4hr8nBJTkQtIcLMagxuGK1yAPTyU2VOmQARksl8&e=
> REINDEX database xxx
> ALTER DATABASE xxx REFRESH COLLATION VERSION
> But this defeats the whole idea of having short downtime because REINDEX
> will take forever.
The indexes that don't involve collatable types (text,varchar), and those
that use the C collation don't need to be reindexed.
Maybe you can reduce significantly the downtime by including only
the ones that matter.
The wiki page gives the query to obtain the list of affected indexes:
SELECT DISTINCT indrelid::regclass::text, indexrelid::regclass::text,
collname, pg_get_indexdef(indexrelid)
FROM (SELECT indexrelid, indrelid, indcollation[i] coll FROM pg_index,
generate_subscripts(indcollation, 1) g(i)) s
JOIN pg_collation c ON coll=c.oid
WHERE collprovider IN ('d', 'c') AND collname NOT IN ('C', 'POSIX');
> I do not recall having similar issue when going from RH6 to RH7.
This warning was added relatively recently, in Postgres 15 (october 2022).
Best regards,
--
Daniel Vérité
https://urldefense.proofpoint.com/v2/url?u=https-3A__postgresql.verite.pro_&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=NhYMQQwCXq5TXHFVjyJeB1eB9KkyHC8FU3lyswgVpUMpAzADq2cffu-xjSUeNCND&s=yED6Nru4eGTULRzJymNtMgJjXhgirkjOuDzCQnae9Go&e=
Twitter: @DanielVerite
Help. The database was created using collation version 2.17, but the operating system provides version 2.34.
Hello, I am in the process of migrating DB to Alma9 host. The databse is rather large - few TBs. I have run pg_basebackup on Alma9 host and established replication from production to it. The idea is to quickly switch from master to this new host during downtime. Establishing replication went fine. Source postgresql version is 15.6, destination is 15.7 When I psql into replica I get: WARNING: database "xxx" has a collation version mismatch DETAIL: The database was created using collation version 2.17, but the operating system provides version 2.34. HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE xxx REFRESH COLLATION VERSION, or build PostgreSQL with the right library version. Looking up the issue the solution seems to be REINDEX database xxx ALTER DATABASE xxx REFRESH COLLATION VERSION But this defeats the whole idea of having short downtime because REINDEX will take forever. What is this "or build PostgreSQL with the right library version"? Is this about 15.7 vs 15.6 or is it about different glibc version between RH7 and Alma9? Is there a better way to handle it? I cannot afford long downtime. This came up rather unexpectedly and I am now in a tight situation having to find solution fast. I do not recall having similar issue when going from RH6 to RH7. Thank you for your help.
Re: fail to install postgresql15 on Alma9
dnf -qy module sisable postgresql -> "dnf -qy module disable postgresql". Proper syntax was used. :) ____ From: Dmitry O Litvintsev Sent: Tuesday, June 18, 2024 12:48 PM To: Adrian Klaver; pgsql-generallists.postgresql.org Subject: Re: fail to install postgresql15 on Alma9 [EXTERNAL] – This message is from an external sender Yes I did. Sorry did not mention that. I ran the dnf -qy module sisable postgresql prior to running. dnf install postgresql15-server (so that did not help) From: Adrian Klaver Sent: Tuesday, June 18, 2024 12:44 PM To: Dmitry O Litvintsev; pgsql-generallists.postgresql.org Subject: Re: fail to install postgresql15 on Alma9 [EXTERNAL] – This message is from an external sender On 6/18/24 10:40 AM, Dmitry O Litvintsev wrote: > Hello, > > I am foillowing instructions on > > https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_download_linux_redhat_&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=mN1kv7hRIfX0oHlFSSc1RH4ouENojXHa2_3698E0kc58mIkDJKb2bf8RjnP6ZWob&s=I2ltoVawe3G4fDFhBnctZ1KL0SOhQ6aSbVqMSepdekg&e= > > I select version "15" > I select "Red Hat Enterprise, Rocky, AlmaLinux or or Oracle 9" > > I get this command to run: > > "dnf install -y > https://urldefense.proofpoint.com/v2/url?u=https-3A__download.postgresql.org_pub_repos_yum_reporpms_EL-2D9-2Dx86-5F64_pgdg-2Dredhat-2Drepo-2Dlatest.noarch.rpm&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=mN1kv7hRIfX0oHlFSSc1RH4ouENojXHa2_3698E0kc58mIkDJKb2bf8RjnP6ZWob&s=pe18CD7xIVa4f2Npl1shD05CkZ2fTQSJQEWbhzzB8lI&e= > " > > But then I fail to install postgersql15: > > # yum install postgresql15-server > Last metadata expiration check: 0:22:07 ago on Tue 18 Jun 2024 12:16:52 PM > CDT. > No match for argument: postgresql15-server > Error: Unable to find a match: postgresql15-server > > I can install postgresql16-server sussessfully but not 15. > I need 15. What am I doing wrong? I don't use RH, still the instructions here: https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_download_linux_redhat_&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=mN1kv7hRIfX0oHlFSSc1RH4ouENojXHa2_3698E0kc58mIkDJKb2bf8RjnP6ZWob&s=I2ltoVawe3G4fDFhBnctZ1KL0SOhQ6aSbVqMSepdekg&e= Mention doing: # Disable the built-in PostgreSQL module: sudo dnf -qy module disable postgresql Did you do the above? Then: # Install PostgreSQL: sudo dnf install -y postgresql15-server > > Thank you, > Dmitry > > -- Adrian Klaver adrian.kla...@aklaver.com
Re: fail to install postgresql15 on Alma9
Yes I did. Sorry did not mention that. I ran the dnf -qy module sisable postgresql prior to running. dnf install postgresql15-server (so that did not help) From: Adrian Klaver Sent: Tuesday, June 18, 2024 12:44 PM To: Dmitry O Litvintsev; pgsql-generallists.postgresql.org Subject: Re: fail to install postgresql15 on Alma9 [EXTERNAL] – This message is from an external sender On 6/18/24 10:40 AM, Dmitry O Litvintsev wrote: > Hello, > > I am foillowing instructions on > > https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_download_linux_redhat_&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=mN1kv7hRIfX0oHlFSSc1RH4ouENojXHa2_3698E0kc58mIkDJKb2bf8RjnP6ZWob&s=I2ltoVawe3G4fDFhBnctZ1KL0SOhQ6aSbVqMSepdekg&e= > > I select version "15" > I select "Red Hat Enterprise, Rocky, AlmaLinux or or Oracle 9" > > I get this command to run: > > "dnf install -y > https://urldefense.proofpoint.com/v2/url?u=https-3A__download.postgresql.org_pub_repos_yum_reporpms_EL-2D9-2Dx86-5F64_pgdg-2Dredhat-2Drepo-2Dlatest.noarch.rpm&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=mN1kv7hRIfX0oHlFSSc1RH4ouENojXHa2_3698E0kc58mIkDJKb2bf8RjnP6ZWob&s=pe18CD7xIVa4f2Npl1shD05CkZ2fTQSJQEWbhzzB8lI&e= > " > > But then I fail to install postgersql15: > > # yum install postgresql15-server > Last metadata expiration check: 0:22:07 ago on Tue 18 Jun 2024 12:16:52 PM > CDT. > No match for argument: postgresql15-server > Error: Unable to find a match: postgresql15-server > > I can install postgresql16-server sussessfully but not 15. > I need 15. What am I doing wrong? I don't use RH, still the instructions here: https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_download_linux_redhat_&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=mN1kv7hRIfX0oHlFSSc1RH4ouENojXHa2_3698E0kc58mIkDJKb2bf8RjnP6ZWob&s=I2ltoVawe3G4fDFhBnctZ1KL0SOhQ6aSbVqMSepdekg&e= Mention doing: # Disable the built-in PostgreSQL module: sudo dnf -qy module disable postgresql Did you do the above? Then: # Install PostgreSQL: sudo dnf install -y postgresql15-server > > Thank you, > Dmitry > > -- Adrian Klaver adrian.kla...@aklaver.com
fail to install postgresql15 on Alma9
Hello, I am foillowing instructions on https://www.postgresql.org/download/linux/redhat/ I select version "15" I select "Red Hat Enterprise, Rocky, AlmaLinux or or Oracle 9" I get this command to run: "dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm"; But then I fail to install postgersql15: # yum install postgresql15-server Last metadata expiration check: 0:22:07 ago on Tue 18 Jun 2024 12:16:52 PM CDT. No match for argument: postgresql15-server Error: Unable to find a match: postgresql15-server I can install postgresql16-server sussessfully but not 15. I need 15. What am I doing wrong? Thank you, Dmitry
Re: Confusing error message in 15.6
Oops. This means major rewrite of my backup procedure. Thanks for pointing
this out. I will likely just switch to pg_basebackup. What I have is something
old and gnarly from the days of psql version 8.
From: David G. Johnston
Sent: Tuesday, May 21, 2024 6:42 PM
To: Dmitry O Litvintsev
Cc: pgsql-generallists.postgresql.org
Subject: Re: Confusing error message in 15.6
[EXTERNAL] – This message is from an external sender
On Tue, May 21, 2024, 17:29 Dmitry O Litvintsev
mailto:litvi...@fnal.gov>> wrote:
Hi,
I am observing the following error which confuses me:
# psql -U postgres template1 -c "checkpoint; select
pg_backup_start('${dest}.tar.Z', true)"
CHECKPOINT
pg_backup_start
-
17BF7/3009498
(1 row)
# psql -U postgres template1 -c "select pg_backup_stop(true)"
ERROR: backup is not in progress
HINT: Did you call pg_backup_start()?
This is postgresql 15.6 running on RH 7.
This is not just amusing, it is breaking my backup script after update from 11
to 15 (and change from pg_{start,stop}_backup to pg_backup_{start_stop})
Yep, nowadays you must keep the transaction where you issued backup start open
until you issue backup end. Using -c isn't going to cut it.
David J.
Confusing error message in 15.6
Hi,
I am observing the following error which confuses me:
# psql -U postgres template1 -c "checkpoint; select
pg_backup_start('${dest}.tar.Z', true)"
CHECKPOINT
pg_backup_start
-
17BF7/3009498
(1 row)
# psql -U postgres template1 -c "select pg_backup_stop(true)"
ERROR: backup is not in progress
HINT: Did you call pg_backup_start()?
This is postgresql 15.6 running on RH 7.
This is not just amusing, it is breaking my backup script after update from 11
to 15 (and change from pg_{start,stop}_backup to pg_backup_{start_stop})
Thanks,
Dmitry
Re: Seeing new stuff in log after upgrading from 11 to 15
Thanks for the quick reply! NP, I was just being extra cautious after upgrade. From: Tom Lane Sent: Wednesday, May 15, 2024 6:50 PM To: Dmitry O Litvintsev Cc: pgsql-general@lists.postgresql.org Subject: Re: Seeing new stuff in log after upgrading from 11 to 15 [EXTERNAL] – This message is from an external sender Dmitry O Litvintsev writes: > After upgrade 11 -> 15 started to see these messages in hot standby log: > < 2024-05-15 17:20:24.164 CDT 151879 > LOG: restartpoint complete: wrote > 296338 buffers (28.3%); 0 WAL file(s) added, 134 removed, 0 recycled; > write=1619.469 s, sync=0.022 s, total=1619.539 s; sync files=1492, > longest=0.002 s, average=0.001 s; distance=2195446 kB, estimate=4171347 kB > < 2024-05-15 17:20:24.164 CDT 151879 > LOG: recovery restart point at > 17AB8/47000140 > < 2024-05-15 17:20:24.164 CDT 151879 > DETAIL: Last completed transaction > was at log time 2024-05-15 17:20:24.138362-05. > < 2024-05-15 17:20:24.167 CDT 151879 > LOG: restartpoint starting: wal > while running 11 the log was almost empty, and I did not modify verbosity > settings... > Queston : are above harmless and just informational? This is checkpoint logging, which is on by default now (a decision I didn't particularly approve of). Feel free to set "log_checkpoints = off" if you don't want it. regards, tom lane
Seeing new stuff in log after upgrading from 11 to 15
Hi, After upgrade 11 -> 15 started to see these messages in hot standby log: < 2024-05-15 17:20:24.164 CDT 151879 > LOG: restartpoint complete: wrote 296338 buffers (28.3%); 0 WAL file(s) added, 134 removed, 0 recycled; write=1619.469 s, sync=0.022 s, total=1619.539 s; sync files=1492, longest=0.002 s, average=0.001 s; distance=2195446 kB, estimate=4171347 kB < 2024-05-15 17:20:24.164 CDT 151879 > LOG: recovery restart point at 17AB8/47000140 < 2024-05-15 17:20:24.164 CDT 151879 > DETAIL: Last completed transaction was at log time 2024-05-15 17:20:24.138362-05. < 2024-05-15 17:20:24.167 CDT 151879 > LOG: restartpoint starting: wal while running 11 the log was almost empty, and I did not modify verbosity settings... Queston : are above harmless and just informational? I checked the data and the data is definitely being replicated. Thank you, Dmitry
how to check that recovery is complete
Hi, I have a workflow where I recover from PITR backup and run a query on it. The program that runs query checks that it can connect to database in a loop, until it can, and then runs the query. This has worked fine far. Recently I upgraded to 11 and I see that I can connect to DB while recovery is not complete yet. See this: < 2020-11-05 03:34:36.114 CST >LOG: starting archive recovery < 2020-11-05 03:34:36.590 CST >LOG: restored log file "000102EF00F9" from archive < 2020-11-05 03:34:36.641 CST >LOG: redo starts at 2EF/F928 ... < 2020-11-05 03:34:46.392 CST >LOG: restored log file "000102F8" from archive < 2020-11-05 03:34:46.658 CST 127.0.0.1 >FATAL: the database system is starting up < 2020-11-05 03:34:47.028 CST >LOG: restored log file "000102F9" from archive You can see above fail to connect , but sometime into recover I see; < 2020-11-05 04:07:51.987 CST >LOG: restored log file "000102F20029" from archive < 2020-11-05 04:08:23.195 CST 127.0.0.1 >ERROR: canceling statement due to conflict with recovery < 2020-11-05 04:08:23.195 CST 127.0.0.1 >DETAIL: User query might have needed to see row versions that must be removed. < 2020-11-05 04:08:23.195 CST 127.0.0.1 >STATEMENT: select count(*) from file < 2020-11-05 04:08:23.195 CST >FATAL: terminating connection due to administrator command < 2020-11-05 04:08:23.195 CST >STATEMENT: select count(*) from file < 2020-11-05 04:08:23.195 CST >FATAL: terminating connection due to administrator command < 2020-11-05 04:08:23.195 CST >STATEMENT: select count(*) from file < 2020-11-05 04:08:23.232 CST >LOG: background worker "parallel worker" (PID 13577) exited with exit code 1 < 2020-11-05 04:08:23.244 CST >LOG: background worker "parallel worker" (PID 13578) exited with exit code 1 < 2020-11-05 04:08:23.244 CST 127.0.0.1 >FATAL: terminating connection due to conflict with recovery < 2020-11-05 04:08:23.244 CST 127.0.0.1 >DETAIL: User query might have needed to see row versions that must be removed. < 2020-11-05 04:08:23.244 CST 127.0.0.1 >HINT: In a moment you should be able to reconnect to the database and repeat your command. < 2020-11-05 04:08:25.354 CST >LOG: restored log file "000102F2002A" from archive < 2020-11-05 04:08:55.555 CST 127.0.0.1 >ERROR: canceling statement due to conflict with recovery < 2020-11-05 04:08:55.555 CST 127.0.0.1 >DETAIL: User query might have needed to see row versions that must be removed. < 2020-11-05 04:08:55.555 CST 127.0.0.1 >STATEMENT: select count(*) from file < 2020-11-05 04:08:55.556 CST >FATAL: terminating connection due to administrator command < 2020-11-05 04:08:55.556 CST >STATEMENT: select count(*) from file < 2020-11-05 04:08:55.561 CST >FATAL: terminating connection due to administrator command < 2020-11-05 04:08:55.561 CST >STATEMENT: select count(*) from file < 2020-11-05 04:08:55.640 CST >LOG: background worker "parallel worker" (PID 13683) exited with exit code 1 < 2020-11-05 04:08:55.653 CST >LOG: background worker "parallel worker" (PID 13684) exited with exit code 1 < 2020-11-05 04:08:55.653 CST 127.0.0.1 >FATAL: terminating connection due to conflict with recovery < 2020-11-05 04:08:55.653 CST 127.0.0.1 >DETAIL: User query might have needed to see row versions that must be removed. < 2020-11-05 04:08:55.653 CST 127.0.0.1 >HINT: In a moment you should be able to reconnect to the database and repeat your command. < 2020-11-05 04:09:00.307 CST >LOG: restored log file "000102F2002B" from archive As you can see a query "select count(*) from file" failed due to table not being restored yet. BUT connection was allowed before DB was ready Only few hours after ; < 2020-11-05 09:31:30.319 CST >LOG: archive recovery complete < 2020-11-05 09:34:51.729 CST >LOG: database system is ready to accept connections After which the query runs fine without errors. This is bad because I see that select count(*) takes progressively longer to execute and the count(*) is not what I am interested in. I run the "real" query after that. As a result I add hours to program execution time. Is there a more robust method to it? Ideally I do not want to be able to connect to db until : < 2020-11-05 09:31:30.319 CST >LOG: archive recovery complete < 2020-11-05 09:34:51.729 CST >LOG: database system is ready to accept connections And I believe this was the behavior before upgrade. If connection can't be disabled, how can I detect condition "database system is ready to accept connections" I believe "pg_isready" utility would succeed once it can connect. And as can see I could connect way before DB is really ready. Thanks! Dmitry
ownership of "/var/run/postgresql"
Hi, we run 9.6 postgresql DB on Linux box. We run as different user than postgres. To get it to work we had to chown /var/run/postgresql to be owned by the process user. Upgraded to 11 and now we see that file /var/run/postgresql changes ownership to postgres:postgres on reboot , even though postgresql-11.service is disabled. What is doing it and is it possible to disable it? Thanks, Dmitry
Re: postgresql11-devel RPM is missing from "Direct RPM downloads"
D'oh. Thanks! /--\ | Tel: (630) 840 5005| | FAX: (630) 840 2968| |(630) 840 2783| | office:FCC 240 | | E-mail:litvi...@fnal.gov | \--/ On Wed, 5 Jun 2019, Jakub Olczyk wrote: > Hi Dimiry, > > On 04/06/2019 23:49, Dmitry O Litvintsev wrote: >> Where can I find postgresq11-devel RPM ? >> [...] >> https://urldefense.proofpoint.com/v2/url?u=https-3A__yum.postgresql.org_11_redhat_rhel-2D7-2Dx86-5F64_repoview_&d=DwICaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=x0FsGjpFkd074Owr0TbLitPaA8Y3iFdjJIgsUZQKx4Q&s=64-qFF0vgct9WdlN14qDUr3Dx58YQv0sWwCFLg2D8M4&e= > > have you looked under the letter "P" in your first link? > There you can find the -devel package. > > https://urldefense.proofpoint.com/v2/url?u=https-3A__yum.postgresql.org_11_redhat_rhel-2D7-2Dx86-5F64_repoview_letter-5Fp.group.html&d=DwICaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=x0FsGjpFkd074Owr0TbLitPaA8Y3iFdjJIgsUZQKx4Q&s=9BjL2HUlWfddhgpV1vnwDsRjRXNO4d1DNN0USk3J1uE&e= > > Cheers! > Jakub > >
postgresql11-devel RPM is missing from "Direct RPM downloads"
Hi, Where can I find postgresq11-devel RPM ? It is missing from direct RPM download page : https://yum.postgresql.org/11/redhat/rhel-7-x86_64/repoview/ or https://yum.postgresql.org/11/redhat/rhel-7-x86_64/repoview/postgresqldbserver11.group.html Thanks, Dmitry
something weird happened - can select by column value although column value exist
Hi,
Today the following happened:
Found this error in my production log:
< 2018-10-11 13:31:52.587 CDT >ERROR: insert or update on table "file"
violates foreign key constraint "$1"
< 2018-10-11 13:31:52.587 CDT >DETAIL: Key (volume)=(155303) is not present in
table "volume".
< 2018-10-11 13:31:52.587 CDT >STATEMENT:
INSERT INTO file
(sanity_crc,uid,pnfs_id,crc,deleted,cache_mod_time,drive,volume,sanity_size,cache_status,gid,location_cookie,cache_location,original_library,bfid,pnfs_path,size)
VALUES (
4002229874,0,'',256655919,'u','2018-10-11 13:31:52','',(SELECT
id FROM volume where label='A'),65536,'B',0,'C','D','E','F','',197243)
RETURNING *
file table references volume table on file.volume = volume.id and file.volume
is FK to volume.id. I doctored the query for privacy replacing string values
with 'A', 'B'. ...
(Queries similar to the above quoted are executed by an application and run
thousand of times every day for years)
So, the problem:
SELECT id FROM volume where label='A';
id
155303
(1 row)
BUT:
select * from volume where id = 155303;
...
(0 rows)
?!
id is a sequence:
id| integer | not null default
nextval(('volume_seq'::text)::regclass)
This entry id = 155303 has existed for some time and has a lot of existing file
entries holding
FK reference to volume id = 155303
I "fixed" the issue just by:
update volume set id = 155303 where label='A';
BUT It did not work right away. Meaning I did this once:
update volume set id = 155303 where label='A';
no effect.
I did it again, I also did it;
update volume set id = (select id from volume where label='A');
and then again
update volume set id = 155303 where label='A';
eventually it worked. Now,
select count(*) from volume where label='A';
count
---
1
(1 row)
What is this? Version 9.3.9,. running on Linux RH6.
Thanks,
Dmitry
