RSYNC YUM Mirror for pg14 not available yet

[Dennis Schwan]

Hey,


we are trying to set up a pg14 yum mirror (as we did with all the former 
versions) but it is not working:


rsync rsync://yum.postgresql.org
yum-repoPostgreSQL YUM Repository
zypp-repo  PostgreSQL ZYPP Repository
pgrpm-95PostgreSQL 9.5 YUM Repository
pgsrpm-95  PostgreSQL 9.5 YUM Repository - SRPMS
pgrpm-96PostgreSQL 9.6 YUM Repository
pgsrpm-96  PostgreSQL 9.6 YUM Repository - SRPMS
pgrpm-10PostgreSQL 10 YUM Repository
pgsrpm-10  PostgreSQL 10 YUM Repository - SRPMS
pgrpm-11PostgreSQL 11 YUM Repository
pgsrpm-11  PostgreSQL 11 YUM Repository - SRPMS
pgrpm-12PostgreSQL 12 YUM Repository
pgsrpm-12  PostgreSQL 12 YUM Repository - SRPMS
pgrpm-13PostgreSQL 13 YUM Repository
pgsrpm-13  PostgreSQL 13 YUM Repository - SRPMS
pgrpmzypp-95PostgreSQL 9.5 ZYPP Repository
pgsrpmzypp-95  PostgreSQL 9.5 ZYPP Repository - SRPMS
pgrpmzypp-96PostgreSQL 9.6 ZYPP Repository
pgsrpmzypp-96  PostgreSQL 9.6 ZYPP Repository - SRPMS
pgrpmzypp-10PostgreSQL 10 ZYPP Repository
pgsrpmzypp-10  PostgreSQL 10 ZYPP Repository - SRPMS
pgrpmzypp-11PostgreSQL 11 ZYPP Repository
pgsrpmzypp-11  PostgreSQL 11 ZYPP Repository - SRPMS
pgrpmzypp-12PostgreSQL 12 ZYPP Repository
pgsrpmzypp-12  PostgreSQL 12 ZYPP Repository - SRPMS
pgrpmzypp-13PostgreSQL 13 ZYPP Repository
pgsrpmzypp-13  PostgreSQL 13 ZYPP Repository - SRPMS
pgcommonpgcommon

Am I just too early for this?

Thanks,
Dennis




Dennis Schwan

DBA
Database Services

1&1 De-Mail GmbH | Brauerstraße 50 | 76135 Karlsruhe | Deutschland
Phone: +49 721 91374-8738 | Mobil: +49 160 4779866
E-Mail: dennis.sch...@1und1.de | Web: 
www.1und1.de

Hauptsitz Montabaur, Amtsgericht Montabaur, HRB 23266

Geschäftsführer: Alexander Charles, Ralf Hartings, Thomas Ludwig, Jan Oetjen

Member of United Internet

Diese E-Mail kann vertrauliche und/oder gesetzlich geschützte Informationen 
enthalten. Wenn Sie nicht der bestimmungsgemäße Adressat sind oder diese E-Mail 
irrtümlich erhalten haben, unterrichten Sie bitte den Absender und vernichten 
Sie diese E-Mail. Anderen als dem bestimmungsgemäßen Adressaten ist untersagt, 
diese E-Mail zu speichern, weiterzuleiten oder ihren Inhalt auf welche Weise 
auch immer zu verwenden.

This e-mail may contain confidential and/or privileged information. If you are 
not the intended recipient of this e-mail, you are hereby notified that saving, 
distribution or use of the content of this e-mail in any way is prohibited. If 
you have received this e-mail in error, please notify the sender and delete the 
e-mail.

Re: Duplicate key in UUID primary key index...

[Peter J. Holzer]

On 2021-10-14 03:02:08 +, Eric Tobias wrote:
> I have a table with a UUID as the primary key field. I am using a procedure to
> insert a parent and child record and using a UUID generated by an external
> system (webhook). The UUID insert is generating a “duplicate key” error on
> insert, but when I SELECT the uuid, nothing is returned.

Are you perhaps trying to insert the same uuid twice in the same
transaction? In this case the second insert would fail causing the
whole transaction to fail and you would never see that uuid in another
session.

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

ask for help：I want to compile a dynamic library file, such as libpsqlodbc w.so

[张进涛]

hello!
I compile code on linux and find that when I compile it, it is a static 
library file by default, 
but I want to compile a dynamic library file, such as libpsqlodbcw.so. How 
should I set the compilation options，or what should i do?

Postresql/postgis/qgis : assign privileges only for access/reading tables ?

[celati Laurent]

Good morning,

I work on Postgresql/postgis and Qgis.
 I had created a superuser account for me.
In qgis, I can see the postgis schemas and tables and browse the tables
without problem.

This time, i  created another simple user account just to be able in qgis
to access and load the table in qgis.
In pgadmin I created a new role with privileges "can login" only.
When i tape :

GRANT SELECT ON ALL TABLES IN SCHEMA TO USER

the request is well recognized and carried out.
But in qgis GIS, I only see the schema structure, I don't see the list of
tables.
Where can the error come from? Have I forgotten something?

Thank you.

RE: Duplicate key in UUID primary key index...

[Eric Tobias]

It turns out the issue was related to JSON and the UUID. I had a single row of 
data being inserted from the "source" side, but there were two rows contained 
in JSON within one of the fields. My extract pattern denormalized the JSON 
content and caused two records to be insert... with the same UUID. I should 
have thought of the JSON part!

Thank you for all the assistance, everyone.

Certificate validity error download.postgresql.org

[Cedric Rey]

Hi guys,

the certificate on download.postgresql.org has expired :

openssl s_client -connect download.postgresql.org:443
CONNECTED(0003)
depth=3 O = Digital Signature Trust Co., CN = DST Root CA X3
verify error:num=10:certificate has expired
notAfter=Sep 30 14:01:15 2021 GMT
---
Certificate chain
0 s:/CN=ftp.postgresql.org
   i:/C=US/O=Let's Encrypt/CN=R3
1 s:/C=US/O=Let's Encrypt/CN=R3
   i:/C=US/O=Internet Security Research Group/CN=ISRG Root X1
2 s:/C=US/O=Internet Security Research Group/CN=ISRG Root X1
   i:/O=Digital Signature Trust Co./CN=DST Root CA X3
---

Best Regards

Cédric


Cédric Rey
Ingénieur systèmes
Informatique / infrastructures

Tél. 058 758 3427
Mobile 079 699 00 12
www.groupemutuel.ch
_
Groupe Mutuel - Rue des Cèdres 5 - 1919 Martigny

-
https://www.groupemutuel.ch
https://www.facebook.com/groupemutuel.ch
https://twitter.com/Groupe_Mutuel
https://www.linkedin.com/company/groupe-mutuel
https://www.instagram.com/groupemutuel/

This e-mail may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this e-mail in error) 
please notify the sender immediately and delete this e-mail.
Any unauthorized copying, disclosure or distribution of the material in this 
e-mail is strictly forbidden.

JOB | DBA (Canada)

[James Tobin]

Hello, we are working with an employer that is looking to hire someone
capable of managing Mongo and Sybase databases at their office in
Canada.  Consequently, I had hoped that some members of this list may
like to discuss further.  Kind regards, James

Re: Certificate validity error download.postgresql.org

[Daniel Gustafsson]

> On 14 Oct 2021, at 14:34, Cedric Rey  wrote:

>  the certificate on download.postgresql.org has expired :

Are you perhaps running an old version of OpenSSL?

--
Daniel Gustafsson   https://vmware.com/

Re: Certificate validity error download.postgresql.org

[Christoph Moench-Tegeder]

## Cedric Rey (ce...@groupemutuel.ch):

> the certificate on download.postgresql.org has expired :
> 
> openssl s_client -connect download.postgresql.org:443
> CONNECTED(0003)
> depth=3 O = Digital Signature Trust Co., CN = DST Root CA X3
> verify error:num=10:certificate has expired
> notAfter=Sep 30 14:01:15 2021 GMT

That's complaining about the "DST Root CA X3" certificate, and that's
(partially) expected: https://letsencrypt.org/2021/10/01/cert-chaining-help.html

But the fact that you're seeing this indicates that you're either
running an horribly outdated version of openssl (as Daniel mentioned),
but even CentOS' "OpenSSL 1.0.2k-fips  26 Jan 2017" has been fixed
in this regard.
The other possibility is that your trusted CA list is outdated: that
would be package ca-certificates (same name in deb and rpm world).
I do know from my own experience that at least the "old" (2020.2.something)
Redhat package is missing the new "ISRG Root X1" certificate, you'll
need version 2021.2.something.

Regards,
Christoph

-- 
Spare Space

Re: JOB | DBA (Canada)

[Chris Travers]

On Thu, Oct 14, 2021 at 3:00 PM James Tobin  wrote:

> Hello, we are working with an employer that is looking to hire someone
> capable of managing Mongo and Sybase databases at their office in
> Canada.  Consequently, I had hoped that some members of this list may
> like to discuss further.  Kind regards, James
>

If you are posting here, is it because they want to move all these to
PostgreSQL?


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

RE: Certificate validity error download.postgresql.org

[Cedric Rey]

Hi, 

 It was indeed related to the ca-certificates package.

Thanks for your help!

Best Regards

-Message d'origine-
De : Christoph Moench-Tegeder [mailto:c...@burggraben.net] 
Envoyé : jeudi 14 octobre 2021 15:29
À : Cedric Rey 
Cc : pgsql-general@lists.postgresql.org
Objet : Re: Certificate validity error download.postgresql.org

## Cedric Rey (ce...@groupemutuel.ch):

> the certificate on download.postgresql.org has expired :
> 
> openssl s_client -connect download.postgresql.org:443
> CONNECTED(0003)
> depth=3 O = Digital Signature Trust Co., CN = DST Root CA X3 verify 
> error:num=10:certificate has expired notAfter=Sep 30 14:01:15 2021 GMT

That's complaining about the "DST Root CA X3" certificate, and that's
(partially) expected: https://letsencrypt.org/2021/10/01/cert-chaining-help.html

But the fact that you're seeing this indicates that you're either running an 
horribly outdated version of openssl (as Daniel mentioned), but even CentOS' 
"OpenSSL 1.0.2k-fips  26 Jan 2017" has been fixed in this regard.
The other possibility is that your trusted CA list is outdated: that would be 
package ca-certificates (same name in deb and rpm world).
I do know from my own experience that at least the "old" (2020.2.something) 
Redhat package is missing the new "ISRG Root X1" certificate, you'll need 
version 2021.2.something.

Regards,
Christoph

--
Spare Space
-
https://www.groupemutuel.ch
https://www.facebook.com/groupemutuel.ch
https://twitter.com/Groupe_Mutuel
https://www.linkedin.com/company/groupe-mutuel
https://www.instagram.com/groupemutuel/

This e-mail may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this e-mail in error) 
please notify the sender immediately and delete this e-mail.
Any unauthorized copying, disclosure or distribution of the material in this 
e-mail is strictly forbidden.

Re: NOTIFY queue is at 66% and climbing...

[Jeff Ross]

On 10/13/21 5:50 PM, Tom Lane wrote:

Jeff Ross  writes:

On 10.15 I'm getting the following on a logically replicated server.
2021-10-13 18:49:39.792 EDT,,,213601,,6143c257.34261,64243,,2021-09-16
18:16:55 EDT,4/3914851,60709901,WARNING,01000,"NOTIFY queue is 66%
full",""
In the CSV logs above what part points to "the session that is
preventing cleanup" so that I can kill it?

Normally there's a DETAIL entry citing the session's PID.  Looking
at the code, the reason for the lack of any such entry must be that
there is no session whose current notify queue position exactly
matches the supposed global minimum position.  This corresponds to
a known bug that was fixed in 10.16, so I'd suggest upgrading.
As a temporary workaround you could restart that server, but
likely the problem would recur after awhile.

regards, tom lane

Thanks as always, Tom.  I'll schedule the upgrade to 10.18 and restart 
for this weekend.


Jeff

Re: Certificate validity error download.postgresql.org

[Tom Lane]

Christoph Moench-Tegeder  writes:
> I do know from my own experience that at least the "old" (2020.2.something)
> Redhat package is missing the new "ISRG Root X1" certificate, you'll
> need version 2021.2.something.

Seems unlikely that it changed that recently, for a couple of reasons:

* AFAICT, Red Hat's policy is to track the Mozilla NSS trusted-CA
list exactly.  They do update from there only once a year or so,
but NSS has trusted ISRG Root X1 for five years.

* Looking at "rpm -q ca-certificates --changelog" on a RHEL8 machine,
the package maintainer appears to have started a policy in mid-2019
of listing every single cert addition and removal in the changelog.
None of the updates since then mention ISRG Root X1.

* While Let's Encrypt's list of compatible platforms [1] doesn't mention
Red Hat directly, they do say that NSS has trusted X1 since release 3.26.
According to the changelog, Red Hat adopted that in August 2016:

* Tue Aug 16 2016 Kai Engert  - 2016.2.9-3
- Revert to the unmodified upstream CA list, changing the legacy trust
  to an empty list. Keeping the ca-legacy tool and existing config,
  however, the configuration has no effect after this change.

* Tue Aug 16 2016 Kai Engert  - 2016.2.9-2
- Update to CKBI 2.9 from NSS 3.26 with legacy modifications

So it sure looks from here like Red Hat has trusted the X1 certificate
since mid-2016, pretty much the same length of time as other major
distros.  The most probable explanation for the OP's problem seems
to be failure to update ca-certificates and/or openssl at all for
several years.

regards, tom lane

[1] https://letsencrypt.org/docs/certificate-compatibility/

Re: JOB | DBA (Canada)

[Gavin Flower]

On 15/10/21 02:00, James Tobin wrote:

Hello, we are working with an employer that is looking to hire someone
capable of managing Mongo and Sybase databases at their office in
Canada.  Consequently, I had hoped that some members of this list may
like to discuss further.  Kind regards, James


If they wanted people from this list the they should upgrade to 
PostgreSQL, they'll find PostgreSQL faster and more reliable than 
Mongo.  I suspect  the same might true for Sybase, but I've never done a 
detailed comparison.



Cheers,
Gavin

Re: JOB | DBA (Canada)

[Chris Travers]

On Thu, Oct 14, 2021 at 5:37 PM Gavin Flower 
wrote:

> On 15/10/21 02:00, James Tobin wrote:
> > Hello, we are working with an employer that is looking to hire someone
> > capable of managing Mongo and Sybase databases at their office in
> > Canada.  Consequently, I had hoped that some members of this list may
> > like to discuss further.  Kind regards, James
> >
> >
> If they wanted people from this list the they should upgrade to
> PostgreSQL, they'll find PostgreSQL faster and more reliable than
> Mongo.  I suspect  the same might true for Sybase, but I've never done a
> detailed comparison.
>

Indeed.  MongoDB has some interesting features like capped collections
which make it useful as a cache, but my experience always leaves me feeling
like performance and scalability are lacking.

>
>
> Cheers,
> Gavin
>
>
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Certificate validity error download.postgresql.org

[Tom Lane]

Cedric Rey  writes:
> rpm -q ca-certificates --changelog
> * Tue Sep 14 2021 Bob Relyea  - 2021.2.50-72
> - Fix expired certificate.
> -Removing:
> - # Certificate "DST Root CA X3"

> As you can see they just remove the old "DST Root CA X3" in the latest el7 
> ca-certificate version  which correct the problem I had before.

Wow, that is quite interesting, because they've propagated no such
update to my RHEL8 or Fedora 34 machines (mumble dnf update mumble
... nope, still not there).  I speculate that that's because those
releases don't need it: they're both running openssl 1.1.1something,
which will do the right thing as soon as it finds the ISRG Root X1
certificate in the chain.  But RHEL7 is still using openssl 1.0.2,
which will follow the chain to the DST cert and then spit up [1].
So evidently Red Hat has implemented OpenSSL's "workaround 1" [2]
on RHEL7, but they left well enough alone on newer platforms.

They could not have pushed out the DST cert removal much before
that cert expired, for fear of causing unnecessary problems
elsewhere.  So that's why the seemingly short notice.

regards, tom lane

[1] 
https://community.letsencrypt.org/t/openssl-client-compatibility-changes-for-let-s-encrypt-certificates/143816
[2] https://www.openssl.org/blog/blog/2021/09/13/LetsEncryptRootCertExpire/

Conditional Tables in Postgres

[Mubashir Anwar]

Hi!

I am a PhD student, researching on using databases to implement network
control infrastructure. For this purpose, I wanted to use conditional tables
 in
databases, which allow representing unknown values in a DB with conditions
over tuples. However, I could not find any DBMS that supports them. Are
there any postgres extensions/features that implement conditional tables? I
wanted to confirm by asking here in case I missed something in my search.

Thanks in advance!

Best,
Mubashir

RE: Certificate validity error download.postgresql.org

[Cedric Rey]

rpm -q ca-certificates --changelog
* Tue Sep 14 2021 Bob Relyea  - 2021.2.50-72
- Fix expired certificate.
-Removing:
- # Certificate "DST Root CA X3"


As you can see they just remove the old "DST Root CA X3" in the latest el7 
ca-certificate version  which correct the problem I had before.

Openssl v1.0.2 is still the default version for Red Hat 7 and is already in the 
latest version available.

So no, it wasn't a failure to update ca-certificates for "several years" but 
for several days since the latest ca-certificates rpm was release Sep 14 2021.

Anyway thanks for pointing me out that it was an error related to this expired 
Root CA and not related to postgresql download site certificate.

Regards,

Cédric

-Message d'origine-
De : Tom Lane [mailto:t...@sss.pgh.pa.us] 
Envoyé : jeudi 14 octobre 2021 16:51
À : Christoph Moench-Tegeder 
Cc : Cedric Rey ; pgsql-general@lists.postgresql.org
Objet : Re: Certificate validity error download.postgresql.org

Christoph Moench-Tegeder  writes:
> I do know from my own experience that at least the "old" 
> (2020.2.something) Redhat package is missing the new "ISRG Root X1" 
> certificate, you'll need version 2021.2.something.

Seems unlikely that it changed that recently, for a couple of reasons:

* AFAICT, Red Hat's policy is to track the Mozilla NSS trusted-CA list exactly. 
 They do update from there only once a year or so, but NSS has trusted ISRG 
Root X1 for five years.

* Looking at "rpm -q ca-certificates --changelog" on a RHEL8 machine, the 
package maintainer appears to have started a policy in mid-2019 of listing 
every single cert addition and removal in the changelog.
None of the updates since then mention ISRG Root X1.

* While Let's Encrypt's list of compatible platforms [1] doesn't mention Red 
Hat directly, they do say that NSS has trusted X1 since release 3.26.
According to the changelog, Red Hat adopted that in August 2016:

* Tue Aug 16 2016 Kai Engert  - 2016.2.9-3
- Revert to the unmodified upstream CA list, changing the legacy trust
  to an empty list. Keeping the ca-legacy tool and existing config,
  however, the configuration has no effect after this change.

* Tue Aug 16 2016 Kai Engert  - 2016.2.9-2
- Update to CKBI 2.9 from NSS 3.26 with legacy modifications

So it sure looks from here like Red Hat has trusted the X1 certificate since 
mid-2016, pretty much the same length of time as other major distros.  The most 
probable explanation for the OP's problem seems to be failure to update 
ca-certificates and/or openssl at all for several years.

regards, tom lane

[1] https://letsencrypt.org/docs/certificate-compatibility/
-
https://www.groupemutuel.ch
https://www.facebook.com/groupemutuel.ch
https://twitter.com/Groupe_Mutuel
https://www.linkedin.com/company/groupe-mutuel
https://www.instagram.com/groupemutuel/

This e-mail may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this e-mail in error) 
please notify the sender immediately and delete this e-mail.
Any unauthorized copying, disclosure or distribution of the material in this 
e-mail is strictly forbidden.

Re: Conditional Tables in Postgres

[Imre Samu]

Hi Mubashir,

>  ... researching on using databases to implement network control
infrastructure.  ...
>  Are there any postgres extensions/features that implement conditional
tables

like this?

https://dl.acm.org/doi/10.1145/3472716.3472848  "Sarasate: a strong
representation system for networking policies"
*"Policy information in computer networking today is hard to manage. This
is in sharp contrast to relational data structured in a database that
allows easy access. In this demonstration, we ask why cannot (or how can)
turn network policies into relational data. Our key observation is that
oftentimes a policy does not prescribe a single ``definite'' network state,
but rather is an ``incomplete'' description of all the legitimate network
states. Based on this idea, we adopt conditional tables and the usual SQL
interface (a relational structure developed for incomplete database) as a
means to represent and query sets of network states in exactly the same way
as a single definite network snapshot. More importantly, like relational
tables that improve data productivity and innovation, relational policies
allow us to extend a rich set of data mediating methods to address the
networking problem of coordinating policies in a distributed environment."*

As I see the source code is:
https://github.com/ravel-net/Faure/blob/main/apps/README_sarasate.md
   And it is a* [ PL/python3u extension  + Z3-solver (Z3 API in Python) ]*
solution.
Probably easy to reimplement in
-  PL/Julia ( https://gitlab.com/pljulia/pljulia ) + Z3 API in Julia (
https://github.com/ahumenberger/Z3.jl )

IMHO: if you have a favorite Python/R/Julia library in this topic - you can
easily integrate it with PostgreSQL.

Regards,
  Imre


Mubashir Anwar  ezt írta (időpont: 2021. okt.
14., Cs, 21:41):

>
> Hi!
>
> I am a PhD student, researching on using databases to implement network
> control infrastructure. For this purpose, I wanted to use conditional
> tables  in
> databases, which allow representing unknown values in a DB with conditions
> over tuples. However, I could not find any DBMS that supports them. Are
> there any postgres extensions/features that implement conditional tables? I
> wanted to confirm by asking here in case I missed something in my search.
>
> Thanks in advance!
>
> Best,
> Mubashir
>

"two time periods with only an endpoint in common do not overlap" ???

[Bryn Llewellyn]

I’m quoting this from “9.9. Date/Time Functions and Operators” in the PG 
Version 14 doc on the “overlaps” operator, here:

www.postgresql.org/docs/14/functions-datetime.html

It’s the same in “current”—and in the Version 11 doc.

«
This expression yields true when two time periods (defined by their endpoints) 
overlap, false when they do not overlap. The endpoints can be specified as 
pairs of dates, times, or time stamps; or as a date, time, or time stamp 
followed by an interval. When a pair of values is provided, either the start or 
the end can be written first; OVERLAPS automatically takes the earlier value of 
the pair as the start. Each time period is considered to represent the 
half-open interval start <= time < end, unless start and end are equal in which 
case it represents that single time instant. This means for instance that two 
time periods with only an endpoint in common do not overlap.
»

I tried this obvious test (using Version 13.4):

with c as (
  select
'2000-01-15'::timestamp as start_1,
'2000-02-15'::timestamp as start_2,
'2000-03-15'::timestamp as common_endpoint)
select (
  (start_1, common_endpoint) overlaps
  (start_2, common_endpoint)
)::text
from c;

The result is "true". Seems to me that the doc is therefore wrong—not only as 
shown by this test but also w.r.t. what reasoning from the account at 
"half-open interval" says.

Now consider this:

with c as (
  select
'2000-01-15'::timestamp as start,
'2000-02-15'::timestamp as common_touchpoint,
'2000-03-15'::timestamp as endpoint)
select (
  (start, common_touchpoint) overlaps
  (common_touchpoint, endpoint)
)::text
from c;

The result is now "false".  As it seems to me this is correct w.r.t. what 
reasoning from the account at "half-open interval" says.

It also seems to me that whenever the doc derives a predicted result from the 
stated rules, it's honor bound to substantiate this with a code example.

Re: "two time periods with only an endpoint in common do not overlap" ???

[Tom Lane]

Bryn Llewellyn  writes:
> I tried this obvious test (using Version 13.4):

> with c as (
>   select
> '2000-01-15'::timestamp as start_1,
> '2000-02-15'::timestamp as start_2,
> '2000-03-15'::timestamp as common_endpoint)
> select (
>   (start_1, common_endpoint) overlaps
>   (start_2, common_endpoint)
> )::text
> from c;

> The result is "true". Seems to me that the doc is therefore wrong

Huh?  Those intervals have lots of points in common, not only a
single point.  The documentation is referring to a case like your
second example.

regards, tom lane

Re: "two time periods with only an endpoint in common do not overlap" ???

[Adrian Klaver]

On 10/14/21 16:38, Bryn Llewellyn wrote:
I’m quoting this from “9.9. Date/Time Functions and Operators” in the PG 
Version 14 doc on the “overlaps” operator, here:


www.postgresql.org/docs/14/functions-datetime.html 



It’s the same in “current”—and in the Version 11 doc.

«
This expression yields true when two time periods (defined by their
endpoints) overlap, false when they do not overlap. The endpoints
can be specified as pairs of dates, times, or time stamps; or as a
date, time, or time stamp followed by an interval. When a pair of
values is provided, either the start or the end can be written
first; OVERLAPS automatically takes the earlier value of the pair as
the start. Each time period is considered to represent the half-open
interval start <= time < end, unless start and end are equal in
which case it represents that single time instant. This means for
instance that two time periods with only an endpoint in common do
not overlap.
»


I tried this obvious test (using Version 13.4):

*with c as (
   select
     '2000-01-15'::timestamp as start_1,
     '2000-02-15'::timestamp as start_2,
     '2000-03-15'::timestamp as common_endpoint)
select (
   (start_1, **common_endpoint**) overlaps
   (start_2, **common_endpoint**)
)::text
from c;


This resolves to:

select ('2000-01-15'::timestamp, '2000-03-15'::timestamp) overlaps 
('2000-02-15'::timestamp, '2000-03-15'::timestamp);

 overlaps
--
 t

which to me looks like an overlap.

What you are referring to is:

select ('2000-01-15'::timestamp, '2000-03-15'::timestamp) overlaps 
('2000-03-15'::timestamp, '2000-03-20'::timestamp);

 overlaps
--
 f

or the third example in the docs:

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
   (DATE '2001-10-30', DATE '2002-10-30');
Result: true
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
   (DATE '2001-10-30', DATE '2002-10-30');
Result: false
SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS
   (DATE '2001-10-30', DATE '2001-10-31');
Result: false
SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
   (DATE '2001-10-30', DATE '2001-10-31');
Result: true



*
The result is "true". Seems to me that the doc is therefore wrong—not 
only as shown by this test but also w.r.t. what reasoning from the 
account at "half-open interval" says.


Now consider this:

*with c as (
   select
     '2000-01-15'::timestamp as start,
     '2000-02-15'::timestamp as common_touchpoint,
     '2000-03-15'::timestamp as endpoint)
select (
   (start, **common_touchpoint**) overlaps
   (**common_touchpoint**, **endpoint**)
)::text
from c;
*
The result is now "false".  As it seems to me this is correct w.r.t. 
what reasoning from the account at "half-open interval" says.


It also seems to me that whenever the doc derives a predicted result 
from the stated rules, it's honor bound to substantiate this with a code 
example.





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

Re: "two time periods with only an endpoint in common do not overlap" ???

[David G. Johnston]

On Thu, Oct 14, 2021, 16:38 Bryn Llewellyn  wrote:

> . This means for instance that two time periods with only an endpoint in
> common do not overlap.
>
>
A range has two endpoints.  The one at the later (end) of the range and the
one at the earlier (start).  I suppose rewording it to say "boundary point"
in common would avoid the ambiguity in the use of the word "end".

>
David J.

Re: "two time periods with only an endpoint in common do not overlap" ???

[Tom Lane]

"David G. Johnston"  writes:
> A range has two endpoints.  The one at the later (end) of the range and the
> one at the earlier (start).  I suppose rewording it to say "boundary point"
> in common would avoid the ambiguity in the use of the word "end".

Hmm, it seems clear to me in the context of the whole paragraph that
"endpoint" means either end of the range.  "Boundary point" would be
longer but I doubt any clearer.

regards, tom lane

Re: "two time periods with only an endpoint in common do not overlap" ???

[Bryn Llewellyn]

> David Johnston wrote:
> 
>> Bryn wrote:
>> 
>> This means for instance that two time periods with only an endpoint in 
>> common do not overlap.
> 
> A range has two endpoints. The one at the later (end) of the range and the 
> one at the earlier (start).  I suppose rewording it to say “boundary point” 
> in common would avoid the ambiguity in the use of the word “end”.

Thanks, David. And thanks, too, to Tom and to Adrian for your prompt replies.

I see that I should have expressed myself more clearly. I never thought that 
either of the examples that I showed was behaving wrongly. David guessed right: 
I thought that the wording in the doc was confusing and might be improved.

A period (unless it collapses to an instant) is defined by the two moment 
values that bound it. (I’m using “moment” to mean a point in absolute time in a 
way that doesn’t care about the data type.) And when these two moments are 
distinct, one will be earlier than the other.

In plain English, people talk about, say, a relationship starting and (at least 
as often happens) ending. You ask “when did the relationship start and end?” 
Nobody talks about a relationship’s two endpoints. (But maybe they do in a 
different culture with a different language).

In fact, the PG doc reflects this vernacular usage by giving the signature of 
one of the overloads thus:

(start1, end1) OVERLAPS (start2, end2)

So I read “endpoint” in the doc I quoted to mean “either end1 or end2” (and, by 
extension, “startpoint”, if it had been used, to mean “either start1 or start2”.

But the doc wants me to take “endpoint” to mean “either start1, end1, start2, 
or end2”.

Maybe you think that I’m being too fussy. If so, please forgive me.

Certainly, David’s suggestion to use “boundary point” would be easy to 
implement, and would be an improvement. I think that I prefer this:

When the end of one period coincides with the start of the other period, then 
“overlaps” returns “false”. 

because it uses the terms in the same way that they are used in the signature.

Re: Postresql/postgis/qgis : assign privileges only for access/reading tables ?

[Ben Madin]

Two thoughts:

1. Can the user view the tables when connecting through another database
program (ie psql or PgAdmin)?
2. Does the user have access to the database schema that has the PostGIS
extension (normally public schema)?

cheers

Ben


On Thu, 14 Oct 2021 at 19:00, celati Laurent 
wrote:

> Good morning,
>
> I work on Postgresql/postgis and Qgis.
>  I had created a superuser account for me.
> In qgis, I can see the postgis schemas and tables and browse the tables
> without problem.
>
> This time, i  created another simple user account just to be able in qgis
> to access and load the table in qgis.
> In pgadmin I created a new role with privileges "can login" only.
> When i tape :
>
> GRANT SELECT ON ALL TABLES IN SCHEMA TO USER
>
> the request is well recognized and carried out.
> But in qgis GIS, I only see the schema structure, I don't see the list of
> tables.
> Where can the error come from? Have I forgotten something?
>
> Thank you.
>


-- 

[image: Ausvet Logo] 

Dr Ben Madin

BVMS MVPHMgmt PhD MANZCVS GAICD
Managing Director
E-mail:
b...@ausvet.com.au
Website:
www.ausvet.com.au

Address:
5 Shuffrey Street
Fremantle, WA 6160
Australia