RE: Table/Column Constraints
От | Christopher Kings-Lynne |
---|---|
Тема | RE: Table/Column Constraints |
Дата | |
Msg-id | NEBBIOAJBMEENKACLNPCAEIGCCAA.chriskl@familyhealth.com.au обсуждение исходный текст |
Ответ на | Re: Table/Column Constraints (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Table/Column Constraints
|
Список | pgsql-hackers |
> > In fact, IMHO, this would be a great place to start: we'd all love the > > fuctionality, it'd have you examining almost all the same code, and it'd > > be a feature we could all test, in diverse situations. DROP CONSTRAINT > > is unlikely to be as widely tested. If you can build the introspection > > correctly, so that it dumps/reloads correctly for _everyone_, then I'd > > trust your DROP CONSTRAINT work a lot more. Just to catch up here - does this mean that pg_dump has issues with correctly recreating the contraints? If you tell me exactly what the problem is - I'll give it a burl. However, a reimplementation of constraints would probably be beyond my knowledge atm. > Yes. My take on this is that a lot of the constraint-related stuff, > especially foreign keys, is misdesigned: the reason it's so hard to > extract the info is that we are only storing an execution-oriented > representation. There should be a purely declarative representation > of each constraint someplace, too, for ease of introspection. By this, do you mean that the existence of a foreign key is implied rather than explicit by the existence of various triggers, etc.? > So, my idea is that this ought to be a three-part process: > > 1. Redesign the representation of constraints into something more > reasonable --- at least add a declarative representation, maybe alter > or drop existing representation if it seems appropriate. Problem is that there are 5 difference types of constraints, implemented in 5 different ways. Do you want a unifed, central catalog of constraints, or just for some of them, or what? Maybe it could be done like this (given my limited knowledge...) a. Create a system catalog that names all contraints associated with tables. I assume that column contraints implicitly become table constraints. This will also make it easy to have global unique contraint names. Actually - are the constraint names currently unique for an entire database? b. In all the places where the constraints are implemented. (ie. pg_relcheck, indicies and pg_trigger add a column that flags the entry as being a 'system constraint'. That way finding and dropping constraints should be ok, so long as everything is kept consistent! > 2. Adjust pg_dump to use the declarative representation rather than > trying to reconstruct things from the execution-oriented representation. > (Note this will imply that, for example, triggers generated to implement > foreign keys should NOT be dumped. Thus, it needs to be reasonably easy > to identify such triggers --- maybe an additional flag column is needed > in pg_trigger to mark system-generated triggers.) This would be straightforward, given the implementation of (1). It would be nice, however, if pg_dump produced the exact same sql as used to create a table. For instance, if you specify a column constraint, it comes back as a column constraint, rather than a trigger, or a table constraint. This would especially aid portability of the dumped SQL. > 3. Work on ALTER ... DROP CONSTRAINT. Again, this should be straightforward given (1). > Christopher may now be wondering what he's got himself in for ;-). There's no better way to learn databases than to code for one I think! Any comments? Chris
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Larry RosenmanДата:
Сообщение: Re: [COMMITTERS] pgsql/src/backend/access/transam (xlog.c)
Следующее
От: "Robert D. Nelson"Дата:
Сообщение: RE: [GENERAL] PHPBuilder article -- Postgres vs MySQL