Hacker News Reader

Comment by bombcar | Hacker News Reader

SELECT causing changes to things makes me feel weird inside.

That's how every Postgres extension works (e.g. Timescale, PostGIS, etc.)

Not just Postgres extensions, most of Postgres’ own administrative functions can be triggered in SELECT statements. e.g. pg_terminate_backend()

https://www.postgresql.org/docs/current/functions-admin.html

To be quite clear, that always felt weird to me as well - something to the effect of "I thought SELECT was for reading data; why am I using it to create a replica?"

  DO $$ BEGIN
      PERFORM rebalance_table_shards();
  END $$;

SELECT is a bit like return?

return 6;

return x++;

or even

return (exit(0));

I think GP is saying they view SELECT as a "pure" function. A pure function doesn't modify state, so neither should SELECT.

Yeah, from limited experience with SQL "SELECT is safe" (assuming no weirdness in sub functions or commands) but that's clearly not always the case.

Reply on news.ycombinator.com