tap tests driving the database via psql

Hi,

The discussion in [1]
again reminded me how much I dislike that we currently issue database
queries in tap tests by forking psql and writing/reading from it's
stdin/stdout.

That's quite cumbersome to write, and adds a good number of additional
failure scenarios to worry about. For a lot of tasks you then have to
reparse psql's output to separate out columns etc.

I think we have a better approach for [1] than using tap tests, but I
think it's a more general issue preventing us from having more extensive
test coverage, and especially from having more robust coverage.

I think we seriously ought to consider depending on a proper perl
database driver. I can see various approaches:

1) Just depend on DBD::Pg being installed. It's fairly common, after
all. It'd be somewhat annoying that we'd often end up using a
different version of libpq than what we're testing against. But in
most cases that'd not be particularly problematic.

2) Depend on DBD::PgPP, a pure perl driver. It'd likely often be more
lightweight to install. On the other hand, it's basically
unmaintained (last commit 2010), and is a lot less commonly already
installed than DBD::Pg. Also depends on DBI, which isn't part of a
core perl IIRC.

3) Vendor a test-only copy of one of those libraries, and build them as
part of the test setup. That'd cut down on the number of
dependencies.

But probably not that much, because we'd still depend on DBI, which
IIRC isn't part of core perl.

DBI by default does include C code, and is not that small. There's
however a pure perl version maintained as part of DBI, and it looks
like it might be reasonably enough sized. If we vendored that, and
DBD::PgPP, we'd not have any additional dependencies.

I suspect that the licensing (dual GPL *version 1* / Artistic
License, also V1), makes this too complicated, however.

4) We develop a fairly minimal pure perl database driver, that doesn't
depend on DBI. Include it somewhere as part of the test code, instead
of src/interfaces, so it's clearer that it's not ment as an actual
official driver.

The obvious disadvantage is that this would be a noticable amount of
code. But it's also not that crazily much.

One big advantage I can see is that that'd make it a lot easier to
write low-level protocol tests. Right now we either don't have them,
or they have to go through libpq, which quite sensibly doesn't expose
all the details to the outside. IMO it'd be really nice if we had a
way to to write low level protocol tests, especially for testing
things like the v2 protocol.

I'm not volunteering to do 4), my perl skills aren't great (if the test
infra were python, otoh... I have the skeleton of a pure perl driver
that I used for testing somewhere). But I am leaning towards that being
the most reasonable choice.

Craig, IIRC you'd thought about this before too?

Greetings,

Andres Freund

[1] https://www.postgresql.org/message-id/31304.1564246011%40sss.pgh.pa.us