Should we document IS [NOT] OF?

"David G. Johnston" <david.g.johnston@gmail.com> writes:

Over in news [1] Josh Drake and Eric Ridge discovered the undocumented
feature "IS [NOT] OF"; introduced seemingly as an "oh-by-the-way" in 2002
via commit eb121ba2cfe [2].

Is there any reason not to document this back to 9.5,

As far as I can tell from reading the SQL spec, this has nothing much in
common with the SQL feature of that name except for the syntax. The SQL
feature seems to be a *run time* test on subtype inclusion, not something
that can be answered in parse analysis. Even if I'm getting that wrong,
it's clear that the spec intends IS OF to return true for subtype
relationships, not only exact type equality which is the only thing
transformAExprOf considers.

So my vote would be to rip it out, not document it. Somebody can try
again in future, perhaps. But if we document it we're just locking
ourselves into a SQL incompatibility.

regards, tom lane

I wrote:

So my vote would be to rip it out, not document it. Somebody can try
again in future, perhaps. But if we document it we're just locking
ourselves into a SQL incompatibility.

Apparently, somebody already had that thought. See func.sgml
lines 765-782, which were commented out by 8272fc3f7.

regards, tom lane

On Wednesday, November 18, 2020, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I wrote:

So my vote would be to rip it out, not document it. Somebody can try
again in future, perhaps. But if we document it we're just locking
ourselves into a SQL incompatibility.

Apparently, somebody already had that thought. See func.sgml
lines 765-782, which were commented out by 8272fc3f7.

Is there a feature code? I skimmed the standard and non-standard tables in
our appendix and couldn’t find this in either.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

Is there a feature code? I skimmed the standard and non-standard tables in
our appendix and couldn’t find this in either.

a19d9d3c4 seems to have thought it was S151.

regards, tom lane

On 11/19/20 2:03 AM, Tom Lane wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

Is there a feature code? I skimmed the standard and non-standard tables in
our appendix and couldn’t find this in either.

a19d9d3c4 seems to have thought it was S151.

Here is a link to previous list discussions:

/messages/by-id/45DA44F3.3010401@joeconway.com

HTH,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Joe Conway <mail@joeconway.com> writes:

Here is a link to previous list discussions:
/messages/by-id/45DA44F3.3010401@joeconway.com

Ah, thanks, I was intending to go do some searching for that.

So at this point this has been re-debated at least three times.
I think it's time to put it out of its misery. You can get
equivalent behavior using something like
pg_typeof(foo) in ('int'::regtype, 'float8'::regtype)
so we've got the "another way to do it" covered. And I'm
still convinced that the existing implementation is not
anywhere near per-spec. The issue about NULL is somewhat
minor perhaps, but the real problem is that I think the
spec intends "foo is of (sometype)" to return true if foo
is of any subtype of sometype. We don't have subtypes in
the way SQL intends, but we do have inheritance children
which are more or less the same idea. The closest you can
get right now is to do something like

select * from parent_table t where t.tableoid = 'child1'::regclass

but this will fail to match grandchilden of child1.
I think a minimum expectation is that you could do
something like "where (t.*) is of (child1)", but
our existing code is nowhere near making that work.

Let's just rip it out and be done. If anyone is ever
motivated to make it work per spec, they can resurrect
whatever seems useful from the git history.

regards, tom lane

On 11/19/20 11:06 AM, Tom Lane wrote:

Let's just rip it out and be done. If anyone is ever
motivated to make it work per spec, they can resurrect
whatever seems useful from the git history.

+1

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

On Thu, Nov 19, 2020 at 11:15:33AM -0500, Joe Conway wrote:

On 11/19/20 11:06 AM, Tom Lane wrote:

Let's just rip it out and be done. If anyone is ever
motivated to make it work per spec, they can resurrect
whatever seems useful from the git history.

+1

+1

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee

Bruce Momjian <bruce@momjian.us> writes:

On Thu, Nov 19, 2020 at 11:15:33AM -0500, Joe Conway wrote:

On 11/19/20 11:06 AM, Tom Lane wrote:

Let's just rip it out and be done. If anyone is ever
motivated to make it work per spec, they can resurrect
whatever seems useful from the git history.

+1

+1

Here's a proposed patch for that. I was amused to discover that we have
a couple of regression test cases making use of IS OF. However, I think
using pg_typeof() is actually better for those tests anyway, since
printing the regtype result is clearer, and easier to debug if the test
ever goes wrong.

regards, tom lane

On 11/19/20 12:08 PM, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

On Thu, Nov 19, 2020 at 11:15:33AM -0500, Joe Conway wrote:

On 11/19/20 11:06 AM, Tom Lane wrote:

Let's just rip it out and be done. If anyone is ever
motivated to make it work per spec, they can resurrect
whatever seems useful from the git history.

+1

+1

Here's a proposed patch for that. I was amused to discover that we have
a couple of regression test cases making use of IS OF.

I didn't check but those might be my fault ;-)

However, I think using pg_typeof() is actually better for those tests anyway, since
printing the regtype result is clearer, and easier to debug if the test
ever goes wrong.

Looks good to me.

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Joe Conway <mail@joeconway.com> writes:

On 11/19/20 12:08 PM, Tom Lane wrote:

Here's a proposed patch for that. I was amused to discover that we have
a couple of regression test cases making use of IS OF.

I didn't check but those might be my fault ;-)

I suspect at least one of them is mine ;-). But I didn't check.

Looks good to me.

Thanks for looking!

regards, tom lane

After digging a bit more I noticed that we'd discussed removing
IS OF in the 2007 thread, but forebore because there wasn't an easy
replacement. pg_typeof() was added a year later (b8fab2411), so we
could have done this at any point since then.

Pushed.

regards, tom lane

Howdy,

Well I certainly wasn't trying to make work out of that blog but I am glad
to see it was productive.

JD

On Thu, Nov 19, 2020 at 2:43 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

On Thu, Nov 19, 2020 at 6:43 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

After digging a bit more I noticed that we'd discussed removing
IS OF in the 2007 thread, but forebore because there wasn't an easy
replacement. pg_typeof() was added a year later (b8fab2411), so we
could have done this at any point since then.

Pushed.

Documenting or improving IS OF was a TODO, so I've removed that entry.

--
John Naylor
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company