Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

guillaume@xxxxxxxxxxxx wrote:

This won't answer your question

It has been answered now. See my "case closed" email here:

www.postgresql.org/message-id/B33C40D9-2B79-44C7-B527-86E672BEA71A%40yugabyte.com

…but still… I usually really like your scripts, it's nicely written, but this part seems really weird to me:

bryn@xxxxxxxxxxxx wrote:

-- No error
do $body$
declare
  p int not null := 0;
begin
  for p in (
    select pid
    from pg_stat_activity
    where backend_type =  'client backend'
    and   pid          <> pg_backend_pid())
  loop
    perform pg_terminate_backend(p);
  end loop;
end;
$body$;

While your script works great, I'm wondering why you don't write it this way:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE backend_type =  'client backend' AND pid <> pg_backend_pid();

As it is less code, it's quicker to understand what it does.

Well, yes… I have often been accused of being anally fixated on details—and of verbosity. This is just the old chestnut that a "select" statement shouldn't have side effects. "pg_terminate_backend(p)" ought, by the book, to be a procedure. But I suppose that it dates from the time when PG had only user-defined functions (and no shipped procedures). And "perform" makes a function feel to me to be a bit more like a procedure than just selecting it feels. Others might well disagree…