4 ms·

I'm having trouble imagining why you need this. Often when people come up with "interesting" scenarios involving UPSERT, they are trying to use the database as

I'm having trouble imagining why you need this. Often when people come up with "interesting" scenarios involving UPSERT, they are trying to use the database as persistent memory for some algorithm, rather than as a relational database. What are you actually trying to do? Why would it be insufficient to do something like: BEGIN; INSERT INTO entities (name) (VALUES ('a'), ('b'), ('c') EXCEPT SELECT name FROM entities); SELECT id, name FROM entities WHERE name IN ('a', 'b', 'c'); COMMIT;

kuschku 9y ago
You can even go further. Use WITH select id, name from entities where name in :data AS existing Then return a UNION of existing and (INSERT into entities (name) :data except existing returning id, name)
- jimktrains2 9y ago
  That still executes two queries under the hood. The basic issue is a misunderstanding of what INSERT RETURNING does. I've actually seen the specific misunderstanding many times on forums and on IRC. I wonder if it'd be possible to change the syntax to something like "RETURNING [ ALL | NEW ] * | output_expression [ [ AS ] output_name ]" where when empty "NEW" would be used and be equivalent to the current semantics where "ALL" would include rows that were not inserted on conflict. "ALL" would have no different meaning on "ON CONFLICT DO UPDATE" or when no conflict resolution was specified.
  - fusiongyro 9y ago
    I balk at the added complexity. Just use two queries. This is why God created transactions.
    - tomp 9y ago
      I think your solution is good. I'll try it. Assuming entries are only ever added (and never changed), you don't even need a transaction!
      - fusiongyro 9y ago
        You always need a transaction, even just for reads. Otherwise you are not reading from a single consistent snapshot of the world.
        merb 9y ago
        not everybody needs strong serializability. most people do read (sometimes even in another thread / other connection) -> transaction -> write. Also the basic transaction isolation in pg would still have these inconsistentsis of phantom/non repeatable reads and serialization anomalies.
        fusiongyro 9y ago
        There's a difference between not using transactions because you don't know better and not using transactions for reasons that arise out of a deep understanding of what is going on. 99% of the time, it's the former. The argument that transactions aren't perfect is a shitty reason not to use them, the kind of bad advice that doesn't hurt you today, but may hurt you in the future.
        merb 9y ago
        well I'm not against using transactions, I'm using them a lot. But I most often I read data on other threads where I do not need a transaction, especially for validating data or just getting data there is not the biggest need for transactions. And especially if your data can be dirty (not 100% accurate data) basically yes you need transactions to guard against dirty reads, but most often you don't care for dirty data, because if you are not having financial data or data that needs to be taken with care, let's say you have a timeline with posts, you don't care if the last read was dirty and probably the user does not care anyway, he will just refresh the page once he needs more up to date data.
        tomp 9y ago
        So you're saying that a SELECT statement that happens strictly after an INSERT statement (on the same connection) can possibly not see the data that was written by the INSERT statement? I guess that would be possible, but I would be very surprised if that was so... Also, I would also expect writes to rows to be atomic (i.e. you wouldn't see a row with half-written data, or a row that has an `id` but not (yet) a `name`) - again, that kind of behaviour would be possible, but very surprising to the point of the DB being unusable.
  - kuschku 9y ago
    > That still executes two queries under the hood. For now, there are optimizations planned for the query planner that would optimize this to a single query in the next releases. Some commercial databases already do that. INSERT RETURNING only returns the value of the changed rows, but it does not return anything if you redirect writes with triggers or child tables, including partitioning

kuschku 9y ago

You can even go further. Use WITH select id, name from entities where name in :data AS existing Then return a UNION of existing and (INSERT into entities (name) :data except existing returning id, name)

jimktrains2 9y ago
That still executes two queries under the hood. The basic issue is a misunderstanding of what INSERT RETURNING does. I've actually seen the specific misunderstanding many times on forums and on IRC. I wonder if it'd be possible to change the syntax to something like "RETURNING [ ALL | NEW ] * | output_expression [ [ AS ] output_name ]" where when empty "NEW" would be used and be equivalent to the current semantics where "ALL" would include rows that were not inserted on conflict. "ALL" would have no different meaning on "ON CONFLICT DO UPDATE" or when no conflict resolution was specified.
- fusiongyro 9y ago
  I balk at the added complexity. Just use two queries. This is why God created transactions.
  - tomp 9y ago
    I think your solution is good. I'll try it. Assuming entries are only ever added (and never changed), you don't even need a transaction!
    - fusiongyro 9y ago
      You always need a transaction, even just for reads. Otherwise you are not reading from a single consistent snapshot of the world.
      - merb 9y ago
        not everybody needs strong serializability. most people do read (sometimes even in another thread / other connection) -> transaction -> write. Also the basic transaction isolation in pg would still have these inconsistentsis of phantom/non repeatable reads and serialization anomalies.
        fusiongyro 9y ago
        There's a difference between not using transactions because you don't know better and not using transactions for reasons that arise out of a deep understanding of what is going on. 99% of the time, it's the former. The argument that transactions aren't perfect is a shitty reason not to use them, the kind of bad advice that doesn't hurt you today, but may hurt you in the future.
        merb 9y ago
        well I'm not against using transactions, I'm using them a lot. But I most often I read data on other threads where I do not need a transaction, especially for validating data or just getting data there is not the biggest need for transactions. And especially if your data can be dirty (not 100% accurate data) basically yes you need transactions to guard against dirty reads, but most often you don't care for dirty data, because if you are not having financial data or data that needs to be taken with care, let's say you have a timeline with posts, you don't care if the last read was dirty and probably the user does not care anyway, he will just refresh the page once he needs more up to date data.
      - tomp 9y ago
        So you're saying that a SELECT statement that happens strictly after an INSERT statement (on the same connection) can possibly not see the data that was written by the INSERT statement? I guess that would be possible, but I would be very surprised if that was so... Also, I would also expect writes to rows to be atomic (i.e. you wouldn't see a row with half-written data, or a row that has an `id` but not (yet) a `name`) - again, that kind of behaviour would be possible, but very surprising to the point of the DB being unusable.
- kuschku 9y ago
  > That still executes two queries under the hood. For now, there are optimizations planned for the query planner that would optimize this to a single query in the next releases. Some commercial databases already do that. INSERT RETURNING only returns the value of the changed rows, but it does not return anything if you redirect writes with triggers or child tables, including partitioning