Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

On Mon, Jul 20, 2015 at 7:01 AM, Geoff Winkless wrote: > On 20 July 2015 at 14:33, Rafal Pietrak wrote: > >> If I'm not mistaken, the conclusions from posts in this thread are: >> >> 3. there are methods (like cryptographic "random" sequence), which >> guarantee no conflicts. So one should resor

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

Geoff Winkless wrote: > On 20 July 2015 at 14:33, Rafal Pietrak wrote: > > > If I'm not mistaken, the conclusions from posts in this thread are: > > > > 3. there are methods (like cryptographic "random" sequence), which > > guarantee no conflicts. So one should resort to that. > > > > > Some web

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

On 20 July 2015 at 15:07, Alvaro Herrera wrote: > Not sure what type of indexes would be affected by that problem, but I > don't think Postgres' btrees would be. > I admit it's not really my area.​ ​Take it up with Drew Blas, I guess :) https://blog.starkandwayne.com/2015/05/23/uuid-primary-ke

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

Geoff Winkless wrote: > On 20 July 2015 at 14:33, Rafal Pietrak wrote: > > > If I'm not mistaken, the conclusions from posts in this thread are: > > > > 3. there are methods (like cryptographic "random" sequence), which > > guarantee no conflicts. So one should resort to that. > > > > > Some web

Re: [GENERAL] Lots of stuck queries after upgrade to 9.4

On 2015-07-20 17:00:52 +0300, Spiros Ioannou wrote: > FYI we have an 9.3.5 with commit_delay = 4000 and commit_siblings = 5 with > a 8TB dataset which seems fine. (Runs on different - faster hardware > though). 9.4 has a different xlog insertion algorithm (scaling much better), so that unfortunate

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

On 20 July 2015 at 14:33, Rafal Pietrak wrote: > If I'm not mistaken, the conclusions from posts in this thread are: > > 3. there are methods (like cryptographic "random" sequence), which > guarantee no conflicts. So one should resort to that. > > Some web research suggests that random sequences

Re: [GENERAL] Lots of stuck queries after upgrade to 9.4

FYI we have an 9.3.5 with commit_delay = 4000 and commit_siblings = 5 with a 8TB dataset which seems fine. (Runs on different - faster hardware though). *Spiros Ioannou IT Manager, inAccesswww.inaccess.com M: +30 6973-903808T: +30 210-6802-358* On 20 July 2015 at 1

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

If I'm not mistaken, the conclusions from posts in this thread are: 1. recognizing of a "RETRY" action, as a separate case of "ON CONFLICT" transaction continuation is not generally appreciated. 2. I shouldn't expect any "hidden corruption/performance" obstacles when simply re-attempting of an IN

Re: [GENERAL] Lots of stuck queries after upgrade to 9.4

Debian 6, x64, HP Proliant, dbsize: 3TB on EMC AX-4i (iscsi), 16 cores, 24GB RAM. kernel: Linux vserver11 2.6.32-5-xen-amd64 #1 SMP Sun Dec 7 22:52:42 UTC 2014 x86_64 GNU/Linux I attach postgresql.conf *Spiros Ioannou IT Manager, inAccesswww.inaccess.com M: +30 69

Re: [GENERAL] Lots of stuck queries after upgrade to 9.4

Hi, On 2015-07-20 15:58:33 +0300, Spiros Ioannou wrote: > Happened again, another backtrace from a COMMIT process. I changed the > commit_delay to 0 (it was 4000 to help with our storage) and will report > back. What hardware & OS is this happening on? Regards, Andres -- Sent via pgsql-gener

Re: [GENERAL] Lots of stuck queries after upgrade to 9.4

Happened again, another backtrace from a COMMIT process. I changed the commit_delay to 0 (it was 4000 to help with our storage) and will report back. Already logging to debuglog.txt. #0 0x7f47b0789ec7 in semop () from /lib/libc.so.6 #1 0x7f47b2513d91 in PGSemaphoreLock (sema=0x7f47abfe7e

Re: [GENERAL] Postgres Recovery

On Mon, Jul 20, 2015 at 7:00 PM, Ramesh T wrote: > Hi All, > What i need to know for postgres recovery..?let me know in detail. Documentation is always a good start: http://www.postgresql.org/docs/devel/static/backup.html -- Michael -- Sent via pgsql-general mailing list (pgsql-gene

[GENERAL] Postgres Recovery

Hi All, What i need to know for postgres recovery..?let me know in detail.

Re: [GENERAL] Lots of stuck queries after upgrade to 9.4

On 07/20/2015 03:01 PM, Andres Freund wrote: Heikki, On 2015-07-20 13:27:12 +0200, Andres Freund wrote: On 2015-07-20 13:22:42 +0200, Andres Freund wrote: Hm. The problem seems to be the WaitXLogInsertionsToFinish() call in XLogFlush(). These are the relevant stack traces: db9lock/debuglog-c

Re: [GENERAL] Lots of stuck queries after upgrade to 9.4

Heikki, On 2015-07-20 13:27:12 +0200, Andres Freund wrote: > On 2015-07-20 13:22:42 +0200, Andres Freund wrote: > > Hm. The problem seems to be the WaitXLogInsertionsToFinish() call in > > XLogFlush(). > > These are the relevant stack traces: > db9lock/debuglog-commit.txt > #2 0x7f7405bd44f4

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

For this purpose, I have seen it recommended to use a UUID instead of a randomly generated integer. I do this myself for production applications and over millions of records I have yet to log a conflict. Also, as stated above, you could create a plpgsql function which would achieve exactly what you

Re: [GENERAL] Lots of stuck queries after upgrade to 9.4

On 2015-07-20 13:22:42 +0200, Andres Freund wrote: > Hm. The problem seems to be the WaitXLogInsertionsToFinish() call in > XLogFlush(). These are the relevant stack traces: db9lock/debuglog-commit.txt #2 0x7f7405bd44f4 in LWLockWaitForVar (l=0x7f70f2ab6680, valptr=0x7f70f2ab66a0, oldval=, n

Re: [GENERAL] Problem building both Python 2 and 3 into Postgres 9.4.4?

That worked, thank you. The Tip in 43.1 did not explain in that much detail. I searched but could not find that explained anywhere in the docs. Your paragraph would be a nice enhancement to the tip. PJ On Sun, 7/19/15, Tom Lane wrote: Subject: Re

Re: [GENERAL] Lots of stuck queries after upgrade to 9.4

On 2015-07-20 13:06:51 +0200, Alvaro Herrera wrote: > Spiros Ioannou wrote: > > Hi Tom, > > thank you for your input. The DB was stuck again, I attach all logs and > > stack traces. > > > > A stack trace from a COMMIT, an INSERT, an UPDATE, the wal writer, the > > writer, and a sequence. > > > >

Re: [GENERAL] Lots of stuck queries after upgrade to 9.4

Spiros Ioannou wrote: > Hi Tom, > thank you for your input. The DB was stuck again, I attach all logs and > stack traces. > > A stack trace from a COMMIT, an INSERT, an UPDATE, the wal writer, the > writer, and a sequence. > > Stracing the commit was stuck at: semop(3145761, {{12, -1, 0}}, 1 Hmm

Re: [GENERAL] Lots of stuck queries after upgrade to 9.4

Hi Tom, thank you for your input. The DB was stuck again, I attach all logs and stack traces. A stack trace from a COMMIT, an INSERT, an UPDATE, the wal writer, the writer, and a sequence. Stracing the commit was stuck at: semop(3145761, {{12, -1, 0}}, 1 Please tell me what else could I do to he

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

Hi Daniel: On Sun, Jul 19, 2015 at 9:03 PM, Daniel Verite wrote: > For SERIAL, it's too obvious to guess what is the next one, > so malicious people could claim access codes or vouchers > they don't own. Why don't you use encryption? Specifically only on the external side. You use a serial in th

[GENERAL] Way to get timeline

Hi all. What is the best way to get current timeline of host? Right now I can imagine two variants: 1. Do checkpoint and read it from control file. 2. Do something like "SELECT substr(pg_xlogfile_name(pg_current_xlog_location()), 1, 8)". Both variants seem to be a bit tricky. Is there a way be