EnterpriseDB
Watch temp tables in procedures?
Hello.
I have a job-posting web scraper that I built entirely in PostgreSQL. Many important parts of the scraping engine do calculations in temporary tables. It would be hence god-sent for me to be able to poke around those temporary tables as they are constructed while still being able to drop them at the end of the procedure.
But even while not dropping them, I am facing an issue watching them since they are only visible in the same session.
Do you have any ideas about how to approach this?
Orwa.
This issue is not solved simply by making a temp table non temporary. I have done that in order to gain more visibility. The issue is that procedures run inside of a transaction, so it really boils down to having the ability to see the queued changes in the database emanating from the procedure being debugged.
This would be enormously useful but I am not sure if it is possible since I am not very technical when it comes to PostgreSQL internals. In fact, this extension already feel god-sent and I am very grateful that it is possible to step through pl/pgsql code!
Yet I still want to discuss this to see how we can push it forward. As the world moves towards thicker databases, it is becoming imperative to make the dev experience within PostgreSQL better.
This issue is not solved simply by making a temp table non temporary. I have done that in order to gain more visibility. The issue is that procedures run inside of a transaction, so it really boils down to having the ability to see the queued changes in the database emanating from the procedure being debugged.
This would be enormously useful but I am not sure if it is possible since I am not very technical when it comes to PostgreSQL internals. In fact, this extension already feel god-sent and I am very grateful that it is possible to step through pl/pgsql code!
Yet I still want to discuss this to see how we can push it forward. As the world moves towards thicker databases, it is becoming imperative to make the dev experience within PostgreSQL better.
Hi. If I correctly understand you, your problem is that you do not see what is inside a temp table or not temp because it all goes in a separate transaction. If so, please see plpgsql functions print_table_as_json and print_query_as_json. The second one is able to print a prepared statement and the first one can print a normal table. The functions are in my fork of pgTap. https://github.com/v-maliutin/pgtap By the way, I face this problem every time I use pgTap. It always executes code in a transaction. And my functions save me all the times.
@diraneyya here how it looks
One note. My functions are in schema "tap". That's why I use search_path. Anyway please note when you are in a cycle you have to deallocate prepared statement calling drop_prepared_statement or doing it yourself. Another important thing is that print_query_as_json creates a table in public schema. Since those functions are for pgTap it is ok and such tables are rolled back in the end of test. But if you use my functions out of any test you should know that they create artefacts. print_table_as_json creates nothing. But to output a temp table you must create a normal table like create temp mytemptab as select * from mytab and give its name to function as a parameter.