order not set on child insertion using postgresql #84
Description
In versions 4.2.7 and 4.3.0 using PostgreSQL9.2.
Table is set up with:
acts_as_tree order: 'sort_order', hierarchy_table_name: 'dcache_hierarchies', dependent: :delete_all
and table has an integer field named "sort_order".
When adding a child using the following steps the sort_order is never updated on the child records:
r = Dcache.create => #<Dcache id: 80, type: nil, value: nil, parent_id: nil, sort_order: nil, created_at: "2014-01-13 23:26:08", updated_at: "2014-01-13 23:26:08"> c = Dcache.new(value: 'child') => #<Dcache id: nil, type: nil, value: "child", parent_id: nil, sort_order: nil, created_at: nil, updated_at: nil> r.children << c => [#<Dcache id: 81, type: nil, value: "child", parent_id: 80, sort_order: nil, created_at: "2014-01-13 23:26:33", updated_at: "2014-01-13 23:26:33">] c2 = Dcache.new(value: 'child2') => #<Dcache id: nil, type: nil, value: "child2", parent_id: nil, sort_order: nil, created_at: nil, updated_at: nil> r.children << c2 => [#<Dcache id: 81, type: nil, value: "child", parent_id: 80, sort_order: nil, created_at: "2014-01-13 23:26:33", updated_at: "2014-01-13 23:26:33">, #<Dcache id: 82, type: nil, value: "child2", parent_id: 80, sort_order: nil, created_at: "2014-01-13 23:26:53", updated_at: "2014-01-13 23:26:53">] Note that both child records have sort_order = nil
Here's the same thing with all SQL logged:
1.9.3-p448 :001 > Dcache.delete_all SQL (1.7ms) DELETE FROM "dcaches" => 2 1.9.3-p448 :002 > r = Dcache.create (0.2ms) BEGIN SQL (41.3ms) INSERT INTO "dcaches" ("created_at", "parent_id", "sort_order", "type", "updated_at", "value") VALUES ($1, $2, $3, $4, $5, $6) RETURNING "id" [["created_at", Mon, 13 Jan 2014 23:26:08 UTC +00:00], ["parent_id", nil], ["sort_order", nil], ["type", nil], ["updated_at", Mon, 13 Jan 2014 23:26:08 UTC +00:00], ["value", nil]] (0.3ms) SELECT pg_try_advisory_lock(2085799232), 1389655568.840321 SQL (0.6ms) INSERT INTO "dcache_hierarchies" ("ancestor_id", "descendant_id", "generations") VALUES ($1, $2, $3) [["ancestor_id", 80], ["descendant_id", 80], ["generations", 0]] Dcache Load (0.8ms) SELECT "dcaches".* FROM "dcaches" WHERE "dcaches"."parent_id" = 80 ORDER BY sort_order (0.5ms) UPDATE "dcaches" SET "sort_order" = t.seq + -1 FROM ( SELECT "id" AS id, row_number() OVER(ORDER BY sort_order) AS seq FROM "dcaches" WHERE "parent_id" = 80 ) AS t WHERE "dcaches"."id" = t.id (0.2ms) SELECT pg_advisory_unlock(2085799232), 1389655568.887552 (2.3ms) COMMIT => #<Dcache id: 80, type: nil, value: nil, parent_id: nil, sort_order: nil, created_at: "2014-01-13 23:26:08", updated_at: "2014-01-13 23:26:08"> 1.9.3-p448 :003 > c = Dcache.new(value: 'child') => #<Dcache id: nil, type: nil, value: "child", parent_id: nil, sort_order: nil, created_at: nil, updated_at: nil> 1.9.3-p448 :004 > r.children << c (0.2ms) BEGIN SQL (0.6ms) INSERT INTO "dcaches" ("created_at", "parent_id", "sort_order", "type", "updated_at", "value") VALUES ($1, $2, $3, $4, $5, $6) RETURNING "id" [["created_at", Mon, 13 Jan 2014 23:26:33 UTC +00:00], ["parent_id", 80], ["sort_order", nil], ["type", nil], ["updated_at", Mon, 13 Jan 2014 23:26:33 UTC +00:00], ["value", "child"]] (0.2ms) SELECT pg_try_advisory_lock(2085799232), 1389655593.266377 SQL (0.3ms) INSERT INTO "dcache_hierarchies" ("ancestor_id", "descendant_id", "generations") VALUES ($1, $2, $3) [["ancestor_id", 81], ["descendant_id", 81], ["generations", 0]] (0.4ms) INSERT INTO "dcache_hierarchies" (ancestor_id, descendant_id, generations) SELECT x.ancestor_id, 81, x.generations + 1 FROM "dcache_hierarchies" x WHERE x.descendant_id = 80 Dcache Load (0.3ms) SELECT "dcaches".* FROM "dcaches" WHERE "dcaches"."parent_id" = 81 ORDER BY sort_order (0.4ms) UPDATE "dcaches" SET "sort_order" = t.seq + -1 FROM ( SELECT "id" AS id, row_number() OVER(ORDER BY sort_order) AS seq FROM "dcaches" WHERE "parent_id" = 81 ) AS t WHERE "dcaches"."id" = t.id (0.2ms) SELECT pg_advisory_unlock(2085799232), 1389655593.271582 (2.2ms) COMMIT => [#<Dcache id: 81, type: nil, value: "child", parent_id: 80, sort_order: nil, created_at: "2014-01-13 23:26:33", updated_at: "2014-01-13 23:26:33">] 1.9.3-p448 :005 > c2 = Dcache.new(value: 'child2') => #<Dcache id: nil, type: nil, value: "child2", parent_id: nil, sort_order: nil, created_at: nil, updated_at: nil> 1.9.3-p448 :006 > r.children << c2 (0.2ms) BEGIN SQL (0.5ms) INSERT INTO "dcaches" ("created_at", "parent_id", "sort_order", "type", "updated_at", "value") VALUES ($1, $2, $3, $4, $5, $6) RETURNING "id" [["created_at", Mon, 13 Jan 2014 23:26:53 UTC +00:00], ["parent_id", 80], ["sort_order", nil], ["type", nil], ["updated_at", Mon, 13 Jan 2014 23:26:53 UTC +00:00], ["value", "child2"]] (0.2ms) SELECT pg_try_advisory_lock(2085799232), 1389655613.5307488 SQL (0.3ms) INSERT INTO "dcache_hierarchies" ("ancestor_id", "descendant_id", "generations") VALUES ($1, $2, $3) [["ancestor_id", 82], ["descendant_id", 82], ["generations", 0]] (0.3ms) INSERT INTO "dcache_hierarchies" (ancestor_id, descendant_id, generations) SELECT x.ancestor_id, 82, x.generations + 1 FROM "dcache_hierarchies" x WHERE x.descendant_id = 80 Dcache Load (0.3ms) SELECT "dcaches".* FROM "dcaches" WHERE "dcaches"."parent_id" = 82 ORDER BY sort_order (0.3ms) UPDATE "dcaches" SET "sort_order" = t.seq + -1 FROM ( SELECT "id" AS id, row_number() OVER(ORDER BY sort_order) AS seq FROM "dcaches" WHERE "parent_id" = 82 ) AS t WHERE "dcaches"."id" = t.id (0.2ms) SELECT pg_advisory_unlock(2085799232), 1389655613.535767 (2.2ms) COMMIT => [#<Dcache id: 81, type: nil, value: "child", parent_id: 80, sort_order: nil, created_at: "2014-01-13 23:26:33", updated_at: "2014-01-13 23:26:33">, #<Dcache id: 82, type: nil, value: "child2", parent_id: 80, sort_order: nil, created_at: "2014-01-13 23:26:53", updated_at: "2014-01-13 23:26:53">] 1.9.3-p448 :007 >