[SQL] selecting latest record
Hi, I have a simple table price(id_product, price, date) which records price changes for each id_product. Each time a price changes a new tuple is created. What is the best way to select only the latest price of each id_product? Thanks, -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] selecting latest record
Hello 2009/9/22 Louis-David Mitterrand : > Hi, > > I have a simple table > > price(id_product, price, date) > > which records price changes for each id_product. Each time a price > changes a new tuple is created. > > What is the best way to select only the latest price of each id_product? there are more ways - depends on what you wont. one way is SELECT * FROM price WHERE (id_product, date) = (SELECT id_product, max(date) FROM price GROUP BY id_product) Regards Pavel Stehule > > Thanks, > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] selecting latest record
On Tue, Sep 22, 2009 at 11:56:54AM +0200, Pavel Stehule wrote: > > there are more ways - depends on what you wont. > > one way is > > SELECT * >FROM price > WHERE (id_product, date) = (SELECT id_product, max(date) >FROM price > GROUP BY > id_product) Nice. I didn't know one could have several args in a single WHERE clause. Thanks, -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] selecting latest record
In response to Louis-David Mitterrand : > Hi, > > I have a simple table > > price(id_product, price, date) > > which records price changes for each id_product. Each time a price > changes a new tuple is created. > > What is the best way to select only the latest price of each id_product? There are several ways to do that, for instance with DISTINCT ON (only postgresql): test=*# select * from price ; id_product | price | datum +---+ 1 |10 | 2009-09-01 1 |12 | 2009-09-10 2 |11 | 2009-09-10 2 | 8 | 2009-09-13 (4 rows) test=*# select distinct on (id_product) id_product, price from price order by id_product, datum desc; id_product | price +--- 1 |12 2 | 8 (2 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] selecting latest record
Let's say there's an index on the date column: Does the where clause approach necessarily out perform the distinct on version? Hoping the OP has enough data to make analyse useful. A. Kretschmer wrote: In response to Louis-David Mitterrand : Hi, I have a simple table price(id_product, price, date) which records price changes for each id_product. Each time a price changes a new tuple is created. What is the best way to select only the latest price of each id_product? There are several ways to do that, for instance with DISTINCT ON (only postgresql): test=*# select * from price ; id_product | price | datum +---+ 1 |10 | 2009-09-01 1 |12 | 2009-09-10 2 |11 | 2009-09-10 2 | 8 | 2009-09-13 (4 rows) test=*# select distinct on (id_product) id_product, price from price order by id_product, datum desc; id_product | price +--- 1 |12 2 | 8 (2 rows) Andreas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better)
Dear Postgres Gurus; I've just diagnosed a PostgreSQL 8.3.4 server which, about once a month, would deadlock shortly after 11pm. It had been doing this for years, and the prior response was simply to reboot everything. The culprit boils down to: # create table cache_table_20090921 ( site_key int NOT NULL REFERENCES contexts(context_key), blah_blah TEXT NULL, PRIMARY KEY (site_key) ); Without the REFERENCES this is a very fast operation and always works. Adding "DEFERRABLE INITIALLY DEFERRED" or "ON DELETE CASCADE" (as the original did) makes no difference. With any REFERENCES, the create table may block a long time (up to 45 minutes and counting), all while attempting to get an AccessExclusiveLock. The server quickly backs up, simple select statements block, the server maxes out the number of incoming apache connections, things start swapping, and generally it all gets into a huge tangle: # SELECT l.locktype,c.relname,l.pid,l.mode,granted from pg_locks l,pg_class c where l.relation=c.oid order by relname,granted; locktype | relname | pid |mode | granted relation | article_key_idx | 18891 | AccessShareLock | t relation | articles| 18891 | AccessShareLock | t relation | articles_editorid_idx | 18891 | AccessShareLock | t relation | articles_pkey | 18891 | AccessShareLock | t relation | articles_response_to_key_idx| 18891 | AccessShareLock | t relation | articles_state_idx | 18891 | AccessShareLock | t relation | article_words | 18891 | AccessShareLock | t relation | article_words_cw| 18891 | AccessShareLock | t relation | article_words_wc| 18891 | AccessShareLock | t relation | collection_context_key_idx | 18891 | AccessShareLock | t relation | collection_owner_key_idx| 18891 | AccessShareLock | t relation | collections | 18891 | AccessShareLock | t relation | context_publication_key_idx | 18891 | AccessShareLock | t relation | contexts| 18891 | AccessShareLock | t relation | contexts| 3879 | AccessExclusiveLock | f relation | contexts| 5477 | AccessShareLock | f relation | contexts| 5484 | AccessShareLock | f relation | contexts| 5485 | AccessShareLock | f relation | contexts| 5486 | AccessShareLock | f relation | contexts| 5487 | AccessShareLock | f relation | contexts| 5489 | AccessShareLock | f relation | contexts| 5493 | AccessShareLock | f relation | contexts| 5494 | AccessShareLock | f relation | contexts| 5496 | AccessShareLock | f relation | contexts| 5497 | AccessShareLock | f relation | contexts| 5498 | AccessShareLock | f relation | contexts| 5499 | AccessShareLock | f relation | contexts| 5500 | AccessShareLock | f relation | contexts| 5502 | AccessShareLock | f relation | contexts| 5503 | AccessShareLock | f relation | contexts| 5504 | AccessShareLock | f relation | contexts| 5505 | AccessShareLock | f relation | contexts| 5506 | AccessShareLock | f relation | contexts| 5507 | AccessShareLock | f relation | contexts| 5508 | AccessShareLock | f relation | contexts| 5509 | AccessShareLock | f relation | contexts| 5510 | AccessShareLock | f relation | contexts| 5511 | AccessShareLock | f relation | contexts| 5512 | AccessShareLock | f relation | contexts| 5515 | AccessShareLock | f relation | contexts| 5516 | AccessShareLock | f relation | contexts| 5517 | AccessShareLock | f relation | contexts| 5518 | AccessShareLock | f relation | contexts| 5519 | AccessShareLock | f relation | contexts| 5520 | AccessShareLock | f relation | contexts| 5521 | AccessShareLock | f relation | contexts
[SQL] AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks
Dear Postgres Gurus; I've just diagnosed a PostgreSQL 8.3.4 server which, about once a month, would deadlock shortly after 11pm. It had been doing this for years, and the prior response was simply to reboot everything. The culprit boils down to: # create table cache_table_20090921 ( site_key int NOT NULL REFERENCES contexts(context_key), blah_blah TEXT NULL, PRIMARY KEY (site_key) ); Without the REFERENCES this is a very fast operation and always works. Adding "DEFERRABLE INITIALLY DEFERRED" or "ON DELETE CASCADE" (as the original did) makes no difference. With any REFERENCES, the create table may blocks a long time (up to 45 minutes and counting), all while attempting to get an AccessExclusiveLock. The server quickly backs up, simple select statements block, the server maxes out the number of incoming apache connections, things start swapping, and generally it all gets into a huge tangle: # SELECT l.locktype,c.relname,l.pid,l.mode,granted from pg_locks l,pg_class c where l.relation=c.oid order by relname,granted; locktype | relname | pid | mode | granted relation | article_key_idx | 18891 | AccessShareLock | t relation | articles| 18891 | AccessShareLock | t relation | articles_editorid_idx | 18891 | AccessShareLock | t relation | articles_pkey | 18891 | AccessShareLock | t relation | articles_response_to_key_idx| 18891 | AccessShareLock | t relation | articles_state_idx | 18891 | AccessShareLock | t relation | article_words | 18891 | AccessShareLock | t relation | article_words_cw| 18891 | AccessShareLock | t relation | article_words_wc| 18891 | AccessShareLock | t relation | collection_context_key_idx | 18891 | AccessShareLock | t relation | collection_owner_key_idx| 18891 | AccessShareLock | t relation | collections | 18891 | AccessShareLock | t relation | context_publication_key_idx | 18891 | AccessShareLock | t relation | contexts| 18891 | AccessShareLock | t <- HERE relation | contexts| 3879 | AccessExclusiveLock | f relation | contexts| 5477 | AccessShareLock | f relation | contexts| 5484 | AccessShareLock | f relation | contexts| 5485 | AccessShareLock | f relation | contexts| 5486 | AccessShareLock | f relation | contexts| 5487 | AccessShareLock | f relation | contexts| 5489 | AccessShareLock | f relation | contexts| 5493 | AccessShareLock | f relation | contexts| 5494 | AccessShareLock | f relation | contexts| 5496 | AccessShareLock | f relation | contexts| 5497 | AccessShareLock | f relation | contexts| 5498 | AccessShareLock | f relation | contexts| 5499 | AccessShareLock | f relation | contexts| 5500 | AccessShareLock | f relation | contexts| 5502 | AccessShareLock | f relation | contexts| 5503 | AccessShareLock | f relation | contexts| 5504 | AccessShareLock | f relation | contexts| 5505 | AccessShareLock | f relation | contexts| 5506 | AccessShareLock | f relation | contexts| 5507 | AccessShareLock | f relation | contexts| 5508 | AccessShareLock | f relation | contexts| 5509 | AccessShareLock | f relation | contexts| 5510 | AccessShareLock | f relation | contexts| 5511 | AccessShareLock | f relation | contexts| 5512 | AccessShareLock | f relation | contexts| 5515 | AccessShareLock | f relation | contexts| 5516 | AccessShareLock | f relation | contexts| 5517 | AccessShareLock | f relation | contexts| 5518 | AccessShareLock | f relation | contexts| 5519 | AccessShareLock | f relation | contexts| 5520 | AccessShareLock | f relation | contexts| 5521 | AccessShareLock | f relation | contexts| 5523 | AccessShareLock
Re: [SQL] AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better)
Bryce Nesbitt writes: > 1) Why the AccessExclusiveLock on create table? It has to install a trigger on the referenced table. There has been some discussion that maybe CREATE TRIGGER could take just ExclusiveLock and not AccessExclusiveLock, but it hasn't been done yet; and I'm not sure how much that would help you anyway. It would only help if the referenced table (contexts) is essentially read-only to the rest of your workload, else it'll block anyhow. > 2) Why is the foreign key check a heavy operation, since a new table > will have zero foreign keys, it can't possibly violate the constraint yet. It's not a heavy operation in that case. The problem doubtless is that it's backed up behind some other transaction that is sitting on a lock on the contexts table. And then everything else backs up behind it. > 3) Other than eliminating dynamic table creation, how can this operation > be altered? Get rid of long-running transactions that hold locks on the contexts table. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better)
Tom Lane wrote: Bryce Nesbitt writes: 1) Why the AccessExclusiveLock on create table? It has to install a trigger on the referenced table. There has been some discussion that maybe CREATE TRIGGER could take just ExclusiveLock and not AccessExclusiveLock, but it hasn't been done yet; and I'm not sure how much that would help you anyway. It would only help if the referenced table (contexts) is essentially read-only to the rest of your workload, else it'll block anyhow. Thanks for the great info. In our case all the long running access is read-only. We have a poorly designed table that several postgres consultants have burned out on trying to fix. Most notably there are also zillions of short read-only references that presently block while the create table attempts to gain the AccessExclusiveLock. -Bryce
[SQL] Data integration tool in Chinese?
Hello to all, Being in a pretty much international company, I have come here to ask a few things about ETL tools and their different languages. We have offices in the US, in Europe (Italy, France) and in China. We think English is fine but our European team and Chinese team especially would like to get software in their language. What we are trying to find is an ETL program to perform data integration, data synchronization and deduplication on our database in French, Italian and especially in Chinese. Thanks for your help. -- View this message in context: http://www.nabble.com/Data-integration-tool-in-Chinese--tp25530756p25530756.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Data integration tool in Chinese?
Are you asking that all strings be stored into the other three languages as part of (potentially many-master) replication? hfdabler wrote: Hello to all, Being in a pretty much international company, I have come here to ask a few things about ETL tools and their different languages. We have offices in the US, in Europe (Italy, France) and in China. We think English is fine but our European team and Chinese team especially would like to get software in their language. What we are trying to find is an ETL program to perform data integration, data synchronization and deduplication on our database in French, Italian and especially in Chinese. Thanks for your help. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
