Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

An UPDATE without a WHERE, or something close to it

rachelbythebay.com

Post reply on HN
Print view View original

91–100 of 112 posts

Re: An UPDATE without a WHERE, or something close to it

#91

Post by bentcorner »

jerf wrote: post #59
Someone wrote: post #54

Earlier quoted context omitted.

Tools could be better at supporting rolling updates. With 20.000 servers, for example, an automated process could roll out updates to 1,000 every hour during 20 hours, check that response times, system load, etc. are within limits for the updated set during each hour, and send out alerts and pause updating when they do not. So, the user still would press one button to do the update, but the change would slowly take e…

This is an example of what I mean by "AI Hard". Yes, obviously, rolling updates is an improvement over non-rolling updates at scale. However, you still have things like "this update severs the machine from the management system due to unexpected XYZ", "this update is fine until it's rolled out to 80% of the world, at which point interactions with the other deployed systems hammer the system so hard the management int…

I think the only answer is the branch the universe, apply your changes, and if things go wrong, send a quantum tunneled bit signal to the control universe that those changes were bad. Then destroy the universe, nobody will ever know.

Re: An UPDATE without a WHERE, or something close to it

#92

Post by jesterpm »

DoneWithAllThat wrote: post #69
jusssi wrote: post #16

A particularly nasty aspect of SQL UPDATE syntax is the predicate order, i.e. SET is before WHERE. So when you type it in order, there's a dangerous phase you have to go through if you hit enter by accident. Having run an `mkfs.ext3 /dev/sda` (note the missing partition number) by accident, I've learned to start potentially destructive commands by typing first whatever makes that command a comment (# in shell, -- in…

My approach is to type the “limit conditions” first, then go to the start of the line and do as you say (predicate the command with the comment string). So in the case of sql it might be something like starting with: WHERE foo=bar LIMIT 1; Then ctrl-a, and fill in the rest: —- UPDATE name=“new name” WHERE foo=bar LIMIT 1; Then take a moment, read over what I typed, and hit ctrl-a again and remove the comment string.…

I take a similar approach. I usually want to see what I'm about to change before I change it, so I'll SELECT * FROM ... WHERE ... , then go back in the command history and delete the SELECT FROM.

Re: An UPDATE without a WHERE, or something close to it

#93

Post by mhink »

Amezarak wrote: post #80
sumtechguy wrote: post #60

Earlier quoted context omitted.

In fact I would go one step further and say why does 'where' default to ALL? You did not say what you wanted. So you should get nothing. This should be true for select and delete as well as well as update. If I just do 'SELECT *' and execute it, it does not rotate through all the tables. I did not specify it. Same with 'SELECT from xyz' if I can not have it empty and return all. Yet where is special somehow.

Because tables and SQL operators are really sets and acting on sets, so naturally operations take place on the set unless a subset is specified. Once you completely and totally internalize this SQL is pretty intuitive, although I think that the clause order might be nicer in a different order. For example, I would like SELECT to be FROM, WHERE, SELECT. UPDATE could be UPDATE, WHERE, SET. But then DELETE would end up…

Honestly, I think the problem is just that SQL isn't a particularly great language. This is an example of where the syntax and semantics are coupled so tightly that it's just gonna be confusing no matter what you do. My vote would be to not even bother with a "literate" kind of language.

Re: An UPDATE without a WHERE, or something close to it

#94

Post by meowface »

jesterpm wrote: post #92
DoneWithAllThat wrote: post #69

Earlier quoted context omitted.

My approach is to type the “limit conditions” first, then go to the start of the line and do as you say (predicate the command with the comment string). So in the case of sql it might be something like starting with: WHERE foo=bar LIMIT 1; Then ctrl-a, and fill in the rest: —- UPDATE name=“new name” WHERE foo=bar LIMIT 1; Then take a moment, read over what I typed, and hit ctrl-a again and remove the comment string.…

I take a similar approach. I usually want to see what I'm about to change before I change it, so I'll SELECT * FROM ... WHERE ... , then go back in the command history and delete the SELECT FROM.

Same. (Also for DELETEs.) I would guess this is what a high percentage of people do.

Re: An UPDATE without a WHERE, or something close to it

#95

Post by eyelidlessness »

JeffRosenberg wrote: post #88
jusssi wrote: post #16

A particularly nasty aspect of SQL UPDATE syntax is the predicate order, i.e. SET is before WHERE. So when you type it in order, there's a dangerous phase you have to go through if you hit enter by accident. Having run an `mkfs.ext3 /dev/sda` (note the missing partition number) by accident, I've learned to start potentially destructive commands by typing first whatever makes that command a comment (# in shell, -- in…

I try to start any UPDATE or DELETE by writing out a transaction first: BEGIN TRAN; -- TODO: update statement ROLLBACK; Usually I'll run it with the ROLLBACK first, to confirm that it impacts the number of rows I'd expect, and only then change my ROLLBACK to a COMMIT.

I don’t even do this, it’s too easy to sidestep. For example using any GUI client where the current selection or statement is executed by default. (Yes this is very useful, no I am not interested in being convinced not to use these tools thank you.)

Instead I do my dry run queries with a read only user and I select the affected data, often using CTEs (or temp tables where performance is an issue) to model any intermediate state. I don’t ever run any writes of this kind without review, backups, and automation.

Re: An UPDATE without a WHERE, or something close to it

#96

Post by eyelidlessness »

lol768 wrote: post #47
jusssi wrote: post #16

A particularly nasty aspect of SQL UPDATE syntax is the predicate order, i.e. SET is before WHERE. So when you type it in order, there's a dangerous phase you have to go through if you hit enter by accident. Having run an `mkfs.ext3 /dev/sda` (note the missing partition number) by accident, I've learned to start potentially destructive commands by typing first whatever makes that command a comment (# in shell, -- in…

> A particularly nasty aspect of SQL UPDATE syntax is the predicate order, i.e. SET is before WHERE. So when you type it in order, there's a dangerous phase you have to go through if you hit enter by accident. I religiously write it out of order for this reason. The IDE complains for a bit, but it's better to deal with some squiggles for a few seconds until you've filled in the column assignments part.

The problem with this is the same reason I don’t write dangerous CLI commands with the safety parts first: you can’t know whether your safety parts are safe until you introduce the dangerous parts. It’s the same reason I don’t ignore the linter, even if it’s cognitive overhead that formatting will solve… it’s more cognitive overhead to find out whether it’s safe to ignore.

At least for me, it’s safer to just… write the thing correctly in an environment incapable of running it.

Re: An UPDATE without a WHERE, or something close to it

#97

Post by hotsauceror »

codeulike wrote: post #14

re: UPDATE without a WHERE In SSMS (the main query console tool thing for Sql Server) you can highlight a bit of code with the cursor (as if you want to copy/paste it) and press CTRL-E to execute it, its really handy when you've got a big sketchpad-like series of SELECT statements and you're doing exploratory tinkering. But if your UPDATE statement is accross three lines, its a little bit too easy to accidentally sel…

I worked at a place where this happened pretty much monthly, in production. Outages, blown SLA $$$, uptime metrics blown. Certain teams’ entire toolbox was a sql script with various query templates commented out, that they tweaked, manually highlighted, and ran. Passwords, configs, user data, all got wiped out this way. No one got fired, no one got reprimanded, the workflow didn’t change despite this happening over and over. And each time the DBA team got to spend a few hours restoring a multi-TB backup so that someone could grab the prior data from the table they wiped out. Middle of the night? No problem, wake up the DBAs and go back to bed, it’s not like there would be any consequences.

Re: An UPDATE without a WHERE, or something close to it

#98

Post by hotsauceror »

gfody wrote: post #81
codeulike wrote: post #14

re: UPDATE without a WHERE In SSMS (the main query console tool thing for Sql Server) you can highlight a bit of code with the cursor (as if you want to copy/paste it) and press CTRL-E to execute it, its really handy when you've got a big sketchpad-like series of SELECT statements and you're doing exploratory tinkering. But if your UPDATE statement is accross three lines, its a little bit too easy to accidentally sel…

I once saw a coworker get destroyed by a rendering glitch in SSMS - he had GPU acceleration enabled and didn't notice his text selection highlight glitching out, smashed F5 on a query that looked like it had a where clause selected but if you fiddled with the scrollbar suddenly it wasn't actually selected. I noticed his query blocking everything and killed it, walked over to his office like WTF and he reran it right…

I’ve had something similar happen with lag on the right-click menus. It’s very easy to actually be clicking on the wrong option, like “Delete database,” because there was lag in updating the cursor position. Happens in scary places like MMC too. Hope you don’t accidentally drop a cluster volume because windows wasn’t done drawing a pretty arrow!

Powershell FTW.

Re: An UPDATE without a WHERE, or something close to it

#99

Post by dragonwriter »

jusssi wrote: post #16

A particularly nasty aspect of SQL UPDATE syntax is the predicate order, i.e. SET is before WHERE. So when you type it in order, there's a dangerous phase you have to go through if you hit enter by accident. Having run an `mkfs.ext3 /dev/sda` (note the missing partition number) by accident, I've learned to start potentially destructive commands by typing first whatever makes that command a comment (# in shell, -- in…

> A particularly nasty aspect of SQL UPDATE syntax is the predicate order, i.e. SET is before WHERE. So when you type it in order, there's a dangerous phase you have to go through if you hit enter by accident.

Well, dangerous if you are using a simple terminal interface and not using a transaction when doing updates. The latter is generally a bad idea even if you aren't also doing the former.

Re: An UPDATE without a WHERE, or something close to it

#100

Post by andreareina »

RenThraysk wrote: post #40
andreareina wrote: post #23

Earlier quoted context omitted.

And be sure that you're not using something that implicitly commits the transaction, such as TRUNCATE in Redshift.

TRUNCATE in a few databases effectively drops the table, and recreates it. If typing in an interactive session usually use a specific user account for it, with minimal set of privileges. And wouldn't have the DROP privilege or any other DDL statement privilege. DDL would be scripted out, tested and ran using another user account that had only privileges on specific databases it needed.

IIRC (I'm not about to go test it now) DROP TABLE is transactional. But yeah, TRUNCATE is an odd beast. In Postgresql 10:

> TRUNCATE is not MVCC-safe. After truncation, the table will appear empty to concurrent transactions, if they are using a snapshot taken before the truncation occurred. See Section 13.5 for more details.

> TRUNCATE is transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit.

Post reply on HN