A simple question: Why 'pg_ctl: command not found...' ?
Hello everyone, When I start to learn the 'pg_ctl', the bash echo 'pg_ctl: command not found...' and then print 'Install package 'postgresql-server' to provide command 'pg_ctl'? [N/y]' I am confused because I had already install the PostgreSQL correctly just follow the postgres Document,to prove that,I use the psql to connect to the postgres and execute some sql statements (Just like 'CREATE TABLE', 'SELECT' and 'INSERT'),and these statements executed successfully. So how to solve this problem? Should I use the dnf to install the 'postgresql-server' or just add some path to the environment variable? My system is Fedora 35 and the PostgreSQL version is 14. Thanks in advance!
Re: A simple question: Why 'pg_ctl: command not found...' ?
On Tue, 2022-03-08 at 17:44 +0800, BeginnerC wrote: > When I start to learn the 'pg_ctl', > the bash echo 'pg_ctl: command not found...' and then print 'Install package > 'postgresql-server' to provide command 'pg_ctl'? [N/y]' > I am confused because I had already install the PostgreSQL correctly just > follow the postgres Document, > to prove that,I use the psql to connect to the postgres and execute some sql > statements > (Just like 'CREATE TABLE', 'SELECT' and 'INSERT'),and these statements > executed successfully. > So how to solve this problem? Should I use the dnf to install the > 'postgresql-server' or just add some path to the environment variable? > My system is Fedora 35 and the PostgreSQL version is 14. Ignore the overly helpful prompt to install PostgreSQL packages from your Linux distribution. Your only problem is that /usr/pgsql-14/bin is not in your PATH environment variable. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Serializable read only deferrable- implications
https://www.postgresql.org/docs/14/sql-set-transaction.html "The DEFERRABLE transaction property has no effect unless the transaction is also SERIALIZABLE and READ ONLY. When all three of these properties are selected for a transaction, the transaction may block when first acquiring its snapshot, after which it is able to run without the normal overhead of a SERIALIZABLE transaction and without any risk of contributing to or being canceled by a serialization failure. This mode is well suited for long-running reports or backups." Could anyone expound on the above? What are the real life differences between the two commands below? Would this be applicable at all to sessions on a physical replica? SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY; *Michael Lewis | Database Engineer* *Entrata*
Re: A simple question: Why 'pg_ctl: command not found...' ?
On 3/8/22 01:44, BeginnerC wrote: Hello everyone, When I start to learn the 'pg_ctl', the bash echo 'pg_ctl: command not found...' and then print 'Install package 'postgresql-server' to provide command 'pg_ctl'? [N/y]' I am confused because I had already install the PostgreSQL correctly just follow the postgres Document,to prove that,I use the psql to What exactly did you install and from where? What document section? connect to the postgres and execute some sql statements (Just like 'CREATE TABLE', 'SELECT' and 'INSERT'),and these statements executed successfully. So how to solve this problem? Should I use the dnf to install the 'postgresql-server' or just add some path to the environment variable? My system is Fedora 35 and the PostgreSQL version is 14. Thanks in advance! -- Adrian Klaver adrian.kla...@aklaver.com
Re: Serializable read only deferrable- implications
On Tue, 2022-03-08 at 06:29 -0700, Michael Lewis wrote: > "The DEFERRABLE transaction property has no effect unless the transaction is > also > SERIALIZABLE and READ ONLY. When all three of these properties are selected > for a transaction, > the transaction may block when first acquiring its snapshot, after which it > is able to run > without the normal overhead of a SERIALIZABLE transaction and without any > risk of contributing > to or being canceled by a serialization failure. This mode is well suited for > long-running reports or backups." > > > Could anyone expound on the above? What are the real life differences between > the two commands below? Would this be applicable at all to sessions on a > physical replica? > > SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE READ > ONLY DEFERRABLE; > > SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY; A transaction started with the first statement will not take any SI locks, nor will it ever receive a serialization error. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Serializable read only deferrable- implications
> > A transaction started with the first statement will not take any SI locks, > nor > will it ever receive a serialization error. > What is the meaning of SI? Anything you are aware of in source code or a blog post that discusses this?
Re: Serializable read only deferrable- implications
Michael Lewis writes: >> A transaction started with the first statement will not take any SI locks, >> nor will it ever receive a serialization error. > What is the meaning of SI? Anything you are aware of in source code or a > blog post that discusses this? There's src/backend/storage/lmgr/README-SSI, which might or might not be the level of detail you are looking for. regards, tom lane
Re: Serializable read only deferrable- implications
On 3/8/22 08:21, Tom Lane wrote: Michael Lewis writes: A transaction started with the first statement will not take any SI locks, nor will it ever receive a serialization error. What is the meaning of SI? Anything you are aware of in source code or a blog post that discusses this? There's src/backend/storage/lmgr/README-SSI, which might or might not be the level of detail you are looking for. If you want to ease into the above: https://www.postgresql.org/docs/current/mvcc-intro.html "PostgreSQL maintains this guarantee even when providing the strictest level of transaction isolation through the use of an innovative Serializable Snapshot Isolation (SSI) level." Then: https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE and https://www.postgresql.org/docs/current/applevel-consistency.html#SERIALIZABLE-CONSISTENCY regards, tom lane -- Adrian Klaver adrian.kla...@aklaver.com
Re: Serializable read only deferrable- implications
On Tue, Mar 8, 2022 at 9:27 AM Adrian Klaver wrote: > "PostgreSQL maintains this guarantee even when providing the strictest > level of transaction isolation through the use of an innovative > Serializable Snapshot Isolation (SSI) level." > > Then: > > > https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE > > and > > > https://www.postgresql.org/docs/current/applevel-consistency.html#SERIALIZABLE-CONSISTENCY Thanks to you both. If other concurrent sessions are using default isolation level of Read committed, would putting long running reports (read-only) into that read-only serializable deferrable mode be impactful at all? The documentation says that a transaction ID is only assigned to a connection once a write is done, but is the assignment or not of a txn id actually impactful on anything? I ask partly because it doesn't seem possible to reset that once assigned, through discard all; or something else like that which might be used by a connection pooler such as pg bouncer. is there any way to check if a session has "done writes/updates up to this point"? It seems pg_my_temp_schema() also returns the same value even after 'discard temp' or 'discard all' is executed. That was surprising to me, but would it be considered an issue by anyone?
Re: Serializable read only deferrable- implications
On 3/8/22 10:47 AM, Michael Lewis wrote: Thanks to you both. If other concurrent sessions are using default isolation level of Read committed, would putting long running reports (read-only) into that read-only serializable deferrable mode be impactful at all? The documentation says that a transaction ID is only assigned to a connection once a write is done, but is the assignment or not of a txn id actually impactful on anything? I ask partly because it doesn't seem possible to reset that once assigned, through discard all; or something else like that which might be used by a connection pooler such as pg bouncer. is there any way to check if a session has "done writes/updates up to this point"? It seems pg_my_temp_schema() also returns the same value even after 'discard temp' or 'discard all' is executed. That was surprising to me, but would it be considered an issue by anyone? I'm not following what you are asking or trying to achieve. For instance how pg_my_temp_schema() fits into this? You will need to provide a more complete description of what it is you are doing. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal
On Mon, Mar 07, 2022 at 09:54:22AM -0800, Andres Freund wrote: > > Initially select didn't break anything, but when I tuned down > > jit_above_cost so that it will kick in - got fails immediately. > Could you set jit_debugging_support=on and show a backtrace with that? Here you go: Program received signal SIGSEGV, Segmentation fault. 0xfffe7587b6c8 in deform_0_8 () (gdb) bt #0 0xfffe7587b6c8 in deform_0_8 () #1 0xfffe7587b5f4 in evalexpr_0_7 () #2 0xb67e6f7c in ExecEvalExprSwitchContext (isNull=0xf7956217, econtext=0xf6014608, state=0xf6014ab0) at ./build/../src/include/executor/executor.h:316 #3 ExecProject (projInfo=0xf6014aa8) at ./build/../src/include/executor/executor.h:350 #4 ExecScan (node=, accessMtd=0xb68051b8 , recheckMtd=0xb6805248 ) at ./build/../src/backend/executor/execScan.c:239 #5 0xb67ded5c in ExecProcNode (node=0xf60143b8) at ./build/../src/include/executor/executor.h:242 #6 ExecutePlan (execute_once=, dest=0xf5fc15e8, direction=, numberTuples=0, sendTuples=, operation=CMD_SELECT, use_parallel_mode=, planstate=0xf60143b8, estate=0xf5e01360) at ./build/../src/backend/executor/execMain.c:1632 #7 standard_ExecutorRun (queryDesc=0xf5ef4130, direction=, count=0, execute_once=) at ./build/../src/backend/executor/execMain.c:350 #8 0xa87751fc in pgss_ExecutorRun (queryDesc=0xf5ef4130, direction=ForwardScanDirection, count=0, execute_once=true) at ./build/../contrib/pg_stat_statements/pg_stat_statements.c:893 #9 0xa875f688 in explain_ExecutorRun (queryDesc=0xf5ef4130, direction=ForwardScanDirection, count=0, execute_once=true) at ./build/../contrib/auto_explain/auto_explain.c:320 #10 0xb69354c8 in PortalRunSelect (portal=portal@entry=0xf5e83ef0, forward=forward@entry=true, count=0, count@entry=9223372036854775807, dest=dest@entry=0xf5fc15e8) at ./build/../src/backend/tcop/pquery.c:938 #11 0xb6936a64 in PortalRun (portal=portal@entry=0xf5e83ef0, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true, run_once=run_once@entry=true, dest=dest@entry=0xf5fc15e8, altdest=altdest@entry=0xf5fc15e8, completionTag=completionTag@entry=0xf7956848 "") at ./build/../src/backend/tcop/pquery.c:779 #12 0xb6932a18 in exec_simple_query (query_string=query_string@entry=0xf5d9af90 "select p.proname, (SELECT rolname from pg_catalog.pg_roles where oid = p.proowner) from pg_proc p;") at ./build/../src/backend/tcop/postgres.c:1215 #13 0xb69336ac in PostgresMain (argc=, argv=argv@entry=0xf5df6f18, dbname=, username=) at ./build/../src/backend/tcop/postgres.c:4271 #14 0xb68ba768 in BackendRun (port=0xf5df0910, port=0xf5df0910) at ./build/../src/backend/postmaster/postmaster.c:4510 #15 BackendStartup (port=0xf5df0910) at ./build/../src/backend/postmaster/postmaster.c:4193 #16 ServerLoop () at ./build/../src/backend/postmaster/postmaster.c:1725 #17 0xb68bb74c in PostmasterMain (argc=, argv=) at ./build/../src/backend/postmaster/postmaster.c:1398 #18 0xb664c8a8 in main (argc=5, argv=0xf5d94ea0) at ./build/../src/backend/main/main.c:228 > > > FWIW, I went to the trouble of installing Ubuntu Focal on my > > > raspberry pi to see if I could duplicate this, and I couldn't. > > > However, what you get from a fresh install now is > > > $ dpkg -l | grep libllvm > > > ii libllvm10:arm641:10.0.0-4ubuntu1 > > > arm64Modular compiler and toolchain technologies, runtime library > > > not 9.0.1. I also found that Fedora 31/aarch64 is still downloadable, and > > > that does contain LLVM 9 ... and I could not reproduce it there either. > > > > Soo... plot thickens. > > > > Looks that pg 12 supplied by pgdg required libllvm9: > What are the libllvm package versions on which you can reproduce the crash? Hmm .. not sure I fully understand. Here are all packages that I think are relevant: root@c999-pg1302:~# dpkg -l | grep -iE 'postgresql-12|llvm|clang' ii libllvm9:arm64 1:9.0.1-12 arm64Modular compiler and toolchain technologies, runtime library ii postgresql-12 12.9-2.pgdg20.04+1 arm64The World's Most Advanced Open Source Relational Database ii postgresql-12-dbgsym 12.9-2.pgdg20.04+1 arm64debug symbols for postgresql-12 ii postgresql-12-pg-collkey 0.5.1-1insops1 arm64ICU collation function wrapper for PostgreSQL 12 ii postgresql-12-postgis-2.5 2.5.5+dfsg-1.pgdg20.04+2 arm64Geographic objects support for PostgreSQL 12 ii postgresql-12-postgis-2.5-scripts 2.5.5+dfsg-1.pgdg20.04+2 all Geographic objects support for PostgreSQL 12 -- SQL scripts ii postgresql-12-repack
Re: Serializable read only deferrable- implications
On Tue, Mar 8, 2022 at 11:47 AM Michael Lewis wrote: > > Thanks to you both. If other concurrent sessions are using default > isolation level of Read committed, would putting long running reports > (read-only) into that read-only serializable deferrable mode be impactful > at all? > I'm tending to agree that this documentation could be improved (I too am not totally fluent on how the different modes interact with each other). In this specific case the docs say: "When all three of these properties are selected for a transaction, the transaction may block when first acquiring its snapshot,..." They fail to say what they are blocking on and thus what has to occur in the system before the lock can be acquired and the transaction continues onward. I suspect it is simply that any not yet committed transactions become committed or reverted. Whether those transactions were run under serializable, read committed, or repeatable read would be immaterial. > The documentation says that a transaction ID is only assigned to a > connection once a write is done, but is the assignment or not of a txn id > actually impactful on anything? > I ask partly because it doesn't seem possible to reset that once assigned, > through discard all; or something else like that which might be used by a > connection pooler such as pg bouncer. is there any way to check if a > session has "done writes/updates up to this point"? It > seems pg_my_temp_schema() also returns the same value even after 'discard > temp' or 'discard all' is executed. That was surprising to me, but would it > be considered an issue by anyone? > I'm getting an impression that you have confusion regarding transactions and sessions, since all of this concurrency stuff only cares about the transactions but you now start talking about session state and comparing what you would see at initial login versus what you see after resetting. The temporary schema for a session is assigned once the first temporary object is created. At which point that assignment of the random temporary schema is fixed for the duration of the session. It never gets reset as there is no point. Whether a session has done "write/updates up to this point" is likewise not something that can be readily ascertained nor, from what I can tell, would doing so be a useful exercise. There may be some reason to inspect whether a transaction has performed a write/update, but even that seems unlikely. David J.
Postgres migration from 9.2 to 14
Hi guys! You may have seen my previous posts about Streaming Replication Lag issues and some questions regarding upgrading from PostgreSQL 9.2 to PostgreSQL 14. Well, I have completed the project a couple of weeks ago and have successfully migrated from PG 9.2 to PG 14. :) You can check it out here: PostgreSQL Live Migration from 9.2 to 14 with Bucardo (lpossamai.me) [1] My previous posts: [1] https://www.postgresql.org/message-id/Z14WgPUs_T-sranXV8i1KakN-eGRgP7fFcKUR8wHJDmjR-V8tHXkIIVWPxEEUd69GBeQCCKD0BeFn4f9AKItYa22mk_DzQP0yKuhRoyXZCc=@sud0.nz [2] https://www.postgresql.org/message-id/4fd63109744f5ef6eee098fc92c4fa87%40sud0.nz Cheers! Lucas Links: -- [1] https://lpossamai.me/2022-03-09-pg9_to_pg14/
