Re: [SQL] [NOVICE] Suspend Referential Integrity?
JJ> I'm trying to port a database from Oracle to PostgreSQL. I used a perl script, JJ> ora2pg to extract the info from Oracle. JJ> The table data was extracted in alphabetical order. When I attempt to load it, JJ> I get referential integrity violations (eg. I attempt to load CUSTOMER, but JJ> CUSTOMOER depends on the SOURCE table, which hasn't been loaded yet). JJ> Is there a way to temporarily suspend RI checking so I can load the data and JJ> then fix it later? JJ> = JJ> | Jim Jarrett,Madison, WI 94 Passat GLX | JJ> | mailto:[EMAIL PROTECTED] 81 Rabbit Convertible 16v | JJ> | | JJ> |Any problem can be solved with the proper application of | JJ> | Force, Heat, Chemicals, or Money. | JJ> JJ> ---(end of broadcast)--- JJ> TIP 6: explain analyze is your friend You can switch off all triggers on a table. update pg_class set reltriggers=0 where relname='your_table_name'; To switch back: update pg_class set reltriggers=(select count(*) from pg_triggers where pg_class.oid=tgrelid) where relname='your_table_name'; DAQ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] unsubscribe
Timothy Smith wrote: i have the following function in plpgsql giving stynax errors all over the place. i have doen createlang on the db, as far as i can see i'm right. is there anything obviously wrong? one thing to note is i followed this example http://www.zigo.dhs.org/postgresql/#insert_or_update and it gives the same errors. "ERROR: unterminated dollar-quoted string at or near "$$ BEGIN LOOP" CREATE OR REPLACE FUNCTION insert_update_daily_takings (ID BIGINT, TillName VARCHAR, Tape NUMERIC(10,2), Cash NUMERIC(10,2), GM NUMERIC(10,2), VenueManager NUMERIC(10,2), AsstManager NUMERIC(10,2), BarManager NUMERIC(10,2), PRCards NUMERIC(10,2), otherPromo NUMERIC(10,2), Functions NUMERIC(10,2), Accounts NUMERIC(10,2), Spill NUMERIC(10,2), Orings NUMERIC(10,2), Variance NUMERIC(10,2) ) RETURNS VOID AS $$ BEGIN LOOP UPDATE daily_takings SET till_name = TillName, tape = Tape, cash = Cash, promo_manager = GM, venue_manager = VenueManager, asst_manager = AsstManager, bar_manager = BarManager, pr_cards = PRCards, other_promo = otherPromo, functions = Functions, accounts = Accounts, spill = Spill, o_rings = Orings, variance = Variance WHERE id = ID AND till_name = TillName; IF found THEN RETURN; END IF; BEGIN INSERT INTO daily_takings (id, till_name, tape, cash, promo_manager, venue_manager, asst_manager, bar_manager, pr_cards, other_promo, functions, accounts, spill, o_rings, variance) VALUES (ID, TillName, Tape, Cash, GM, VenueManager, AsstManager, BarManager, PRCards, otherPromo, Functions, Accounts, Spill, Orings, Variance); RETURN; EXCEPTION WHEN unique_violation THEN NULL END; END LOOP; END; $$ LANGUAGE plpgsql; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Faster count(*)?
Salve. I understand from various web searches and so on that PostgreSQL's MVCC mechanism makes it very hard to use indices or table metadata to optimise count(*). Is there a better way to guess the "approximate size" of a table? I'm trying to write a trigger that fires on insert and performs some maintenance (collapsing overlapping boxes into a single large box, specifically) as the table grows. My initial attempt involved count(*) and, as the number of pages in the table grew, that trigger bogged down the database. Any thoughts? -O ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: **SPAM** [SQL] Faster count(*)?
I believe running count(*) means fulltable scan, and there's no way to do it without it. But what about some "intermediate" table, with the necessary counts? That means to create a table with values (counts) you need, and on every insert/delete/update increment or decrement the appropriate values. This way you won't need the count(*) query anymore, and the performance should be much better. t.v. > Salve. > I understand from various web searches and so on that PostgreSQL's MVCC > mechanism makes it very hard to use indices or table metadata to optimise > count(*). Is there a better way to guess the "approximate size" of a table? > I'm trying to write a trigger that fires on insert and performs some > maintenance (collapsing overlapping boxes into a single large box, > specifically) as the table grows. My initial attempt involved count(*) and, > as the number of pages in the table grew, that trigger bogged down the > database. > Any thoughts? ---(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
Re: **SPAM** [SQL] Faster count(*)?
[EMAIL PROTECTED] writes: > I believe running count(*) means fulltable scan, and there's no way > to do it without it. But what about some "intermediate" table, with > the necessary counts? There's a fairly complete discussion in the PG list archives of a reasonably-efficient scheme for maintaining such counts via triggers. It wasn't efficient enough that we were willing to impose the overhead on every application ... but if you really NEED a fast count(*) you could implement it. I'd like to see someone actually do it and put up working code on pgfoundry; AFAIK it's only a paper design so far. If you only want a very-approximate count, the best bet is to rely on the planner's estimates, eg regression=# explain select * from tenk1; QUERY PLAN - Seq Scan on tenk1 (cost=0.00..458.00 rows=1 width=244) ^ Current best practice is to run the explain and parse out the "rows" figure using a perl (or axe-of-choice) regexp, though we could be persuaded to supply a simpler API if there's enough demand for it. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: **SPAM** [SQL] Faster count(*)?
On Tue, Aug 09, 2005 at 10:49:14PM -0400, Tom Lane wrote: > Current best practice is to run the explain and parse out the "rows" > figure using a perl (or axe-of-choice) regexp, though we could be > persuaded to supply a simpler API if there's enough demand for it. Somebody else requested a row-count-estimate function a couple of weeks ago: http://archives.postgresql.org/pgsql-admin/2005-07/msg00256.php -- Michael Fuhr ---(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
