[SQL] atomic multi-threaded upsert
Hi, All I'm trying to make a kind of upsert for the following table: CREATE TABLE sequences ( section VARCHAR( 50 ) NOT NULL, name VARCHAR( 50 ) NOT NULL, counter BIGINT NOT NULL ); CREATE UNIQUE INDEX IDX_SEQUENCES_SN ON sequences(section, name); Next, I execute the following two queries for that table: -- increment and get the counter if exists UPDATE sequences SET counter = counter + 1 WHERE section = 'testSection' AND name = 'testKey' RETURNING counter; -- insert new counter if not exists INSERT INTO sequences ( section, name, counter ) SELECT 'testSection', 'testKey', 0 WHERE NOT EXISTS ( SELECT * FROM sequences WHERE section = 'testSection' AND name = 'testKey' ); It works OK when executed in single thread. When such pairs of queries are executed in parallel (3-10 threads, 1 JDBC connection for each thread, with autoCommit set), occasionally I get the following error: ERROR: duplicate key value violates unique constraint "idx_sequences_sn" 1) That looks weird actually, should the INSERT ... NOT EXISTS (...) be executed in atomic fashion? Also, AFAIK, UNIQUE INDEX locks rows before insert (or not?). Can it be related to JDBC? Or it's the result of MVCC conflict resolution? 2) Are there some patterns for such kind of task? Thanks. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] "compressing" consecutive values into one
On Tue, Nov 23, 2010 at 04:19:18PM +0100, Louis-David Mitterrand wrote: > Hi, > > On time series price data I'm trying to remove consecutive identical > prices and keep only the latest. I tried: > > delete from price where id_price in (select t.id_price2 from (select > first_value(p.id_price) over w as id_price1, > nth_value(p.id_price, 2) over w as id_price2, > first_value(p.price) over w as price1, > nth_value(p.price,2) over w as price2 > from price p > window w as (partition by > p.id_rate,p.id_cabin_category,p.id_cruise > order by p.id_price desc rows between unbounded > preceding and > unbounded following)) as t where price1 = price2); > > and it mostly works but I have to do several runs to completely > eliminate identical consecutive prices. Actually I found the answer to my own question. It's the WINDOW lag/lead functions that I needed and this time one pass is enough: delete from price where id_price in ( select t.id_price1 from (select lead(p.id_price) over w as id_price1, lead(p.price) over w as price1, p.id_price, p.price from price p window w as (partition by p.id_rate,p.id_cabin_category,p.id_cruise order by p.id_price rows between unbounded preceding and unbounded following)) as t where t.price = t.price1); -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] atomic multi-threaded upsert
"Mikhail V. Puzanov" writes: >-- increment and get the counter if exists > UPDATE sequences SET counter = counter + 1 > WHERE section = 'testSection' AND name = 'testKey' > RETURNING counter; Seems that what you have here is a bad manual implementation of a sequence. Why don't you use a real sequence object and nextval()? regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] insert from a select
Hi,
I have a strange issue that is mostly likely me not understanding something.
I always thought that an insert statement would accept any select statement.
I'm guessing I am wrong.
I have created a temporary table ("tempclass") that is exact match to an
existing table ('esclass').
When I attempt to do the following
insert into tempclass Select cl.pkid, cl.depart, cl.sessionid, cl.instrid,
cl.classseq,(select facility from esloc where esloc.pkid = cl.locationid) as
facility, cl.schedule from esclass cl where cl.pkid in (14507,14508)
I get the following error:
ERROR: column "schedule" is of type date but expression is of type character
varying
LINE 1: ... cl.depart, cl.sessionid, cl.instrid, cl.classseq,(select fa...
^
HINT: You will need to rewrite or cast the expression.
The error makes no sense to me. But most important if I just run the select
statement it works perfectly.
Like I said the table "tempclass" (a temporary) is a dup of table "esclass" so
none of it makes sense. Of course I did say I'm missing something.
So why isn't the select statement working with the insert?
Johnf
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] insert from a select
On Wednesday 24 November 2010 4:07:43 pm John Fabiani wrote:
> Hi,
> I have a strange issue that is mostly likely me not understanding
> something. I always thought that an insert statement would accept any
> select statement. I'm guessing I am wrong.
>
> I have created a temporary table ("tempclass") that is exact match to an
> existing table ('esclass').
>
> When I attempt to do the following
> insert into tempclass Select cl.pkid, cl.depart, cl.sessionid, cl.instrid,
> cl.classseq,(select facility from esloc where esloc.pkid = cl.locationid)
> as facility, cl.schedule from esclass cl where cl.pkid in (14507,14508)
>
> I get the following error:
>
> ERROR: column "schedule" is of type date but expression is of type
> character varying
> LINE 1: ... cl.depart, cl.sessionid, cl.instrid, cl.classseq,(select fa...
> ^
> HINT: You will need to rewrite or cast the expression.
>
> The error makes no sense to me. But most important if I just run the
> select statement it works perfectly.
>
> Like I said the table "tempclass" (a temporary) is a dup of table "esclass"
> so none of it makes sense. Of course I did say I'm missing something.
>
> So why isn't the select statement working with the insert?
Looks like an off by one situation. See error detail below:
LINE 1: ... cl.depart, cl.sessionid, cl.instrid, cl.classseq,(select fa..
^
Looks like the result of the 'select facility.." is being inserted into the
schedule column.
>
> Johnf
--
Adrian Klaver
[email protected]
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How strings are sorted by LC_COLLATE specifically?
Hello Dear All. How strings are sorted when LC_COLLATE = ja_JP.UTF-8. I tried to read the documention on that,but there are just a few words, like LC_COLLATE determines string sort order, Is there a specific reference about this? So I can implement an equivalent string sort function in JAVA. because some of the sort logic is here. Any clue will be greatly appreciated. Charles -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] atomic multi-threaded upsert
On 2010-11-24, "Mikhail V. Puzanov" wrote: > Next, I execute the following two queries for that table: > >-- increment and get the counter if exists > UPDATE sequences SET counter = counter + 1 > WHERE section = 'testSection' AND name = 'testKey' > RETURNING counter; > >-- insert new counter if not exists > INSERT INTO sequences ( section, name, counter ) > SELECT 'testSection', 'testKey', 0 > WHERE NOT EXISTS ( > SELECT * FROM sequences > WHERE section = 'testSection' AND name = 'testKey' > ); > ERROR: duplicate key value violates unique constraint "idx_sequences_sn" > > 1) That looks weird actually, should the INSERT ... NOT EXISTS (...) be > executed > in atomic fashion? Also, AFAIK, UNIQUE INDEX locks rows before > insert (or not?). > Can it be related to JDBC? > Or it's the result of MVCC conflict resolution? Perhaps - Thread A UPDATEs, affecting no row. Thread B UPDATEs, affecting no row. Thread A INSERTs one row. Autocommit is on, so it commits the INSERT. Thread B attempts INSERT and fails on the duplicate. -- KM -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] atomic multi-threaded upsert
2010/11/25 Tom Lane > "Mikhail V. Puzanov" writes: > >-- increment and get the counter if exists > > UPDATE sequences SET counter = counter + 1 > > WHERE section = 'testSection' AND name = 'testKey' > > RETURNING counter; > > Seems that what you have here is a bad manual implementation of a > sequence. Why don't you use a real sequence object and nextval()? > > > Well, if I use the real sequence, I'll need unpredictable number of them and will have to store mapping (section, name)->sequenceName, and also will have to create those sequences dynamically the similar way.
Re: [SQL] atomic multi-threaded upsert
2010/11/25 KM > On 2010-11-24, "Mikhail V. Puzanov" wrote: > > Next, I execute the following two queries for that table: > > > >-- increment and get the counter if exists > > UPDATE sequences SET counter = counter + 1 > > WHERE section = 'testSection' AND name = 'testKey' > > RETURNING counter; > > > >-- insert new counter if not exists > > INSERT INTO sequences ( section, name, counter ) > > SELECT 'testSection', 'testKey', 0 > > WHERE NOT EXISTS ( > > SELECT * FROM sequences > > WHERE section = 'testSection' AND name = 'testKey' > > ); > > > ERROR: duplicate key value violates unique constraint "idx_sequences_sn" > > > > 1) That looks weird actually, should the INSERT ... NOT EXISTS (...) be > > executed > > in atomic fashion? Also, AFAIK, UNIQUE INDEX locks rows before > > insert (or not?). > > Can it be related to JDBC? > > Or it's the result of MVCC conflict resolution? > > Perhaps - > > Thread A UPDATEs, affecting no row. > Thread B UPDATEs, affecting no row. > Thread A INSERTs one row. Autocommit is on, so it commits the INSERT. > Thread B attempts INSERT and fails on the duplicate. > > Yeah, but my expectation was that only one INSERT occurs due to WHERE NOT EXISTS clause. Seems, the task generally needs using pessimistic locks.
