Re: Indexes mysteriously change to ON ONLY

[Ron]

I cheat by using sed to remove "ONLY ON " from the CREATE statements.

On 1/27/23 15:30, Rumpi Gravenstein wrote:

Tom/Christophe  I now understand.  Thanks for the clear explanation.

On Fri, Jan 27, 2023 at 4:16 PM Tom Lane  wrote:

Rumpi Gravenstein  writes:
> We are using the pg_indexes view (indexdef) to retrieve the index
> definition.

Ah.

> Are you saying that as a normal part of building an index, there are
short
> periods of time where the pg_indexes view will show the index with
ON ONLY
> specified?

No, there's no "short periods", this is what it shows.  That's partly
because the output is designed for pg_dump to use.  But there's
a reasonably good argument for it anyway, which is that if you just
say "create index" then that's effectively a macro for building the
whole partitioned index set.  That pg_indexes entry is only about the
top-level "virtual" index, and there are other entries for the leaf
indexes.  For example,

regression=# create table foo (f1 int primary key) partition by list (f1);
CREATE TABLE
regression=# create table foo_1 partition of foo for values in (1);
CREATE TABLE
regression=# create table foo_2 partition of foo for values in (2);
CREATE TABLE
regression=# select tablename,indexname,indexdef from pg_indexes where
indexname like 'foo%';
 tablename | indexname  |  indexdef

---++--
 foo       | foo_pkey   | CREATE UNIQUE INDEX foo_pkey ON ONLY
public.foo USING btree (f1)
 foo_1     | foo_1_pkey | CREATE UNIQUE INDEX foo_1_pkey ON
public.foo_1 USING btree (f1)
 foo_2     | foo_2_pkey | CREATE UNIQUE INDEX foo_2_pkey ON
public.foo_2 USING btree (f1)
(3 rows)

If you wanted to reconstruct this from individual parts, as pg_dump does,
you'd issue those commands and then connect them together with ATTACH
PARTITION commands.

                        regards, tom lane



--
Rumpi Gravenstein


--
Born in Arizona, moved to Babylonia.

Re: nextval per counted

[Rob Sargent]

On 1/27/23 14:31, David G. Johnston wrote:

On Fri, Jan 27, 2023 at 2:25 PM Rob Sargent  wrote:

On 1/27/23 14:20, David G. Johnston wrote:

On Fri, Jan 27, 2023 at 1:59 PM Rob Sargent
 wrote:

I'm trying to craft SQL to invoke a sequence nextval once per
grouped value.


This seems like a very unusual usage of nextval/sequences...

with cleanup as (
select DISTINCT e.ma , coalesce(e.pa ,
'fix') as pa from ...
), compute as (
select ma, pa, nextval(...) from cleanup
)
select * from compute ... -- do whatever else you want

So far I have this:

with husb as(
select e.ma , count(distinct coalesce(e.pa
, nextval('egogen')::text)) as mates
from emp_all_by3 e group by e.ma  order by mates
)
select mates, count(*)
from husb
group by mates order by mates desc;

Your "order by mates" in the CTE is totally pointless and wasting
resources.

David J.


Yeah, it wasn't when that was the last bit executed...

Seems asking a sequence for an id isn't too unusual?  Or are they
specifically intended for primary keys?


Yes, their design is excellent for surrogate primary keys.  I don't 
even know what to call what you are doing but it isn't that.  It seems 
like some form of counting but stuff like "row_number" and "count" 
perform that function.  I don't think I'd trust using them as a 
counter...for that I'd do counting in the query then have a separate 
aspect, in user-space, not the system catalogs, that increments a counter.


David J.

Yeah, in this case I'm actually trying to assign an id in place of a 
null, but only one id for all null for one ma.  One thought was to 
simply use the "ma" value but the table constraints are such that that 
cannot be.


TL/DR
This is pedigree  ego/ma/pa triplet work.  Set of ego with a mother and 
missing father can be considered a sibship (one mother/father pair) or a 
"half-sibship" where each child get assigned a unique father (one 
mother/N fathers).  In that latter case, the sequence works perfectly.  
We don't allow for mother/mother, the analysis tools are not up for that 
(yet).



Thanks for your time,
rjs

Re: nextval per counted

[David G. Johnston]

On Fri, Jan 27, 2023 at 2:25 PM Rob Sargent  wrote:

> On 1/27/23 14:20, David G. Johnston wrote:
>
> On Fri, Jan 27, 2023 at 1:59 PM Rob Sargent  wrote:
>
>> I'm trying to craft SQL to invoke a sequence nextval once per grouped
>> value.
>>
>>
> This seems like a very unusual usage of nextval/sequences...
>
> with cleanup as (
>   select DISTINCT e.ma, coalesce(e.pa, 'fix') as pa from ...
> ), compute as (
>   select ma, pa, nextval(...) from cleanup
> )
> select * from compute ... -- do whatever else you want
>
> So far I have this:
>>
>> with husb as(
>> select e.ma, count(distinct coalesce(e.pa, nextval('egogen')::text)) as
>> mates
>> from emp_all_by3 e group by e.ma order by mates
>> )
>> select mates, count(*)
>> from husb
>> group by mates order by mates desc;
>>
>> Your "order by mates" in the CTE is totally pointless and wasting
> resources.
>
> David J.
>
> Yeah, it wasn't when that was the last bit executed...
>
> Seems asking a sequence for an id isn't too unusual?  Or are they
> specifically intended for primary keys?
>
>
Yes, their design is excellent for surrogate primary keys.  I don't even
know what to call what you are doing but it isn't that.  It seems like some
form of counting but stuff like "row_number" and "count" perform that
function.  I don't think I'd trust using them as a counter...for that I'd
do counting in the query then have a separate aspect, in user-space, not
the system catalogs, that increments a counter.

David J.

Re: Indexes mysteriously change to ON ONLY

[Rumpi Gravenstein]

Tom/Christophe  I now understand.  Thanks for the clear explanation.

On Fri, Jan 27, 2023 at 4:16 PM Tom Lane  wrote:

> Rumpi Gravenstein  writes:
> > We are using the pg_indexes view (indexdef) to retrieve the index
> > definition.
>
> Ah.
>
> > Are you saying that as a normal part of building an index, there are
> short
> > periods of time where the pg_indexes view will show the index with ON
> ONLY
> > specified?
>
> No, there's no "short periods", this is what it shows.  That's partly
> because the output is designed for pg_dump to use.  But there's
> a reasonably good argument for it anyway, which is that if you just
> say "create index" then that's effectively a macro for building the
> whole partitioned index set.  That pg_indexes entry is only about the
> top-level "virtual" index, and there are other entries for the leaf
> indexes.  For example,
>
> regression=# create table foo (f1 int primary key) partition by list (f1);
> CREATE TABLE
> regression=# create table foo_1 partition of foo for values in (1);
> CREATE TABLE
> regression=# create table foo_2 partition of foo for values in (2);
> CREATE TABLE
> regression=# select tablename,indexname,indexdef from pg_indexes where
> indexname like 'foo%';
>  tablename | indexname  | indexdef
>
>
> ---++--
>  foo   | foo_pkey   | CREATE UNIQUE INDEX foo_pkey ON ONLY public.foo
> USING btree (f1)
>  foo_1 | foo_1_pkey | CREATE UNIQUE INDEX foo_1_pkey ON public.foo_1
> USING btree (f1)
>  foo_2 | foo_2_pkey | CREATE UNIQUE INDEX foo_2_pkey ON public.foo_2
> USING btree (f1)
> (3 rows)
>
> If you wanted to reconstruct this from individual parts, as pg_dump does,
> you'd issue those commands and then connect them together with ATTACH
> PARTITION commands.
>
> regards, tom lane
>


-- 
Rumpi Gravenstein

Re: nextval per counted

[Rob Sargent]

On 1/27/23 14:20, David G. Johnston wrote:

On Fri, Jan 27, 2023 at 1:59 PM Rob Sargent  wrote:

I'm trying to craft SQL to invoke a sequence nextval once per
grouped value.


This seems like a very unusual usage of nextval/sequences...

with cleanup as (
  select DISTINCT e.ma , coalesce(e.pa , 
'fix') as pa from ...

), compute as (
  select ma, pa, nextval(...) from cleanup
)
select * from compute ... -- do whatever else you want

So far I have this:

with husb as(
select e.ma , count(distinct coalesce(e.pa
, nextval('egogen')::text)) as mates
from emp_all_by3 e group by e.ma  order by mates
)
select mates, count(*)
from husb
group by mates order by mates desc;

Your "order by mates" in the CTE is totally pointless and wasting 
resources.


David J.


Yeah, it wasn't when that was the last bit executed...

Seems asking a sequence for an id isn't too unusual?  Or are they 
specifically intended for primary keys?

Re: nextval per counted

[David G. Johnston]

On Fri, Jan 27, 2023 at 1:59 PM Rob Sargent  wrote:

> I'm trying to craft SQL to invoke a sequence nextval once per grouped
> value.
>
>
This seems like a very unusual usage of nextval/sequences...

with cleanup as (
  select DISTINCT e.ma, coalesce(e.pa, 'fix') as pa from ...
), compute as (
  select ma, pa, nextval(...) from cleanup
)
select * from compute ... -- do whatever else you want

So far I have this:
>
> with husb as(
> select e.ma, count(distinct coalesce(e.pa, nextval('egogen')::text)) as
> mates
> from emp_all_by3 e group by e.ma order by mates
> )
> select mates, count(*)
> from husb
> group by mates order by mates desc;
>
> Your "order by mates" in the CTE is totally pointless and wasting
resources.

David J.

Re: Indexes mysteriously change to ON ONLY

[Tom Lane]

Rumpi Gravenstein  writes:
> We are using the pg_indexes view (indexdef) to retrieve the index
> definition.

Ah.

> Are you saying that as a normal part of building an index, there are short
> periods of time where the pg_indexes view will show the index with ON ONLY
> specified?

No, there's no "short periods", this is what it shows.  That's partly
because the output is designed for pg_dump to use.  But there's
a reasonably good argument for it anyway, which is that if you just
say "create index" then that's effectively a macro for building the
whole partitioned index set.  That pg_indexes entry is only about the
top-level "virtual" index, and there are other entries for the leaf
indexes.  For example,

regression=# create table foo (f1 int primary key) partition by list (f1);
CREATE TABLE
regression=# create table foo_1 partition of foo for values in (1);
CREATE TABLE
regression=# create table foo_2 partition of foo for values in (2);
CREATE TABLE
regression=# select tablename,indexname,indexdef from pg_indexes where 
indexname like 'foo%';
 tablename | indexname  | indexdef  
   
---++--
 foo   | foo_pkey   | CREATE UNIQUE INDEX foo_pkey ON ONLY public.foo USING 
btree (f1)
 foo_1 | foo_1_pkey | CREATE UNIQUE INDEX foo_1_pkey ON public.foo_1 USING 
btree (f1)
 foo_2 | foo_2_pkey | CREATE UNIQUE INDEX foo_2_pkey ON public.foo_2 USING 
btree (f1)
(3 rows)

If you wanted to reconstruct this from individual parts, as pg_dump does,
you'd issue those commands and then connect them together with ATTACH
PARTITION commands.

regards, tom lane

Re: Indexes mysteriously change to ON ONLY

[Christophe Pettus]

> On Jan 27, 2023, at 13:01, Rumpi Gravenstein  wrote:
> 
> We are using the pg_indexes view (indexdef) to retrieve the index definition.

This is as expected.  Once the index is created on the partitioned set of 
tables, the index on the *root* table will be ON ONLY that table; the child 
tables appear separately:

xof=# create table t (i bigint) partition by range(i);
CREATE TABLE
xof=# create table t001 partition of t for values from (1) to (2); 
CREATE TABLE
xof=# create index on t(i);
CREATE INDEX
xof=# select * from pg_indexes where tablename = 't';
 schemaname | tablename | indexname | tablespace |   
indexdef
+---+---++---
 public | t | t_i_idx   || CREATE INDEX t_i_idx ON ONLY 
public.t USING btree (i)
(1 row)

xof=# select * from pg_indexes where tablename = 't001';
 schemaname | tablename | indexname  | tablespace |
indexdef
+---+++
 public | t001  | t001_i_idx || CREATE INDEX t001_i_idx ON 
public.t001 USING btree (i)
(1 row)

Re: Indexes mysteriously change to ON ONLY

[Rumpi Gravenstein]

We are using the pg_indexes view (indexdef) to retrieve the index
definition.

Are you saying that as a normal part of building an index, there are short
periods of time where the pg_indexes view will show the index with ON ONLY
specified?

On Fri, Jan 27, 2023 at 3:53 PM Tom Lane  wrote:

> Rumpi Gravenstein  writes:
> >> We have recently discovered that on some of our partitioned tables
> indexes
> >> that were created as:
> >>
> >> CREATE UNIQUE INDEX chapter_u01 USING btree (dur_uk, catalog_id)
> >>
> >> somehow changed to include the ON ONLY option:
> >>
> >> CREATE UNIQUE INDEX chapter_u01 *ON ONLY *chapter USING btree (dur_uk,
> >> catalog_id)
>
> What do you mean "somehow changed"?  There is nothing in the system
> catalogs that stores that exact string, so I suppose what you mean
> is that some tool is presenting the indexes to you that way.
>
> If that tool is pg_dump, this is its normal behavior.  There will
> be other commands in its output that build the rest of the
> partitioned index set.
>
> regards, tom lane
>


-- 
Rumpi Gravenstein

nextval per counted

[Rob Sargent]

I'm trying to craft SQL to invoke a sequence nextval once per grouped value.

So far I have this:

   with husb as(
   select e.ma, count(distinct coalesce(e.pa, nextval('egogen')::text))
   as mates
   from emp_all_by3 e group by e.ma order by mates
   )
   select mates, count(*)
   from husb
   group by mates order by mates desc;

which works nicely but it "ids" each null separately.

The following lets me count the "fixes" as a mate

   with husb as(
  select e.ma, count(distinct coalesce(e.pa,
   nextval('egogen')::text)) mates
  from emp_all_by3 e
  where ma is not null
  group by e.ma order by mates
   )
   select mates, count(*) from husb group by mates order by mates desc;

   with husb as(
  select e.ma, coalesce(e.pa,'fix') as pa
  from emp_all_by3 e
  where e.ma is not null
   ),
   fixed as (
  select e.ma, count(distinct e.pa) mates
  from husb e group by e.ma order by mates
   )
   select mates, count(*) from fixed group by mates order by mates desc;

but I would love to able to assign a single "nextval"  to those fixes.

Any pointers appreciated.

Re: Indexes mysteriously change to ON ONLY

[Tom Lane]

Rumpi Gravenstein  writes:
>> We have recently discovered that on some of our partitioned tables indexes
>> that were created as:
>> 
>> CREATE UNIQUE INDEX chapter_u01 USING btree (dur_uk, catalog_id)
>> 
>> somehow changed to include the ON ONLY option:
>> 
>> CREATE UNIQUE INDEX chapter_u01 *ON ONLY *chapter USING btree (dur_uk,
>> catalog_id)

What do you mean "somehow changed"?  There is nothing in the system
catalogs that stores that exact string, so I suppose what you mean
is that some tool is presenting the indexes to you that way.

If that tool is pg_dump, this is its normal behavior.  There will
be other commands in its output that build the rest of the
partitioned index set.

regards, tom lane

Re: Indexes mysteriously change to LOG

[Rumpi Gravenstein]

> CREATE UNIQUE INDEX chapter_u01 USING btree (dur_uk, catalog_id)

How did you do the above without the table name?

That's a cut/paste error.  The original index create is:

create unique index chapter_u01 on chapter (dur_uk,catalog_id);

On Fri, Jan 27, 2023 at 3:34 PM Adrian Klaver 
wrote:

> On 1/27/23 12:23, Rumpi Gravenstein wrote:
> > We are on PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
> > 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bitPostgreSQL 14.5 on
> > x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat
> > 8.5.0-10), 64-bit
> >
> > We have recently discovered that on some of our partitioned tables
> > indexes that were created as:
> >
> > CREATE UNIQUE INDEX chapter_u01 USING btree (dur_uk, catalog_id)
>
> How did you do the above without the table name?
>
> >
> > somehow changed to include the ON ONLY option:
> >
> > CREATE UNIQUE INDEX chapter_u01 *ON ONLY *chapter USING btree (dur_uk,
> > catalog_id)
> >
> > There is no SQL issued that explicitly requests this "ON ONLY" option.
> > I am wondering if this is a side-effect of some other activity.
> > Googling and looking through documentation haven't helped.
> >
> > Does anyone have any thoughts on how this might happen?
> >
> > --
> > Rumpi Gravenstein
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

-- 
Rumpi Gravenstein

Re: Indexes mysteriously change to LOG

[Adrian Klaver]

On 1/27/23 12:23, Rumpi Gravenstein wrote:
We are on PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 
8.5.0 20210514 (Red Hat 8.5.0-10), 64-bitPostgreSQL 14.5 on 
x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 
8.5.0-10), 64-bit


We have recently discovered that on some of our partitioned tables 
indexes that were created as:


CREATE UNIQUE INDEX chapter_u01 USING btree (dur_uk, catalog_id)


How did you do the above without the table name?



somehow changed to include the ON ONLY option:

CREATE UNIQUE INDEX chapter_u01 *ON ONLY *chapter USING btree (dur_uk, 
catalog_id)


There is no SQL issued that explicitly requests this "ON ONLY" option.  
I am wondering if this is a side-effect of some other activity.  
Googling and looking through documentation haven't helped.


Does anyone have any thoughts on how this might happen?

--
Rumpi Gravenstein


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

Re: Indexes mysteriously change to ON ONLY

[Rumpi Gravenstein]

Whoops ... fixed the subject line.

On Fri, Jan 27, 2023 at 3:23 PM Rumpi Gravenstein 
wrote:

> We are on PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
> 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bitPostgreSQL 14.5 on
> x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat
> 8.5.0-10), 64-bit
>
> We have recently discovered that on some of our partitioned tables indexes
> that were created as:
>
> CREATE UNIQUE INDEX chapter_u01 USING btree (dur_uk, catalog_id)
>
> somehow changed to include the ON ONLY option:
>
> CREATE UNIQUE INDEX chapter_u01 *ON ONLY *chapter USING btree (dur_uk,
> catalog_id)
>
> There is no SQL issued that explicitly requests this "ON ONLY" option.  I
> am wondering if this is a side-effect of some other activity.  Googling and
> looking through documentation haven't helped.
>
> Does anyone have any thoughts on how this might happen?
>
> --
> Rumpi Gravenstein
>


-- 
Rumpi Gravenstein

Indexes mysteriously change to LOG

[Rumpi Gravenstein]

We are on PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
8.5.0 20210514 (Red Hat 8.5.0-10), 64-bitPostgreSQL 14.5 on
x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat
8.5.0-10), 64-bit

We have recently discovered that on some of our partitioned tables indexes
that were created as:

CREATE UNIQUE INDEX chapter_u01 USING btree (dur_uk, catalog_id)

somehow changed to include the ON ONLY option:

CREATE UNIQUE INDEX chapter_u01 *ON ONLY *chapter USING btree (dur_uk,
catalog_id)

There is no SQL issued that explicitly requests this "ON ONLY" option.  I
am wondering if this is a side-effect of some other activity.  Googling and
looking through documentation haven't helped.

Does anyone have any thoughts on how this might happen?

-- 
Rumpi Gravenstein

Re: timestamptz, local time in the future, and Don't do it wiki

[Peter J. Holzer]

On 2023-01-27 19:12:08 +0700, Max Nikulin wrote:
> I am unsure what is the proper mailing list to discuss an the issue,
> this one or pgsql-doc.
> 
> PostgreSQL has a reputation of software with excellent support of time
> zones, so some people take recommendation to use "timestamp with time zone"
> type excessively literally. I mean the "Don't do this" page in the wiki:
> https://wiki.postgresql.org/wiki/Don%27t_Do_This#Date.2FTime_storage
> 
> Could you, please, add a case when the timestamptz type should not be used?
> UTC timestamps of forthcoming events may change due to an updates of tzdata
> if they really scheduled at specific local time. An example:

Yes. I could argue that this isn't really a "timestamp", though. The
time when the future event will happen isn't fixed yet - it depends on
future decisions (e.g. an update to DST rules or even a complete switch
to a different time zone).

However, few people will be that picky in their terminology. So it's
probably a good idea to point out that times which are supposed to be
relative to a specific time zone should be stored as local time + time
zone, not timestamptz (the time zone can be implicit).

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Sequence vs UUID

[Rob Sargent]

> So forget about performance issues (there will ALWAYS be need for faster 
> systems). The ease and functionality with UUID
> is so mutch better. Sequence keys are a terrible idea!
> 
> // GH
> 
Wow. I am not alone
> 

PostgreSQL DBA training

[Matthias Apitz]

Hello,

I'm with PostgreSQL for some years now, coding in ESQL/C, setting up
clusters inhouse and for our customers running our Library Management
System now on top of PostgreSQL (formerly Informix, Oracle and Sybase)
and migrated databases of these old DBSes to PostgreSQL.

Said that (and having expressed that I'm not a novice im matters of
PostgreSQL) I'm looking for a good DBA course, if possible face to face
and not online. Any pointer are welcome. I'm located in Germany, near
Munich.

Thanks in advance

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

timestamptz, local time in the future, and Don't do it wiki

[Max Nikulin]

Hi,

I am unsure what is the proper mailing list to discuss an the issue,
this one or pgsql-doc.

PostgreSQL has a reputation of software with excellent support of time 
zones, so some people take recommendation to use "timestamp with time 
zone" type excessively literally. I mean the "Don't do this" page in the 
wiki:

https://wiki.postgresql.org/wiki/Don%27t_Do_This#Date.2FTime_storage

Could you, please, add a case when the timestamptz type should not be 
used? UTC timestamps of forthcoming events may change due to an updates 
of tzdata if they really scheduled at specific local time. An example:


http://www.creativedeletion.com/2015/03/19/persisting_future_datetimes.html
Lau Taarnskov. How to save datetimes for future events - (when UTC is 
not the right answer)


I faced a similar issue once. One day I was asking myself looking at the 
time displayed by a web application

- Was this event scheduled in proper time zone or in UTC?
- Was the time zone database updated?
I do not know particular reason but the time was wrong. It was a morning 
just after change of time zone offset.


Various changes of time zone offset are not really rare, see
https://github.com/eggert/tz/blob/main/NEWS

However I am unsure what should be recommended instead of timestamptz. A 
column for timezone identifier in addition to local time as string is an 
option. Unfortunately there is an issue with ambiguous local time. I 
noticed a suggestion to use time zones abbreviations:


https://www.postgresql.org/docs/current/datetime-invalid-input.html
"B.2. Handling of Invalid or Ambiguous Timestamps"

Some problems:
- tzdata update may include changes of abbreviations
- it is necessary to determine these abbreviations somehow
Examples:
- Prior to abbreviations like "+08" some letters were used for the same 
timezones.
- Abbreviations may be changed between storing the event and scheduled 
time. The reason is the same. Offset of standard time changed, so 
timezone database is updated:


Europe/Kyiv  Sat Sep 23 22:59:59 1989 UT = Sun Sep 24 02:59:59 1989 MSD 
isdst=1 gmtoff=14400
Europe/Kyiv  Sat Sep 23 23:00:00 1989 UT = Sun Sep 24 02:00:00 1989 MSK 
isdst=0 gmtoff=10800


Europe/Kyiv  Sat Sep 28 23:59:59 1991 UT = Sun Sep 29 02:59:59 1991 EEST 
isdst=1 gmtoff=10800
Europe/Kyiv  Sun Sep 29 00:00:00 1991 UT = Sun Sep 29 02:00:00 1991 EET 
isdst=0 gmtoff=7200


Let's assume that an appointment was created in 1989 or 1990 before 
tzdata update

'1991-02-30 02:30:00' 'Europe/Kyiv' 'MSK' (or +03:00)
with hope to get time after time jump. When new rules was applied 
attempt to convert the same local time to UTC would give time moment 
before time transition.


In this sense Python's approach with fold=0 or fold=1 looks more 
straightforward, but I have not idea how to do it in Postgres:



from datetime import datetime
from zoneinfo import ZoneInfo
zi = ZoneInfo("Africa/Juba")
"{:%F %T %z %Z}".format(datetime(2021, 1, 31, 23, 30, 0, tzinfo=zi, fold=0))

'2021-01-31 23:30:00 +0300 EAT'
"{:%F %T %z %Z}".format(datetime(2021, 1, 31, 23, 30, 0, tzinfo=zi, 

fold=1))
'2021-01-31 23:30:00 +0200 CAT'

Details for this thime transition:

zdump -v Africa/Juba | grep 2021
Africa/Juba  Sun Jan 31 20:59:59 2021 UT = Sun Jan 31 23:59:59 2021 EAT 
isdst=0 gmtoff=10800
Africa/Juba  Sun Jan 31 21:00:00 2021 UT = Sun Jan 31 23:00:00 2021 CAT 
isdst=0 gmtoff=7200


It would be great to learn a better approach for appointments in local 
time in general and for treating of local time ambiguity in particular. 
I have some links, but they are more to state the problem than to 
present a robust solution. I do not think they are suitable for the wiki 
section.


To summarize, please, document timestamps in the future as an exception 
from the "use timestamp with time zone" rule. It would be nice to 
recommend a better approach to ensure correct local time despite changes 
in tzdata.

Re: Sequence vs UUID

[G Hasse]

Hello.

I have been using UUID for quite a long time now. The reason I began to use 
UUID was the need to be able to move data between
databases and the need to create record outside the database. You should use 
UUID as a primary key for a record and also have
some bookkeeping UUID:s in the record like "origin" of the record. In this way it is 
"easy" to handle different sources of
data.

We have also written some nice replicating software on this basis (postsync) 
that can check for alterations in one database
and update others. In this way we can keep one or many replicas of databases.

So forget about performance issues (there will ALWAYS be need for faster 
systems). The ease and functionality with UUID
is so mutch better. Sequence keys are a terrible idea!

// GH


Den 2023-01-26 kl. 20:17, skrev veem v:

Hello, We were trying to understand whether we should use UUID or Sequence in 
general for primary keys. In many of the blogs(one is below) across multiple 
databases, I saw over the internet and all are mostly stating the sequence is 
better as compared to UUID. But I think in the case of concurrent data load 
scenarios UUID will spread the contention point whereas sequence can be a 
single point of contention.

So we want to understand from experts here, if there are anyclear rules 
available or if we haveany pros vs cons list available for each of those to 
understand the exact scenario in which we should go for one over other? 
Basically I wantedto see if we can perform some test on sample data to see the 
percentage of overhead on read and write performances of the query in presence 
of UUID VS Sequence to draw some conclusion in general? And also considering 
open source postgres as the base for many databases like redshift etc, so the 
results which apply to progress would apply to others as well.

https://www.percona.com/blog/2019/11/22/uuids-are-popular-but-bad-for-performance-lets-discuss/
 




--
gor...@raditex.nu
http://www.raditex.nu
Raditex Control AB
Bo 229, 715 91 ODENSBACKEN
Mob: 070-5530148