Short Version:

Title basically. In other words, if a PostgreSQL query begins with "SELECT" then is it safe to assume that this query is read-only, i.e. not changing anything in the DB?

Detailed Version:

I'm scaling a PostgreSQL DB for an object oriented multi-component application by dispatching READ queries to a read-replica instance. Each component in the application is responsible for building up its query, but all queries end up in one component to dispatch to the DB for execution. I don't want other components to deal with the task of differentiating read queries from non-read queries. I only want the component in charge of query handling to deal with it. Hence, I'm looking for a way to detect read-only queries and dispatch them to the read-replica DB rather than the main DB to balance the load on the main DB.

My current solution is to check if the query begins with "SELECT" and if so, mark it as read-only and safely dispatch it to the read-replica DB. Am I doing it wrong?

CodePudding user response：

No, that's not safe to assume.

In Postgres functions are allowed to run DML and DDL statements, so

select some_function();

can change the database.

Postgres also has a non-standard alternative to create table ... as select ... that starts with a SELECT

 select *
   into some_table
 from ...

Which will create a new table named some_table