Re: Thoughts on "Love Your Database"

On 21 May 2016 at 11:28, Chris Travers <chris(dot)travers(at)gmail(dot)com> wrote:

>
>
> On Fri, May 20, 2016 at 10:43 PM, Guyren Howe <guyren(at)gmail(dot)com> wrote:
>
>> On May 20, 2016, at 13:38 , Pierre Chevalier Géologue <
>> pierrechevaliergeol(at)free(dot)fr> wrote:
>> >
>> > Le 04/05/2016 18:29, Szymon Lipiński a écrit :
>> >> On the other hand, when I was trying to store all my logic in a
>> >> database, there was just one thing that made me hate it. Testing.
>> >> Testing the procedures inside the database was not easy, not funny, and
>> >> too much time consuming.
>> >
>> > Yes, very good point.
>>
>> Are there any best practices or tricks to make this easier?
>>
>
> Strangely I have never had a problem testing stored procedures. You have
> to create a data set for the tests of course and that is the hardest part,
> but there are some really nice things:
>
> 1. If your test scripts always roll back you can run them on a production
> database as a troubleshooting step
> 2. It is easy to hook things up to a TAP harness (whether using PgTAP or
> some hand-rolled solution). I think it would be harder to connect to xunit
> though. So use TAP ;-)
> 3. I usually create a test results table (in my test case, rolled back
> after!) which stores the test description and pass status. That makes it
> easy to check using other tools.
>
> Usually I set aside a range of things (negative id's for example) for
> testing purposes.
>
>
I had problems, and I'm really interested in making it work for me. I have
a couple of questions:
How do you manage versioning of the stored procedures? Especially do you
have any problems upgrades?
What about testing logic which is outside the database? Do you use pgtap
for testing the schema only, or to test some of the external logic as well?
Do you use logic inside and outside the database at the same time?
How does this scale to a couple of servers when the load is so huge you
need to have e.g. ten physical web servers at front? It seems for me that
it would be easier to spread the cpu logic overhead to plenty of servers
instead of having just one machine which needs to do all the things. But
maybe I'm wrong.

--
regards Szymon Lipiński