Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

On 2017-06-12 21:03:31 -0400, Tom Lane wrote: > Andres Freund writes: > > Possibly too hard to be precise enough in a hint, but a number of these > > could benefit from one suggesting moving things into FROM, using > > LATERAL. > > Maybe "You might be able to move the

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

Andres Freund writes: > Possibly too hard to be precise enough in a hint, but a number of these > could benefit from one suggesting moving things into FROM, using > LATERAL. Maybe "You might be able to move the set-returning function into a LATERAL FROM item."? > I'm kinda

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

On 2017-06-09 17:33:45 -0400, Tom Lane wrote: > diff --git a/src/backend/catalog/information_schema.sql > b/src/backend/catalog/information_schema.sql > index cbcd6cf..98bcfa0 100644 > --- a/src/backend/catalog/information_schema.sql > +++ b/src/backend/catalog/information_schema.sql > @@

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

Alvaro Herrera writes: > Interesting stuff. Here's a small recommendation for a couple of those > new messages. Hm. I don't object to folding those two messages into one, but now that I look at it, the text needs some more work anyway, perhaps. What we're actually

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

Tom Lane wrote: > I wrote: > > Yes, we already have guards for those cases, but they return fairly opaque > > error messages to the tune of "set-valued function called in context that > > cannot accept a set", because the executor hasn't enough context to do > > better. I'd like the messages to

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

I wrote: > Yes, we already have guards for those cases, but they return fairly opaque > error messages to the tune of "set-valued function called in context that > cannot accept a set", because the executor hasn't enough context to do > better. I'd like the messages to be more specific, like

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

Andres Freund writes: > On 2017-06-08 23:05:53 -0400, Tom Lane wrote: >> ... The first >> attached patch does it that way, and it seems nice and clean, but I ran >> into a complete dead end while trying to extend it to handle related cases >> such as disallowing

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

Hi, On 2017-06-08 23:05:53 -0400, Tom Lane wrote: > I spent some time experimenting with this, and immediately found out > that information_schema.user_mapping_options contains an instance of the > problematic usage :-(. However, that view also turns out to be a poster > child for why our old

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

I wrote: > As to *how* to throw an error, I think it should be possible to teach > parse analysis to detect such cases, with something like the > ParseExprKind mechanism that could be checked to see if we're inside > a subexpression that restricts what's allowed. There are some other > checks

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

"'Andres Freund'" writes: > On 2017-06-08 11:57:49 -0400, Regina Obe wrote: >> My main concern in these cases is the short-circuiting not happening. > Note there's also no short-circuiting e.g. for aggregates inside case > either. Well, depends. If const-folding manages to

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

On 2017-06-08 11:57:49 -0400, Regina Obe wrote: > My main concern in these cases is the short-circuiting not happening. Note there's also no short-circuiting e.g. for aggregates inside case either. - Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

> But this line of thinking does strengthen my feeling that throwing an error is the right thing to do for the moment. If we allow v10 to accept such cases but do something different from what we used to, that > will greatly complicate any future attempt to try to restore the old behavior. >

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

"Regina Obe" writes: > I'm not a fan of either solution, but I think what Tom proposes of throwing > an error sounds like least invasive and confusing. > I'd much prefer an error thrown than silent behavior change. Given that we > ran into this in 3 places in PostGIS code, I'm not

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

> After chewing on this for awhile, I'm starting to come to the conclusion that we'd be best off to throw an error for SRF-inside-CASE (or COALESCE). Mark is correct that the simplest case of > SELECT x, CASE WHEN y THEN generate_series(1,z) ELSE 5 END > FROM

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

On Wed, Jun 7, 2017 at 11:57 AM, Tom Lane wrote: > If people are on board with throwing an error, I'll go see about > writing a patch. > +1 from me. David J.​

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

Mark Dilger writes: >> On Jun 4, 2017, at 2:19 PM, Andres Freund wrote: >> Seems very unlikely that we'd ever want to do that. The right way to do >> this is to simply move the SRF into the from list. Having the executor >> support arbitrary sources

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

> On Jun 4, 2017, at 2:19 PM, Andres Freund wrote: > > On 2017-06-04 14:16:14 -0700, Mark Dilger wrote: >> Sorry, I was not clear. What I meant to get at was that if you remove from >> the >> executor all support for SRFs inside case statements, you might foreclose >> the

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

On 2017-06-04 14:16:14 -0700, Mark Dilger wrote: > Sorry, I was not clear. What I meant to get at was that if you remove from > the > executor all support for SRFs inside case statements, you might foreclose the > option > of extending the syntax at some later date to allow aggregates over >

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

> On Jun 4, 2017, at 12:35 PM, Andres Freund wrote: > > Hi Mark, > > On 2017-06-04 11:55:03 -0700, Mark Dilger wrote: >>> Yea, I'm not a big fan of the either the pre v10 or the v10 behaviour of >>> SRFs inside coalesce/case. Neither is really resonable imo - I'm not >>>

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

Hi Mark, On 2017-06-04 11:55:03 -0700, Mark Dilger wrote: > > Yea, I'm not a big fan of the either the pre v10 or the v10 behaviour of > > SRFs inside coalesce/case. Neither is really resonable imo - I'm not > > sure a reasonable behaviour even exists. IIRC I'd argued in the > > original SRF

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

> On Jun 4, 2017, at 11:55 AM, Mark Dilger wrote: > > SELECT x, CASE WHEN y THEN SUM(generate_series(1,z)) ELSE 5 END > FROM table_with_columns_x_and_y; Sorry, this table is supposed to be the same as the previous one, table_with_columns_x_and_y_and_z --

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

> On Jun 2, 2017, at 8:11 PM, Andres Freund wrote: > > Hi, > > > On 2017-06-02 22:53:00 -0400, Tom Lane wrote: >> I think you've got enough on your plate. I can take care of whatever >> we decide to do here. > > Thanks. > > >> Andres Freund writes:

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

Hi, On 2017-06-02 22:53:00 -0400, Tom Lane wrote: > I think you've got enough on your plate. I can take care of whatever > we decide to do here. Thanks. > Andres Freund writes: > >> Another possibility is to say that we've broken this situation > >> irretrievably and we

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

Andres Freund writes: > On 2017-05-28 14:03:26 -0400, Tom Lane wrote: >> I think it would be possible to teach eval_const_expressions that >> it must not discard CASE/COALESCE subexpressions that contain SRFs, >> which would preserve the rule that expression simplification

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

Hi, On 2017-05-28 14:03:26 -0400, Tom Lane wrote: > I think it would be possible to teach eval_const_expressions that > it must not discard CASE/COALESCE subexpressions that contain SRFs, > which would preserve the rule that expression simplification doesn't > change the query semantics. That

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

"Regina Obe" writes: > Is this behavior going to stay or change? > It seems inconsistent from a user perspective that > CASE constant == short-circuit skipping over SRFs that may otherwise > fail > While > CASE not_constant_table_dependent doesn't short-circuit. > I can

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

> "Regina Obe" writes: >> I figured out the culprit was the change in CASE WHEN behavior with >> set returning functions Had a criteria something of the form: >> CASE WHEN some_condition_dependent_on_sometable_that_resolves_to_false >> THEN (regexp_matches(...))[1] ELSE ...

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

"Regina Obe" writes: > I figured out the culprit was the change in CASE WHEN behavior with set > returning functions > Had a criteria something of the form: > CASE WHEN some_condition_dependent_on_sometable_that_resolves_to_false THEN > (regexp_matches(...))[1] ELSE ... END > FROM

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

> Did something change with how exclusion constraints are handled? I'm trying to troubleshoot a regression we are having with PostGIS raster support. > As best I can guess, it's because exclusion constraints that used to work in past versions are failing in PostgreSQL 10 with an error something

[HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change?

Did something change with how exclusion constraints are handled? I'm trying to troubleshoot a regression we are having with PostGIS raster support. As best I can guess, it's because exclusion constraints that used to work in past versions are failing in PostgreSQL 10 with an error something like