Re: Information_schema fixes for sequences and temporary tables

More to come, but these two are probably worth backpatching. Sequences
were not being shown due to the use of lowercase 's' instead of 'S', and
the views were not checking for table visibility with regards to
temporary tables and sequences.

--
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation
PGP Key: 0x14964AC8 200608181942
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

Greg Sabino Mullane <greg(at)turnstep(dot)com> writes:
> More to come, but these two are probably worth backpatching.

We can't really backpatch changes to information_schema, since we can't
force initdb in back branches.

I'd be interested to see you cite chapter and verse in the SQL spec
where it says that information_schema should hide temp tables of
other backends. That change seems pretty dubious to me. (More
likely, we should be adding tests on whether the caller has USAGE
privilege on the table's containing schema.)

As for all that ESCAPE junk, consider using regexps instead; they
play nicer with underscores in patterns.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> I'd be interested to see you cite chapter and verse in the SQL spec
> where it says that information_schema should hide temp tables of
> other backends. That change seems pretty dubious to me. (More
> likely, we should be adding tests on whether the caller has USAGE
> privilege on the table's containing schema.)

Fair point: what brought this about was someone wondering why a
SELECT 1 FROM information_schema.tables WHERE table_name = 'mytemptable'
returned true but a subsequent DROP TABLE mytemptable; failed.
Another subtle difference between \d and i_s.tables I suppose.
It all depends on how one interprets "accessible" here:

<quote>
Function: [of information_schema.tables]

Identify the tables defined in this catalog that are accessible
to a given user or role.
</quote>

While I might extend "accessible" to schemas outside of a user's search path,
I'm not sure that should include the pg_temp_ ones. Seems confusing for the
user to see other temp tables, even if the schema is returned, as one does not
specify a schema when creating temp tables. +1 on the USAGE idea.

> As for all that ESCAPE junk, consider using regexps instead; they
> play nicer with underscores in patterns.

Hmph. I was just copying the surrounding code, in the theory that it
increases the chance of my patches being accepted. :)

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation
PGP Key: 0x14964AC8 200608182237
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFE5n7EvJuQZxSWSsgRAr9UAKDSXYExsVwsYazS1ygaOCmsudGVpwCeKPHj
1g/fpDkpDdfOr9eGQzr3M9U=
=9QRx
-----END PGP SIGNATURE-----

Patch applied. Thanks.

I did not batckpatch because someone would need to re-initdb to see the
changes, and we haven't gotten any complaints about the bug.

---------------------------------------------------------------------------

Greg Sabino Mullane wrote:
-- Start of PGP signed section.
> More to come, but these two are probably worth backpatching. Sequences
> were not being shown due to the use of lowercase 's' instead of 'S', and
> the views were not checking for table visibility with regards to
> temporary tables and sequences.
>
> --
> Greg Sabino Mullane greg(at)turnstep(dot)com
> End Point Corporation
> PGP Key: 0x14964AC8 200608181942
> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
>

[ Attachment, skipping... ]
-- End of PGP section, PGP failed!

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +