read only transaction, temporary tables

[Carl R. Brune]

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

[John DeSoi]

Post by Carl R. Brune
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

[Carl R. Brune]

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

Post by John DeSoi

Post by Carl R. Brune
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...

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

[Richard Huxton]

Post by Carl R. Brune
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.

[Tom Lane]

Post by Richard Huxton

Post by Carl R. Brune
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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Peter Eisentraut]

Post by Tom Lane

Post by Richard Huxton

Post by Carl R. Brune
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.

[Martijn van Oosterhout]

Post by Peter Eisentraut

Post by Tom Lane
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,

[Tom Lane]

Post by Martijn van Oosterhout
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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Carl R. Brune]

Thanks for the information clarifications.

Carl B.

Post by Carl R. Brune
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

Post by John DeSoi

Post by Carl R. Brune
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...

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend