Re: Insert records in the tavke only if they are not exist

On 12/7/24 12:17, Igor Korot wrote:
> Hi, Adrian,
>
> On Sat, Dec 7, 2024 at 12:32 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>>
>> On 12/7/24 09:59, Igor Korot wrote:
>>> Hi, Adrian,
>>>
>>> On Sat, Dec 7, 2024 at 11:18 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>>>>
>>>> On 12/6/24 19:36, Igor Korot wrote:
>>>>> Hi, All,
>>>>>
>>>>> When my application starts up, ot creates some tables and insert records
>>>>> in them.
>>>>>
>>>>> When the app starts for the second time it should check if the tables
>>>>> and the records in them are exist and skip the process.
>>>>
>>>> Does this also apply to starts after the second time?
>>>
>>> Yes, it does.
>>>
>>> However, if the new table is created in the meantime - the new record
>>> should be created, because it is not there yet.
>>
>> What determines that a new table needs to be created and populated?
>
> Consider the following scenario:

I did and it made me start twitching.

>
> 1. Program is installed.
> 2. Program is started for the first time.
> 3. My tables are created and populated

What distinguishes your tables from other users tables?

> 4. Program is executed.
> 5. User closes the program.
> 6.. Later on the user decides that there is a need
> for another table (inside psql or any other client)

The above is where I started twitching.

How do you keep them out of your tables?

How do you get these changes to play nice with the existing structure?

> 7. Then the program starts for the second time.
>
> At this time all my tables that were created will stay (courtesy
> of CREATE TABLE IF NOT EXIST).

You are depending on folks not knowing about DROP TABLE and/or you
having thought out the permissions for access thoroughly.

> All records that were there are staying unchanged.

INSERT/UPDATE against your tables is not a possibility?

> However, for the table that was made in between the runs
> will be added
>
> Now if the program is installed on 2 different machines
> and started simultaneously on both - I want to ensure that
> only 1 set of tables is made and only 1 set of records in them
> is available

I assume this means they are both pointing at the same instance of a
database?

This is the part that confuses me.

If you are going to allow ad hoc and at will changes how do you know
what is actually the correct change?

>
> Now, the creation/population is done inside a transaction.

I'm not sure that a transaction is going to solve the issue I raised
above, it will just make one thing happen with no guarantee that it is
the correct outcome.

>> Are you talking about the front end that the user launches or the
>> backend that runs the database or something else?
>
> Front-end.
> This is a C++ app.

To me this is the tail wagging the dog. The thought of allowing users to
change the database structure and you dealing with it after the fact is
just disturbing to me.

>
>>
>>
>>>
>>> Thank you.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com