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

Hi, Adrian,

On Sat, Dec 7, 2024 at 5:07 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> 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.

Good..

>
> >
> > 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?

I am making the tables and naming them with a specific pattern.

Now I'm curious - if I start psql and will want to create a table
named pg_am, what will happen?
I presume psql will produce error saying that the system table with
that name exists and the user can't create ome.
Am I right?

>
> > 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?

My tables are named with the specific pattern.

Also - see above.

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

Again - not sure what you mean here....

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

If one of my tables  will be dropped - it will be re-created.
I'm using CREATE TABLE IF NOT EXIST.

>
>
> > All records that were there are staying unchanged.
>
> INSERT/UPDATE against your tables is not a possibility?

It is..
All I;m saying that when the app starts-up, this is done automatically

And if the user decides to insert some data - that's on him

>
> > 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?

Correct.

>
> 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?

I don't.
Think about MS ACCESS-like applications.
ACCESS creates the internal tables to keep track of some
internals.
Is there a possibility of a clash with the user table? Ofc there is.
But MS is still doing it nevertheless, because chances of this occurring
are really slim.
And if a user acquire access to those internal tables and start modify
them and in the process screw something up, well he uses MS product
as a developer and so should know better as a developer
And if MS can do it - why can't I?


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

What is considered correct outcome is strongly on user if we are talking
about my app.

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

But not to me.
My application targets developers, not end users.

Thank you.

>
> >
> >>
> >>
> >>>
> >>> Thank you.
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>