Any plans on allowing user-defined triggers to be deferrable?

I'd like to ensure that the creation of a department also implies the
creation of two to eight projects; no more, no less:

CREATE TABLE departments (
department text primary key not null
);

CREATE TABLE projects (
project text primary key not null,
department text not null
references departments(department)
on delete cascade
on update cascade
);

So it'd be nice to have an INSERT trigger that's fired on departments at
the end of the transaction to ensure that between two and eight projects
exist for the newly created department.

Is there no way to achieve the above stated goal in the server? Must I
rely on the application to enforce consistency?

Mike Mascari



---------------------------(end of broadcast)---------------------------
TIP 3: 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

re: Any plans on allowing user-defined triggers to be deferrable?

Valentin Militaru wrote:[color=blue]
> You can do that. But first you have to do some optimisations, like:
> add a column id(bigserial) to the departamens table, after which you
> will replace the column department with id_department in the projects
> table. It is an optimisation, as you are dealing with integer, not text.[/color]

Well, that's an argument for surrogate keys, which will invoke a
philosophical war amongst purists that I won't touch...
[color=blue]
> After that, what do you want to achieve? When you are inserting a
> department, should the server insert 2 to 8 blank records in the
> projects table which contain the inserted department? Or do you want not
> to be able to insert a department if there aren't already 2 to 8
> projects containing that department in the projects table?[/color]

I want the database to enforce logical consistency by ensuring that if a
department exists, there are at a minimum two projects and a maximum of
eight projects associated with it. Date & Darwen attribute the
enforcement of such business requirements to database constraints.
PostgreSQL lacks database constraints, but the result can often be
achieved through triggers. But I can't figure out how to enforce
consistency without deferrable triggers and without relying on the
application to maintain consistency, which is not its job.

Mike Mascari

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

re: Any plans on allowing user-defined triggers to be deferrable?

On Tue, 26 Oct 2004, Mike Mascari wrote:
[color=blue]
> I'd like to ensure that the creation of a department also implies the
> creation of two to eight projects; no more, no less:
>
> CREATE TABLE departments (
> department text primary key not null
> );
>
> CREATE TABLE projects (
> project text primary key not null,
> department text not null
> references departments(department)
> on delete cascade
> on update cascade
> );
>
> So it'd be nice to have an INSERT trigger that's fired on departments at
> the end of the transaction to ensure that between two and eight projects
> exist for the newly created department.
>
> Is there no way to achieve the above stated goal in the server? Must I
> rely on the application to enforce consistency?[/color]

Well. It's not exactly meant to be a user facing feature, but check out
CREATE CONSTRAINT TRIGGER.


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

re: Any plans on allowing user-defined triggers to be deferrable?

Stephan Szabo wrote:[color=blue]
> On Tue, 26 Oct 2004, Mike Mascari wrote:
>
>[color=green]
>>I'd like to ensure that the creation of a department also implies the
>>creation of two to eight projects; no more, no less:[/color][/color]
[color=blue][color=green]
>>Is there no way to achieve the above stated goal in the server? Must I
>>rely on the application to enforce consistency?[/color]
>
>
> Well. It's not exactly meant to be a user facing feature, but check out
> CREATE CONSTRAINT TRIGGER.[/color]

Thanks, Stephan!

I read the disclaimer "It is not intended for general use" but am
curious as to why it isn't a user-facing feature? Is it a function of
just exposing a cleaner SQL interface, or is it a function of the
trigger queue having been written after user-defined triggers, or is
there some philosophical argument against allowing user-definable
triggers to be deferred?

At any rate, it looks like precisely what I need.

Thanks!

Mike Mascari

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

re: Any plans on allowing user-defined triggers to be deferrable?

On Tue, 26 Oct 2004, Mike Mascari wrote:
[color=blue]
> Stephan Szabo wrote:[color=green]
> > On Tue, 26 Oct 2004, Mike Mascari wrote:
> >
> >[color=darkred]
> >>I'd like to ensure that the creation of a department also implies the
> >>creation of two to eight projects; no more, no less:[/color][/color]
>[color=green][color=darkred]
> >>Is there no way to achieve the above stated goal in the server? Must I
> >>rely on the application to enforce consistency?[/color]
> >
> >
> > Well. It's not exactly meant to be a user facing feature, but check out
> > CREATE CONSTRAINT TRIGGER.[/color]
>
> Thanks, Stephan!
>
> I read the disclaimer "It is not intended for general use" but am
> curious as to why it isn't a user-facing feature? Is it a function of
> just exposing a cleaner SQL interface, or is it a function of the
> trigger queue having been written after user-defined triggers, or is
> there some philosophical argument against allowing user-definable
> triggers to be deferred?[/color]

It was written basically for dumping/restoring foreign keys in the initial
version of fks and at the time we didn't want to say that we wouldn't
change the syntax to better handle the constraints. We're not using it
for the constraint dumping/restore any longer, but it seems fairly
unlikely to go away at this point unless something else gets put in to
replace it, although the syntax might still change.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

re: Any plans on allowing user-defined triggers to be deferrable?

On Tue, 26 Oct 2004, Mike Mascari wrote:
[color=blue]
> Stephan Szabo wrote:[color=green]
> > On Tue, 26 Oct 2004, Mike Mascari wrote:
> >
> >[color=darkred]
> >>I'd like to ensure that the creation of a department also implies the
> >>creation of two to eight projects; no more, no less:[/color][/color]
>[color=green][color=darkred]
> >>Is there no way to achieve the above stated goal in the server? Must I
> >>rely on the application to enforce consistency?[/color]
> >
> >
> > Well. It's not exactly meant to be a user facing feature, but check out
> > CREATE CONSTRAINT TRIGGER.[/color]
>
> Thanks, Stephan!
>
> I read the disclaimer "It is not intended for general use" but am
> curious as to why it isn't a user-facing feature? Is it a function of
> just exposing a cleaner SQL interface, or is it a function of the
> trigger queue having been written after user-defined triggers, or is
> there some philosophical argument against allowing user-definable
> triggers to be deferred?[/color]

I should also add that it's also not likely to get upgraded to handle new
trigger features (for example statement triggers) quickly unless someone
wants to step up and do so.

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

http://archives.postgresql.org