8000 [Messenger] Fix cannot select FOR UPDATE from view on Oracle by rjd22 · Pull Request #45714 · symfony/symfony · GitHub
[go: up one dir, main page]
Skip to content

[Messenger] Fix cannot select FOR UPDATE from view on Oracle #45714

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Merged
merged 1 commit into from
Apr 1, 2022
Merged

[Messenger] Fix cannot select FOR UPDATE from view on Oracle #45714

merged 1 commit into from
Apr 1, 2022

Conversation

rjd22
Copy link
Contributor
@rjd22 rjd22 commented Mar 11, 2022 •
edited by nicolas-grekas
Loading
Q A
Branch? 4.4
Bug fix? yes
New feature? no
Deprecations? no
Tickets Fix #33718
License MIT

I realize this solution is quite getto. I hope to get some feedback on it so we can solve this oracle issue properly but ATM this is a good starting point to reach a proper solution.

For this reason I didn't add tests yet.

What happens is that Oracle doesn't like query's that combine ROWNUM and FOR UPDATE. To solve this the query needs to be wrapped in a subquery that limits the results by ID so:

SELECT a.* FROM (
    SELECT m.* 
    FROM messenger_messages m 
    WHERE (m.delivered_at is null OR m.delivered_at < ?) AND (m.available_at <= ?) AND (m.queue_name = ?) 
    ORDER BY available_at ASC
) a WHERE ROWNUM <= 1 FOR UPDATE;

becomes:

SELECT * FROM messenger_messages WHERE id in (
    SELECT a.id FROM (
        SELECT m.* 
        FROM messenger_messages m 
        WHERE (m.delivered_at is null OR m.delivered_at < ?) AND (m.available_at <= ?) AND (m.queue_name = ?) 
        ORDER BY available_at ASC
    ) a WHERE ROWNUM <= 1
) FOR UPDATE;

I don't really know another way to solve this better. But feel free to nitpick at my solution.

@carsonbot carsonbot added this to the 5.4 milestone Mar 11, 2022
@carsonbot
Copy link

Hey!

I see that this is your first PR. That is great! Welcome!

Symfony has a contribution guide which I suggest you to read.

In short:

  • Always add tests
  • Keep backward compatibility (see https://symfony.com/bc).
  • Bug fixes must be submitted against the lowest maintained branch where they apply (see https://symfony.com/releases)
  • Features and deprecations must be submitted against the 6.1 branch.

Review the GitHub status checks of your pull request and try to solve the reported issues. If some tests are failing, try to see if they are failing because of this change.

When two Symfony core team members approve this change, it will be merged and you will become an official Symfony contributor!
If this PR is merged in a lower version branch, it will be merged up to all maintained branches within a few days.

I am going to sit back now and wait for the reviews.

Cheers!

Carsonbot

@rjd22 rjd22 mentioned this pull request Mar 11, 2022
Closed
@carsonbot carsonbot changed the title Fix #33718: ORA-02014: cannot select FOR UPDATE from view [Messenger] Fix #33718: ORA-02014: cannot select FOR UPDATE from view Mar 12, 2022
@nicolas-grekas
Copy link
Member

I'd trust you on this one as I don't use Oracle. I don't have a better idea, code wise, so I'm fine with this approach.
Can you please rebase to solve the conflict? You mentioned tests. Please add some if you can.

@nicolas-grekas nicolas-grekas changed the title [Messenger] Fix #33718: ORA-02014: cannot select FOR UPDATE from view [Messenger] Fix cannot select FOR UPDATE from view on Oracle Apr 1, 2022
@nicolas-grekas nicolas-grekas modified the milestones: 5.4, 4.4 Apr 1, 2022
@nicolas-grekas nicolas-grekas changed the base branch from 5.4 to 4.4 April 1, 2022 10:11
nicolas-grekas
nicolas-grekas reviewed Apr 1, 2022
View reviewed changes
Copy link
Member
@nicolas-grekas nicolas-grekas left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I've actually rebased on 4.4. I'm merging now but please follow up with tests if you can.

@nicolas-grekas
Copy link
Member

Thank you @rjd22.

@nicolas-grekas nicolas-grekas merged commit deb9030 into symfony:4.4 Apr 1, 2022
@rjd22
Copy link
Contributor Author
rjd22 commented Apr 1, 2022

@nicolas-grekas I will add some tests for this within a week.

This was referenced Apr 2, 2022
Merged
Merged
Merged
@rjd22 rjd22 mentioned this pull request Apr 7, 2022
Merged
@rjd22 rjd22 deleted the fix-oracle-support-symfony-messenger branch April 7, 2022 08:11
@rjd22
Copy link
Contributor Author
rjd22 commented Apr 7, 2022

@nicolas-grekas Tests added in #45966

chalasr added a commit that referenced this pull request Apr 7, 2022
@chalasr
minor #45966 Add tests to messenger connection get for OraclePlatform…
33420de 
… (rjd22)

This PR was merged into the 4.4 branch.

Discussion
----------

Add tests to messenger connection get for OraclePlatform

| Q             | A
| ------------- | ---
| Branch?       | 4.4
| Bug fix?      | no
| New feature?  | no
| Deprecations? | no
| License       | MIT

Add tests for Oracle for code added in #45714

Commits
-------

6d5887c Add tests to messenger connection get for OraclePlatform
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers

@nicolas-grekas nicolas-grekas nicolas-grekas left review comments

@lyrixx lyrixx Awaiting requested review from lyrixx

@yceruto yceruto Awaiting requested review from yceruto

@wouterj wouterj Awaiting requested review from wouterj

@chalasr chalasr Awaiting requested review from chalasr

@dunglas dunglas Awaiting requested review from dunglas

@xabbuh xabbuh Awaiting requested review from xabbuh

Assignees
No one assigned
Labels
Bug Messenger Status: Needs Review
Projects
None yet
Milestone
4.4
Development

Successfully merging this pull request may close these issues.
4 participants
@rjd22 @carsonbot @nicolas-grekas @derrabus
0