sql: show correct persistence information in pg_class table

[arulajmani]

Currently, regardless of persistence status (TEMP/UNLOGGED) pg_catalog.pg_class's relpersistence column returns p. Instead, this should return 't' for temp tables and 'u' for unlogged tables.

[blathers-crl]

Hi @arulajmani, please add a C-ategory label to your issue. Check out the label system docs.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[rafiss]

We don't actually support the functionality for unlogged tables so I think for now we will keep returning u for those. But we should handle temporary tables correctly

If an unlogged table is created now, this notice is sent

NOTICE: UNLOGGED TABLE will behave as a regular table in CockroachDB

[rafiss]

Watch out for this (and see if it can be fixed)

So 20.1 CREATE TABLE AS with temp tables does not seem to set the isTemporary bool on the table descriptor even if the temp option is specified on creation (https://github.com/cockroachdb/cockroach/blob/release-20.1/pkg/sql/parser/sql.y#L4423)

[arulajmani]

I think for now we will keep returning u for those.

We don't return u currently -- we return p, and we should switch to u.

Watch out for this (and see if it can be fixed)

There's a separate issue tracking this #56733 which I'm gonna be fixing. It is orthogonal to this issue though, as the comment above wrongly identifies the table descriptor state as the source of the problem.

[rafiss]

Oops my first comment had a mistake. I meant that it doesn't seem like we have an easy way of identifying UNLOGGED tables nor do we implement any UNLOGGED functionality right now, so we return p right now, and perhaps should keep returning p after this change.

[arulajmani]

Yeah, I dug into this a bit more and looks like we don't store UNLOGGED beyond the parser at the moment. I'm not sure if there's value in changing this so that we can return u during introspection, but unless we do decide to persist this information on disk I think we'll have to continue returning p for unlogged tables.