Re: Seems to be impossible to set a NULL search_path

On Wed, Jul 13, 2022 at 10:02 PM Bryn Llewellyn wrote: > > > > * The owner name | The *identifier* for the owner > name +- Joe > | "Joe"* > > This is what I've been banging on about all the time. It seems that I'm > the only person in the

Re: Seems to be impossible to set a NULL search_path

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> role_name >> - >> Bllewell >> ... >> "Bllewell" >> >> ...Are there really two distinct roles with those two names?... > > Is this another one of your mistakes in presenting a self-contained test case? I

Re: Seems to be impossible to set a NULL search_path

On Wed, Jul 13, 2022 at 4:33 PM Bryn Llewellyn wrote: > > > > > > * role_name - Bllewell Exotic Me "Exotic Me" "Bllewell"* > > Of course I understand why I see both Exotic Me with no quotes and "Exotic > Me" with double quotes: I asked for it. But why do I see both Bllewell with >

Re: Seems to be impossible to set a NULL search_path

> b...@yugabyte.com wrote: > >> david.g.johns...@gmail.com wrote: >> >> As for "schema identifiers" vs. "schema names" - they both seem equally >> wrong. The list can very much contain sequences of characters that when >> interpreted as an identifier and

Re: Seems to be impossible to set a NULL search_path

On 2022-07-06 20:47:19 -0700, Bryn Llewellyn wrote: > But never mind. If you'd like a diverting read on this topic, go here: > > https://blogs.oracle.com/sql/post/a-collection-of-plsql-essays > > look for this, and download the PDF: > > « > Names vs identifiers > > Databases are full of

Re: Seems to be impossible to set a NULL search_path

> david.g.johns...@gmail.com wrote: > > As for "schema identifiers" vs. "schema names" - they both seem equally > wrong. The list can very much contain sequences of characters that when > interpreted as an identifier and looked for in the pg_namespace catalog do > not find a matching entry and

Re: Seems to be impossible to set a NULL search_path

On Wed, Jul 6, 2022 at 1:47 PM Bryn Llewellyn wrote: > > It seems that the wording is wrong here: > > « The value for search_path must be a comma-separated list of schema > names. » > > It's schema identifiers—and not schema names. Yes? > > To add further clarity (or confusion) there is also

Re: Seems to be impossible to set a NULL search_path

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> « >> A convention often used is to write key words in upper case and names in >> lower case, e.g.: >> >> UPDATE my_table SET a = 5; >> » >> >> It should be « to write key words in upper case and unquoted identifiers in

Re: Seems to be impossible to set a NULL search_path

On 7/6/22 14:47, Bryn Llewellyn wrote: The problem was my stupid typo: writing « set search_path = 'pg_catalog, pg_temp'; » when I should *not* have typed those single quotes. Now the demo that I'd intended gets the outcome that I'd expected: *select count(*) from pg_class; --<<

Re: Seems to be impossible to set a NULL search_path

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote >> ... > > You either didn't read or failed or retain knowledge of the words in the > documentation that are the canonical reference for search_path and explain > exactly this. I suggest you (re-)read them. > >

Re: Seems to be impossible to set a NULL search_path

On Wed, Jul 6, 2022 at 1:13 PM Bryn Llewellyn wrote: > *david.g.johns...@gmail.com wrote:* > > At the level of discussion you want to have when you encounter unfamiliar > syntax please read the syntax chapter for the related concept (expression > identifiers). > > >

Re: Seems to be impossible to set a NULL search_path

> david.g.johns...@gmail.com wrote: > > At the level of discussion you want to have when you encounter unfamiliar > syntax please read the syntax chapter for the related concept (expression > identifiers). > >

Re: Seems to be impossible to set a NULL search_path

On Wed, Jul 6, 2022 at 11:50 AM Bryn Llewellyn wrote: > > It succeeded. And the \d metacommand showed me that I now have a table > pg_temp_3.x. Using a different database, it ends up in "pg_temp_1. What's > going on here? Is "pg_temp" a kind of generic nickname for ANY "pg_temp_N"? > > I hate to

Re: Seems to be impossible to set a NULL search_path

> david.g.johns...@gmail.com wrote: > > unless you are going to write: operator(pg_catalog.=) in your function the > advice to always use schema qualifications is not going to be taken > seriously... the correct search_path to set isn't "empty" but "pg_catalog", > "pg_temp". While this does

Re: Seems to be impossible to set a NULL search_path

On Wed, Jul 6, 2022 at 10:03 AM Christophe Pettus wrote: > > > > On Jul 6, 2022, at 09:48, Bryn Llewellyn wrote: > > Neither causes an error. The "show", in each case, prints the bare value > with no quotes. It never struck me try try double quotes around the > timezone argument. I'm shocked

Re: Seems to be impossible to set a NULL search_path

On Wed, Jul 6, 2022 at 9:49 AM Bryn Llewellyn wrote: > adrian.kla...@aklaver.com wrote: > > Not sure what your point is? > > > Try these two: > > > > > > > *set timezone = 'America/New_York';show timezone;set timezone = > "America/New_York";show timezone;* > Neither causes an error. The "show",

Re: Seems to be impossible to set a NULL search_path

> On Jul 6, 2022, at 09:48, Bryn Llewellyn wrote: > Neither causes an error. The "show", in each case, prints the bare value with > no quotes. It never struck me try try double quotes around the timezone > argument. I'm shocked that they are silently accepted here and seem to have > the

Re: Seems to be impossible to set a NULL search_path

> adrian.kla...@aklaver.com wrote: > > Not sure what your point is? Try these two: set timezone = 'America/New_York'; show timezone; set timezone = "America/New_York"; show timezone; Neither causes an error. The "show", in each case, prints the bare value with no quotes. It never struck me

Re: Seems to be impossible to set a NULL search_path

On 7/5/22 20:55, Bryn Llewellyn wrote: // /david.g.johns...@gmail.com wrote: / t...@sss.pgh.pa.us  wrote: search_path's value is not a SQL name.  It's a list of SQL names wrapped in a string ... and the list can be empty. This

Re: Seems to be impossible to set a NULL search_path

On Tue, Jul 5, 2022 at 11:12 AM Bryn Llewellyn wrote: > The section "Writing SECURITY DEFINER Functions Safely": > > > https://www.postgresql.org/docs/current/sql-createfunction.html#id-1.9.3.67.10.2 > > explains the risk brought if a bad actor creates an object that preemps > what the developer

Re: Seems to be impossible to set a NULL search_path

> david.g.johns...@gmail.com wrote: > >> t...@sss.pgh.pa.us wrote: >> >> search_path's value is not a SQL name. It's a list of SQL names wrapped in >> a string ... and the list can be empty. > > This doesn't seem to be correct - wrapping them in single quotes in the SET > command ends up

Re: Seems to be impossible to set a NULL search_path

On Tue, Jul 5, 2022 at 12:13 PM Tom Lane wrote: > Bryn Llewellyn writes: > > Thanks, all, for your replies. I'd assumed that the arguments of "set > search_path" had to be SQL names. so I tried "". But that caused an error. > I didn't try the ordinary empty string because I'd assumed that, as

Re: Seems to be impossible to set a NULL search_path

On 7/5/22 15:08, Bryn Llewellyn wrote: I was informed by this precedent: *truncate table u1.t1, t2; * It uses a comma-separated list of optionally qualified  SQL names. And this: « *CREATE SCHEMA schema_name ... *» Those are creating objects. Set search_path is setting a configuration

Re: Seems to be impossible to set a NULL search_path

> t...@sss.pgh.pa.us wrote: > >> b...@yugabyte.com writes: >> >> ...I'd assumed that the arguments of "set search_path" had to be SQL names... > > search_path's value is not a SQL name. It's a list of SQL names wrapped in a > string ... and the list can be empty. I was informed by this

Re: Seems to be impossible to set a NULL search_path

Bryn Llewellyn writes: > Thanks, all, for your replies. I'd assumed that the arguments of "set > search_path" had to be SQL names. so I tried "". But that caused an error. I > didn't try the ordinary empty string because I'd assumed that, as an illegal > SQL name, it would be illegal in "set

Re: Seems to be impossible to set a NULL search_path

>>> adrian.kla...@aklaver.com wrote: >>> >>> set search_path = ''; >>> show search_path ; >>> search_path >>>- >>> "" >> pavel.steh...@gmail.com >> >> ...But still in this case, there is pg_catalog in search path. > > Yes but from OP: > > « I've confirmed that even a

Re: Seems to be impossible to set a NULL search_path

On 7/5/22 11:24 AM, Pavel Stehule wrote: > set search_path = ''; show search_path ;   search_path -   "" \d Did not find any relations. But still in this case, there is pg_catalog in search path. Yes but from OP: "I've confirmed that even a

Re: Seems to be impossible to set a NULL search_path

út 5. 7. 2022 v 20:18 odesílatel Adrian Klaver napsal: > On 7/5/22 11:12 AM, Bryn Llewellyn wrote: > > The section "Writing SECURITY DEFINER Functions Safely": > > > > > https://www.postgresql.org/docs/current/sql-createfunction.html#id-1.9.3.67.10.2 > > > > > Finally, what do you think of a

Re: Seems to be impossible to set a NULL search_path

> On Jul 5, 2022, at 11:12, Bryn Llewellyn wrote: > Finally, what do you think of a possible future enhancement to allow setting > a null search_path? You use the empty string, rather than NULL, but it works right now: xof=# show search_path; search_path - "$user",

Re: Seems to be impossible to set a NULL search_path

On 7/5/22 11:12 AM, Bryn Llewellyn wrote: The section "Writing SECURITY DEFINER Functions Safely": https://www.postgresql.org/docs/current/sql-createfunction.html#id-1.9.3.67.10.2 Finally, what do you think of a possible future enhancement to allow setting a null search_path? set

Seems to be impossible to set a NULL search_path

The section "Writing SECURITY DEFINER Functions Safely": https://www.postgresql.org/docs/current/sql-createfunction.html#id-1.9.3.67.10.2 explains the risk brought if a bad actor creates an object that preemps what the developer intended by putting it in a schema that's ahead of the intended