Re: Database logins taking longer and longer, showing up as "authentication" in ps(1)

Tom writes: > Frequently, stack traces taken without debug symbols are pretty useless, > but you seem to have got lucky. For once, hah! > It seems clear that the unexpected wait is here: > >> (gdb) bt >> #0 0x7f8272cf2827 in futex_abstimed_wait_cancelable (private=128, >> abstime=0x0,

Re: BUG? Slave don't reconnect to the master

> 19 авг. 2020 г., в 16:07, Jehan-Guillaume de Rorthais > написал(а): > > On Tue, 18 Aug 2020 13:48:41 +0300 > Олег Самойлов wrote: > >> Hi all. >> >> I found some strange behaviour of postgres, which I recognise as a bug. First >> of all, let me explain situation. >> >> I created a "test

Loading Oracle Spatial Data to Postgresql

Hi All, I am running into issue using DMS to load oracle spatial data (oracle 11.2.0.3) to postgresql. Oracle table: CREATE TABLE Spatial_Tbl IDNUMBER(38,9), P_ID NUMBER(38,9), GEOMETRY MDSYS.SDO_GEOMETRY AWS DMS calls the SDO2GEOJSON custom function trying to load the d

Re: Loading Oracle Spatial Data to Postgresql

On 8/20/20 7:30 AM, Ko, Christina wrote: Hi All, I am running into issue using DMS to load oracle spatial data (oracle 11.2.0.3) to postgresql. Oracle table: CREATETABLESpatial_Tbl IDNUMBER(38,9),   P_ID NUMBER(38,9), GEOMETRY  MDSYS.SDO_GEOMETRY AWS DMS calls the SDO2GEOJSON custom fun

Re: Loading Oracle Spatial Data to Postgresql

Le 20/08/2020 à 16:30, Ko, Christina a écrit : Hi All, I am running into issue using DMS to load oracle spatial data (oracle 11.2.0.3) to postgresql. Oracle table: CREATETABLESpatial_Tbl IDNUMBER(38,9), P_ID NUMBER(38,9), GEOMETRY MDSYS.SDO_GEOMETRY AWS DMS calls the SDO2GEOJSON custom

RE: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql

-Original Message- From: Adrian Klaver Sent: Thursday, August 20, 2020 10:42 AM To: Ko, Christina (US) ; pgsql-general@lists.postgresql.org Cc: Ho, Chuong Subject: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql On 8/20/20 7:30 AM, Ko, Christina wrote: > Hi All, > > I am ru

Re: Understanding EXPLAIN ANALYZE estimates when loops != 1

> On Aug 19, 2020, at 6:24 PM, David Rowley wrote: > > On Thu, 20 Aug 2020 at 09:55, Philip Semanchuk > wrote: >> I could use some help interpreting EXPLAIN ANALYZE output. >> >> -> Index Scan using ix_foo on t1 (cost=0.69..68.57 rows=3283 width=105) >> (actual time=0.006..0.918 rows=3760

Re: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql

On 8/20/20 9:57 AM, Ko, Christina wrote: -Original Message- From: Adrian Klaver Sent: Thursday, August 20, 2020 10:42 AM To: Ko, Christina (US) ; pgsql-general@lists.postgresql.org Cc: Ho, Chuong Subject: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql On 8/20/20 7:30 AM, Ko,

RE: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql

From: Gilles Darold Sent: Thursday, August 20, 2020 10:54 AM To: Ko, Christina (US) ; pgsql-general@lists.postgresql.org Cc: Ho, Chuong Subject: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql Le 20/08/2020 à 16:30, Ko, Christina a écrit : Hi All, I am running into issue using DMS to

Re: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql

On 8/20/20 7:57 AM, Ko, Christina wrote: -Original Message- From: Adrian Klaver Sent: Thursday, August 20, 2020 10:42 AM To: Ko, Christina (US) ; pgsql-general@lists.postgresql.org Cc: Ho, Chuong Subject: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql On 8/20/20 7:30 AM, Ko

Re: Sequence generating negative numbers

Adrian, The sequence is not set as a default on PK. The sequence is exclusively being used by the ORM.  Thanks, Shantanu On Wednesday, August 19, 2020, 06:28:53 PM EDT, Adrian Klaver wrote: On 8/19/20 3:24 PM, Shantanu Shekhar wrote: Please reply to list also. Ccing list. > Thanks Adr

Re: Sequence generating negative numbers

On 8/20/20 9:21 AM, Shantanu Shekhar wrote: Adrian, The sequence is not set as a default on PK. The sequence is exclusively being used by the ORM. Then something there is generating negative numbers and using them to populate the PK. Thanks, -- Adrian Klaver adrian.kla...@aklaver.com

Re: Database logins taking longer and longer, showing up as "authentication" in ps(1)

=?utf-8?Q?Adam_Sj=C3=B8gren?= writes: > Tom writes: >> ... which implies that the problem is unexpectedly high contention for the >> ProcArrayLock. > One thing I should have mentioned, but forgot, is that the database is > configured to do logical replication to another machine - could that > hav

Re: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql

Le 20/08/2020 à 17:57, Ko, Christina a écrit : *From:* Gilles Darold *Sent:* Thursday, August 20, 2020 10:54 AM *To:* Ko, Christina (US) ; pgsql-general@lists.postgresql.org *Cc:* Ho, Chuong *Subject:* EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql Le 20/08/2020 à 16:30, Ko, Christ

RE: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql

From: Gilles Darold Sent: Thursday, August 20, 2020 1:59 PM To: Ko, Christina (US) ; pgsql-general@lists.postgresql.org Cc: Ho, Chuong Subject: Re: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql Le 20/08/2020 à 17:57, Ko, Christina a écrit : From: Gilles Darold

RE: Window functions speed

Confidential From: Michael Lewis Sent: 19 August 2020 20:53 To: Zahir Lalani Cc: pgsql-gene...@postgresql.org Subject: Re: Window functions speed Where's the query? Are you able to run explain analyze so we can see estimates vs actual counts? What version are you using? Can you share explain

Misestimate when applying condition like id = id

Simplified example query- explain select * from organizations o where id = id; On a PG10 instance I get- Seq Scan on organizations o (cost=0.00..4.21 ***rows=1*** width=37) Filter: (id = id) On a PG11 instance with the same or very very similar data, I get- Seq Scan on organizations o (cost=0.00

Re: Misestimate when applying condition like id = id

Michael Lewis writes: > Simplified example query- > explain > select * from organizations o where id = id; > On a PG10 instance I get- > Seq Scan on organizations o (cost=0.00..4.21 ***rows=1*** width=37) > Filter: (id = id) > On a PG11 instance with the same or very very similar data, I get- >

Re: Orphaned relations after crash/sigkill during CREATE TABLE

On Tue, Aug 18, 2020 at 3:49 PM Adrian Klaver wrote: > So from [1] you are using CREATE TABLE AS. Have you tried with: > > BEGIN; > CREATE TABLE some_table SELECT some_data FROM other_table LIMIT 1 WITH > NO DATA; > COMMIT; > > The above gets you the table structure, but no data. > > BEGIN; > INSE

Re: Orphaned relations after crash/sigkill during CREATE TABLE

On 8/20/20 1:37 PM, Jason Myers wrote: On Tue, Aug 18, 2020 at 3:49 PM Adrian Klaver > wrote: > So from [1] you are using CREATE TABLE AS. Have you tried with: > > BEGIN; > CREATE TABLE some_table SELECT some_data FROM other_table LIMIT 1 WITH > NO DATA;

Re: Understanding EXPLAIN ANALYZE estimates when loops != 1

On Fri, 21 Aug 2020 at 03:21, Philip Semanchuk wrote: > > > > > On Aug 19, 2020, at 6:24 PM, David Rowley wrote: > > > > On Thu, 20 Aug 2020 at 09:55, Philip Semanchuk > > wrote: > >> -> Parallel Index Scan using pk_xyz on xyz (cost=0.29..2354.67 > >> rows=54285 width=25) (actual time=0.049..

Re: Misestimate when applying condition like id = id

Thanks very much. I tested with a much bigger table and it seems that it is using the default .005 selectivity for this integer column. Cool. Nice to understand even if it was just a typo that had things going off the rails. And thanks for your work 3 years ago for that commit.

Re: Orphaned relations after crash/sigkill during CREATE TABLE

Jason Myers writes: > However we were still seeing orphaned files on crash, and I believe I > tracked it down to subsequent CREATE INDEX statements also creating these > orphaned files (if they are running during a crash). > Is that issue known as well? I don't believe I can use the same trick to

Re: Orphaned relations after crash/sigkill during CREATE TABLE

On Thu, Aug 20, 2020 at 5:46 PM Tom Lane wrote: > Jason Myers writes: > > However we were still seeing orphaned files on crash, and I believe I > > tracked it down to subsequent CREATE INDEX statements also creating these > > orphaned files (if they are running during a crash). > > Is that issue

Re: Orphaned relations after crash/sigkill during CREATE TABLE

On 8/20/20 14:46, Tom Lane wrote: > There might be value in a user-invokable tool that runs in an existing > non-crashed database and looks for orphan files, but I'm not aware that > anyone has written one. (Race conditions against concurrent table > creation would be a problem; but probably that