Thread: Re: [PATCHES] Information_schema fixes for sequences and temporary tables

Greg Sabino Mullane <greg@turnstep.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@turnstep.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-----