4 ms·

Just one thing: 1. Teach people transaction isolation levels. 2. Have some standard written down rules about what SQL type to use in which situation and what

Just one thing: 1. Teach people transaction isolation levels. 2. Have some standard written down rules about what SQL type to use in which situation and what approaches to use for table structure. 3. don't overuse triggers or stored plSQL procedures or similar they are hard to test and debug The two main problems I have seen with SQL databases is: 1. People not understanding transaction isolation, most inconsistency bugs or strange behaviour no seem to be able to explain I have seen where due to this. As far as I can tell this is a HUGE problem, even through SQL databases are used much less and at least theoretically it's normally through in any bachelor level course about SQL. 2. people wasting time on discussion about types and table structure. For example weather this or that int type should be used,for a lot of use-cases it's just fine to use bigint (64bit) initially for everything. With 32bit there is often some edge case in which it isn't enough. For example if you do 5_000_000 increments per second on a 31bit (i32>=0) counter it overflows in ~7 minutes but if you the same with a 63bit counter it takes ~58494 years. Sure you sometimes might have to go back and optimize storage and there are cases with clearly constrained numbers, but it's the best default for not clearly bound integer numbers. Similar "initial start with" default approaches can be written down for most situations in just a single DIN-A4 page of paper or so.

KingOfCoders 4y ago
I think (1.) is especially tricky b/c it works if you're small (experienced this myself with Hibernate, not understanding transaction levels) and then breaks with the number of concurrent transactions. When the company rapidly grows you get mysterious bugs - in the worst case customers seing other customers data because of the wrong transaction levels.
- dathinab 4y ago
  Yes, thats why I think it's a huge problem for the industry. How the heck is it possible that so many developers with bachelor and master degrees which otherwise do a reasonable job don't even know that they have a dangerous knowledge gap there??? through that: > in the worst case customers seing other customers data because of the wrong transaction levels. should not happen even with wrong transaction levels, that indicates some additional serious design problems IMHO, likely related to premature optimizations
  - ericmcer 4y ago
    I feel fairly competent but would definitely struggle to debug a transaction isolation problem. I think part of the issue is that I have always operated at an ORM level, on personal projects and at large (>400 dev) companies. From my understanding getting this kind of error would involve something going wrong at a pretty low level? I am not sure how a developer could cause this at the ORM level.
    - tlarkworthy 4y ago
      It's very easy to cause in the ORM because by default a chain of SELECTs may ready different committed data unless you add row level locks like FOR SHARE . In a horizontally scaled backend you need these locks usually, but it takes scanning the DB log to figure out how to get them in the right place. You can't effectively use an ORM without detailed knowledge of the generated output unfortunately. It does not add locks for you, so it's probably just wrong in prod when horizontally scaled and requests span multiple statements.
      - doctor_eval 4y ago
        This is one of the many problems I have with ORMs, you end up needing to know more about the ORM and the database than you would need to know if you just used stored procedures. I don’t recall ever having a transaction isolation issue with my busy stored procedures - though I often use SELECT .. FOR UPDATE which is maybe cheating because an ORM can’t do that efficiently - but I’ve certainly seen them in ORMs.
victor106 4y ago
> even through SQL databases are used much less Really!!!
asguy 4y ago
> 3. don't overuse triggers or stored plSQL procedures or similar they are hard to test and debug Mandate automated DB unit testing [0] from day 1, just like you would for the rest of your code base. [0] - https://pgtap.org/ https://pgtap.org/
doctor_eval 4y ago
> don't overuse triggers or stored plSQL procedures or similar they are hard to test and debug I just don’t find this to be true at all, quite the opposite in fact. It’s true that (AFAIK) there isn’t a stepping debugger for pl/pgsql, although I would love to hear that I’m wrong. But it’s trivial to debug procedures using RAISE NOTICE commands, and you can run your tests non destructively (in a transaction that you abort) which makes setting up the state for debugging much easier. I also personally find that I write fewer bugs in plpgsql simply because there is less abstraction (no DAOs, ORMs, caches etc) and I’m working much more closely with the actual data structures I care about. And the referential integrity checks tend to catch those I do write, early.
- Tostino 4y ago
  > although I would love to hear that I’m wrong. You'd be wrong there: https://www.pgadmin.org/docs/pgadmin4/development/debugger.html https://www.pgadmin.org/docs/pgadmin4/development/debugger.h...
ilyt 4y ago
> People not understanding transaction isolation, most inconsistency bugs or strange behaviour no seem to be able to explain I have seen where due to this. As far as I can tell this is a HUGE problem, even through SQL databases are used much less and at least theoretically it's normally through in any bachelor level course about SQL. Well, same people won't have much success with NoSQL either, as they inevitably will skip reading on how this particular NoSQL DB handles it.

KingOfCoders 4y ago

I think (1.) is especially tricky b/c it works if you're small (experienced this myself with Hibernate, not understanding transaction levels) and then breaks with the number of concurrent transactions. When the company rapidly grows you get mysterious bugs - in the worst case customers seing other customers data because of the wrong transaction levels.

dathinab 4y ago
Yes, thats why I think it's a huge problem for the industry. How the heck is it possible that so many developers with bachelor and master degrees which otherwise do a reasonable job don't even know that they have a dangerous knowledge gap there??? through that: > in the worst case customers seing other customers data because of the wrong transaction levels. should not happen even with wrong transaction levels, that indicates some additional serious design problems IMHO, likely related to premature optimizations
- ericmcer 4y ago
  I feel fairly competent but would definitely struggle to debug a transaction isolation problem. I think part of the issue is that I have always operated at an ORM level, on personal projects and at large (>400 dev) companies. From my understanding getting this kind of error would involve something going wrong at a pretty low level? I am not sure how a developer could cause this at the ORM level.
  - tlarkworthy 4y ago
    It's very easy to cause in the ORM because by default a chain of SELECTs may ready different committed data unless you add row level locks like FOR SHARE . In a horizontally scaled backend you need these locks usually, but it takes scanning the DB log to figure out how to get them in the right place. You can't effectively use an ORM without detailed knowledge of the generated output unfortunately. It does not add locks for you, so it's probably just wrong in prod when horizontally scaled and requests span multiple statements.
    - doctor_eval 4y ago
      This is one of the many problems I have with ORMs, you end up needing to know more about the ORM and the database than you would need to know if you just used stored procedures. I don’t recall ever having a transaction isolation issue with my busy stored procedures - though I often use SELECT .. FOR UPDATE which is maybe cheating because an ORM can’t do that efficiently - but I’ve certainly seen them in ORMs.

victor106 4y ago

> even through SQL databases are used much less Really!!!

asguy 4y ago

> 3. don't overuse triggers or stored plSQL procedures or similar they are hard to test and debug Mandate automated DB unit testing [0] from day 1, just like you would for the rest of your code base. [0] - https://pgtap.org/ https://pgtap.org/

doctor_eval 4y ago

> don't overuse triggers or stored plSQL procedures or similar they are hard to test and debug I just don’t find this to be true at all, quite the opposite in fact. It’s true that (AFAIK) there isn’t a stepping debugger for pl/pgsql, although I would love to hear that I’m wrong. But it’s trivial to debug procedures using RAISE NOTICE commands, and you can run your tests non destructively (in a transaction that you abort) which makes setting up the state for debugging much easier. I also personally find that I write fewer bugs in plpgsql simply because there is less abstraction (no DAOs, ORMs, caches etc) and I’m working much more closely with the actual data structures I care about. And the referential integrity checks tend to catch those I do write, early.

Tostino 4y ago
> although I would love to hear that I’m wrong. You'd be wrong there: https://www.pgadmin.org/docs/pgadmin4/development/debugger.html https://www.pgadmin.org/docs/pgadmin4/development/debugger.h...

ilyt 4y ago

> People not understanding transaction isolation, most inconsistency bugs or strange behaviour no seem to be able to explain I have seen where due to this. As far as I can tell this is a HUGE problem, even through SQL databases are used much less and at least theoretically it's normally through in any bachelor level course about SQL. Well, same people won't have much success with NoSQL either, as they inevitably will skip reading on how this particular NoSQL DB handles it.