Thread: [HACKERS] Document bug regarding read only transactions
In https://www.postgresql.org/docs/10/static/hot-standby.html#hot-standby-users It is explained that read only transactions (not in standby) allow to update sequences. In normal operation, <quote>read-only</> transactions are allowed to update sequences and to use <command>LISTEN</>,<command>UNLISTEN</>, and <command>NOTIFY</>, so Hot Standby sessions operate under slightly tighter restrictions than ordinary read-only sessions. It is possible that some of these restrictions might be loosenedin a future release. This is plain wrong. BEGIN; BEGIN test=# SET transaction_read_only TO on; SET test=# SELECT nextval('t1_i_seq'); ERROR: cannot execute nextval() in a read-only transaction test=# \q Attached is the patch against master branch. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml index 92e3b45..91cbabd 100644 --- a/doc/src/sgml/high-availability.sgml +++ b/doc/src/sgml/high-availability.sgml @@ -1824,7 +1824,7 @@ if (!triggered) <para> In normal operation, <quote>read-only</> transactions are allowed to - update sequences and to use <command>LISTEN</>, <command>UNLISTEN</>, and + use <command>LISTEN</>, <command>UNLISTEN</>, and <command>NOTIFY</>, so Hot Standby sessions operate under slightlytighter restrictions than ordinary read-only sessions. It is possible that some of these restrictions mightbe loosened in a future release.
On Wed, Jun 14, 2017 at 11:08 AM, Tatsuo Ishii <ishii@sraoss.co.jp> wrote: > <para> > In normal operation, <quote>read-only</> transactions are allowed to > - update sequences and to use <command>LISTEN</>, <command>UNLISTEN</>, and > + use <command>LISTEN</>, <command>UNLISTEN</>, and > <command>NOTIFY</>, so Hot Standby sessions operate under slightly tighter > restrictions than ordinary read-only sessions. It is possible that some > of these restrictions might be loosened in a future release. Your modification does not look completely correct to me either. Temporary sequences can be updated in read-only transactions. I think that the page listing the sequence-related functions should as well mention those restrictions for nextval() and setval(). -- Michael
> Your modification does not look completely correct to me either. > Temporary sequences can be updated in read-only transactions. Not sure. Temporary sequences are meaningless on standby because "create temporary sequence" command cannot be executed on standby anyway (and temporary sequence are not replicated to standby of course). > I think > that the page listing the sequence-related functions should as well > mention those restrictions for nextval() and setval(). If we do so, ANALYZE, VACUUM, LISTEN and NOTIFY man pages should also be updated to mention that they can be executed in read only transaction but not in standby servers. I'm not sure it's worth the trouble. Moreover, that will create maintenance headache once we decide to remove some of the restrictions, because we need to update multiple places in the doc. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
On Tue, Jun 13, 2017 at 10:08 PM, Tatsuo Ishii <ishii@sraoss.co.jp> wrote: > In https://www.postgresql.org/docs/10/static/hot-standby.html#hot-standby-users > > It is explained that read only transactions (not in standby) allow to > update sequences. > > In normal operation, <quote>read-only</> transactions are allowed to > update sequences and to use <command>LISTEN</>, <command>UNLISTEN</>, and > <command>NOTIFY</>, so Hot Standby sessions operate under slightly tighter > restrictions than ordinary read-only sessions. It is possible that some > of these restrictions might be loosened in a future release. > > This is plain wrong. It used to be true. Tom changed it in commit 05d8a561ff85db1545f5768fe8d8dc9d99ad2ef7, back in 2010. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
> It used to be true. Tom changed it in commit > 05d8a561ff85db1545f5768fe8d8dc9d99ad2ef7, back in 2010. Thank you for the info. For a record, I will add it to the commit message. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp