RE: Row locks, SKIP LOCKED, and transactions

> (Or you could use serializable mode, but that feels like using a hammer to > swat a fly.) Do you mean the serializable transaction isolation level? Because that doesn't work either. Here (finally) is a tiny repro case. You'll need 2 psql sessions (S1, S2): S1: CREATE TABLE t (id integer):

RE: Row locks, SKIP LOCKED, and transactions

> Huh. SERIALIZABLE shouldn't allow two transactions to see no result row > for a given ID and then insert a result row for that ID. One of those > transactions should have to roll back, because otherwise it'd be > incompatible with both serial orderings of the two transactions. Sorry for the

RE: Row locks, SKIP LOCKED, and transactions

>> * I observe this even if I crank up the transaction isolation level to >> repeatable read and serializable. >> >> >> I'm wondering if row locks are not obeying the same transactional semantics >> as row data, >Gotta believe it is this: >

Row locks, SKIP LOCKED, and transactions

Hi all, I'm seeing some unexpected behaviour with SELECT ... FOR UPDATE SKIP LOCKED, and having finding it tricky to boil it down to a simple repro case as there's almost certainly a race condition somewhere (more later). So I thought I would ask if what I'm doing is unsupported (or just plain

RE: Preventing in-session 'set role' commands

Maybe check out the set_user extension: https://github.com/pgaudit/set_user Steve.

RE: How to run a task continuously in the background

pg_cron, perhaps? https://www.citusdata.com/blog/2016/09/09/pgcron-run-periodic-jobs-in-postgres/ I _think_ it ticks all three of your boxes. Steve. This email is confidential. If you are not the intended recipient, please advise us immediately and delete this message. The registered name

RE: Incremental aggregate/rollup strategy advice

There are a couple of extensions that might help you: PipelineDB[1]: Their “Continuous Views” could be useful. A big caveat here is that PipelineDB’s future is uncertain[2], though. I haven’t used it myself, but it looks like you could put triggers onto your existing tables to insert data in

RE: Questions about btree_gin vs btree_gist for low cardinality columns

> In the case of a single column with a small set of distinct values over a > large set of rows, how would a Bloom filter be preferable to, say, a GIN > index on an integer value?  I don't think it would - it's probably better suited to the multi-column case you described previously. > I have

RE: why select count(*) consumes wal logs

> How to find out which parameter were used with initdb pg_controldata -D | grep sum ...should give you something like: Data page checksum version: 0 ...and 0 means off. Similarly, from SQL: select data_page_checksum_version from pg_control_init() Steve. This email is

RE: why select count(*) consumes wal logs

As long as you don’t have page checksums turned on, you can prevent this by turning off wal_log_hints. Steve. This email is confidential. If you are not the intended recipient, please advise us immediately and delete this message. The registered name of Cantab- part of GAM Systematic is

RE: Advice on machine specs for growth

> Disable memory overcommit and set swappiness to 0 on database servers. Just to be clear, are you suggesting (on linux) setting overcommit_memory = 2 but leaving overcommit_ratio alone, which defaults to 50%? I’ve tried setting them to 2 and 0% respectively in the past and it didn’t end well…

RE: Extremely slow autovacuum:vacuum to prevent wraparound

From: Rijo Roy [mailto:rjo_...@yahoo.com] Sent: 11 July 2018 15:14 To: Steven Winfield Cc: pgsql-gene...@postgresql.org Subject: RE: Extremely slow autovacuum:vacuum to prevent wraparound Hi Steve, Apologies, I misread it as 2 billion, it is as you said set as 200 million which is the default

RE: Extremely slow autovacuum:vacuum to prevent wraparound

From: Rijo Roy [mailto:rjo_...@yahoo.com] Sent: 11 July 2018 14:30 To: srkris...@yahoo.com Cc: pgsql-gene...@postgresql.org Subject: Re: Extremely slow autovacuum:vacuum to prevent wraparound No, I have 3 sessions of autovacuum against 3 tables which is doing a Vacuum to prevent wraparound as

RE: JSONB arrays

From: Victor Noagbodji [mailto:vnoagbo...@amplify-nation.com] Sent: 28 June 2018 11:28 To: pgsql-general Subject: JSONB arrays Hey people, I ended up with the following to check (or similar to return) the intersection of two list of values on a JSONB object:

RE: Index Gin Creation is taking long time..

From: Anto Aravinth [mailto:anto.aravinth@gmail.com] Sent: 28 June 2018 11:34 To: pgsql-generallists.postgresql.org Subject: Index Gin Creation is taking long time.. Hello, I'm trying to create an index: create index search_idx on so2 using gin (to_tsvector('english',posts)); Looks

RE: Insert data if it is not existing

Winfield Cc: pgsql-generallists.postgresql.org Subject: Re: Insert data if it is not existing thanks for the response Steven. Will ON CONFLICT DO UPDATE/NOTHING if there's no error? On Wed, May 23, 2018 at 5:43 PM, Steven Winfield <steven.winfi...@cantabcapital.com<mailto:steven

RE: Insert data if it is not existing

INSERT … ON CONFLICT DO UPDATE … is probably what you want, perhaps using a specified unique index/constraint: https://www.postgresql.org/docs/10/static/sql-insert.html#SQL-ON-CONFLICT Steve. From: tango ward [mailto:tangowar...@gmail.com] Sent: 23 May 2018 10:04 To:

Many Backends stuck in wait event IPC/ParallelFinish

Hi, We just had an incident on one of our non-production databases where 14 unrelated queries were all hung in wait event IPC / ParallelFinish. We had systematically called pg_cancel/terminate_backend on all other backends except these (and the autovacuum process mentioned below) to make sure

RE: A little RULE help?

On 01/25/2018 03:14 PM, Steven Winfield wrote: > > https://www.postgresql.org/docs/10/static/rules-triggers.html<https://www.postgresql.org/docs/10/static/rules-triggers.html> > > “For the things that can be implemented by both, which is best depends > on the usage of th

RE: A little RULE help?

Maybe this?: https://www.postgresql.org/docs/10/static/sql-createrule.html "There is a catch if you try to use conditional rules for complex view updates: there must be an unconditional INSTEAD rule for each action you wish to allow

RE: A little RULE help?

> Don't use RULEs. HTH. Why not?

A little RULE help?

Hi all, I'm trying to create a temporal table, from which data is never really deleted but each row "exists" for only a certain period of time, and a view of this table showing the currently "live" rows. I would also like the view to accept INSERT, UPDATE, and DELETEs, and perform the relevant