Re: [BUGS] Two foreign keys in one table both referencing same record in primary table gives error on update of primary table
Thank you Stephan, for the quick reply. Please, do you know if your fixes will be available soon, or are they already in the development release or any of the production releases ? > Yes, it's because it sees the intermediate > state (6,1) when it shouldn't (it processes > each foreign key separately and does the cascade > update which places it in an "invalid" state > since the 1 key no longer exists). > > I believe the fixes I've been working on for deferred > constraints will make this case work as well. > > > Bob Soeters ([EMAIL PROTECTED]) reports a bug with a severity of 2 > > Short Description Two foreign keys in one table both referencing same > > record in primary table gives error on update of primary table ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] BUG(fixed) in CREATE TABLE ADD CONSTRAINT...
--
[EMAIL PROTECTED]
Hi !
My system is i686/Linux Mandrake 7.0/Postgresql v-7.0.2.
I found a bug in the sql command ALTER TABLE ADD CONSTRAINT..., when I tried to add a
composite foreign key constraint
(a FK with more than one attribute). The problem is in the file identified by
$Header: /home/projects/pgsql/cvsroot/pgsql/src/backend/commands/command.c,v 1.71
2000/04/12 17:14:57 momjian Exp $
in the code lines #1139 to #1150, when the function AlterTableAddConstraint() tries to
construct the vector of the trigger´s tgargs.
>From the position 4 and forward, it must collect the pairs of fk_attrs and pk_attrs
>(interleaved), but the current code put first all
fk_attrs and then all the pk_attrs, leading to an error.
I fixed the bug and tested the update and now it works well. I send you a "diff -c
command.c command.fixed.c" (with the diff :
GNU diffutils version 2.7) and the output is:
*** command.c Sun May 6 21:13:06 2001
--- command.fixed.c Mon Jul 9 19:58:21 2001
***
*** 19,24
--- 19,25
* manipulating code in the commands/ directory, should go
* someplace closer to the lib/catalog code.
*
+ *
*-
*/
#include "postgres.h"
***
*** 1138,1152
{
Ident *fk_at = lfirst(list);
! trig.tgargs[count++] = fk_at->name;
}
foreach(list, fkconstraint->pk_attrs)
{
Ident *pk_at = lfirst(list);
! trig.tgargs[count++] = pk_at->name;
}
! trig.tgnargs = count;
scan = heap_beginscan(rel, false, SnapshotNow, 0,
NULL);
AssertState(scan != NULL);
--- 1139,1156
{
Ident *fk_at = lfirst(list);
! trig.tgargs[count] = fk_at->name;
! count+=2;
}
+ count = 5;
foreach(list, fkconstraint->pk_attrs)
{
Ident *pk_at = lfirst(list);
! trig.tgargs[count] = pk_at->name;
! count+=2;
}
! trig.tgnargs = (count-1);
scan = heap_beginscan(rel, false, SnapshotNow, 0,
NULL);
AssertState(scan != NULL);
***
*** 1220,1223
LockRelation(rel, lockstmt->mode);
heap_close(rel, NoLock);/* close rel, keep lock */
! }
--- 1224,1227
LockRelation(rel, lockstmt->mode);
heap_close(rel, NoLock);/* close rel, keep lock */
! }
I wish it would help you. If it´s necessary, drop me a line. Regards
Jose Luis Ozzano.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[BUGS] Documentation Bug related to Inheritance
Inheritance is one of the most useful and 'marketed' features in PostgreSQL. However its behavior is not what one expected it to be. Inherited tables do not inherit some constraints, including primary and foreing keys. This information is very important, specially for newbies, who can get very frustated by referential integrity violation happening when they would not be expected to happen, and vice versa. The mailing lists have many questions related to it, including: http://fts.postgresql.org/db/mw/msg.html?mid=118834 where Peter Eisentraut remommends one NOT TO mix inheritance and foreing keys; http://fts.postgresql.org/db/mw/msg.html?mid=1022481 where Stephan Szabo advices the reader to see past discussion in the GENERAL list for more information and http://fts.postgresql.org/db/mw/msg.html?mid=122007 where Bruce Momjian adds this issue to the TODO list. Looking for past discussions in the lists require too much work, since this is a very popular question. ( you find a lot of questions and answers, but those only tells the reader 'Currently you can't do that' ) I would suggest this question (referential integrity in inherited tables) to be added to the FAQ, since in think it is a bug in the documentation. Perhaps it would also be useful a documentation page (probably the inheritance one - http://www.postgresql.org/idocs/index.php?inherit.html) warning the reader about this particular issue. Additionally, this page should help the user to work through it telling how to use inheritance and primary keys, using the current postgreSQL features (rules, triggers, inheritance itself, etc.) or the 'pure' referential model. A simple example of the problem follows: CREATE TABLE student ( id serial, namevarchar(35), email varchar(25), UNIQUE ( email ), PRIMARY KEY ( id ) ); CREATE TABLE graduate ( thesisname varchar (100) ) INHERITS ( student ); CREATE TABLE address ( student int4 REFERENCES student, add1varchar(50), add2varchar(50) ); testeinh=> INSERT INTO student ( name, email ) VALUES ( 'Danilo', 'danilogh@comp' ); INSERT 28259 1 -- *[ Cannot duplicate UNIQUE value, as expected ]* testeinh=> INSERT INTO student ( name, email ) VALUES ( 'Another Danilo', 'danilogh@comp' ); ERROR: Cannot insert a duplicate key into unique index student_email_key -- *[ Should stop duplicating UNIQUE value, defined in student ]*** -- *[ Strange behavior: must be documented ]*** testeinh=> INSERT INTO graduate ( name, email ) VALUES ( 'Yet Anothe Danilo', 'danilogh@comp' ); INSERT 28261 1 -- *[ Duplicated UNIQUE field: 'broken' referential integrity ] testeinh=> SELECT * FROM student*; id | name| email +---+--- 1 | Danilo| danilogh@comp 3 | Yet Anothe Danilo | danilogh@comp (2 rows) -- *[ Referential integrity tested, and OK ]* testeinh=> INSERT INTO address ( student, add1 ) VALUES ( 1, 'Some Street' ); INSERT 28262 1 -- *[ No id=2 in table: normal behavior ]* testeinh=> INSERT INTO address ( student, add1 ) VALUES ( 2, 'Some Other Street' ); ERROR: referential integrity violation - key referenced from address not found in student -- *[ Should allow inclusion, since id=3 exists for table inherited from student ]*** -- *[ Strange behavior: must be documented ]*** testeinh=> INSERT INTO address ( student, add1 ) VALUES ( 3, 'Some Other Street' ); ERROR: referential integrity violation - key referenced from address not found in student Sorry my broken English, I'd be glad to explain better any confusing passage. Regards, Danilo Hashimoto ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] help for:FATAL 1: configuration file `postgresql.conf' has wrong permissions
pgsql-bugs: Hello! I install postgres 7.1 on solaris 2.6(sparc cpu).And create some db,runing normal.Today,I want to restart the database,it report "FATAL 1: configuration file `postgresql.conf' has wrong permissions",I check and change the permission to 775 ,but fail either,can you help.I need your help. Thanks! Best regard! BonoLin(ÁÖº£µ¤) Email:[EMAIL PROTECTED] 2001-07-19 12:07:34 _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] check contraint allows illegal value?
[EMAIL PROTECTED] (Lee Harr) writes:
> Is this a bug?
> create table foo( c char(2)
> check (c in ('09', '10', '11', '12', 'n/a'))
> );
I don't think so; at least, I don't think there's any way in the general
case for the system to realize that some part of a check condition can
never succeed or never fail. (See "halting problem".)
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] Documentation Bug related to Inheritance
Hi, i've posted various problems regarding inheritance in postgres, Never see a reply :( Well, beside the referential constraint issue ( that can be solved by hand ), the most frustrating is that queries like DELETE FROM table* where key = some_value Fails ( parser error ). ( UPDATE either ) I you have some direct contact with the developers please forward this issue. Thanx. Danilo Gonzalez Hashimoto writes: > Inheritance is one of the most useful and 'marketed' features in PostgreSQL. > However its behavior is not what one expected it to be. Inherited tables do > not inherit some constraints, including primary and foreing keys. > This information is very important, specially for newbies, who can get very > frustated by referential integrity violation happening when they would not > be expected to happen, and vice versa. > The mailing lists have many questions related to it, including: > > http://fts.postgresql.org/db/mw/msg.html?mid=118834 > where Peter Eisentraut remommends one NOT TO mix inheritance > and foreing keys; > http://fts.postgresql.org/db/mw/msg.html?mid=1022481 > where Stephan Szabo advices the reader to see past discussion in the > GENERAL list for more information > and http://fts.postgresql.org/db/mw/msg.html?mid=122007 > where Bruce Momjian adds this issue to the TODO list. > > Looking for past discussions in the lists require too much work, since this > is a very popular question. ( you find a lot of questions and answers, but > those only tells the reader 'Currently you can't do that' ) > I would suggest this question (referential integrity in inherited tables) to > be added to the FAQ, since in think it is a bug in the documentation. > Perhaps it would also be useful a documentation page (probably the inheritance > one - http://www.postgresql.org/idocs/index.php?inherit.html) warning the > reader about this particular issue. > Additionally, this page should help the user to work through it telling how > to use inheritance and primary keys, using the current postgreSQL features > (rules, triggers, inheritance itself, etc.) or the 'pure' referential model. > > A simple example of the problem follows: > > CREATE TABLE student ( > id serial, > namevarchar(35), > email varchar(25), > UNIQUE ( email ), > PRIMARY KEY ( id ) ); > > CREATE TABLE graduate ( > thesisname varchar (100) > ) INHERITS ( student ); > > CREATE TABLE address ( > student int4 REFERENCES student, > add1varchar(50), > add2varchar(50) > ); > > > testeinh=> INSERT INTO student ( name, email ) VALUES ( 'Danilo', 'danilogh@comp' ); > INSERT 28259 1 > > -- *[ Cannot duplicate UNIQUE value, as expected ]* > testeinh=> INSERT INTO student ( name, email ) VALUES ( 'Another Danilo', >'danilogh@comp' ); > ERROR: Cannot insert a duplicate key into unique index student_email_key > > -- *[ Should stop duplicating UNIQUE value, defined in student ]*** > -- *[ Strange behavior: must be documented ]*** > testeinh=> INSERT INTO graduate ( name, email ) VALUES ( 'Yet Anothe Danilo', >'danilogh@comp' ); > INSERT 28261 1 > > -- *[ Duplicated UNIQUE field: 'broken' referential integrity ] > testeinh=> SELECT * FROM student*; > id | name| email > +---+--- > 1 | Danilo| danilogh@comp > 3 | Yet Anothe Danilo | danilogh@comp > (2 rows) > > -- *[ Referential integrity tested, and OK ]* > testeinh=> INSERT INTO address ( student, add1 ) VALUES ( 1, 'Some Street' ); > INSERT 28262 1 > > -- *[ No id=2 in table: normal behavior ]* > testeinh=> INSERT INTO address ( student, add1 ) VALUES ( 2, 'Some Other Street' ); > ERROR: referential integrity violation - key referenced from address not >found in student > > -- *[ Should allow inclusion, since id=3 exists for table inherited from >student ]*** > -- *[ Strange behavior: must be documented ]*** > testeinh=> INSERT INTO address ( student, add1 ) VALUES ( 3, 'Some Other Street' ); > ERROR: referential integrity violation - key referenced from address not >found in student > > Sorry my broken English, I'd be glad to explain better any confusing passage. > Regards, > > Danilo Hashimoto > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTE
Re: [BUGS] Documentation Bug related to Inheritance
>> Well, beside the referential constraint issue ( that can be solved by >> hand ), the most frustrating is that queries like >> DELETE FROM table* where key = some_value >> Fails ( parser error ). ( UPDATE either ) Not as of 7.1. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] libpgtcl doesn't use UTF encoding of TCL
> > On Wed, 18 Jul 2001, Tom Lane wrote: > > > > > Reinhard Max <[EMAIL PROTECTED]> writes: > > > > On Wed, 18 Jul 2001, Bruce Momjian wrote: > > > >> Do you have any idea how this will work with earlier TCL versions? > > > > > > > It won't. If pgtcl is supposed to still be able to compile with older > > > > versions of Tcl, the changes have to be made a compile time option. > > > > > > Please do that and resubmit the patch. > > > > OK, I'll pack the new stuff inside #ifdef TCL_UTF8 and define that if > > the Tcl version is 8.1 or greater. > > Is the TCL_UTF8 some variable that gets set at tcl runtime? I hope so. I now realize we can't have this configure at runtime. It has to read the tcl include file for the version it is about to be linked to. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 3: 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
Re: [BUGS] libpgtcl doesn't use UTF encoding of TCL
On Wed, Jul 18, 2001 at 02:53:22PM -0400, Tom Lane wrote: > > It won't. If pgtcl is supposed to still be able to compile with older > > versions of Tcl, the changes have to be made a compile time option. > > Please do that and resubmit the patch. We really don't want to give up > backwards compatibility just yet. > Thank you, gentlemans :-) I will be interest with this result too. -- Eugene Faukin SOLVO Ltd. Company ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] Re: performance
On Sat, 30 Jun 2001 13:10:17 +0800, Zhan Yi <[EMAIL PROTECTED]> wrote: > I found performance problem. > > createdbspeed fast > > after many inserts, updates then delete all from tables and redo the same > thing > speed degrade > > drop table and rebuild table redo operations > speed fast Make sure you read the manual regarding VACUUM. This sounds like it could be related to needing to VACUUM your tables. The good news is that it looks like 7.2 will have a much better way of dealing with the need to VACUUM. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] help for:FATAL 1: configuration file `postgresql.conf' has wrong permissions
bonolin <[EMAIL PROTECTED]> writes: > I install postgres 7.1 on solaris 2.6(sparc cpu).And create some > db,runing normal.Today,I want to restart the database,it report > "FATAL 1: configuration file `postgresql.conf' has wrong > permissions",I check and change the permission to 775 ,but fail > either,can you help.I need your help. Thanks! A look at the source code shows it wants 744 or less. This is probably overly restrictive; in fact, I would argue that there should be no such check at all. We do not do runtime checks for permissions on any other files, and some of them are far more sensitive than postgresql.conf (password files for example). Peter, what is the rationale for having this check? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] help for:FATAL 1: configuration file `postgresql.conf' has wrong permissions
Bruce Momjian <[EMAIL PROTECTED]> writes: > Isn't the file in /data, which is 700. Why do we care what permissions > we give it? Indeed. I could understand having a postmaster-start-time check that $PGDATA has mode 700 (initdb makes this true anyway, but having the postmaster double-check is not unreasonable). But I don't see why we should expend cycles on checking the permissions of stuff inside $PGDATA. regards, tom lane ---(end of broadcast)--- TIP 3: 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
Re: [BUGS] help for:FATAL 1: configuration file `postgresql.conf' haswrong permissions
> bonolin <[EMAIL PROTECTED]> writes: > > I install postgres 7.1 on solaris 2.6(sparc cpu).And create some > > db,runing normal.Today,I want to restart the database,it report > > "FATAL 1: configuration file `postgresql.conf' has wrong > > permissions",I check and change the permission to 775 ,but fail > > either,can you help.I need your help. Thanks! > > A look at the source code shows it wants 744 or less. > > This is probably overly restrictive; in fact, I would argue that there > should be no such check at all. We do not do runtime checks for > permissions on any other files, and some of them are far more sensitive > than postgresql.conf (password files for example). Peter, what is the > rationale for having this check? Isn't the file in /data, which is 700. Why do we care what permissions we give it? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Documentation Bug related to Inheritance
> > Hi, i've posted various problems regarding inheritance in postgres, > Never see a reply :( > > Well, beside the referential constraint issue ( that can be solved by > hand ), the most frustrating is that queries like > > DELETE FROM table* where key = some_value > > Fails ( parser error ). ( UPDATE either ) > > I you have some direct contact with the developers please forward this > issue. Are you sure about this? I see all sorts of inheritance updates in the regression tests: UPDATE a SET aa='' WHERE aa=''; UPDATE ONLY a SET aa='z' WHERE aa='a'; UPDATE b SET aa='zzz' WHERE aa='aaa'; UPDATE ONLY b SET aa='zzz' WHERE aa='aaa'; UPDATE a SET aa='zz' WHERE aa LIKE 'aaa%'; -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] check contraint allows illegal value?
Is this a bug?
create table foo( c char(2)
check (c in ('09', '10', '11', '12', 'n/a'))
);
CREATE
insert into foo values('09');
INSERT
insert into foo values('10');
INSERT
insert into foo values('08');
ExecAppend: rejected due to CHECK constraint foo_c
insert into foo values('n/a');
ExecAppend: rejected due to CHECK constraint foo_c
clearly this was a 'whups' on my part, but it would have
been nice to know that my constraint was bogus at the
time the table was created.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [BUGS] libpgtcl doesn't use UTF encoding of TCL
> On Wed, 18 Jul 2001, Tom Lane wrote: > > > Reinhard Max <[EMAIL PROTECTED]> writes: > > > On Wed, 18 Jul 2001, Bruce Momjian wrote: > > >> Do you have any idea how this will work with earlier TCL versions? > > > > > It won't. If pgtcl is supposed to still be able to compile with older > > > versions of Tcl, the changes have to be made a compile time option. > > > > Please do that and resubmit the patch. > > OK, I'll pack the new stuff inside #ifdef TCL_UTF8 and define that if > the Tcl version is 8.1 or greater. Is the TCL_UTF8 some variable that gets set at tcl runtime? I hope so. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] libpgtcl doesn't use UTF encoding of TCL
Reinhard Max writes: > OK, I'll pack the new stuff inside #ifdef TCL_UTF8 and define that if > the Tcl version is 8.1 or greater. No, please add a configure check for Tcl_UtfToExternalDString or some other function representative of this interface.. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] libpgtcl doesn't use UTF encoding of TCL
> On Fri, 20 Jul 2001, Peter Eisentraut wrote: > > > Reinhard Max writes: > > > > > OK, I'll pack the new stuff inside #ifdef TCL_UTF8 and define that if > > > the Tcl version is 8.1 or greater. > > > > No, please add a configure check for Tcl_UtfToExternalDString or > > some other function representative of this interface.. > > Why make simple things complicated? > Tcl changed it's internal string representation starting with release > 8.1 . It is not an interface one can decide whether to use it or not. > Every extension that imports or exports strings and gets compiled for > Tcl >= 8.1 has to make sure that they are UTF8 regardless, if it uses > the Tcl_*Utf*DString functions or something else. So I consider it > sufficient to define TCL_UTF8 if Tcl's Version is >= 8.1 as I did in > the patch that was attached to my last mail. I think he is OK checking the TCL version. It is pretty common to check the TCL include file for symbols and handle things that way. Do we test any other TCL include defines from configure? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] libpgtcl doesn't use UTF encoding of TCL
On Fri, 20 Jul 2001, Peter Eisentraut wrote: > Reinhard Max writes: > > > OK, I'll pack the new stuff inside #ifdef TCL_UTF8 and define that if > > the Tcl version is 8.1 or greater. > > No, please add a configure check for Tcl_UtfToExternalDString or > some other function representative of this interface.. Why make simple things complicated? Tcl changed it's internal string representation starting with release 8.1 . It is not an interface one can decide whether to use it or not. Every extension that imports or exports strings and gets compiled for Tcl >= 8.1 has to make sure that they are UTF8 regardless, if it uses the Tcl_*Utf*DString functions or something else. So I consider it sufficient to define TCL_UTF8 if Tcl's Version is >= 8.1 as I did in the patch that was attached to my last mail. cu Reinhard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [BUGS] check contraint allows illegal value?
>
> Is this a bug?
>
> create table foo( c char(2)
> check (c in ('09', '10', '11', '12', 'n/a'))
> );
> CREATE
>
> insert into foo values('09');
> INSERT
>
> insert into foo values('10');
> INSERT
>
> insert into foo values('08');
> ExecAppend: rejected due to CHECK constraint foo_c
>
> insert into foo values('n/a');
> ExecAppend: rejected due to CHECK constraint foo_c
>
>
>
> clearly this was a 'whups' on my part, but it would have
> been nice to know that my constraint was bogus at the
> time the table was created.
That is interesting. You want the check constraint to be a valid value
for the column. I can see char() having this issue. Is this something
we should check for all types folks? Not sure.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
