5 ms·

You didn't state a single reason why you think that grabbing multiple resultsets in a single trip to the database is a bad idea though... The only real argumen

You didn't state a single reason why you think that grabbing multiple resultsets in a single trip to the database is a bad idea though... The only real argument I've ever seen against it has to do with "Separation of Concerns". Some people don't want any logic in their database. However, there's never any reason why. (I bet you've UPDATEd multiple tables in one query though.) Reasons are often given, sure...but they're almost never based on that persons experience because PGSQL users don't have this feature. So, they'll never know just how awesome it is to have real stored procedures like TSQL has. Anyway, it's not about "jamming" things into one query. It's about composability and PGSQL is lacking it.

koolba 10y ago
> You didn't state a single reason why you think that grabbing multiple resultsets in a single trip to the database is a bad idea though... It's a bad idea for the same reason that having a function that returns back multiple values is a bad idea. Yes it's useful sometimes but on the whole it ends up being confusing. From a result set processing perspective it's also a pain in the ass as your app code is now tied to handling multiple results in a particular order, i.e., more/tighter coupling. > (I bet you've UPDATEd multiple tables in one query though.) You'd win that bet. There are a lot of ways to do this and it's a legit operation because you want N things to change at once (where N isn't in the same table). > Reasons are often given, sure...but they're almost never based on that persons experience because PGSQL users don't have this feature. So, they'll never know just how awesome it is to have real stored procedures like TSQL has. > Anyway, it's not about "jamming" things into one query. It's about composability and PGSQL is lacking it. What's your goto "killer example" of what this could be used for? I've used SQL Server quite a bit[1] and while it's not a terrible database, on the whole I don't like it. Rattling off at random: lack of MVCC (sure they have it now but it's not the default and still has warts), explicit locking that drives you nuts, defaulting to case insensitive string comparison, lack of built in functions. The inane licensing options don't help either. I'd take Postgres over SQL Server any day of the week and twice on Sundays (i.e. side projects). [1]: Officer I swear! It was already like that when I got there!
- WayneBro 10y ago
  > It's a bad idea for the same reason that having a function that returns back multiple values is a bad idea. OK, what's the reason then? I didn't hear you give one. Every PGSQL query already return multiple values because you get a command status along with the result set. Every Golang call returns 2 values. They even make it very easy for you to return even more values. So what is your reasoning?
  - koolba 10y ago
    > OK, what's the reason then? I didn't hear you give one. Read the rest of that paragraph. You only quoted the first sentence. > Every PGSQL query already return multiple values because you get a command status along with the result set. And most generic database driver interfaces (ex: JDBC) return that out of band. Errors get turned into exceptions and update counts get returned back as integers. That's far from perfect (particularly with JDBC) but it's usually done to fit the programming paradigms of the driver's native language. > Every Golang call returns 2 values. They even make it very easy for you to return even more values. So what is your reasoning? That's because Golang doesn't have exceptions. Every function that could fail needs to indicate if there's an error. It's like errno in C but per-function.
    - WayneBro 10y ago
      He gave an opinion. That is not a reason.
  - jhall1468 10y ago
    He did give you one, you just aren't listening. Having a function return the minimum and maximum of an array is returning one value. The value is an array of size 2. Having a function return an array of the minimum, maximum and the number of states in a given country is returning two values. You are asking PG to return multiple unrelated data sets. The number of use cases that this is useful and good is pretty much for displaying reports. Do two queries. The problem is already solved.
    - 10y ago

koolba 10y ago

> You didn't state a single reason why you think that grabbing multiple resultsets in a single trip to the database is a bad idea though... It's a bad idea for the same reason that having a function that returns back multiple values is a bad idea. Yes it's useful sometimes but on the whole it ends up being confusing. From a result set processing perspective it's also a pain in the ass as your app code is now tied to handling multiple results in a particular order, i.e., more/tighter coupling. > (I bet you've UPDATEd multiple tables in one query though.) You'd win that bet. There are a lot of ways to do this and it's a legit operation because you want N things to change at once (where N isn't in the same table). > Reasons are often given, sure...but they're almost never based on that persons experience because PGSQL users don't have this feature. So, they'll never know just how awesome it is to have real stored procedures like TSQL has. > Anyway, it's not about "jamming" things into one query. It's about composability and PGSQL is lacking it. What's your goto "killer example" of what this could be used for? I've used SQL Server quite a bit[1] and while it's not a terrible database, on the whole I don't like it. Rattling off at random: lack of MVCC (sure they have it now but it's not the default and still has warts), explicit locking that drives you nuts, defaulting to case insensitive string comparison, lack of built in functions. The inane licensing options don't help either. I'd take Postgres over SQL Server any day of the week and twice on Sundays (i.e. side projects). [1]: Officer I swear! It was already like that when I got there!

WayneBro 10y ago
> It's a bad idea for the same reason that having a function that returns back multiple values is a bad idea. OK, what's the reason then? I didn't hear you give one. Every PGSQL query already return multiple values because you get a command status along with the result set. Every Golang call returns 2 values. They even make it very easy for you to return even more values. So what is your reasoning?
- koolba 10y ago
  > OK, what's the reason then? I didn't hear you give one. Read the rest of that paragraph. You only quoted the first sentence. > Every PGSQL query already return multiple values because you get a command status along with the result set. And most generic database driver interfaces (ex: JDBC) return that out of band. Errors get turned into exceptions and update counts get returned back as integers. That's far from perfect (particularly with JDBC) but it's usually done to fit the programming paradigms of the driver's native language. > Every Golang call returns 2 values. They even make it very easy for you to return even more values. So what is your reasoning? That's because Golang doesn't have exceptions. Every function that could fail needs to indicate if there's an error. It's like errno in C but per-function.
  - WayneBro 10y ago
    He gave an opinion. That is not a reason.
- jhall1468 10y ago
  He did give you one, you just aren't listening. Having a function return the minimum and maximum of an array is returning one value. The value is an array of size 2. Having a function return an array of the minimum, maximum and the number of states in a given country is returning two values. You are asking PG to return multiple unrelated data sets. The number of use cases that this is useful and good is pretty much for displaying reports. Do two queries. The problem is already solved.
  - 10y ago