Select for update with outer join broken?

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