pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
what causes new temp schemas to be created
7+ messages / 4 participants
[nested] [flat]

* what causes new temp schemas to be created
@ 2023-07-10 14:18  Ted Toth <txtoth@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Ted Toth @ 2023-07-10 14:18 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

When a temp table is created I see a pg_temp_NNN (for example
pg_temp_3, pg_toast_temp_3) schemas created when/why are additional
temp schemas created( pg_temp_4/pg_toast_temp_4)?

Ted





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: what causes new temp schemas to be created
@ 2023-07-10 14:20  David G. Johnston <david.g.johnston@gmail.com>
  parent: Ted Toth <txtoth@gmail.com>
  0 siblings, 2 replies; 7+ messages in thread

From: David G. Johnston @ 2023-07-10 14:20 UTC (permalink / raw)
  To: Ted Toth <txtoth@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

On Mon, Jul 10, 2023 at 7:18 AM Ted Toth <txtoth@gmail.com> wrote:

> When a temp table is created I see a pg_temp_NNN (for example
> pg_temp_3, pg_toast_temp_3) schemas created when/why are additional
> temp schemas created( pg_temp_4/pg_toast_temp_4)?
>
>
Temporary schemas are isolated to the session they are created in.  Hence,
you get multiple temporary schemas if you have concurrent sessions using
temporary objects.

David J.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: what causes new temp schemas to be created
@ 2023-07-10 14:28  Ron <ronljohnsonjr@gmail.com>
  parent: David G. Johnston <david.g.johnston@gmail.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Ron @ 2023-07-10 14:28 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 7/10/23 09:20, David G. Johnston wrote:
> On Mon, Jul 10, 2023 at 7:18 AM Ted Toth <txtoth@gmail.com> wrote:
>
>     When a temp table is created I see a pg_temp_NNN (for example
>     pg_temp_3, pg_toast_temp_3) schemas created when/why are additional
>     temp schemas created( pg_temp_4/pg_toast_temp_4)?
>
>
> Temporary schemas are isolated to the session they are created in. Hence, 
> you get multiple temporary schemas if you have concurrent sessions using 
> temporary objects.

IOW, temporary schemata are how Pg lets different sessions have temporary 
objects use the same name?


-- 
Born in Arizona, moved to Babylonia.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: what causes new temp schemas to be created
@ 2023-07-10 16:19  Ted Toth <txtoth@gmail.com>
  parent: David G. Johnston <david.g.johnston@gmail.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Ted Toth @ 2023-07-10 16:19 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

I don't see that the schema is removed when the session is over and I
see other sessions come along later and use it. I'm assuming here that
a session is started on connect and ended when the connection is
closed.

On Mon, Jul 10, 2023 at 9:21 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Mon, Jul 10, 2023 at 7:18 AM Ted Toth <txtoth@gmail.com> wrote:
>>
>> When a temp table is created I see a pg_temp_NNN (for example
>> pg_temp_3, pg_toast_temp_3) schemas created when/why are additional
>> temp schemas created( pg_temp_4/pg_toast_temp_4)?
>>
>
> Temporary schemas are isolated to the session they are created in.  Hence, you get multiple temporary schemas if you have concurrent sessions using temporary objects.
>
> David J.





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: what causes new temp schemas to be created
@ 2023-07-10 16:22  David G. Johnston <david.g.johnston@gmail.com>
  parent: Ted Toth <txtoth@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: David G. Johnston @ 2023-07-10 16:22 UTC (permalink / raw)
  To: Ted Toth <txtoth@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

On Mon, Jul 10, 2023 at 9:20 AM Ted Toth <txtoth@gmail.com> wrote:

> I don't see that the schema is removed when the session is over and I
> see other sessions come along later and use it. I'm assuming here that
> a session is started on connect and ended when the connection is
> closed.
>
>
The first time a session needs a temporary schema it is assigned one which
is then immediately cleared out.

David J.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: what causes new temp schemas to be created
@ 2023-07-10 17:03  Ted Toth <txtoth@gmail.com>
  parent: David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Ted Toth @ 2023-07-10 17:03 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

So it's an optimization to reuse existing but currently unused temp
schemas, correct?

On Mon, Jul 10, 2023 at 11:22 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Mon, Jul 10, 2023 at 9:20 AM Ted Toth <txtoth@gmail.com> wrote:
>>
>> I don't see that the schema is removed when the session is over and I
>> see other sessions come along later and use it. I'm assuming here that
>> a session is started on connect and ended when the connection is
>> closed.
>>
>
> The first time a session needs a temporary schema it is assigned one which is then immediately cleared out.
>
> David J.





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: what causes new temp schemas to be created
@ 2023-07-10 17:08  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Ted Toth <txtoth@gmail.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Tom Lane @ 2023-07-10 17:08 UTC (permalink / raw)
  To: Ted Toth <txtoth@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-general@lists.postgresql.org

Ted Toth <txtoth@gmail.com> writes:
> So it's an optimization to reuse existing but currently unused temp
> schemas, correct?

Exactly.  We could just destroy and recreate the old temp schema,
but that seems to lead to useless catalog churn.  Instead we just
destroy any old contents, if there's an existing temp schema in
the slot assigned to the current backend.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2023-07-10 17:08 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-10 14:18 what causes new temp schemas to be created Ted Toth <txtoth@gmail.com>
2023-07-10 14:20 ` David G. Johnston <david.g.johnston@gmail.com>
2023-07-10 14:28   ` Ron <ronljohnsonjr@gmail.com>
2023-07-10 16:19   ` Ted Toth <txtoth@gmail.com>
2023-07-10 16:22     ` David G. Johnston <david.g.johnston@gmail.com>
2023-07-10 17:03       ` Ted Toth <txtoth@gmail.com>
2023-07-10 17:08         ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox