Обсуждение: 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

On Sat, Jul 27, 2019 at 12:15:23PM -0700, Andres Freund wrote:
> 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.

There's one that may or may not need updates that's basically just a
wrapper around libpq.

https://ftp.postgresql.org/pub/projects/gborg/pgperl/stable/

>    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.

That sounds worth doing as a separate thing, and an obvious
application of it would be something like a febesmith, which would get
us a better idea as to whether we've implemented the protocol we say
we have.

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Hi,

On 2019-07-27 22:32:37 +0200, David Fetter wrote:
> On Sat, Jul 27, 2019 at 12:15:23PM -0700, Andres Freund wrote:
> > 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.
> 
> There's one that may or may not need updates that's basically just a
> wrapper around libpq.
> 
> https://ftp.postgresql.org/pub/projects/gborg/pgperl/stable/

That's pretty darn old however (2002). Needs to be compiled. And is GPL
v1 / Artistic v1 licensed.  I think all of the other alternatives are
better than this.


> >    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.
> 
> That sounds worth doing as a separate thing

What would be the point of doing this separately? If we have a small
driver for writing protocol tests, why would we want something
separate for the tap tests?


> and an obvious application of it would be something like a febesmith,
> which would get us a better idea as to whether we've implemented the
> protocol we say we have.

Hm, not convinced that's useful. And fairly sure that's pretty
independent of what I was writing about.

Greetings,

Andres Freund

On 7/27/19 3:15 PM, Andres Freund wrote:
> 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.
>


+1 for #4.


I'll be happy to participate in any effort.


About 22 years ago I wrote a pure perl implementation of a wire protocol
of roughly similar complexity (RFC1459). I got it basically working in
just a few days, so this sort of thing is very doable. Let's see your
skeleton and maybe it's a good starting point.


cheers


andrew

-- 
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Hi,

On 2019-07-27 17:48:39 -0400, Andrew Dunstan wrote:
> On 7/27/19 3:15 PM, Andres Freund wrote:
> > 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.
> 
> +1 for #4.
> 
> 
> I'll be happy to participate in any effort.
> 
> 
> About 22 years ago I wrote a pure perl implementation of a wire protocol
> of roughly similar complexity (RFC1459). I got it basically working in
> just a few days, so this sort of thing is very doable. Let's see your
> skeleton and maybe it's a good starting point.

The skeleton's in python though, not sure how helpful that is?

/me once more regrets that perl, not python, has been chosen as the
scripting language of choice for postgres...

Greetings,

Andres Freund

On 7/27/19 6:37 PM, Andres Freund wrote:
> Hi,
>
> On 2019-07-27 17:48:39 -0400, Andrew Dunstan wrote:
>> On 7/27/19 3:15 PM, Andres Freund wrote:
>>> 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.
>> +1 for #4.
>>
>>
>> I'll be happy to participate in any effort.
>>
>>
>> About 22 years ago I wrote a pure perl implementation of a wire protocol
>> of roughly similar complexity (RFC1459). I got it basically working in
>> just a few days, so this sort of thing is very doable. Let's see your
>> skeleton and maybe it's a good starting point.
> The skeleton's in python though, not sure how helpful that is?


Maybe I don't write much python but I can read it without too much
difficulty :-)


But you did say your skeleton was pure perl ... slip of the fingers?


>
> /me once more regrets that perl, not python, has been chosen as the
> scripting language of choice for postgres...
>


That ship has sailed long ago.


cheers


andrew



-- 
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Hi,

On 2019-07-27 18:57:58 -0400, Andrew Dunstan wrote:
> Maybe I don't write much python but I can read it without too much
> difficulty :-)
> 
> 
> But you did say your skeleton was pure perl ... slip of the fingers?

Ooops, yea.


> > /me once more regrets that perl, not python, has been chosen as the
> > scripting language of choice for postgres...

> That ship has sailed long ago.

Indeed. Not proposing to change that. Just sad about it...

Greetings,

Andres Freund

Craig Ringer <craig@2ndquadrant.com> writes:
> On Sun, 28 Jul 2019 at 03:15, Andres Freund <andres@anarazel.de> wrote:
>> 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.

> I advocated for this in the past, and still think it's the best option.

I think the not-same-libpq issue would be a much bigger problem than either
of you are accounting for.  Some off-the-top-of-the-head reasons:

* Since we'd have to presume a possibly-very-back-rev libpq, we could
never add tests related to any recent libpq bug fixes or new features.

* The system libpq might have a different idea of default port
and/or socket directory than the test build.  Yeah, we could work
around that by forcing the choice all the time, but it would be a
constant hazard.

* We don't have control over what else gets brought in beside libpq.
Depending on the whims of the platform packagers, there might need
to be other parts of the platform's default postgres installation,
eg psql, sitting in one's PATH.  Again, this wouldn't be too much
of a hazard for pre-debugged test scripts --- but it would be a huge
hazard for developers, who do lots of things manually and would always be
at risk of invoking the wrong psql.  I learned long ago not to have any
part of a platform's postgres packages in place on my development systems,
and I will fight hard against any test procedure change that requires me
to do differently.

Now, none of these things are really a problem with DBD/DBI as such
--- rather, they are reasons not to depend on a pre-packaged build
of DBD::Pg that depends on a pre-packaged build of libpq.so.
I haven't looked at the size, or the license, of DBD::Pg ... but
could it be sane to include our own modified copy in the tree?

(Forking DBD::Pg would also let us add custom testing features
to it ...)

> The community IMO wastes *so* much time on not-invented-here make-work and
> on jumping through hoops to avoid depending on anything newer than the late
> '90s.

That's an unnecessary, and false, ad-hominem attack.

            regards, tom lane

Hi,

On 2019-07-30 09:40:54 -0400, Tom Lane wrote:
> Craig Ringer <craig@2ndquadrant.com> writes:
> > On Sun, 28 Jul 2019 at 03:15, Andres Freund <andres@anarazel.de> wrote:
> >> 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.
> 
> > I advocated for this in the past, and still think it's the best option.
> 
> I think the not-same-libpq issue would be a much bigger problem than either
> of you are accounting for.  Some off-the-top-of-the-head reasons:

I came to the same conclusion?


> Now, none of these things are really a problem with DBD/DBI as such
> --- rather, they are reasons not to depend on a pre-packaged build
> of DBD::Pg that depends on a pre-packaged build of libpq.so.
> I haven't looked at the size, or the license, of DBD::Pg ... but
> could it be sane to include our own modified copy in the tree?

I had that as an alternative too. I think the license (Artistic v1/GPL
v1) probably makes that non-feasible. The pure-perl version of DBI
probably would otherwise be realistic.

Greetings,

Andres Freund

Andres Freund <andres@anarazel.de> writes:
> On 2019-07-30 09:40:54 -0400, Tom Lane wrote:
>> Now, none of these things are really a problem with DBD/DBI as such
>> --- rather, they are reasons not to depend on a pre-packaged build
>> of DBD::Pg that depends on a pre-packaged build of libpq.so.
>> I haven't looked at the size, or the license, of DBD::Pg ... but
>> could it be sane to include our own modified copy in the tree?

> I had that as an alternative too. I think the license (Artistic v1/GPL
> v1) probably makes that non-feasible. The pure-perl version of DBI
> probably would otherwise be realistic.

OK, so just lifting DBD::Pg in toto is out for license reasons.
However, maybe we could consider writing a new DBD driver from
scratch (while using a platform-provided DBI layer) rather than
doing everything from scratch.  I'm not sure how much actual
functionality is in the DBI layer, so maybe that approach
wouldn't buy much.

            regards, tom lane

On 2019-Jul-30, Tom Lane wrote:

> OK, so just lifting DBD::Pg in toto is out for license reasons.
> However, maybe we could consider writing a new DBD driver from
> scratch (while using a platform-provided DBI layer) rather than
> doing everything from scratch.  I'm not sure how much actual
> functionality is in the DBI layer, so maybe that approach
> wouldn't buy much.

Then again, maybe we don't *need* all the functionality that DBI offers.
DBI is enormous, has a lot of extensibility, cross-database
compatibility ... and, well, just the fact that it's a layered design
(requiring a DBD on top of it before it even works) makes it even more
complicated.

I think a pure-perl standalone driver might be a lot simpler than
maintanining our own DBD ... and we don't have to convince animal
maintainers to install the right version of DBI in the first place.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Hi,

On 2019-07-31 09:32:10 +0800, Craig Ringer wrote:
> OK. So rather than building our own $everything from scratch, lets look at
> solving that.

IDK, a minimal driver that just does what we need it to do is a few
hundred lines, not more. And there's plenty of stuff that we simply
won't be able to test with any driver that's not purposefully written
for testing. There's e.g. basically no way with any of the drivers to
test intentionally bogus sequences of protocol messages, yet that's
something we really ought to test.

I've written custom hacks^Wprograms to tests things like that a few
times. That really shouldn't be necessary.


> Perl modules support local installations. Would it be reasonable to require
> DBI to be present, then rebuild DBD::Pg against our libpq during our own
> test infra compilation, and run it with a suitable LD_LIBRARY_PATH or using
> rpath? That'd actually get us a side-benefit of some test coverage of
> DBD::Pg and libpq.

You're intending to download DBD::Pg? Or how would you get around the
licensing issues? Downloading it will have some issues too: For one at least I
often want to be able to run tests when offline; there's security
concerns too.

Greetings,

Andres Freund

On Tue, Jul 30, 2019 at 07:20:53PM -0700, Andres Freund wrote:
> IDK, a minimal driver that just does what we need it to do is a few
> hundred lines, not more. And there's plenty of stuff that we simply
> won't be able to test with any driver that's not purposefully written
> for testing. There's e.g. basically no way with any of the drivers to
> test intentionally bogus sequences of protocol messages, yet that's
> something we really ought to test.

Yes, I don't know if a hundred of lines measure that correctly, but
really we should avoid bloating the stuff we rely on like by fetching
an independent driver if we finish by not using most of its features.
300~500 lines would be fine for me, 10k definitely not.

> I've written custom hacks^Wprograms to tests things like that a few
> times. That really shouldn't be necessary.

Mine are usually plain hacks :), and usually on top of libpq but I
have done some python-ish script to send bogus and hardcoded protocol
messages.  If we can automate a bit this area, that could be useful.

>> Perl modules support local installations. Would it be reasonable to require
>> DBI to be present, then rebuild DBD::Pg against our libpq during our own
>> test infra compilation, and run it with a suitable LD_LIBRARY_PATH or using
>> rpath? That'd actually get us a side-benefit of some test coverage of
>> DBD::Pg and libpq.
>
> You're intending to download DBD::Pg? Or how would you get around the
> licensing issues? Downloading it will have some issues too: For one at least I
> often want to be able to run tests when offline; there's security
> concerns too.

As Craig has mentioned CPAN can be configured to install all the
libraries for a local user, so there is no need to be online if a copy
has been already downloaded.
--
Michael