Обсуждение: Idempotency for all DDL statements
Hello everyone. This is my first time posting in -hackers, so please keep this in mind.
I would like to formally request the addition of "IF EXISTS", "IF NOT EXISTS", "OR REPLACE", and any other CINE variants to DDL statements that currently do not feature an idempotent construct.
The lack of an idempotent option in statements such as "CREATE TRIGGER" for example is particularly painful when performing upgrades on sizable schemas.
I understand that there may be some difficulties in providing this to various database objects. That said, I believe IMHO that this would be a huge win for the community at large. Could I request DDL idempotency as an addition to the development roadmap?
Thanks,
Kenaniah
On 10/17/16 2:05 PM, Kenaniah Cerny wrote: > Could I request DDL idempotency as an addition to the development roadmap? There is not really a roadmap, but I think there is general interest in this. If you want to make it happen faster, however, you will need to start coding it yourself. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 18 October 2016 at 10:26, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote: > On 10/17/16 2:05 PM, Kenaniah Cerny wrote: >> Could I request DDL idempotency as an addition to the development roadmap? > > There is not really a roadmap, but I think there is general interest in > this. If you want to make it happen faster, however, you will need to > start coding it yourself. Yeah. The way to make that happen is to start submitting patches. There have been a number of "IF NOT EXISTS" options added lately so take a look at those patches for guidance. Be aware that not all cases are simple, and some community members are less than fond of the whole concept so you'll need to do some convincing. I'm ambivalent myself. I'm certain that every DROP should support it. I'm less sure about CREATE and ALTER, since assuming it is in fact idempotent is questionable at best. All that IF NOT EXISTS does is skip acting if the target already exists. If the target exists but is entirely different to what would result from running the CREATE statement it silently does nothing. CREATE TABLE fred (id integer); CREATE TABLE IF NOT EXISTS fred(id text); The latter statement is not truly idempotent. To achieve that, we'd have to implicitly transform it into an ALTER and magically figure out what the correct way to preserve/transform user data is, which is not possible since there are so many possibilities and there's no way to choose between them. Second-best would be to detect that the target exists, but does not match the results of executing the current statement and ERROR appropriately. Personally I think use of CREATE / ALTER ... IF NOT EXISTS is almost always a mistake, and you should instead use a schema versioning system that handles schema changes in a managed way. But I'm still in favour of IF NOT EXISTS for convenience, easy development, and user friendliness, though they need warnings in the docs really. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services