1

Postgres server is in hot standbuy mode. Asynchronou streaming binary replication is used.

Command like 

INSERT INTO logfile (logdate) values (current_date)

Causes error

cannot execute INSERT in a read-only transaction.

Maybe it should be changed to 

INSERT INTO logfile (logdate) 
SELECT current_date
WHERE   ???

What where condition should used ? It should work starting at Postgres 9.0 If direct where clause is not possible, maybe some plpgsql function can used in where.

Maybe 

show transaction_read_only

result should captured or some function can used.

Alternately application can determine if database is read-only in startup. Should show transaction_read_only result used for this.

Andrus
  • 26,339
  • 60
  • 204
  • 378

1 Answers1

1

Running INSERT on a standby server is not possible in pure (non-procedural) SQL because when the server is in standby mode, all the data-modification queries are rejected in planning phase, before it's executed.

It's possible with conditionals in PL/PgSQL.

DO $code$
BEGIN
  IF NOT pg_is_in_recovery() THEN
    INSERT INTO logfile (logdate) VALUES (current_date);
  END IF;
END;
$code$;

However, it's probably not recommended - it's usually better to test pg_is_in_recovery() once (in application code) and then act accordingly.

I'm using pg_is_in_recovery() system function instead of transaction_read_only GUC because it's not exactly the same thing. But if you prefer that, please use:

SELECT current_setting('transaction_read_only')::bool

More info: DO command, conditionals in PL/PgSQL, system information functions.

filiprem
  • 6,721
  • 1
  • 29
  • 42
  • 1
    Shold I use `INSERT INTO logfile (logdate) select current_date where not is_in_recovery() and not current_setting('transaction_read_only')::bool` or is it OK to use only one condition? – Andrus Apr 06 '20 at 14:38