How to update column for each SELECT in a given table?

Question

I tried to create a trigger for a table using for it to be activated after each select, but researching a little I discovered that it is not possible to create triggers for selections, only for update (update), insertion (Insert) and deletion (delete).

My application is an api and the same is presenting problems in Benchmark test; every time I make one select i have another function that performs an update and increments a column of that selected record. The problem is that many updates at the same time are causing this error:

Sorry, Too Many clients already

That’s when I thought about creating a trigger right in the database not to have to run a update via code to each select.

This was the code I created in plpgsql to try incrementing the column:

create table urls(
    id integer not null,
    url varchar(255) not null,
    encoded varchar(255) not null UNIQUE,
    clicks integer DEFAULT 0,
    created_at timestamp not null DEFAULT current_timestamp,
    constraint pk_urls_id primary key (id)
);


CREATE OR REPLACE FUNCTION increment_clicks_counter()
    returns trigger as
        $BODY$
            BEGIN
                UPDATE urls SET clicks = clicks + 1 WHERE encoded = OLD.encoded;
            END;
        $BODY$
language 'plpgsql';

CREATE trigger increment_clicks AFTER SELECT ON urls
    for each ROW EXECUTE procedure increment_clicks_counter();

How can I create a routine to increment a column for each selection of a given table?

This is the Benchmark I’m running on GO:

func BenchmarkAPI(b *testing.B) {
    // Valores que estão presentes na coluna 'encoded' da tabela urls
    random := []string{
        "A", "B", "C", "D", "H", "F", "E", "G",
        "8", "5", "9", "6", "7", "2",
    }

    for n := 0; n < b.N; n++ {
        url := fmt.Sprintf("http://localhost:5000/%s", random[rand.Intn(len(random))])

        _, err := http.Get(url)
        if err != nil {
            b.Log(err.Error())
        }
    }
}

Answer 1

Unfortunately (or fortunately), in PostgreSQL there are no TRIGGERS of the kind AFTER SELECT.

However, you can solve your problem by "encapsulating" your table in a stored Procedure written in PL/pgSQL.

That one stored Procedure would be able to recover the data from the table and then record this activity in another specific table, the table could not be accessed directly and your data would only be recovered through this encapsulation function.

Consider the following structure/data:

CREATE TABLE tb_pessoa
(
    id BIGINT PRIMARY KEY,
    nome TEXT NOT NULL,
    nascimento DATE NOT NULL,
    sexo VARCHAR(1) NOT NULL
);

CREATE TABLE tb_log
(
    id BIGSERIAL PRIMARY KEY,
    tabela TEXT NOT NULL,
    operacao TEXT NOT NULL,
    diahora TIMESTAMP NOT NULL 
);

INSERT INTO tb_pessoa ( id, nome, nascimento, sexo ) VALUES ( 1, 'JOAO',  '1980.02.10', 'M' );
INSERT INTO tb_pessoa ( id, nome, nascimento, sexo ) VALUES ( 2, 'MARIA', '1966.05.10', 'F' );
INSERT INTO tb_pessoa ( id, nome, nascimento, sexo ) VALUES ( 3, 'JOSE',  '1973.07.10', 'M' );
INSERT INTO tb_pessoa ( id, nome, nascimento, sexo ) VALUES ( 4, 'ANA',   '1984.03.10', 'F' );
INSERT INTO tb_pessoa ( id, nome, nascimento, sexo ) VALUES ( 5, 'JESUS', '1970.12.10', 'M' );

This encapsulation function would look something like this:

CREATE OR REPLACE FUNCTION fc_wrapper_pessoa()
RETURNS SETOF tb_pessoa AS
$$
DECLARE
    rec tb_pessoa%rowtype;
BEGIN
    FOR  rec IN SELECT * FROM tb_pessoa
    LOOP
        return next rec;
    END LOOP;

    INSERT INTO tb_log ( tabela, operacao, diahora ) VALUES ( 'tb_pessoa', 'SELECT', now() );

    RETURN;
END
$$
LANGUAGE 'plpgsql';

The queries to the table tb_pessoa would all be done through this function, for example:

SELECT * FROM fc_wrapper_pessoa();
SELECT * FROM fc_wrapper_pessoa() WHERE id = 5;
SELECT * FROM fc_wrapper_pessoa() WHERE nome = 'JESUS';
SELECT * FROM fc_wrapper_pessoa() WHERE nascimento BETWEEN '1960-01-01' AND '1969-12-31';

And finally, for each query made in the table tb_pessoa would be recorded a record in the table tb_log:

SELECT * FROM tb_log WHERE tabela = 'tb_pessoa' AND operacao = 'SELECT';

Exit:

| id |    tabela | operacao |                     diahora |
|----|-----------|----------|-----------------------------|
| 11 | tb_pessoa |   SELECT | 2018-06-14T16:06:18.073404Z |
| 12 | tb_pessoa |   SELECT | 2018-06-14T16:06:18.073404Z |
| 13 | tb_pessoa |   SELECT | 2018-06-14T16:06:18.073404Z |
| 14 | tb_pessoa |   SELECT | 2018-06-14T16:06:18.073404Z |

If what matters to you is the amount of queries the table has suffered:

SELECT count(1) FROM tb_log WHERE tabela = 'tb_pessoa' AND operacao = 'SELECT';

Exit:

| count |
|-------|
|     4 |

Sqlfiddle