Re: BUG #15344: pg_proc.proisagg was removed incompatibly in PostgreSQL 11

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Lukas Eder <lukas(dot)eder(at)gmail(dot)com>
Cc: Andres Freund <andres(at)anarazel(dot)de>, pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #15344: pg_proc.proisagg was removed incompatibly in PostgreSQL 11
Date: 2018-08-21 18:55:37
Message-ID: CAFj8pRD8y4CLmMq8zEcNsx8YW27s55yvSyFAZ-kkoEch1NrcKg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Dne út 21. 8. 2018 17:04 uživatel Lukas Eder <lukas(dot)eder(at)gmail(dot)com> napsal:

>
>
> On Tue, Aug 21, 2018 at 4:45 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
>
>> On 2018-08-21 16:39:18 +0200, Lukas Eder wrote:
>> > On Tue, Aug 21, 2018 at 4:28 PM Andres Freund <andres(at)anarazel(dot)de>
>> wrote:
>> >
>> > > Hi,
>> > >
>> > > On 2018-08-21 14:23:45 +0000, PG Bug reporting form wrote:
>> > > > When comparing the current version (10) [1] and the developer
>> version
>> > > (11)
>> > > > [2] of the pg_proc documentation, then it can be seen that the
>> > > > pg_proc.proisagg column was removed backwards incompatibly. The
>> > > > documentation states for [1]:
>> > >
>> > > Please note that the pg_catalog.* tables (and views) are *NOT*
>> intended
>> > > to backwards compatible between major versions. We change them in ways
>> > > backward incompatible all the time.
>> > >
>> >
>> > The pg_catalog tables do seem to be the only way to reverse engineer
>> some
>> > more sophisticated things in the database.
>>
>> Yes, there's some things that aren't represented in a standardized way
>> in information_schema.*. If that's the case it's good for tool vendors
>> to pipe up and ask for something intended to be externally visible.
>>
>
> I have mixed feelings about this. The information_schema is part of the
> SQL standard. I reckon that this particular information would belong in
> INFORMATION_SCHEMA.ROUTINES.ROUTINE_TYPE. The SQL:2011 standard (I don't
> have newer versions of the SQL/Schemata document) mentions these possible
> values for that column:
>
> 'PROCEDURE', 'FUNCTION', 'INSTANCE METHOD', 'STATIC METHOD', 'CONSTRUCTOR
> METHOD'
>
> So, no aggregate functions or window functions as in pg_proc.prokind. When
> extending the standard, the functionality becomes a bit less standard, and
> risks breaking as well in the future, e.g. when the standard *does* add
> aggregate functions as a possible value, but not using the name PostgreSQL
> chooses now.
>
> Which is why the vendor specific pg_catalog is so useful. Any value is
> acceptable in those tables as you do not have to coordinate their layout
> with the standard committee.
>
> In Oracle, the dictionary views aren't following the information_schema
> standard, but are vastly richer than what the standard supports - just like
> pg_catalog. They are definitely kept backwards compatible for the same
> reason I've mentioned: Tool support.
>
> A database product thrives on the quality of the tools supporting it.
> Making it hard for the tool vendors might mean there's less support for
> advanced features. From a market adoption perspective, in the long run,
> there is no option but to be more backwards compatible.
>
>
>> > I imagine that this is being
>> > done by tool vendors like myself (jOOQ) quite a bit. And there are tons
>> of
>> > Stack Overflow answers that show how to query the pg_catalog tables,
>> all of
>> > them risking to be outdated between major versions.
>>
>> People doing bad things on stackoverflow isn't very convincing.... ;)
>>
>
> Your perception of "bad" is biased of course, just like mine. People use
> what's available, this has always been the case with any product. If there
> were "internal" and "public" catalog tables / views, then it would be more
> understandable that using the (internal) pg_catalog is being dismissed, but
> given that there is no option...
>
> Here, have a quick google search for questions on Stack Overflow involving
> pg_proc.proisagg:
> https://www.google.ch/search?q=site%3Astackoverflow.com+proisagg
>
> It returns 107 results on Stack Overflow alone. Some examples:
>
> - https://stackoverflow.com/a/50093399/521799
> - https://stackoverflow.com/a/18200250/521799
> - https://stackoverflow.com/a/20549944/521799
> - https://stackoverflow.com/a/24774064/521799
> - https://stackoverflow.com/a/24034609/521799
> - https://dba.stackexchange.com/a/46996/2512
> - https://stackoverflow.com/a/48709779/521799
> - https://stackoverflow.com/a/48709779/521799
> - https://stackoverflow.com/a/308500/521799
> - https://stackoverflow.com/a/20549944/521799
>
> All of these answers are now outdated with PostgreSQL 11. Not only are
> they outdated, but if the answers were fixed for PostgreSQL 11, the fixed
> version wouldn't work on older PostgreSQL versions, because pg_proc.prokind
> didn't exist earlier.
>
> I find that a relatively high price to pay in this case for the relatively
> easy solution to keep a pg_proc.proisagg computed column around for
> backwards compatibility.
>
>
>> > I understand that backwards compatibility is quite a bit of extra work,
>> but
>> > in cases like this particular one, the price to pay seems relatively
>> low.
>> > Perhaps a new strategy could be to break things only if there is really
>> no
>> > other solution?
>>
>> I mean we don't break things willy-nilly already. And there's plenty
>> cases where we kept things around for backward compatibility. But
>> usually the problem is that that means you have to keep the shim around
>> forever. Even if better solutions have been around for many years,
>> there'll be complaints about removing them. So IMO the backward compat
>> price for pg_catalog.* has to be low enough that there's essentially no
>> point in keeping it around forever, or so painful that it'd cost a lot
>> of people a lot.
>>
>
> I agree that at some point, the price to pay is too high. But in this
> case, a boolean value has been replaced by an enumeration, so it would be
> really easy to maintain the boolean value as a computed column, right?
>

now, the logic of pgproc is different, and this column has not sense.
Probably nobody is happy, but I dont see any benefit holds garbage in
system catalogue. Pgproc is table, not view, so computed column is not
possible feature, and if can be, then it has not any consistency there.

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message jimmy 2018-08-22 02:54:27 Re:Re: Bug: ERROR: invalid cache ID: 42 CONTEXT: parallel worker
Previous Message Jeff Janes 2018-08-21 18:41:28 Re: Not found indexed word