[GENERAL] INSERT ... ON CONFLICT DO UPDATE

[Rafal Pietrak]

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 insert.

In my experience, most often I generate a random value for PK, with that
random value becoming a unique ticket like a voucher (related to
monetary value). for that I:

CREATE TABLE vouchers (a_voucher bigint PRIMARY KEY default
(random()*10)::bigint, issued date default now(), .);

Naturally:
1. A_VOUCHER range space is always significantly larger then currently
issued voucher count - so conflicts are rare.
2. with current (as of 9.5) implementation I think I can always "ON
CONFLICT DO NOTHING", and retry the INSERT from application level.

But it would be immenensly more comfortable if one could: "INSERT ... ON
CONFLICT (a_voucher) DO RETRY"; with semantics of that statement being:
1. prepare should check if there is a DFAULT for specified "conflict
column" (here: "a_voucher"), and fail if there isn't one.
2. prepare shoud check if the default is a VOLATILE function... or fail.
3. when all that pass, the prepared insert, when executed and with a
conflict, should be re-attempt with NEW call to that DEFAULT function of
the indicated CONFLICT column(s).
3. and there should be a /ETC/POSTGRES.CONF parameter limiting the
number of retries for a single conflict - as a programmer I know, that
if I need to retry more then twice, the space is too dense, always. So I
need to change the DFAULT function, not increase the retry_count ...
thus haveing DDS allowing the change to the DFAULT FUNCTION means it's
not necesary to allow for change of the RETRY_CONT (during database
life) - and when the later is in the CONFIG, the less it's prone to typo
errors of application authors.

Was the above considered for "ON CONFLICT" implementation before?

If so, can someone pls point me to critics it received.

If not: is it unreasonable? why?

-R


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Charles Clavadetscher]

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 insert.
> 
> In my experience, most often I generate a random value for PK, with that
> random value becoming a unique ticket like a voucher (related to monetary
> value). for that I:
> 
> CREATE TABLE vouchers (a_voucher bigint PRIMARY KEY default
> (random()*10)::bigint, issued date default now(), .);
> 
> Naturally:
> 1. A_VOUCHER range space is always significantly larger then currently issued
> voucher count - so conflicts are rare.
> 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 UPDATE 
some values of the existing record". The scenario that you suggest is not an 
UPSERT, because what you want to reach is to try a  new INSERT, hoping that 
this works.
What speak against using a sequence for the primary key column a_voucher? This 
would guarantee that you don't have a conflict.

> But it would be immenensly more comfortable if one could: "INSERT ... ON
> CONFLICT (a_voucher) DO RETRY"; with semantics of that statement being:
> 1. prepare should check if there is a DFAULT for specified "conflict column"
> (here: "a_voucher"), and fail if there isn't one.
> 2. prepare shoud check if the default is a VOLATILE function... or fail.
> 3. when all that pass, the prepared insert, when executed and with a conflict,
> should be re-attempt with NEW call to that DEFAULT function of the
> indicated CONFLICT column(s).
> 3. and there should be a /ETC/POSTGRES.CONF parameter limiting the
> number of retries for a single conflict - as a programmer I know, that if I 
> need
> to retry more then twice, the space is too dense, always. So I need to change
> the DFAULT function, not increase the retry_count ...
> thus haveing DDS allowing the change to the DFAULT FUNCTION means it's
> not necesary to allow for change of the RETRY_CONT (during database
> life) - and when the later is in the CONFIG, the less it's prone to typo 
> errors of
> application authors.
> 
> Was the above considered for "ON CONFLICT" implementation before?
> 
> If so, can someone pls point me to critics it received.
> 
> If not: is it unreasonable? why?

IMHO, as I mentioned, this is not an UPSERT use case, but maybe the 
implementors of the feature may have different arguments. You could implement 
that in a function instead of the application, if you prefer.

Bye
Charles




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Rafal Pietrak]

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 UPDATE 
> some values of the existing record". The scenario that you suggest is not an 
> UPSERT, because what you want to reach is to try a  new INSERT, hoping that 
> this works.
> What speak against using a sequence for the primary key column a_voucher? 
> This would guarantee that you don't have a conflict.
> 

It have to be random, since it barres a "sort of monetary" value. The
vouches are destined to be one-time authorization tokens, they have to
be harder to guess then those drawn from the sequence are.

[]
>>
>> If not: is it unreasonable? why?
> 
> IMHO, as I mentioned, this is not an UPSERT use case, but maybe the 
> implementors of the feature may have different arguments. You could implement 
> that in a function instead of the application, if you prefer.
> 

I'm not particularly fond of using functions to accessing RDBMS instead
of tables.

And I'm not particularly fond of "workarounds".

But if that usage scenario is not appreciated here, then guess I have to
live with what is available. And the availability of ON CONFLICT is a
great improvement anyway.

Thenx,

-R


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Geoff Winkless]

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 unlikely event that the random key isn't
unique, it doesn't matter. That's not a workaround, it's good design.

You're asking for a feature that is completely unnecessary and is easily
resolved. UPSERT is designed for a situation which is neither.

Geoff

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

[Rafal Pietrak]

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 combination of factors (a sequence ID and the key) for your
> authorization. So in the extremely unlikely event that the random key
> isn't unique, it doesn't matter. That's not a workaround, it's good design.

I'm sory. At this point I don't want to prolong the discussion (like
flaming), but I feel like having to defend myself a little.

Regarding the above: 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).

In my current implementations I captures (the rare) PK conflict
exceptions and redo the INSERT at application level. It works
sufficiently well. I was just curious if that usage scenario is
currently also covered by current ON CONFLICT, or not.

> 
> You're asking for a feature that is completely unnecessary and is easily
> resolved. UPSERT is designed for a situation which is neither.
> 

1. Despite possibly sounding like one, I wasn't actually asking for a
feature - I wasn't sure if that could possibly be implemented using
currently available postresql features. So I've just explained a usage
scenario (explaining the semantics using "invented pseudo syntax") which
I've experienced.

2. that usage scenario, IMHO wasn't obviously covered (as of my first
reading of "the upsert" implementation). It might have been covered ...
only I wasn't seeing it; so I've brought it up.

3. and obviously that usage scenario (despite my personal experience)
might actually be very rare - unworthy implementation effort and thus
qualifying for workarounds. This happen, I understand.

I hope this explains my point better.

-R


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] IO in constraint trigger

[Andreas Joseph Krogh]

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 application and to 
pass the absolute file-name down to the DB so the trigger can rename it ON 
COMMIT. This way we can use a naming-convention to figure out what files are OK 
and which are not (which can be deleted due to rolled back transactions).
 
I have found this extension: https://github.com/csimsek/pgsql-fio/
but it doesn't seem to support renaming.
 
Any advice on how to proceed?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 

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

[Geoff Winkless]

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 you feel defensive about this, and apologies for repeating
myself, but the fact that the random key can be duplicated means it should
not be used as a primary key, so using a sequence as a primary key is not a
workaround, it's a correction to the design.

Notwithstanding that, the reason UPSERT is required is because it's
possible that two competing transactions can end up fighting over an INSERT
and the workarounds that are required are either highly complex or not 100%
successful (eg
http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/).

Conversely, the workaround in the above case (even if you don't want to
change the primary key) is trivial - as you yourself described.

Geoff

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

[Rafal Pietrak]

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, but necessary for implementation)
> is a workaround (almost by definition).
> 
> 
> I'm sorry that you feel defensive about this, and apologies for
> repeating myself, but the fact that the random key can be duplicated
> means it should not be used as a primary key, so using a sequence as a
> primary key is not a workaround, it's a correction to the design. 

OK. I think I need to apology myself, too. I hope my defense wasn't too
fierce.

But I need to clearify one thing:

Although "a random" can duplicate its previous values, "my random(s)"
(which are created for this application purpose) cannot be duplicated
when it's stored in the database as "live active data". I understand,
that UNIQUE constraint is precisely the RDBMS tool to guarantee that.

Naturally, if I put a UNIQUE constraint on that column, or make it a PK,
is just a matter of choice here. That shouldn't rise concern. I just use
tools RDBMS provides for "semantics" the application needs.


> 
> Notwithstanding that, the reason UPSERT is required is because it's
> possible that two competing transactions can end up fighting over an
> INSERT and the workarounds that are required are either highly complex
> or not 100% successful (eg
> http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/). 
> 

I knew that Depesz publication before.

Actually it was the reason I've brought up "my usage scenario" here now.
I'm not as competent as Depesz, so:

1. I worry, that while restarting a failed INSERT transaction at
application level I miss something important (you people know by heart)
and unwillingly corrupt and/or "suboptimise" my application/data. (much
to the point Depesz described).

2. But, since the majority of the hard work of postgresql UPSERT
implementation is already done; I wanted to check out if the usage
scenario I point out falls into it as a "case", or is covered by it by
some "indiomatic SQL sequence", or otherwise. From current discussion I
gather: "its otherwise" - it isn't considered as applicable. (so I
concluded: I'll live with manual re-attempt of failed insert)

-R


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Adrian Klaver]

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),
 which are not required by the design, but necessary for implementation)
 is a workaround (almost by definition).


I'm sorry that you feel defensive about this, and apologies for
repeating myself, but the fact that the random key can be duplicated
means it should not be used as a primary key, so using a sequence as a
primary key is not a workaround, it's a correction to the design.


OK. I think I need to apology myself, too. I hope my defense wasn't too
fierce.

But I need to clearify one thing:

Although "a random" can duplicate its previous values, "my random(s)"
(which are created for this application purpose) cannot be duplicated
when it's stored in the database as "live active data". I understand,
that UNIQUE constraint is precisely the RDBMS tool to guarantee that.


From my perspective the issue is, you are using a 'unique' key 
generator that you know is not creating unique keys and then asking the 
database to make it right. Sort of like making a square peg fit a round 
hole by shaving the corners. It is possible but has sort of a messy feel 
to it.




Naturally, if I put a UNIQUE constraint on that column, or make it a PK,
is just a matter of choice here. That shouldn't rise concern. I just use
tools RDBMS provides for "semantics" the application needs.




Notwithstanding that, the reason UPSERT is required is because it's
possible that two competing transactions can end up fighting over an
INSERT and the workarounds that are required are either highly complex
or not 100% successful (eg
http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/).



I knew that Depesz publication before.

Actually it was the reason I've brought up "my usage scenario" here now.
I'm not as competent as Depesz, so:

1. I worry, that while restarting a failed INSERT transaction at
application level I miss something important (you people know by heart)
and unwillingly corrupt and/or "suboptimise" my application/data. (much
to the point Depesz described).

2. But, since the majority of the hard work of postgresql UPSERT
implementation is already done; I wanted to check out if the usage
scenario I point out falls into it as a "case", or is covered by it by
some "indiomatic SQL sequence", or otherwise. From current discussion I
gather: "its otherwise" - it isn't considered as applicable. (so I
concluded: I'll live with manual re-attempt of failed insert)


As noted upstream, what you want is not an UPSERT. An UPSERT is based on 
the premise that if you try an INSERT where the unique constraint 
already exists then the INSERT is turned into an UPDATE.


To be fair:

http://www.postgresql.org/docs/9.5/static/sql-insert.html
"
 ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome - 
provided there is no independent error, one of those two outcomes is 
guaranteed, even under high concurrency. This feature is also known as 
UPSERT"


So an UPSERT is just one feature of ON CONFLICT. The other being DO 
NOTHING. Therefore I could see an argument made for adding other ON 
CONFLICT clauses. How difficult/plausible that would be is above my 
level of expertise.





-R





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Rafal Pietrak]

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 for this application purpose) cannot be duplicated
>> when it's stored in the database as "live active data". I understand,
>> that UNIQUE constraint is precisely the RDBMS tool to guarantee that.
> 
> From my perspective the issue is, you are using a 'unique' key generator
> that you know is not creating unique keys and then asking the database
> to make it right. Sort of like making a square peg fit a round hole by
> shaving the corners. It is possible but has sort of a messy feel to it.

Hmmm, yes.

Yet, I don't feel guilty as much, since that is quite similar to a
unique key on database "username", while the "generator" (human mind)
does not guarantee that. The DB just makes sure it does.

[--]
> 
> So an UPSERT is just one feature of ON CONFLICT. The other being DO
> NOTHING. Therefore I could see an argument made for adding other ON
> CONFLICT clauses. How difficult/plausible that would be is above my
> level of expertise.
> 

Mine too. But I'd say that the above wording exactly makes the point I
was trying to make. Thank you.

-R


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] IO in constraint trigger

[Tom Lane]

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.  There is *no* certainty that the
transaction will commit just because it's gotten to the point of running
commit triggers.  You will end up with files that are not in sync with
the database.

> The plan 
> is to write stuff to a file (with at tmp-filename) in the application and to 
> pass the absolute file-name down to the DB so the trigger can rename it ON 
> COMMIT. This way we can use a naming-convention to figure out what files are 
> OK 
> and which are not (which can be deleted due to rolled back transactions).

Safer to design it like VACUUM, ie periodically remove old files that
don't have database entries.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Adrian Klaver]

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 previous values, "my random(s)"
(which are created for this application purpose) cannot be duplicated
when it's stored in the database as "live active data". I understand,
that UNIQUE constraint is precisely the RDBMS tool to guarantee that.


 From my perspective the issue is, you are using a 'unique' key generator
that you know is not creating unique keys and then asking the database
to make it right. Sort of like making a square peg fit a round hole by
shaving the corners. It is possible but has sort of a messy feel to it.


Hmmm, yes.

Yet, I don't feel guilty as much, since that is quite similar to a
unique key on database "username", while the "generator" (human mind)
does not guarantee that. The DB just makes sure it does.


I think the argument to be made here is you have no control over what 
people choose as a username, you do have control over what your key 
generator outputs.




[--]


So an UPSERT is just one feature of ON CONFLICT. The other being DO
NOTHING. Therefore I could see an argument made for adding other ON
CONFLICT clauses. How difficult/plausible that would be is above my
level of expertise.



Mine too. But I'd say that the above wording exactly makes the point I
was trying to make. Thank you.

-R




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Melvin Davidson]

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 that simplify things by insuring uniqueness?

On Sun, Jul 19, 2015 at 11:12 AM, Adrian Klaver 
wrote:

> 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 previous values, "my random(s)"
 (which are created for this application purpose) cannot be duplicated
 when it's stored in the database as "live active data". I understand,
 that UNIQUE constraint is precisely the RDBMS tool to guarantee that.

>>>
>>>  From my perspective the issue is, you are using a 'unique' key generator
>>> that you know is not creating unique keys and then asking the database
>>> to make it right. Sort of like making a square peg fit a round hole by
>>> shaving the corners. It is possible but has sort of a messy feel to it.
>>>
>>
>> Hmmm, yes.
>>
>> Yet, I don't feel guilty as much, since that is quite similar to a
>> unique key on database "username", while the "generator" (human mind)
>> does not guarantee that. The DB just makes sure it does.
>>
>
> I think the argument to be made here is you have no control over what
> people choose as a username, you do have control over what your key
> generator outputs.
>
>
>> [--]
>>
>>>
>>> So an UPSERT is just one feature of ON CONFLICT. The other being DO
>>> NOTHING. Therefore I could see an argument made for adding other ON
>>> CONFLICT clauses. How difficult/plausible that would be is above my
>>> level of expertise.
>>>
>>>
>> Mine too. But I'd say that the above wording exactly makes the point I
>> was trying to make. Thank you.
>>
>> -R
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] Upgrade postgres cluster on FreeBSD using pg_upgrade

[Amitabh Kant]

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 be present at the same time, which seems
difficult as I have installed using ports. I don't want to compile PG
myself unless that is the only way out. Reading the mailing lists and
previous questions, I see following options:

a) Use the work directory of port to compile the binaries and use it with
pg_upgrade

b) use jails as noted in some of the discussions online, however no idea
whether it would work in my case.

c) Somehow modify the ports options so each versions binary is installed
separately. I have no idea how to do it, maybe need to ask this on the
FreeBSD mailing list.

What is the route generally preferred by those running PG on FreeBSD? Is
there something simple which I have missed out?


With regards

Amitabh

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

[Daniel Verite]

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/Pseudo_encrypt

You may replace the round function with your own secret function,
so you'll get the required randomness, secrecy and uniqueness.
No need to deal with collisions on insertion as there are none.

> 2. with current (as of 9.5) implementation I think I can always "ON
> CONFLICT DO NOTHING", and retry the INSERT from application level.

Yes, but retrying is now easy, let's not underappreciate that.

As a test, with 9.5alpha1, I create a table with 100k unique
random numbers:

  CREATE TABLE vouchers(id int primary key);

Then try to populate it immediately with 100k rows:

INSERT INTO vouchers
   select (random()*10)::int from
   generate_series(1,10)
 ON CONFLICT DO NOTHING;

psql result: INSERT 0 5

Note how 5 values conflicted right from the beginning,
even though we're claiming only 10^5 out of the 10^9 output
range (or 0.01%). 
The probability of at least one collision is pretty high,
see the "birthday paradox" for the theory on that.

Anyway the collisions got eliminated without any effort
from me and that's quite useful already.

Now trying to insert 10k rows at a time:

  INSERT INTO vouchers 
  SELECT (random()*10)::int
  FROM generate_series(1,1)
  ON CONFLICT DO NOTHING
  RETURNING id;

when run repeatedly, it tends to return between 9995 and 1 values.
If we want exactly N rows and we get back N-epsilon, then we need to
re-ask for epsilon rows, but this will converge fast  to completion.
(that is, until you have enough values that the birthday paradox effect
really kicks in).

My point is that we can now achieve that without any exception handling
or transaction retry, and no plpgsql function to create, so it's really
a significant improvement in ease of use. And presumably in
performance too.


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @ManitouMail


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Daniel Verite]

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/datatype-numeric.html#DATATYPE-SERIAL?
> Wouldn't that simplify things by insuring uniqueness?

UUIDs are 36 characters wide; it's too boring and error-prone
for a person to type this on a keyboard or spell it over the phone
to an operator.

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.

The constraint is that such codes must be reasonably short, but
someone who tries to make up one must have a near-zero chance
of guessing one that actually exists.

Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @ManitouMail


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Joseph Kregloh]

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 the /opt directory.
2. Install the contrib the same way.
3. Initialize the 9.4 database in another directory /opt/bin/initdb -D
/usr/local/pgsql_94/data -E UTF8.
4. Make sure the two databases are compatible: /opt/bin/pg_upgrade -d
/usr/local/pgsql/data -D /usr/local/pgsql_94/data -b /usr/local/bin -B
/opt/bin -p 5001 -P 5002 -c
5. Once everything is complete, run the scripts to delete old data and
analyze the new db
6. Deinstall 9.0 and 9.4.
7. Install 9.4 using the defaults.
8. Move the /usr/local/pgsql_94/data to /usr/local/pgsql/data
9. Copy any settings you want to restore and that's it.

Above is the basic steps as I remember from memory. But I've successfully
upgraded many databases this way.

On Sun, Jul 19, 2015 at 1:35 PM, Amitabh Kant  wrote:

> 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 be present at the same time, which
> seems difficult as I have installed using ports. I don't want to compile PG
> myself unless that is the only way out. Reading the mailing lists and
> previous questions, I see following options:
>
>
Do you use pkg? Don't be afraid of building from source, give you more
options and you can see what is actually installed in terms of dependencies.


> a) Use the work directory of port to compile the binaries and use it with
> pg_upgrade
>
>
You install it in a different location.


> b) use jails as noted in some of the discussions online, however no idea
> whether it would work in my case.
>
>
Jails work, but in this case it would be more trouble. If you had it in a
jail already then that would be different story.


> c) Somehow modify the ports options so each versions binary is installed
> separately. I have no idea how to do it, maybe need to ask this on the
> FreeBSD mailing list.
>

This I explained above.


-Joseph Kregloh

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

[pbj]

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 references to both under ./src/pl/plpython.  Is this a bug,
something I'm doing wrong, or perhaps my system is too old?

make distclean

./configure --with-openssl --with-libxml --with-libxslt --with-python PYTHON=/us
r/bin/python

make

./configure --with-openssl --with-libxml --with-libxslt --with-python PYTHON=/us
r/bin/python3

make

make install
service postgresql restart

psql -U postgres
postgres=# create database py2;
CREATE DATABASE
postgres=# create database py3;
CREATE DATABASE
postgres=# \c py2
You are now connected to database "py2" as user "postgres".
py2=# create extension plpython2u;
CREATE EXTENSION
py2=# \c py3
You are now connected to database "py3" as user "postgres".
py3=# create extension plpython3u;
ERROR:  could not load library "/usr/local/pgsql/lib/plpython3.so": /usr/local/p
gsql/lib/plpython3.so: undefined symbol: PyString_AsString



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Lots of stuck queries after upgrade to 9.4

[Spiros Ioannou]

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 are in semop
semop(2064417, {{0, -1, 0}}, 1^C 

ipcs reveals no shortage of semaphores..

Please any ideas very welcome, our service now in a very sad state..







*Spiros Ioannou IT Manager, inAccesswww.inaccess.com
M: +30 6973-903808T: +30 210-6802-358*

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

[Spiros Ioannou]

Os is debian 6

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

[Tom Lane]

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 --with-openssl --with-libxml --with-libxslt --with-python 
> PYTHON=/usr/bin/python3
> make
> make install

You need to configure and make one way, then install, then make distclean,
reconfigure and remake the other way, then make install in only the
src/pl/plpython directory.  The recipe as you have it will end up with
only one installed copy of plpython, not two, and I'm not real sure that
that copy will be correctly built for either case.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Tom Lane]

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 reveals that all stuck processes they are in semop
> semop(2064417, {{0, -1, 0}}, 1^C 

Stack traces of the stuck processes might help determine what's going on.

https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend

Please try to capture traces for both the COMMIT and INSERT cases,
as they're almost certainly different.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Amitabh Kant]

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-server/ && make install clean
> PREFIX=/opt, this will install 9.4 on the /opt directory.
> 2. Install the contrib the same way.
> 3. Initialize the 9.4 database in another directory /opt/bin/initdb -D
> /usr/local/pgsql_94/data -E UTF8.
> 4. Make sure the two databases are compatible: /opt/bin/pg_upgrade -d
> /usr/local/pgsql/data -D /usr/local/pgsql_94/data -b /usr/local/bin -B
> /opt/bin -p 5001 -P 5002 -c
> 5. Once everything is complete, run the scripts to delete old data and
> analyze the new db
> 6. Deinstall 9.0 and 9.4.
> 7. Install 9.4 using the defaults.
> 8. Move the /usr/local/pgsql_94/data to /usr/local/pgsql/data
> 9. Copy any settings you want to restore and that's it.
>
> Above is the basic steps as I remember from memory. But I've successfully
> upgraded many databases this way.
>
> On Sun, Jul 19, 2015 at 1:35 PM, Amitabh Kant 
> wrote:
>
>> 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 be present at the same time, which
>> seems difficult as I have installed using ports. I don't want to compile PG
>> myself unless that is the only way out. Reading the mailing lists and
>> previous questions, I see following options:
>>
>>
> Do you use pkg? Don't be afraid of building from source, give you more
> options and you can see what is actually installed in terms of dependencies.
>
>
>> a) Use the work directory of port to compile the binaries and use it with
>> pg_upgrade
>>
>>
> You install it in a different location.
>
>
>> b) use jails as noted in some of the discussions online, however no idea
>> whether it would work in my case.
>>
>>
> Jails work, but in this case it would be more trouble. If you had it in a
> jail already then that would be different story.
>
>
>> c) Somehow modify the ports options so each versions binary is installed
>> separately. I have no idea how to do it, maybe need to ask this on the
>> FreeBSD mailing list.
>>
>
> This I explained above.
>
>
> -Joseph Kregloh
>

I had a feeling I was missing something simple. I was looking at DESTDIR
instead of PREFIX. I will try it out. Seems pretty elegant and simple.

I do use pkg on production servers, but I have my own pkg repository where
I build packages to be deployed across multiple servers.

Thanks for the help.


With regards

Amitabh