Re: [GENERAL] Error with DefineSavepoint:Unexpected state STARTED

On Tue, Jun 20, 2017 at 11:12 AM, Computer Study wrote: > The UI application is to add/remove some permissions through a listbox for > a certain user. For any add/remove, it will first delete all records for > this certain user in the table, then insert the values

Re: [GENERAL] Streaming replication bandwith per table

Am 20. Juni 2017 03:06:05 MESZ schrieb Peter Eisentraut : >On 6/19/17 20:50, Maeldron T. wrote: >> > >Not easily. You could play around with pg_xlogdump to see what's going >on in the WAL. But even if you figure it out, there is not much you >can >do about it.

Re: [GENERAL] Error with DefineSavepoint:Unexpected state STARTED

Thanks Tom. The version of Postgres is: 9.4.7 The UI application is to add/remove some permissions through a listbox for a certain user. For any add/remove, it will first delete all records for this certain user in the table, then insert the values chose from UI. In my code snippet, it first

Re: [GENERAL] Error with DefineSavepoint:Unexpected state STARTED

Computer Study writes: > I am working on a project of DB migration from Oracle to Postgres. The > application is working well with Oracle. But when switch to Postgres, some > SQL executionss couldn't finish and got an error like: > FATAL: DefineSavepoint: unexpected

[GENERAL] Error with DefineSavepoint:Unexpected state STARTED

Hi, I am working on a project of DB migration from Oracle to Postgres. The application is working well with Oracle. But when switch to Postgres, some SQL executionss couldn't finish and got an error like: FATAL: DefineSavepoint: unexpected state STARTED STATEMENT:

Re: [GENERAL] Streaming replication bandwith per table

On 6/19/17 20:50, Maeldron T. wrote: > Streaming replication generates too much traffic to set it up between > different regions for financial reasons. The streaming replication would > cost more than every other hosting expense altogether (including every > the traffic, even though it’s web and

[GENERAL] Streaming replication bandwith per table

Hello, tl;dr Streaming replication generates too much traffic to set it up between different regions for financial reasons. The streaming replication would cost more than every other hosting expense altogether (including every the traffic, even though it’s web and huge amount of emails). Is

Re: [GENERAL] effective_io_concurrency increasing

On Mon, Jun 19, 2017 at 4:51 PM, Peter Geoghegan wrote: > This would make only the first lookup for each distinct value on the > outer side actually do an index scan on the inner side. I can imagine > the optimization saving certain queries from consuming a lot of memory >

Re: [GENERAL] effective_io_concurrency increasing

On Mon, Jun 19, 2017 at 4:35 PM, Andres Freund wrote: >> I think that this is the way index scan prefetch is normally >> implemented. Index scans will on average have a much more random >> access pattern than what is typical for bitmap heap scans, making this >> optimization

Re: [GENERAL] effective_io_concurrency increasing

On 2017-06-19 15:21:20 -0700, Peter Geoghegan wrote: > On Mon, Jun 19, 2017 at 8:36 AM, Jeff Janes wrote: > > Unfortunately, it is only implemented in very narrow circumstances. You > > have to be doing bitmap index scans of many widely scattered rows to make it > > useful.

Re: [GENERAL] effective_io_concurrency increasing

On Mon, Jun 19, 2017 at 3:25 PM, Alvaro Herrera wrote: > Surely you could prefetch all the heap pages pointed to by index items > in the current leaf index page ... I'm sure that you could do that too. I'm not sure how valuable each prefetching optimization is. I can

Re: [GENERAL] effective_io_concurrency increasing

Peter Geoghegan wrote: > On Mon, Jun 19, 2017 at 8:36 AM, Jeff Janes wrote: > > Unfortunately, it is only implemented in very narrow circumstances. You > > have to be doing bitmap index scans of many widely scattered rows to make it > > useful. I don't think that this is

Re: [GENERAL] effective_io_concurrency increasing

On Mon, Jun 19, 2017 at 8:36 AM, Jeff Janes wrote: > Unfortunately, it is only implemented in very narrow circumstances. You > have to be doing bitmap index scans of many widely scattered rows to make it > useful. I don't think that this is all that common of a situation.

Re: [GENERAL] performance considerations of jsonb vs separate rows

On Mon, Jun 19, 2017 at 2:29 PM, Rob Nikander wrote: > I'm wondering about the tradeoffs, specifically: is it possible to update > one piece of a jsonb value without having to rewrite the entire field? There > are cases where that data field was getting pretty big (500kb).

Re: [GENERAL] effective_io_concurrency increasing

On Mon, Jun 19, 2017 at 10:49:59AM -0500, Merlin Moncure wrote: > On Mon, Jun 19, 2017 at 10:36 AM, Jeff Janes wrote: > > If you have a RAID, set it to the number of spindles in your RAID and forget > > it. It is usually one of the less interesting knobs to play with.

Re: [GENERAL] autovacuum holds exclusive lock on table preventing it from to be updated

On Mon, Jun 19, 2017 at 1:53 PM, Dmitry O Litvintsev wrote: > yes, we had to restart database 4 days ago (and vacuum has resumed on start). > I checked the log files and discovered that autovacuum on this table takes > > pages: 0 removed, 14072307 remain >

Re: [GENERAL] sub-select with multiple records, columns

On Jun 19, 2017, at 12:29 PM, Thomas Kellerer wrote: > > Israel Brewster schrieb am 19.06.2017 um 22:17: >> SELECT >> ... >> (SELECT >> array_agg(to_json(row(notedate,username,note))) >> FROM sabrenotes >> INNER JOIN users ON author=users.id >> WHERE ticket=sabretickets.id )

Re: [GENERAL] sub-select with multiple records, columns

On Mon, Jun 19, 2017 at 1:32 PM, David G. Johnston wrote: > On Mon, Jun 19, 2017 at 1:29 PM, Thomas Kellerer wrote: >> >> Israel Brewster schrieb am 19.06.2017 um 22:17: >>> >>> SELECT >>> ... >>> (SELECT >>>

Re: [GENERAL] sub-select with multiple records, columns

Israel Brewster schrieb am 19.06.2017 um 22:17: SELECT ... (SELECT array_agg(to_json(row(notedate,username,note))) FROM sabrenotes INNER JOIN users ON author=users.id WHERE ticket=sabretickets.id ) notes FROM tickets WHERE ... The only problem with this query is that the notes aren't sorted. Of

[GENERAL] sub-select with multiple records, columns

I have two tables, a ticket table and a notes table, set up where each ticket can have multiple notes. I'm trying to come up with a query that returns the ticket fields as well as a field that is an array type field with the values being json-encoded note records. I've come up with the following

Re: [GENERAL] autovacuum holds exclusive lock on table preventing it from to be updated

yes, we had to restart database 4 days ago (and vacuum has resumed on start). I checked the log files and discovered that autovacuum on this table takes pages: 0 removed, 14072307 remain tuples: 43524292 removed, 395006545 remain buffer usage: -1493114028 hits, 107664973

Re: [GENERAL] performance considerations of jsonb vs separate rows

> On 19 June 2017 at 21:29, Rob Nikander wrote: > > I'm wondering about the tradeoffs, specifically: is it possible to update one piece of a jsonb value without having to rewrite the entire field? There are cases where that data field was getting pretty big (500kb). Would

Re: [GENERAL] performance considerations of jsonb vs separate rows

Am 19. Juni 2017 21:29:40 MESZ schrieb Rob Nikander : > >I'm wondering about the tradeoffs, specifically: is it possible to >update one piece of a jsonb value without having to rewrite the entire >field? Updates in PostgreSQL are always Delete & Insert. So the answer is

[GENERAL] performance considerations of jsonb vs separate rows

Hi, I’ve got a web app where I want to store user’s session data. The schema in this data changes a lot so it may be useful here to store the session properties in either a jsonb column, or in multiple rows. Something like: session_id | data 100 { a: 1, bar: 2 ... 101

Re: [GENERAL] autovacuum holds exclusive lock on table preventing it from to be updated

On Mon, 19 Jun 2017 17:33:23 + Dmitry O Litvintsev wrote: > > The test stand where I was to test schema upgrade is stuck cuz vacuum is > blocking. If you're in "panic mode" I would recommend cancelling the existing vacuum, running your upgrades, then immeditely running

Re: [GENERAL] autovacuum holds exclusive lock on table preventing it from to be updated

On Mon, Jun 19, 2017 at 10:33 AM, Dmitry O Litvintsev wrote: > Hi > > Since I have posted this nothing really changed. I am starting to panic > (mildly). > > The source (production) runs : > > relname | mode | granted | >

Re: [GENERAL] autovacuum holds exclusive lock on table preventing it from to be updated

Dmitry O Litvintsev wrote: > Hi > > Since I have posted this nothing really changed. I am starting to panic > (mildly). ... > vacuum_cost_delay = 50ms Most likely, this value is far too high. You're causing autovacuum to sleep for a very long time with this setting. Hard to say for

Re: [GENERAL] autovacuum holds exclusive lock on table preventing it from to be updated

Hi Since I have posted this nothing really changed. I am starting to panic (mildly). The source (production) runs : relname | mode | granted | substr| query_start |

Re: [GENERAL] Remote connection to PostgreSQL

On Mon, Jun 19, 2017 at 12:21 PM, Igor Korot wrote: > Thx, David. > > On Mon, Jun 19, 2017 at 12:09 PM, David G. Johnston > wrote: > > On Mon, Jun 19, 2017 at 9:02 AM, Igor Korot wrote: > >> > >> Hi, ALL, > >> Is there some

Re: [GENERAL] Remote connection to PostgreSQL

Thx, David. On Mon, Jun 19, 2017 at 12:09 PM, David G. Johnston wrote: > On Mon, Jun 19, 2017 at 9:02 AM, Igor Korot wrote: >> >> Hi, ALL, >> Is there some magic in order to turn on remote connection to PostgreSQL? >> >> There are some extra

Re: [GENERAL] effective_io_concurrency increasing

On Mon, Jun 19, 2017 at 8:49 AM, Merlin Moncure wrote: > On Mon, Jun 19, 2017 at 10:36 AM, Jeff Janes wrote: > > If you have a RAID, set it to the number of spindles in your RAID and > forget > > it. It is usually one of the less interesting knobs to

Re: [GENERAL] Remote connection to PostgreSQL

On Mon, Jun 19, 2017 at 9:02 AM, Igor Korot wrote: > Hi, ALL, > Is there some magic in order to turn on remote connection to PostgreSQL? > > There are some extra steps to turn it on for MS SQL and MySQL, so I figured > it should be the same for Postgre. > ​See

[GENERAL] Remote connection to PostgreSQL

Hi, ALL, Is there some magic in order to turn on remote connection to PostgreSQL? There are some extra steps to turn it on for MS SQL and MySQL, so I figured it should be the same for Postgre. Thank you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to

Re: [GENERAL] effective_io_concurrency increasing

On Mon, Jun 19, 2017 at 10:36 AM, Jeff Janes wrote: > If you have a RAID, set it to the number of spindles in your RAID and forget > it. It is usually one of the less interesting knobs to play with. (Unless > your usage pattern of the database is unusual and exact fits the

Re: [GENERAL] Postgres Data Encryption Using LUKS with dm-crypt ?

On 06/19/2017 12:40 AM, Scott Marlowe wrote: On Sun, Jun 18, 2017 at 2:20 PM, Condor wrote: What I should expect, what is good and bad things that can be happened. I've run Postgres on a LUKS volume for a few years now and it's all been pretty quiet. One challenge is you

Re: [GENERAL] effective_io_concurrency increasing

On Sun, Jun 18, 2017 at 7:09 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Sun, Jun 18, 2017 at 6:02 PM, Patrick B > wrote: > >> Hi guys. >> >> I just wanna understand the effective_io_concurrency value better. >> >> My current Master database server

Re: [GENERAL] inheritence children with integer columns of differing width

Justin Pryzby writes: > I wondered if anyone had considered allowing inheritence children to have > different column types than the parent (and each other). No, and it's not sane to consider it. > I'm thinking of the trivial (?) case of smallint/int/bigint. What's trivial

[GENERAL] inheritence children with integer columns of differing width

I wondered if anyone had considered allowing inheritence children to have different column types than the parent (and each other). I'm thinking of the trivial (?) case of smallint/int/bigint. Reason is that when we load data which exceeds the theshold for the current data type we have to promote

Re: [GENERAL] Postgres Data Encryption Using LUKS with dm-crypt ?

On Sun, Jun 18, 2017 at 2:20 PM, Condor wrote: > Hello ppl, > > a few years ago I asked the same question but did not receive valued answers > and we use different way to realize the project. > Today I wanna ask did some one do it and most important for me, can some one > share

Re: [GENERAL] effective_io_concurrency increasing

Am 19.06.2017 um 03:02 schrieb Patrick B: Hi guys. I just wanna understand the effective_io_concurrency value better. My current Master database server has 16 vCPUS and I use effective_io_concurrency = 0. What can be the benefits of increasing that number? Also, do you guys have any