Обсуждение: upsert with trigger (or rule)
Hello,
I’m trying to rewrite inserts to upserts on a table when a certain
column has a certain value. Reason: the inserts are coming from an ORM.
It’s possible to send upsert from the ORM, however, in this case I find
it more elegant and future-proof to deal with it at the DB level.
First attempt:
create rule messages_insert_draft as on insert to messages where
new.is_draft do instead insert into messages values (new.*) on conflict
(sender_id, recipient_id) where is_draft do update set body =
excluded.body, updated_at = excluded.updated_at;
This has two disadvantages:
1. It doesnt work because of the endless recursion. Is there a way to
deal with the recursion without adding another column to the table?
2. Every time the table’s sctructure changes the rule has to be updated too.
With trigger:
create function trigger_messages_insert_draft() returns trigger as $$
begin
insert into messages values (new.*) on conflict (sender_id,
recipient_id) where is_draft do update set body = excluded.body,
updated_at = excluded.updated_at;
return null;
end;
$$ language plpgsql;
create trigger messages_before_insert_draft before insert on messages
for each row when ( new.is_draft and pg_trigger_depth() = 0 ) execute
procedure trigger_messages_insert_draft();
This works fine. As far as I see adding new columns to messages table
won’t require updating the procedure. Which is great.
There is one issue though. The orm sends 'insert into messages ....
returning id'. As the original insert is skipped, the id, which is a
serial, is not returned, so the orm can’t see the new/updated record's id.
Is there a way to make the 'returning id' part work?
M.
On 07/19/2016 11:56 AM, Maeldron T. wrote: > Hello, > > I’m trying to rewrite inserts to upserts on a table when a certain > column has a certain value. Reason: the inserts are coming from an ORM. > It’s possible to send upsert from the ORM, however, in this case I find > it more elegant and future-proof to deal with it at the DB level. > > First attempt: > > create rule messages_insert_draft as on insert to messages where > new.is_draft do instead insert into messages values (new.*) on conflict > (sender_id, recipient_id) where is_draft do update set body = > excluded.body, updated_at = excluded.updated_at; > > This has two disadvantages: > > 1. It doesnt work because of the endless recursion. Is there a way to > deal with the recursion without adding another column to the table? > 2. Every time the table’s sctructure changes the rule has to be updated > too. > > > With trigger: > > create function trigger_messages_insert_draft() returns trigger as $$ > begin > insert into messages values (new.*) on conflict (sender_id, > recipient_id) where is_draft do update set body = excluded.body, > updated_at = excluded.updated_at; > return null; > end; > $$ language plpgsql; > > create trigger messages_before_insert_draft before insert on messages > for each row when ( new.is_draft and pg_trigger_depth() = 0 ) execute > procedure trigger_messages_insert_draft(); By returning NULL in your function you are skipping the original INSERT. I am sure exactly what you are trying to achieve, but it would seem the thing to is check for the uniqueness of (sender_id, recipient_id) in your function and then modify the original INSERT row as needed and then RETURN it as NEW. > > This works fine. As far as I see adding new columns to messages table > won’t require updating the procedure. Which is great. > > There is one issue though. The orm sends 'insert into messages .... > returning id'. As the original insert is skipped, the id, which is a > serial, is not returned, so the orm can’t see the new/updated record's id. > > Is there a way to make the 'returning id' part work? Not that I know of: https://www.postgresql.org/docs/9.5/static/plpgsql-trigger.html "A trigger function must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for." > > M. > > > -- Adrian Klaver adrian.klaver@aklaver.com
Hello Adrian, On 19/07/16 21:27, Adrian Klaver wrote: > On 07/19/2016 11:56 AM, Maeldron T. wrote: >> > > By returning NULL in your function you are skipping the original INSERT. I know that, I wrote it. > I am sure exactly what you are trying to achieve, In general, there are the following conditions: The application uses ORM. The ORM generates inserts. In this case it’s Ruby on Rails / ActiveRecord, but it could be anything else. The business logic requires uniqueness in certain cases. Upsert is finally there in PostgreSQL. It’s possible to tell the application to send 'upsert' (on conflict do), however, it works by turning off the ORM and build the query. To keep the advantages of the ORM, one has to build a library to generate the queries. Actually I did it, and it works fine, but there are cases when overwriting the insert into upsert in the database is simpler, more elegant, more efficient and more stable. > but it would seem the thing to is check for the uniqueness of > (sender_id, recipient_id) in your function and then modify the > original INSERT row as needed and then RETURN it as NEW. I don’t see how that would help. Except if you mean deleting the old record, which is neither efficient nor safe in race conditions. > >> >> This works fine. As far as I see adding new columns to messages table >> won’t require updating the procedure. Which is great. >> >> There is one issue though. The orm sends 'insert into messages .... >> returning id'. As the original insert is skipped, the id, which is a >> serial, is not returned, so the orm can’t see the new/updated >> record's id. >> >> Is there a way to make the 'returning id' part work? > > Not that I know of: > > https://www.postgresql.org/docs/9.5/static/plpgsql-trigger.html > > "A trigger function must return either NULL or a record/row value > having exactly the structure of the table the trigger was fired for." 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.
On 07/19/2016 01:25 PM, Maeldron T. wrote: > Hello Adrian, > > On 19/07/16 21:27, Adrian Klaver wrote: >> On 07/19/2016 11:56 AM, Maeldron T. wrote: >>> >> >> By returning NULL in your function you are skipping the original INSERT. > I know that, I wrote it. >> I am sure exactly what you are trying to achieve, > > In general, there are the following conditions: > > The application uses ORM. The ORM generates inserts. In this case it’s > Ruby on Rails / ActiveRecord, but it could be anything else. > > The business logic requires uniqueness in certain cases. > > Upsert is finally there in PostgreSQL. > > It’s possible to tell the application to send 'upsert' (on conflict do), > however, it works by turning off the ORM and build the query. To keep > the advantages of the ORM, one has to build a library to generate the > queries. Actually I did it, and it works fine, but there are cases when > overwriting the insert into upsert in the database is simpler, more > elegant, more efficient and more stable. > > >> but it would seem the thing to is check for the uniqueness of >> (sender_id, recipient_id) in your function and then modify the >> original INSERT row as needed and then RETURN it as NEW. > > I don’t see how that would help. Except if you mean deleting the old > record, which is neither efficient nor safe in race conditions. 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. > >> >>> >>> This works fine. As far as I see adding new columns to messages table >>> won’t require updating the procedure. Which is great. >>> >>> There is one issue though. The orm sends 'insert into messages .... >>> returning id'. As the original insert is skipped, the id, which is a >>> serial, is not returned, so the orm can’t see the new/updated >>> record's id. >>> >>> Is there a way to make the 'returning id' part work? >> >> Not that I know of: >> >> https://www.postgresql.org/docs/9.5/static/plpgsql-trigger.html >> >> "A trigger function must return either NULL or a record/row value >> having exactly the structure of the table the trigger was fired for." > 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. > > > > -- Adrian Klaver adrian.klaver@aklaver.com
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).
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.
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.
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.klaver@aklaver.com