Re: [PATCH] Hooks at XactCommand level

Hi,

On 2021-08-10 10:12:26 +0200, Gilles Darold wrote:
> Sorry for the response delay. I have though about adding this odd hook to be
> able to implement this feature through an extension because I don't think
> this is something that should be implemented in core. There were also
> patches proposals which were all rejected.
>
> We usually implement the feature at client side which is imo enough for the
> use cases. But the problem is that this a catastrophe in term of
> performances. I have done a small benchmark to illustrate the problem. This
> is a single process client on the same host than the PG backend.
>
> For 10,000 tuples inserted with 50% of failures and rollback at statement
> level handled at client side:
>
>         Expected: 5001, Count:  5001
>         DML insert took: 13 wallclock secs ( 0.53 usr +  0.94 sys =  1.47
> CPU)

Something seems off here. This suggests every insert took 2.6ms. That
seems awfully long, unless your network latency is substantial. I did a
quick test implementing this in the naive-most way in pgbench, and I get
better times - and there's *lots* of room for improvement.

I used a pgbench script that sent the following:
BEGIN;
SAVEPOINT insert_fail;
INSERT INTO testinsert(data) VALUES (1);
ROLLBACK TO SAVEPOINT insert_fail;
SAVEPOINT insert_success;
INSERT INTO testinsert(data) VALUES (1);
RELEASE SAVEPOINT insert_success;
{repeat 5 times}
COMMIT;

I.e. 5 failing and 5 succeeding insertions wrapped in one transaction. I
get >2500 tps, i.e. > 25k rows/sec. And it's not hard to optimize that
further - the {ROLLBACK TO,RELEASE} SAVEPOINT; SAVEPOINT; INSERT can be
sent in one roundtrip. That gets me to somewhere around 40k rows/sec.

BEGIN;

\startpipeline
SAVEPOINT insert_fail;
INSERT INTO testinsert(data) VALUES (1);
\endpipeline

\startpipeline
ROLLBACK TO SAVEPOINT insert_fail;
SAVEPOINT insert_success;
INSERT INTO testinsert(data) VALUES (1);
\endpipeline

\startpipeline
RELEASE SAVEPOINT insert_success;
SAVEPOINT insert_fail;
INSERT INTO testinsert(data) VALUES (1);
\endpipeline

\startpipeline
ROLLBACK TO SAVEPOINT insert_fail;
SAVEPOINT insert_success;
INSERT INTO testinsert(data) VALUES (1);
\endpipeline

{repeat last two blocks three times}
COMMIT;

Greetings,

Andres Freund