RE: Streaming wal from primiry terminating

[Lahnov, Igor]

Do you need any additional information?

-Original Message-
From: Lahnov, Igor 
Sent: Friday, September 30, 2022 10:18 AM
To: 'Michael Paquier' 
Cc: Kyotaro Horiguchi ; 
pgsql-general@lists.postgresql.org; Timonin, Alexey 
; Dmitriyev, Andrey 
Subject: RE: Streaming wal from primiry terminating

>> Which version of PostgreSQL are you using?  There has been a few commits 
>> around continuation records lately, so it is hard to say if you are pointing 
>> at a new issue or if this is a past one already addressed.

Hi Michael,
We are using PostgreSQL 14.4.

Re: Postgres calendar?

[Peter Eisentraut]

On 04.10.22 23:02, Bruce Momjian wrote:

Would people be interesting in subscribing to a Postgres calendar that
includes dates for minor releases, final minor release dates for major
versions, commit fests, and even Postgres events?


Events already exists: 
https://calendar.google.com/calendar/ical/57a0a03a4925700a1bdfeef20cbb341330aa3433ebe678e9d5cd34f605a2fd92%40group.calendar.google.com/public/basic.ics

Number of updated rows with LibPQ

[Dominique Devienne]

Hi,

Is there a way to programmatically now how many rows an UPDATE did update?
I've read about [PQcmdTuples][1], but surely I shouldn't have to parse
that string, no?
For selects, I have [PQnTuples][2], but what to do on INSERT, UPDATE, DELETE?
Parse the result of PQcmdTuples myself??? If so, what's the 0 in the
INSERT below?
Is the output of PQcmdTuples "stable", i.e. "official"? Not locale dependent?

Thanks, --DD

[1]: https://www.postgresql.org/docs/14/libpq-exec.html#LIBPQ-PQCMDTUPLES
[2]: https://www.postgresql.org/docs/14/libpq-exec.html#LIBPQ-PQNTUPLES

postgres=# create table foo (v int);
CREATE TABLE
postgres=# insert into foo values (1), (2), (3);
INSERT 0 3
postgres=# update foo set v = 2*v where v = 2;
UPDATE 1
postgres=# delete from foo where v = 3;
DELETE 1
postgres=# select v from foo where v > 1;
...
(1 row)
postgres=# delete from foo;
DELETE 2
postgres=# drop table foo;
DROP TABLE
postgres=#

Re: Number of updated rows with LibPQ

[Laurenz Albe]

On Wed, 2022-10-05 at 16:38 +0200, Dominique Devienne wrote:
> Hi,
> 
> Is there a way to programmatically now how many rows an UPDATE did update?
> I've read about [PQcmdTuples][1], but surely I shouldn't have to parse
> that string, no?
> For selects, I have [PQnTuples][2], but what to do on INSERT, UPDATE, DELETE?
> Parse the result of PQcmdTuples myself??? If so, what's the 0 in the
> INSERT below?
> Is the output of PQcmdTuples "stable", i.e. "official"? Not locale dependent?
> 
> Thanks, --DD
> 
> [1]: https://www.postgresql.org/docs/14/libpq-exec.html#LIBPQ-PQCMDTUPLES
> [2]: https://www.postgresql.org/docs/14/libpq-exec.html#LIBPQ-PQNTUPLES
> 
> postgres=# create table foo (v int);
> CREATE TABLE
> postgres=# insert into foo values (1), (2), (3);
> INSERT 0 3
> postgres=# update foo set v = 2*v where v = 2;
> UPDATE 1
> postgres=# delete from foo where v = 3;
> DELETE 1
> postgres=# select v from foo where v > 1;
> ...
> (1 row)
> postgres=# delete from foo;
> DELETE 2
> postgres=# drop table foo;
> DROP TABLE
> postgres=#

Yes, you have to use PQcmdTuples(), and you have to convert the string to an 
integer.

But don't worry: the result will *not* be "INSERT 0 5", it will be just "5", so
you won't have to parse anything.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Replication between Master PostgreSQL database version 9.6.1 and Standby/Slave PostgreSQL database version 10.17.

[Kaushal Shriyan]

Hi,

Is replication possible between PostgreSQL support Master database
running 9.6.1 version which is a lower version to Standby/Slave running
version 10.17?

I have a datacenter *DC1* which is a production environment and serves live
customer traffic hosting Master PostgreSQL database version *9.6.1* and
Standby/Slave PostgreSQL database version *9.6.1* for storing analytics
data. I am adding the new datacenter *DC2* by referring to
https://docs.apigee.com/private-cloud/v4.51.00/adding-data-center?hl=en
which will install *another additional* *Standby/Slave PostgreSQL database
version* *10.17*.

More details :- https://docs.apigee.com/release/supported-software

Please guide me. Thanks in advance.

Best Regards,

Kaushal

Re: Replication between Master PostgreSQL database version 9.6.1 and Standby/Slave PostgreSQL database version 10.17.

[Christophe Pettus]

> On Oct 5, 2022, at 09:32, Kaushal Shriyan  wrote:
> 
> Hi,
> 
> Is replication possible between PostgreSQL support Master database running 
> 9.6.1 version which is a lower version to Standby/Slave running version 
> 10.17? 

Binary replication is not possible between different major versions.  You can 
do logical replication, using the pglogical extension:

https://github.com/2ndQuadrant/pglogical

(Although not directly related, do note that 9.6 has been past end-of-life for 
nearly a year, and version 10 will reach end-of-life in November of this year.  
10.17 is over a year old, and 9.6.1 is almost six years old; the most recent 
versions of each are 10.22 and 9.6.24.)

Re: Number of updated rows with LibPQ

[Tom Lane]

Laurenz Albe  writes:
> On Wed, 2022-10-05 at 16:38 +0200, Dominique Devienne wrote:
>> For selects, I have [PQnTuples][2], but what to do on INSERT, UPDATE, DELETE?
>> Parse the result of PQcmdTuples myself??? If so, what's the 0 in the
>> INSERT below?

> Yes, you have to use PQcmdTuples(), and you have to convert the string to an 
> integer.
> But don't worry: the result will *not* be "INSERT 0 5", it will be just "5", 
> so
> you won't have to parse anything.

Yeah, just applying atoi() or atol() to the result should be enough.

regards, tom lane

Re: Postgres calendar?

[Josef Šimánek]

út 4. 10. 2022 v 23:02 odesílatel Bruce Momjian  napsal:
>
> Would people be interesting in subscribing to a Postgres calendar that
> includes dates for minor releases, final minor release dates for major
> versions, commit fests, and even Postgres events?  For example, it could
> include information from:
>
> https://www.postgresql.org/developer/roadmap/
> https://www.postgresql.org/support/versioning/
> https://commitfest.postgresql.org/
> https://www.postgresql.org/about/events/
>
> We could even add information about beta, release candidate, and final
> major releases, though the final release dates are usually not public.
>
> This could be done in Google Calendar, with an exported ICS file, or via
> a dedicated ICS file.  I could even automate it by scraping our website.

Yes!

> --
>   Bruce Momjian  https://momjian.us
>   EDB  https://enterprisedb.com
>
>   Indecision is a decision.  Inaction is an action.  Mark Batterson
>
>
>

('dog$house' = quote_ident('dog$house')) is surprisingly FALSE

[Bryn Llewellyn]

The doc for "quote_ident()" says this:

«
https://www.postgresql.org/docs/14/functions-string.html
Returns the given string suitably quoted to be used as an identifier in an SQL 
statement string. Quotes are added only if necessary (i.e., if the string 
contains non-identifier characters or would be case-folded). Embedded quotes 
are properly doubled.
»

B.t.w, the value of "quote_ident()" rests on the distinction between a name 
(what you provide with the function's actual argument) and an identifier (what 
it returns). Some of you flatly reject (borrowing a phrase from Tom) the 
distinction between these two terms of art. Oh well…

Try this:

create table dog$(n int); -- OK
create table $dog(n int); -- Bad
create table "$dog"(n int); -- OK

These outcomes are consistent with the rules that say when a proposed name 
needs to be double-quoted to form its identifier in a SQL statement (or 
PL/pgSQL source text).

So it's correct for this to return FALSE:

select '$dog' = quote_ident('$dog');

But it's incorrect w.r.t. "quotes are added only if necessary" for this to 
return FALSE:

select 'dog$' = quote_ident('dog$');

"format()" shows the same error when you use the %I placeholder. I suppose that 
"format()" and "quote_ident()" share the same underlying implementation.

select format('What happens with %I?', 'dog'); -- double quotes are not added
select format('What happens with %I?', 'dog$'); -- double quotes are added

Re: ('dog$house' = quote_ident('dog$house')) is surprisingly FALSE

[David G. Johnston]

On Wed, Oct 5, 2022 at 5:17 PM Bryn Llewellyn  wrote:

> The doc for "quote_ident()" says this:
>
> «
> https://www.postgresql.org/docs/14/functions-string.html
> Returns the given string suitably quoted to be used as an identifier in an
> SQL statement string. Quotes are added only if necessary (i.e., if the
> string contains non-identifier characters or would be case-folded).
> Embedded quotes are properly doubled.
> »
>
But it's incorrect w.r.t. "quotes are added only if necessary" for this to
> return FALSE:
>
> select 'dog$' = quote_ident('dog$');
>

The documentation also says:

"Note that dollar signs are not allowed in identifiers according to the
letter of the SQL standard, so their use might render applications less
portable."

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

So I can see an argument for the existing behavior.  It doesn't seem worth
changing in any case.  And I don't really see the documentation being
improved by covering this corner case in detail when the current behavior
is at least intuitive.
David J.

Re: ('dog$house' = quote_ident('dog$house')) is surprisingly FALSE

[Tom Lane]

"David G. Johnston"  writes:
> So I can see an argument for the existing behavior.  It doesn't seem worth
> changing in any case.  And I don't really see the documentation being
> improved by covering this corner case in detail when the current behavior
> is at least intuitive.

quote_ident is a good bit more conservative than the core lexer
about what is an "identifier character" --- it considers all
non-ASCII characters as requiring quoting, too.

For typical uses of quote_ident, I think this is good future-proofing:
it makes it very much less likely that something quote_ident decides
not to quote would be rejected by some future PG version (not to mention
non-PG SQL databases).  So I'm not really in a hurry to change the code.
Maybe we should tweak the docs a bit.

regards, tom lane

Re: ('dog$house' = quote_ident('dog$house')) is surprisingly FALSE

[Adrian Klaver]

On 10/5/22 17:16, Bryn Llewellyn wrote:

The doc for "quote_ident()" says this:

«
https://www.postgresql.org/docs/14/functions-string.html
Returns the given string suitably quoted to be used as an identifier in an SQL 
statement string. Quotes are added only if necessary (i.e., if the string 
contains non-identifier characters or would be case-folded). Embedded quotes 
are properly doubled.
»

B.t.w, the value of "quote_ident()" rests on the distinction between a name 
(what you provide with the function's actual argument) and an identifier (what it 
returns). Some of you flatly reject (borrowing a phrase from Tom) the distinction between 
these two terms of art. Oh well…


What it returns is text, quoted if needed:

create table "$dog"(n int);

select  pg_typeof(quote_ident('$dog')), quote_ident('$dog');
 pg_typeof | quote_ident
---+-
 text  | "$dog"

The way I see is if it where an actual identifier then this:

select * from quote_ident('$dog');
 quote_ident
-
 "$dog"

would be equal to this:

select * from "$dog";
 n
---



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: ('dog$house' = quote_ident('dog$house')) is surprisingly FALSE

[Christophe Pettus]

> On Oct 5, 2022, at 17:16, Bryn Llewellyn  wrote:
> B.t.w, the value of "quote_ident()" rests on the distinction between a name 
> (what you provide with the function's actual argument) and an identifier 
> (what it returns).

There is no first-class "identifier" type in PostgreSQL, so a function can't 
"return an identifier."  It returns a string which might, when placed into a 
larger string and processed as SQL, be lexically correct as an identifier.

To be useful, quote_ident() can't fail to quote a string in such a way that 
it's not a valid identifier to PostgreSQL.  If it quotes some strings that 
PostgreSQL would accept as identifiers without quotes, that's interesting, I 
guess, but I'm not sure I see how it is a bug.

Pragmatically, what this function is for it to assemble SQL statements as 
strings.  Any review of its correctness needs to be based on a situation where 
it can't be used for that.

Re: ('dog$house' = quote_ident('dog$house')) is surprisingly FALSE

[Bryn Llewellyn]

> t...@sss.pgh.pa.us wrote:
> 
>> david.g.johns...@gmail.com writes:
>> 
>> So I can see an argument for the existing behavior. It doesn't seem worth 
>> changing in any case. And I don't really see the documentation being 
>> improved by covering this corner case in detail when the current behavior is 
>> at least intuitive.
> 
> quote_ident is a good bit more conservative than the core lexer about what is 
> an "identifier character" --- it considers all non-ASCII characters as 
> requiring quoting, too.
> 
> For typical uses of quote_ident, I think this is good future-proofing: it 
> makes it very much less likely that something quote_ident decides not to 
> quote would be rejected by some future PG version (not to mention non-PG SQL 
> databases). So I'm not really in a hurry to change the code. Maybe we should 
> tweak the docs a bit.

Is the use of *any* non-Latin character in the name of a database object a 
corner case? I appreciate that some outfits require Latin object names because 
they don't want to make it hard to have a global development staff. But not all 
outfits follow such a rule.

It's a pity that the doc reference that I started with and the second one that 
David mentioned:

https://www.postgresql.org/docs/14/functions-string.html 

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
 


don't x-reference each other. I had no reason to suspect that what I read in 
the dedicated account of quote_ident was something of a sketch—and that it 
(loosely speaking) has to be interpreted in the light of the second one. I 
appreciate that the PG doc aims for maximally terse, full-on DRY accounts. But 
neither of the two accounts gives a hint that quote_ident is less permissive 
than are PG's SQL and PL/pgSQL parsers themselves. This would have fixed it for 
me:

«
[quote_ident] Returns its input argument string suitably rendered to be used as 
an identifier in an SQL statement string. Rendering is not always needed and in 
some cases the return value is identical to the input value. The rules for when 
rendering is needed, and what form it takes, are explained in [x-ref]. Here are 
four examples:

  select quote_ident('eye'); → eye
  select quote_ident($$ab"cd'ef$$); → "ab""cd'ef"
  select quote_ident('øye'); → "øye"
  select quote_ident('我的桌子'); → "我的桌子"

The third and fourth examples show that quote_ident is much stricter than the 
rules explained in [x-ref] demand. They explain that for example, the SQL 
identifier for this role name:

  redaktør

is that bare name—with no rendering. Quote_ident's unnecessary strictness means 
that some tempting uses for the function are not viable. A test like this:

  if 'proposed_name' <> quote_ident('proposed_name') then ...

will give a false negative for large classes of proposed names.
»

About:

> good future-proofing: it makes it very much less likely that something 
> quote_ident decides not to quote would be rejected by some future PG version


Does this imply a risk that a future PG version will go against the SQL 
standard and reject any non-latin name that is free of all punctuation 
characters, when used in the role of a SQL identifier, unless it's double 
quoted?

After all, I'd guess that a great deal of SQL text and PL/pgSQL source text is 
typed by hand—and you don't usually see double quotes delineating every SQL 
identifier when this isn't needed. (And you almost never see double quotes 
delineating a SQL identifier where this rendering is necessary.)

Re: ('dog$house' = quote_ident('dog$house')) is surprisingly FALSE

[Bryn Llewellyn]

> adrian.kla...@aklaver.com wrote:
> 
> The way I see is if it where an actual identifier then this:
> 
> select * from quote_ident('$dog');
> 
> quote_ident
> -
> "$dog"
> 
> would be equal to this:
> 
> select * from "$dog";

I think that the clue here is to go into philosophical overdrive. SQL 
statements, and if-then-else programs, are platonic notions. Like in the famous 
example of the notion of Boston itself—as opposed to how it's denoted in 
different contexts. Some would refer to it by saying "Boston". Others would say 
"波士顿".

In our world, the phenomenon is illustrated by this (after authorizing as a 
superuser):

create role "my name" login;
create database db;
grant all on database db to "my name";

\c db "my name"

create schema s;
create table s."silly name"(n int);
select relname
from pg_class c inner join pg_roles r on c.relowner = r.oid
where r.rolname = 'my name';

This is the result (in "\t on" mode)

 silly name

So that's *three* different ways to denote the platonic notion that I had in my 
head, of a certain table in a certain schema in a certain database, before I 
typed anything

What we deal with in our ordinary professional work is SQL texts, program 
source texts, within these, SQL identifier texts, and then the conventional 
display of the results of SQL and program execution. To emphasize the point 
about resulst display, try "\d s.*" in "\t off" mode. You'll see this:

  Table "s.silly name"
 Column |  Type   | Collation | Nullable | Default 
+-+---+--+-
 n  | integer |   |  | 

But this SQL text:

drop table "s.silly name";

tells me that there's no such table. It's all a matter of convention. In an 
alternative universe, maybe manifest string constants are rendered, with no 
delineation, in red text; and identifiers are rendered, again with no 
delineation, in green text. In another universe, all SQL and program 
composition is done by talking. Manifest string constants are rendered by 
shouting; and identifiers are rendered in a whisper. And why not...

Re: ('dog$house' = quote_ident('dog$house')) is surprisingly FALSE

[Bryn Llewellyn]

> x...@thebuild.com wrote:
> 
> There is no first-class "identifier" type in PostgreSQL, so a function can't 
> "return an identifier."  It returns a string which might, when placed into a 
> larger string and processed as SQL, be lexically correct as an identifier.

It takes huge discipline always to say "the text of an identifier" when the 
context of discourse is established. But, yes, I agree, when I wrote this:

«
...the value of "quote_ident()" rests on the distinction between a name (what 
you provide with the function's actual argument) and an identifier (what it 
returns). 
»

that the context of discourse was indeed established. I should have made no 
such assumption and written this instead:

«
...the value of "quote_ident()" rests on the distinction between the text of a 
name (what you provide with the function's actual argument) and the text of an 
identifier (what it returns). 
»