Re: BEGIN WORK READ ONLY;

Hello,

Command Prompt has been teaching alot of classes lately, and one of the
questions that I received recently was:

What is the use case for a READ ONLY transaction?

I haven't been able to come up with a good answer. Anyone got a use case
for this feature? I know the community didn't implement it for giggles.

Sincerely,

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> What is the use case for a READ ONLY transaction?

> I haven't been able to come up with a good answer. Anyone got a use case
> for this feature? I know the community didn't implement it for giggles.

No, we implemented it because it's required by the SQL spec.

I'm not too sure about use-cases either. It certainly seems pretty
useless from a protection standpoint. It might be that some other
DBMSes like to know about READ ONLY so they can optimize transaction
processing, but Postgres doesn't care. (We do the equivalent optimization
by noting at COMMIT time whether you actually made any DB changes,
which we can determine basically for free by seeing if the xact emitted
any WAL records ...)

regards, tom lane

Tom Lane wrote:
> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
>> What is the use case for a READ ONLY transaction?
>
>> I haven't been able to come up with a good answer. Anyone got a use case
>> for this feature? I know the community didn't implement it for giggles.
>
> No, we implemented it because it's required by the SQL spec.
>
> I'm not too sure about use-cases either. It certainly seems pretty
> useless from a protection standpoint. It might be that some other
> DBMSes like to know about READ ONLY so they can optimize transaction
> processing, but Postgres doesn't care. (We do the equivalent optimization
> by noting at COMMIT time whether you actually made any DB changes,
> which we can determine basically for free by seeing if the xact emitted
> any WAL records ...)

Thank you, that's what I needed.

Sincerely,

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

On Sat, Oct 14, 2006 at 11:35:12AM -0700, Joshua D. Drake wrote:
> Hello,
>
> Command Prompt has been teaching alot of classes lately, and one of the
> questions that I received recently was:
>
> What is the use case for a READ ONLY transaction?

It would be handy for things like pgpool and Continuent, which could
reliably distinguish up front the difference between a transaction
that can write and one that can safely be sliced up and dispatched to
read-only databases.

Cheers,
D
> I haven't been able to come up with a good answer. Anyone got a use case
> for this feature? I know the community didn't implement it for giggles.
>
> Sincerely,
>
> Joshua D. Drake
>
>
> --
>
> === The PostgreSQL Company: Command Prompt, Inc. ===
> Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
> Providing the most comprehensive PostgreSQL solutions since 1997
> http://www.commandprompt.com/
>
> Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

On Sat, Oct 14, 2006 at 11:35:12AM -0700, Joshua D. Drake wrote:
> What is the use case for a READ ONLY transaction?

I use read-only transactions as a safety net for interactive sessions
when I want to avoid modifying anything accidentally. Here's an
example:

CREATE ROLE foo LOGIN PASSWORD 'password';
CREATE ROLE foo_ro LOGIN PASSWORD 'password';
ALTER ROLE foo_ro SET default_transaction_read_only TO on;
GRANT foo TO foo_ro;

The foo_ro role now has the same privileges as foo but it can't
modify anything because its transactions are read-only by default.
Using GRANT/REVOKE would be more secure (foo_ro could set
default_transaction_read_only to off and then do anything that foo
could do) but you'd have to remember to set the correct privileges
on every object the read-only role might need to examine; this would
be easy to automate but you'd still have to remember to do it. When
the intent is to prevent "oops" mistakes rather than to provide
real security, using read-only transactions can be convenient.

--
Michael Fuhr

David Fetter <david(at)fetter(dot)org> writes:
> On Sat, Oct 14, 2006 at 11:35:12AM -0700, Joshua D. Drake wrote:
>> What is the use case for a READ ONLY transaction?

> It would be handy for things like pgpool and Continuent, which could
> reliably distinguish up front the difference between a transaction
> that can write and one that can safely be sliced up and dispatched to
> read-only databases.

I don't think that works for PG's interpretation of READ ONLY, though.
IIRC we let a "read only" transaction create and modify temp tables.

regards, tom lane

On Sat, Oct 14, 2006 at 03:42:48PM -0400, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)org> writes:
> > On Sat, Oct 14, 2006 at 11:35:12AM -0700, Joshua D. Drake wrote:
> >> What is the use case for a READ ONLY transaction?
>
> > It would be handy for things like pgpool and Continuent, which could
> > reliably distinguish up front the difference between a transaction
> > that can write and one that can safely be sliced up and dispatched to
> > read-only databases.
>
> I don't think that works for PG's interpretation of READ ONLY, though.
> IIRC we let a "read only" transaction create and modify temp tables.

Am I missing something then?

test=> BEGIN READ ONLY;
BEGIN
test=> CREATE TEMPORARY TABLE foo (x integer);
ERROR: transaction is read-only

--
Michael Fuhr

am Sat, dem 14.10.2006, um 13:35:21 -0600 mailte Michael Fuhr folgendes:
> On Sat, Oct 14, 2006 at 11:35:12AM -0700, Joshua D. Drake wrote:
> > What is the use case for a READ ONLY transaction?
>
> I use read-only transactions as a safety net for interactive sessions
> when I want to avoid modifying anything accidentally. Here's an
> example:
>
> CREATE ROLE foo LOGIN PASSWORD 'password';
> CREATE ROLE foo_ro LOGIN PASSWORD 'password';
> ALTER ROLE foo_ro SET default_transaction_read_only TO on;
> GRANT foo TO foo_ro;

Great.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 10/14/06 14:35, Michael Fuhr wrote:
> On Sat, Oct 14, 2006 at 11:35:12AM -0700, Joshua D. Drake wrote:
>> What is the use case for a READ ONLY transaction?
>
> I use read-only transactions as a safety net for interactive sessions
> when I want to avoid modifying anything accidentally. Here's an
> example:
>
> CREATE ROLE foo LOGIN PASSWORD 'password';
> CREATE ROLE foo_ro LOGIN PASSWORD 'password';
> ALTER ROLE foo_ro SET default_transaction_read_only TO on;
> GRANT foo TO foo_ro;
>
> The foo_ro role now has the same privileges as foo but it can't
> modify anything because its transactions are read-only by default.

Another benefit (with ISOLATION LEVEL SERIALIZABLE) is that you are
guaranteed to have unchanging source data, no matter how many ways
you aggregate, join and WHERE it.

As Tom notes, other RDBMSs do pre-query optimizations. SET TRANS
READ ONLY tells the engine that these statements won't have to take
out concurrent write locks, and can thus take a different, faster
code path.

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFMUbHS9HxQb37XmcRAu1FAJ9jBwddmyS5V0IQgbeZYS8Jv85W/wCgpeAf
j3jNyYxx7RWT74ed5YrfNLA=
=rLJe
-----END PGP SIGNATURE-----

David Fetter wrote:
> It would be handy for things like pgpool and Continuent, which could
> reliably distinguish up front the difference between a transaction
> that can write and one that can safely be sliced up and dispatched to
> read-only databases.

Yes, I think that would be the use case. I wish someone were to
implement that.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Sun, Oct 15, 2006 at 11:39:20AM +0200, Peter Eisentraut wrote:
> David Fetter wrote:
> > It would be handy for things like pgpool and Continuent, which
> > could reliably distinguish up front the difference between a
> > transaction that can write and one that can safely be sliced up
> > and dispatched to read-only databases.
>
> Yes, I think that would be the use case. I wish someone were to
> implement that.

I think you meant "would" rather than "were to." ;)

I've brought it up with the pgpool people :)

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!