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.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 maximum time (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) documentation to 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 (no password)?

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 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.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) 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.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