From: Tom Lane <tgl@sss.pgh.pa.us>
To: Regina Obe <lr@pcorp.us>
Cc: 'Mark Dilger' <hornschnorter@gmail.com>
Cc: 'Andres Freund' <andres@anarazel.de>
Cc: 'PostgreSQL-development' <pgsql-hackers@postgresql.org>
Subject: Re: PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity
Date: Wed, 07 Jun 2017 22:01:57 -0400
Message-ID: <31985.1496887317@sss.pgh.pa.us> (raw)
In-Reply-To: <000201d2dfd2$07fad150$17f073f0$@pcorp.us>
References: <000001d2d5de$d8d66170$8a832450$@pcorp.us>
<29994.1495809534@sss.pgh.pa.us>
<000c01d2d654$518f2be0$f4ad83a0$@pcorp.us>
<21809.1495994606@sss.pgh.pa.us>
<20170603024503.yv6gxvkyx6mtc63m@alap3.anarazel.de>
<7181.1496458380@sss.pgh.pa.us>
<20170603031127.fqg7zjhfgbcbcank@alap3.anarazel.de>
<4125B63A-886A-442D-9932-34F0D4F76D41@gmail.com>
<20170604193521.ha6d5kpkdswmissm@alap3.anarazel.de>
<63850C96-9570-4969-8074-7033DA4C334F@gmail.com>
<20170604211942.aiavcbagsho5owcz@alap3.anarazel.de>
<FB5BA655-95A0-4EC5-900D-2582D3E98A74@gmail.com>
<13450.1496861842@sss.pgh.pa.us>
<000201d2dfd2$07fad150$17f073f0$@pcorp.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
"Regina Obe" <lr@pcorp.us> 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 convinced the issue is
> all that rare.
> Make sure to point out the breaking change in the release notes though and
> syntax to remedy it.
As far as that goes, the best fix I could think of after a few minutes is
to integrate your conditional logic into a custom set-returning function.
For example,
select x, case when y > 0 then generate_series(1, z) else 5 end from tt;
could become
create function mysrf(cond bool, start int, fin int, els int)
returns setof int as $$
begin
if cond then
return query select generate_series(start, fin);
else
return query select els;
end if;
end$$ language plpgsql;
select x, mysrf(y > 0, 1, z, 5) from tt;
(adjust the amount of parameterization to taste, of course)
Now, the fact that a fairly mechanical conversion like this is possible
suggests that we *could* solve the problem if we had to, at least for
simple cases like this one. But it'd be a lot of work, not least because
we'd presumably not want core-defined syntax to depend on an extension
like plpgsql --- and I don't see a way to do this with straight SQL
functions. So my feeling is that we should not expend that effort.
If it turns out that a lot more people are affected than I currently
think will be the case, maybe we'll have to revisit that choice.
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.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, lr@pcorp.us, hornschnorter@gmail.com, andres@anarazel.de
Subject: Re: PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity
In-Reply-To: <31985.1496887317@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox