Horton

Thread

  1. DISABLE TRIGGER doc wrong?

    Dominique Devienne <ddevienne@gmail.com> — 2025-08-25T16:19:19Z

    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

  2. Re: DISABLE TRIGGER doc wrong?

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-08-25T16:24:50Z

    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

  3. Re: DISABLE TRIGGER doc wrong?

    Dominique Devienne <ddevienne@gmail.com> — 2025-08-25T16:33:39Z

    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.

  4. Re: DISABLE TRIGGER doc wrong?

    Dominique Devienne <ddevienne@gmail.com> — 2025-08-25T17:12:50Z

    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

  5. Re: DISABLE TRIGGER doc wrong?

    Pavel Luzanov <p.luzanov@postgrespro.ru> — 2025-08-25T17:13:50Z

    On 25.08.2025 19:19, Dominique Devienne wrote:
> Fromhttps://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?
Simple experiment shows that it is still up to 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

  6. Re: DISABLE TRIGGER doc wrong?

    Dominique Devienne <ddevienne@gmail.com> — 2025-08-25T17:24:06Z

    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

  7. Re: DISABLE TRIGGER doc wrong?

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-08-25T17:33:35Z

    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

  8. Re: DISABLE TRIGGER doc wrong?

    Dominique Devienne <ddevienne@gmail.com> — 2025-08-26T07:01:32Z

    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

  9. Re: DISABLE TRIGGER doc wrong?

    Ron <ronljohnsonjr@gmail.com> — 2025-08-26T12:53:37Z

    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!

  10. Re: DISABLE TRIGGER doc wrong?

    Dominique Devienne <ddevienne@gmail.com> — 2025-08-26T13:01:16Z

    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.

  11. Re: DISABLE TRIGGER doc wrong?

    Ron <ronljohnsonjr@gmail.com> — 2025-08-26T13:08:35Z

    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!

  12. Re: DISABLE TRIGGER doc wrong?

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-08-26T13:57:22Z

    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