4 ms·

> or you build DB views to fix that. That's what VIEWs are for! Well, one use-case of VIEWs, anyways. There's nothing wrong with the schema as the API since

> or you build DB views to fix that. That's what VIEWs are for! Well, one use-case of VIEWs, anyways. There's nothing wrong with the schema as the API since you can use VIEWs to maintain backwards compatibility as you evolve your product. Put another way: you will have an API, you will need to maintain backwards compatibility. Not exposing a SQL schema as an API does not absolve you or make it easier to be backwards-compatible. You might argue that you could have server-side JSON schema mapping code to help with schema transitions, and, indeed, that would be true, but whatever you write that code in, it's code, and using SQL or something else is just as well.

mycall 7y ago
How do you do CRUD with views? I know Reads are what views do.
- flukus 7y ago
  You can have insert/update trigger on views. You shouldn't but you can. More realistically, stored procs would do the CUD parts.
  - dragonwriter 7y ago
    > You can have insert/update trigger on views. You shouldn't but you can. You can, and there is no reason you shouldn't. > More realistically, stored procs would do the CUD parts. Why are stored procs more realistic?
    - taffer 7y ago
      Triggers are implicit, have side effects and are not deterministic. They are confusing and surprising. A procedure call is explicit, a trigger is implicit. You don't call a trigger, it just happens as a side effect of something else. People tend to forget implicit things. Suddenly you notice that something is acting strangely or slowly in your application. You can look at your functions and procedures and try to find the problem. But if your application has triggers all over the place, how do you know what is going on? A trigger can change a dozen rows, which in turn can change other rows, so changing a single row can fire thousands or millions of triggers. Also, triggers are not fired in a particular order, the database is free to change the query plan according to what it thinks is best at the moment, so triggers are not deterministic. Triggers can sometimes work and sometimes not. Almost everything that can be done with a trigger can be done with a procedure, but explicitly, deterministically and in most cases without side effects.
      - cryptonector 7y ago
        > Triggers are implicit, have side effects and are not deterministic. > ... > A procedure call is explicit, a trigger is implicit. Method dispatch (whether the language be OOP or otherwise) is also "implicit". Think of triggers as controlled code injection.
        taffer 7y ago
        You still have to call a method if you want it to do something. It doesn't simply happen as a side effect of something else. A method call only ever does one thing at a time, not multiple things in a random order. Read this if you still think triggers are a good idea: https://blogs.oracle.com/oraclemagazine/the-trouble-with-triggers https://blogs.oracle.com/oraclemagazine/the-trouble-with-tri...
        dragonwriter 7y ago
        > A method call only ever does one thing at a time, not multiple things in a random order. That's...not at all guaranteed if it is possible for the method call to make use of concurrency and parallelism constructs. It's actually not at all uncommon for method calls to do a unknown number of things in an unknown order (and it can even have an unknown number of them still in progress when the method returns!)
        cryptonector 7y ago
        Exactly.
        cryptonector 7y ago
        Yeah, and in SQL that's INSERT/UPDATE/DELETE on a table/view. Syntactically, using a stored procedure is really no different, and it can have function calls you don't know about because its body could dispatch just like triggers do. This whole thing about triggers being implied/hidden is not based on anything other than taste.
  - cryptonector 7y ago
    > You shouldn't but you can. Shouldn't? Why?
    - flukus 7y ago
      They're non-obvious and can bite you in several ways. A lot has been written about why triggers are bad (random example: https://www.itprotoday.com/development-techniques-and-management/reasons-avoid-triggers https://www.itprotoday.com/development-techniques-and-manage...). Back before service layers were common and desktop clients connected to the database directly they were common and just about everyone has some war stories of triggers breaking things.
      - cryptonector 7y ago
        A lot of things are non-obvious, especially when you're not aware of them and don't expect them.
- dragonwriter 7y ago
  > How do you do CRUD with views? I know Reads are what views do Both automatically updatable views (supporting insert/update/delete by default because their structure provides a clean mapping to the backing table) and views made updatable through instead-of triggers exist in Postgres.
- cryptonector 7y ago
  CREATE TRIGGER <name> INSTEAD OF INSERT OR UPDATE OR DELETE ON <view-name> ..
deleted 7y ago
[deleted]

mycall 7y ago

How do you do CRUD with views? I know Reads are what views do.

flukus 7y ago
You can have insert/update trigger on views. You shouldn't but you can. More realistically, stored procs would do the CUD parts.
- dragonwriter 7y ago
  > You can have insert/update trigger on views. You shouldn't but you can. You can, and there is no reason you shouldn't. > More realistically, stored procs would do the CUD parts. Why are stored procs more realistic?
  - taffer 7y ago
    Triggers are implicit, have side effects and are not deterministic. They are confusing and surprising. A procedure call is explicit, a trigger is implicit. You don't call a trigger, it just happens as a side effect of something else. People tend to forget implicit things. Suddenly you notice that something is acting strangely or slowly in your application. You can look at your functions and procedures and try to find the problem. But if your application has triggers all over the place, how do you know what is going on? A trigger can change a dozen rows, which in turn can change other rows, so changing a single row can fire thousands or millions of triggers. Also, triggers are not fired in a particular order, the database is free to change the query plan according to what it thinks is best at the moment, so triggers are not deterministic. Triggers can sometimes work and sometimes not. Almost everything that can be done with a trigger can be done with a procedure, but explicitly, deterministically and in most cases without side effects.
    - cryptonector 7y ago
      > Triggers are implicit, have side effects and are not deterministic. > ... > A procedure call is explicit, a trigger is implicit. Method dispatch (whether the language be OOP or otherwise) is also "implicit". Think of triggers as controlled code injection.
      - taffer 7y ago
        You still have to call a method if you want it to do something. It doesn't simply happen as a side effect of something else. A method call only ever does one thing at a time, not multiple things in a random order. Read this if you still think triggers are a good idea: https://blogs.oracle.com/oraclemagazine/the-trouble-with-triggers https://blogs.oracle.com/oraclemagazine/the-trouble-with-tri...
        dragonwriter 7y ago
        > A method call only ever does one thing at a time, not multiple things in a random order. That's...not at all guaranteed if it is possible for the method call to make use of concurrency and parallelism constructs. It's actually not at all uncommon for method calls to do a unknown number of things in an unknown order (and it can even have an unknown number of them still in progress when the method returns!)
        cryptonector 7y ago
        Exactly.
        cryptonector 7y ago
        Yeah, and in SQL that's INSERT/UPDATE/DELETE on a table/view. Syntactically, using a stored procedure is really no different, and it can have function calls you don't know about because its body could dispatch just like triggers do. This whole thing about triggers being implied/hidden is not based on anything other than taste.
- cryptonector 7y ago
  > You shouldn't but you can. Shouldn't? Why?
  - flukus 7y ago
    They're non-obvious and can bite you in several ways. A lot has been written about why triggers are bad (random example: https://www.itprotoday.com/development-techniques-and-management/reasons-avoid-triggers https://www.itprotoday.com/development-techniques-and-manage...). Back before service layers were common and desktop clients connected to the database directly they were common and just about everyone has some war stories of triggers breaking things.
    - cryptonector 7y ago
      A lot of things are non-obvious, especially when you're not aware of them and don't expect them.
dragonwriter 7y ago
> How do you do CRUD with views? I know Reads are what views do Both automatically updatable views (supporting insert/update/delete by default because their structure provides a clean mapping to the backing table) and views made updatable through instead-of triggers exist in Postgres.
cryptonector 7y ago
CREATE TRIGGER <name> INSTEAD OF INSERT OR UPDATE OR DELETE ON <view-name> ..

deleted 7y ago

[deleted]