Re: = t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value

[Francisco Olarte]

Bryn:

( 1st, sorry if I misquote something, but i use text-only for the list )

On Fri, Mar 26, 2021 at 10:16 PM Bryn Llewellyn  wrote:

> I’d deduced the conceptual background that both Tom and Francisco referred 
> to. And I’ve coined the terms “horological interval” and “cultural interval” 
> to capture the distinction. I’d also noticed that it seems that, to first 
> order, the “interval day to second” flavor maps to “horological” and the 
> "interval year to month” flavor maps to “cultural”. However, as my testcase 
> shows, “day” is an oddity because subtracting two timestamptz values treats 
> “day” in the “horological” sense but adding an interval value to a 
> timestamptz treats “day” in the cultural sense. This was the central point of 
> my question. Neither of you referred to this.

I, personally, did not refer to that on purpose, as I did not fully
understand what you were trying to prove, and as your code defaulted
the timezones I could not easily reproduce result. I saw intervals
working as they are dessigned, thought you might be trying to use them
for a purpose they are not dessigned and pointed that.

> I’m going to conclude just that it is what it is and it won’t change.

Intervals have a behaviour. Many people do not like it/consider it
wrong. My advice is normally "do not use them". That is what I do,
except for quick and dirty reports I rarely ever use them .

...

> create table t(k int primary key, i interval day to second not null);
> insert into t(k, i) values(1, '1 day 1 hour'), (2, '25 hours');
> select k, i from t order by k;
>
> The “select” shows that the difference in spelling the two values is 
> preserved in what’s persisted. This is consistent with months, days, and 
> seconds being persisted separately. And it’s consistent with the different 
> semantic effect that the two values can have on addition. I therefore find it 
> confusing that wrong semantics are imposed on the represented value here:
>
> create unique index on t(i);
>
> It causes the “could not create unique index” error. This is the same effect 
> that the “assert” for equality in my testcase shows. The two values can be 
> defined to be equal only if “day” is given a strict horological meaning. But 
> the present discussion shows that it’s taken culturally on interval addition.

Interval are a hairy datatype. I'm not sure they even have a proper
order defined  (i.e, is 30 days 6 hours more or less than 1 month ).
They have some normalization conversions, but IMHO they are not good
to use in a btree, which needs to order them.


> This brings me to another strange observation. Do this:
.. Skipping intermediates...
> select (
> ('2021-11-15 12:00:00'::timestamptz - '2021-02-15 
> 12:00:00'::timestamptz)::interval month
>   )::text as i;
> It silently produces this result:
> 00:00:00

You are right, the substraction produces a days interval (273 here),
whcih when truncated to months just go  to zero.

It surprised me a bit, so I dug into the docs and found this:

postgres=> select('2021-11-15'::timestamptz - '2021-02-15'::timestamptz);
 ?column?
--
 273 days
(1 row)

postgres=> select age('2021-11-15'::timestamptz, '2021-02-15'::timestamptz);
  age

 9 mons
(1 row)

I just stored it in the "why you should always look at the manual
before using interval" slots and went on. Note if you alter months to
cross a single DST jump ( in here ) and repeat you would see more
strange results.

postgres=> set timezone TO 'Europe/Madrid';
SET
postgres=> select age('2021-09-15'::timestamptz,
'2021-02-15'::timestamptz), '2021-09-15'::timestamptz -
'2021-02-15'::timestamptz;
  age   | ?column?
+---
 7 mons | 211 days 23:00:00
(1 row)

It seems substraction is trying to preserve extract(epoch from
(tza-tzb)) = extract(epoch from tza) - extract(epoch from tzb), but
"beautifying" it a bit by using days. Docs should have it somewhere,
but those chapters are a dense read.

> In plain English, you can’t produce a cultural interval value by subtraction; 
> subtraction can only populate a “day to second” flavor interval. Moreover, 
> the computed “hours” component never exceeds 24 and the computed “days” 
> component is always the horological value.

Probably true. That seems to be the purpose of the age() function and
is one of the reasons I normally avoid intervals.

> I failed to find an explanation of this in the doc—but I dare say that my 
> searching skills are too feeble.

You could find some things like "SQL STD mandates it"·. It does
mandate some really weird things.



> I can guess the rules for the outcome when such a hybrid is added to a 
> timestamptz value. It’s possible to design edge case tests where you’d get 
> different outcomes if: (a) the cultural component is added first and only 
> then the horological component is added; or (b) the components are added in 
> the other order. It seems to me that the outcome is governed by rule (a). Am 
> I right?

I'm comple

ERROR: could not start WAL streaming: ERROR: replication slot "XXX" does not exist

[FOUTE K . Jaurès]

Hello EveryOne,

How can I solve Issue. ???



*ERROR:  could not start WAL streaming: ERROR:  replication slot "XXX" does
not exist2021-03-27 11:48:33.012 WAT [1090] LOG:  background worker
"logical replication worker" (PID 8458) exited with exit code 12021-03-27
11:48:38.019 WAT [8461] LOG:  logical replication apply worker for
subscription "XXX has started*

All working fine a few days ago but this morning I have this issue.

-- 
Jaurès FOUTE

Re: Hello - About how to install PgAdmin4 on Debian 10

[Carlos Montenegro]

Hello Adrian.
First I installed postgresql version 12.6 then for pgadmin4 followed this
guide:  https://www.pgadmin.org/download/pgadmin-4-apt/

So, when  try to open pgadmin have the next message:

The PgAdmin4 server could not be contacted:

pgAdmin Runtime Environment

Python Path: "/usr/pgadmin4/venv/bin/python3"
Runtime Config File: "/home/ics-debian/.config/pgadmin/runtime_config.json"
pgAdmin Config File: "/usr/pgadmin4/web/config.py"
Webapp Path: "/usr/pgadmin4/web/pgAdmin4.py"
pgAdmin Command: "/usr/pgadmin4/venv/bin/python3 -s
/usr/pgadmin4/web/pgAdmin4.py"
Environment:
  - USER: ics-debian
  - LANGUAGE: es_NI:es
  - XDG_SEAT: seat0
  - XDG_SESSION_TYPE: x11
  - SSH_AGENT_PID: 1330
  - HOME: /home/ics-debian
  - DESKTOP_SESSION: lightdm-xsession
  - XDG_SEAT_PATH: /org/freedesktop/DisplayManager/Seat0
  - GTK_MODULES: gail:atk-bridge
  - DBUS_SESSION_BUS_ADDRESS: unix:path=/run/user/1000/bus
  - GLADE_MODULE_PATH: :
  - LOGNAME: ics-debian
  - XDG_SESSION_CLASS: user
  - XDG_SESSION_ID: 2
  - PATH: /usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games
  - GLADE_PIXMAP_PATH: :
  - XDG_SESSION_PATH: /org/freedesktop/DisplayManager/Session0
  - XDG_RUNTIME_DIR: /run/user/1000
  - XDG_MENU_PREFIX: xfce-
  - LANG: es_NI.UTF-8
  - XDG_CURRENT_DESKTOP: XFCE
  - XDG_SESSION_DESKTOP: lightdm-xsession
  - XAUTHORITY: /home/ics-debian/.Xauthority
  - XDG_GREETER_DATA_DIR: /var/lib/lightdm/data/ics-debian
  - SSH_AUTH_SOCK: /tmp/ssh-7U4tMKJqlRu8/agent.1299
  - GLADE_CATALOG_PATH: :
  - SHELL: /bin/bash
  - GDMSESSION: lightdm-xsession
  - QT_ACCESSIBILITY: 1
  - XDG_VTNR: 7
  - PWD: /home/ics-debian
  - XDG_DATA_DIRS: /usr/share/xfce4:/usr/local/share/:/usr/share/:/usr/share
  - XDG_CONFIG_DIRS: /etc/xdg
  - SESSION_MANAGER: local/ics-debian:@
/tmp/.ICE-unix/1340,unix/ics-debian:/tmp/.ICE-unix/1340
  - DISPLAY: :0.0
  - GDK_BACKEND: x11
  - NO_AT_BRIDGE: 1
  - PGADMIN_INT_PORT: 5433
  - PGADMIN_INT_KEY: f6b73bcd-abb3-42b8-9bf2-c8d47536c80e
  - PGADMIN_SERVER_MODE: OFF


Failed to launch pgAdmin4. Error:
Error: spawn /usr/pgadmin4/venv/bin/python3 ENOENT

Thank you in advance,
Carlos

On Thu, Mar 25, 2021 at 4:14 PM Adrian Klaver 
wrote:

> On 3/25/21 12:49 PM, Carlos Montenegro wrote:
> > Hello dear Adrian and Ray.
> > Good day !
> >
> > Thank you both for helping me.
> > It has worked and have installed pgadmin4, but have this issue when try
> > to open the server:
>
> What packages did you install?
>
> Define open server.
>
> What is the complete text of the actual error message?
>
> >
> > pgAdmin4 server could not be contacted
> > Any suggestions?
> > Best
> >
> >
> > On Wed, Mar 24, 2021 at 4:10 PM Adrian Klaver  > > wrote:
> >
> > On 3/24/21 1:17 PM, Carlos Montenegro wrote:
> >  > Yes Ray,
> >  > I followed those instructions, but it seems the repository
> location
> >  > changed. It is not available, but thank you so much for your
> support.
> >
> > It worked for me using the instructions listed here(per Ray's post):
> >
> > https://www.pgadmin.org/download/pgadmin-4-apt/
> > 
> >  >
> >  > Best,
> >  > Carlos
> >  >
> >  > On Wed, Mar 24, 2021 at 1:55 PM Ray O'Donnell  > 
> >  > >> wrote:
> >  >
> >  > On 24/03/2021 19:08, Carlos Montenegro wrote:
> >  >  > Hello dear Ray.
> >  >  > Thanks for your answer.
> >  >  >
> >  >  > Yes, I see pgadmin4 and then tried, but any success.
> >  >  > How did you make the installation? Any advice?
> >  >
> >  > Hi Carlos,
> >  >
> >  > I had forgotten - pgAdmin has its own apt repository, which
> > you need
> >  > to add:
> >  >
> >  > https://www.pgadmin.org/download/pgadmin-4-apt/
> > 
> >  >  > >
> >  >
> >  > Once you've done that, then "apt update" and "apt install..."
> > should do
> >  > the job.
> >  >
> >  > I hope this helps.
> >  >
> >  > Ray.
> >  >
> >  >
> >  > --
> >  > Raymond O'Donnell // Galway // Ireland
> >  > r...@rodonnell.ie 
> > >
> >  >
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: = t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value

[Bryn Llewellyn]

Tom Lane wrote:

b...@yugabyte.com writes:
> There's more. We see that while the two "interval second” values '1 day 
> 01:00:00' and '25 hours' test as equal, the results of adding each to the 
> same timestamptz value are different.

You're misunderstanding how it works…

> PLEASE STATE THE RULES THAT ALLOW THE BEHAVIOR OF THE BLOCK TO BE PREDICTED.

…there is absolutely nothing about either the Gregorian calendar or 
daylight-savings time that you will like.

> (I searched the doc but found nothing.)

There's a specific discussion of the DST-boundary issue on… 
https://www.postgresql.org/docs/11/functions-datetime.html. 


Thanks for the prompt reply Tom.

and for the doc ref. (I used the Version 11 page ‘cos that’s my particular 
focus.)

« When adding an interval value to (or subtracting an interval value from) a 
timestamp with time zone value, the days component advances or decrements the 
date of the timestamp with time zone by the indicated number of days, keeping 
the time of day the same. Across daylight saving time changes (when the session 
time zone is set to a time zone that recognizes DST), this means interval '1 
day' does not necessarily equal interval '24 hours’.. »

I also noted this from fola...@peoplecall.com:

« It seems you want to use them as simple seconds count, like “I'm starting a 
batch job which will take 25 hours to be done, when will it end?". Like unix 
timestamps. If you want that, just avoid interval, use extract epoch to convert 
timestamptz to a simple number of seconds, do arithmetic there (where intervals 
are just plain numbers) and convert back using  to_timestamp(). »

Thanks Francisco. That’s a useful tip.

I’d deduced the conceptual background that both Tom and Francisco referred to. 
And I’ve coined the terms “horological interval” and “cultural interval” to 
capture the distinction. I’d also noticed that it seems that, to first order, 
the “interval day to second” flavor maps to “horological” and the "interval 
year to month” flavor maps to “cultural”. However, as my testcase shows, “day” 
is an oddity because subtracting two timestamptz values treats “day” in the 
“horological” sense but adding an interval value to a timestamptz treats “day” 
in the cultural sense. This was the central point of my question. Neither of 
you referred to this.

I’m going to conclude just that it is what it is and it won’t change.

I noted this from https://www.postgresql.org/docs/11/datatype-datetime.html:

« Internally interval values are stored as months, days, and seconds. This is 
done because the number of days in a month varies, and a day can have 23 or 25 
hours if a daylight savings time adjustment is involved. The months and days 
fields are integers while the seconds field can store fractions. Because 
intervals are usually created from constant strings or timestamp subtraction, 
this storage method works well in most cases, but can cause unexpected results… 
»

(Francisco referred to this.) Consider this:

create table t(k int primary key, i interval day to second not null);
insert into t(k, i) values(1, '1 day 1 hour'), (2, '25 hours');
select k, i from t order by k;

The “select” shows that the difference in spelling the two values is preserved 
in what’s persisted. This is consistent with months, days, and seconds being 
persisted separately. And it’s consistent with the different semantic effect 
that the two values can have on addition. I therefore find it confusing that 
wrong semantics are imposed on the represented value here:

create unique index on t(i);

It causes the “could not create unique index” error. This is the same effect 
that the “assert” for equality in my testcase shows. The two values can be 
defined to be equal only if “day” is given a strict horological meaning. But 
the present discussion shows that it’s taken culturally on interval addition.

This brings me to another strange observation. Do this:

set time zone 'US/Pacific';
select (
('2021-02-15 12:00:00'::timestamptz + (interval '9 months')::interval 
month)::timestamptz at time zone 'US/Pacific'
  )::text as t;

This is the result:

2021-11-15 12:00:00

In plain English, 9 months after 15-Feb is 15-Nov—consistent with what has been 
said about “month” always being taken culturally. Now try this:

select (
('2021-11-15 12:00:00'::timestamptz - '2021-02-15 
12:00:00'::timestamptz)::interval month
  )::text as i;

It silently produces this result:

00:00:00

In plain English, you can’t produce a cultural interval value by subtraction; 
subtraction can only populate a “day to second” flavor interval. Moreover, the 
computed “hours” component never exceeds 24 and the computed “days” component 
is always the horological value.

I failed to find an explanation of this in the doc—but I dare say that my 
searching skills are too feeble.

Finally, I discovered that this is OK:

create table t(i interval);

But I can’t find a de

Re: Hello - About how to install PgAdmin4 on Debian 10

[Ray O'Donnell]

Hello Carlos,

This sounds like a different issue, and you would have more luck asking 
about it on the pgadmin-support list [1], which the developers follow.


If you can, include the relevant bits from the pgAdmin log, which 
according to [2] should be here:


  ~/.pgadmin/pgadmin4.log

Ray.


[1] https://www.pgadmin.org/support/list/

[2] https://www.pgadmin.org/faq/#8



--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Hello - About how to install PgAdmin4 on Debian 10

[Ray O'Donnell]

On 27/03/2021 14:59, Ray O'Donnell wrote:

If you can, include the relevant bits from the pgAdmin log, which 
according to [2] should be here:


  ~/.pgadmin/pgadmin4.log


A quick find + grep also found me these:

~/.local/share/pgadmin4.startup.log
~/.local/share/pgadmin/pgadmin4/.pgAdmin4.startup.log

...though the second one is much older.

Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: = t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value

[Adrian Klaver]

On 3/26/21 2:16 PM, Bryn Llewellyn wrote:

/Tom Lane wrote:/




Finally, I discovered that this is OK:

*create table t(i interval);*

But I can’t find a definition of the semantics of a bare interval. 
However, I did find a column headed “Mixed Interval”  at 
https://www.postgresql.org/docs/11/datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE 
. 
But the example values in the column are consistent with this:


*select ((interval '2 years, 3 months, 4 days, 5 hours, 6 minutes 7.8 
seconds')::interval)::text as i;*


This is the result:

*2 years 3 mons 4 days 05:06:07.8*

If you repeat the “select” using the typecast “*::interval month*” then 
the other components are silently thrown away. But if you repeat it 
using the typecast “*::interval second*” then all components are 
preserved just as with bare “*interval*”. This muddies my idea that 
there were three distinct interval flavors: horological, cultural, and 
hybrid. Is the behavior that I’ve just shown intended?



https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT

"Also, field values “to the right” of the least significant field 
allowed by the fields specification are silently discarded. For example, 
writing INTERVAL '1 day 2:03:04' HOUR TO MINUTE results in dropping the 
seconds field, but not the day field."


So you get:

test(5432)=> select '2 years 3 mons 4 days 05:06:07.8'::interval month;
interval

 2 years 3 mons
(1 row)

Equivalent to:

test(5432)=> select '2 years 3 mons 4 days 05:06:07.8'::interval YEAR TO 
MONTH;

interval

 2 years 3 mons


test(5432)=> select '2 years 3 mons 4 days 05:06:07.8'::interval second;
 interval
--
 2 years 3 mons 4 days 05:06:07.8
(1 row)

Equivalent to:

select '2 years 3 mons 4 days 05:06:07.8'::interval HOUR TO SECOND;
 interval
--
 2 years 3 mons 4 days 05:06:07.8
(1 row)



I can guess the rules for the outcome when such a hybrid is added to a 
timestamptz value. It’s possible to design edge case tests where you’d 
get different outcomes if: (a) the cultural component is added first and 
only then the horological component is added; or (b) the components are 
added in the other order. It seems to me that the outcome is governed by 
rule (a). Am I right?


B.t.w., I think that the specific complexities of the proleptic 
Gregorian calendar are cleanly separable from the basic idea that 
(considering only the requirements statement space) there is a real 
distinction to be drawn between “horological” and “cultural”—no matter 
what calendar rules might be used.



Looking for logic in dates/times/calendars is a recipe for a continuous 
pounding headache. Not the least because horological = cultural.


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

Re: Hello - About how to install PgAdmin4 on Debian 10

[Benedict Holland]

The containers for postgres and pgadmin4 are incredible. This entire
thread, worrying about configurations settings and options, it all
disappears. You just run the container. There are tutorials to help you get
it set up with nginx. It just works. The developers and maintainers have
done an incredible job making it ridiculously easy to just make it work.

Thanks,
Ben

On Sat, Mar 27, 2021, 11:15 AM Ray O'Donnell  wrote:

> On 27/03/2021 14:59, Ray O'Donnell wrote:
>
> > If you can, include the relevant bits from the pgAdmin log, which
> > according to [2] should be here:
> >
> >   ~/.pgadmin/pgadmin4.log
>
> A quick find + grep also found me these:
>
> ~/.local/share/pgadmin4.startup.log
> ~/.local/share/pgadmin/pgadmin4/.pgAdmin4.startup.log
>
> ...though the second one is much older.
>
> Ray.
>
> --
> Raymond O'Donnell // Galway // Ireland
> r...@rodonnell.ie
>
>
>

Re: ERROR: could not start WAL streaming: ERROR: replication slot "XXX" does not exist

[Atul Kumar]

As per your error, it seems replication slot has been dropped.

On Saturday, March 27, 2021, FOUTE K. Jaurès  wrote:

> Hello EveryOne,
>
> How can I solve Issue. ???
>
>
>
> *ERROR:  could not start WAL streaming: ERROR:  replication slot "XXX"
> does not exist2021-03-27 11:48:33.012 WAT [1090] LOG:  background worker
> "logical replication worker" (PID 8458) exited with exit code 12021-03-27
> 11:48:38.019 WAT [8461] LOG:  logical replication apply worker for
> subscription "XXX has started*
>
> All working fine a few days ago but this morning I have this issue.
>
> --
> Jaurès FOUTE
>