#postgresql multi inserts return order

96 messages · Page 1 of 1 (latest)

sweet drift
#

You can insert multiple entries in one query in SQL

#

That way you can also get all the IDs back at the same time

plush bough
#

Just do a select for all items with those IDs afterwards?

#

I don't know how performance critical this is. That seems an easy answer though.

covert smelt
#

@cosmic cypress do your database tables not have unique indexes on any fields? it might help if you can give a complete/concrete example—maybe you can expand your "4 objects" scenario and explain exactly what the database and in-memory objects would look like during that process

#

upon re-read i think you might be saying there is a unique field (ID) but it's assigned by the database via auto-increment or somesuch?

plush bough
#

Apparently postgres does return in order for simple queries, but it isn't guaranteed by a specification

covert smelt
#

i'm still confused, why does the order matter? if RETURNING * doesn't already give you what you want for some reason, it'll at least give you the generated IDs that you could then use to select exactly the rows you want (or run other queries as needed)

plush bough
#

How bad is it to just insert them 1 by 1?

#

Yeah

covert smelt
#

ah, ApplyValuesToEntityFromDatabaseResult(player.inventory, insertResult) has a side effect of mutating player.inventory?

plush bough
#

So is there like a playerId + itemId combination that acts as a unique identifier?

#

then you can match up the results after in JS?

#

Can there be?

covert smelt
#

can't you use insertResult directly to decide what items to insert rather than player.inventory.items? that way you'll only be looking at inventories that were actually inserted (and not also ones that may have existed before)

plush bough
#

So can you have
Player Item Number
1 Apple 3
1 Apple 1

?

#

Are items fungible?

#

Because if the are fungible it would seem better to require items to be combined. Then you have a unique identifier

#

Player + Item, that you can use to relate the input to the output

covert smelt
#

right. i perhaps still don't understand the problem (which is why i wanted a complete/concrete example), but i thought you were saying that for (const item of player.inventory.items) is looping over items that it shouldn't be. if you instead did for (const item of itemResult) or whatnot then that would solve that problem

plush bough
#

Is a batch insert + on duplicate update possible?

#

aka batch upsert?

covert smelt
#

i dunno what they meant by "delayed" earlier though. maybe batch upserts are not enough

plush bough
#

I'm thinking make a map of player+item to entry when inserting, do the batch upsert, then loop over the result using the map to relate the result back to the input

covert smelt
#

the same item can be in multiple inventories?

#

i think i'm just too lost on the details. i'll jump back in if you can write it all up, otherwise i'll duck out and let @plush bough continue helping

plush bough
#

I don't see a solution if you have to generate a unique id for a certain items, unless you can handle generating those IDs before inserting. Seems viable to make that a separate process, maybe store unique IDs in memory. Then that unique ID gives you a unique handle to relate back again

plush bough
plush bough
#

I explained that here

covert smelt
#

FWIW these days i almost always generate IDs in code and not the DB. i find it makes many things simpler, and isn't usually very expensive if you're smart about it. it also naturally helps with future scaling stuff (DB sharding, etc)

#

a giant uuid or a giant number aren't that different IMO

plush bough
#

If a player can't have 2 "apple" items, and instead the query updates the "apple" row by increasing the count, then you can relate back using player ID + "apple". You might have multiple "apple" inputs, but then maybe there is no problem. I'm not actually sure of what the purpose of relating back to the inputs does, what is that for?

#

For fungible (non unique) items, I don't think you would need to

#

For unique items you can generate a unique ID before inserting

#

I've explained this twice. You can re-read the comments until you understand

#

I am talking about changing the way you handle it to solve the problem you presented

#

If items are unique, generate the ID before you insert, like I've said

#

If you have a single server you can just have an int and +1 for each item to get an ID

#

That's not related to anything I've been telling you

#

Why?

#

You have more than 1 process inserting?

#

You can give a unique prefix to different processes

#

So they cannot conflict

#

I don't know what "out of order" means, I'm not sure items have any meaningful order

#

Yeah...

#

here's the source code

covert smelt
#

you generate the ID on your server right before insert (at least that's what i do). there's a separate type used for the values needed to specify an item to be created (which may be different from the item itself in more ways than just lacking an ID). ItemSpec vs ItemEntity or whatever

plush bough
#

If you think updating postgres source and maintaining your own fork that has consistent return ordering is easier, than be my guest. Let me know how it goes.

covert smelt
#

i don't think i grok what the first query is doing anyway. the concept of "creating an inventory" detached from player creation confounds me a bit. is it like when the player buys a bag or something?

plush bough
#

Usually the player inventory is their space that holds their items

#

So it just means "player 1 picked up 3 apples, register these item to the player in the database"

#

The batching is a bit confusing to me though.

covert smelt
plush bough
#

Where is the inventory creation?

covert smelt
#

INSERT INTO inventories? but again i'm just guessing here

plush bough
#

Ah

#

maybe this is intended to fail if the entry already exists

#

So it's just lazy creation

#

But, that would be a little strange

# 
 const [insertResult] = await sql<PlayerInventory[]>`INSERT INTO inventories ${sql(player.inventory, GetDatabaseColumnsOfEntity(PlayerInventory))} RETURNING *`;
                    
                    ApplyValuesToEntityFromDatabaseResult(player.inventory, insertResult);
#

Maybe it's like saving the state of the player's inventory? I don't know without xor's input

covert smelt
#

¯_(ツ)_/¯

plush bough
#

Maybe I scared him off

covert smelt
#

he's busy patching postgres 🚎

plush bough
#

💡

covert smelt
#

my bad, i missed that. so players are created with arbitrary items already present in their inventory? and there's a possibility to (re-)create the same player more than once and that needs to be handled gracefully?

plush bough
#

So the new player gets certain starting items? Does it vary depending on their character choice? It seems to be that if a player is created with 3 shirts, and each shirt has a unique ID, it really doesn't matter which of the shirts from the input map to which if the shirts that were created. As this is player creation those shirts should all be equivalent.

#

After they've been created with a unique ID and allowed out into the world, maybe one will take damage, and then uniqueness matters.

#

Probably the result of the insert is assigned to the player as their inventory, and they aren't allowed to interact with the items at all before the insert. If you have a kind of lazy creation and they can start interacting first then of course you have an issue. But doesn't seem likely something that is really necessary.

#

Is there any reason why you need to have multiple "shirt" entries for a player, with a count for each entry?

#

There are 2 simple ways to do it: A) separate entry for each shirt B) only one entry that keeps the count of all shirts

#

Do you have something like stacks, where you could have a stack of 2, and a stack of 1?

#

Also your result contains the number in each entry if you do it that way, so again why does it need to reference the input?

#

Yeah I see

#

So just assign the result to the player. That contains all the info. Any reason you need to match the result rows to the input?

#

Right

#

So what's the reason that you need to relate it to the original stack? Is it something like player 1 picks up a stack of 3 apples, and puts them in position 8

#

then player 1 removes that stack before the database replies

#

so you want to match it up?

#

Options are to either not let the player interact with the stack or choose where it goes until it have been processed by the server and assigned an ID

#

Or assign it an ID on the client, and then use that for tracking.

#

I think there will only be 1 client process

plush bough
#

Do you not actually need this, you just want to know a way to do it because it bugs you?

plush bough
#

I asked you why you need to know which input object matches up to each inserted row

#

You seem to kind of avoid the questions, and keep going back to this issue of just matching up the input to the result, rather than talking about the actual problem in general

plush bough
#

Yeah, I put that here a while ago, or something similar

plush bough
#

So, can you answer, why do you need to match up the original item to the created row?

#

I see

#

I feel that you really need to assign unique IDs on the client then

#

It's pretty simple. Give a clientId, save that in the database, and use that to map back to the original thing.

#

It should be OK to make sure clientIds are always unique. They only need to be unique to that player, not to other players, because you can also check the playerId