Re: [SQL] Make Trigger run after completion of ENTIRE transaction
Hi, Henry, Henry Ortega wrote: > This maybe more of a theoretical question, can you actually make a > Trigger run > after completion of the entire transaction? [...] > ... another 20-30 more inserts.. Which frontend do yu use? Maybe COPY is better than insert. > The trigger actually runs on each Insert and therefore slows down the > Insert quite a bit. This sounds like the trigger itsself could be optimized a bit, or does some work that is not best suited to be in a trigger. > My question is, can you tell the trigger to run after Commit? I assume the trigger has to do some integrity checking work? Could you reformulate it as foreign key or check constraints? (you may even call a complex plpgsql function from the check constraint). Most constraint checks can be deferred to the end of the transaction, see the docs for more details. HTH, 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Need a SQL to create sets of hobbies
In article <[EMAIL PROTECTED]>, "CN" <[EMAIL PROTECTED]> writes: > select * from x1; > name | hobby > ---+-- > John | music > John | arts > Bob | arts > Bob | music > Rocky | computer > Steve | arts > Steve | football > Tom | computer > Tom | music > (9 rows) > Now we have 4 sets of hobbies: > set 1: music, arts > set 2: computer > set 3: arts, football > set 4: computer, music > I am looking for an SQL that creates sets of hobbies in table x2 by > selecting from table x1: > CREATE TABLE x2 (sid INTEGER, hobby TEXT, PRIMARY KEY(sid,hobby)); > and makes x2 contain rows: > sid | hobby > ---+-- > 1 | music > 1 | arts > 2 | computer > 3 | arts > 3 | football > 4 | computer > 4 | music > where gid starts from 1. You could use something like that: CREATE TEMP TABLE tmp ( id SERIAL NOT NULL, name TEXT NOT NULL, PRIMARY KEY (id) ); INSERT INTO tmp (name) SELECT DISTINCT ON (ARRAY ( SELECT y1.hobby FROM x1 y1 WHERE y1.name = y2.name ORDER BY y1.name, y1.hobby )) y2.name FROM x1 y2; INSERT INTO x2 (sid, hobby) SELECT tmp.id, x1.hobby FROM tmp JOIN x1 ON x1.name = tmp.name; ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Dividing results from two tables with different time frames
On 9/18/06, Becky Hoff <[EMAIL PROTECTED]> wrote: The sum(dr.loan_starting_balance + dr.loan_net_change) part of the calculation have a single date, the last day in a date range chosen by the user Sum(ds.pulled_loan_total) and sum(ds.renew_loan_amount) both have a range of dates chosen by the user What I need it the result of the second _expression_ divided by the result of the first _expression_.Can you provide some data examples. Show a sample of the data you have to query, what results you are getting and what results you are really wanting so we can see where your queries are not giving you what you need. By your post it sounds like you just need to join your two queries and then use the formula:Sum(ds.pulled_loan_total) and sum(ds.renew_loan_amount ) / sum(dr.loan_starting_balance + dr.loan_net_change)but if it were this simple you probably would have it figured out by now. Data examples help us help you much better.== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
Re: [SQL] Nested loops are killing throughput
>Is that the one off gborg? It's broken because the equality function is >marked volatile, and so the planner is afraid to try to use it for >merging or hashing. (It's also not marked strict, which means you can >trivially crash the backend by passing it a null ...) Tom, you are a genius. I changed the supporting functions and started getting what I expected. You were right on the money and saved my bacon. I don't say it often enough: Thanks! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Need a SQL to create sets of hobbies
On 9/20/06, CN <[EMAIL PROTECTED]> wrote:
Hi!CREATE TABLE x1 (name TEXT, hobby TEXT, PRIMARY KEY(name,hobby));INSERT INTO x1 VALUES('John','music');INSERT INTO x1 VALUES('John','arts');INSERT INTO x1 VALUES('Bob','arts');INSERT INTO x1 VALUES('Bob','music');
INSERT INTO x1 VALUES('Rocky','copmputer');INSERT INTO x1 VALUES('Steve','arts');INSERT INTO x1 VALUES('Steve','football');INSERT INTO x1 VALUES('Tom','computer');INSERT INTO x1 VALUES('Tom','music');
select * from x1; name | hobby---+-- John | music John | arts Bob | arts Bob | music Rocky | computer Steve | arts Steve | football Tom | computer Tom | music
(9 rows)John and Bob have the same hobbies - music and arts. So music and artsare treated as one set of hobbies.Rocky has an unique set of interest - computer.Steve also likes arts just as John and Bob do, but he also has an
exclusive interest - football. Thus, his set of hobbies is unique -arts, football.One of Tom's hobbies, music, overlaps those of John, Bob, and Rocky; butcomputer does not. Hence his hobbies, computer and music, forms a new
set of hobbies.Now we have 4 sets of hobbies:set 1: music, artsset 2: computerset 3: arts, footballset 4: computer, musicI am looking for an SQL that creates sets of hobbies in table x2 by
selecting from table x1:CREATE TABLE x2 (sid INTEGER, hobby TEXT, PRIMARY KEY(sid,hobby));and makes x2 contain rows: sid | hobby---+--1 | music1 | arts2 | computer
3 | arts3 | football4 | computer4 | musicwhere gid starts from 1. Your best design is to break this into 3 tables:person ( person_id,
person_name)hobby ( hobby_id, hobby_name)person_hobby ( person_id, hobby_id)Then you can get the list of hobbies for each person like this:
SELECT
person.person_id,
array(
SELECT hobby_name
FROM person_hobby
INNER JOIN hobby ON (hobby.hobby_id = person_hobby.hobby_id)
WHERE person_hobby.person_id = person.person_id
ORDER BY hobby_name
) AS hobby_list
FROM personThen do this to flatten it back out:SELECT -- Gives you a unique id though using a SERIAL on a table would be better min(mysub.person_id), hobby.hobby_nameFROM (
SELECT person.person_id, array( SELECT hobby_name FROM person_hobby INNER JOIN hobby ON (hobby.hobby_id = person_hobby.hobby_id) WHERE person_hobby.person_id =
person.person_id ORDER BY hobby_name ) AS hobby_list FROM person) mysubINNER JOIN person_hobby ON (person_hobby.person_id = mysub.person_id)INNER JOIN hobby ON (hobby.hobby_id = person_hobby.hobby_id)
GROUP BY mysub.hobby_list, hobby.hobby_nameI did not try it so it may require a little tweaking to work. Also, I don't know what the performance would be like.==
Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
[SQL] ERROR: could not write block 196261 of temporary file: No space left
I got the following error during a psql session of a big transaction filling a temporary table, then selecting distinct values from it for insertion into a permanent table. CREATE TABLE CREATE TABLE ERROR: could not write block 196261 of temporary file: No space left on device HINT: Perhaps out of disk space? UPDATE 0 I can't tell if this was because I ran out of real disk space, or because of some disk space limit set in pg config files. The log file contains no extra information. After this failure the real disk space was not full, of course because the transaction died and apparently any used disk space got cleaned up. I also can't tell if the error came from the Copy or the Insert. Can someone help me figure this out? Here is the SQL. Create Table fragment_table (smarts character varying, frequency integer); Create Temporary Table temp_table (smarts character varying); Copy temp_table (smarts) from stdin; (millions of strings here generated programatically) \. Insert into fragment_table (smarts) Select distinct smarts from temp_table where gnova.oe_valid(smarts) and gnova.matches(gnova.cansmiles(smarts),smarts); Update fragment_table set frequency = (select count(smiles) from input_set where smiles is not null and gnova.matches(smiles,smarts) group by smarts); ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] ERROR: could not write block 196261 of temporary file: No space left
"TJ O'Donnell" <[EMAIL PROTECTED]> writes: > ERROR: could not write block 196261 of temporary file: No space left > on device > I can't tell if this was because I ran out of real disk space, > or because of some disk space limit set in pg config files. There is no "disk space limit in pg config files". Depending on your platform, that error could represent a per-user disk quota limit being enforced against the postmaster rather than genuine disk-full, but whatever the limit was it's at the OS level not Postgres. > I also can't tell if the error came from the Copy or the Insert. Since it says "temporary file", it's not talking about insertion of data into a real table (even a temporary one). It's talking about genuinely temporary intra-query workspace. I'd venture that the error comes from the SELECT DISTINCT step trying to write a sort temp file that there's not room for. (The fact that you see UPDATE 0 right after that is further evidence for this theory --- after the failure, there's nothing in fragment_table, so the UPDATE just falls through.) regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] UnSubscribe
[EMAIL PROTECTED] wrote: Date: Fri, 15 Sep 2006 11:16:06 +0200From: [EMAIL PROTECTED]To: [email protected]: [SQL] Omission of 'FROM' ? diff between pgsql 7.4.1 and pgsql 8.1.4hiI am upgrading from pgsql 7.4.1 to pgsql 8.1.4. However, some of the applicationqueries won't run on the newer version of pgsql.An example of the type of query is:SELECTprotein_external_links.protein_id, external_links.external_id,proteins.public_nameWHEREprotein_external_links.external_link_id = external_links.id andprotein_external_links.protein_id = proteins.id andexternal_links.external_database_id != 8 andexternal_links.external_id_type_id = 2;on pgsql7.4.1 what tables need to be queried seems to be implicitly deduced fromthe rest of the SQL without the need for a FROM clause.However, on pgsql 8.1.4 the following error is given:ERROR: missing FROM-clause entry for table "protein_external_links"Is there some query optimization config setting I can set in pgsql 8.1.4 to fixthis problem rather than having to change the queries in the code? I've readover the changes between pgsql 7 and pgsql 8 but nothing seems to stand out tome as referencing this problem. Anyone with more knowledge that can point me inthe right direction?ThanksKate---(end of broadcast)---TIP 9: In versions below 8.0, the planner will ignore your desire tochoose an index scan if your joining column's datatypes do notmatch Find out what India is talking about on - Yahoo! Answers India Send FREE SMS to your friend's mobile from Yahoo! Messenger Version 8. Get it NOW
[SQL] unique rows
I want to make a table that has unique rows - no problem. Create Table x (smarts Varchar Unique); I have input data that has many duplicates and until now I was creating a temp table allowing duplicates, and then Insert into x (smarts) select distinct smarts from tmpx; This is now failing when I have hundreds of millions of rows, running out of temp space (while trying to sort to accomplish the distinct?) So, is there a way (a constraint, a check?) that will simply REJECT a duplicate when I insert the raw data directly into x rather than FAILING on an error as it does with the unique constraint above? Thanks, TJ O'Donnell ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] UnSubscribe
*** Your mail has been scanned by InterScan MSS. *** ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Unsubscribe
---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
