4 ms·

> Except there is no such thing as "actual parametrized queries" [...] as supported by any of the major RDBMS vendors. That would surprise me... I thought that

> Except there is no such thing as "actual parametrized queries" [...] as supported by any of the major RDBMS vendors. That would surprise me... I thought that when I send a parameterized query to PostgreSQL or MS SQL Server, most (or even all) of the query plan gets created without looking at any parameter values. And if that is true, then I think your "no such thing" claim cannot be true. If the query plan has already been created based on the unparameterized SQL string, then parameter values cannot cause it to do something crazy like drop an unrelated table. (But I haven't read the source code to either of those RDBMS, so maybe I am about to be surprised.)

tomnipotent 5y ago
> when I send a parameterized query to PostgreSQL or MS SQL Server You're not sending a parameterized query. The libraries are creating prepared statements under the hood, and managing them for you. This generally requires one call to prepare the statement and fetch some sort of handle, and then executing the statement with the handle from the first call. Part of the confusion is that it's common to see SQL libraries offer an API to parametrize queries using some sort of placeholder in the query, but it's really just a facade to perform local string interpolation along with some sort of vendor-specific sanitation scheme.
- hn_throwaway_99 5y ago
  > This generally requires one call to prepare the statement and fetch some sort of handle, and then executing the statement with the handle from the first call. Not always. The Node `pg` module pipelines down the commands on the connection: it sends a `prepare` command, then a `bind` command, then an `execute` command. Yes, it uses prepared statements under the hood, but there is no wire round trip between prepare and bind.
  - tomnipotent 5y ago
    Looks like Postgres 14 added pipeline mode in libpq, good to know!
hn_throwaway_99 5y ago
> I thought that when I send a parameterized query to PostgreSQL or MS SQL Server, most (or even all) of the query plan gets created without looking at any parameter values. Your comment peaked my interest so I looked into what postgres does, and it's pretty ingenious in my opinion. It can create either a generic plan (not looking at parameter values) or a custom plan where it looks at parameter values and then uses statistics to find the best plan. By default, for the first 5 executions of a prepared statement uses a custom plan. If the execution costs of these different plans are pretty close to each other, from then on it just uses the generic plan (idea being that the extra cost of generating a custom plan each time isn't worth it), but if they are different enough (meaning looking at statistics can have a big speedup), then custom plans are used going forward. This behavior can be tweaked using DB flags.

tomnipotent 5y ago

> when I send a parameterized query to PostgreSQL or MS SQL Server You're not sending a parameterized query. The libraries are creating prepared statements under the hood, and managing them for you. This generally requires one call to prepare the statement and fetch some sort of handle, and then executing the statement with the handle from the first call. Part of the confusion is that it's common to see SQL libraries offer an API to parametrize queries using some sort of placeholder in the query, but it's really just a facade to perform local string interpolation along with some sort of vendor-specific sanitation scheme.

hn_throwaway_99 5y ago
> This generally requires one call to prepare the statement and fetch some sort of handle, and then executing the statement with the handle from the first call. Not always. The Node `pg` module pipelines down the commands on the connection: it sends a `prepare` command, then a `bind` command, then an `execute` command. Yes, it uses prepared statements under the hood, but there is no wire round trip between prepare and bind.
- tomnipotent 5y ago
  Looks like Postgres 14 added pipeline mode in libpq, good to know!

hn_throwaway_99 5y ago

> I thought that when I send a parameterized query to PostgreSQL or MS SQL Server, most (or even all) of the query plan gets created without looking at any parameter values. Your comment peaked my interest so I looked into what postgres does, and it's pretty ingenious in my opinion. It can create either a generic plan (not looking at parameter values) or a custom plan where it looks at parameter values and then uses statistics to find the best plan. By default, for the first 5 executions of a prepared statement uses a custom plan. If the execution costs of these different plans are pretty close to each other, from then on it just uses the generic plan (idea being that the extra cost of generating a custom plan each time isn't worth it), but if they are different enough (meaning looking at statistics can have a big speedup), then custom plans are used going forward. This behavior can be tweaked using DB flags.