Locks analysis after-the-fact

Hello all, PostgreSQL version 9.4.17 We have a number of queries running on the same DB from many systems. Among other things, we need to INSERT / UPDATE on a table based on external events - this is done via a pool of 5 SQL connections which process an average of 500 events / second. The tabl

Re: Locks analysis after-the-fact

On 2018-04-27 10:55, Olivier Macchioni wrote: > Does anyone have an idea on how to process in such a case? > Log statments too. :)

Re: Locks analysis after-the-fact

> On 27 Apr 2018, at 11:58, Olleg Samoylov wrote: > > On 2018-04-27 10:55, Olivier Macchioni wrote: >> Does anyone have an idea on how to process in such a case? >> > Log statments too. :) > Thank you - I was afraid of such an answer and on the load it may generate... I'll give it a try duri

Re: Asynchronous Trigger?

Try to look at PGQ from SkyTools. On 2018-03-30 01:29, Cory Tucker wrote: Is it possible to have the execution of a trigger (or any function) not block the completion of the statement they are associated with?

Re: pg_stat_statements : how to catch non successfully finished statements ?

Hello, On Thu, Apr 26, 2018 at 01:24:25PM -0700, legrand legrand wrote: > Hello all, > > I was wondering if there is a hook to collect non successfully finished SQL > statements in pg_stat_statements (timed-out, cancelled, killed, or simply > errored) ? Some time ago I looked for a such hook. My

invalid byte sequence for encoding "UTF8": 0xff

I just did this on all my import dump files: perl -p -i -e 's/\xff//g' *.dump Kris PUBLIC RECORDS NOTICE: In accordance with NRS Chapter 239, this email and responses, unless otherwise made confidential by law, may be subject to the Nevada Public Records laws and may be disclosed to the public

Re: Parameter passing in trigger function write in C

On 04/26/2018 06:48 PM, a wrote: hey thanks mate, I have red that. But the actual data retirement seems to be combination of that and the SPI_get* functions. I'm still testing on it. By the way, if I can pass parameters that is out of the scope of sql statement?? Can you be more specific abo

Re: Long running INSERT+SELECT query

On Thu, 26 Apr 2018 19:13:17 +0300 Vitaliy Garnashevich wrote: > We're trying to populate a table with aggregated data from other > tables. For that we're running a huge INSERT+SELECT query which joins > several tables, aggregates values, and then inserts the results into > another table. The pro

Re: Parameter passing in trigger function write in C

Yep, I wanna pass the brief reason of manipulating(update, insert or delete), name and password of the manipulator (This might be done by database itself). -- Original -- From: "Adrian Klaver"; Date: Friday, Apr 27, 2018 10:16 PM To: "a"<372660...@qq.com>; "pgs

Re: Long running INSERT+SELECT query

On Thu, 26 Apr 2018 21:08:01 +0300 Vitaliy Garnashevich wrote: >     INSERT INTO cmdb_sp_usage_history >   (created_by, updated_by, created_on, updated_on, mod_count, >   summary_on, quarter, product, used_from, "user", >   keystrokes, minutes_in_use, times_started, avg_keystrokes

Re: Long running INSERT+SELECT query

On Thu, 26 Apr 2018 23:32:33 +0300 Vitaliy Garnashevich wrote: > The aggregated table has hundreds of millions of rows, and the query > runs for many hours (which is one of the reasons why it's better not > to fail). I really doubt that row level locking would work. That > would be a lot of RAM

Re: Parameter passing in trigger function write in C

On 04/27/2018 07:25 AM, a wrote: Yep, I wanna pass the brief reason of manipulating(update, insert or https://www.postgresql.org/docs/10/static/trigger-interface.html " tg_event Describes the event for which the function is called. You can use the following macros to examine tg_event ..

Re: Locks analysis after-the-fact

Olleg Samoylov writes: > On 2018-04-27 10:55, Olivier Macchioni wrote: >> Does anyone have an idea on how to process in such a case? > Log statments too. :) Another idea is to get the app to set application_name differently for each session (and be sure to include %a in log_line_prefix). It mig

Re: Rationale for aversion to the central database?

On Sun, 8 Apr 2018 14:39:49 -0700 Guyren Howe wrote: > I am a Rails developer at a medium-large size company. I’ve mostly > worked at smaller companies. I’ve some exposure to other web > development communities. > > When it comes to databases, I have universally encountered the > attitude that o

Re: decompose big queries

> Hi, > I want to know what are the best practice to use in order to > decompose a big query which contains so many joins.Is it recommended > to use stored procedures ? or is there any other solution? The main problem with SP's is that they can really screw up optimization. Most of the time you'l

Re: Rationale for aversion to the central database?

All, Just chiming in . . . we’ve taken a somewhat different approach and actually encourage our programmers to build out thier own DBs. We’re using Postgres to aggregate many varied datasources into postgres as a cahing system, and then develop against this aggregated data. Yes, we understan

Re: Rationale for aversion to the central database?

On Apr 27, 2018, at 8:45 , Basques, Bob (CI-StPaul) wrote: > > Just chiming in . . . we’ve taken a somewhat different approach and actually > encourage our programmers to build out thier own DBs. We’re using Postgres > to aggregate many varied datasources into postgres as a cahing system, an

Re: Asynchronous Trigger?

On Thu, Mar 29, 2018 at 5:29 PM, Cory Tucker wrote: > Is it possible to have the execution of a trigger (or any function) not > block the completion of the statement they are associated with? > > A pattern I had hoped to implement was to do a quick update of rows that > signaled they needed attent

Re: Rationale for aversion to the central database?

On Apr 27, 2018, at 10:46 AM, Guyren Howe mailto:guy...@gmail.com>> wrote: On Apr 27, 2018, at 8:45 , Basques, Bob (CI-StPaul) mailto:bob.basq...@ci.stpaul.mn.us>> wrote: Just chiming in . . . we’ve taken a somewhat different approach and actually encourage our programmers to build out thie

Re: Can PostgreSQL create new WAL files instead of reusing old ones?

On Wed, Apr 18, 2018 at 09:34:50AM -0400, Vick Khera wrote: > On Wed, Apr 18, 2018 at 12:35 AM, Michael Paquier wrote: > > > That looks like a rather difficult problem to solve in PostgreSQL > itself, as the operator running the cluster is in charge of setting up > the FS options whi

Re: Long running INSERT+SELECT query

Everybody thanks for the suggestions! We're going to try using "SELECT 1 FROM table FOR KEY SHARE" for each of the tables, which are referenced by results, before running the big query. That should be up to a million of rows in total. It will probably not cover the case when a record is INSERT

Re: Can PostgreSQL create new WAL files instead of reusing old ones?

I was wondering the same. The WAL writer is overwriting the same inodes again and again, so block COW logic should only happen once: at allocation. I'm no expert: does XFS track COW based on path (ugh?) Maybe I'm crazy but here's a possible workaround if the problem is effectively at that lev

Re: Asynchronous Trigger?

As suggested, note in ToDo table, also maybe look at LISTEN and NOTIFY and have a job runner process LISTENing (and cleaning up the queue, or, marking an item as in progress if you've multiple workers) The work queue table is to help maintain state...if noone is LISTENing then the table acts as ba

Re: Can PostgreSQL create new WAL files instead of reusing old ones?

On 2018-04-27 12:28:25 -0400, Bruce Momjian wrote: > On Wed, Apr 18, 2018 at 09:34:50AM -0400, Vick Khera wrote: > > On Wed, Apr 18, 2018 at 12:35 AM, Michael Paquier > > wrote: > > > > > > That looks like a rather difficult problem to solve in PostgreSQL > > itself, as the operator run

Re: Long running INSERT+SELECT query

On Fri, 27 Apr 2018 19:38:15 +0300 Vitaliy Garnashevich wrote: > We're going to try using "SELECT 1 FROM table FOR KEY SHARE" for each of > the tables, which are referenced by results, before running the big > query. That should be up to a million of rows in total. It will probably > not cover

Re: Asynchronous Trigger?

On Fri, 2018-04-27 at 11:25 -0600, Michael Loftis wrote: > As suggested, note in ToDo table, also maybe look at LISTEN and > NOTIFY and have a job runner process LISTENing We use a simple python process to listen with a PostgreSQL cursor for NOTIFY events, and push them into RabbitMQ (message bro

Re: Rationale for aversion to the central database?

On Sun, Apr 8, 2018 at 4:39 PM, Guyren Howe wrote: > I am a Rails developer at a medium-large size company. I’ve mostly worked at > smaller companies. I’ve some exposure to other web development communities. > > When it comes to databases, I have universally encountered the attitude that > one sho

Re: Long running INSERT+SELECT query

Steven Lembark writes: > On Fri, 27 Apr 2018 19:38:15 +0300 > Vitaliy Garnashevich wrote: > >> We're going to try using "SELECT 1 FROM table FOR KEY SHARE" for each of >> the tables, which are referenced by results, before running the big >> query. That should be up to a million of rows in to

Re: Rationale for aversion to the central database?

    On April 24, 2018 07:27:59 am PDT, "Sam Gendler" wrote:     On Sun, Apr 8, 2018 at 15:37 [1]g...@luxsci.net <[2]g...@luxsci.net> wrote:     On April 8, 2018 02:40:46 pm PDT, "Guyren Howe" <[3]guy...@gmail.com> wrote: One advantage to using logic and functions in  the db is that you

Re: Rationale for aversion to the central database?

On 04/27/2018 05:52 PM, g...@luxsci.net wrote: On April 24, 2018 07:27:59 am PDT, "Sam Gendler" wrote: On Sun, Apr 8, 2018 at 15:37 g...@luxsci.net mailto:g...@luxsci.net>> wrote: On April 8, 2018 02:40:46 pm PDT, "Guyren Howe" mailto:guy...@gmail.com>> wrote

Re: Rationale for aversion to the central database?

> On Sun, 8 Apr 2018 14:39:49 -0700 > Guyren Howe mailto:guy...@gmail.com>> wrote: > > When it comes to databases, I have universally encountered the > attitude that one should treat the database as a dumb data bucket. > There is a *very* strong aversion to putting much of any business > logic in

Re: Rationale for aversion to the central database?

On 2018-04-27 22:52:39 +, g...@luxsci.net wrote: > Perhaps I'm extreme. In my ideal world, developers might not even know table > names! I'm kidding ,sorta... If they don't know the table names, how can they write those stored procedures? hp -- _ | Peter J. Holzer| we build