Обсуждение: database design with temporary tables
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); 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 viae-mail (after which all user data is automatically deleted). What part of the documention would be most relevant to read firstly, please?
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 viae-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.klaver@aklaver.com
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.klaver@aklaver.com
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Sunday, August 29th, 2021 at 5:24 PM, Adrian Klaver <adrian.klaver@aklaver.com> 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 maximumtime (such as 24 hours), even up to a certain time if the web browser crashes for example? > > The abo > Incomplete response?
On 29/08/2021 17:36, ourdiaspora wrote: > ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ > > On Sunday, August 29th, 2021 at 5:24 PM, Adrian Klaver > <adrian.klaver@aklaver.com> 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 ray@rodonnell.ie
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Sunday, August 29th, 2021 at 5:30 PM, Adrian Klaver <adrian.klaver@aklaver.com> 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) documentationto read...
On Sunday, August 29, 2021, ourdiaspora <ourdiaspora@protonmail.com> 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.
On Sunday, August 29, 2021, Ray O'Donnell <ray@rodonnell.ie> 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.
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Sunday, August 29th, 2021 at 5:38 PM, Ray O'Donnell <ray@rodonnell.ie> 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 (nopassword)?
On 8/29/21 9:36 AM, ourdiaspora wrote: > ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ > > On Sunday, August 29th, 2021 at 5:24 PM, Adrian Klaver <adrian.klaver@aklaver.com> 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 maximumtime (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.klaver@aklaver.com
On 8/29/21 9:38 AM, ourdiaspora wrote: > > ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ > > On Sunday, August 29th, 2021 at 5:30 PM, Adrian Klaver <adrian.klaver@aklaver.com> 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) documentationto 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.klaver@aklaver.com
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 >> <adrian.klaver@aklaver.com> 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 ray@rodonnell.ie
On Sunday, August 29th, 2021 at 5:56 PM, Ray O'Donnell <ray@rodonnell.ie> 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.
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
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.klaver@aklaver.com
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