May 5, 2015 at 8:28 pm
Comments posted to this topic are about the item Idempotent
May 6, 2015 at 12:34 am
Been writing my release scripts like this for years. Like you I didnt know there was a specific term for it.
As one co-worker said: "write it like this [ed: idemptent] then no one will notice if you release a script half asleep or whether you got distracted, you just run it again".
When Fire fighting an issue and the pointy-haired-boss starts asking damn fool questions an idempotent script does reduce a lot of risks.
May 6, 2015 at 6:14 am
I've been writing idempotent scripts for years. However, even that's not the best defense against a script run twice. I've found it's more economical to write a guard at the top of the script to check database/app version or existence of a change already being performed.
In other words, stop the script immediately at start instead of reading through 20k lines to make sure everything was idempotent.
May 6, 2015 at 6:28 am
I think the whole approach is the right one. Writing stuff that works, checking itself along the way and doing the work that needs to be done, is the way I've written my release scripts for a long time.
I have monthly procedures that are the same way. It does the work it needs to do, but doesn't run the sections of the code that would cause trouble. When someone else gets their part of the process done, I simply run the same procedure and it does the parts that need to be done while skipping what's already been done.
For many of us "seasoned" people, I'm sure the concept and word are nothing new, but I think it could be a paradigm shift for the beginner.
May 6, 2015 at 6:38 am
This also applies to application code. Not just scripts. I use this technique in stored procedures too.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
May 6, 2015 at 7:15 am
For many of us "seasoned" people, I'm sure the concept and word are nothing new, but I think it could be a paradigm shift for the beginner.
Not just beginners. Almost all coders who are used to writing "write once, run once" scripts hate "wasting time doing this pointless stuff" ... in my experience.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 6, 2015 at 7:19 am
Bradley Deem (5/6/2015)
... However, even that's not the best defense against a script run twice...
The whole point is that the script can be run once, twice or a thousand times and the results are the same. That is what makes it idempotent.
Bradley Deem (5/6/2015)
...I've found it's more economical to write a guard at the top of the script to check database/app version or existence of a change already being performed.
In other words, stop the script immediately at start instead of reading through 20k lines to make sure everything was idempotent.
So what happens if someone changes something in the database and you need to run the script again. Unfortunately not all databases are locked down, some are a bit too open to rely on a version number being checked at the top of the script.
May 6, 2015 at 7:49 am
For slowly changing reference tables, something like States or ProductCategory, I prefer to maintain the entire collection of inserts bundled into one "idempotent" deployment script. When adding a new row, I simply check out the script from source control, add a new insert, and then check back in.
For more complex tables with multiple attributes, this is a MERGE, but sometimes only a simple INSERT/SELECT is required.
Below are a few techniques I've used for tables containing up to several hundred rows.
CREATE TABLE ProductCategory
(
ProductCategoryID INT NOT NULL PRIMARY KEY,
ProductCategoryName VARCHAR(180) NOT NULL
);
-- The INSERT/SELECT/WHERE_NOT method.
INSERT INTO ProductCategory
( ProductCategoryID, ProductCategoryName )
SELECT ProductCategoryID, ProductCategoryName FROM
(
SELECT 1 ProductCategoryID, 'Kitchenware' ProductCategoryName UNION ALL
SELECT 2 ProductCategoryID, 'Bathroom' ProductCategoryName UNION ALL
SELECT 4 ProductCategoryID, 'Bedroom' ProductCategoryName UNION ALL
SELECT 8 ProductCategoryID, 'Office' ProductCategoryName
) x
WHERE ProductCategoryID not in
(SELECT ProductCategoryID FROM ProductCategory);
-- The clever INSERT/SELECT/EXCEPT method:
INSERT INTO ProductCategory ( ProductCategoryID, ProductCategoryName )
SELECT 1 ProductCategoryID, 'Kitchenware' ProductCategoryName UNION ALL
SELECT 2 ProductCategoryID, 'Bathroom' ProductCategoryName UNION ALL
SELECT 4 ProductCategoryID, 'Bedroom' ProductCategoryName UNION ALL
SELECT 8 ProductCategoryID, 'Office' ProductCategoryName
EXCEPT
SELECT ProductCategoryID, ProductCategoryName
FROM ProductCategory;
Another option is simply to set the IGNORE_DUP_KEY option on the table's primary key.
Why not ??
CREATE TABLE ProductCategory
(
ProductCategoryID INT NOT NULL PRIMARY KEY
WITH (IGNORE_DUP_KEY = ON),
ProductCategoryName VARCHAR(180) NOT NULL
);
-- The IGNORE_DUP_KEY method:
INSERT INTO ProductCategory ( ProductCategoryID, ProductCategoryName )
SELECT 1 ProductCategoryID, 'Kitchenware' ProductCategoryName UNION ALL
SELECT 2 ProductCategoryID, 'Bathroom' ProductCategoryName UNION ALL
SELECT 4 ProductCategoryID, 'Bedroom' ProductCategoryName UNION ALL
SELECT 8 ProductCategoryID, 'Office' ProductCategoryName;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 6, 2015 at 8:38 am
Idempotent scripts are great when possible, but you must be pragmatic. For example, data migrations with table transformations are often not easily made idempotent.
You can reliably check the database/app version before hand, set the version to an interim upgrade version. Then if you are successful set to version next.
May 6, 2015 at 9:11 am
Yet Another DBA (5/6/2015)
Been writing my release scripts like this for years. Like you I didnt know there was a specific term for it.As one co-worker said: "write it like this [ed: idemptent] then no one will notice if you release a script half asleep or whether you got distracted, you just run it again".
When Fire fighting an issue and the pointy-haired-boss starts asking damn fool questions an idempotent script does reduce a lot of risks.
Great points. I've done far too many things half asleep.
May 6, 2015 at 9:12 am
Bradley Deem (5/6/2015)
I've been writing idempotent scripts for years. However, even that's not the best defense against a script run twice. I've found it's more economical to write a guard at the top of the script to check database/app version or existence of a change already being performed.In other words, stop the script immediately at start instead of reading through 20k lines to make sure everything was idempotent.
Any examples here? What do you use to check for db version?
May 6, 2015 at 9:12 am
Gary Varga (5/6/2015)
This also applies to application code. Not just scripts. I use this technique in stored procedures too.
Examples? Want to share in an article, Gary?
May 6, 2015 at 9:24 am
Yet Another DBA (5/6/2015)
Been writing my release scripts like this for years. Like you I didnt know there was a specific term for it.As one co-worker said: "write it like this [ed: idemptent] then no one will notice if you release a script half asleep or whether you got distracted, you just run it again".
When Fire fighting an issue and the pointy-haired-boss starts asking damn fool questions an idempotent script does reduce a lot of risks.
I write database scripts, but someone in production control deploys them. If the script fails in production, then I may have to wait until the next deployment window to resubmit a corrected version. Therefore I have to write scripts that simply work: no errors, no weird messages or warnings, no confusing instructions, and advanced notice if I expect the script to run longer than a few minutes.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 6, 2015 at 9:43 am
Creating objects is straightforward, but I've found the most problematic DDL deployment scripts are those that alter tables. However, INFORMATION_SCHEMA and SYS tables can be leveraged to determine to current state of a table's schema.
-- Add column, if it doesn't exist:
if not exists (select * from information_schema.columns
where table_schema+'.'+table_name+'.'+column_name = 'dbo.ProductCategory.InactiveDate')
alter table dbo.ProductCategory
add InactiveDate date null;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 6, 2015 at 9:49 am
I've never heard that term before. But I try to always create my deployment scripts so that they can be rerun over and over. In fact I put the text ...SafeToReRun into the name to let inform the person who deploys it. In fact I created two just this morning. Not all programmers here do that. I also find it reassuring to my own self when working with the script and other related coding. Now I know what to call it. But I would spell it a little different. Instead of Idempotent I think it should be IDempotent.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply