public inbox for [email protected]
help / color / mirror / Atom feedFrom: Japin Li <[email protected]>
To: Dean Rasheed <[email protected]>
Cc: Kirk Wolak <[email protected]>
Cc: Pavel Stehule <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Global temporary tables
Date: Wed, 01 Jul 2026 14:24:46 +0800
Message-ID: <SY7PR01MB1092104CDB43530A408012EAFB6F62@SY7PR01MB10921.ausprd01.prod.outlook.com> (raw)
In-Reply-To: <CAEZATCUqP6iu0QcuZ+718iz630hsfQLdqZdhW=B3+vz6iOuq9Q@mail.gmail.com>
References: <CAEZATCU_goy9GiN75Rhqwg8=AEHrujYdf+cF9ORMGg98Tc44YA@mail.gmail.com>
<[email protected]>
<CAFj8pRDbGis5SPwaoGjyeVd9K0ynabEXfe-C3RzOF+d46WLOyA@mail.gmail.com>
<CAEZATCU0fg2JTYCJoY1m3FGmUMoDUHzDsQ9JExujWRrHz68sUg@mail.gmail.com>
<CAEZATCUdg2tGj1OWbSWMCubrQdXpGirQkdXNgQBgGf-UQG2hGA@mail.gmail.com>
<CACLU5mTSDkxr7R9MpXqO8Fi-TL-V+JO_f4vQFMU92OhhDYTtww@mail.gmail.com>
<CAEZATCUqP6iu0QcuZ+718iz630hsfQLdqZdhW=B3+vz6iOuq9Q@mail.gmail.com>
Hi, Dean
Thanks for working on this.
On Fri, 26 Jun 2026 at 20:37, Dean Rasheed <[email protected]> wrote:
> On Fri, 26 Jun 2026 at 04:52, Kirk Wolak <[email protected]> wrote:
>>
>> + 1 for the idea. I will try to review this over the weekend. We could use this.
>
> Thanks. Any reviews will be very helpful.
>
> In the meantime, comparing the 2 patchsets, I realised that there was
> a bug in mine -- ON COMMIT DELETE ROWS wasn't working properly in all
> cases.
>
> More specifically, it was working for a global temporary table created
> in the current session, but disconnecting and then reconnecting caused
> it to stop working, because I hadn't realised that the ON COMMIT
> action of a temporary table is not saved to the database.
>
> Not saving the ON COMMIT action to the database kind-of made sense for
> old-style temporary tables, since they disappear at the end of the
> session. However, even then, it can be useful to have it in the
> database so that psql's \d meta-command can display it, and of course,
> for global temporary tables, saving it to the database is essential so
> that new sessions can pick it up.
>
> So here's a new patchset, where 0001 is new -- it saves a temporary
> table's ON COMMIT action to pg_class, and updates psql's \d to display
> it. I think we could commit that independently of the global temporary
> tables feature, since it seems somewhat useful by itself.
>
> (I chose to do it as a new column "reloncommit" in pg_class, rather
> than as a reloption, because a reloption didn't seem quite right for
> this, but that's a matter of opinion.)
>
While testing the v5 patch, I encountered a lock wait.
2026-07-01 14:18:43.603 CST [1486593] LOG: process 1486593 still waiting for AccessExclusiveLock on relation 16384 of database 5 after 1000.106 ms
2026-07-01 14:18:43.603 CST [1486593] DETAIL: Process holding the lock: 1486484. Wait queue: 1486593.
2026-07-01 14:18:43.603 CST [1486593] CONTEXT: waiting for AccessExclusiveLock on relation 16384 of database 5
2026-07-01 14:18:43.603 CST [1486593] STATEMENT: SELECT * FROM gtt_delete;
To reproduce:
Session 1:
CREATE GLOBAL TEMPORARY TABLE gtt_delete (id int) ON COMMIT DELETE ROWS;
BEGIN;
INSERT INTO gtt_delete VALUES (1);
Session 2:
SELECT * FROM gtt_delete; ---> blocked
Is this expected?
> Regards,
> Dean
--
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.
view thread (492+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Global temporary tables
In-Reply-To: <SY7PR01MB1092104CDB43530A408012EAFB6F62@SY7PR01MB10921.ausprd01.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox