Oracle compatibility(lock inside subquery)

[NZX]

Hi, as the follow up of this issue: refinery/refinerycms#2066, I further discovered the library is not happy with Oracle database. When I create a new page in refinerycms, I've got this:

OCIError: ORA-00907: missing right parenthesis: SELECT * FROM (SELECT "REFINERY_PAGES".* FROM "REFINERY_PAGES" ORDER BY "REFINERY_PAGES"."RGT" desc FOR UPDATE) WHERE ROWNUM <= 1)

As mentioned by rsim(rsim/oracle-enhanced#260), Oracle doesn't support lock in subquery. I removed the lock and the above error is gone. However, when I delete one page, all other pages get deleted at the same times. I also get this error while trying to reorder pages:
ActiveRecordError (Impossible move, target node cannot be inside moved tree.)

I just notice this issue #70, Was the "lock" committed after this discussion?

Any hint on this issue would be much appreciated. Thanks:)

[NZX]

I reverted all the changes before the lock was add(0868405#lib/awesome_nested_set/awesome_nested_set.rb):

#highest_right_row = nested_set_scope(:order => "#{quoted_right_column_full_name} desc").limit(1).lock(true).first
#maxright = highest_right_row ? (highest_right_row[right_column_name] || 0) : 0
maxright = nested_set_scope.maximum(right_column_name) || 0

Everything is fixed after this change. Should the lock even be there?

[parndt]

The lock was introduced by @MarkusQ to fix problems with concurrency.

[NZX]

hmm, ok, apparently, the above code generates invalid Oracle SQL as addressed by @awd-switzerland in #70. Thanks Phil:)

[MarkusQ]

hmm, ok, apparently, the above code generates invalid Oracle SQL as
addressed by @awd-switzerland in #70. Thanks Phil:)

I don't have ready access to an Oracle server, etc. to test, and have
been tied up with work of my own, so haven't done anything further with
this. The intent of the lock is that the "highest_right_row" we get
back isn't also obtained by another concurrent copy of the same code
(thus causing data corruption).

We're only getting one row (due to the :first) and this is the one we're
locking, so it seems as if the :limit => 1 could be safely removed.
This may produce a query more to Oracle's liking.

This is just speculation I my part, offered in the hopes that it ,ay be
helpful, and should be looked at / tested by others. :)

-- MarkusQ

[NZX]

Thanks @MarkusQ for the explanation. I look into the Gemfile and the Oracle section is grey out. Seems not much of testing has been done with Oracle yet. It's not really a Rails favorite I guess.

[parndt]

It's really hard to test on Oracle ;-) you're right not many people Rails developers use it and it makes CI hard because of the ginormous cost involved.

[collingridge]

Oracle Express Edition is free, actually.

[parndt]

@collingridge ah good to know :)

[jhanggi]

Did anyone have any luck with this? I'm running into this same problem.

[parndt]

Fixed by #187