Re: how to best remove version 10 (and keep version 9.5)

[Michael Paquier]

On Sat, Mar 13, 2021 at 12:03:04PM -0800, Adrian Klaver wrote:
> So, the 10 instance is not running and the 9.5 instance is listening on the
> default port. At this point I would leave things as they are.

Robert, you may want to know that 9.5 has been EOL'd by community.
Just saying.
--
Michael


signature.asc
Description: PGP signature

Re: Compare with default value?

[Ulrich Goebel]

Hi,

Am 13.03.21 um 22:21 schrieb Tim Cross:


Ulrich Goebel  writes:


Hi,

o.k. I have to give some more information...

Am 13.03.21 um 19:39 schrieb David G. Johnston:

On Saturday, March 13, 2021, Ulrich Goebel mailto:m...@fam-goebel.de>> wrote:

 I would like to get the rows, where a column has the default value,
 similar to:
 select id fromt tbl where col = default

If the default is a simple constant then why go through the trouble instead of
just writing col = ‘constant’ ?




I think you may need to re-think your design or at least come at it from
a different perspective. As shown by another post in the thread, at some
level, this is 'sort of' possible, but it will be ugly and fragile.


Yes, I am re-thinking allredy...



Possibly one of the issues you have is on one hand, you want the
solution to be as generic as possible, but on the other, you require it
to have specific knowledge about the definition of your table. i.e.
which column(s) have a default value. Furthermore, your 'completion'
table will need to be defined based on this information i.e. potentially
multiple columns with multiple different data types etc.

While the additional information you provided does give some increased
understanding of what your trying to do, it is still describing your
desired solution implementation. It might be better if you provide more
high level details of what your attempting to do so that we can better
understand how you arrived at the proposed solution and whether there
may be better alternatives available.


o.k.: For a conference I have a tbl_person which holds all peoble which 
are involved: participants and people which provide private lodgins for 
other participants. (There are much more roles, but for illustration 
these two should be enough.) Of course each person can have one or more 
roles at the conference, a n-n-relation models that. Now our workflow 
allows that one person find it way in the tbl_person twice (ore even 
more often): for example the conference office generates a row for Tom, 
because he provides private lodgin. Later on Tom decides to participate 
an fills the online registration formular. These data generate the 
second row for Tom. Both rows hold significant information which the 
other doesn't hold. Let's say the online register gave the birthday, the 
other holds information about the lodgin (bed with or without 
breakfast). The next step then is that the conference office get notice 
of the doubled person an should make one row out of the existing two 
rows. The office decide which of the two rows should be completed with 
data from the other row. Therefore I would like to pick the columns in 
the first row where we have default values and replace it by the value 
from the second row.


There are more ways to end with two or even more rows per person. May be 
it would have been much better to avoid these possibilities. But for the 
moment I have a given database structure running in productive mode, so 
it is not easy to re-structure the structure or even the workflows...


Thanks for patience reading all that!

--
Ulrich Goebel
Am Büchel 57, 53173 Bonn

Re: Compare with default value?

[Adrian Klaver]

On 3/14/21 6:30 AM, Ulrich Goebel wrote:

Hi,

Am 13.03.21 um 22:21 schrieb Tim Cross:





I think you may need to re-think your design or at least come at it from
a different perspective. As shown by another post in the thread, at some
level, this is 'sort of' possible, but it will be ugly and fragile.


Yes, I am re-thinking allredy...







o.k.: For a conference I have a tbl_person which holds all peoble which 
are involved: participants and people which provide private lodgins for 
other participants. (There are much more roles, but for illustration 
these two should be enough.) Of course each person can have one or more 
roles at the conference, a n-n-relation models that. Now our workflow 
allows that one person find it way in the tbl_person twice (ore even 
more often): for example the conference office generates a row for Tom, 
because he provides private lodgin. Later on Tom decides to participate 
an fills the online registration formular. These data generate the 
second row for Tom. Both rows hold significant information which the 
other doesn't hold. Let's say the online register gave the birthday, the 
other holds information about the lodgin (bed with or without 
breakfast). The next step then is that the conference office get notice 
of the doubled person an should make one row out of the existing two 
rows. The office decide which of the two rows should be completed with 
data from the other row. Therefore I would like to pick the columns in 
the first row where we have default values and replace it by the value 
from the second row.


This is just part of a bigger issue, which value to believe:

col_1   col_2   col_3 col_4
row 1   default some_valdefault   some_val
row 2   other_val   default default   other_val

How do you arrive at assumption that row 2(or any other row) has 
precedence over row 1?




There are more ways to end with two or even more rows per person. May be 
it would have been much better to avoid these possibilities. But for the 
moment I have a given database structure running in productive mode, so 
it is not easy to re-structure the structure or even the workflows...


Thanks for patience reading all that!




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Compare with default value?

[Ulrich Goebel]

Am 14.03.21 um 18:13 schrieb Adrian Klaver:

On 3/14/21 6:30 AM, Ulrich Goebel wrote:

Hi,

Am 13.03.21 um 22:21 schrieb Tim Cross:





I think you may need to re-think your design or at least come at it from
a different perspective. As shown by another post in the thread, at some
level, this is 'sort of' possible, but it will be ugly and fragile.


Yes, I am re-thinking allredy...







o.k.: For a conference I have a tbl_person which holds all peoble 
which are involved: participants and people which provide private 
lodgins for other participants. (There are much more roles, but for 
illustration these two should be enough.) Of course each person can 
have one or more roles at the conference, a n-n-relation models that. 
Now our workflow allows that one person find it way in the tbl_person 
twice (ore even more often): for example the conference office 
generates a row for Tom, because he provides private lodgin. Later on 
Tom decides to participate an fills the online registration formular. 
These data generate the second row for Tom. Both rows hold significant 
information which the other doesn't hold. Let's say the online 
register gave the birthday, the other holds information about the 
lodgin (bed with or without breakfast). The next step then is that the 
conference office get notice of the doubled person an should make one 
row out of the existing two rows. The office decide which of the two 
rows should be completed with data from the other row. Therefore I 
would like to pick the columns in the first row where we have default 
values and replace it by the value from the second row.


This is just part of a bigger issue, which value to believe:

 col_1 col_2  col_3  col_4
row 1    default  some_val    default  some_val
row 2    other_val   default default   other_val

How do you arrive at assumption that row 2(or any other row) has 
precedence over row 1?


That goes in two steps:
1. The conference office will decide, which of the two rows will be
   completed by the other
2. My function will do the job, but only for the columns, where it would
   overwrite the default value.

By the way: the discussion here brought me to give up the completely 
automated (generic) way. Now in my python code for step 2. I go through 
all of the columns and decide for each of them what to do:

- either to overwrite a default value (which I define in python rather
  then take it from the database)
- or put the values together (for examples in the case of "remarks",
  which could be significant in both rows
- or even other handling...

Thank You all for creative thinking on my issue!





There are more ways to end with two or even more rows per person. May 
be it would have been much better to avoid these possibilities. But 
for the moment I have a given database structure running in productive 
mode, so it is not easy to re-structure the structure or even the 
workflows...


Thanks for patience reading all that!






--
Ulrich Goebel
Am Büchel 57, 53173 Bonn

Re: Compare with default value?

[Tim Cross]

Ulrich Goebel  writes:

> Hi,
>
> Am 13.03.21 um 22:21 schrieb Tim Cross:
>> Ulrich Goebel  writes:
>>
>>> Hi,
>>>
>>> o.k. I have to give some more information...
>>>
>>> Am 13.03.21 um 19:39 schrieb David G. Johnston:
 On Saturday, March 13, 2021, Ulrich Goebel >>> > wrote:

  I would like to get the rows, where a column has the default value,
  similar to:
  select id fromt tbl where col = default

 If the default is a simple constant then why go through the trouble 
 instead of
 just writing col = ‘constant’ ?
>>>
>> I think you may need to re-think your design or at least come at it from
>> a different perspective. As shown by another post in the thread, at some
>> level, this is 'sort of' possible, but it will be ugly and fragile.
>
> Yes, I am re-thinking allredy...
>
>> Possibly one of the issues you have is on one hand, you want the
>> solution to be as generic as possible, but on the other, you require it
>> to have specific knowledge about the definition of your table. i.e.
>> which column(s) have a default value. Furthermore, your 'completion'
>> table will need to be defined based on this information i.e. potentially
>> multiple columns with multiple different data types etc.
>> While the additional information you provided does give some increased
>> understanding of what your trying to do, it is still describing your
>> desired solution implementation. It might be better if you provide more
>> high level details of what your attempting to do so that we can better
>> understand how you arrived at the proposed solution and whether there
>> may be better alternatives available.
>
> o.k.: For a conference I have a tbl_person which holds all peoble which are
> involved: participants and people which provide private lodgins for other
> participants. (There are much more roles, but for illustration these two 
> should
> be enough.) Of course each person can have one or more roles at the 
> conference,
> a n-n-relation models that. Now our workflow allows that one person find it 
> way
> in the tbl_person twice (ore even more often): for example the conference 
> office
> generates a row for Tom, because he provides private lodgin. Later on Tom
> decides to participate an fills the online registration formular. These data
> generate the second row for Tom. Both rows hold significant information which
> the other doesn't hold. Let's say the online register gave the birthday, the
> other holds information about the lodgin (bed with or without breakfast). The
> next step then is that the conference office get notice of the doubled person 
> an
> should make one row out of the existing two rows. The office decide which of 
> the
> two rows should be completed with data from the other row. Therefore I would
> like to pick the columns in the first row where we have default values and
> replace it by the value from the second row.
>
> There are more ways to end with two or even more rows per person. May be it
> would have been much better to avoid these possibilities. But for the moment I
> have a given database structure running in productive mode, so it is not easy 
> to
> re-structure the structure or even the workflows...
>


I really hate to say this, but I think you have a major fundamental flaw
in your database design. As soon as I read

> Now our workflow allows that one person find it way
> in the tbl_person twice (ore even more often): for example the conference 
> office
> generates a row for Tom, because he provides private lodgin. Later on Tom
> decides to participate an fills the online registration formular. These data
> generate the second row for Tom. Both rows hold significant
> information

I thought - "oh dear, this is going to be a problem".

Based on this and your previous post, I suspect your now beginning to
run into maintenance problems with your application database and are
trying to find ways to deal with the issues your encountering. The good
news, it can be fixed. The bad news, it will take a fair amount of work.
The really really bad news is while you may be able to work around some
of the issues, things are just going to snowball and get worse,
requiring increasing amounts of maintenance and increasing amounts of
effort to fix.

Although you have said there is an existing structure in production and
changing it will not be easy, bottom line is that it really is your only
sustainable course of action. Anything else you do will at best delay
the inevitable and at worst could result in far worse consequences (such
as major data loss or curruption resulting from efforts to maintain a
poor data model design). What you need to do is re-examine your data
model e.g. tables, columns, views etc and make sure you are at least
meeting 3rd normal form
(https://www.guru99.com/database-normalization.html).

This is really your only sane solution. There are things you can do with
views and database functions whi