Re: Merge into does not work
On 11/26/21 12:10, Alvaro Herrera wrote: On 2021-Nov-26, Adrian Klaver wrote: On 11/26/21 11:44, Alvaro Herrera wrote: On 2021-Nov-26, Shaozhong SHI wrote: I am using the MERGE patch I posted here[1], on top of Postgres 15. https://postgr.es/m/20252245.byerxxac444d@alvherre.pgsql A patch that as AFAIK is not even committed to what is at best an alpha version would in my opinion not qualify as ready much less supported. I look forward to seeing it make it in, but I would hardly recommend it for general use. All true. I'm recruiting testers. Which is great, that was not how it was presented though: 'It does work for me ...' The issue being there are already folks that come across: https://wiki.postgresql.org/wiki/SQL_MERGE https://wiki.postgresql.org/wiki/SQL_MERGE_Patch_Status and do a partial read on the pages and leap to the conclusion the MERGE exists in Postgres. I fully expect people to see: " It does work for me: 55479 15devel 680346=# MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume); MERGE 2 Duración: 3,879 ms 55479 15devel 680346=# select * from stock; item_id │ balance ─┼─ 20 │1900 10 │3200 30 │ 300 (3 filas) " quit reading and then ask why MERGE does not exist in their instance? My two cents worth. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Merge into does not work
On 2021-Nov-26, Adrian Klaver wrote: > On 11/26/21 11:44, Alvaro Herrera wrote: > > On 2021-Nov-26, Shaozhong SHI wrote: > > > > I am using the MERGE patch I posted here[1], on top of Postgres 15. > > > > https://postgr.es/m/20252245.byerxxac444d@alvherre.pgsql > > A patch that as AFAIK is not even committed to what is at best an alpha > version would in my opinion not qualify as ready much less supported. I look > forward to seeing it make it in, but I would hardly recommend it for general > use. All true. I'm recruiting testers. -- Álvaro Herrera Valdivia, Chile — https://www.EnterpriseDB.com/ "I love the Postgres community. It's all about doing things _properly_. :-)" (David Garamond)
Re: Merge into does not work
On 11/26/21 11:44, Alvaro Herrera wrote: On 2021-Nov-26, Shaozhong SHI wrote: I am using the MERGE patch I posted here[1], on top of Postgres 15. https://postgr.es/m/20252245.byerxxac444d@alvherre.pgsql A patch that as AFAIK is not even committed to what is at best an alpha version would in my opinion not qualify as ready much less supported. I look forward to seeing it make it in, but I would hardly recommend it for general use. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Merge into does not work
On 2021-Nov-26, Shaozhong SHI wrote: > MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id > WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume > WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume); It does work for me: 55479 15devel 680346=# MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume); MERGE 2 Duración: 3,879 ms 55479 15devel 680346=# select * from stock; item_id │ balance ─┼─ 20 │1900 10 │3200 30 │ 300 (3 filas) > I am using Postgres 9.6. I am using the MERGE patch I posted here[1], on top of Postgres 15. https://postgr.es/m/20252245.byerxxac444d@alvherre.pgsql -- Álvaro Herrera Valdivia, Chile — https://www.EnterpriseDB.com/ "Pido que me den el Nobel por razones humanitarias" (Nicanor Parra)
Re: What do you do with a long running rollback
Chris Cawley writes: > Here's the query : > | SELECT pid, age(clock_timestamp(), query_start), usename, query | > | | FROM pg_stat_activity | > | | WHERE query != '' AND query NOT ILIKE '%pg_stat_activity%' | > | | ORDER BY query_start desc; | > Return output is pid | 4 days | user | ROLLBACK I think the fault's in your query; it's presuming a long-obsolete convention about how idle sessions are represented in pg_stat_activity. These days you should be filtering on "state" or "wait_event_type". This output is just telling you that the last thing that session did, four days ago, was a ROLLBACK. regards, tom lane
Re: Merge into does not work
On 11/26/21 1:08 PM, Shaozhong SHI wrote: CREATE TABLE Stock(item_id int UNIQUE, balance int); INSERT INTO Stock VALUES (10, 2200); INSERT INTO Stock VALUES (20, 1900);CREATE TABLE Buy(item_id int, volume int); INSERT INTO Buy values(10, 1000); INSERT INTO Buy values(30, 300); MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume); I am using Postgres 9.6. *ERROR MESSAGES! ALWAYS SHOW ERROR MESSAGES!!! * -- Angular momentum makes the world go 'round.
Re: Merge into does not work
As evidenced by the lack of such a command in our documentation this doesn't exist. We do offer similar functionality directly as part of the INSERT command via its ON CONFLICT clause. David J.
Merge into does not work
CREATE TABLE Stock(item_id int UNIQUE, balance int); INSERT INTO Stock VALUES (10, 2200); INSERT INTO Stock VALUES (20, 1900); CREATE TABLE Buy(item_id int, volume int); INSERT INTO Buy values(10, 1000); INSERT INTO Buy values(30, 300); MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume); I am using Postgres 9.6. Regards, David
RE: [EXTERNAL] Re: Inserts and bad performance
Excellent idea David, silly me, I didn't think of that. For the other questions: >How many partitions? 14 >How many rows do they have when performance is slowing considerably? Not sure, maybe on the low millions >Does this table get many updates or is it insert only? insert >What version of PostgreSQL? 13 >Are the inserts randomly distributed among the partitions or targeting one or >a few partitions? Sequentially one partition at a time, so each set of runs is inserting across each part. >Are you able to capture an example and run it in a transaction with explain >(analyze, buffers, verbose) and then rollback? Yes, I'm looking into that pg -Original Message- From: David Rowley Sent: Wednesday, November 24, 2021 7:13 PM To: Godfrin, Philippe E Cc: Tom Lane ; pgsql-general@lists.postgresql.org Subject: Re: [EXTERNAL] Re: Inserts and bad performance On Thu, 25 Nov 2021 at 08:59, Godfrin, Philippe E wrote: > Hi Tom. Good point about the index paging out of the buffer. I did that and > no change. I do have the shared buffers at 40GB, so there’s a good bit there, > but I also did all those things on the page you referred, except for using > copy. At this point the data has not been scrubbed, so I’m trapping data > errors and duplicates. I am curios though, as sidebar, why copy is considered > faster than inserts. I was unable to get COPY faster than around 25K inserts > a second (pretty fast anyway). Frankly, initially I was running 3 concurrent > insert jobs and getting 90K ins/sec ! but after a certain number of records, > the speed just dropped off. EXPLAIN (ANALYZE, BUFFERS) works with INSERTs. You just need to be aware that using ANALYZE will perform the actual insert too. So you might want to use BEGIN; and ROLLBACK; if it's not data that you want to keep. SET track_io_timing = on; might help you too. David
Re: case insensitive collation of Greek's sigma
Jakub Jedelsky writes: > during our tests of Postgres with ICU we found an issue with ILIKE of upper > and lowercase sigma (Σ). The letter has two lowercase variants σ and ς (at > the end of a word). I'm working with en_US and en-US-x-icu collations and > results are a bit unexpected - they are inverted: Not sure why you would expect en_US collation to give appropriate results for Greek? I think you'd need el_GR. Be that as it may, Postgres doesn't define anything about this --- we just rely on the collations supplied by the respective system libraries (libc or ICU). You'd need to file bugs against those libraries if you think their behavior should change. regards, tom lane
download question
Hi, on the postgres repo, on centos 7 x86, I can found the old postgres 9.5, and a postgis 2.4 I need to found a postgres 9.5 with a postgis 2.5.1 where can I found it ? (I know... its obsolete...) thanks Marc MILLAS Senior Architect +33607850334 www.mokadb.com
Re: case insensitive collation of Greek's sigma
On 26/11/21 9:37 π.μ., Jakub Jedelsky wrote: Hello, Thank you for dealing with Greek! during our tests of Postgres with ICU we found an issue with ILIKE of upper and lowercase sigma (Σ). The letter has two lowercase variants σ and ς (at the end of a word). I'm working with en_US and en-US-x-icu collations and results are a bit unexpected - they are inverted: postgres=# SELECT postgres-# 'ΣΣ' ILIKE 'σσ' COLLATE "en_US", postgres-# 'ΣΣ' ILIKE 'σς' COLLATE "en_US" postgres-# ; ?column? | ?column? --+-- t | f (1 row) postgres=# SELECT postgres-# 'ΣΣ' ILIKE 'σσ' COLLATE "en-US-x-icu", postgres-# 'ΣΣ' ILIKE 'σς' COLLATE "en-US-x-icu"; ?column? | ?column? --+-- f | t (1 row) I run those commands on the latest (14.1) official docker image. Is it possible to unify the behaviour?And which one is correct from the community point of view? IMHO all those letters are wrong, the correct S used to be the letter C (equal to the Cyrillic S). Σ is a new invention, same like σ, the final ς looks a lot like the calligraphic version of c . If I could start, I think both results are wrong as both should return True. If I got it right, in the background there is a lower() function running to compare strings, which is not enough for such cases (until the left side isn't taken as a standalone word). I agree with you all of them should be deducted to a single letter s. Firefox's find recognizes all three (Σ,σ,ς) as the same letter. Thanks, - jj -- Achilleas Mantzios DBA, Analyst, IT Lead IT DEPT Dynacom Tankers Mgmt
