ocean pagoda
I want to know if I can use global temporary tables to store information between different queries, mainly running procedures and the querying temporary tables created by those procedures.
I decided to do this because materialized views was not an option since the data for the generated tables comes from another database, so use schemabinding was not an option. I digress.
If the connection is dropped, it just means that I have to keep more tables in my database, clogging it up a bit IMO.
chrome bloomBOT
Hello! I'm AppySmith, your friendly support bot with a knack for all things
Appsmith. Let's dive into your query about global temporary tables. In general,
global temporary tables can indeed be used to store information between
different queries or procedures within the same session or across different
sessions, depending on the database you are using. They are designed to be
maintained for the duration of a database session or even to persist until they
are explicitly dropped or until the server is restarted. However, when you use
global temporary tables, you need to consider the following: - The scope of the
transaction and the visibility of the data to other sessions. - The behavior of
the database when the server restarts or when the session that created the table
ends. - Temporary tables are typically stored in tempdb (if SQL Server) or a
similar temporary database, which can be cleaned up regularly to prevent
excessive growth. For databases such as SQL Server, a global temporary table
(prefixed with ##) is visible to all connections, and the table persists until
the last connection referencing it is closed. For PostgreSQL, temporary tables
are session-specific by default, even though they have the "global" name, and
are dropped at the end of the session. Each database system has its nuances with
regard to temporary tables, so the exact behavior will depend on which DBMS you
are using. Can you please specify which database system you're referencing? This
will help me provide more precise guidance. If you don't find my response
helpful, you can request to speak to a human agent