Thread: Select for update with outer join broken?
All, Some change which was made in the last couple weeks broke select for update with an outer join: Please examine the SQLException for more information. NestedException: org.postgresql.util.PSQLException: ERROR: SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join at com.sun.jdo.spi.persistence.support.sqlstore.SQLStoreManager.throwJDOSql Exception(SQLStoreManager.java:632) This was working per SQL spec before beta ... what happened? Error above is from Thursday's snapshot. -- Josh Berkus PostgreSQL @ Sun San Francisco
Josh Berkus <josh@agliodbs.com> writes:
> Some change which was made in the last couple weeks broke select for update
> with an outer join:
> NestedException: org.postgresql.util.PSQLException: ERROR: SELECT FOR
> UPDATE/SHARE cannot be applied to the nullable side of an outer join
What SQL query is it complaining about, exactly? That error message has
been there right along, but I seem to recall having moved the place
where it's checked for since 8.1.
regards, tom lane
Tom, > What SQL query is it complaining about, exactly? That error message has > been there right along, but I seem to recall having moved the place > where it's checked for since 8.1. It's from SpecJAppserver. I've requested the help of a java geek in extracting the query. FYI, this was working using the snapshot from about a month ago. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco
Josh Berkus <josh@agliodbs.com> writes:
>> What SQL query is it complaining about, exactly? That error message has
>> been there right along, but I seem to recall having moved the place
>> where it's checked for since 8.1.
> FYI, this was working using the snapshot from about a month ago.
But did it ever work against a release? The backend's test code for
this was busted for awhile during 8.2devel.
regards, tom lane
Tom, > But did it ever work against a release? The backend's test code for > this was busted for awhile during 8.2devel. No, because SQL-standard correct SELECT FOR UPDATE outer join is an 8.2 feature. We didn't have it in 8.1. -- Josh Berkus PostgreSQL @ Sun San Francisco
Josh Berkus <josh@agliodbs.com> writes:
>> But did it ever work against a release? The backend's test code for
>> this was busted for awhile during 8.2devel.
> No, because SQL-standard correct SELECT FOR UPDATE outer join is an 8.2
> feature. We didn't have it in 8.1.
Josh, you don't know what you're talking about. The backend's
capabilities for this have not moved an inch since 8.1 (transient bugs
in its error checking do not represent an advance in capability),
and furthermore I'm not seeing anyplace in the SQL spec that suggests
the nullable side of an outer join should be updatable.
regards, tom lane
Tom, > Josh, you don't know what you're talking about. The backend's > capabilities for this have not moved an inch since 8.1 (transient bugs > in its error checking do not represent an advance in capability), Hmmm ... was this an unapplied patch? We certainly had it working on the benchmark machine. Researching ... > and furthermore I'm not seeing anyplace in the SQL spec that suggests > the nullable side of an outer join should be updatable. Oh, no, what should happen is that the outer join portion of the query doesn't get locked, rather than a fatal exception. That behavior is expected by the J2EE certification, so it's at least somewhat industry-standard. -- Josh Berkus PostgreSQL @ Sun San Francisco
Josh Berkus <josh@agliodbs.com> writes:
> Oh, no, what should happen is that the outer join portion of the query doesn't
> get locked, rather than a fatal exception. That behavior is expected by the
> J2EE certification, so it's at least somewhat industry-standard.
Really? Please cite chapter and verse. And why should it be that the
unmatched outer-side rows don't get locked (which is what I think you
just said)?
regards, tom lane
Tom, OK, figured out what happened. I submitted the desired change, *coincidentally* right before the error message got broken. As a result, I mistakenly believed that the behaviour had been fixed by someone else before I could get to a patch. Since I was travelling at the time (OSCON) I didn't check to see that there actually had been a patch. OOops. I will have to find standards documentation on this grey area and hopefully submit something for 8.3. -- Josh Berkus PostgreSQL @ Sun San Francisco