Re: pgbench - test whether a variable exists

Bonjour Michaël,

>> Patch v3 is also a rebase.
>
> This has rotten for half a year, so I am marking it as returned with
> feedback. There have been comments from Alvaro and Andres as well...

Attached a v4. I'm resurrecting this small patch, after "\aset" has been
added to pgbench (9d8ef988).

Alvaro's feedback was about the lack of "pg_strndup" availability, but he
concluded that it would seldom be used if available, so it was not worth
the effort to add it for the sake of this small patch.

About arguments for this patch:

First, this syntax is already available in "psql", and I think that
keeping pgbench/psql in sync is better for users' ease of mind. That is
the initial (weak) argument about which Andres objected.

Second, it is on the path to move pgbench expression as a front-end util
that can be used by psql, which is still a project of mine, although I
have not started much on that yet. For that pgbench expressions must be
able to do what psql can do before merging, including this test. Other
things needed before this is stated are a working free on expression trees
(trivial), merging variable management to some extent (at least the same
API, possibly the same implementation would save quite a few lines of
code), having string values, support for :'var' and :"var" string escapes…
no very big deals, but some work anyway.

Third, I have a practical pgbench-specific use case, which motivates the
resurrection right now: I'd like to be able to run a benchmark with a mix
of SELECT, UPDATE, DELETE and INSERT commands, that is expected in a
normal functioning database system.

For INSERT, I think I have a few ideas for possible and simple solutions,
but it still need some thoughts so this is for later. The key issue is
how to handle a varying number of rows.

Under DELETE, some SELECT and UPDATE scripts may fail because the data are
not there anymore, hence the ability to check whether a variable is empty
comes handy:

Low probability delete script:

\set uid random(...)
DELETE FROM Operations WHERE oid IN (... :uid) \;
DELETE FROM Accounts WHERE aid IN (... :uid) \;
DELETE FROM Client WHERE ... :uid;

Parallel running update script:

-- a pseudo random client arrives
\set uname random(...)
SELECT uid FROM Client WHERE uname = :uname::TEXT \aset
-- remainder must be skipped if no client was found
\if :{?uid}
SELECT SUM(abalance) FROM Account WHERE uid = :uid ...
-- if the balance is right, withdrawing is possible...
...
\else
-- skip silently, the client has left!
\endif

--
Fabien.