Обсуждение: DISABLE TRIGGER doc wrong?
or I'm reading it wrong? --DD From https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DESC-DISABLE-ENABLE-TRIGGER: > Disabling or enabling internally generated constraint triggers requires superuser privileges We were disabling triggers as the owner of the table just fine, no SUPERUSER involved. So is the doc out-of-date? PS: By mistake... We were ALTER TABLE {}.{} DISABLE TRIGGER ALL when we meant ALTER TABLE {}.{} DISABLE TRIGGER USER
On 8/25/25 09:19, Dominique Devienne wrote: > or I'm reading it wrong? --DD > > From https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DESC-DISABLE-ENABLE-TRIGGER: >> Disabling or enabling internally generated constraint triggers requires superuser privileges > > We were disabling triggers as the owner of the table just fine, no > SUPERUSER involved. So is the doc out-of-date? From the sentence before the above: "... internally generated constraint triggers, such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints)." > > PS: By mistake... We were ALTER TABLE {}.{} DISABLE TRIGGER ALL > when we meant ALTER TABLE {}.{} DISABLE TRIGGER USER > > -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, Aug 25, 2025 at 6:24 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 8/25/25 09:19, Dominique Devienne wrote: > > or I'm reading it wrong? --DD > > > > From https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DESC-DISABLE-ENABLE-TRIGGER: > >> Disabling or enabling internally generated constraint triggers requires superuser privileges > > > > We were disabling triggers as the owner of the table just fine, no > > SUPERUSER involved. So is the doc out-of-date? > > From the sentence before the above: > > "... internally generated constraint triggers, such as those that are > used to implement foreign key constraints or deferrable uniqueness and > exclusion constraints)." And? I don't see your point Adrian. Has no bearing on the SUPERUSER requirement, in my reading.
On Mon, Aug 25, 2025 at 6:33 PM Dominique Devienne <ddevienne@gmail.com> wrote: > On Mon, Aug 25, 2025 at 6:24 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > On 8/25/25 09:19, Dominique Devienne wrote: > > > or I'm reading it wrong? --DD > > > > > > From https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DESC-DISABLE-ENABLE-TRIGGER: > > >> Disabling or enabling internally generated constraint triggers requires superuser privileges > > > > > > We were disabling triggers as the owner of the table just fine, no > > > SUPERUSER involved. So is the doc out-of-date? > > > > From the sentence before the above: > > > > "... internally generated constraint triggers, such as those that are > > used to implement foreign key constraints or deferrable uniqueness and > > exclusion constraints)." > > And? I don't see your point Adrian. > Has no bearing on the SUPERUSER requirement, in my reading. OK, maybe my initial was poorly worded. Let me try again: We were ALTER TABLE {}.{} DISABLE TRIGGER ALL as the table owner. ALL includes "internally generated constraint triggers", FKs in our case. Yet the doc clearly states one must be SUPERUSER to do that. Thus the doc should say "requires owning the altered table, or have superuser privileges", no? Hope this is a clearer inquiry. Thanks, --DD
On 25.08.2025 19:19, Dominique Devienne wrote:
Simple experiment shows that it is still up to date:From https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DESC-DISABLE-ENABLE-TRIGGER:Disabling or enabling internally generated constraint triggers requires superuser privilegesWe were disabling triggers as the owner of the table just fine, no SUPERUSER involved. So is the doc out-of-date?
postgres@postgres(17.5)=# set session authorization alice;
SET
alice@postgres(17.5)=> create table t (id int primary key, parent_id int references t(id));
CREATE TABLE
alice@postgres(17.5)=> select tgname from pg_trigger where tgrelid = 't'::regclass;
tgname
--------------------------------
RI_ConstraintTrigger_a_1260370
RI_ConstraintTrigger_a_1260371
RI_ConstraintTrigger_c_1260372
RI_ConstraintTrigger_c_1260373
(4 rows)
alice@postgres(17.5)=> alter table t disable trigger "RI_ConstraintTrigger_a_1260370";
ERROR: permission denied: "RI_ConstraintTrigger_a_1260370" is a system trigger
alice@postgres(17.5)=> \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres@postgres(17.5)=# alter table t disable trigger "RI_ConstraintTrigger_a_1260370";
ALTER TABLE
-- Pavel Luzanov Postgres Professional: https://postgrespro.com
On Mon, Aug 25, 2025 at 7:13 PM Pavel Luzanov <p.luzanov@postgrespro.ru> wrote: > On 25.08.2025 19:19, Dominique Devienne wrote: > Simple experiment shows that it is still up to date: > alice@postgres(17.5)=> alter table t disable trigger "RI_ConstraintTrigger_a_1260370"; > ERROR: permission denied: "RI_ConstraintTrigger_a_1260370" is a system trigger > alice@postgres(17.5)=> \c - postgres > You are now connected to database "postgres" as user "postgres". > postgres@postgres(17.5)=# alter table t disable trigger "RI_ConstraintTrigger_a_1260370"; > ALTER TABLE We were using ALL, successfully but incorrectly, and are now using USER, successfully. Not a named constraint trigger as you did. Could it be ALL implicitly excludes such constraint triggers, when not SUPERUSER? And thus, as the table owner, ALL == USER, implicitly? If that's the case, then the doc still needs clarifications IMHO. --DD
On 8/25/25 10:24, Dominique Devienne wrote: > On Mon, Aug 25, 2025 at 7:13 PM Pavel Luzanov <p.luzanov@postgrespro.ru> wrote: >> On 25.08.2025 19:19, Dominique Devienne wrote: >> Simple experiment shows that it is still up to date: > >> alice@postgres(17.5)=> alter table t disable trigger "RI_ConstraintTrigger_a_1260370"; >> ERROR: permission denied: "RI_ConstraintTrigger_a_1260370" is a system trigger >> alice@postgres(17.5)=> \c - postgres >> You are now connected to database "postgres" as user "postgres". >> postgres@postgres(17.5)=# alter table t disable trigger "RI_ConstraintTrigger_a_1260370"; >> ALTER TABLE > > We were using ALL, successfully but incorrectly, and are now using > USER, successfully. > Not a named constraint trigger as you did. Could it be ALL implicitly > excludes such constraint triggers, when not SUPERUSER? And thus, as > the table owner, ALL == USER, implicitly? If that's the case, then the > doc still needs clarifications IMHO. --DD > > I am not seeing it: select version(); version ----------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 17.6 (Ubuntu 17.6-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit create table test_table_parent(id integer primary key, fld varchar); create table test_table_child(id integer primary key, fld_child varchar, parent_id integer references test_table_parent on update cascade); \dt+ test_table_child List of tables Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+------------------+-------+---------+-------------+---------------+------------+------------- public | test_table_child | table | db_user | permanent | heap | 8192 bytes | NULL \du+ db_user List of roles Role name | Attributes | Description -----------+------------+------------- db_user | | alter table test_table_child disable trigger all; ERROR: permission denied: "RI_ConstraintTrigger_c_121933" is a system trigger -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, Aug 25, 2025 at 7:33 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 8/25/25 10:24, Dominique Devienne wrote: > > On Mon, Aug 25, 2025 at 7:13 PM Pavel Luzanov <p.luzanov@postgrespro.ru> wrote: > >> On 25.08.2025 19:19, Dominique Devienne wrote: > >> Simple experiment shows that it is still up to date: Thanks Pavel. Indeed. See below. > I am not seeing it: Thanks Adrian. Indeed, it was my mistake. I thought the 4 tables we had USER triggers on also had FKs, but I was mistaken. I just tried on the 5th (and last) one we're adding triggers on, which does have an FK, and I do get the documented (and verified by you guys) error: ERROR: permission denied: "RI_ConstraintTrigger_c_1226298044" is a system trigger (yes, that's a large OID... For a 1 year old DB) I apologize for wasting your time. Thanks, --DD
On Tue, Aug 26, 2025 at 3:01 AM Dominique Devienne <ddevienne@gmail.com> wrote:
[snip]
ERROR: permission denied: "RI_ConstraintTrigger_c_1226298044" is a
system trigger
(yes, that's a large OID... For a 1 year old DB)
PG's OID allocation of "user-land" OIDs doesn't start at 16384 anymore. And it can seem quite random to someone who doesn't dig into the source code. For example, a couple of years ago, I installed PG14 on 5 newly-built servers. The OIDs of the ten databases I created on them were:
Srv 1: 19762693, 544452602
Srv 2: 1002727, 11988067
Srv 3: 16388, 509694991
Srv 4: 16387, 1805148571
Srv 5: 16388, 3046645364
(Too bad Postgresql doesn't have CREATED_ON timestamp, CREATED_BY oid, MODIFIED_ON timestamp and MODIFIED_BY oid fields in pg_database and pg_class,to verify whether my memory is correct. The counter-argument when I requested such fields was "pg_dump/pg_upgrade creates new objects, so it's not _really_ when they were created" and "you don't _really_ need those fields".)
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
On Tue, Aug 26, 2025 at 2:54 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote: > On Tue, Aug 26, 2025 at 3:01 AM Dominique Devienne <ddevienne@gmail.com> wrote: >> ERROR: permission denied: "RI_ConstraintTrigger_c_1226298044" is a >> (yes, that's a large OID... For a 1 year old DB) > > PG's OID allocation of "user-land" OIDs doesn't start at 16384 anymore. This is a 17 cluster. So not even 1 year old then. So getting to 1.2B OIDs in 10 months, doesn't bode well for the longevity of that cluster. But we're getting OT here. This is not a "production" server, lots of CIs and manual testing there.
On Tue, Aug 26, 2025 at 9:01 AM Dominique Devienne <ddevienne@gmail.com> wrote:
On Tue, Aug 26, 2025 at 2:54 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> On Tue, Aug 26, 2025 at 3:01 AM Dominique Devienne <ddevienne@gmail.com> wrote:
>> ERROR: permission denied: "RI_ConstraintTrigger_c_1226298044" is a
>> (yes, that's a large OID... For a 1 year old DB)
>
> PG's OID allocation of "user-land" OIDs doesn't start at 16384 anymore.
This is a 17 cluster. So not even 1 year old then.
So getting to 1.2B OIDs in 10 months,
doesn't bode well for the longevity of that cluster.
But we're getting OT here.
This is not a "production" server, lots of CIs and manual testing there.
Like I said, it's not completely linear. The real question, though, is whether PG looks for gaps in oid allocation once it wants to try and allocate an oid of uint32 max.
I bet it does. If it doesn't... pg_dump/pg_restore, baby!
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Ron Johnson <ronljohnsonjr@gmail.com> writes: > Like I said, it's not completely linear. The real question, though, is > whether PG looks for gaps in oid allocation once it wants to try and > allocate an oid of uint32 max. OID counter wraparound is not particularly a problem. regards, tom lane