Re: pg_restore creates public schema?

[Ron]

On 10/6/22 17:01, Adrian Klaver wrote:

On 10/6/22 2:03 PM, Ron wrote:

On 10/6/22 14:35, Adrian Klaver wrote:

On 10/6/22 09:46, Ron wrote:

On 10/6/22 10:20, Tom Lane wrote:




Because installing new software on production servers requires hurdles 
(Service Now change ticket approved by the application support manager, 
Delivery Service Manager, Engineering Change Board, and a one week lead 
time before installing during the Saturday night maintenance window) 
that I'm not willing to jump through just to take an /ad hoc/ database 
backup.


1) So I assume that means Postgres 13.8 has not been installed in 
anticipation of the change over?


It's certainly been installed on the *new* (RHEL8) server. Not the EOL 
RHEL6 server, because of course the point is to get off of EOL software...


And the RHEL8 server can't talk to the RHEL6 server?


Give me /some/ credit for thinking of that first...

--
Angular momentum makes the world go 'round.

Re: Cannot convert partitioned table to a view

[Kouber Saparev]

На чт, 6.10.2022 г. в 17:02 ч. Tom Lane  написа:

> That is an ancient backwards-compatibility hack that you should not
> rely on, and most certainly shouldn't try to make use of from user
> code.  It exists because very ancient versions of pg_dump didn't
> know how to dump views as views.  We're more likely to rip it out
> as no-longer-needed than to expand what it does.
>

Does that mean that the ability to point remote partitions to views (on the
remote side) will be forbidden in the future? Currently this feature is
saving a lot of work (and disk-space) for us, as it allows us to introduce
significant changes to the partitioned tables model without the need to
populate these changes on all the hundreds of past partitions that do exist
(and that we barely read anyway, that's why they reside on a remote server).

--
Kouber Saparev

Re: pg_restore creates public schema?

[Alban Hertroys]

> On 6 Oct 2022, at 22:52, Ron  wrote:
> 
> On 10/6/22 12:46, Christophe Pettus wrote:
>>> On Oct 6, 2022, at 10:44, Ron  wrote:
>>> Sadly, that VM doesn't have nearly enough disk space to hold the backup 
>>> folder.
>> Use file mode, and stream the output via scp/ssh to a different machine?
> 
> I thought of that, too.  Unfortunately, the ssh version in RHEL 8.6 is 
> sufficiently old that "three way" ssh (person at HostA wanting to transfer a 
> file from Server1 to Server2) requires that port 22 be open from Server1 to 
> Server2.

Bit of a wild idea, I haven’t used SSH tunnels in years, so a bit fuzzy on the 
details:

Can you create an SSH tunnel to the new machine from the VM, then pipe that to 
an SSH connection from a machine that does have enough space to dump?

And then vice versa to the new machine to restore? (Unless access to that one 
is easier of course)

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Re: Cannot convert partitioned table to a view

[Tom Lane]

Kouber Saparev  writes:
> Does that mean that the ability to point remote partitions to views (on the
> remote side) will be forbidden in the future?

No ... what has that got to do with this?  We don't particularly care
what a foreign table is referencing, as long as it acts like a table.

regards, tom lane

Re: Cannot convert partitioned table to a view

[Kouber Saparev]

>
> No ... what has that got to do with this?  We don't particularly care
> what a foreign table is referencing, as long as it acts like a table.
>

Thank you.



> We're more likely to rip it out as no-longer-needed than to expand what it
> does.


Excuse me, I didn't understand what was about to be ripped out then. Is it
the ability to attach ON SELECT DO INSTEAD rules over tables in general?...
Currently it is possible to do it (and the tables are converted to views,
which is a bit confusing and counter-intentional at least in my case - I'd
like the table to stay always a table), but not possible in case the table
is a partition (yet still possible if the partition is remote pointing back
to the same original server for instance).

Regards,
--
Kouber Saparev

Re: Cannot convert partitioned table to a view

[Tom Lane]

Kouber Saparev  writes:
> Excuse me, I didn't understand what was about to be ripped out then.

The fact that creating a _RETURN rule for a table causes it to be
changed into a view.  That's ugly and arguably dangerous.

regards, tom lane

Re: pg_restore creates public schema?

[Adrian Klaver]

On 10/7/22 10:11, Ron wrote:


And the RHEL8 server can't talk to the RHEL6 server?


Give me /some/ credit for thinking of that first...


Also give me credit for not assuming what you have done and instead 
confirming it. I have been in involved in or followed multiple threads 
on this list where the participants assumed the obvious and not until 
some point deep in the thread did anyway get around to confirming the 
assumption and in doing so solved the problem. So I will continue to ask 
the obvious when it is not explicitly stated.




--
Angular momentum makes the world go 'round.


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

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

[Bryn Llewellyn]

> david.g.johns...@gmail.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> (3) The PG doc on quote_ident says this in large friendly letters:
>> 
>>> Quotes are added only if necessary…
>> 
>> Notice "only". I now know that this is very much not the case. You can 
>> compose an effectively unlimited number of different examples along these 
>> lines:
>> 
>> select quote_ident('redaktør'); → "redaktør"
>> create table redaktør(n int); → table successfully created
> 
> Yep, and that is precisely what would make for a good bug report. Pointing 
> out that "if necessary" does not indeed match up with the behavior. I suspect 
> it is likely to get changed - everything else being discussed just detracts 
> attention from it.

*BRIEFLY*

What does "make for a good bug report" mean, David? Is it:

(1.1) You, David, or somebody else who has been officially recognized as a PG 
Contributor (https://www.postgresql.org/community/contributors/) will file the 
bug, granting it credibility with their imprimatur?

or (1.2) I, Bryn, should file the bug.

About "I suspect it is likely to get changed", do you mean:

(2.1) Change the doc to match quote_ident's current, unpredictable, behavior? 
(By all means, substitute "hard to describe accurately, precisely, and yet 
still tersely" for "unpredictable".)

(2.2) Change quote_ident's implementation—and then write new doc to describe 
the new behavior precisely and accurately? And for this option, the next 
question is "What's the spec of the changed implementation?"

Notice that the issue is broader than just quote_ident, as this test shows:

prepare x(text) as select format('« %s », gives « %I ».', $1::text, $1::text);
execute x('dog');
execute x('Dog');
execute x('农民');

The same over-zealous double-quoting that quote_ident shows for 农民 is shown by 
format. Presumably they share the same underlying implementation (but, 
surprisingly, don't re-use the actual SQL parser code). Option 2.1 implies 
using the same wording for what provokes double-quoting for each function. I'd 
make a similar argument for option 2.2.

*MORE DETAIL*

About option (2.2), I mentioned that ORCL's equivalent to quote_ident 
implements a simpler rule: the text of the identifier that it returns is always 
surrounded with double quotes, whether or not doing this is necessary. The ORCL 
scheme relies on the fact that double-quoting when this isn't necessary is in 
no way harmful. Here’s pseudocode (presented as tested PL/pgSQL) for what a 
patched C implementation of quote_ident might do:

create function quote_ident_2(name in text)
  returns text
  language plpgsql
as $body$
declare
  i0 text not null := quote_ident(name);
  i1 text not null := regexp_replace(regexp_replace(i0, '^"', ''), '"$', 
'');
  ident  text not null := case(i1 = i0)
when true then '"'||i0||'"'
elsei0
  end case;
begin
  return ident;
end;
$body$;

Re David’s

> everything else being discussed just detracts attention from it.


I’m not convinced. The discussion has shown that some people are somewhat 
confused. For example, it was suggested that a name like this:

农民

ought to be double-quoted. A simple test shows that this isn’t the case. And it 
helps if everybody is clear about that.

There's also the question of use-cases. I've been forced to think a lot about 
SQL injection over the years. It's immediately obvious from reading any of the 
skimpiest blogs on the topic that the root cause is always faulty code that's 
been written by a confused developer. But it's very rare to see an account of 
the root cause whose wording uses carefully defined terms of art. (In fact, the 
notion of defining and using such terms of art has been resisted by 
contributors to this list.) If a future PG shipped with a built-in function 
like this:

function is_exotic(name in text) returns boolean

...with, of course, excellent documentation, then an outfit that decided to 
outlaw the use of exotic names (and this is almost always how the outcome 
emerges) could police adherence to the rule, database wide, (and cluster wide 
for global phenomena) with a few trivial tests against the relevant catalog 
relations, like this:

do $body$
begin
  assert not exists (select 1 from pg_class where is_exotic(relname));
end;
$body$;

A shipped "is_exotic" function would bring the secondary, but by no means 
insignificant, benefit, that the case for using “format” with "%I" or its 
"quote_ident" cousin could be grounded upon solidly defined, and named, 
notions. Like this example shows:

do $body$
declare
  stmt constant  text   not null := 'create table %s(n int);';
  n  text   not null := '';

  simple_names constant text[] not null := array['farmers', 'bønder', '农民', 
'农民、儿童', '农民，儿童'];

  exotic_names constant text[] not null := array['T', 'farmers and children', 
'"x', 'y"', '农民 & 儿童'];
begin
  foreach n in array simple_names loop
assert no

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

[Adrian Klaver]

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

david.g.johns...@gmail.com  wrote:


b...@yugabyte.com  wrote:

(3) The PG doc on quote_ident says this in large friendly letters:


Quotes are added only if necessary…


Notice "only". I now know that this is very much not the case. You 
can compose an effectively unlimited number of different examples 
along these lines:


*select quote_ident('redaktør'); → "redaktør"
create table redaktør(n int); → table successfully created
*


Yep, and that is precisely what would make for a good bug report. 
Pointing out that "if necessary" does not indeed match up with the 
behavior. I suspect it is likely to get changed - everything else 
being discussed just detracts attention from it.


**BRIEFLY**

What does "make for a good bug report" mean, David? Is it:


Oh for goodness sake just file a bug here:

https://www.postgresql.org/account/login/?next=/account/submitbug/

with the test case you showed in your previous post.

As to below:

1) If you want a guaranteed outcome then you are in the wrong business.

2) Excessive verbiage(writing for writing's sake) takes away from any 
argument you are trying to make. Less is more. I have come to the point 
where I ignore most of what you write as it really does not go anywhere 
other then make noise.








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

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

[Christophe Pettus]

> On Oct 7, 2022, at 17:16, Bryn Llewellyn  wrote:
> What does "make for a good bug report" mean, David?

Well, first of all, brevity. :-)

> Is it:
> 
> (1.1) You, David, or somebody else who has been officially recognized as a PG 
> Contributor (https://www.postgresql.org/community/contributors/) will file 
> the bug, granting it credibility with their imprimatur?
> 
> or (1.2) I, Bryn, should file the bug.

That's unnecessarily snarky.  You are the one who feels that there is an issue, 
so you are the one who should report the bug.  The community documents how to 
file a bug report here:

https://www.postgresql.org/docs/current/bug-reporting.html

People completely new to the community report bugs all the time; it's often 
their first contact with the community.

I don't think there is widespread agreement that this is as big an issue as you 
clearly feel it is, so it's up to you to persuade the community that it's worth 
changing.  You may not be successful, and the implementation of quote_ident 
stays the same as it is now.  If that makes PostgreSQL useless to you, well, 
contact the bursar for a full refund.

> About "I suspect it is likely to get changed", do you mean:
> 
> (2.1) Change the doc to match quote_ident's current, unpredictable, behavior? 
> (By all means, substitute "hard to describe accurately, precisely, and yet 
> still tersely" for "unpredictable".)
> 
> (2.2) Change quote_ident's implementation—and then write new doc to describe 
> the new behavior precisely and accurately? And for this option, the next 
> question is "What's the spec of the changed implementation?"


Make a proposal, and it can be debated.  If you feel up to it, prepare a 
documentation patch, a code patch, or both.  It's just SGML and C; they won't 
bite.  But you really need to make a specific, concrete, "it should do this 
instead" proposal.

And, with all due respect, there is a strong "you FOOLS" tone to your 
conversation on the list, as if the community are all morons, stubborn, or 
both.  I would really suggest you dial that back, because it's getting in the 
way of your often-reasonable points.

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

[David G. Johnston]

On Fri, Oct 7, 2022 at 5:16 PM Bryn Llewellyn  wrote:

> david.g.johns...@gmail.com wrote:
>
> b...@yugabyte.com wrote:
>
> (3) The PG doc on quote_ident says this in large friendly letters:
>
> Quotes are added only if necessary…
>
>
> Notice "only". I now know that this is very much not the case. You can
> compose an effectively unlimited number of different examples along these
> lines:
>
>
>
> *select quote_ident('redaktør'); → "redaktør"create table redaktør(n int);
> → table successfully created*
>
>
> Yep, and that is precisely what would make for a good bug report. Pointing
> out that "if necessary" does not indeed match up with the behavior. I
> suspect it is likely to get changed - everything else being discussed just
> detracts attention from it.
>
>
> **BRIEFLY**
>
> What does "make for a good bug report" mean, David? Is it:
>
> (1.1) You, David, or somebody else who has been officially recognized as a
> PG Contributor (https://www.postgresql.org/community/contributors/) will
> file the bug, granting it credibility with their imprimatur?
>
>
The research, evidence, and argument should be able to stand on their own.
With those qualities it doesn't really matter too much the reputation of
the person filing.


> or (1.2) I, Bryn, should file the bug.
>

I was providing some suggested wording for how your original email could
have been written as a simple bug report.  And I definitely encourage
people to take the time to consider and write good bug reports - while I do
try and provide a community service in either writing or responding to such
reports I am quite happy focusing on the later.


>
> About "I suspect it is likely to get changed", do you mean:
>
> (2.1) Change the doc to match quote_ident's current, unpredictable,
> behavior? (By all means, substitute "hard to describe accurately,
> precisely, and yet still tersely" for "unpredictable".)
>

The documentation would be my expectation, but the report doesn't need to
presuppose either outcome, just point out the inconsistency.


> (2.2) Change quote_ident's implementation—and then write new doc to
> describe the new behavior precisely and accurately? And for this option,
> the next question is "What's the spec of the changed implementation?"
>
> Notice that the issue is broader than just quote_ident, as this test shows:
>
>
Then add that to the report - they do indeed seem to be of a similar
nature.  If they weren't, then you'd have two bug reports.

I’m not convinced. The discussion has shown that some people are somewhat
> confused. For example, it was suggested that a name like this:
>
>
The follow-on conversation was likely to happen once the "why" of the
inconsistency started to be discussed.


> Compare this with the implementation that I thought, at first, that I
> could use when I simply believed the doc. (The subject line of this thread
> hits at the trivial SQL statement that would implement the "language SQL"
> function.) ANd if that's all there is to it, then when not ship is as a
> built-in?
>

I just suggest you separate straight-forward seeming bugs from
philosophical discussions.  This was a good example of a situation that was
simple enough to be a bug - whether the docs or code get changed is what
the bug report follow-up from the community is meant to work out.

David J.

Re: pg_restore creates public schema?

[Ron]

On 10/7/22 15:31, Alban Hertroys wrote:

On 6 Oct 2022, at 22:52, Ron  wrote:

On 10/6/22 12:46, Christophe Pettus wrote:

On Oct 6, 2022, at 10:44, Ron  wrote:
Sadly, that VM doesn't have nearly enough disk space to hold the backup folder.

Use file mode, and stream the output via scp/ssh to a different machine?

I thought of that, too.  Unfortunately, the ssh version in RHEL 8.6 is sufficiently old 
that "three way" ssh (person at HostA wanting to transfer a file from Server1 
to Server2) requires that port 22 be open from Server1 to Server2.

Bit of a wild idea, I haven’t used SSH tunnels in years, so a bit fuzzy on the 
details:

Can you create an SSH tunnel to the new machine from the VM, then pipe that to 
an SSH connection from a machine that does have enough space to dump?


Interesting.  (It's above my ssh expertise, though.)

--
Angular momentum makes the world go 'round.

Re: Cannot convert partitioned table to a view

[Ron]

On 10/7/22 15:27, Kouber Saparev wrote:

На чт, 6.10.2022 г. в 17:02 ч. Tom Lane  написа:

That is an ancient backwards-compatibility hack that you should not
rely on, and most certainly shouldn't try to make use of from user
code.  It exists because very ancient versions of pg_dump didn't
know how to dump views as views.  We're more likely to rip it out
as no-longer-needed than to expand what it does.


Does that mean that the ability to point remote partitions to views (on 
the remote side) will be forbidden in the future? Currently this feature 
is saving a lot of work (and disk-space) for us, as it allows us to 
introduce significant changes to the partitioned tables model without the 
need to populate these changes on all the hundreds of past partitions that 
do exist (and that we barely read anyway, that's why they reside on a 
remote server).


I think maybe you and Tom are talking about different things.

Why do you need rules and views, when a regular old partitioned table can 
have foreign tables in it?



--
Angular momentum makes the world go 'round.