Working with temporary tables (pg_temp)

Support
#1

Hi,

I have a Hierarchical Datasource to represent a treetable (not updatable by the user - only programatically updated), which is actually bound to a temporary Postgresql table (let’s call it pg_temp.mytable). Because it is temporary, this table is only visible in the current session (connection). It is created before the screen is loaded and populated with data by a postgresql function called from a service as the user double-clicks on the tree leafs. This function recreates the table if it doesn’t exist before filing it with data. This seems to work fine, but randomly, from time to time the table turns empty, as if it is deleted and recreated / the session is closed.
This wasn’t the case with non-temporary tables. But I need temporary table because I want a user to be able to generate this treetable and , at the same time, another user to generate another one without seeing the data from the other user. That’s why I thought that a temporary table would be ideal.
Has anyone worked with temporary postgresql tables before? Is this a normal behaviour? If yes, do you have a better solution?

Thank you.

#2

Hi Tudor,

Unfortunately I haven’t worked with temp tables in postgres, however they are very complex and volatile. Is there any other reason other than hiding data between the users? What happens to the data in the temp table once the screen closed or user logged out.

If you don’t want one user data to be seen by others, then you can use permanent table and use Row level Security a.k.a Constraints.

Constraints - CUBA Platform. Developer’s Manual.

Thanks,
Hari

#3

Hi Hari,

Thanks for your reply. Though, I worked with temporary tables in Postgresql before ( but not in cuba) and they are very stable. They are visible from they creation until the postgres connection closes and visible only for that connection. After that, postgresql does the job and deletes them. It is very convenient because you don’t bother and you don’t have to delete data manually as in working with normal tables and using constraints.
I don’t see why it shouldn’t work with cuba also.
Thanks,
Tudor

#4

Hi Tudor,

I’m not sure why it doesn’t work with Cuba. I’m wondering what would be the reason for creating a temporary table with persistence.

Thanks,
Hari

#5

Hi,
CUBA uses connection pool to access the database.
It means that connection pool stores and re-uses several connections for a long time.
For every transaction some random connection is borrowed from the pool. After transaction ends - connection returns back.
For the next invocation of DataManager or any other persistence-related code - another random connection is borrowed from the pool.

So you can’t rely on temporary tables in CUBA applications, other than for code which does all its job in one transaction.

If you need to store temporary per-session data, you can create normal table with one additional column - USER_SESSION_ID uuid;
Populate it from UserSessionSource.getUserSession().getId()
And then it to filter data from different user sessions.

1 Like
#6

Thanks Alex. I supposed that it has something to do with the db connection.
I will use your and Hari’s advice with User session, although the drawback is that now I’ll have to delete the data manually after session ends.

#7

Hi Tudor,

Why don’t you use an in memory object instead of table? As it is temporary and you don’t need the data after the session is complete.

Create the entity instance and keep it in a collection object. This way you don’t need to delete the record once the session is complete.

Thanks
Hari

Thanks
Hari

#8

Hi Hari,

It’s true, I thought of using a non-persistent entity. But, for now, it’s more complicated - in order to represent the data as a table (more specific - a treetable), I need a CustomCollectionDatasource. I found a topic that with Cuba 7.0 it’s no difference between persistent and non-persistent entities for representing the data. But, I cannot upgrade at this time, I have an ongoing project and there are a lot of changes.

1 Like