Re: Implementing replace function

Hello Postgres users,

to mimic the MySQL-REPLACE statement I need
to try to UPDATE a record and if that fails - INSERT it.

But how can I detect that the UPDATE has failed in my SQL procedure?

begin transaction;

create table pref_users (
id varchar(32) primary key,
first_name varchar(32),
last_name varchar(32),
female boolean,
avatar varchar(128),
city varchar(32),
lat real check (-90 <= lat and lat <= 90),
lng real check (-90 <= lng and lng <= 90),
last_login timestamp default current_timestamp,
last_ip inet,
medals smallint check (medals > 0)
);

create table pref_rate (
obj varchar(32) references pref_users(id),
subj varchar(32) references pref_users(id),
good boolean,
fair boolean,
nice boolean,
about varchar(256),
last_rated timestamp default current_timestamp
);

create table pref_money (
id varchar(32) references pref_users,
yw char(7) default to_char(current_timestamp, 'YYYY-WW'),
money real
);
create index pref_money_yw_index on pref_money(yw);

create or replace function update_pref_users(id varchar,
first_name varchar, last_name varchar, female boolean,
avatar varchar, city varchar, last_ip inet) returns void as $$

update pref_users set
first_name = $2,
last_name = $3,
female = $4,
avatar = $5,
city = $6,
last_ip = $7
where id = $1;

-- XXX how to detect failure here? XXX

insert into pref_users(id, first_name, last_name,
female, avatar, city, last_ip)
values ($1, $2, $3, $4, $5, $6, $7);
$$ language sql;

commit;

Thank you
Alex

Hello

2010/10/31 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>:
> Hello Postgres users,
>
> to mimic the MySQL-REPLACE statement I need
> to try to UPDATE a record and if that fails - INSERT it.
>
> But how can I detect that the UPDATE has failed in my SQL procedure?
>

see: http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html
near to end of page

Regards

Pavel Stehule

>        begin transaction;
>
>        create table pref_users (
>                id varchar(32) primary key,
>                first_name varchar(32),
>                last_name varchar(32),
>                female boolean,
>                avatar varchar(128),
>                city varchar(32),
>                lat real check (-90 <= lat and lat <= 90),
>                lng real check (-90 <= lng and lng <= 90),
>                last_login timestamp default current_timestamp,
>                last_ip inet,
>                medals smallint check (medals > 0)
>        );
>
>        create table pref_rate (
>                obj varchar(32) references pref_users(id),
>                subj varchar(32) references pref_users(id),
>                good boolean,
>                fair boolean,
>                nice boolean,
>                about varchar(256),
>                last_rated timestamp default current_timestamp
>        );
>
>        create table pref_money (
>                id varchar(32) references pref_users,
>                yw char(7) default to_char(current_timestamp, 'YYYY-WW'),
>                money real
>        );
>        create index pref_money_yw_index on pref_money(yw);
>
>        create or replace function update_pref_users(id varchar,
>            first_name varchar, last_name varchar, female boolean,
>            avatar varchar, city varchar, last_ip inet) returns void as $$
>
>                update pref_users set
>                    first_name = $2,
>                    last_name = $3,
>                    female = $4,
>                    avatar = $5,
>                    city = $6,
>                    last_ip = $7
>                where id = $1;
>
>                -- XXX how to detect failure here? XXX
>
>                insert into pref_users(id, first_name, last_name,
>                    female, avatar, city, last_ip)
>                    values ($1, $2, $3, $4, $5, $6, $7);
>        $$ language sql;
>
>        commit;
>
> Thank you
> Alex
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Thanks Pavel, but I have an SQL procedure and not plpgsql?

On Sun, Oct 31, 2010 at 9:34 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> But how can I detect that the UPDATE has failed in my SQL procedure?
>>
>
> see: http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html
> near to end of page

>>        create or replace function update_pref_users(id varchar,
>>            first_name varchar, last_name varchar, female boolean,
>>            avatar varchar, city varchar, last_ip inet) returns void as $$
>>
>>                update pref_users set
>>                    first_name = $2,
>>                    last_name = $3,
>>                    female = $4,
>>                    avatar = $5,
>>                    city = $6,
>>                    last_ip = $7
>>                where id = $1;
>>
>>                -- XXX how to detect failure here? XXX
>>
>>                insert into pref_users(id, first_name, last_name,
>>                    female, avatar, city, last_ip)
>>                    values ($1, $2, $3, $4, $5, $6, $7);
>>        $$ language sql;

2010/10/31 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>:
> Thanks Pavel, but I have an SQL procedure and not plpgsql?

it's not possible in sql. But plpgsql is same level like stored
procedures language from mysql.

>
> On Sun, Oct 31, 2010 at 9:34 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>>> But how can I detect that the UPDATE has failed in my SQL procedure?
>>>
>>
>> see: http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html
>> near to end of page
>
>>>        create or replace function update_pref_users(id varchar,
>>>            first_name varchar, last_name varchar, female boolean,
>>>            avatar varchar, city varchar, last_ip inet) returns void as $$
>>>
>>>                update pref_users set
>>>                    first_name = $2,
>>>                    last_name = $3,
>>>                    female = $4,
>>>                    avatar = $5,
>>>                    city = $6,
>>>                    last_ip = $7
>>>                where id = $1;
>>>
>>>                -- XXX how to detect failure here? XXX
>>>
>>>                insert into pref_users(id, first_name, last_name,
>>>                    female, avatar, city, last_ip)
>>>                    values ($1, $2, $3, $4, $5, $6, $7);
>>>        $$ language sql;
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Hey Alexander, Pavel

The solution like below should works IMO, but it does not.
insert into pref_users(id, first_name, last_name,
female, avatar, city, last_ip)
select $1, $2, $3, $4, $5, $6, $7
where not exists
(update pref_users set first_name = $2,
last_name = $3,
female = $4,
avatar = $5,
city = $6,
last_ip = $7
where id = $1
returning id);

BTW, I don't understand why it not possible to write query like this:
SELECT id FROM (UPDATE test SET nm = 'dima' WHERE id = 1 RETURNING id) AS
foo;
According to the doc (of UPDATE command) "The syntax of the RETURNING list
is identical to
that of the output list of SELECT).
With this syntax, the OPs goal can be implemented in SQL..

--
// Dmitriy.

Hello

2010/10/31 Dmitriy Igrishin <dmitigr(at)gmail(dot)com>:
> Hey Alexander, Pavel
>
> The solution like below should works IMO, but it does not.
>   insert into pref_users(id, first_name, last_name,
>     female, avatar, city, last_ip)
>     select $1, $2, $3, $4, $5, $6, $7
>       where not exists
>          (update pref_users set first_name = $2,
>                     last_name = $3,
>                     female = $4,
>                     avatar = $5,
>                     city = $6,
>                     last_ip = $7
>                 where id = $1
>                 returning id);
>
> BTW, I don't understand why it not possible to write query like this:
> SELECT id FROM (UPDATE test SET nm = 'dima' WHERE id = 1 RETURNING id) AS
> foo;
> According to the doc (of UPDATE command) "The syntax of the RETURNING list
> is identical to
> that of the output list of SELECT).
> With this syntax, the OPs goal can be implemented in SQL..
>

UPDATE RETURNING isn't subselect - so you can't do SELECT FROM (UPDATE
RETURNING) directly. It's possible with wrapping to sql function.

In next pg version 9.1 you can do it via Updatable Common Table
Expression, but it isn't possible in older version.

Regards

Pavel Stehule

> --
> // Dmitriy.
>
>
>

Okay, Pavel, will wait for 9.1 :-)

It is a common case - insert new row if it cannot be updated.

2010/10/31 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>

> Hello
>
> 2010/10/31 Dmitriy Igrishin <dmitigr(at)gmail(dot)com>:
> > Hey Alexander, Pavel
> >
> > The solution like below should works IMO, but it does not.
> > insert into pref_users(id, first_name, last_name,
> > female, avatar, city, last_ip)
> > select $1, $2, $3, $4, $5, $6, $7
> > where not exists
> > (update pref_users set first_name = $2,
> > last_name = $3,
> > female = $4,
> > avatar = $5,
> > city = $6,
> > last_ip = $7
> > where id = $1
> > returning id);
> >
> > BTW, I don't understand why it not possible to write query like this:
> > SELECT id FROM (UPDATE test SET nm = 'dima' WHERE id = 1 RETURNING id) AS
> > foo;
> > According to the doc (of UPDATE command) "The syntax of the RETURNING
> list
> > is identical to
> > that of the output list of SELECT).
> > With this syntax, the OPs goal can be implemented in SQL..
> >
>
> UPDATE RETURNING isn't subselect - so you can't do SELECT FROM (UPDATE
> RETURNING) directly. It's possible with wrapping to sql function.
>
> In next pg version 9.1 you can do it via Updatable Common Table
> Expression, but it isn't possible in older version.
>
> Regards
>
> Pavel Stehule
>
> > --
> > // Dmitriy.
> >
> >
> >
>

--
// Dmitriy.

2010/10/31 Dmitriy Igrishin <dmitigr(at)gmail(dot)com>:
> Okay, Pavel, will wait for 9.1 :-)
>
> It is a common case - insert new row if it cannot be updated.

you can find (probably) MERGE statement in 9.1.

Pavel

>
> 2010/10/31 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>>
>> Hello
>>
>> 2010/10/31 Dmitriy Igrishin <dmitigr(at)gmail(dot)com>:
>> > Hey Alexander, Pavel
>> >
>> > The solution like below should works IMO, but it does not.
>> >   insert into pref_users(id, first_name, last_name,
>> >     female, avatar, city, last_ip)
>> >     select $1, $2, $3, $4, $5, $6, $7
>> >       where not exists
>> >          (update pref_users set first_name = $2,
>> >                     last_name = $3,
>> >                     female = $4,
>> >                     avatar = $5,
>> >                     city = $6,
>> >                     last_ip = $7
>> >                 where id = $1
>> >                 returning id);
>> >
>> > BTW, I don't understand why it not possible to write query like this:
>> > SELECT id FROM (UPDATE test SET nm = 'dima' WHERE id = 1 RETURNING id)
>> > AS
>> > foo;
>> > According to the doc (of UPDATE command) "The syntax of the RETURNING
>> > list
>> > is identical to
>> > that of the output list of SELECT).
>> > With this syntax, the OPs goal can be implemented in SQL..
>> >
>>
>> UPDATE RETURNING isn't subselect - so you can't do SELECT FROM (UPDATE
>> RETURNING) directly. It's possible with wrapping to sql function.
>>
>> In next pg version 9.1 you can do it via Updatable Common Table
>> Expression, but it isn't possible in older version.
>>
>> Regards
>>
>> Pavel Stehule
>>
>> > --
>> > // Dmitriy.
>> >
>> >
>> >
>
>
>
> --
> // Dmitriy.
>
>
>

Alexander Farber wrote on 31.10.2010 09:22:
> Hello Postgres users,
>
> to mimic the MySQL-REPLACE statement I need
> to try to UPDATE a record and if that fails - INSERT it.
>

There is actually an example of this in the PG manual ;)

http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE

Here is another solution based on triggers:
http://database-programmer.blogspot.com/2009/06/approaches-to-upsert.html

Regards
Thomas

Thanks for all the comments.

Do I need to use BEGIN/COMMIT in my plpgsql-function for UPSERT or are
functions atomic?

Regards
Alex

And would a pure SQL-function solution to call
an INSERT followed by an UPDATE in its body
and ignoring the error? (don't know how ignore it
best though, so that I don't ignore other critical errors)

2010/10/31 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>:
> Thanks for all the comments.
>
> Do I need to use BEGIN/COMMIT in my plpgsql-function for UPSERT or are
> functions atomic?

If you use a code from documentation, then you don't need explicit
transaction - every SQL run inside outer implicit transaction in
PostgreSQL.

Pavel

>
> Regards
> Alex
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[corrected typo, sorry]

And wouldn't a pure SQL-function solution be:
to call an INSERT followed by an UPDATE in its body
and ignoring the error? (don't know how ignore that error
best though, so that I don't ignore other critical errors)

2010/10/31 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>:
> And would a pure SQL-function solution to call
> an INSERT followed by an UPDATE in its body
> and ignoring the error? (don't know how ignore it
> best though, so that I don't ignore other critical errors)
>

You must not ignore errors in SQL - it's not T-SQL :)

Pavel

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

I've created a function now (below) and can call it as well,
but how can I see it at the psql prompt? Is there a \d command
for that or should I dump the database to see my declarations?

And is my function atomic? I.e. can't it happen, that FOUND
is not true, but then another session calls a INSERT inbetween?

pref=> create or replace function pref_update_users(_id varchar,
pref(> _first_name varchar, _last_name varchar, _female boolean,
pref(> _avatar varchar, _city varchar, _last_ip inet)
returns void as $BODY$
pref$> BEGIN
pref$>
pref$> update pref_users set
pref$> first_name = _first_name,
pref$> last_name = _last_name,
pref$> female = _female,
pref$> avatar = _avatar,
pref$> city = _city,
pref$> last_ip = _last_ip
pref$> where id = _id;
pref$>
pref$> IF NOT FOUND THEN
pref$> insert into pref_users(id, first_name,
pref$> last_name, female, avatar, city, last_ip)
pref$> values (_id, _first_name, _last_name,
pref$> _female, _avatar, _city, _last_ip);
pref$> END IF;
pref$> END;
pref$> $BODY$ language plpgsql;
CREATE FUNCTION

Thanks and merry Halloween
Alex

\df *update*

Alexander Farber wrote:
> I've created a function now (below) and can call it as well,
> but how can I see it at the psql prompt? Is there a \d command
> for that or should I dump the database to see my declarations?
>
> And is my function atomic? I.e. can't it happen, that FOUND
> is not true, but then another session calls a INSERT inbetween?
>
> pref=> create or replace function pref_update_users(_id varchar,
> pref(> _first_name varchar, _last_name varchar, _female boolean,
> pref(> _avatar varchar, _city varchar, _last_ip inet)
> returns void as $BODY$
> pref$> BEGIN
> pref$>
> pref$> update pref_users set
> pref$> first_name = _first_name,
> pref$> last_name = _last_name,
> pref$> female = _female,
> pref$> avatar = _avatar,
> pref$> city = _city,
> pref$> last_ip = _last_ip
> pref$> where id = _id;
> pref$>
> pref$> IF NOT FOUND THEN
> pref$> insert into pref_users(id, first_name,
> pref$> last_name, female, avatar, city, last_ip)
> pref$> values (_id, _first_name, _last_name,
> pref$> _female, _avatar, _city, _last_ip);
> pref$> END IF;
> pref$> END;
> pref$> $BODY$ language plpgsql;
> CREATE FUNCTION
>
> Thanks and merry Halloween
> Alex
>
>

On 31/10/2010 17:28, Alexander Farber wrote:
> I've created a function now (below) and can call it as well,
> but how can I see it at the psql prompt? Is there a \d command
> for that or should I dump the database to see my declarations?

You can do \df public.*, assuming that your function is in the public
schema, to get a list of all functions; and \df+ <function name> will
give you the function body also.

\? is your friend here.

> And is my function atomic? I.e. can't it happen, that FOUND
> is not true, but then another session calls a INSERT inbetween?

Yes - all functions are executed in an implicit transaction.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie

Hello,

If you expect your data to reach some kind of "critical size" at some
point ( ie updates will be more likely than inserts at that point ), you
can optimize your UPSERT code by trying to UPDATE before INSERTing.
Otherwise trying to INSERT first should decrease the average UPSERT
execution time in the long run, since you are less likely to hit the
exception and do some extra work on the table.

Anyway, glad to hear this is going to be accounted for in 9.1 . :)

Le 31/10/2010 11:07, Thomas Kellerer a écrit :
> Alexander Farber wrote on 31.10.2010 09:22:
>> Hello Postgres users,
>>
>> to mimic the MySQL-REPLACE statement I need
>> to try to UPDATE a record and if that fails - INSERT it.
>>
>
> There is actually an example of this in the PG manual ;)
>
> http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
>
>
> Here is another solution based on triggers:
> http://database-programmer.blogspot.com/2009/06/approaches-to-upsert.html
>
> Regards
> Thomas
>
>

On Tue, Nov 02, 2010 at 10:46:42AM +0100, Matthieu Huin wrote:
> Hello,
>
> If you expect your data to reach some kind of "critical size" at some
> point ( ie updates will be more likely than inserts at that point ), you
> can optimize your UPSERT code by trying to UPDATE before INSERTing.
> Otherwise trying to INSERT first should decrease the average UPSERT
> execution time in the long run, since you are less likely to hit the
> exception and do some extra work on the table.

You'd almost think of using some kind of branch prediction techniques.
You could track what happened the last two times and use that to
predict which would be better. There's always pathelogical cases, but
it could work well for normal workloads.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patriotism is when love of your own people comes first; nationalism,
> when hate for people other than your own comes first.
> - Charles de Gaulle