1

How can I run a function a on master only?

I don't want to connect directly to my master server, because the master server might change.

Or can I change my function to work on this setup? Because at the moment using the below function, will say that the server is read-only?

ERROR:  cannot execute SELECT in a read-only transaction

CREATE FUNCTION.....
 WITH sel AS (
   SELECT id
   FROM   univ.domains
   WHERE  name = domain_name
  )
, ins AS (
  INSERT INTO univ.domains (name)
  SELECT domain_name
  WHERE  NOT EXISTS (SELECT 1 FROM sel)
  RETURNING id
  )
SELECT id
FROM   sel NATURAL FULL OUTER JOIN ins 
INTO   domain_id;
 ...
LANGUAGE plpgsql

Also tried adding SET TRANSACTION READ WRITE; after begin and got:

cannot set transaction read-write mode inside a read-only transaction
  • So, I don't know where your original question is, but: you can write only on a master. There is no other way. However, there is a way to decouple the application from the (possibly changing) cluster role (I mean master/slave). We use an additional, separate IP address for both the master and slave (dubbed a service IP). When failing over, we move over the IP, too - which, in turn, has a host name defined in DNS. This was, for example, order.internal.domain always points to the master, while order-slave.internal.domain always points to the slave. – dezso May 27 '15 at 12:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.