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
Import Notes
Reply to msg id not found: 609049536d27954f3f071cd64fd361aa6a18dfde@postgresql.orgReference msg id not found: 609049536d27954f3f071cd64fd361aa6a18dfde@postgresql.org
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:
Show quoted text
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:
Show quoted text
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/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
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