pg 13 fatal error message mentionning "standby_mode"
Hi, I had forgotten to include a restore_command and my standby database did not start (expected behavior) but the error message puzzled me : select version(); version --- PostgreSQL 13.2 (Debian 13.2-1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit (1 ligne) juin 08 11:50:26 sv-t-vtl-bas06 postgresql@13-echange_p[2921]: 2021-06-08 11:50:26.666 CEST [2927] FATAL: doit spécifier une restore_command quand standby_mode n'est pas activé It's in French but it means I have to specify restore_command if standby_mode is not activated. It's a pg 13 cluster and standby_mode parameter was removed with pg 12+ ? Perhaps the French translation is wrong ? Best regards, Phil
Re: pg 13 fatal error message mentionning "standby_mode"
Hi, Le mar. 8 juin 2021 à 12:11, Phil Florent a écrit : > Hi, > > I had forgotten to include a restore_command and my standby database did > not start (expected behavior) but the error message puzzled me : > > select version(); > > version > > > --- > > PostgreSQL 13.2 (Debian 13.2-1) on x86_64-pc-linux-gnu, compiled by gcc > (Debian 10.2.1-6) 10.2.1 20210110, 64-bit > > (1 ligne) > > > > juin 08 11:50:26 sv-t-vtl-bas06 postgresql@13-echange_p[2921]: 2021-06-08 > 11:50:26.666 CEST [2927] *FATAL: doit spécifier une restore_command > quand standby_mode n'est pas activé* > > It's in French but it means I have to specify restore_command if > standby_mode is not activated. > > It's a pg 13 cluster and standby_mode parameter was removed with pg 12+ ? > Perhaps the French translation is wrong ? > > The translation is wrong. The original message is "must specify restore_command when standby mode is not enabled". There's no underscore between standby and mode, so it isn't the parameter. I must have missed that change during the translation. I'll fix it right away. Thanks. Regards. -- Guillaume.
Re: pg 13 fatal error message mentionning "standby_mode"
Le mar. 8 juin 2021 à 13:15, Guillaume Lelarge a écrit : > Hi, > > Le mar. 8 juin 2021 à 12:11, Phil Florent a > écrit : > >> Hi, >> >> I had forgotten to include a restore_command and my standby database did >> not start (expected behavior) but the error message puzzled me : >> >> select version(); >> >> version >> >> >> --- >> >> PostgreSQL 13.2 (Debian 13.2-1) on x86_64-pc-linux-gnu, compiled by gcc >> (Debian 10.2.1-6) 10.2.1 20210110, 64-bit >> >> (1 ligne) >> >> >> >> juin 08 11:50:26 sv-t-vtl-bas06 postgresql@13-echange_p[2921]: >> 2021-06-08 11:50:26.666 CEST [2927] *FATAL: doit spécifier une >> restore_command quand standby_mode n'est pas activé* >> >> It's in French but it means I have to specify restore_command if >> standby_mode is not activated. >> >> It's a pg 13 cluster and standby_mode parameter was removed with pg 12+ ? >> Perhaps the French translation is wrong ? >> >> > The translation is wrong. The original message is "must specify > restore_command when standby mode is not enabled". There's no underscore > between standby and mode, so it isn't the parameter. I must have missed > that change during the translation. I'll fix it right away. > > Done. -- Guillaume.
RE: pg 13 fatal error message mentionning "standby_mode"
Thanks Guillaume. After this change the error message will even be clearer in French than in the original version in fact. Regards, Phil De : Guillaume Lelarge Envoyé : mardi 8 juin 2021 13:21 À : Phil Florent Cc : pgsql-gene...@postgresql.org Objet : Re: pg 13 fatal error message mentionning "standby_mode" Le mar. 8 juin 2021 à 13:15, Guillaume Lelarge mailto:guilla...@lelarge.info>> a écrit : Hi, Le mar. 8 juin 2021 à 12:11, Phil Florent mailto:philflor...@hotmail.com>> a écrit : Hi, I had forgotten to include a restore_command and my standby database did not start (expected behavior) but the error message puzzled me : select version(); version --- PostgreSQL 13.2 (Debian 13.2-1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit (1 ligne) juin 08 11:50:26 sv-t-vtl-bas06 postgresql@13-echange_p[2921]: 2021-06-08 11:50:26.666 CEST [2927] FATAL: doit spécifier une restore_command quand standby_mode n'est pas activé It's in French but it means I have to specify restore_command if standby_mode is not activated. It's a pg 13 cluster and standby_mode parameter was removed with pg 12+ ? Perhaps the French translation is wrong ? The translation is wrong. The original message is "must specify restore_command when standby mode is not enabled". There's no underscore between standby and mode, so it isn't the parameter. I must have missed that change during the translation. I'll fix it right away. Done. -- Guillaume.
Re: Database issues when adding GUI
On Mon, 7 Jun 2021, Adrian Klaver wrote: 2021-06-07 10:36:38.330 PDT [29537] LOG: database system is ready to accept connections 2021-06-07 14:19:45.491 PDT [31353] ERROR: column pg_attrdef.adsrc does not exist at character 128 " Adrian, Postgres dropped the pg_attrdev.adsrc column from the system table in version 12.0. As you wrote yesterday, Qt5 added support for postgres-12 in the QPSQL driver for 5.15.0. I'm now working on building Qt5-5.15.2 on my slackware-14.2 host. While QSqlDatabase is ready to accept connections it cannot recognize tables using the installed version. Regards, Rich
Re: Aw: Re: Database issues when adding GUI
On Tue, 8 Jun 2021, Karsten Hilbert wrote: The problem source is postgres telling me it cannot connect to the database via TCP/IP but I can do so directly using psql: via UNIX domain sockets. Karsten, That's because psql is direct while PyQt5-5.12.3 cannot access postgres-12.x tables. I'm working on getting Qt5-5.15.2 built. Regards, Rich
Re: index unique
quite funny to see how a tech question seems to end into an english grammar thing :-) quote > You make this sound like an either-or proposition, While he is talking about *a* primary key, it should be *the* primary key. There can be only one (that's why it is the primary key). There can be several unique keys, though. > but personally it takes a very exceptional circumstance to forgo > defining a unique natural key. True, but not what he's talking about. end quote when I did write the question, I write: "on a table we need a primary key" its quite obvious that there is only one PK, but, at that stage, I dont need one for functional reasons so, I need "a" primary key, whatever it is, just because postgres logical replication needs one to accept updates. then, to build "the" needed PK, we need to decide which columns or set of will be appropriate as it has to be able to identify something unique. and this is how we get to this list of 3 columns. So, ok, this set doesn't work asis. all solutions we thought of are just tech workaround, providing no functionnal meaning. still, its somehow surprising: (at least to me !) postgres has no problem creating a btree for a geometry column, and, so, no problem for putting a geom column in a PK. (very different from Oracle...) the only pb observed is the size of the object accepted. if the geom is a bit "big" then the index errors.about btree size of index object. but if I create a table test_l with a text column blabla as a PK, and insert a 100 000 character long string , no pb. if I do an explain analyze select blabla from test_l order by blabla, postgres tells me that it did an index only scan. so, I wonder why Postgres is able to put a 100 000 long text in a btree index and NOT a geom column which wkt is 10 000 bytes long. there, sure, is a reason. please, let me know :-) Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, Jun 8, 2021 at 12:09 AM Peter J. Holzer wrote: > On 2021-06-07 10:20:22 -0700, David G. Johnston wrote: > > On Sun, Jun 6, 2021 at 11:55 PM Peter J. Holzer > wrote: > > On 2021-06-03 22:51:55 +0200, Marc Millas wrote: > > > postgres 12 with postgis. > > > on a table we need a primary key and to get a unique combinaison, > we need > > > 3 columns of that table: > > > 1 of type integer, > > > 1 of type text, > > > 1 of type geometry > > > > > > creating the PK constraint doesn work: > > > > I find that if a natural primary key candidate is so complex, it is > > usually better to use a surrogate key. > > > > > > You make this sound like an either-or proposition, > > While he is talking about *a* primary key, it should be *the* primary > key. There can be only one (that's why it is the primary key). > > There can be several unique keys, though. > > > but personally it takes a very exceptional circumstance to forgo > > defining a unique natural key. > > True, but not what he's talking about. > > hp > > -- >_ | Peter J. Holzer| Story must make more sense than reality. > |_|_) || > | | | h...@hjp.at |-- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!" >
Re: index unique
On 2021-06-08 18:30:16 +0200, Marc Millas wrote:
> the only pb observed is the size of the object accepted. if the geom is a bit
> "big" then the index errors.about btree size of index object.
> but if I create a table test_l with a text column blabla as a PK, and insert a
> 100 000 character long string , no pb.
> if I do an explain analyze select blabla from test_l order by blabla, postgres
> tells me that it did an index only scan.
> so, I wonder why Postgres is able to put a 100 000 long text in a btree index
> and NOT a geom column which wkt is 10 000 bytes long.
Good question. Maybe your texts compresses better than your geometries:
hjp=> insert into t(t) values(repeat('a', 235327) || '1');
INSERT 0 1
Time: 60.057 ms
hjp=> insert into t(t) values(repeat('a', 235328) || '1');
ERROR: index row size 2720 exceeds maximum 2712 for index "t_pkey1"
HINT: Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text
indexing.
Time: 58.751 ms
Note the difference between the length of the string I was trying to
insert and the length of the row it complains about.
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
Re: index unique
Cristal clear !
and it have to be the case as my test was done with some not so random data.
but this mean that we cannot put a bunch of datatypes in a PK, as soon as
it may be longer than 2701.
I know, its clearly stated in postgres doc about btree," The only
limitation is that an index entry cannot exceed approximately one-third of
a page (after TOAST compression, if applicable)."
but as this looks quite hard coded, it means that for long utf8 things the
data length is not so long before hitting the limit.
is there any plan to adress this ?
kind of index toast ?? :-)
thanks
Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com
On Tue, Jun 8, 2021 at 8:42 PM Peter J. Holzer wrote:
> On 2021-06-08 18:30:16 +0200, Marc Millas wrote:
> > the only pb observed is the size of the object accepted. if the geom is
> a bit
> > "big" then the index errors.about btree size of index object.
> > but if I create a table test_l with a text column blabla as a PK, and
> insert a
> > 100 000 character long string , no pb.
> > if I do an explain analyze select blabla from test_l order by blabla,
> postgres
> > tells me that it did an index only scan.
> > so, I wonder why Postgres is able to put a 100 000 long text in a btree
> index
> > and NOT a geom column which wkt is 10 000 bytes long.
>
> Good question. Maybe your texts compresses better than your geometries:
>
> hjp=> insert into t(t) values(repeat('a', 235327) || '1');
> INSERT 0 1
> Time: 60.057 ms
> hjp=> insert into t(t) values(repeat('a', 235328) || '1');
> ERROR: index row size 2720 exceeds maximum 2712 for index "t_pkey1"
> HINT: Values larger than 1/3 of a buffer page cannot be indexed.
> Consider a function index of an MD5 hash of the value, or use full text
> indexing.
> Time: 58.751 ms
>
> Note the difference between the length of the string I was trying to
> insert and the length of the row it complains about.
>
> hp
>
> --
>_ | Peter J. Holzer| Story must make more sense than reality.
> |_|_) ||
> | | | h...@hjp.at |-- Charles Stross, "Creative writing
> __/ | http://www.hjp.at/ | challenge!"
>
Re: index unique
On Tuesday, June 8, 2021, Marc Millas wrote: > > but as this looks quite hard coded, it means that for long utf8 things > the data length is not so long before hitting the limit. > > is there any plan to adress this ? > None that I’ve seen, and I don’t expect to see one either. Mainly because I’ve yet to see or think of a use case that would warrant even considering doing such, ignoring the fact that actually changing such a value is likely to be a non-starter from a compatability perspective. David J.
Re: index unique
Marc Millas writes: > I know, its clearly stated in postgres doc about btree," The only > limitation is that an index entry cannot exceed approximately one-third of > a page (after TOAST compression, if applicable)." Yup. > is there any plan to adress this ? No. The cost/benefit ratio seems completely untenable. The usual recommendation is that you shorten long values by hashing them, eg create a unique index on md5(my_long_column). regards, tom lane
Re: index unique
Marc Millas schrieb am 03.06.2021 um 22:51: on a table we need a primary key and to get a unique combinaison, we need 3 columns of that table: 1 of type integer, 1 of type text, 1 of type geometry creating the PK constraint doesn work: (even with our current small data set) ERROR: index row size 6072 exceeds btree version 4 maximum 2704 for index "xxx_spkey" DETAIL: Index row references tuple (32,1) in relation "xxx". HINT: Values larger than 1/3 of a buffer page cannot be indexed. Consider a function index of an MD5 hash of the value, or use full text indexing. ok. we can do this. but if so, we need to create a gist index on the geometry column to do any topology request. so 2 indexes containing this single column. if we install extension btree_gist, no pb to create an index on all 3 columns. but as gist does not support unicity, this index cannot be used for the PK. OK, we may try to use a function to get the bounding box around the geometry objects and use the result into a btree index Any idea (I mean: another idea !) to tackle this ? Or any critic on the "solution" ?? How do you define the "uniqueness" of the geometry? GIST can support "uniqueness" through exclusion constraints. It's not a primary key, so you can't create foreign keys referencing that table, but it does ensure uniqueness (In fact the "normal" unique indexes are essentially a special case of exclusion constraints) create index on the_table using gist (int_column with =, text_col with =, geometry_col with &&); Replace the && operator with whatever is appropriate for your use case. Thomas
Re: bottom / top posting
On Mon, Jun 7, 2021 at 07:53:30PM +0200, Francisco Olarte wrote: > message like yours in 5-10 seconds, properly scanning a top posted one > takes much longer. So normally I stop reading at the quote and discard > the message if I have not understood with what I have read UNLESS it's > from my mother or I'm been paid to read it. > > I find top-posting moderately offensive, like saying "I am not going > to waste time to make your reading experience better". Yes, that is basically it, and if you are only communicating with one other person, maybe that is reasonable. However, since our emails are read by thousands, we have a responsibility to make them as clear as possible, and that includes trimming and quoting relevant parts of messages. However, as much as I try, I still regularly have typos in my emails. :-( And you are right in your earlier email in saying that "don't top-post" or "bottom-post" is not clear enough in explaining how to properly "trim-post", as you stated. Some people trim-post automatically, while others have never thought about it since their communication is almost always informal. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
