This project is archived and is in readonly mode.

#6767 new
Nghi Pham

"Returning ID" does not work due to trigger

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

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

Comments and changes to this ticket

  • Kane

    Kane May 3rd, 2011 @ 03:17 PM

    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?

  • Nghi Pham

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

    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

  • Nghi Pham

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

    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

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

People watching this ticket

Pages