Re: Insert records in the tavke only if they are not exist - Mailing list pgsql-general

On 12/7/24 12:17, Igor Korot wrote:
> Hi, Adrian,
> 
> On Sat, Dec 7, 2024 at 12:32 PM Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.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.klaver@aklaver.com