Re: [SQL] Refactored queries needing validation of syntactic equivalence
Richard Huxton wrote: Mike Adams wrote: So. The first query should pull all 'MOM' records that have one or more corresponding, and possibly orphaned, unassigned receiving records belonging to the same po_cd and item_cd. The second query should pull all unassigned, and possibly orphaned receiving records that have one or more corresponding 'MOM' records once again matching on po_cd and item_cd. Using the results of both queries to double check each other, I can figure out which (if any) open records are, in fact, orphans and do an "after the fact" assignment to the "SPLIT IN MOM" invoice to reduce our accrual. Of course, our ERMS should take care of this automagically; but, tragically, it seems "real" inventory cost flow was attached to the system using duct tape, hot glue, and a couple of thumb tacks. So, given all the administriva above, have I actually refactored them correctly? Well, clearly you could have multiple possible matches, because apart from anything else you could in theory have multiple entries with the same item-code on the same purchase-order-code. In practice it will be rare, but it could happen. Yep! and it's not rare: if we receive 20 serialized items, we *will* get 20 entries of same "itm_cd,po_cd" as serialized items are individually posted in inventory (in accounting speak, they have a "specific item" costing basis, whereas "non serialized" items (parts etc) are (by us) considered to have a "FIFO" costing basis and can be lumped into "lots"). Yesterday I ran both the "legacy" and "refactor" versions of each query after the AP clerk (for once) let me know that her assistant had "joined" a number of receivings (did the reverse of a split for some reason). The "orphans" query (select o.co_cd, ...) came back with the same result set for both the legacy and refactor versions. The "moms" query (select m.co_cd, ...) did not! What I had for the "moms" result sets were (fake products replacing the real ones in the results below): legacy | refactor + 2 hotplate | 2 hotplate 6 scooper | 2 hotplate | 6 scooper | 6 scooper | 6 scooper | 6 scooper | 6 scooper | 6 scooper The "orphans" result sets were the same (faked products in results below): result set - 1 hotplate 1 hotplate 1 scooper 1 scooper 1 scooper 1 scooper 1 scooper 1 scooper In truth those eight records returned by both "orphans" versions *were* actually orphaned by the *2* "moms" records that /do/ exist and were correctly reported by the legacy version... Oops! the refactored "moms" query is an unintentional (by me) cross product! However, since the purpose is to provide you with a list so you can make manual changes there's no problem with that. Except for the unwanted cross productions! Well, there isn't an available "natural" way to prevent that as the table /doesn't/ have a pkey or even a good candidate key. What I did, and it did fix the result set to reflect reality, was change the select o.co_cd, ... from ... to select distinct o.co_cd, ..., o.rowid from ... rowid being Oracle's version of ctid and is the only "unique" item "in" the table ( oh the shame ). What I might be tempted to do is restrict the dates more - you had <= '31 Oct 2007' I'd also apply >= '1 Aug 2007' (or whatever period is reasonable). You can always run an unconstrained match once a month to catch any that slip through the net, but presumably most will fall within a 90-day period. HTH I may restrict the dates more, however the refactored queries both run in under 1000 ms, and given the rcv_mo table currently has >5 && <7 years worth of historical data for them to plow through, and the plan is to only keep the data in the table for 7 yrs max... Thank you for the help! I've appreciated it greatly! Mike. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] backup database tablespace with rsync?
On 10/19/07, chester c young <[EMAIL PROTECTED]> wrote: > postgres A, db 'test', tablespace /pg/test1 > postgres B, db 'test', tablespace /pg/test2 > > tablespace /pg/test1 only has A db 'test' > tablespace /pg/test2 only has B db 'test' > > if > - A and B shut down > - /pg/test1 copied to /pg/test2 > - A and B restarted > > would B db 'test' be running the data that was in A db 'test'? You can't rsync just pieces of the db. you have to copy the whole database ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] two queryes in a single tablescan
Hi, Stefano, "Stefano Dal Pra" <[EMAIL PROTECTED]> wrote: > suppose you have a large table tab and two (or more) queryes like this: > > SELECT count(*),A FROM tab WHERE C GROUP BY A; > SELECT count(*),B FROM tab WHERE C GROUP BY B; > > is there any way to get both results in a single query, > eventually through stored procedure? > The retrieved [count(*),A] ; [count(*),B)] data couldnt fit > on a single table, of course. > > The main goal would be to get multiple results while scanning the > table[s] once only > thus getting results in a faster way. PostgreSQL 8.3 contains great improvements in this area, you can simply start the selects from concurrent connections, and the backend will synchronize the scans. Regards, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] [SQL] two queryes in a single tablescan
Markus Schaber <[EMAIL PROTECTED]> schrieb: > > is there any way to get both results in a single query, > > eventually through stored procedure? > > The retrieved [count(*),A] ; [count(*),B)] data couldnt fit > > on a single table, of course. > > > > The main goal would be to get multiple results while scanning the > > table[s] once only > > thus getting results in a faster way. > > PostgreSQL 8.3 contains great improvements in this area, you can simply > start the selects from concurrent connections, and the backend will > synchronize the scans. works this right across different transactions? I mean, for instance, TX a insert rows and TX b insert other rows and both clients (with different transactions) starts a seq-scan? Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] [SQL] two queryes in a single tablescan
On Oct 20, 2007, at 12:19 PM, Andreas Kretschmer wrote: Markus Schaber <[EMAIL PROTECTED]> schrieb: is there any way to get both results in a single query, eventually through stored procedure? The retrieved [count(*),A] ; [count(*),B)] data couldnt fit on a single table, of course. The main goal would be to get multiple results while scanning the table[s] once only thus getting results in a faster way. PostgreSQL 8.3 contains great improvements in this area, you can simply start the selects from concurrent connections, and the backend will synchronize the scans. works this right across different transactions? I mean, for instance, TX a insert rows and TX b insert other rows and both clients (with different transactions) starts a seq-scan? If you are in read-committed mode and both backends start their scans after the other has made its insert, then yes. Note Markus's point that both queries must be initiated by concurrent connections. Since Postgres doesn't have any kind of shared transaction mechanism across connections then this is inherent. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
