Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

I read this blog postPostgreSQL 11 – Server-side Procedures — Part 1 and Part 2It starts with “Thanks to the work done by 2ndQuadrant contributors, we now have the ability to write Stored Procedures in PostgreSQL… once PostgreSQL 11 comes out”. It focuses on doing txn control from a stored proc.In

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

Thanks Adrian. My aim with p1() was to show that its behavior, in each AUTOCOMMIT mode, aligns with my intuition. I’ve noticed the system-generated “begin” that you mention when I add this to my “/usr/local/var/postgres/postgresql.conf”: log_statement = 'all' log_directory = 'log' logging_colle

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

te: On Wed, Aug 7, 2019 at 12:19 AM Bryn Llewellyn wrote: > 1. my call p2() starts a txn. In my opinion this is the point: a procedure must be "owner" of the transaction to issue transaction control statements. You can watch different behaviors placing here and there txid_current()

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

) all implicitly start a txn and so “start transaction” isn’t needed! Thanks to all who responded. The synthesis of what you all wrote helped me enormously. Case closed. On 07-Aug-2019, at 00:26, Kyotaro Horiguchi wrote: Hello, Bryn. At Tue, 6 Aug 2019 15:18:51 -0700, Bryn Llewellyn wr

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

mentioned is actually issued by (some) clients—at least psql and Python-on-psycopg2—as an explicit call from the client. In other words, it isn’t the server that generates this. Does anyone know for sure how this works? On 07-Aug-2019, at 11:56, Adrian Klaver wrote: On 8/7/19 11:46 AM, Bryn

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

Thanks, Adrian. On 07-Aug-2019, at 13:19, Adrian Klaver wrote: On 8/7/19 12:54 PM, Bryn Llewellyn wrote: > Yes, I do believe that I understand this. But there’s no getting away from > the fact that the AUTOCOMMIT mode, and what this implies, is a server-side > phenomenon—at least a

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

ug-2019, at 13:40, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Wed, Aug 7, 2019 at 12:18 PM Luca Ferrari mailto:fluca1...@gmail.com>> wrote: On Wed, Aug 7, 2019 at 8:28 PM Bryn Llewellyn mailto:b...@yugabyte.com>> wrote: > B.t.w., I noticed that “set transaction is

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

’t understand, therefore, why some people (but not you, Daniel!) who’ve responded to my questions say that starting my proc with “rollback” is pointless.I tried, earlier, to say “case closed”. I’ll say it again now.On 08-Aug-2019, at 06:53, Daniel Verite <dan...@manitou-mail.org> wrote: Bry

psql behavior change on upgrade from version 12.x to 13.1

Using a MacBook Pro with the current Big Sur—Version 11.2 (20D64). I just upgraded to PostgreSQL 13.1. (Earlier, I was on 12.x.) Using psql, the behavior of ordinary copy-and-paste has change dramatically, and for the worse, w.r.t. Version 12. HAS ANYBODY ELSE SEEN WHAT I REPORT BELOW? First

Re: psql behavior change on upgrade from version 12.x to 13.1

paul.foers...@gmail.com wrote: Hi Bryn, > On 09. Feb, 2021, at 19:55, Bryn Llewellyn wrote: > > Using a MacBook Pro with the current Big Sur—Version 11.2 (20D64). > > I just upgraded to PostgreSQL 13.1. (Earlier, I was on 12.x.) Using psql, the > behavior of ordinary

Re: psql behavior change on upgrade from version 12.x to 13.1

On 09-Feb-2021, at 11:43, Tom Lane wrote: Bryn Llewellyn writes: > HAS ANYBODY ELSE SEEN WHAT I REPORT BELOW? > First observation > Now, when I copy a single line SQL command, terminated with semicolon and > newline from the Text Edit app (with Command-C or the menu it

Re: psql behavior change on upgrade from version 12.x to 13.1

On 09-Feb-2021, at 12:11, Tom Lane wrote: Bryn Llewellyn writes: > Here’s what I get when I do "otool -L /usr/local/bin/psql"; > /usr/local/bin/psql: > /usr/local/lib/libpq.5.dylib (compatibility version 5.0.0, current > version 5.13.0) > /usr/local/opt

Re: psql behavior change on upgrade from version 12.x to 13.1

On 09-Feb-2021, at 12:49, Adrian Klaver wrote: On 2/9/21 12:19 PM, Bryn Llewellyn wrote: > On 09-Feb-2021, at 12:11, Tom Lane wrote: > Bryn Llewellyn writes: >> Here’s what I get when I do "otool -L /usr/local/bin/psql"; >> /usr/local/bin/psql: >>

Re: psql behavior change on upgrade from version 12.x to 13.1

On 09-Feb-2021, at 14:16, raf wrote: On Tue, Feb 09, 2021 at 12:19:21PM -0800, Bryn Llewellyn wrote: > > > On 09-Feb-2021, at 12:11, Tom Lane wrote: > > Bryn Llewellyn writes: >> Here’s what I get when I do "otool -L /usr/local/bin/psql"; > >> /us

Unexpected result from ALTER FUNCTION— looks like a bug

SUMMARY This part of the syntax diagram for "alter function": ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] action [ … ] says that the first "action" can be followed (without punctuation) by zero, one, or many other actions. A semantic rule says that no particular act

Re: Unexpected result from ALTER FUNCTION— looks like a bug

> t...@sss.pgh.pa.us wrote: > >> david.g.johns...@gmail.com wrote: >> >> Might I suggest the following... > > Actually, the reason proconfig is handled differently is that it's a > variable-length field, so it can't be represented in the C struct that we > overlay onto the catalog tuple... Th

"create function... depends on extension..." not supported. Why?

Note: I’m asking because the answer to the question “Why isn’t X supported?” is always useful. For example, if supporting it would imply a logical impossibility that I’m too slow to spot, it helps me when someone explains what I failed to realize. Equally, it helps me to know when the answer is

Re: "create function... depends on extension..." not supported. Why?

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> Note: I’m asking because the answer to the question “Why isn’t X supported?” >> is always useful. For example, if supporting it would imply a logical >> impossibility that I’m too slow to spot, it helps me when someone expl

Re: "create function... depends on extension..." not supported. Why?

> t...@sss.pgh.pa.us wrote: > >> b...@yugabyte.com wrote: >> >> The discussion has diverging threads and very many turns. I think that I >> managed to skim through the entire tree. As I read it, the discussion was >> entirely about the semantics of the proposed dependency of a function (or >>

Meaning of "constant" not honored when the variable is used as the actual for a proc's OUT formal paameter

I just did this using PG 14.2: create procedure p(a out int) language plpgsql as $body$ begin a := 42; end; $body$; do $body$ declare a constant int := 0; begin call p(a); raise info '%', a::text; end; $body$; The DO block runs without error and reports "INFO: 42". This is an unambi

Re: Meaning of "constant" not honored when the variable is used as the actual for a proc's OUT formal paameter

> t...@sss.pgh.pa.us wrote: > >> b...@yugabyte.com wrote: >> >> The DO block runs without error and reports "INFO: 42". This is an >> unambiguous semantic error because "a" is declared "constant"… Is this a >> known issue? > > It is, see > https://git.postgresql.org/gitweb/?p=postgresql.git&

"A transaction cannot be ended inside a block with exception handlers."

The PG doc section 43.8. Transaction Management: https://www.postgresql.org/docs/current/plpgsql-transactions.html says "A transaction cannot be ended inside a block with exception handlers." It's easy to demonstrate the restriction by adding this just before the final "end;" in the simple examp

Re: "A transaction cannot be ended inside a block with exception handlers."

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> Is there simply no way that inserts into table "t" in my example can be >> encapsulated in PL/pgSQL so that the error from the failing trigger can be >> handled rather there than escaping, raw, to the client? > > Any poten

Re: "A transaction cannot be ended inside a block with exception handlers."

> t...@sss.pgh.pa.us wrote: > >> b...@yugabyte.com wrote: >> >> I want to demonstrate how to meet this requirement: >> >> « >> Encapsulate each business function in a user-defined subprogram that hides >> all the implementation details like table names and the SQL statements that >> manipulate

The P0004 assert_failure exception assert_failure exception seems to be unhandleable

I just stumbled on the fact that the "assert_failure" exception seems to be unhandleable. My test is at the end. Is this intended? I looked at the section: « 43.9.2. Checking Assertions https://www.postgresql.org/docs/current/plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-ASSERT » It says

Re: "A transaction cannot be ended inside a block with exception handlers."

> david.g.johns...@gmail.com wrote: > > I do understand better now and indeed the current limitation has no > workaround that I can come up with. I was hoping maybe subblocks would work > but its pretty clear cut that to catch an error at the commit command you > must catch it within a block an

Re: The P0004 assert_failure exception assert_failure exception seems to be unhandleable

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> « >> Note that ASSERT is meant for detecting program bugs, not for reporting >> ordinary error conditions. Use the RAISE statement, described above, for >> that. >> » >> >> But it takes quite a stretch of the imagination t

Deferred constraint trigger semantics

SUMMARY === I looked at the sections "CREATE TRIGGER" and "Chapter 39. Triggers" in the Current PG doc. But I failed to find any information about the semantics of the deferred constraint trigger or about the use cases that motivated this feature. Nor could I find any code examples. Interne

Re: Deferred constraint trigger semantics

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> INFO: trg fired. new.v = 80, n = 8 >> >> It shows the expected "new" value each time it fires. And yet the query >> reflects the table content on statement completion. This seems to be very >> strange. > > From the docum

Re: Deferred constraint trigger semantics

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: Thanks for the point-by-point reply, David. >> ...makes no mention of what you might expect to see in an AFTER EACH ROW >> trigger. > > ...the absence of a comment declaring a guarantee of order means that, like > the comment f

Re: Deferred constraint trigger semantics

> laurenz.a...@cybertec.at wrote: > >> b...@yugabyte.com wrote: >> >> …Internet Search turned up this 2019 post by Laurenz Albe—but nothing else >> at all. >> >> https://www.cybertec-postgresql.com/en/triggers-to-enforce-constraints >>

Re: Deferred constraint trigger semantics

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> Oops. I did a copy-and-paste error on going from my test env. to email and >> missed out the "deferral" that I'd intended. For completeness, here's the >> test that I meant: >> >> create constraint trigger trg >> after ins

Re: Restricting user to see schema structure

> adrian.kla...@aklaver.com wrote: > >> neerajmr12...@gmail.com wrote: >> >> Is there anyway that we can restrict a user from seeing the schema >> structure. I can restrict the user from accessing the data in the schema but >> the user is still able to see the table names and what all columns a

Re: Deferred constraint trigger semantics

> postgres.ro...@gmail.com wrote: > > It adds the "initially deferred" decoration to the "create constraint > trigger" statement. This is (still) the result: > > INFO: trg fired. new.v = 10, n = 5 > INFO: trg fired. new.v = 20, n = 5 > INFO: trg fired. new.v = 30, n = 5 > INFO: trg fired. ne

Re: Restricting user to see schema structure

> david.g.johns...@gmail.com wrote: > >> adrian.kla...@aklaver.com wrote: >> >>> b...@yugabyte.com wrote: >>> >>> I've prototyped this scheme. It seems to work as designed. A client that >>> connects with psql (or any other tool) can list the API functions and >>> whatever \df and \sf show. (n

Re: Restricting user to see schema structure

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> However, the design decision that, way back when, leads to this outcome does >> surprise me. The principle of least privilege insists that (in the database >> regime) you can create users that can do exactly and only what t

Re: Restricting user to see schema structure

> t...@sss.pgh.pa.us wrote: > >> b...@yugabyte.com writes: >> >> Maybe this entire discussion is moot when hackers can read the C code of >> PG's implementation… > > We have pretty much no interest in revisiting that design choice, even if > doing so wouldn't likely break a couple decades' wor

Re: Deferred constraint trigger semantics

> laurenz.a...@cybertec.at wrote: > >> b...@yugabyte.com wrote: >> >> …I have always understood that (in Postgres and any respectable RDBMS) >> commits in a multi-session environment are always strictly >> serialized—irrespective of the transaction's isolation level. Am I correct >> to assume

Re: Restricting user to see schema structure

> neerajmr12...@gmail.com wrote: > > I am using pgAdmin . I have a database 'db' and it has got 2 schemas > 'schema1' and 'schema2', I have created some views in schema2 from tables of > schema1. I have created a new user and granted connection access to database > and granted usage on tables

Re: Restricting user to see schema structure

> neerajmr12...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> What exactly do you mean by "have created a new user and granted connection >> access to database"? As I understand it, there's no such thing. I mentioned >> a simple test in my earlier email that showed that any user (with no

Re: Deferred constraint trigger semantics

> laurenz.a...@cybertec.at wrote: > > Be "seeing" I didn't mean "show to the user". I mean that the code that > implements PostgreSQL constraints takes uncommitted data into account. > > The documentation describes that for the case of uniqueness in some detail: > > https://www.postgresql.org/d

Re: Deferred constraint trigger semantics

> laurenz.albe@cybertec.atwrote: > >> b...@yugabyte.com wrote: > >> >> …I tried this: >> >> create table t( >> k serial primary key, >> v int not null, >> constraint t_v_unq unique(v) initially deferred); Here's a better test: -- BLUE session start transaction isolation level read commi

Re: Restricting user to see schema structure

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >>> neerajmr12...@gmail.com wrote: >>> >>> ... >> >> What exactly do you mean by "have created a new user and granted connection >> access to database"? As I understand it, there's no such thing. I mentioned >> a simple test

Re: Restricting user to see schema structure

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >>> adrian.kla...@aklaver.com wrote: >>> b...@yugabyte.com wrote: > neerajmr12...@gmail.com wrote: > > ... What exactly do you mean b

Re: Restricting user to see schema structure

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> Right, I see the importance of this now. I now realize that when a database >> is created, CONNECT on it is automatically granted to PUBLIC. But there's no >> mention of this (or what to read to learn that this is the case)

Re: Restricting user to see schema structure

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> The paragraph describes very surprising behavior in the present era of >> "secure by default". The sentence "For maximum security..." at the end >> emphasizes this and has you go to some effort (CREATE and REVOKE in the same

"Join Postgres on Slack" ?

Forgive me if this is the wrong address. I went to https://postgresteam.slack.com/join/signup but found that my email address has an unknown domain. It says "Don’t have an email address from one of those domains? Contact the workspace administrator a

Re: "Join Postgres on Slack" ?

david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> …I went to https://postgresteam.slack.com/join/signup but found that my >> email address has an unknown domain. It says "Don’t have an email address >> from one of those domains? Contact the workspace administrator at Postgre

Re: Is it possible to index "deep" into a JSONB column?

> shaheedha...@gmail.com wrote: > > Suppose I have a JSONB field called "snapshot". I can create a GIN > index on it like this: > > create index idx1 on mytable using gin (snapshot); > > In principle, I believe this allows index-assisted access to keys and > values nested in arrays and inner ob

'{"x": 42, "y": null}'::jsonb != '{"x": 42}'::jsonb ... Really?

Here’s the minimal testcase: do $body$ declare j1 constant jsonb not null := '{"x": 42, "y": null}'; j2 constant jsonb not null := '{"x": 42 }'; predicate_1 constant boolean not null := (j1->>'y' is null) AND (j2->>'y' is null); predicate_2 constant boolea

Re: '{"x": 42, "y": null}'::jsonb != '{"x": 42}'::jsonb ... Really?

> david.g.johns...@gmail.com wrote: > > b...@yugabyte.com wrote: > >> declare >> j1 constant jsonb not null := '{"x": 42, "y": null}'; >> j2 constant jsonb not null := '{"x": 42 }'; >> ... >> (j1->>'y' is null) > > This produces a JSON Null which when asked

"A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one"

Does the “Tip” call-out box, from which the “Subject” here is copied, and the larger story that I copied below, apply even when the executable section of the block statement in question does nothing at all that could be rolled back? This is my block: begin year_as_int := year_as_text; excepti

Re: "A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one"

> pavel.steh...@gmail.com wrote: > >> b...@yugabyte.com napsal: >> >> Does the “Tip” call-out box, from which the “Subject” here is copied, and >> the larger story that I copied below, apply even when the executable section >> of the block statement in question does nothing at all that could be

Re: "A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one"

> x...@thebuild.com wrote: > > Isn't one of the reasons for the savepoint (in fact, the principal reason) to > reset the connection back to non-error state so that execution can continue? > In that case, it really does need to create the savepoint at the start of the > block, regardless of what

ERROR: failed to find conversion function from key_vals_nn to record[]

I’ve copied a self-contained testcase below. Is the error that the "as intended" test causes due to a known limitation—or even a semantic dilemma that I'm failing to spot? Or might it be due to a bug? If you're interested, the testcase rests on this thinking: Many SQL experts claim that the RDB

Re: ERROR: failed to find conversion function from key_vals_nn to record[]

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> I’ve copied a self-contained testcase below. Is the error that the "as >> intended" test causes due to a known limitation—or even a semantic dilemma >> that I'm failing to spot? Or might it be due to a bug? > > I read the

Re: Operators on ranges with a domain subtype do not implicitly cast operands of the domain's base type

> dpopow...@artandlogic.com wrote: > > I'm using PG 14 and have an application using a custom range with a custom > domain subtype. My problem: PG does not do an implicit cast from the domain's > base type when used with range operators. I hit what looks to be

Re: ERROR: failed to find conversion function from key_vals_nn to record[]

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> This is what the doc promises. But how can you see it as anything but a bug? >> The subquery evaluates to "null", and only then is the attempt made to >> create a new row which self-evidently violates the domain's constrain

Re: ERROR: failed to find conversion function from key_vals_nn to record[]

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> Can anybody show me an implementation of a realistic use case that follows >> proper practice — like "every table must a primary key", "a foreign key must >> refer to a primary key", and "joins may be made only "on" columns

Re: '{"x": 42, "y": null}'::jsonb != '{"x": 42}'::jsonb ... Really?

> mmonc...@gmail.com wrote: > > yeah. I would expect for json or jsonb, two values, "a, b", "a" is distinct > from "b" should give the same answer as "a::text is distinct from b::text". > >> t...@sss.pgh.pa.us wrote: >> >>> b...@yugabyte.

Re: ISBN (was: '{"x": 42, "y": null}'::jsonb != '{"x": 42}'::jsonb ... Really?)

> hjp-pg...@hjp.at wrote: > >> Bryn wrote: >> >> "isbn" — string >> values must be unique across the entire set of documents (in other words, it >> defines the unique business key); values must have this pattern: >> >> « ^[0-9]{3}-[0-9]{1}-[0-9]{2}-[0-9]{6}-[0-9]{1}$ » > > Completely off-topic

Re: '{"x": 42, "y": null}'::jsonb != '{"x": 42}'::jsonb ... Really?

> haram...@gmail.com wrote > >> b...@yugabyte.com wrote: >> >> I implemented two complementary functions: >> >> —"no_null_keys()" checks that a "jsonb" value has no occurrences of « "some >> key": null » >> >> —"strip_null_keys()" removes « "some key": null » occurrences from a "jsonb" >> val

Outer joins and NULLs (old subject "ERROR: failed to find conversion function from key_vals_nn to record[]")

> b...@yugabyte.com wrote: > >> david.g.johns...@gmail.com wrote: >> >>> b...@yugabyte.com wrote: >>> >>> Can anybody show me an implementation of a realistic use case that follows >>> proper practice — like "every table must a prim

Re: '{"x": 42, "y": null}'::jsonb != '{"x": 42}'::jsonb ... Really?

> jian.universal...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> /* — START OF SPEC —— */ >> >> The document's top-level object may use only these keys: >> >> ... >> >> And the meaning of *not required* is simply "no information is available for >> this

Re: '{"x": 42, "y": null}'::jsonb != '{"x": 42}'::jsonb ... Really?

> sham...@gmx.net wrote: > >> b...@yugabyte.com wrote: >> >> Am I missing a use case where an object with a key-value pair with a JSON >> null value is meaningfully different from one where the key is simply absent? > > It seems the JSON specification doesn't actually define equality. But the

Re: Outer joins and NULLs (old subject "ERROR: failed to find conversion function from key_vals_nn to record[]")

> david.g.johns...@gmail.com wrote: > > Allowing domains to be defined as not null at this point is simply something > that we don't support but don't error out upon in the interest of backward > compatibility. (IMO, the documentation is not this strongly worded.) It, as > you note, has some co

User's responsibility when using a chain of "immutable" functions?

I’ve copied my self-contained testcase at the end. I create three functions, marking each of them "immutable". "f1()" simply returns the manifest constant 'dog'. So it seems perfectly honest to mark it as I did. "f2()" simply returns "f1()"—so, here too, it seems that I'm being honest. But I do

Re: User's responsibility when using a chain of "immutable" functions?

> david.g.johns...@gmail.com wrote: > >> x...@thebuild.com wrote: >> >>> b...@yugabyte.com wrote: >>> >>> Should I simply understand that when I have such a dynamic dependency chain >>> of "immutable" functions, and should I drop and re-create the function at >>> the start of the chain, then a

Re: User's responsibility when using a chain of "immutable" functions?

> x...@thebuild.com wrote: > > I think "not allowed" is putting it too strongly. It would be a bit much to > ask that every single user-written immutable function be 100% perfect when it > is rolled out, and never have to fix any bugs in them. However, you > definitely *do* have to understand t

Re: User's responsibility when using a chain of "immutable" functions?

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> When I drop the first link, f1() in the chain of "immutable" functions, I >> see that I cannot invoke f(2) because it now complains that f1() doesn't >> exist. This surprises me because, after all, the result of f2() is now

Re: User's responsibility when using a chain of "immutable" functions?

> david.g.johns...@gmail.com wrote: > > b...@yugabyte.com wrote: >> >> Meanwhile. I'll appeal for some pointers to what I should read... > > I tend not to search...or at least that isn't my first (or at least only) > recourse. The pg/pgsql chapter has a subchapter named "Plan Caching": > > htt

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 ob

Re: Seems to be impossible to set a NULL search_path

I cannot force the use of fully qualified names by setting a > null search_path: I could set the attribute of my subprogram to "pg_catalog". > » > > So Bryn Llewellyn does not seem to be concerned about that. Thanks, all, for your replies. I'd assumed that the arguments of

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 precede

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 beha

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 tr

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 vi

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). > > https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIER

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. > > https://www.postgr

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

> 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

> 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 look

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

PL/pgSQL: « arr[j].a := v » works fine in PG Version 14.4, fails to compile in Version 11.2. Which version brought the fix?

I copied my testcase at the end. It runs OK and produces the output that I expect using PG Version 14.4. But using Version 11.9 (and earlier 11 sub-versions), it fails to compile with this error: syntax error at or near "." lhs[j].a := rhs[j].a; ^ If I comment out the "Ideal approach" loop

Re: PL/pgSQL: « arr[j].a := v » works fine in PG Version 14.4, fails to compile in Version 11.2. Which version brought the fix?

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> I copied my testcase at the end. It runs OK and produces the output that I >> expect using PG Version 14.4. But using Version 11.9 (and earlier 11 >> sub-versions), it fails to compile with this error: >> >> syntax error a

« The PL/pgSQL interpreter parses the function's source text and produces an internal binary instruction tree... »

The subject line is copied from "PL/pgSQL under the Hood" (https://www.postgresql.org/docs/current/plpgsql-implementation.html). It implies the question: « What does the term "parse" mean? » I couldn't find more than what I quoted. Have I missed something? Anyway, I tried some tests. Here's an

Re: « The PL/pgSQL interpreter parses the function's source text and produces an internal binary instruction tree... »

> x...@thebuild.com wrote: > >> b...@yugabyte.com wrote: >> >> Is this expected? > > Yes. This isn't a bug… Database objects (such as tables and columns) are > left as identifiers until they are executed, because that is the point at > which a plan for those statements is created. The other

Re: « The PL/pgSQL interpreter parses the function's source text and produces an internal binary instruction tree... »

> t...@sss.pgh.pa.us wrote: > >> x...@thebuild.com wrote: >> >> This isn't a bug. > > It's actually a feature… > > Having said that, there are certainly aspects of what happens when in plpgsql > that don't have a lot of justification other than being implementation > artifacts… Thanks, Tom.

Re: « The PL/pgSQL interpreter parses the function's source text and produces an internal binary instruction tree... »

> x...@thebuild.com wrote: > >> b...@yugabyte.com wrote: >> >> It's this that surprises me. And it's this, and only this, that I'm asking >> about: might _just_ this be a fixable bug? > > It might be surprising, but it's not a bug. You can demonstrate it with a > very small test case… ["create

Surprisingly forgiving behavior when a case expression is terminated with "end case"

The account of the CASE expression here: https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-CASE says that it's terminated with the keyword END (just as I've always understood)—i.e. not with the PL/pgSQL CASE statement's END CASE. Moreover CASE is a reserved word—as a

Re: Surprisingly forgiving behavior when a case expression is terminated with "end case"

t...@sss.pgh.pa.us wrote: > I think we've spent a great deal of blood, sweat, and tears making that so, > or as nearly so as we could. We will in fact take any keyword after "AS", and > in recent versions many non-reserved keywords will work that way without "AS". > > (Mind you, I think the SQL

Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

My code example ended up quite big—so I'll show it to you all only if you ask. But it's easy to describe. My script does this: 1. It creates three non-superuser roles: "data", "code", and "client". 2. It creates a text-book masters-and-details table pair with owner "data". Each table has the ob

Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

david.g.johns...@gmail.com wrote: > …you should spend some time making a smaller code example that still shows > the desired behavior but can be easily read and executed by others. In > particular, your description of simply returning NULL for all triggers seems > suspect. If only two of the ei

Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

> karsten.hilb...@gmx.net wrote: > >> b...@yugabyte.com wrote: >> >> I'll be happy to make a smaller example. It will, however, need to create… >> After all, how would I know which of the eight to skip while I don't know >> the intended rules for the current_role? > > You'd certainly start out

Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

> david.g.johns...@gmail.com wrote: > > You are correct that the behavior here is not explicitly documented [where] I > would expect to find it. > > My expectation is that the trigger owner is the context in which the trigger > function is executed. Table owners can grant the ability to other r

Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

> rjuju...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> Nobody has told me how an outsider like me can deliver such a .zip file, >> together with its typographically nuanced external documentation… > > You mentioned previously that "Email attachments don't make it to the archive > for

Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

david.g.johns...@gmail.com wrote:My code example ended up quite big—so I'll show it to you all only if you ask. But it's easy to describe. My script does this: 1. It creates three non-superuser roles: "data", "code", and "client". 2. It creates a text-book masters-and-details table pair with o

Re: Can I get the number of results plus the results with a single query?

>>> p...@easesoftware.com wrote: >>> >>> Currently I’m doing this with two queries such as: >>> >>> SELECT COUNT(*) FROM table WHERE …. expression … >>> SELECT * FROM table WHERE …. expression … >>> >>> But this requires two queries. Is there a way to do th

Re: Can I get the number of results plus the results with a single query?

> hjp-pg...@hjp.at wrote: > > That's not quite what I meant. I meant "I don't think there can be what you > want with just one query", > > The OP wants some kind of progress indicator. To be useful, such an indicator > should be approximately linear in time. I.e. if your query returns 1 row

Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

> b...@yugabyte.com wrote: > >> david.g.johns...@gmail.com wrote: >> >> You are correct that the behavior here is not explicitly documented [where] >> I would expect to find it. >> >> My expectation is that the trigger owner is the context in which the trigger >> function is executed. Table ow

  1   2   3   4   >