- Original Message -
> From: "Tim Cross"
> Sent: Wednesday, February 28, 2018 4:07:43 PM
>
> Jeremy Finzel writes:
>
> > We want to enforce a policy, partly just to protect those who might forget,
> > for every table in a particular schema to have a primary key. This can't
> > be done with event triggers as far as I can see, because it is quite
> > legitimate to do:
> >
> > BEGIN;
> > CREATE TABLE foo (id int);
> > ALTER TABLE foo ADD PRIMARY KEY (id);
> > COMMIT;
> >
> > It would be nice to have some kind of "deferrable event trigger" or some
> > way to enforce that no transaction commits which added a table without a
> > primary key.
> >
>
> I think you would be better off having an automated report which alerts
> you to tables lacking a primary key and deal with that policy through
> other means. Using triggers in this way often leads to unexpected
> behaviour and difficult to identify bugs. The policy is a management
> policy and probably should be dealt with via management channels rather
> than technical ones. Besides, the likely outcome will be your developers
> will just adopt the practice of adding a serial column to every table,
> which in itself doesn't really add any value.
I concur with other respondents that suggest this is more of a policy issue. In
fact, you yourself identify it right there in the first sentence as a policy
issue!
One tool that changed my life (as a PostgreSQL enthusiast) forever is David
Wheeler's pgTAP (http://pgtap.org/) tool. It includes a suite of functionality
to assess the database schema via automated testing. Part of a rigorous
development environment might include using this tool so that any
application/database changes be driven by tests, and then your code review
process would assure that the appropriate tests are added to the pgTAP script
to confirm that changes meet a policy standard such as what you are demanding.
I can't imagine doing PostgreSQL development without it now.
Same guy also produced a related tool called Sqitch (http://sqitch.org/) for
data base change management. Use these tools together, so that before a
developer is allowed to check in a feature branch, your teams' code review
process maintains rigorous oversight of modifications.
-- B
