[BUGS] BUG #3424: Not able to drop trigger(RI_trigger*) on specific table.
The following bug has been logged online: Bug reference: 3424 Logged by: S R Madhu Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.3 Operating system: Linux RH6.2 Description:Not able to drop trigger(RI_trigger*) on specific table. Details: We have created a table "student". Foreign key and triggers are created in student. We are able to map triggers and constraints from pg_class table. We have used following mapping mechanism to get the trigger name. pg_class.relfilenode -> pg_trigger.tgrelid. We have used following mapping mechanism to get the constraint name. pg_class.relfilenode -> pg_constraint.conrelid. Problem : After ALTER TABLE command to this table, We are not able to map triggers/constraints and tables. relfilenode in pg_class is changed after ALTER TABLE. but this is not reflected in pg_constraint/pg_triggers. Please do the needful. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] BUG #3424: Not able to drop trigger(RI_trigger*) on specific table.
S R Madhu wrote: We have created a table "student". Foreign key and triggers are created in student. We are able to map triggers and constraints from pg_class table. We have used following mapping mechanism to get the trigger name. pg_class.relfilenode -> pg_trigger.tgrelid. We have used following mapping mechanism to get the constraint name. pg_class.relfilenode -> pg_constraint.conrelid. Problem : After ALTER TABLE command to this table, We are not able to map triggers/constraints and tables. relfilenode in pg_class is changed after ALTER TABLE. but this is not reflected in pg_constraint/pg_triggers. pg_trigger.tgrelid and pg_constraint.conrelid reference the oid of the relation, not relfilenode. Initially, relfilenode happens to have the same value, but as you noticed, some commands like ALTER TABLE change it. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #3426: Rows disappear from complex join
The following bug has been logged online:
Bug reference: 3426
Logged by: David Flater
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.2.4
Operating system: GNU/Linux
Description:Rows disappear from complex join
Details:
Hello,
I am getting NO DATA on queries that attempt to match one of the "outer"
rows generated by an outer join.
The following test script can be run on an empty database to demonstrate the
problem I am having. The problem only appeared after I rewrote the view
BlankBallot to use a join instead of a subquery. The contents of the old
and new versions of BlankBallot are identical according to select *, but the
behavior of queries that try to match specific rows has changed.
The results I get and the results I expected are in comments around the
three select statements at the end.
Thank you,
DWF
--- cut here --
create table ReportingContext (
Name Text primary key
);
create table Contest (
ContestId Integer primary key,
DescriptionText not null,
N Integer not null check (N > 0),
MaxWriteInsInteger not null check (MaxWriteIns between 0 and N),
Rotate Boolean not null
);
create table Choice (
ChoiceId Integer primary key,
ContestIdInteger not null references Contest,
Name Text not null,
IsWriteInBoolean not null
);
create table BallotStyle (
StyleId Integer primary key,
Name Text not null
);
create table Ballot (
BallotId Integer primary key,
StyleId Integer not null references BallotStyle,
Accepted Boolean not null
);
create table VoterInput (
BallotId Integer references Ballot,
ChoiceId Integer references Choice,
Value Integer not null check (Value > 0),
primary key (BallotId, ChoiceId)
);
create table BallotStyleContestAssociation (
StyleIdInteger references BallotStyle,
ContestId Integer references Contest,
primary key (StyleId, ContestId)
);
create table BallotStyleReportingContextAssociation (
StyleId Integer references BallotStyle,
ReportingContext Text references ReportingContext,
primary key (StyleId, ReportingContext)
);
create table BallotReportingContextAssociation (
BallotId Integer references Ballot,
ReportingContext Text references ReportingContext,
primary key (BallotId, ReportingContext)
);
create view ReportingContextAssociationMerge (BallotId, ReportingContext)
as
select BallotId, ReportingContext
from BallotReportingContextAssociation
union
select BallotId, ReportingContext
from Ballot natural join BallotStyleReportingContextAssociation;
create view ReportingContextContestAssociation (ReportingContext, ContestId)
as
select ReportingContext, ContestId
from BallotStyleReportingContextAssociation
natural join BallotStyleContestAssociation
union
select ReportingContext, ContestId
from BallotReportingContextAssociation
natural join Ballot
natural join BallotStyleContestAssociation;
create view BlankBallot (BallotId, StyleId, Accepted) as
select BallotId, StyleId, Accepted
from Ballot
natural left outer join VoterInput
where Value is null;
create view BlankBallotCounts (ReportingContext, Read, Counted) as
select Name, count(BallotId), count (nullif (Accepted, false))
from BlankBallot
natural join ReportingContextAssociationMerge
right outer join ReportingContext on (Name = ReportingContext)
group by Name;
insert into ReportingContext values
('Precinct 1'),
('District 1'),
('District 2');
insert into Contest (ContestId, Description, N,
MaxWriteIns, Rotate) values
(1, 'President, vote for at most 1', 1, 0, false);
insert into Choice (ChoiceId, ContestId, Name, IsWriteIn) values
(0, 1, 'Nada Zayro', false),
(1, 1, 'Oona Won', false),
(2, 1, 'Beeso Tu', false),
(3, 1, 'Tayra Tree', false),
(4, 1, 'Car Tay Fower', false);
insert into BallotStyle (StyleId, Name) values
(1, 'District 1 Style'),
(2, 'District 2 Style');
insert into BallotStyleContestAssociation (StyleId, ContestId) values
(1, 1),
(2, 1);
insert into BallotStyleReportingContextAssociation (StyleId,
ReportingContext) values
(1, 'Precinct 1'),
(1, 'District 1'),
(2, 'Precinct 1'),
(2, 'District 2');
insert into Ballot (BallotId, StyleId, Accepted) values
(0, 1, true),
(1, 2, true),
(2, 1, true),
(3, 2, true),
(4, 1, true),
(5, 2, true),
(6, 1, true),
(7, 2, true),
(8, 1, true),
(9, 2, true),
(10, 1, true),
(11, 2, true);
insert into VoterInput (BallotId, ChoiceId, Value) values
(1, 1, 1),
(2, 2, 1),
(3, 2, 1),
(4, 3, 1),
(5, 3, 1),
(6, 3, 1),
(7, 4, 1),
(8, 4, 1),
(9, 4, 1),
(10, 4, 1),
(11, 0, 1),
(11, 1, 1);
-- We have 3 rows, including one where reportingcontext = 'District 2'
Re: [BUGS] BUG #3426: Rows disappear from complex join
"David Flater" <[EMAIL PROTECTED]> writes: > I am getting NO DATA on queries that attempt to match one of the "outer" > rows generated by an outer join. Your test case works fine for me on CVS HEAD and 8.2 branch tip. I think it is another manifestation of this bug: http://archives.postgresql.org/pgsql-bugs/2007-05/msg00187.php which was fixed here: http://archives.postgresql.org/pgsql-committers/2007-05/msg00305.php regards, tom lane ---(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 #3426: Rows disappear from complex join
"David Flater" <[EMAIL PROTECTED]> writes: > I installed postgresql-snapshot.tar.bz2 2007-07-03 10:18:42 from > stable_snapshot and confirm that the problem does not reproduce there. (I > assume that this snapshot is 8.2 branch tip and CVS HEAD is 8.3 dev?) Hm, I would've thought HEAD actually. What does select version() say? regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] BUG #3426: Rows disappear from complex join
> From: Tom Lane [mailto:[EMAIL PROTECTED] > > "David Flater" <[EMAIL PROTECTED]> writes: > > I installed postgresql-snapshot.tar.bz2 2007-07-03 10:18:42 from > > stable_snapshot and confirm that the problem does not reproduce there. (I > > assume that this snapshot is 8.2 branch tip and CVS HEAD is 8.3 dev?) > > Hm, I would've thought HEAD actually. What does select version() say? PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.0 (It's definitely not 8.2.4 but perhaps 8.2.4 + 1/2) -- David Flater, National Institute of Standards and Technology, U.S.A. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] BUG #3426: Rows disappear from complex join
> From: Tom Lane [mailto:[EMAIL PROTECTED] > > "David Flater" <[EMAIL PROTECTED]> writes: > > I am getting NO DATA on queries that attempt to match one of the "outer" > > rows generated by an outer join. > > Your test case works fine for me on CVS HEAD and 8.2 branch tip. I > think it is another manifestation of this bug: > http://archives.postgresql.org/pgsql-bugs/2007-05/msg00187.php > which was fixed here: > http://archives.postgresql.org/pgsql-committers/2007-05/msg00305.php I installed postgresql-snapshot.tar.bz2 2007-07-03 10:18:42 from stable_snapshot and confirm that the problem does not reproduce there. (I assume that this snapshot is 8.2 branch tip and CVS HEAD is 8.3 dev?) Thanks, -- David Flater, National Institute of Standards and Technology, U.S.A. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #3426: Rows disappear from complex join
"David Flater" <[EMAIL PROTECTED]> writes: >> From: Tom Lane [mailto:[EMAIL PROTECTED] >> Hm, I would've thought HEAD actually. What does select version() say? > PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.0 > (It's definitely not 8.2.4 but perhaps 8.2.4 + 1/2) Yeah, we don't change the version string until a new minor release is made. So it sounds like you're good to go. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #3418: Memory leak with sql EXCEPTION?
Hello, Tom
As of you last mail:
> Well, if you think there's some other memory leak then you need to
> submit a test case that demonstrates it.
Not to be going to make any assumptions on what might be malfunctioning
internally, I populated a test case that at least on our system
demonstrates behavior that imo couldn't take place if per-iteration
overhead was only a record in XIDs list.
I've made two cases actually, simple one that I already mentioned about
in my previous mails and more complex one that I tried to make somewhat
close to our work case where I first encountered the problem in question.
Simple:
/---/
create table dummy(
id integer primary key,
value varchar(10)
);
CREATE OR REPLACE FUNCTION "public"."test_smpl" (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;
/---/
Complex:
/---/
create table ref1 (
id integer primary key,
valuevarchar(10)
);
insert into ref1 values (1, 'ref#1');
create table cplx1 (
id integer primary key,
fid integer references ref1 (id),
value1 smallint not null,
value2 varchar(100) unique,
value3 varchar(100)
);
create table cplx2 (
fid integer references cplx1 (id),
value1 varchar(50),
value2 varchar(50),
value3 varchar(50),
value4 smallint,
value5 real
);
CREATE OR REPLACE FUNCTION "public"."test_cplx" (fi integer) RETURNS
"pg_catalog"."void" AS
$body$
declare
idx integer := 0;
viidinteger;
vivalue1smallint;
vsvalue2varchar;
vsvalue3varchar;
vsvalue2_1 varchar;
vsvalue2_2 varchar;
vsvalue2_3 varchar;
vivalue2_4 smallint;
vxvalue2_5 real;
begin
while idx < fi loop
idx := idx + 1;
viid := nextval('autoinc');
vivalue1 := idx % 32000;
vsvalue2 := 'val' || trunc(random() * 1000);
vsvalue3 := 'aaa' || idx;
vsvalue2_1 := 'bbb' || idx;
vsvalue2_2 := 'ccc' || idx;
vsvalue2_3 := 'ddd' || idx;
vivalue2_4 := trunc(random() * 2);
vxvalue2_5 := random();
begin
perform internal_insert(viid, vivalue1, vsvalue2, vsvalue3,
vsvalue2_1, vsvalue2_2, vsvalue2_3,
vivalue2_4, vxvalue2_5);
exception
when others then
raise notice '% %', idx, SQLERRM;
end;
end loop;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;
CREATE OR REPLACE FUNCTION "public"."internal_insert" (piid integer,
pivalue1 smallint, psvalue2 varchar, psvalue3 varchar, psvalue2_1
varchar, psvalue2_2 varchar, psvalue2_3 varchar, pivalue2_4 smallint,
pxvalue2_5 real) RETURNS "pg_catalog"."void" AS
$body$
begin
perform 1
from cplx1
where value2 = psvalue2;
if found then
raise exception 'not unique';
end if;
insert into cplx1
values (piid, 1, pivalue1, psvalue2, psvalue3);
if psvalue2_1 is not null or
psvalue2_2 is not null or
psvalue2_3 is not null or
pivalue2_4 is not null or
pxvalue2_5 is not null
then
insert into cplx2
values (piid, psvalue2_1, psvalue2_2, psvalue2_3, pivalue2_4,
pxvalue2_5);
end if;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;
/---/
Couple of notes on the second one: main function fills in 2 linked
indexed tables with certain data of various types, work prototype of the
function "test_cplx" is used as an interface to some applications (such
as CGI scripts) thus does some internal parameters check demonstrated
here as 'perform' block.
Our box is Linux 2.6.9-34 running on P4 1.8GHz.
Starting from new connection:
# ps -eo %cpu,cputime,vsize,size,rss,pid,cmd | grep postgres
0.0 00:00:00 141524 2620 4912 21861 postgres: postgres <...> idle
--
I do 'select test_smpl(10);' and near the end of the transaction get
the following report:
11.9 00:00:05 143988 4968 13712 21861 postgres: postgres <...> SELECT
--
When I do 'select test_cplx(10)' I get:
99.6 00:01:01 971552 832508 858012 21812 postgres: postgres <...> SELECT
-
I should also note here that it seems that the allocated memory is not
freed after th transaction ends, so if I start next select memory
consumption continues to grow.
Having commented out 'begin' and
