Prohibit non-IMMUTABLE functions in multi-shard SELECT queries

[citus-github-bot]

Issue by jasonmp85
Thursday Jan 22, 2015 at 23:09 GMT
Originally opened as citusdata/pg_shard#60

---

Though all (non-side-effecting) functions are safe to execute in a single-shard SELECT query, the same is not true for multi-shard SELECTs. In particular, a function such as random() or now() would return a different value for each shard and so the query results would not represent a consistent view of the world.

This may overlap somewhat with 47, but that issue is more concerned with single-shard ramifications. We've noticed this bug affects multi-shard queries as well, so they'll need similar treatment.

[citus-github-bot]

Comment by jasonmp85
Thursday Sep 03, 2015 at 05:11 GMT

---

Closes #231 (manually updated issue number).

[saicitus]

Im not sure whether this is right issue to comment on. Seems most relevant: (Came across in 2 customer engagements.

1. Typecasting in multi-shard delete throws the following error:

SELECT master_modify_multiple_shards('DELETE FROM test_timestamp WHERE updated_at=''2016-10-27T14:00:00+00:00''::timestamptz');
ERROR:  STABLE functions used in UPDATE queries cannot be called with column references

1. Upsert statement with now() in where clause throws error:

INSERT INTO test_timestamp VALUES(1,now()) ON CONFLICT(id) DO UPDATE SET updated_at=now();
ERROR:  functions used in the DO UPDATE SET clause of INSERTs on distributed tables must be marked IMMUTABLE

Simple workaround is:

DO LANGUAGE plpgsql
$$ DECLARE
update_temp timestamp;
BEGIN
update_temp=now();
INSERT INTO test_timestamp VALUES(1,now()) ON CONFLICT(id) DO UPDATE SET updated_at=update_temp;
END;
$$;