Thread: 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@commandprompt.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@commandprompt.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@fetter.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@fetter.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@fetter.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@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!