Fedora 36

[Kieran McCusker]

Hi

Is there any timeline for a Fedora 36 repository as it should be released
today?

Many thanks

Kieran

Re: Fedora 36

[Adrian Klaver]

On 5/10/22 09:38, Kieran McCusker wrote:

Hi

Is there any timeline for a Fedora 36 repository as it should be 
released today?


Looks like it is there:

https://yum.postgresql.org/packages/#pg14



Many thanks

Kieran



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

Re: PLPGSQL - extra column existence in trigger

[Peter J. Holzer]

On 2022-05-07 15:02:09 +0200, Durumdara wrote:
> We stored the username in a temporary table which was created by the client
> app.
> With this technique we can log these names in triggers too.
> 
> Now we extend it with user id and later the comp name.
> 
> Because we can update the client applications slowly, some client's tables 
> have
> these one or two extra fields, some not.
> 
> So in the new trigger we can't load them all with:

How do you get a new trigger on one table but not the new columns on the
other table? Wouldn't you update both at the same time?

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Deferred constraint trigger semantics

[Bryn Llewellyn]

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. Internet Search turned up this 2019 post by 
Laurenz Albe's—but nothing else at all.

https://www.cybertec-postgresql.com/en/triggers-to-enforce-constraints/

(This is why I CC'd you, Laurenz.)

Laurenz described a use case that's best met by a SQL Assertion. But I don't 
know of a single available relational database system that supports this 
feature—though the SQL Standard defines it. (There's been talk of bringing SQL 
Assertion support in Oracle Database for several years. See 
https://community.oracle.com/tech/apps-infra/discussion/4390732/sql-assertions-declarative-multi-row-constraints
 
.
 But I don't know if any progress has been made.)

Laurenz's example treats the semantics as if the deferred constraint trigger 
fires exactly once, the moment before commit, so that it can check that the 
business rule is met and, if so, perform the commit before a concurrent session 
is allowed to commit a change that would, in such a race condition and together 
with the present session's commit, cause rule violation.

Is this a supported use—even though, as I show below, the trigger fires many 
times and produces the same result each time that it fires? And if so, why 
cannot the definition, optionally, be spelled "after... statement" and fire 
that way?

If the use that Laurenz's example shows is supported, then I could have such a 
trigger on every table that participates in a requirement that's tersely 
expressed using a SQL Assertion (as documentation). I'd simply use the same 
trigger function for the deferred constraint trigger on each of those tables. 
And I'd accept the fact that it unnecessarily (and expensively) fired more 
times than it needed to. Because I want to test only the final state, and 
intermediate states might violate the rule that I want to enforce, I need to 
add logic to populate a log (presumably a temporary table) with an ordinary 
trigger, every time any involved table changes, so that I could check the log 
in the constraint trigger's function. When it has N rows, then the function 
should skip the check except on its Nth invocation.

There's an implicit question lurking here: might it be possible to define a new 
ON COMMIT trigger firing point, legal only for a deferred constraint trigger? 
It should fire just once when a multi-statement transaction is committed if one 
or many of the tables, whose triggers share the same function, see changes.

DETAIL
==

I read this in the Current CREATE TRIGGER section: Constraint triggers must be 
AFTER ROW triggers on plain tables.

And sure enough, this attempt:

create constraint trigger trg
after insert on t
for each statement
execute function trg_fn();

causes a syntax error. This implies that the concept is to program a constraint 
that (like a declarative constraint on a table’s column as part of the table's 
definition) has just single-row scope. Yet I discovered, serendipitously (and 
before reading Laurenz's 's post), that an “after row” constraint trigger does 
allow querying the table it's on (and other tables) in the trigger function's 
body. I used this technique in the example that I used to start this thread:

"A transaction cannot be ended inside a block with exception handlers"
https://www.postgresql.org/message-id/40eb34e2-f9ca-4d29-b0be-6df3d4d0f...@yugabyte.com
 


Nobody said that I was doing anything unsupported.

I also used it in "The complete testcase" at the end of my final response in 
that thread:
https://www.postgresql.org/message-id/549eb7db-214e-420c-beef-324ab124d...@yugabyte.com
 


Is my entire concept (and Laurenz's too) fundamentally flawed? Specifically, is 
querying a trigger's base table in a "for each row" trigger fundamentally 
unsound and not supported? (In Oracle Database, it causes the notorious 
"mutating table" runtime error.)

This little test shows what actually happens:

create table t1(k serial primary key, v int not null);
create table t2(k serial primary key, v int not null);

create function trg_fn()
  returns trigger
  language plpgsql
as $body$
declare
  n int not null := 0;
begin
  n := (select count(*) from t1) + (select count(*) from t2);
  raise info 'trg fired. new.v = %, n = %', new.v, n;
  return new;
end;
$body$;

create constraint trigger trg
after insert on t1
for each row
execute function trg_fn();

create constraint trigger trg
after insert on t2
for each row
execute function trg_fn();

set default

Re: Deferred constraint trigger semantics

[David G. Johnston]

On Tue, May 10, 2022 at 5:47 PM Bryn Llewellyn  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 documentation:

"Row-level BEFORE triggers fire immediately before a particular row is
operated on, while row-level AFTER triggers fire at the end of the
statement (but before any statement-level AFTER triggers)."

https://www.postgresql.org/docs/current/trigger-definition.html

David J.

Re: Deferred constraint trigger semantics

[Bryn Llewellyn]

> 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 documentation:
> 
> "Row-level BEFORE triggers fire immediately before a particular row is 
> operated on, while row-level AFTER triggers fire at the end of the statement 
> (but before any statement-level AFTER triggers)."
> 
> https://www.postgresql.org/docs/current/trigger-definition.html

Thanks, David. Those sentences were too deeply buried, in a page with no 
subsections, for me to spot by ordinary searching in the page. The term 
"Row-level BEFORE" trigger subverted my search for "BEFORE EACH ROW" 
trigger—which is the term that I'm used to. This is another lesson for me to 
read every word in what looks like relevant doc, from start to finish, like a 
book. I'm chastened.

The next section, "39.2. Visibility of Data Changes"
https://www.postgresql.org/docs/current/trigger-datachanges.html

says this:

«
* Statement-level triggers follow simple visibility rules: none of the changes 
made by a statement are visible to statement-level BEFORE triggers, whereas all 
modifications are visible to statement-level AFTER triggers.

* The data change (insertion, update, or deletion) causing the trigger to fire 
is naturally not visible to SQL commands executed in a row-level BEFORE 
trigger, because it hasn't happened yet.

* However, SQL commands executed in a row-level BEFORE trigger will see the 
effects of data changes for rows previously processed in the same outer 
command. This requires caution, since the ordering of these change events is 
not in general predictable; an SQL command that affects multiple rows can visit 
the rows in any order.
»

Strangely, the wording here, explicit as it is, makes no mention of what you 
might expect to see in an AFTER EACH ROW trigger. It's a bit of a stretch to 
put the sentences from the previous section that you quoted together with these 
three bullets to conclude this: querying the trigger's base-table's content 
*is* allowed from the trigger's function for all of the five timing points: 
BEFORE and AFTER EACH ROW (not deferred), BEFORE and AFTER EACH STATEMENT  (not 
deferred), and AFTER EACH ROW (deferred to commit time) is indeed supported. 
I'll take this to be the case unless anybody contradicts me.

The mention of unpredictable results in the third bullet in the BEFORE case 
implies that there's no such unpredictability in the AFTER EACH ROW cases. But 
there has to be a subtle caveat here for the deferred constraint trigger when 
the txn changes two or more tables, all of which participate in the query that 
the trigger function issues. I'll assume that you (all) know what I mean. The 
"raise info" output below illustrates my point (n changes from 5 to 8). But 
this seems to be sound inference from the rules that were stated. I'll take 
this, too, to be the case unless anybody contradicts me.

I assume, though, that considering this output that I showed in my original 
mail:

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. new.v = 40, n = 5
INFO:  trg fired. new.v = 50, n = 5
INFO:  trg fired. new.v = 60, n = 8
INFO:  trg fired. new.v = 70, n = 8
INFO:  trg fired. new.v = 80, n = 8

the actual order in which I see the "raise info" output is unpredictable while 
the values shown in each *are* predictable. Can I rely on this rule?

Is there a fundamental reason why a deferred AFTER EACH STATEMENT constraint 
trigger is not allowed? Nothing in what is explained in the "Overview of 
Trigger Behavior" and "Visibility of Data Changes" sections lets me see why the 
present restriction is needed.

Re: Deferred constraint trigger semantics

[David G. Johnston]

On Tue, May 10, 2022 at 7:52 PM Bryn Llewellyn  wrote:

>
> *david.g.johns...@gmail.com  wrote:*
>
> * However, SQL commands executed in a row-level BEFORE trigger will see
> the effects of data changes for rows previously processed in the same outer
> command. This requires caution, since the ordering of these change events
> is not in general predictable; an SQL command that affects multiple rows
> can visit the rows in any order.
> »
>
> Strangely, the wording here, explicit as it is, makes no mention of what
> you might expect to see in an AFTER EACH ROW trigger.
>

Because the previous, quoted, paragraph did that.  Because of the absence
of a comment declaring a guarantee of order means that, like the comment
for the row-level BEFORE trigger, the row-level AFTER row ordering is not
guaranteed (even if one cannot produce a counter-example in today's
codebase).

I'll take this to be the case unless anybody contradicts me.
>

Caveat emptor...?  I wouldn't be surprised that doing so is technically
possible in all cases - as to whether a particular algorithm is sound, to
some extent, isn't something we try to predict.  We do try to describe all
the known interactions though - and let the user work within what those
mean for them.


> The mention of unpredictable results in the third bullet in the BEFORE
> case implies that there's no such unpredictability in the AFTER EACH ROW
> cases.
>

I would not read it that way.  In general, absence of mention of
predictability like this means there is none - that some other sentence
goes into more detail doesn't change that.

But there has to be a subtle caveat here for the deferred constraint
> trigger when the txn changes two or more tables, all of which participate
> in the query that the trigger function issues. I'll assume that you (all)
> know what I mean. The "raise info" output below illustrates my point (n
> changes from 5 to 8).
>

I'm failing to see the deferral aspect of that example.  First statement
finishes, sees the 5 inserts, next statement finishes, sees 3 more
inserts.  Not, both statements finish, triggers fire, triggers see all 8
inserts (which I suspect they will if you actually perform deferral).


> the actual order in which I see the "raise info" output is unpredictable
> while the values shown in each *are* predictable. Can I rely on this rule?
>

Absent a deferred constraint I would say yes.  Your test case for the
deferred constraint, that supposedly allows for the insertion of invalid
data per the specification of the constraint trigger, isn't something I've
worked through yet; and as written reads like a bug report.


> Is there a fundamental reason why a deferred AFTER EACH STATEMENT
> constraint trigger is not allowed? Nothing in what is explained in the
> "Overview of Trigger Behavior" and "Visibility of Data Changes" sections
> lets me see why the present restriction is needed.
>
>
I imagine having to keep around a working set of what are the changed
records is both memory intensive and also problematic should a future
statement make yet more changes to the table.  This is also an area that
the SQL Standard does make rules in.  And given that constraints are
defined per-row everywhere else there is a pull to not push the envelope of
our extension too far.

David J.

Re: Deferred constraint trigger semantics

[Laurenz Albe]

On Tue, 2022-05-10 at 17:46 -0700, Bryn Llewellyn wrote:
> 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.
> Internet Search turned up this 2019 post by Laurenz Albe's—but nothing else 
> at all.
> 
> https://www.cybertec-postgresql.com/en/triggers-to-enforce-constraints/
> 
> (This is why I CC'd you, Laurenz.)

So I guess I should answer.

About the starting paragraph of your mail: Constraint triggers are a syntactic 
leftover
from the way that triggers are implemented in PostgreSQL.  There is different 
syntax now,
but it was decided to leave constraint triggers, since they may have some use.

> [Lots of ruminations and wandering throughts]

Sorry, that was too much for me to comment on - that would require a mid-sized
article.

> Is my entire concept (and Laurenz's too) fundamentally flawed? Specifically, 
> is
> querying a trigger's base table in a "for each row" trigger fundamentally 
> unsound
> and not supported? (In Oracle Database, it causes the notorious "mutating 
> table"
> runtime error.)

My post claims that constraint triggers alone are *not* a sufficient solution to
validate constraints - you need additional locking or SERIALIZABLE isolation to
make that work reliably.

That does not mean that using constraint triggers is unsound or unsupported,
and the fact that Oracle's implementation of transaction isolation is somewhat
shoddy has little impact on that.

Yours,
Laurenz Albe