Do you think in a later version you can show non-system constraint triggers that have been created by the user via:
CREATE CONSTRAINT TRIGGER ?

The current query pgAdmin uses to get trigger function information is:

SELECT t.oid, t.*, relname, nspname, des.description
FROM pg_trigger t
JOIN pg_class cl ON cl.oid=tgrelid
JOIN pg_namespace na ON na.oid=relnamespace
LEFT OUTER JOIN pg_description des ON des.objoid=t.oid
WHERE NOT tgisconstraint
AND tgrelid = xxxx:id
ORDER BY tgname

where xxxx is the oid of the table.

You could easily get the same result, which would include user defined constraint triggers by using:

SELECT t.oid, t.*, relname, nspname, des.description
FROM pg_trigger t
JOIN pg_class cl ON cl.oid=tgrelid
JOIN pg_namespace na ON na.oid=relnamespace
LEFT OUTER JOIN pg_description des ON des.objoid=t.oid
WHERE tgconstrrelid = 0:id
AND tgrelid = xxxx:id
ORDER BY tgname

Obviously you may want to change the reverse engineered SQL accordingly for constraint triggers.

Cheers
Donald Fraser

Donald Fraser wrote:
> Do you think in a later version you can show non-system constraint
> triggers that have been created by the user via:
> CREATE CONSTRAINT TRIGGER ?
Definitively not. Creating constraint triggers directly is horribly
outdated (pre-7.3), and still available for backwards compatibility
only. Use contrib/adddepend to fix your database.

Regards,
Andreas


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

> Donald Fraser wrote:
> > Do you think in a later version you can show non-system constraint
> > triggers that have been created by the user via:
> > CREATE CONSTRAINT TRIGGER ?

Andreas Pflug wrote:
> Definitively not. Creating constraint triggers directly is horribly
> outdated (pre-7.3), and still available for backwards compatibility
> only. Use contrib/adddepend to fix your database.

I'm not talking about old versions of PostgreSQL that have been upgraded to
later versions.
Prior to version 8.x.x all AFTER trigger events were deferred until the end
of all statements for the current transaction.
As of version 8.x.x AFTER trigger events occur after each statement in the
transaction - basically they are not deferred any more.
Unfortunately, for some of us, having deferred AFTER trigger events was a
nice feature.
There are some things you simply cannot do unless the trigger event is
deferred! I shouldn't need to qualify that statement, but the fact that
referential integrity requires them points out one example of when its nice
to have deferred trigger events.
Moving forward I am trying to upgrade a 7.4.x system to 8.0.x and I cannot
upgrade it without converting a lot of trigger functions from standard AFTER
trigger events to constraint trigger events, simply because I need them to
be deferred until the end of the transaction.
So after converting these "normal" trigger functions to "constraint" trigger
functions they disappear from view in pgAdmin...
They are not system generated trigger functions, they are not missing any
dependencies, they are there in that form because I need them to be there
and I created them using a PostgreSQL command "CREATE CONSTRAINT TRIGGER..."
I know that the documentation says "...It is not intended for general use."
Personally I don't see what's wrong with deferred trigger events, they are
useful, PostgreSQL already supports them, there is no other way of creating
deferred events yet!

I guess I will have to modify pgAdmin myself to achieve this.

Regards
Donald Fraser


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Donald Fraser wrote:
>> Donald Fraser wrote:
>>
>>> Do you think in a later version you can show non-system constraint
>>> triggers that have been created by the user via:
>>> CREATE CONSTRAINT TRIGGER ?
>>>
>
> Andreas Pflug wrote:
>
>> Definitively not. Creating constraint triggers directly is horribly
>> outdated (pre-7.3), and still available for backwards compatibility
>> only. Use contrib/adddepend to fix your database.
>>
>
> I'm not talking about old versions of PostgreSQL that have been upgraded to
> later versions.
> Prior to version 8.x.x all AFTER trigger events were deferred until the end
> of all statements for the current transaction.
> As of version 8.x.x AFTER trigger events occur after each statement in the
> transaction - basically they are not deferred any more.
> Unfortunately, for some of us, having deferred AFTER trigger events was a
> nice feature.
> There are some things you simply cannot do unless the trigger event is
> deferred! I shouldn't need to qualify that statement, but the fact that
> referential integrity requires them points out one example of when its nice
> to have deferred trigger events.
> Moving forward I am trying to upgrade a 7.4.x system to 8.0.x and I cannot
> upgrade it without converting a lot of trigger functions from standard AFTER
> trigger events to constraint trigger events, simply because I need them to
> be deferred until the end of the transaction.
> So after converting these "normal" trigger functions to "constraint" trigger
> functions they disappear from view in pgAdmin...
> They are not system generated trigger functions, they are not missing any
> dependencies, they are there in that form because I need them to be there
> and I created them using a PostgreSQL command "CREATE CONSTRAINT TRIGGER..."
> I know that the documentation says "...It is not intended for general use."
> Personally I don't see what's wrong with deferred trigger events, they are
> useful, PostgreSQL already supports them, there is no other way of creating
> deferred events yet!
>

I'm still disinclined to offer this to the normal pgadmin user.
constraint triggers are internal implementation details, subject to
change without prior notice blablabla. I didn't follow the deferred
constraint discussion back then, but I really wonder why there's no such
option for CREATE TRIGGER. How about contacting pgsql-hackers, maybe
this could go into 8.2.

Regards,
Andreas


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Andreas Pflug wrote:
> Donald Fraser wrote:
> >> Donald Fraser wrote:
> >>
> >>> Do you think in a later version you can show non-system constraint
> >>> triggers that have been created by the user via:
> >>> CREATE CONSTRAINT TRIGGER ?
> >>>
> >
> > Andreas Pflug wrote:
> >
> >> Definitively not. Creating constraint triggers directly is horribly
> >> outdated (pre-7.3), and still available for backwards compatibility
> >> only. Use contrib/adddepend to fix your database.
> >>
> >
> > I'm not talking about old versions of PostgreSQL that have been upgraded
to
> > later versions.
> > Prior to version 8.x.x all AFTER trigger events were deferred until the
end
> > of all statements for the current transaction.
> > As of version 8.x.x AFTER trigger events occur after each statement in
the
> > transaction - basically they are not deferred any more.
> > Unfortunately, for some of us, having deferred AFTER trigger events was
a
> > nice feature.
> > There are some things you simply cannot do unless the trigger event is
> > deferred! I shouldn't need to qualify that statement, but the fact that
> > referential integrity requires them points out one example of when its
nice
> > to have deferred trigger events.
> > Moving forward I am trying to upgrade a 7.4.x system to 8.0.x and I
cannot
> > upgrade it without converting a lot of trigger functions from standard
AFTER
> > trigger events to constraint trigger events, simply because I need them
to
> > be deferred until the end of the transaction.
> > So after converting these "normal" trigger functions to "constraint"
trigger
> > functions they disappear from view in pgAdmin...
> > They are not system generated trigger functions, they are not missing
any
> > dependencies, they are there in that form because I need them to be
there
> > and I created them using a PostgreSQL command "CREATE CONSTRAINT
TRIGGER..."
> > I know that the documentation says "...It is not intended for general
use."
> > Personally I don't see what's wrong with deferred trigger events, they
are
> > useful, PostgreSQL already supports them, there is no other way of
creating
> > deferred events yet!
> >

> I'm still disinclined to offer this to the normal pgadmin user.
> constraint triggers are internal implementation details, subject to
> change without prior notice blablabla. I didn't follow the deferred
> constraint discussion back then, but I really wonder why there's no such
> option for CREATE TRIGGER. How about contacting pgsql-hackers, maybe
> this could go into 8.2.

I'm hearing you.
It seems to be a problem with our project and PostgerSQL - using
non-standard features.
A feature which has been dropped in 8.1.x is the SYSID from CREATE USER. The
syntax is there but it doesn't actually use the number any more. The reason
is oids are now used for dependency checking. Unfortunately our project
depends heavily on the use of SYSID so an upgrade to 8.1.x want happen in a
hurry...:-(

I've already hacked pgAdmin to display manually created constraint triggers.
The property display adds properties: "Constraint Name", "Deferred?" and
"Initially deferred?" but only when the trigger is a manually created
constraint trigger.
It correctly displays the reverse engineered SQL.
I left the creating of a trigger using the GUI "New Trigger" option as is,
i.e. you can only create standard triggers.
So basically only those that have manually created constraint triggers will
see them.

If you want the diff of the two files that got changed I'll send them, no
problem.

I'll have a go at contacting pgsql-hackers, but my guess is that constraint
triggers will stay, as they are heavily used internally, but the command to
create them manually will eventually be dropped. Hopefully we will have
deferred CHK constraints by that time, which would suffice.

Regards
Donald Fraser


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org