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 chose from UI.
>
> In my code sn
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.
>
>Try perhaps logical replication
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 ex
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 state STARTED
> STATEMENT: SAV
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: SAVEPOINT_per_query_svp;DEALLOCA
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 hu
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 ther
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
> bandwidth, as well as sa
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 more compelling, so ho
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. I don't think that th
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 imagine prefetching heap pag
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 all that common of a sit
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. The
> problem is that
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). Would you
> expect any o
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. (Unless
> > your usage patte
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
> tuples: 43524292 removed,
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 ) notes
>> FROM tickets
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
>>> array_agg(to_json(row(notedate,username,note)))
>>> FROM sabrenotes
>>> INNER JOIN users
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
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 su
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
> 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
you expect any obvious pe
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 no. For
session-data pl
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 {
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 VACUUM FREEZE ANALYZ
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 |
> substr
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 certain
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
|
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 magic in order to turn on remote connection to PostgreSQL?
> >>
> >> T
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 steps to turn it on for MS SQL and MySQL, so I
>> fig
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 play with.
> (Unless
> > your usage pattern o
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 "listen_addresses"
https://www.p
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
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 above
> pattern.)
Isn'
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 need to supply the p
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 has 16 vCPUS and I use
>>
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 about that? Consider
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
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 his experience ?
>
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 recom
40 matches
Mail list logo
