This project is archived and is in readonly mode.

"Returning ID" does not work due to trigger

#6767

I use PostgreSQL table partitioning. That is, a trigger is to be invoked prior to every INSERT query.

The trigger looks like this:

create or replace function insert_into_partition()
returns trigger
as $$
begin
    ... (insert into child table instead!)
    return null; -- note this
end;
$$
language plpgsql;

The problem is: the trigger somehow breaks the "returning id" query generated by Rails. As a result, the record is saved correctly into the database, but the ActiveRecord instance cannot retrieve the newly created ID. For example:

person = Person.new(:fullname => 'John', :age => 21)
person.save
# then, person.id --> nil

The problem is, as I figured out, due to the "return null" statement of the corresponding trigger. However, I suggest that Rails should never return a nil value after a successful save statement. There should be kind of check for the value of id before assigning it back to the ActiveRecord instance.

Regards,

Other information:
Gem: 1.7.2
Rails: 3.0.7
Pg: 0.11.0
Platform: Ubuntu 10.04

Reported by Nghi Pham · May 3rd, 2011 @ 04:55 AM

Activity

Kane
i dont get it. why dont you just change your trigger to return the id?

also i dont quite get the last part

However, I suggest that Rails should never return a nil value after a successful save statement. There should be kind of check for the value of id before assigning it back to the ActiveRecord instance.

Does person.save return true?
you think it should return false and probably abort the transaction cause no id is given?

May 3rd, 2011 @ 03:17 PM
Nghi Pham

i dont get it. why dont you just change your trigger to return the id? Yes, "return true" in the trigger may somehow solve the problem. However, what I mean here is the stability of Rails: it should always retrieve the proper id after an insert regardless of whether or not "insert into...returning id" query is supported properly.

In this case, it's a fault of PostgreSQL itself that a trigger with "return null" breaks the "returning id" query (null returned). So, I suggest that Rails should not rely on this unstable feature of PostgreSQL. It should do extra work to make sure "returning id" is returning the right id; otherwise, try other way to obtain the newly inserted id

Does person.save return true? you think it should return false and probably abort the transaction cause no id is given? Yes, I think it should, in the worst case!
However, as I mentioned above, this is a problem of PostgreSQL and Rails should have a workarround for this

May 4th, 2011 @ 04:29 AM
Nghi Pham

i dont get it. why dont you just change your trigger to return the id?

Yes, "return true" in the trigger may somehow solve the problem. However, what I mean here is the stability of Rails: it should always retrieve the proper id after an insert regardless of whether or not "insert into...returning id" query is supported properly.

In this case, it's a fault of PostgreSQL itself that a trigger with "return null" breaks the "returning id" query (null returned). So, I suggest that Rails should not rely on this unstable feature of PostgreSQL. It should do extra work to make sure "returning id" is returning the right id; otherwise, try other way to obtain the newly inserted id

Does person.save return true? you think it should return false and probably abort the transaction cause no id is given?

Yes, I think it should, in the worst case!
However, as I mentioned above, this is a problem of PostgreSQL and Rails should have a workarround for this

May 4th, 2011 @ 04:32 AM