----- Original Message ----- > From: "Tim Cross" <theophil...@gmail.com> > Sent: Wednesday, February 28, 2018 4:07:43 PM > > Jeremy Finzel <finz...@gmail.com> 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
