Re: [GENERAL] Upgrade postgres cluster on FreeBSD using pg_upgrade

On Mon, Jul 20, 2015 at 1:57 AM, Joseph Kregloh wrote: > It's actually simpler than you think. Here are the steps that I follow: > > 1. Install 9.4 in another directory, if you compile it from the ports make > suere you add the PREFIX flag. For example: cd > /usr/ports/databases/postgresql94-serv

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

Spiros Ioannou writes: > We upgraded to 9.4.4 from 8.4 We have tried 9.3.x in the past with success. > Unfortunately in 9.4 gets stuck 2-3 times a week. > Lots of queries seem stuck, that is they have waiting:false. Queries can be > COMMIT or INSERT. > No blocking locks seem to exist. > strace re

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

p...@cmicdo.com writes: > Has anyone successfully built Python 2 and 3 into the same installation > of Postgres 9.4.4? I do not think you can do it like this: > make distclean > ./configure --with-openssl --with-libxml --with-libxslt --with-python > PYTHON=/usr/bin/python > make > ./configure --

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

Os is debian 6

[GENERAL] Lots of stuck queries after upgrade to 9.4

We upgraded to 9.4.4 from 8.4 We have tried 9.3.x in the past with success. Unfortunately in 9.4 gets stuck 2-3 times a week. Lots of queries seem stuck, that is they have waiting:false. Queries can be COMMIT or INSERT. No blocking locks seem to exist. strace reveals that all stuck processes they

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

Has anyone successfully built Python 2 and 3 into the same installation of Postgres 9.4.4? I tried it today on Ubuntu 10.04, Python 2.6.5, Python 3.1.2 and got and error about undefined symbol: PyString_AsString. The Python docs say that PyString_* have been renamed to PyBytes_* and I find referen

Re: [GENERAL] Upgrade postgres cluster on FreeBSD using pg_upgrade

It's actually simpler than you think. Here are the steps that I follow: 1. Install 9.4 in another directory, if you compile it from the ports make suere you add the PREFIX flag. For example: cd /usr/ports/databases/postgresql94-server/ && make install clean PREFIX=/opt, this will install 9.4 on th

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

Melvin Davidson wrote: > Aside from Tom Lane's comments, it seems to me you are reinventing the wheel > by generating random values for keys. Why not just use UUID > http://www.postgresql.org/docs/9.5/static/datatype-uuid.html > or serial > http://www.postgresql.org/docs/9.5/static/datatyp

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

Rafal Pietrak wrote: > CREATE TABLE vouchers (a_voucher bigint PRIMARY KEY default > (random()*10)::bigint, issued date default now(), .); Generators of truly unique pseudo-random values provide a better ground for this. Consider for example: https://wiki.postgresql.org/wiki/

[GENERAL] Upgrade postgres cluster on FreeBSD using pg_upgrade

Hi I need to upgrade a PG cluster from 9.1 to 9.4 running on dedicated FreeBSD 9.2 server. Earlier I followed the simple pg_dump / pg_restore, but now that the cluster has grown to around 700 GB (1 TB total HD size), I am now inclined towards using pg_upgrade. pg_upgrade requires both binary to b

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

Aside from Tom Lane's comments, it seems to me you are reinventing the wheel by generating random values for keys. Why not just use UUID http://www.postgresql.org/docs/9.5/static/datatype-uuid.html or serial http://www.postgresql.org/docs/9.5/static/datatype-numeric.html#DATATYPE-SERIAL? Wouldn't

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

On 07/19/2015 08:04 AM, Rafal Pietrak wrote: W dniu 19.07.2015 o 16:33, Adrian Klaver pisze: On 07/19/2015 06:47 AM, Rafal Pietrak wrote: Hi, W dniu 19.07.2015 o 14:10, Geoff Winkless pisze: On 19 July 2015 at 11:30, Rafal Pietrak [---] Although "a random" can duplicate its pr

Re: [GENERAL] IO in constraint trigger

Andreas Joseph Krogh writes: > We're planning to move away from Blobs (stored as OID) and use files with > path/URI stored in DB. For this to be reliable we're planning to do rename() > (which is atomic) in a constraint trigger, which triggers on commit. That seems broken on its face to me. Th

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

W dniu 19.07.2015 o 16:33, Adrian Klaver pisze: > On 07/19/2015 06:47 AM, Rafal Pietrak wrote: >> Hi, >> >> W dniu 19.07.2015 o 14:10, Geoff Winkless pisze: >>> On 19 July 2015 at 11:30, Rafal Pietrak > Although "a random" can duplicate its previous values, "my random(s)" >> (which are created fo

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

On 07/19/2015 06:47 AM, Rafal Pietrak wrote: Hi, W dniu 19.07.2015 o 14:10, Geoff Winkless pisze: On 19 July 2015 at 11:30, Rafal Pietrak mailto:ra...@ztk-rp.eu>> wrote: when I have to invent/introduce additional features/columns/attributes (like a key in addition to a sequence),

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

Hi, W dniu 19.07.2015 o 14:10, Geoff Winkless pisze: > On 19 July 2015 at 11:30, Rafal Pietrak > wrote: > > when I have to invent/introduce additional > features/columns/attributes (like a key in addition to a sequence), > which are not required by the design,

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

On 19 July 2015 at 11:30, Rafal Pietrak wrote: > when I have to invent/introduce additional > features/columns/attributes (like a key in addition to a sequence), > which are not required by the design, but necessary for implementation) > is a workaround (almost by definition). > I'm sorry that y

[GENERAL] IO in constraint trigger

Hi.   We're planning to move away from Blobs (stored as OID) and use files with path/URI stored in DB. For this to be reliable we're planning to do rename() (which is atomic) in a constraint trigger, which triggers on commit. The plan is to write stuff to a file (with at tmp-filename) in the app

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

Hi, W dniu 19.07.2015 o 10:27, Geoff Winkless pisze: > On 19 July 2015 at 09:11, Rafal Pietrak > wrote: > > I'm not particularly fond of using functions to accessing RDBMS instead > of tables. > > And I'm not particularly fond of "workarounds". > > > Use a

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

On 19 July 2015 at 09:11, Rafal Pietrak wrote: > I'm not particularly fond of using functions to accessing RDBMS instead > of tables. > > And I'm not particularly fond of "workarounds". > Use a combination of factors (a sequence ID and the key) for your authorization. So in the extremely unlikel

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

Hi, W dniu 19.07.2015 o 09:33, Charles Clavadetscher pisze: [---] >> 2. with current (as of 9.5) implementation I think I can always "ON CONFLICT >> DO NOTHING", and retry the INSERT from application level. > > An UPSERT is "try an INSERT and if there is a conflict, do nothing or UPDA

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

Hello > I've just started to read through postgres-9.5 "what's new" ... before giving > it > a try. The "insert ... on conflict do update" is particularly atractive to > me; but I > was wondering why it does not cover the third usage scenario of action that a > programmer may need for a PK confl

[GENERAL] INSERT ... ON CONFLICT DO UPDATE

Hello, I've just started to read through postgres-9.5 "what's new" ... before giving it a try. The "insert ... on conflict do update" is particularly atractive to me; but I was wondering why it does not cover the third usage scenario of action that a programmer may need for a PK conflict during in