Feedback-ID: i76984098:Fastmail
Message-ID: <d10fa09d-cd72-4fca-b5fe-b1f0accc6872@aklaver.com>
Date: Sat, 7 Dec 2024 15:07:22 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Insert records in the tavke only if they are not exist
To: Igor Korot <ikorot01@gmail.com>,
 "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <CA+FnnTwcNNGsM_ck0ye1OsUN3Jjg94PzGMKRm--2ojJM_zVOMA@mail.gmail.com>
 <8770b693-0c44-40de-b883-36cc5e718a0a@aklaver.com>
 <CA+FnnTy7w3jaZCOuMGMMNGySfiTjcHh5D06AUMc+w+i6yoKrMA@mail.gmail.com>
 <acfaa89e-fd23-4318-b946-645e6e0b1749@aklaver.com>
 <CA+FnnTycFrDX4YRXAaqkijv5dLGCStuDV_nZaBQOe_CHcUKTag@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CA+FnnTycFrDX4YRXAaqkijv5dLGCStuDV_nZaBQOe_CHcUKTag@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/d10fa09d-cd72-4fca-b5fe-b1f0accc6872%40aklaver.com>
Precedence: bulk

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