Thread: read only transaction, temporary tables
I recently tried to do something like the following BEGIN READONLY; ... CREATE TEMPORARY TABLE ABC AS SELECT ... ... COMMIT; and it failed because CREATE is not allowed within a read-only transaction. The select is something long and complicated (pieced together with php) and I'm just trying to be careful. What other alternatives are there for accomplishing this? Preferably simple ones... Thanks, Carl Brune
On Aug 8, 2006, at 1:25 PM, Carl R. Brune wrote: > I recently tried to do something like the following > > BEGIN READONLY; > ... > CREATE TEMPORARY TABLE ABC AS SELECT ... > ... > COMMIT; > > and it failed because CREATE is not allowed within a read-only > transaction. The select is something long and complicated (pieced > together with php) and I'm just trying to be careful. What other > alternatives are there for accomplishing this? Preferably > simple ones... How about: BEGIN; ... CREATE TEMPORARY TABLE ABC AS SELECT ... ... ROLLBACK; John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
I should have added that I want to make further use of the temporary table after the COMMIT -- the rollback approach you propose makes it go away. Carl Brune On Tue, 8 Aug 2006, John DeSoi wrote: > > On Aug 8, 2006, at 1:25 PM, Carl R. Brune wrote: > >> I recently tried to do something like the following >> >> BEGIN READONLY; >> ... >> CREATE TEMPORARY TABLE ABC AS SELECT ... >> ... >> COMMIT; >> >> and it failed because CREATE is not allowed within a read-only >> transaction. The select is something long and complicated (pieced >> together with php) and I'm just trying to be careful. What other >> alternatives are there for accomplishing this? Preferably >> simple ones... > > > How about: > > BEGIN; > .... > CREATE TEMPORARY TABLE ABC AS SELECT ... > .... > ROLLBACK; > > > > John DeSoi, Ph.D. > http://pgedit.com/ > Power Tools for PostgreSQL >
Carl R. Brune wrote: > I should have added that I want to make further use of the temporary > table after the COMMIT -- the rollback approach you propose makes it > go away. In which case the transaction isn't READONLY. You have two options: CREATE TEMPORARY TABLE ... AS SELECT ... BEGIN READONLY; ... COMMIT; Or, create a user with only-read permissions on your database and connect as that user. -- Richard Huxton Archonet Ltd
Richard Huxton <dev@archonet.com> writes:
> Carl R. Brune wrote:
>> I should have added that I want to make further use of the temporary
>> table after the COMMIT -- the rollback approach you propose makes it
>> go away.
> In which case the transaction isn't READONLY.
It does seem a bit inconsistent that we allow you to write into a temp
table during a "READONLY" transaction, but not to create/drop one.
I'm not excited about changing it though, as the tests to see if
the command is allowed would become vastly more complex.
regards, tom lane
Thanks for the information clarifications. Carl B. On Tue, 8 Aug 2006, Carl R. Brune wrote: > I should have added that I want to make further use of the temporary > table after the COMMIT -- the rollback approach you propose makes it > go away. > > Carl Brune > > On Tue, 8 Aug 2006, John DeSoi wrote: > >> >> On Aug 8, 2006, at 1:25 PM, Carl R. Brune wrote: >> >>> I recently tried to do something like the following >>> >>> BEGIN READONLY; >>> ... >>> CREATE TEMPORARY TABLE ABC AS SELECT ... >>> ... >>> COMMIT; >>> >>> and it failed because CREATE is not allowed within a read-only >>> transaction. The select is something long and complicated (pieced >>> together with php) and I'm just trying to be careful. What other >>> alternatives are there for accomplishing this? Preferably >>> simple ones... >> >> >> How about: >> >> BEGIN; >> .... >> CREATE TEMPORARY TABLE ABC AS SELECT ... >> .... >> ROLLBACK; >> >> >> >> John DeSoi, Ph.D. >> http://pgedit.com/ >> Power Tools for PostgreSQL >> > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
Tom Lane wrote: > Richard Huxton <dev@archonet.com> writes: > > Carl R. Brune wrote: > >> I should have added that I want to make further use of the > >> temporary table after the COMMIT -- the rollback approach you > >> propose makes it go away. > > > > In which case the transaction isn't READONLY. > > It does seem a bit inconsistent that we allow you to write into a > temp table during a "READONLY" transaction, but not to create/drop > one. I'm not excited about changing it though, as the tests to see if > the command is allowed would become vastly more complex. Temporary tables in the SQL standard are permanent objects, which is why creating or dropping them is a durable operation and not allowed in read-only transactions. It would probably make sense to allow creating or dropping PostgreSQL-style temporary tables, though. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On Sat, Aug 12, 2006 at 08:57:49AM +0200, Peter Eisentraut wrote: > > It does seem a bit inconsistent that we allow you to write into a > > temp table during a "READONLY" transaction, but not to create/drop > > one. I'm not excited about changing it though, as the tests to see if > > the command is allowed would become vastly more complex. > > Temporary tables in the SQL standard are permanent objects, which is why > creating or dropping them is a durable operation and not allowed in > read-only transactions. It would probably make sense to allow creating > or dropping PostgreSQL-style temporary tables, though. Temporary tables still get an entry in pg_class, so for truly readonly systems they wouldn't work. If you can fix that though it might be doable. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
Martijn van Oosterhout <kleptog@svana.org> writes:
> Temporary tables still get an entry in pg_class, so for truly readonly
> systems they wouldn't work.
The "READONLY" transaction status is a security mechanism, not a
performance-enhancing mechanism. It makes no pretense of preventing
all disk writes. I think a reasonable description of the feature
is that it's supposed to prevent you from making any database changes
that are visible to other transactions.
Having said that, though, the point about pg_class is a good one:
if you could create a temp table then you'd be making a catalog
change that would be visible to other transactions if they cared
to look. So at some level or other I'd say it violates the concept
of READONLY.
(And having said *that*, I'd be all for avoiding making any permanent
catalog entries for temp tables, if I could think of a reasonably
noninvasive way to do it...)
regards, tom lane