Igal, thanks for the analysis.
Craig>
Part of the question for Pg is what exactly we should and should not be returning.
I think the following might be a good starting point: return set of columns that would identify the inserted row(s).
E.g. columns of any primary key would do. Columns of any unique key would do as well.
"returning *" would do as well, however it would return excessive columns, thus it would be less efficient.
I do not think it makes sense to tie "generated keys" to sequences or things like that.
For example:
1) Consider Pg returns column_name==ABC, value==42. That would mean client could locate exactly that row via "where ABC=42"
2) Same for multicolumn keys: Pg just returns (col1, col2) == (42, 146). Then client would be able to locate the row via "where col1=42 and col2=146
3) If multiple unique keys present, it is fine if Pg returns one or the another depending on the phase of the moon. Yet more compact key would be preferable to save on bandwidth.
Does that make sense?
I think naming the resulting column(s) like "generated_key" / "generated_keys" does not make much sense. Especially, for multi-column keys.
If ctid was update-proof, it could could do. Unfortunately, ctid might easily get changed.
Theoretical end-to-end (it is the only use of "generated keys" I can imagine at the moment):
1) Client issues an insert statement, asking "generated keys"
2) Pg inserts the row, and returns resultset with "primary key" (or unique key) columns.
3) Client stores it somewhere. For instance, passes that to UI.
4) As UI wants to update the row, client just uses those keys to identify the row to update.
PS. Frankly speaking, I feel "generated keys" is more like a "plug & pray" kind of API. ORMs should know the column names of the primary keys => ORMs should use "return specific column names" API, not just "return something generated".
Vladimir