Эмуляция upsert/merge в postgresql 9.1+ (CTE)

[fixxxer]

Для одной записи - все просто:

# create table t (id int, s varchar(255), primary key(id));
# insert into t values (1, 'a'), (2, 'b');
INSERT 0 2
# with updated as (update t set s='bb' where id=2 returning id)
  insert into t select 2, 'bb' where not exists(select 1 from updated);
INSERT 0 0
# with updated as (update t set s='cc' where id=3 returning id)
  insert into t select 3, 'cc' where not exists(select 1 from updated);
INSERT 0 1
# select * from t;
 id | s  
----+----
  1 | a
  2 | bb
  3 | cc
(3 rows)

Для множественного обновления сложнее. Вот что придумалось:

with
    data as
        (select 1 as id, 'aaa' as s union select 2, 'bbb'),
    updated as
        (update t set s = data.s from data where t.id = data.id returning t.id)
insert into t
    select
        id, s
    from
        data
    where
        data.id not in (select id from updated);

Что то как то немного брейнфак =) Может, есть способ проще?

 

[~WR~]

С ходу - можно UNION и заменить на VALUES.
http://www.postgresql.org/docs/9.2/static/queries-values.html

В остальном, запрос полностью отражает суть того извращения, которое пытаемся сделать.
Не быстрее ли будет отдельными запросами в одной транзакции? Если для каждого id нужен свой особый s.

 

[~WR~]

Только что запостили
http://tapoueh.org/blog/2013/03/15-batch-update.html

 

[fixxxer]

С ходу - можно UNION и заменить на VALUES.

О, спс, был уверен, что как-то можно, не мыскль жэ.

В остальном, запрос полностью отражает суть того извращения, которое пытаемся сделать.

Да нормальное извращение. Фактически, это MERGE, который одно время без особого успеха пытались силами студентов поиметь в pg.

Не быстрее ли будет отдельными запросами в одной транзакции?

Да не думаю чтобы прямо быстрее.

=# explain
-# with
-#     data as
-#         (values (1, 'a'), (2, 'b'), (3, 'c')),
-#     updated as
-#         (update t set s = data.column2 from data where t.id = data.column1 returning t.id)
-# insert into t
-#     select
-#         column1, column2
-#     from
-#         data
-#     where
-#         data.column1 not in (select id from updated);

                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Insert on t  (cost=10.81..10.83 rows=2 width=36)
   CTE data
     ->  Values Scan on "*VALUES*"  (cost=0.00..0.01 rows=3 width=36)
   CTE updated
     ->  Update on t  (cost=0.00..10.79 rows=3 width=102)
           ->  Nested Loop  (cost=0.00..10.79 rows=3 width=102)
                 ->  CTE Scan on data  (cost=0.00..0.01 rows=3 width=96)
                 ->  Index Scan using t_pkey on t  (cost=0.00..3.59 rows=1 width=10)
                       Index Cond: (id = data.column1)
   ->  CTE Scan on data  (cost=0.01..0.03 rows=2 width=36)
         Filter: (NOT (hashed SubPlan 3))
         SubPlan 3
           ->  CTE Scan on updated  (cost=0.00..0.01 rows=3 width=4)
(13 rows)

Нормальный план, че.

 

[grigori]

эх, я скучаю по постгресу, каждый день его вспоминаю

 

[fixxxer]

да у меня тоже по основной работе мыскль. это так, домашний проектик =)

 

[MiksIr]

А я тут втихую огромный проект с мускуля на постгрес перетянул все же. Ох, намучался с регистром в Yii-шных связях. Зато узнал попутно, что InnoDb умеет делать внешние ключи на неуникальные колонки - долго матерился. И что сериализованные объекты не живут в text поле ибо никто не эскейпит \0 =)

 

[fixxxer]

И что сериализованные объекты не живут в text поле ибо никто не эскейпит \0

В мыскле? Да ну.

~$ php -r 'echo mysql_escape_string("\x00");' | hexdump -c
0000000   \   0                                                        
0000002

~$ php -r 'echo mysql_real_escape_string("\x00", mysql_connect("localhost"));' | hexdump -c
0000000   \   0                                                        
0000002

~$ php -r 'echo mysqli_escape_string(mysqli_connect("localhost"), "\x00");' | hexdump -c
0000000   \   0                                                        
0000002

~$ php -r '$pdo = new Pdo("mysql:locahost"); echo $pdo->quote("\x00");' | hexdump -c
0000000   '   \   0   '                                                
0000004

Хотя все равно, это не повод хранить блобы в text: во-первых, collation, во-вторых, trim.

Или ты про postgresql? Там null-terminated строки, ескейпить не поможет, надо использовать BYTEA и эскейпить в hex.

~$ php -r 'echo pg_escape_bytea("\x00");' | hexdump -c
0000000   \   \   0   0   0                                            
0000005

 

[MiksIr]

В мускуле то все ок было, а вот в постгресе да, проблема. Имхо, драйвер мог бы и эскейпить. А bytea пробовал, но там эти поля при получении даются как ресурсы, из которых читать нужно, а хотелось побыстрее закончить и пойти спать =) Так что оставил text и в модели просто тупо \0 зареплейсил.

 

[fixxxer]

Драйвер не мог бы эскейпить, потому что везде в pg используются null terminated-строки.

 

[varan]

А как решать такую же задачу с upsert'ом в случае если primary key составной? Например, по двум полям: (geo_id, date)

 

[MiksIr]

Мог бы, но это был бы костыль, да. Я скорее не понимаю, что курили те, кто использовал нулевой байт в результате сериализации.

 

[MiksIr]

А как решать такую же задачу с upsert'ом в случае если primary key составной? Например, по двум полям: (geo_id, date)

Да так же, просто в RETURNING возвращать обе колонки ключа.

 

[fixxxer]

И вместо not in - not exists, угу.

 

[fixxxer]

Мог бы, но это был бы костыль, да. Я скорее не понимаю, что курили те, кто использовал нулевой байт в результате сериализации.

Только если вообще все гонять в hex — \0 не поможет, null-terminated же.

А вместо serialize вообще (если не нужна десериализация в объекты) лучше юзать json