Postgres Pro
Downloads
Home   >   mailing lists

Thread: Ways to "serialize" result set for later use?

Ways to "serialize" result set for later use?

From
Adam Brusselback
Date:
Hey there everyone,

I am going through the process of writing my first pgtap tests for my database, and I wanted to get some feedback on if my solution seems fine, is just dumb, or could be acomplished much easier another way.

So my main problem I was trying to work around, was my tests are written in functions and called using runtests(), so using psql and \copy to save test data to the filesystem isn't really part of the workflow, but I still needed a way to have my "expected" query resultset passed into results_eq [https://pgtap.org/documentation.html#results_eq] easily within a function body.

I originally manually dumped some "known good" data from a query to csv, and built some SELECT ... FROM VALUES (...) statements by hand to do this. That obviously sucks.

Then I started looking to see if there was a way to get Postgres to "serialize" a query resultset to a values list similar to how pg_dump is able to be configured to dump data as inserts for the plain format. I couldn't find anything at all though. No mention of anything similar on the mailing list, stackoverflow, etc... I feel like I must be searching incorrectly.

Anyways, since I didn't find anything, or any discussion online for something like I wanted, I just tried building it, see attached for source. 
It's a function that takes in the text of a query that produces a resultset, and returns a SELECT ... FROM VALUES (...) statement that will produce the exact same output.
It does so by running that query and creating a temporary table with the results, then we query the system catalogs to get the data types, and column names of the temporary table produced by the query, and then uses that information to build a VALUES from clause that contains each row by scanning the temp table, and also dynamically builds the SELECT ... columns list to cast everything to the correct data type, we then put all of that together and return the query text to the caller. 

Not fully tested or anything, and not guaranteed to work well. Please, if you see any issues let me know. I got this together in an hour of hacking... but it did solve my immediate problem and I have been able to generate easy "expected" result sets for my tests.

So I really just wanted to see if there is a better way to go about what i'm trying to do, does Postgres already support something similar I can harness instead of this hack? Or is this really an alright way to go?


Attachment

RE: Ways to "serialize" result set for later use?

From
Kevin Brannen
Date:

From: Adam Brusselback <adambrusselback@gmail.com>
Sent: Saturday, April 10, 2021 9:06 PM
To: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Ways to "serialize" result set for later use?

 

Hey there everyone,

 

I am going through the process of writing my first pgtap tests for my database, and I wanted to get some feedback on if my solution seems fine, is just dumb, or could be acomplished much easier another way.

 

So my main problem I was trying to work around, was my tests are written in functions and called using runtests(), so using psql and \copy to save test data to the filesystem isn't really part of the workflow, but I still needed a way to have my "expected" query resultset passed into results_eq [https://pgtap.org/documentation.html#results_eq] easily within a function body.

 

I originally manually dumped some "known good" data from a query to csv, and built some SELECT ... FROM VALUES (...) statements by hand to do this. That obviously sucks.

 

…

 

So I really just wanted to see if there is a better way to go about what i'm trying to do, does Postgres already support something similar I can harness instead of this hack? Or is this really an alright way to go?

 

This seems more like an application question, but I'll throw something out for you to consider…

 

IMO, you're trying to put pgtap into an area it wasn't really made for. If you can make it do what you want, good for you, but I wouldn't try that. Pgtap is great for things like:

  • Does my DB/Schema/Tables/Views/Functions/… exist, have the correct owner, etc.
  • Does each table have the right columns, defaults, constraints, etc.
  • Testing of simple functions is possible, but probably only really for "immutable" stuff, like I insert "x" I better always get "y" out of it.
  • More DDL stuff like this that's important because you want to know if your "alter" script after an upgrade did the right thing.

 

Checking data (DML), if functions are doing the right things is something we do in our code unit tests.

 

Of course, TMTOWTDI, YMMV, etc. 😊

 

HTH,

Kevin

.

This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.

Re: Ways to "serialize" result set for later use?

From
Adam Brusselback
Date:
>  Checking data (DML), if functions are doing the right things is something we do in our code unit tests.

This is exactly what I am writing, unit tests for my code (which is pl/pgsql). This is an ELT pipeline for my customers to bulk update their data in my system, with detailed error reporting for any issues per-row/column. The code is all plpgsql, as are the few tests i've written so far. pgTAP is my unit testing framework for this process.

So unit testing my company's (vast) database code is something I am just trying to figure out and get into my workflow, and it didn't feel like I had to fight too hard with it at this point, other than $subject$. And even that isn't an issue with my hacky function in place, it just feels a little...dirty I guess? Was just wanting a gut check if there seemed to be an obviously better way to get the same results.

RE: Ways to "serialize" result set for later use?

From
Kevin Brannen
Date:

From: Adam Brusselback <adambrusselback@gmail.com>
Sent: Monday, April 12, 2021 12:51 PM

 

>  Checking data (DML), if functions are doing the right things is something we do in our code unit tests.

 

This is exactly what I am writing, unit tests for my code (which is pl/pgsql). This is an ELT pipeline for my customers to bulk update their data in my system, with detailed error reporting for any issues per-row/column. The code is all plpgsql, as are the few tests i've written so far. pgTAP is my unit testing framework for this process.

 

So unit testing my company's (vast) database code is something I am just trying to figure out and get into my workflow, and it didn't feel like I had to fight too hard with it at this point, other than $subject$. And even that isn't an issue with my hacky function in place, it just feels a little...dirty I guess? Was just wanting a gut check if there seemed to be an obviously better way to get the same results.

 

If there is something built into Pg that does what you want, I'm not aware of it, but there are a number of extensions out there. You could check out that list to see.

 

Otherwise, I'm not sure we can help you much. If that approach seems to be working, continue on and see where it takes you.

 

Our unit tests are in Perl based on a module we created that inherits from Test::More. From there, we can exercise any code we need, many of which contains calls to DB functions, injecting known values for all the use cases we can think of, and making sure we get back the expected results. That also means we can COPY data in for testing if required. It's pretty simplistic and straight-forward in some ways, but it works for us.

 

HTH,

Kevin

.

This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.