Re: database design with temporary tables
On 8/29/21 2:26 PM, Adrian Klaver wrote: The pool is maintained, the individual connections(sessions) come and go. Otherwise there would be no point to having a pool. Every time the connection(session) is closed the temporary table disappears. Yes, you're right. And that is very convenient. I also prefer temporary tables with ON COMMIT DELETE ROWS so that I don't have leftovers between transactions. There is also pgtt extension by Giles Darold which emulates Oracle global temporary tables: https://github.com/darold/pgtt#use-of-the-extension I have never particularly liked Oracle's implementation of temporary tables, local or global, so I have no problems with questions like that. BTW, I have compared Oracle's implementation with other implementations here: https://dbwhisperer.wordpress.com/2019/03/18/private-temporary-tables-oracle-18c/ I could have used Postgres instead of the SQL Server, the comparison would read the same, except for the "#" character which denotes temporary table in SQL Server. Regards -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: database design with temporary tables
On 8/29/21 10:41 AM, Mladen Gogala wrote: On 8/29/21 12:24 PM, Adrian Klaver wrote: Presumably not. Temporary tables only live at most for the length of a session. It would be a really bad idea to hold sessions open for 24 hours. That is assuming nothing else causes the session to drop and the data to be lost. Well, that's precisely how application servers work. The allocate connection pool and keep them open for a very long time. The pool is maintained, the individual connections(sessions) come and go. Otherwise there would be no point to having a pool. Every time the connection(session) is closed the temporary table disappears. -- Adrian Klaver adrian.kla...@aklaver.com
Re: database design with temporary tables
On 8/29/21 12:24 PM, Adrian Klaver wrote: Presumably not. Temporary tables only live at most for the length of a session. It would be a really bad idea to hold sessions open for 24 hours. That is assuming nothing else causes the session to drop and the data to be lost. Well, that's precisely how application servers work. The allocate connection pool and keep them open for a very long time. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: database design with temporary tables
On Sunday, August 29th, 2021 at 5:56 PM, Ray O'Donnell wrote: > - or do they connect only to the web server, which then > > uses its own account on the database server? Thank you; was not aware of this consideration and this option seems most appropriate for the simple sql commands envisaged.
Re: database design with temporary tables
On 29/08/2021 17:53, Adrian Klaver wrote: On 8/29/21 9:38 AM, ourdiaspora wrote: ‐‐‐ Original Message ‐‐‐ On Sunday, August 29th, 2021 at 5:30 PM, Adrian Klaver wrote: On 8/29/21 9:24 AM, Adrian Klaver wrote: Whoops, unfinished thought. What I was going to ask is: The above is not clear to me. Are you asking about the Postgres documentation? Yes, wanted to know relevant parts because often the first problem is to know which part of the (extensive) documentation to read... From what I see it is basic operations; CREATE TABLE, SELECT, INSERT, UPDATE and COPY. As I said in my previous post create a fairly detailed design flow first and from that you will see what operations you need to do and what part of the docs you need to look at. +100 to this. Also, it's not clear from your description what is happening where - how much functionality is in the database, and how much at the web server. Are your users connecting directly to the database server? - or do they connect only to the web server, which then uses its own account on the database server? Get this clear in your own head and much of the rest will follow. Ray. -- Raymond O'Donnell // Galway // Ireland r...@rodonnell.ie
Re: database design with temporary tables
On 8/29/21 9:38 AM, ourdiaspora wrote: ‐‐‐ Original Message ‐‐‐ On Sunday, August 29th, 2021 at 5:30 PM, Adrian Klaver wrote: On 8/29/21 9:24 AM, Adrian Klaver wrote: Whoops, unfinished thought. What I was going to ask is: The above is not clear to me. Are you asking about the Postgres documentation? Yes, wanted to know relevant parts because often the first problem is to know which part of the (extensive) documentation to read... From what I see it is basic operations; CREATE TABLE, SELECT, INSERT, UPDATE and COPY. As I said in my previous post create a fairly detailed design flow first and from that you will see what operations you need to do and what part of the docs you need to look at. -- Adrian Klaver adrian.kla...@aklaver.com
Re: database design with temporary tables
On 8/29/21 9:36 AM, ourdiaspora wrote: ‐‐‐ Original Message ‐‐‐ On Sunday, August 29th, 2021 at 5:24 PM, Adrian Klaver wrote: Presumably not. Temporary tables only live at most for the length of a session. It would be a really bad idea to hold sessions open for 24 hours. Is there an alternative scenario, such as the user is able to create a new table with saves the session data for a maximum time (such as 24 hours), even up to a certain time if the web browser crashes for example? This is a it depends answer. Some factors in play: 1) Number of users over say a 24 hour period. 2) The size of the CSV files. 3) Is the data merge a one time thing or can it be repeated in a time period. 4) Are users going to be repeat customers? For this sort of thing I write out a detailed outline of the who/what/why/where/when. In doing that answers tend to follow. The abo Incomplete response? -- Adrian Klaver adrian.kla...@aklaver.com
Re: database design with temporary tables
‐‐‐ Original Message ‐‐‐ On Sunday, August 29th, 2021 at 5:38 PM, Ray O'Donnell wrote: > > I'd save a timestamp with the session data, and then run a cron job > > which deletes sessions older than whatever lifetime you want. > Is it prudent in this scenario for the user to create (a) tables of her/his data, with role as her/his e-mail address (no password)?
Re: database design with temporary tables
On Sunday, August 29, 2021, Ray O'Donnell wrote: > >>> >> Is there an alternative scenario, such as the user is able to create >> a new table with saves the session data for a maximum time (such as >> 24 hours), even up to a certain time if the web browser crashes for >> example? > > In general a database doesn’t change just because time has passed. You will need to setup something external to the database schema if you want time to cause change. David J.
Re: database design with temporary tables
On Sunday, August 29, 2021, ourdiaspora wrote: > > > Yes, wanted to know relevant parts because often the first problem is to > know which part of the (extensive) documentation to read... > > Suggest you just start developing. When you get stuck the nature of the block should inform where to go looking for answers. Or at least formulate more specific questions. David J.
Re: database design with temporary tables
On 29/08/2021 17:36, ourdiaspora wrote: ‐‐‐ Original Message ‐‐‐ On Sunday, August 29th, 2021 at 5:24 PM, Adrian Klaver wrote: Presumably not. Temporary tables only live at most for the length of a session. It would be a really bad idea to hold sessions open for 24 hours. Is there an alternative scenario, such as the user is able to create a new table with saves the session data for a maximum time (such as 24 hours), even up to a certain time if the web browser crashes for example? I'd save a timestamp with the session data, and then run a cron job which deletes sessions older than whatever lifetime you want. Ray. -- Raymond O'Donnell // Galway // Ireland r...@rodonnell.ie
Re: database design with temporary tables
‐‐‐ Original Message ‐‐‐ On Sunday, August 29th, 2021 at 5:30 PM, Adrian Klaver wrote: > On 8/29/21 9:24 AM, Adrian Klaver wrote: > > > Whoops, unfinished thought. What I was going to ask is: > > The above is not clear to me. Are you asking about the Postgres > > documentation? > Yes, wanted to know relevant parts because often the first problem is to know which part of the (extensive) documentation to read...
Re: database design with temporary tables
‐‐‐ Original Message ‐‐‐ On Sunday, August 29th, 2021 at 5:24 PM, Adrian Klaver wrote: > > Presumably not. Temporary tables only live at most for the length of a > > session. It would be a really bad idea to hold sessions open for 24 > > hours. Is there an alternative scenario, such as the user is able to create a new table with saves the session data for a maximum time (such as 24 hours), even up to a certain time if the web browser crashes for example? > > The abo > Incomplete response?
Re: database design with temporary tables
On 8/29/21 9:24 AM, Adrian Klaver wrote: On 8/29/21 9:10 AM, ourdiaspora wrote: Readers, User selects data (read-only) from extant tables; Web server combines user data with read-only data to produce content visible to user as html and/or pdf document; User does not need to sign in to use web page, only the user e-mail address; User data deleted (including e-mail address) after time (e.g. 24 hours) and/or user selects to receive combined data via e-mail (after which all user data is automatically deleted). What part of the documention would be most relevant to read firstly, please? The abo Whoops, unfinished thought. What I was going to ask is: The above is not clear to me. Are you asking about the Postgres documentation? -- Adrian Klaver adrian.kla...@aklaver.com
Re: database design with temporary tables
On 8/29/21 9:10 AM, ourdiaspora wrote: Readers, Some advice would be appreciated about appropriate tables to store temporary data. Scenario: User copies csv file of user data, presumably into some temporary table(s); Presumably not. Temporary tables only live at most for the length of a session. It would be a really bad idea to hold sessions open for 24 hours. That is assuming nothing else causes the session to drop and the data to be lost. User selects data (read-only) from extant tables; Web server combines user data with read-only data to produce content visible to user as html and/or pdf document; User does not need to sign in to use web page, only the user e-mail address; User data deleted (including e-mail address) after time (e.g. 24 hours) and/or user selects to receive combined data via e-mail (after which all user data is automatically deleted). What part of the documention would be most relevant to read firstly, please? The abo -- Adrian Klaver adrian.kla...@aklaver.com
