Re: ('dog$house' = quote_ident('dog$house')) is surprisingly FALSE
On 6 Oct 2022, at 16:04, Bryn Llewellyn wrote: 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? From my perspective this thread seems to miss the essential purposes behind quote_ident(). It is part of processing external/user input — 1. Protecting from PostgreSQL which always maps everything to lower case before anything gets to the parser 2. Protection against SQL injection when processing input from outside the trusted perimeter Expecting an arbitrary string to be equal to itself after it has been through string processing code is risky unless that processing is part of the design, and quote_ident() was never designed to be part of any such arrangement. Expanding — 1. It is a complex question what happens to non-ASCII characters when they are mapped to lower case… sometimes this is a meaningful concept e.g., ∏ -> π, sometimes it is not, e.g., pick any Chinese/Korean/Japanese character. If the designer decides to use non-ASCII characters in the identifier they can… just double-quote those identifiers. If the designer wants to use camelCase ASCII they can, but the identifier will be camelcase inside the machine unless it was double quoted. AFAIK we never really use quote_ident() except to process external input. As noted above this function is not designed to be part of an equality test when attempting system introspection, rather — 2. The simple quote_ident() function can also be used to wrap untrusted input so it will not mess with the parser. It is used with quote_literal() when building dynamic SQL statements from user (i.e., untrusted) input. From my perspective any use of these function outside their scope is just that… outside their scope, with no promise this usage will work or comply with any current or future standard, or imply anything useful about pretty much anything. Maybe I’m oversimplifying but I believe the current functions work and do their specific jobs, and have nothing to do with anything else. So there is no surprise for me in the subject line. There is mild surprise the question was asked. BTW this ignores whether or not PG mapping everything that’s not quoted to lower case is standards compliant. This whole topic would be simpler if the case was left alone but that’s a long road ago and I believe most of the bridges have been burnt :) Regards Gavan Schneider —— Gavan Schneider, Sodwalls, NSW, Australia Explanations exist; they have existed for all time; there is always a well-known solution to every human problem — neat, plausible, and wrong. — H. L. Mencken, 1920
Aw: Re: ('dog$house' = quote_ident('dog$house')) is surprisingly FALSE
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. And, indeed, there isn't. Perhaps there's a table s."silly name". It is accidental if unfortunate that the is quoted with ""'s in the \d output... Karsten
[Beginner Question]How to generate a call-graph for project?
Hi teams, I am a student who are interested in database,and now I want to generate a call-graph for this database that can help me to understand the structure.(The project is written in cpp,not only c) I haved tried the doxygen,but the information that generates is very incomplete(Even if I open the extract-all opinion). How can I achieve it(my system is fedora linux 36)? Thanks in advance! Yours, JingZhang.
Cannot convert partitioned table to a view
Is there a special reason why masking views behind table partitions is not allowed? Trying to do so raises an error: CREATE RULE "_RETURN" AS ON SELECT TO xxx_20220715 DO INSTEAD SELECT * FROM yyy WHERE ...; ERROR: cannot convert partitioned table "xxx_20220715" to a view https://github.com/postgres/postgres/blob/master/src/backend/rewrite/rewriteDefine.c#L434 That said, I can trick it if I have a view on a remote server and then attach it as a remote partition through the foreign data wrapper. Are there any dangers that should be considered with such an approach? I noticed that attaching the "_RETURN" rule to a table converts it to a view, and the operation is irreversible, i.e. once attached, I cannot drop the rule and put the relation back to a table one. Cordially, -- Kouber Saparev
Re: Cannot convert partitioned table to a view
Kouber Saparev writes: > I noticed that attaching the "_RETURN" rule to a table converts it to a > view, and the operation is irreversible, i.e. once attached, I cannot drop > the rule and put the relation back to a table one. 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. regards, tom lane
pg_restore creates public schema?
pg_dump 9.6.24 pg_restore 13.8 Why does pg_restore explicitly create "public" even though public is automatically created when the database is created? I noticed that when using "--exit-on-error". It's disappointing, because I had to remove that option, which caused the restore to ignore other, actually important errors.) $ cd /var/lib/pgsql/backups/dumps/2022-10-04 $ pg_restore -vcC --if-exists --jobs=12 -Fd -d postgres CDSLBXW pg_restore: connecting to database for restore pg_restore: dropping DATABASE CDSLBXW pg_restore: processing item 10813 ENCODING ENCODING pg_restore: processing item 10814 STDSTRINGS STDSTRINGS pg_restore: processing item 10815 SEARCHPATH SEARCHPATH pg_restore: processing item 10816 DATABASE CDSLBXW pg_restore: creating DATABASE "CDSLBXW" pg_restore: connecting to new database "CDSLBXW" pg_restore: processing item 14 SCHEMA cds pg_restore: creating SCHEMA "cds" pg_restore: processing item 18 SCHEMA dba pg_restore: creating SCHEMA "dba" pg_restore: processing item 10 SCHEMA public pg_restore: creating SCHEMA "public" pg_restore: while PROCESSING TOC: pg_restore: from TOC entry 10; 2615 2200 SCHEMA public postgres pg_restore: error: could not execute query: ERROR: schema "public" already exists Command was: CREATE SCHEMA public; -- Angular momentum makes the world go 'round.
Re: pg_restore creates public schema?
Ron writes: > pg_dump 9.6.24 You realize that that version's been out of support for a year? > Why does pg_restore explicitly create "public" even though public is > automatically created when the database is created? We fixed that in v11 (see 5955d9341). Evidently the fix requires an updated pg_dump more than pg_restore. However, I believe that only happens with -c, so why are you using both -c and -C? regards, tom lane
Re: Postgres calendar?
+1. On Tue, Oct 4, 2022 at 5:02 PM 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? 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. > > -- > Bruce Momjian https://momjian.us > EDB https://enterprisedb.com > > Indecision is a decision. Inaction is an action. Mark Batterson > > > >
Re: pg_restore creates public schema?
On 10/6/22 09:49, Tom Lane wrote: Ron writes: pg_dump 9.6.24 You realize that that version's been out of support for a year? Yes, which is why I'm dumping from an EOL cluster, and restoring to a supported version. Why does pg_restore explicitly create "public" even though public is automatically created when the database is created? We fixed that in v11 (see 5955d9341). Evidently the fix requires an updated pg_dump more than pg_restore. Ah. However, I believe that only happens with -c, so why are you using both -c and -C? Because the database might already exist on the target before doing the restore. -- Angular momentum makes the world go 'round.
Re: pg_restore creates public schema?
Ron writes: > On 10/6/22 09:49, Tom Lane wrote: >> Ron writes: >>> pg_dump 9.6.24 >> You realize that that version's been out of support for a year? > Yes, which is why I'm dumping from an EOL cluster, and restoring to a > supported version. But why are you using the dead version's pg_dump? You could use the supported version of that. regards, tom lane
Re: pg_restore creates public schema?
On 10/6/22 10:20, Tom Lane wrote: Ron writes: On 10/6/22 09:49, Tom Lane wrote: Ron writes: pg_dump 9.6.24 You realize that that version's been out of support for a year? Yes, which is why I'm dumping from an EOL cluster, and restoring to a supported version. But why are you using the dead version's pg_dump? You could use the supported version of that. 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. -- Angular momentum makes the world go 'round.
Re: pg_restore creates public schema?
> On Oct 6, 2022, at 09:46, Ron 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. Running the new pg_dump doesn't require that it be installed on the server, just that it have access to it. (I understand there may be access restrictions that make that inconvenient as well, but presumably *some* servers have access to 5432 and can be used to run pg_dump.)
Re: Replication between Master PostgreSQL database version 9.6.1 and Standby/Slave PostgreSQL database version 10.17.
Thanks Christophe for the email and appreciate it. I am not sure if I understand the difference between binary and logical replication between PostgreSQL Master and Standby/Slave server. Please guide me. Thanks in advance. Best Regards, Kaushal On Wed, Oct 5, 2022 at 11:02 PM Christophe Pettus wrote: > > > > 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: Replication between Master PostgreSQL database version 9.6.1 and Standby/Slave PostgreSQL database version 10.17.
> On Oct 6, 2022, at 10:20, Kaushal Shriyan wrote: > > I am not sure if I understand the difference between binary and logical > replication between PostgreSQL Master and Standby/Slave server. It's best to review the documentation here: https://www.postgresql.org/docs/10/logical-replication.html Best, -- Christophe
Re: pg_restore creates public schema?
On 10/6/22 11:48, Christophe Pettus wrote: On Oct 6, 2022, at 09:46, Ron 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. Running the new pg_dump doesn't require that it be installed on the server, just that it have access to it. (I understand there may be access restrictions that make that inconvenient as well, but presumably *some* servers have access to 5432 and can be used to run pg_dump.) There is, and that is of course the first thing I thought of. Sadly, that VM doesn't have nearly enough disk space to hold the backup folder. (It's a tiny 2 CPU, 8GB RAM system with 75GB disk who's only purpose is to concentrate all the scripts needed to manage 8 servers in one place and crontab.) -- Angular momentum makes the world go 'round.
Re: pg_restore creates public schema?
> 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?
Re: pg_restore creates public schema?
On 10/6/22 10: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? Or Plan B: 1) Use pg_dump 9.6.24 on existing(going EOL) server 2) Set up a 9.6.24 instance somewhere you have control. 3) pg_restore to it. 4) Then use pg_dump 13.8 on the new instance. -- Adrian Klaver adrian.kla...@aklaver.com
Re: pg_restore creates public schema?
On 10/6/22 09:46, Ron wrote: On 10/6/22 10:20, Tom Lane wrote: Ron writes: On 10/6/22 09:49, Tom Lane wrote: Ron writes: pg_dump 9.6.24 You realize that that version's been out of support for a year? Yes, which is why I'm dumping from an EOL cluster, and restoring to a supported version. But why are you using the dead version's pg_dump? You could use the supported version of that. 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? 2) All those hoops, yet you can move the data off site with no issue? -- Angular momentum makes the world go 'round. -- Adrian Klaver adrian.kla...@aklaver.com
Re: pg_restore creates public schema?
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. -- Angular momentum makes the world go 'round.
Re: pg_restore creates public schema?
On 10/6/22 14:32, Adrian Klaver wrote: On 10/6/22 10: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? Or Plan B: 1) Use pg_dump 9.6.24 on existing(going EOL) server 2) Set up a 9.6.24 instance somewhere you have control. 3) pg_restore to it. 4) Then use pg_dump 13.8 on the new instance. While that would certainly work, it's a heck of a lot of extra effort for large one-time operations. -- Angular momentum makes the world go 'round.
Re: pg_restore creates public schema?
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: Ron writes: On 10/6/22 09:49, Tom Lane wrote: Ron writes: pg_dump 9.6.24 You realize that that version's been out of support for a year? Yes, which is why I'm dumping from an EOL cluster, and restoring to a supported version. But why are you using the dead version's pg_dump? You could use the supported version of that. 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... 2) All those hoops, Those hoops are for installing new software on a server. We jumped through those hoops six months ago to upgrade Pg 9.6.18 to .24 on the RHEL6 server yet you can move the data off site with no issue? This post was about pg_restore creating "public", not about how to copy files from point A to point B. -- Angular momentum makes the world go 'round.
Re: pg_restore creates public schema?
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? -- Adrian Klaver adrian.kla...@aklaver.com
Re: pg_restore creates public schema?
On 10/6/22 1:54 PM, Ron wrote: On 10/6/22 14:32, Adrian Klaver wrote: On 10/6/22 10: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? Or Plan B: 1) Use pg_dump 9.6.24 on existing(going EOL) server 2) Set up a 9.6.24 instance somewhere you have control. 3) pg_restore to it. 4) Then use pg_dump 13.8 on the new instance. While that would certainly work, it's a heck of a lot of extra effort for large one-time operations. 1) It could be scripted. 2) Nothing to stop you from splitting into schema only dump for the 9.6 --> 9.6 --> 13 restore of schema. 3) Then data only dump restored directly to 13. -- Adrian Klaver adrian.kla...@aklaver.com
Re: ('dog$house' = quote_ident('dog$house')) is surprisingly FALSE
> karsten.hilb...@gmx.net wrote: > >> b...@yugabyte.com wrote: >> >> 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. >> >> [Allow me to re-write my bext few words, for sport.] >> >> But a SQL statement with the following text representation >> >> drop table "s.silly name” >> >> when presented to the psql CLI as a text command in its language by >> appending a semi-colon causes that program to respond with some text that >> tells me that there's no such table. > > And, indeed, there isn't. Perhaps there's a table s."silly name”. It is > accidental if unfortunate that the is quoted with ""'s in the > \d output... I believe that you and I see things the same way, Karsten. Yes, it does seem at times that some things in PG are accidental—and sometimes prove to be unfortunate. Naturally, my questions to this list concern what know that I don’t understand. (Who knows what I think that I understand—but don’t. And who knows what I don’t even suspect is there waiting for me to try to understand.) To err is human—and it’s human, too, to misunderstand something when the proper understanding seems to be counter-intuitive. In my case, I developed my intuitions in the context of a different RDBMS with notions and terms of art that differ very significantly from PG’s—even though the core SQL syntax and semantics are deceptively similar. Maybe I should send posts to this list like this: « I just wrote and tested a PG implementation to do something I hadn’t done before. I was very impressed at how straightforward it was—and with how expressive of my intentions the text of my code seemed to be. Well done PG. » I do very often have that experience. But I’ve never seen a contribution along those lines in this forum—and I’ve formed the impression that it would be out of place.
Re: ('dog$house' = quote_ident('dog$house')) is surprisingly FALSE
> list.pg.ga...@pendari.org wrote:
>
>> b...@yugabyte.com wrote:
>>
>> 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?
>
> From my perspective this thread seems to miss the essential purposes behind
> quote_ident(). It is part of processing external/user input… Expecting an
> arbitrary string to be equal to itself after it has been through string
> processing code is risky unless that processing is part of the design, and
> quote_ident() was never designed to be part of any such arrangement.
>
> …If the designer decides to use non-ASCII characters in the identifier they
> can… just double-quote those identifiers.
>
> AFAIK we never really use quote_ident() except to process external input…
> There is no surprise for me in the subject line. There is mild surprise the
> question was asked.
Here's why I fell into the trap that I did.
(1) I used the Oracle RDBMS for a very long time. Now I’m working hard to
forget everything that I learned there and learn the PG way instead. I’m
frequently caught out.
(2) ORCL provides a function with the same aim as quote_indent. Its input is
the text of a name. And its output is the text of a legal SQL identifier for
that name. The output *always* starts and ends with a double quote. And why
not? Sometimes the bare text of a name is perfectly acceptable as the text of a
SQL identifier—and yet it’s still perfectly acceptable in that way when it's
surrounded with double quotes.
(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
The doc might well have said that quotes are often added when they're not
needed. I know that the doc also says "to be used as an identifier in an SQL
statement string". But the reference doc for a function should give a precise
and accurate specification of the rule that derives the output value from the
input value(s). (There might well be performance caveats.) As long as this is
reliable, then the user is free to use the function for any purpose where the
specified behavior fits the use case.
(4) I do have a genuine use case. I know that, usually, it's best to preface a
question to a forum like this with a clear statement of what you want to
achieve. Well, in this case I wanted to achieve a full correct understanding of
what quote_ident does. I think that I have this now. I accept that no change
will ever be made to the actual behavior of quote_ident (for example, to have
it always surround the return text with double quotes). And I accept that
nobody is motivated to change the docs to stop PG neophytes like me being
mislead by taking the extant wording at face value.
B.t.w., my specific use case is best solved by spending just a little effort to
write my own function to check my own, context-specific, spec of what defines a
bad name. Of course, it uses PG's native regular expression functionality. I
was simply distracted from that effort when I briefly wondered if the
ready-made quote_ident might save me some effort. I very soon realized that it
would not. But that the same time, I couldn't help noticing that its behavior
was at odds with the doc. This ought to surprise anybody but the most cynical
amongst us. So naturally I asked about this.
Re: ('dog$house' = quote_ident('dog$house')) is surprisingly FALSE
On Thu, Oct 6, 2022 at 3:53 PM Bryn Llewellyn 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.
David J.
