Re: Patch to fix search_path defencies with pg_bench

On Tue, 5 May 2009, Tom Lane wrote:

> I agree that it probably wasn't considered carefully whether pg_bench
> should do that; but does anyone see a reason not to change it?

I thought of one pretty weak use-case for not making this change, but
would wager the additional flexibility here is far more likely to be
appreciated. I'd say it's a clear net improvement.

As for that case...many good database designs put all the user relations
into a schema, so that it's easier to do bulk operations on all of them
while avoiding catalog tables etc.--less work to filter out pg_class to
find them for example.

I once did some pgbench testing on a system that included a real
"accounts" table in a named schema. "pgbench -i" will execute "drop table
if exists accounts". It had already accidentally wiped out the copy of
the accounts table on the system during an earlier test, before the schema
policy was in place, leaving everyone wary of it.

I was able to defend the risk for running pgbench with the new schema
layout by saying "that can only execute against public.accounts no matter
what the user search_path is, so you're safe now". That made everybody
happy. Anyone counting on such behavior could be rudely surprised at this
change. For all I know I'm the only person to ever actually run into that
particular situation though.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD