I cannot count the number of times I’ve written the same stupid code,
just to do something as simple as making sure the UPDATE
updates exactly one row.
Last time I complained about this in public was at our Christmas party at the office.
Normally nobody cares to listen to me when I’ve had a few beers and start talking about SQL stuff.
Next day I noticed a colleague had spent the hang-over-morning hacking on a patch.
What an awesome Christmas party I thought!
That was quite some time ago and my code still looks ugly, so let’s complain some more and hope it helps.
Number of ugly statements in our codebase:
$ grep -r -E "(IF NOT FOUND THEN)|(RETURNING (1|TRUE) INTO STRICT)" . | wc -l
701
In a typical OLTP application, each UPDATE
typically affects exactly one row.
If more than one row or no row at all would match, you want to throw an exception, return false,
or what ever the proper behaviour might be in the situation.
This sounds like a trivial task, but it turns out there is no nice way of doing this in plpgsql.
Here are three different work-arounds to ensure the update went through:
IF NOT FOUND THEN
Even if FOUND
could mean more than one row was affected,
it’s safe to assume exactly one row was updated if you are updating
using a primary key or any other unique column.
UPDATE Orders SET Processed = 1 WHERE OrderID = _OrderID AND Processed = 0; IF NOT FOUND THEN RAISE no_data_found; END IF;
GET DIAGNOSTICS integer_var = ROW_COUNT;
If you are a bit more paranoid, you can check the exact row count,
and throw different exceptions based on if no rows were found
or if more than one was found.
UPDATE Orders SET Processed = 1 WHERE OrderID = _OrderID AND Processed = 0; GET DIAGNOSTICS _RowCount = ROW_COUNT; IF _RowCount = 0 THEN RAISE no_data_found; ELSIF _RowCount > 1 THEN RAISE too_many_rows; END IF;
RETURNING TRUE INTO STRICT _OK
This is the one I prefer. It guarantees only one row was affected,
and is at the same time a one-liner, or two lines if you could
the DECLARE
of the boolean variable.
DECLARE _OK boolean; ... UPDATE Orders SET Processed = 1 WHERE OrderID = _OrderID AND Processed = 0 RETURNING TRUE INTO STRICT _OK;
However, none of them are pretty. Something as simple and frequent should be in the syntax of a language.
The proposed syntax in johto’s patch was:
UPDATE STRICT Orders SET Processed = 1 WHERE OrderID = _OrderID AND Processed = 0;
Tom Lane suggested to put the STRICT
keyword in the beginning instead,
avoiding conflicts with table names, which sounds like a good idea to me.
STRICT UPDATE Orders SET Processed = 1 WHERE OrderID = _OrderID AND Processed = 0;
I really hope the community will find a way to fix this syntax bug,
without causing to much pain in terms of backwards compatibility issues for users.
I remember when we upgraded from 8.4 to 9.1, I struggled changing all code where
IN/OUT parameters were in conflict with column names,
as the behaviour changed to raise an error.
Users who didn’t want to invest time fixing their code could simply set the setting plpgsql.variable_conflict
in postgresql.conf
to get back the old behaviour.
I think this was a very user-friendly and nice way to deal with the compatibility issue.
If the STRICT
syntax isn’t possible because of hypothetical compatibility issues,
perhaps it could be up to the user to turn it on or off, using a similar setting?
I would even like to go a bit further and suggest making the update of exactly one row the default in PL/pgSQL.
In the typically fewer cases where you want to update more than one row, could explicitly specify it with some other keyword,
such as ALL UPDATE
or whatever. This would make the code much cleaner, as you wouldn’t need to type STRICT
all over your code.
In OLTP I never use UPDATEs or DELETEs. Just INSERTs. This solves also my issues with undoing and history.
How do you update the balance of an account just doing INSERTs?
While it looks like you store the balances, I decided to store the changes in the amounts (money or goods it doesn’t matter) to later query for the balances. I need to keep track of every single change for both full history and for possible “undoing”. Just like you can see in “double entry bookkeeping”.
As extra benefits I don’t need to VACUUM those tables and I don’t have the UPDATE/DELETE issues you mentioned. And I can also make back- and forth- dated changes.
It’s a completely different approach.
I don’t really understand the usefulness of this option.
Usually you update the rows which conform to particular criteria.
Now, if you want to update just *one* row and you cannot specify deterministic criteria in “where” clause, the next question would which one row of the dataset you’re updating?
imho, such non-deterministic should be left out of the database..
An UPDATE is _never_ deterministic, you can specify any WHERE-clause, or even skip it entirely, there is still no guarantee your row will be updated. The row might not be there anymore due to a concurrent update or delete. Not likely if you only update including only the primary key in the WHERE-clause. But it is very common to include other conditions in the WHERE-clause, which you expect to hold true, but you want to verify them, and if any of the conditions would be false, you want the update to fail and raise an error. This is why you need to check if the update did in fact update the row.
i see your point. Looks like the goal is to see that update operation updated exactly one row, not “that it updates one row” it makes total sense to me. thanks for clarifying.
Hello Father Finelli-I started rnaeidg your bulletin and watched you on the talk show.You were great! Before I left for Florida I took some pictures of inside our beautiful church and when I pray in the morning I bring up the pictue of the altar and visualize being in church. I didn’t plan on doing that when I took them. Thank you for what you have done for the parrish and look forward to returning . Barbara Goncalo
that he wouldn’t leave me coz he knew that he wonuldt be able to find a girl who loves him so much like I do, and a girl so nice like how i amFinally, last week he decided to break up. His reason is that he has lost the love feelings; that he feels very guilty when i treat him nice coz he has no motivation to do something for me, and that he can’t go on being my bf without the love feelings although he still cares bout me v. muchI dont blame him, coz i know tat he’s very busy and tired at work. I wonder if he really doesnt love me or only his work makes him very frustrated. But anyway, i love him so much that i dont wanna give up yet.I need your advices on what i should do. Coz there are pros and cons for all the choices, and i dont know what is better for getting him back, i dont know what guys think.1.Ignore him, not contacting him, not being clingy. So that he may realize that he has lost me in his life and he may realize how important im. But the risk is that he will be more used to the life without me, and that his feelings will be cooled down.2.being his friend but not begging him back. So that he may know that im always here for him, im his soulmate, i wanna take care of him and that im friendly. The risk is that he may really think that im his friend and that our love story is over.3.Not ignoring him nor being his friend. But reminding him once in a while that i miss him, i m waiting for him.. and do little things to touch him. I wonder if this is clingy and annoying.4.go to see him in my coming vacation. I have vacations soon and i’d like to see him and give us a last try. I always wonder if his love feelings will be back if we meet again. However, he has rejected this idea coz he doesnt wanna prolong our pains. Im afraid that he may not welcome me i dont know what I should do. Can anyone give me some advices on these ideas besides asking me to give up? I love him so much, and i wanna do everything i can to get him back. Plis help me.
And what better place than Facebook, to enjoy above 1 / 2 the
billion individuals in order to target, as well as most out
of consumers already bifurcated into groups, classes
and sectors. So, several variations of your message may perhaps become
required to get the most exposure. The whole city
became a number of Citizen Journalists who shared internet their views, reactions,
escape Stories,etc.
I bow down humbly in the presence of such grsasnete.
I see a lot of interesting content on your blog. You have to spend a lot of time
writing, i know how to save you a lot of time, there is a tool
that creates unique, SEO friendly posts in couple of minutes, just search in google – laranita’s free content source
I read a lot of interesting posts here. Probably you spend a lot of time writing,
i know how to save you a lot of work, there is an online tool that creates readable, google friendly articles in minutes, just search in google – laranitas
free content source
That’s really thinking at an impressive level