Re: Oracle vs PG

On Wed, Oct 24, 2018 at 07:31:57AM +0200, Laurenz Albe wrote: > I have seen people use savepoints in PostgreSQL to emulate Oracle's > "statement rollback" behavior: If a statement fails, only the statement > is undone, but the transaction continues. > > If you insert a savepoint before *every*

Re: Postgres 10, slave not catching up with master

Yes, times are all identical, set to UTC, ntpd is used. log_delay --- 15.788175 This is delay at this moment, but we graph replication delay and it's fluctuating between 0 and 30s. Before I turned off wal compression, lag was much bigger (0 to up to 8 minutes). We have lots of tables

Re: Oracle vs PG

Ravi Krishna wrote: > I have hardly used savepoints in any application, but if I understand it > correctly, isn't it something which is typically used > in a persistent connection. I wonder how it is applicable in a web based > stateless application like Amazon.com, unless > even web based

Should pg 11 use a lot more memory building an spgist index?

While reloading a database cluster to move from 10.5 to 11, I'm getting out of memory crashes that I did see when doing reloads on pg 10. The statement flagged in the log is this: 2018-10-23 16:44:34.815 CDT [126839] STATEMENT: ALTER TABLE ONLY public.iplocation ADD CONSTRAINT

Re: Oracle vs PG

Ravi Krishna writes: >> Again, pretty much content-free. For all you know some application was >> creating savepoints, needlessly: > >> https://www.postgresql.org/docs/10/static/sql-savepoint.html > > I have hardly used savepoints in any application, but if I understand it > correctly, isn't

Re: Oracle vs PG

> > Amazon's web store may be a (mostly) stateless application, that doesn't mean > their back end applications are. > Oh yes. There is nothing in that article which suggests that the root cause of the outage was in the web based apps. As you indicated, their back end may be the source of

Re: Oracle vs PG

On Tue, Oct 23, 2018 at 6:36 PM Ravi Krishna wrote: > > I have hardly used savepoints in any application, but if I understand it > correctly, isn't it something which is typically used > in a persistent connection. I wonder how it is applicable in a web based > stateless application like

Re: Oracle vs PG

>I have hardly used savepoints in any application, but if I understand >it correctly, isn't it something which is typically used >in a persistent connection. I wonder how it is applicable in a web >based stateless application like Amazon.com, unless >even web based application have database

Re: Oracle vs PG

> Again, pretty much content-free. For all you know some application was > creating savepoints, needlessly: > https://www.postgresql.org/docs/10/static/sql-savepoint.html I have hardly used savepoints in any application, but if I understand it correctly, isn't it something which is typically

Re: Oracle vs PG

e to handle the pressure, slowing down the overall database performance, the report said." > > Again, pretty much content-free. For all you know some application was > creating savepoints, needlessly: > > https://www.postgresql.org/docs/10/static/sql-savepoint.html > > and not cleaning up

Re: Oracle vs PG

> > Is it so hard to accept commercial databases have advantages? > I find that not one bit surprising. > > I've used PG since 90's and it's no secret the "big guys" beat PG on certain > workloads. > In my previous workplace where they tested EDB to replace PG, they found all PL/SQL based

Re: Select "todays" timestamps in an index friendly way

On 24 October 2018 at 07:14, Mike Rylander wrote: > > On Tue, Oct 23, 2018 at 5:38 AM Lutz Horn wrote: > > I am looking for a way to select all timestamps that are "today" in an > > index friendly way. This select should not depend on the concrete value > > of "today". > > Per TFM,

Sv: Re: Sv: Re: Oracle vs PG

På tirsdag 23. oktober 2018 kl. 23:36:29, skrev Adrian Klaver < adrian.kla...@aklaver.com >: On 10/23/18 2:34 PM, Andreas Joseph Krogh wrote: > På tirsdag 23. oktober 2018 kl. 22:45:36, skrev Adrian Klaver > mailto:adrian.kla...@aklaver.com>>: > >     On

Re: Sv: Re: Oracle vs PG

On 10/23/18 2:34 PM, Andreas Joseph Krogh wrote: På tirsdag 23. oktober 2018 kl. 22:45:36, skrev Adrian Klaver mailto:adrian.kla...@aklaver.com>>: On 10/23/18 12:58 PM, Ravi Krishna wrote: > Well it is Aurora. > >

Sv: Re: Oracle vs PG

På tirsdag 23. oktober 2018 kl. 22:45:36, skrev Adrian Klaver < adrian.kla...@aklaver.com >: On 10/23/18 12:58 PM, Ravi Krishna wrote: > Well it is Aurora. > > https://www.cnbc.com/2018/10/23/amazon-move-off-oracle-caused-prime-day-outage-in-warehouse.html >

Re: Oracle vs PG

Adrian Klaver writes: > On 10/23/18 12:58 PM, Ravi Krishna wrote: > >> Well it is Aurora. >> >> https://www.cnbc.com/2018/10/23/amazon-move-off-oracle-caused-prime-day-outage-in-warehouse.html >> > > Since the article was almost content-free I not would use it on either > side of the argument.

Re: Oracle vs PG

On 10/23/18 1:47 PM, Ravi Krishna wrote: Since the article was almost content-free I not would use it on either side of the argument. The only thing I pulled from it was Amazon changed databases and hit the learning curve. That will happen in either direction. I agree but this is the key:

Re: Oracle vs PG

Em ter, 23 de out de 2018 às 17:48, Ravi Krishna escreveu: > > I agree but this is the key: > > "Savepoints are an important database tool for tracking and recovering individual transactions. On Prime Day, an excessive number of savepoints was created, and Amazon's Aurora software wasn't able to

Re: Oracle vs PG

Em ter, 23 de out de 2018 às 17:46, Adrian Klaver escreveu: > > On 10/23/18 12:58 PM, Ravi Krishna wrote: > > Well it is Aurora. > > > > https://www.cnbc.com/2018/10/23/amazon-move-off-oracle-caused-prime-day-outage-in-warehouse.html > > > > Since the article was almost content-free I not would

Re: Oracle vs PG

> > Since the article was almost content-free I not would use it on either side > of the argument. The only thing I pulled from it was Amazon changed databases > and hit the learning curve. That will happen in either direction. I agree but this is the key: "Savepoints are an important

Re: Oracle vs PG

On 10/23/18 12:58 PM, Ravi Krishna wrote: Well it is Aurora. https://www.cnbc.com/2018/10/23/amazon-move-off-oracle-caused-prime-day-outage-in-warehouse.html Since the article was almost content-free I not would use it on either side of the argument. The only thing I pulled from it was

Oracle vs PG

Well it is Aurora. https://www.cnbc.com/2018/10/23/amazon-move-off-oracle-caused-prime-day-outage-in-warehouse.html

Re: Postgres 10, slave not catching up with master

Hi Both servers are configured with the same date, time and time configuration? El mar., 23 de oct. de 2018 a la(s) 13:16, Hellmuth Vargas (hiv...@gmail.com) escribió: > Hi > > which result you get from the following query: > > SELECT CASE WHEN pg_last_wal_receive_lsn() =

Re: Postgres 10, slave not catching up with master

Hi which result you get from the following query: SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END AS log_delay; source: https://severalnines.com/blog/postgresql-streaming-replication-deep-dive

Re: Select "todays" timestamps in an index friendly way

On Tue, Oct 23, 2018 at 5:38 AM Lutz Horn wrote: > > Hi, > > I am looking for a way to select all timestamps that are "today" in an > index friendly way. This select should not depend on the concrete value > of "today". > Per TFM, https://www.postgresql.org/docs/10/static/datatype-datetime.html

Re: Select "todays" timestamps in an index friendly way

On Tue, Oct 23, 2018 at 3:57 PM, Lutz Horn wrote: > On Tue, Oct 23, 2018 at 03:50:14PM +0200, Francisco Olarte wrote: >> It'is not as the problem was stated. Although ts defaulted to now(), >> and it is probably defaulted, nothing prohibits him from inserting >> timestamps in the future. > Yes,

Re: Postgres 10, slave not catching up with master

Nothing special, just: standby_mode = 'on' primary_conninfo = 'host=... user=repmgr application_name=nodex' recovery_target_timeline = 'latest' Boris On Tue, Oct 23, 2018 at 3:10 PM, Hellmuth Vargas wrote: > Hi > > can share recovery.conf file settings?? > > El mar., 23 de oct. de 2018 a

Re: Enabling bdr in multiple databases on the same postgresql instance/cluster

On 23 October 2018 14:24:28 WEST, "Daniel Fink (PDF)" wrote: >Hi all, > > > >I already have a running cluster of BDR nodes. > >Now we want to add an additional database on the same hosts. > > > >Can I just create a new database and then create/join nodes as in this >description: >

Re: Select "todays" timestamps in an index friendly way

On Tue, Oct 23, 2018 at 03:50:14PM +0200, Francisco Olarte wrote: > It'is not as the problem was stated. Although ts defaulted to now(), > and it is probably defaulted, nothing prohibits him from inserting > timestamps in the future. Yes, this table is only used as an example for the technical

Re: Optimizing Postgresql ILIKE while query

Also leading wildcards can inhibit the use of indexes. Best to try to avoid LIKE queries similar to '%TERM' On Mon, Oct 22, 2018 at 12:23 AM Alban Hertroys wrote: > > > > On 22 Oct 2018, at 7:56, aman gupta wrote: > > > > Issue: > > > > We have the base table which contains 22M records and we

Re: Select "todays" timestamps in an index friendly way

On Tue, Oct 23, 2018 at 3:05 PM, Hellmuth Vargas wrote: ... >> Then use current_date: >> >>where ts >= current_date >> and ts < current_date + 1 > > this is equally valid? > > where ts >= current_date It'is not as the problem was stated. Although ts defaulted to now(), and it is

Enabling bdr in multiple databases on the same postgresql instance/cluster

Hi all, I already have a running cluster of BDR nodes. Now we want to add an additional database on the same hosts. Can I just create a new database and then create/join nodes as in this description: http://bdr-project.org/docs/1.0.3/quickstart-enabling.html Best Regards, *DANIEL

Re: Postgres 10, slave not catching up with master

Hi can share recovery.conf file settings?? El mar., 23 de oct. de 2018 a la(s) 00:28, Boris Sagadin ( bo...@infosplet.com) escribió: > Yes, turning wal_compression off improves things. Slave that was mentioned > unfortunately lagged too much before this setting was applied and was > turned off.

Re: Select "todays" timestamps in an index friendly way

Hi El mar., 23 de oct. de 2018 a la(s) 05:41, Thomas Kellerer ( spam_ea...@gmx.net) escribió: > Lutz Horn schrieb am 23.10.2018 um 12:19: > > Hi Thomas, > > > > On Tue, Oct 23, 2018 at 12:11:55PM +0200, Thomas Kellerer wrote: > >> I typically use: > >> > >> where ts >= date '2018-10-23' > >>

Re: Replication question

On Mon, Oct 22, 2018 at 1:20 PM Scot Kreienkamp < scot.kreienk...@la-z-boy.com> wrote: > I remember thinking it was pulling from archive with the restore command > if necessary to augment what it had on disk. If that was the case I wanted > to configure it. I don’t care for the replication

Re: Select "todays" timestamps in an index friendly way

Lutz Horn schrieb am 23.10.2018 um 12:19: > Hi Thomas, > > On Tue, Oct 23, 2018 at 12:11:55PM +0200, Thomas Kellerer wrote: >> I typically use: >> >> where ts >= date '2018-10-23' >> and ts < date '2018-10-23' + 1 > > But here the date is an explicit value. Francisco reworded my question:

Re: Select "todays" timestamps in an index friendly way

Hi Thomas, On Tue, Oct 23, 2018 at 12:11:55PM +0200, Thomas Kellerer wrote: > I typically use: > > where ts >= date '2018-10-23' > and ts < date '2018-10-23' + 1 But here the date is an explicit value. Francisco reworded my question: > if your definition of today is 'same value as now()

Re: Select "todays" timestamps in an index friendly way

Lutz Horn schrieb am 23.10.2018 um 11:38: > I can of course make an explicit select for `ts` values that are > "today": > > select ts, id > from t > where ts >= '2018-10-23T00:00:00'::timestamp >and ts <= '2018-10-23T23:59:59'::timestamp; > > This uses an Bitmap Index Scan

Re: Select "todays" timestamps in an index friendly way

Hi Francisco, On Tue, Oct 23, 2018 at 12:05:17PM +0200, Francisco Olarte wrote: > 1st remark. Do NOT use closed interval for timestamps. Always use > half-open or you'll run into problems Good point, thanks. > where ts >= date_trunc('day',now()) > and ts < date_trunc('day',now()+'1 day')

Re: Select "todays" timestamps in an index friendly way

Hi Lutz. On Tue, Oct 23, 2018 at 11:38 AM, Lutz Horn wrote: > I am looking for a way to select all timestamps that are "today" in an > index friendly way. This select should not depend on the concrete value > of "today". > Given a table > create temporary table t ( > id SERIAL primary

Select "todays" timestamps in an index friendly way

Hi, I am looking for a way to select all timestamps that are "today" in an index friendly way. This select should not depend on the concrete value of "today". Given a table create temporary table t ( id SERIAL primary key, ts timestamp not null default now() ); with some

Re: How to declare PG version for compiling extensions.

> "GPT" == GPT writes: GPT> Unfortunately, I had not installed the following package: GPT> "postgresql-server-dev-11" GPT> By the way, shouldn't a warning message appear while trying to run: GPT> `PATH=/usr/lib/postgresql/11/bin:$PATH make USE_PGXS=1` GPT> warning the user that some