Re: Sequence generating negative numbers

On 8/19/20 3:24 PM, Shantanu Shekhar wrote: Please reply to list also. Ccing list. Thanks Adrian, I will reach out to the ORM team and see if they can help me understand this behavior. I should have asked earlier, is this sequence set as a DEFAULT on the PK field or is it just being used by

Re: Understanding EXPLAIN ANALYZE estimates when loops != 1

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 loops=94) > > The actual rows returned by this plan node ~= 3760 *

Re: Sequence generating negative numbers

On 8/19/20 3:15 PM, Shantanu Shekhar wrote: Team, I have a sequence definition in Postgres 9.6.11 like so: CREATE SEQUENCE IF NOT EXISTS org.my_seq   INCREMENT 1   MINVALUE 1   NO MAXVALUE   START 1   CACHE 20; This sequence is used by a Java ORM framework to generate primary keys for on

Sequence generating negative numbers

Team, I have a sequence definition in Postgres 9.6.11 like so: CREATE SEQUENCE IF NOT EXISTS org.my_seq  INCREMENT 1  MINVALUE 1  NO MAXVALUE  START 1  CACHE 20; This sequence is used by a Java ORM framework to generate primary keys for one of our tables. The initial numbers generated by this seq

Understanding EXPLAIN ANALYZE estimates when loops != 1

Hi all, 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 loops=94) The actual rows returned by this plan node ~= 3760 * 94 = 353,440. Did postgres expect (estimate) 3283 rows

Re: Interpolation problem - pg 12.4 - full correct version!

Hi, > Might want to consider using PL/R with something like the R imputeTS package: > https://cran.r-project.org/web/packages/imputeTS/readme/README.html Thanks for your input - looks interesting, but see my reply to David Johnston. Rgs, Pól > Joe

Re: Interpolation problem - pg 12.4 - full correct version!

Hi, >> So, my question is: Is there a recognised technique (using SQL only, >> not PL/pgSQL - soutions based on the latter are easy to find) whereby >> I can do a basic Linear Interpolation? > I don't have a recognized technique, nor care to ponder one right now, but > what you've described woul

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 analyze before and after the window function? I mean, that limit at the top makes me think it is doing a lot less work without the window function, vs

Re: Interpolation problem - pg 12.4 - full correct version!

On 8/19/20 3:08 PM, David G. Johnston wrote: > On Wed, Aug 19, 2020 at 11:51 AM Pól Ua Laoínecháin > wrote: > > > I think my *MAJOR* problem is that I've developed what is, > essentially, a totally brute force approach - and this simply won't > work at the sce

Window functions speed

Confidential Hello I have spent the last day optimising a critical query which suddenly started behaving very inefficiently. There were issues with the query which are now sorted. The base query is now working in a timeframe that is far better. However, as soon as I add a window count function

Re: Interpolation problem - pg 12.4 - full correct version!

On Wed, Aug 19, 2020 at 11:51 AM Pól Ua Laoínecháin wrote: > > I think my *MAJOR* problem is that I've developed what is, > essentially, a totally brute force approach - and this simply won't > work at the scenario becomes more complex - take a look at the CASE > statement - it's horrible and wou

Interpolation problem - pg 12.4 - full correct version!

Hi all - here is the entire correct problem - apologies again! Hi all, I have an interpolation problem as follows - fiddle available here: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=428aa76d49b37961088d3dfef190757f A table: CREATE TABLE data ( s SERIAL PRIMARY KEY, t TIMESTAMP, lat N

Re: Interpolatioin problem - pg 12.4

HI all, Sorry - posted the wrong URL for my problem - Doh... Here is the correct one! https://dbfiddle.uk/?rdbms=postgres_12&fiddle=428aa76d49b37961088d3dfef190757f Again, apologies and rgs, Pól... On Wed, 19 Aug 2020 at 19:16, Pól Ua Laoínecháin wrote: > > Hi all, > > I have an interpola

Interpolatioin problem - pg 12.4

Hi all, I have an interpolation problem as follows - fiddle available here: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=8d23925146ea11a904c454709b0026fd A table: CREATE TABLE data ( s SERIAL PRIMARY KEY, t TIMESTAMP, lat NUMERIC ); and data: INSERT INTO data (t, lat) VALUES ('2019-01

Re: Inline count on a query

On Wed, Aug 19, 2020 at 8:19 AM Laura Smith < n5d9xq3ti233xiyif...@protonmail.ch> wrote: > On Wednesday, 19 August 2020 15:09, David G. Johnston < > david.g.johns...@gmail.com> wrote: > > > On Wednesday, August 19, 2020, Laura Smith < > n5d9xq3ti233xiyif...@protonmail.ch> wrote: > > > > > Hi, > >

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

=?utf-8?Q?Adam_Sj=C3=B8gren?= writes: > Tom writes: >> * Adam Sjøgren (a...@koldfront.dk) wrote: >>> Sometimes new database logins slow down, from usually taking <0.05s to >>> taking minutes. This is for psql as a normal user using Kerberos, for >>> psql as the postgres superuser, for the web-appl

Re: Inline count on a query

On Wednesday, 19 August 2020 15:09, David G. Johnston wrote: > On Wednesday, August 19, 2020, Laura Smith > wrote: > > > Hi, > > > > Let's say we've got a fairly basic table : > > > > create table networks ( > > lan_id text not null, > > net_id text not null, > > port_id text not null > > ); >

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

Adam writes: > I will try to look at pg_stat_activity and pg_locks the next time I did not have to wait long, so I have captured the output of pg_stat_activity and pg_locks when everything is fine, by just doing "time sudo -u postgres PAGER=cat psql template1 -c 'SELECT * FROM pg_stat_activity';

Re: Inline count on a query

On Wednesday, August 19, 2020, Laura Smith < n5d9xq3ti233xiyif...@protonmail.ch> wrote: > Hi, > > Let's say we've got a fairly basic table : > > create table networks ( > lan_id text not null, > net_id text not null, > port_id text not null > ); > create index net_uniq on networks(lan_id,port_id);

Inline count on a query

Hi, Let's say we've got a fairly basic table : create table networks ( lan_id text not null, net_id text not null, port_id text not null ); create index net_uniq on networks(lan_id,port_id); The query conundrum I am facing is that I need to add metadata to the output of the query that indicates

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

Tom writes: > Stephen Frost writes: >> * Adam Sjøgren (a...@koldfront.dk) wrote: >>> Sometimes new database logins slow down, from usually taking <0.05s to >>> taking minutes. This is for psql as a normal user using Kerberos, for >>> psql as the postgres superuser, for the web-application logging

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

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 bed" (not sure how to call it right), to test high > availability clusters based on Pacem