Thread: temp tables should not have pg_shdepend entries

Among the consequences of creating a temp table is the fact that we
create pg_shdepend entries for the owner.  This is largely unnecessary
-- we only need it when the table owner is not the authenticated user.
I hereby propose we don't create those entries, since obviously the
tables would go away as soon as the connection goes away anyway, and the
user cannot go away until the connection does.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera wrote:
> Among the consequences of creating a temp table is the fact that we
> create pg_shdepend entries for the owner.  This is largely unnecessary
> -- we only need it when the table owner is not the authenticated user.
> I hereby propose we don't create those entries, since obviously the
> tables would go away as soon as the connection goes away anyway, and the
> user cannot go away until the connection does.

But what if some application is using those dependencies while the
session is running?  What is the advantage of not having the entries?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> Alvaro Herrera wrote:
> > Among the consequences of creating a temp table is the fact that we
> > create pg_shdepend entries for the owner.  This is largely unnecessary
> > -- we only need it when the table owner is not the authenticated user.
> > I hereby propose we don't create those entries, since obviously the
> > tables would go away as soon as the connection goes away anyway, and the
> > user cannot go away until the connection does.
> 
> But what if some application is using those dependencies while the
> session is running?  What is the advantage of not having the entries?

The only use of the dependencies is to disallow dropping the user that
owns the table.  But a connected user cannot be dropped.

... or so I thought :-(  Actually you can delete a connected user, but
things do not go well after you do:

alvherre=> create table test (a int);
ERREUR:  le rôle 32792 a été supprimé simultanément

So now my thinking is that we should disallow dropping a connected user.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:
> So now my thinking is that we should disallow dropping a connected user.

I agree with that, but I think your idea of not making the pg_shdepend
entries is a pointless and possibly dangerous micro-optimization.
How much actual speedup would it provide, anyway?
        regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > So now my thinking is that we should disallow dropping a connected user.
> 
> I agree with that, but I think your idea of not making the pg_shdepend
> entries is a pointless and possibly dangerous micro-optimization.
> How much actual speedup would it provide, anyway?

Speedup?  Not sure -- I'm more worried about catalog bloat for people
who create lots of temp tables.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane wrote:
>> I agree with that, but I think your idea of not making the pg_shdepend
>> entries is a pointless and possibly dangerous micro-optimization.
>> How much actual speedup would it provide, anyway?

> Speedup?  Not sure -- I'm more worried about catalog bloat for people
> who create lots of temp tables.

They're going to have far more bloat in pg_class, pg_attribute, etc.

A more useful thing to do to save catalog bloat would be to stop storing
the 100%-predictable pg_attribute entries for system columns --- that'd
save six or seven rows per table, not just one.
        regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Tom Lane wrote:
> >> I agree with that, but I think your idea of not making the pg_shdepend
> >> entries is a pointless and possibly dangerous micro-optimization.
> >> How much actual speedup would it provide, anyway?
> 
> > Speedup?  Not sure -- I'm more worried about catalog bloat for people
> > who create lots of temp tables.
> 
> They're going to have far more bloat in pg_class, pg_attribute, etc.

But these are not shared catalogs.

> A more useful thing to do to save catalog bloat would be to stop storing
> the 100%-predictable pg_attribute entries for system columns --- that'd
> save six or seven rows per table, not just one.

Agreed.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.