Re: upsert with trigger (or rule)

On 07/20/2016 12:54 PM, Maeldron T. wrote:
> On 19/07/16 23:45, Adrian Klaver wrote:
>>
>> To be more complete it would nice to see the schema definition for the
>> table messages.
>>
>> Also maybe some idea of what you the code is supposed to do. If I
>> understand it correctly:
>>
>> 1) Check if a message is a draft.
>>
>> 2) Check if there is a uniqueness conflict with an existing
>> (sender_id, recipient_id) combination
>>
>> 3) If 1) and 2) are true then UPDATE the fields body and updated_at of
>> the existing record with the NEW.body and NEW.updated_at data.
>
> The table and the triggers altogether are huge.
>
> Anyway, you got it right, the point is that the recipient_id and
> sender_id must be unique only if the message is a draft (autosave feature).

If the user is sitting on a record and changing the field data and
system is autosaving, why are INSERTs being done instead of UPDATEs?

>
> But I’m looking for a general solution as there are other tables and
> other cases when "converting" the insert to upsert would be awesome.

All I've got now is:

https://www.postgresql.org/docs/9.5/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

Example 40-2. Exceptions with UPDATE/INSERT

>
> Let’s say that a part of the system will be accessed by another ORM to
> increase the performance. (Hello Rust). Only a DB-level solution
> requires no code duplication.
>
> Accessing the DB with multiple ORMs isn’t uncommon on large scale so
> this might be not only my issue.
>
>> This alone doesn’t prove that it’s not possible.
>>>
>>> The value returned by "returning id" might be set or read from the
>>> existing or inserted record in some way.
>>
>> By returning NULL you said the original INSERT never happened and
>> nothing is returned, so no id is returned. The embedded INSERT happens
>> outside the scope of the trigger.
> I see your point and you probably are right. In theory though, it’s
> possible that there is a solution to manually store the returning id. I
> mean maybe a system table or so. Postgresql knows what the client asked
> to return. This must be somewhere. It sounds hackish though.
>
> It hasn’t be a trigger at all. I’m 99% sure I could make it work by
> using a rule and a view or parent or a child table (to avoid the endless
> recursion). However, these together aren’t less complicated than doing
> it through the ORM. The rule isn’t dynamic (regarding the changes in the
> columns) which makes the solution problematic.
>
> The trigger is simple. It allows adding new columns to the table without
> rewriting the trigger. And it works, except that the ORM has no idea
> about the new record’s id. It’s possible to find the record by the
> unique colums, however, the whole point is not modifying the ORM at all.
>
> M.
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com