Re: [BUGS] BUG #3417: Foreign key constraint violation occurs unexpectedly
Gregory, Thanks for the reply. The data has been added via a file, as it is static data, no user input for this table, and yes I have run the file many times. I have accidently left some information out however that I believe will probably add some light on this. The data has been added to this table via an inherited table. Reference is the parent or base table, the region table is the child table, I have inserted records into the region table, and hence they go into the base table also. I never specify the id column in my inserts it is a serial column. I have since read some doco that postgres does not support referential integrity with inherited tables (shame this, I love the feature), but was'nt sure under what circumstance. This is most likely why this is not working. This is perhaps not a bug after all, appologies. Tried your query with set enable_seqscan=off and then the result of the query brings back the one row as expected. I'll search around and try to find when and if inhertance will be supported with RI. Thanks again. Regards David On Thu, 2007-06-28 at 22:48, Gregory Stark wrote: > "David Boesch" <[EMAIL PROTECTED]> writes: > > > I add data to reference > > > > select * from reference shows as > > > > id | name | description > > ++ > > 11 | rd | road > > 12 | st | street > > 13 | way| way > > 14 | close | close > > 15 | bend | bend > > 3 | vic| victoria > > 4 | nsw| new south wales > > 5 | qld| queensland > > 6 | nt | northern territory > > 7 | sa | south australia > > How did you add this data? Given that the ids are out of order I assume you've > updated or deleted and re-inserted records a few times? That shouldn't break > anything but it's possible the insert on table a doesn't see the same version > of this table that you're looking at with the select. > > Also, just to check that there's nothing wrong with the inex, what do you get > if you do: > > enable_seqscan = off; > select * from reference where id = 7; -- David Boesch 0410452873 95633008 9 Ferntree Gully Rd Oakleigh 3166 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[BUGS] BUG #3418: Memory leak with sql EXCEPTION?
The following bug has been logged online: Bug reference: 3418 Logged by: Viatcheslav Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.4 Operating system: RedHat Linux kernel 2.6.9-34.EL Description:Memory leak with sql EXCEPTION? Details: On our system we came across the following issue: /---/ create table dummy( id integer primary key, value varchar(10) ); CREATE OR REPLACE FUNCTION "public"."test" (fi integer) RETURNS "pg_catalog"."void" AS $body$ declare vi integer; idx integer := 0; begin while idx < fi loop idx := idx + 1; begin insert into dummy values (idx, idx::varchar); exception when others then raise exception '% %', idx, 'stop'; end; end loop; end; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER; select test(1000); /---/ We've been watching CPU and memory usage during the execution of the 'select' with and without exception trap of 'insert'. While in the second case memory usage kept being relatively low and stayed constant at the level of approx 2.9Mb across the execution time, in the first case it gradually raised with step of approx 6Mb up to somewhat 140Mb in the end. With more complicated PL/PgSQL code (with the same structure: begin - exception - end block in the main function, some complex function (without exceptions inside them) doing actual job were called from there) we've run out of virtual memory (> 1Gb) already with 3 cycles loop and the process got killed by operating system. Again, everything worked just fine with negligible memory consumption without exceptions. We are aware of additional costs of exceptions yet this one appears to be a memory leak. My apologies for poor-styled report in advance for this is my first one. ---(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: [BUGS] BUG #3418: Memory leak with sql EXCEPTION?
"Viatcheslav" <[EMAIL PROTECTED]> writes: > We are aware of additional costs of exceptions yet this one appears to be a > memory leak. AFAICT, the only memory consumed per-iteration by your example is the subtransaction XID that's added to the list of XIDs for eventual commit. It's not really possible to dispense with storing it. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
