Re: [BUGS] createdb comments
The following patch fixes the problem. Applied. --- Nigel J. Andrews wrote: > > > Head of development branch (7.3devel) has a problem applying a comment to a > database at the sametime as the database is created using the createdb > utility. > > The error message states the problem: > > pgsql-current-test$ createdb testdb Test > CREATE DATABASE > ERROR: Database comments may only be applied to the current database > createdb: comment creation failed (database was created) > > The database is created but the comment does indeed fail for the reason given, > as illustrated by: > > template1=# \c template1 > You are now connected to database template1. > template1=# comment on database testdb is 'Test'; > ERROR: Database comments may only be applied to the current database > template1=# \c testdb > You are now connected to database testdb. > testdb=# comment on database testdb is 'Test'; > COMMENT > > Sorry if this isn't news. > > > -- > Nigel J. Andrews > Director > > --- > Logictree Systems Limited > Computer Consultants > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- 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 Index: src/bin/scripts/createdb === RCS file: /cvsroot/pgsql/src/bin/scripts/createdb,v retrieving revision 1.21 diff -c -r1.21 createdb *** src/bin/scripts/createdb12 Apr 2002 09:37:10 - 1.21 --- src/bin/scripts/createdb11 Jun 2002 22:09:27 - *** *** 198,204 dbcomment=`echo "$dbcomment" | sed "s/'/\'/g"` ! ${PATHNAME}psql $PSQLOPT -d template1 -c "COMMENT ON DATABASE \"$dbname\" IS '$dbcomment'" if [ "$?" -ne 0 ]; then echo "$CMDNAME: comment creation failed (database was created)" 1>&2 exit 1 --- 198,204 dbcomment=`echo "$dbcomment" | sed "s/'/\'/g"` ! ${PATHNAME}psql $PSQLOPT -d "$dbname" -c "COMMENT ON DATABASE \"$dbname\" IS '$dbcomment'" if [ "$?" -ne 0 ]; then echo "$CMDNAME: comment creation failed (database was created)" 1>&2 exit 1 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Bug #690: pg_ctl doesn't act properly for option -w
[EMAIL PROTECTED] wrote: > shangwu ([EMAIL PROTECTED]) reports a bug with a severity of 3 > The lower the number the more severe it is. > > Short Description > pg_ctl doesn't act properly for option -w > > Long Description > If you start postmaster at some port number other than 5432 by pg_ctl with option >-w, it doesn't block any more. > The problem is that pg_ctl calls "psql -l" to check if the postmaster is started. >Running psql without correct port number gets immediately failed with error exit >code. Alas, inside pg_ctl, there is this comment: # FIXME: This is horribly misconceived. # 1) If password authentication is set up, the connection will fail. # 2) If a virtual host is set up, the connection may fail. # 3) If network traffic filters are set up tight enough, the connection #may fail. # 4) When no Unix domain sockets are available, the connection will #fail. (Using TCP/IP by default ain't better.) # 5) When a different port is configured, the connection will fail #or go to the wrong server. # 6) If the dynamic loader is not set up correctly (for this user/at #this time), psql will fail (to find libpq). # 7) If psql is misconfigured, this may fail. Feel free to send in a patch. :-) -- 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] Bug #690: pg_ctl doesn't act properly for option -w
shangwu ([EMAIL PROTECTED]) reports a bug with a severity of 3 The lower the number the more severe it is. Short Description pg_ctl doesn't act properly for option -w Long Description If you start postmaster at some port number other than 5432 by pg_ctl with option -w, it doesn't block any more. The problem is that pg_ctl calls "psql -l" to check if the postmaster is started. Running psql without correct port number gets immediately failed with error exit code. Sample Code No file was uploaded with this report ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Referential integrity problem postgresql 7.2 ?
Stephan Szabo <[EMAIL PROTECTED]> writes: > As a related side note. The other part of the original patch (the NOT > EXISTS in the upd/del no action trigger) was rejected. For match > full and match unspecified the same result can be reached by doing another > query which may be better than the subquery. Do you think that'd be > better? No opinion offhand; can you show examples of the alternatives you have in mind? 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] Referential integrity problem postgresql 7.2 ?
On 2002.06.11 at 14:43:17 -0400, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > It should work (and does in current sources). If you look in the archives > > you should be able to get info on how to patch 7.2 (it came up recently, > > I'm not sure which list, and Tom Lane sent the message in question). > > BTW, should we back-patch that into 7.2.*? I was resistant to the idea I would appreciate this. I doubt that I it would fix problem with update sometable set a=a+1 where there exist unique index on sometable(a), but it would make postgresql behavoir closer to standard SQL. In my (user) point of view, it is obvoisly bugfix, rather than added feature, so it has right to appear in 7.2.x release. > because of concern about lack of testing, but seeing that we've gotten > several complaints maybe we should do it anyway. -- Victor Wagner [EMAIL PROTECTED] Chief Technical Officer Office:7-(095)-748-53-88 Communiware.Net Home: 7-(095)-135-46-61 http://www.communiware.net http://www.ice.ru/~vitus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Referential integrity problem postgresql 7.2 ?
On Tue, 11 Jun 2002, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On Mon, 10 Jun 2002, Stephen R. van den Berg wrote: > >> ERROR: referential integrity violation - key referenced > >> from b not found in a > >> Or should it work because the check is deferred and in the > >> end no violations are present? > > > It should work (and does in current sources). If you look in the archives > > you should be able to get info on how to patch 7.2 (it came up recently, > > I'm not sure which list, and Tom Lane sent the message in question). > > BTW, should we back-patch that into 7.2.*? I was resistant to the idea > because of concern about lack of testing, but seeing that we've gotten > several complaints maybe we should do it anyway. If we're doing a 7.2.2, it may be worth it. I think that part of the patch (minus concerns about variables possibly not being reset, etc) is reasonably safe (and that part could be reasonably looked at again quickly) and did have some limited testing due to a couple of people getting the patch back during 7.2's development. As a related side note. The other part of the original patch (the NOT EXISTS in the upd/del no action trigger) was rejected. For match full and match unspecified the same result can be reached by doing another query which may be better than the subquery. Do you think that'd be better? I'd like to get the other side of this bug fixed so that at least the no action cases work reasonably correctly. :) ---(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] Referential integrity problem postgresql 7.2 ?
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Mon, 10 Jun 2002, Stephen R. van den Berg wrote: >> ERROR: referential integrity violation - key referenced >> from b not found in a >> Or should it work because the check is deferred and in the >> end no violations are present? > It should work (and does in current sources). If you look in the archives > you should be able to get info on how to patch 7.2 (it came up recently, > I'm not sure which list, and Tom Lane sent the message in question). BTW, should we back-patch that into 7.2.*? I was resistant to the idea because of concern about lack of testing, but seeing that we've gotten several complaints maybe we should do it anyway. 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] Referential integrity problem postgresql 7.2 ?
>From billy Tue Jun 11 13:38:51 2002 Date: Tue, 11 Jun 2002 10:54:27 -0700 (PDT) From: Stephan Szabo <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sender: [EMAIL PROTECTED] On Mon, 10 Jun 2002, Stephen R. van den Berg wrote: > Should the following piece of code cause an: > ERROR: referential integrity violation - key referenced > from b not found in a > Or should it work because the check is deferred and in the > end no violations are present? It should work (and does in current sources). If you look in the archives you should be able to get info on how to patch 7.2 (it came up recently, I'm not sure which list, and Tom Lane sent the message in question). I've verified that it does work in the current CVS checkout. -- Billy O'Connor ---(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] Referential integrity problem postgresql 7.2 ?
On Mon, 10 Jun 2002, Stephen R. van den Berg wrote: > Should the following piece of code cause an: > ERROR: referential integrity violation - key referenced > from b not found in a > Or should it work because the check is deferred and in the > end no violations are present? It should work (and does in current sources). If you look in the archives you should be able to get info on how to patch 7.2 (it came up recently, I'm not sure which list, and Tom Lane sent the message in question). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] referential integrity error
On Tue, 11 Jun 2002, [ISO-8859-1] Matej HollĂ˝ wrote: > /* > If a table contains two or more foreign keys referencing the same > table and they reference the same record in the other table, the > record's primary key cannot be changed even if the keys have the > "ON UPDATE CASCADE" clause (see example below). It will say that > "referential integrity error - key referenced from `table' not found > in `table'". > */ > > CREATE TABLE "Bug1" ( > "ID" INT4, > "Description" TEXT NOT NULL, > PRIMARY KEY("ID") > ); > CREATE TABLE "Bug2" ( > "ID" INT4, > "Source" INT4 NOT NULL > REFERENCES "Bug1" ON UPDATE CASCADE ON DELETE NO ACTION, > "Destination" INT4 NOT NULL > REFERENCES "Bug1" ON UPDATE CASCADE ON DELETE NO ACTION, > "Description" TEXT NOT NULL, > PRIMARY KEY ("ID") > ); > INSERT INTO "Bug1" VALUES (1,'Place'); > INSERT INTO "Bug2" VALUES (10,1,1,'Move from Place to Place'); > UPDATE "Bug1" SET "ID"=2 WHERE "ID"=1; This works in current sources. There's a patch floating around which needs a little work but can be used as a basis to patch earlier versions into working for this case (see archives). ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Bug #689: Bug at moving cursor on joined tables
[EMAIL PROTECTED] writes: > I have problems with moving cursors on joined tables. Most of the more complex plan node types don't really support moving backwards. I suppose it'd be an idea to error out rather than silently doing the wrong thing. Fixing the code to actually do the right thing is way down the priority list, unfortunately. As a workaround you could modify the query to force a sort step after the join, eg with an ORDER BY that doesn't match the join condition. A sort node can cope with backwards scan of its output ... 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])
[BUGS] referential integrity error
/* If a table contains two or more foreign keys referencing the same table and they reference the same record in the other table, the record's primary key cannot be changed even if the keys have the "ON UPDATE CASCADE" clause (see example below). It will say that "referential integrity error - key referenced from `table' not found in `table'". */ CREATE TABLE "Bug1" ( "ID" INT4, "Description" TEXT NOT NULL, PRIMARY KEY("ID") ); CREATE TABLE "Bug2" ( "ID" INT4, "Source" INT4 NOT NULL REFERENCES "Bug1" ON UPDATE CASCADE ON DELETE NO ACTION, "Destination" INT4 NOT NULL REFERENCES "Bug1" ON UPDATE CASCADE ON DELETE NO ACTION, "Description" TEXT NOT NULL, PRIMARY KEY ("ID") ); INSERT INTO "Bug1" VALUES (1,'Place'); INSERT INTO "Bug2" VALUES (10,1,1,'Move from Place to Place'); UPDATE "Bug1" SET "ID"=2 WHERE "ID"=1; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] Referential integrity problem postgresql 7.2 ?
Should the following piece of code cause an: ERROR: referential integrity violation - key referenced from b not found in a Or should it work because the check is deferred and in the end no violations are present? create table a(ia int primary key); create table b(ia int references a initially deferred); insert into a values (7); begin; insert into b values (-7); update b set ia=-ia where ia<0; commit; drop table a; drop table b; -- Sincerely, [EMAIL PROTECTED] Stephen R. van den Berg (AKA BuGless). "-- hit any user to continue" ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] PostGres Doubt
Hi , I am extremely new to PostGreSql. If any one can please answer this question of mine. I want to insert/update records into the postgres database through C or perl code. The only condition is that it should be efficient. Can anybody tell me the difference between ecpg and libpq and which one should I work on for solving my problem. Thanks in advance. Vikas. _ Click below to visit monsterindia.com and review jobs in India or Abroad http://monsterindia.rediff.com/jobs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] Bug #689: Bug at moving cursor on joined tables
Robert Grabowski ([EMAIL PROTECTED]) reports a bug with a severity of 2 The lower the number the more severe it is. Short Description Bug at moving cursor on joined tables Long Description I have problems with moving cursors on joined tables. On Simple tables all is ok... See example code... Sample Code test=# create table t1 (id integer, name text); CREATE test=# create table t2 (id integer, name text); CREATE test=# insert into t1 values (1, 'a'); INSERT 3670086 1 test=# insert into t1 values (2, 'b'); INSERT 3670087 1 test=# insert into t1 values (3, 'd'); INSERT 3670088 1 test=# insert into t2 values (1, 'x'); INSERT 3670089 1 test=# insert into t2 values (2, 'y'); INSERT 3670090 1 test=# insert into t2 values (3, 'z'); INSERT 3670091 1 test=# begin; BEGIN test=# declare c cursor for select t1.id, t1.name, t2.name from t1 join t2 using (id); DECLARE test=# fetch all from c; id | name | name +--+-- 1 | a| x 2 | b| y 3 | d| z (3 rows) test=# move backward all in c; MOVE 0 test=# fetch all from c; id | name | name +--+-- (0 rows) test=# rollback; ROLLBACK test=# begin; BEGIN test=# declare c cursor for select t1.id, t1.name from t1; DECLARE test=# fetch all from c; id | name +-- 1 | a 2 | b 3 | d (3 rows) test=# move backward all in c; MOVE 3 test=# fetch all from c; id | name +-- 1 | a 2 | b 3 | d (3 rows) test=# No file was uploaded with this report ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Bug #640: ECPG: inserting float numbers
Bruce, after checking the libecpg source i'm fairly sure the problem is due to the malloc buffer that the float is being sprintf'd into being too small... It is always allocated 20 bytes but with a %.14g printf specifier -6e-06 results in 20 characters: -6.0e-06 and the NULL goes... bang! I guess the '-' wasn't factored in and 21 bytes would be enough. Patch against current CVS (but untested): Index: src/interfaces/ecpg/lib/execute.c === RCS file: /projects/cvsroot/pgsql/src/interfaces/ecpg/lib/execute.c,v retrieving revision 1.36 diff -r1.36 execute.c 703c703 < if (!(mallocedval = ECPGalloc(var->arrsize * 20, stmt->lineno))) --- > if (!(mallocedval = ECPGalloc(var->arrsize * 21, >stmt->lineno))) 723c723 < if (!(mallocedval = ECPGalloc(var->arrsize * 20, stmt->lineno))) --- > if (!(mallocedval = ECPGalloc(var->arrsize * 21, >stmt->lineno))) Lee. Bruce Momjian writes: > > OK, I have reproduced the problem on my machine: > > #$ ./a.out floattest > col1: -0.06 > *!*!* Error -220: No such connection NULL in line 21. > > Wow, how did that "A" get into the query string: > > insert into tab1 ( col1 ) values ( -6.002122251e-06A ) > > Quite strange. Michael, any ideas? > > Lee Kindness wrote: > Content-Description: message body text > > > Bruce, the attached source reproduces this on 7.2, I don't have a > > later version at hand to test if it's been fixed: > > > > createdb floattest > > echo "CREATE TABLE tab1(col1 FLOAT);" | psql floattest > > ecpg insert-float.pgc > > gcc insert-float.c -lecpg -lpq > > ./a.out floattest > > > > results in: > > > > col1: -0.06 > > *!*!* Error -400: 'ERROR: parser: parse error at or near "a"' in line 21. > > > > and in epcgdebug: > > > > [29189]: ECPGexecute line 21: QUERY: insert into tab1 ( col1 ) values ( >-6.002122251e-06A ) on connection floattest > > [29189]: ECPGexecute line 21: Error: ERROR: parser: parse error at or near "a" > > [29189]: raising sqlcode -400 in line 21, ''ERROR: parser: parse error at or >near "a"' in line 21.'. > > > > Regards, Lee Kindness. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Bug #640: ECPG: inserting float numbers
OK, I have reproduced the problem on my machine: #$ ./a.out floattest col1: -0.06 *!*!* Error -220: No such connection NULL in line 21. Wow, how did that "A" get into the query string: insert into tab1 ( col1 ) values ( -6.002122251e-06A ) Quite strange. Michael, any ideas? --- Lee Kindness wrote: Content-Description: message body text > Bruce, the attached source reproduces this on 7.2, I don't have a > later version at hand to test if it's been fixed: > > createdb floattest > echo "CREATE TABLE tab1(col1 FLOAT);" | psql floattest > ecpg insert-float.pgc > gcc insert-float.c -lecpg -lpq > ./a.out floattest > > results in: > > col1: -0.06 > *!*!* Error -400: 'ERROR: parser: parse error at or near "a"' in line 21. > > and in epcgdebug: > > [29189]: ECPGexecute line 21: QUERY: insert into tab1 ( col1 ) values ( >-6.002122251e-06A ) on connection floattest > [29189]: ECPGexecute line 21: Error: ERROR: parser: parse error at or near "a" > [29189]: raising sqlcode -400 in line 21, ''ERROR: parser: parse error at or near >"a"' in line 21.'. > > Regards, Lee Kindness. > > Bruce Momjian writes: > > Has this been addressed? Can you supply a reproducable example? > > Edward Pilipczuk wrote: > > > On Monday, 22 April 2002 18:41, you wrote: > > > > Edward ([EMAIL PROTECTED]) reports a bug with a severity of 1 > > > > ECPG: inserting float numbers > > > > Inserting records with single precision real variables having small value > > > > (range 1.0e-6 or less) frequently results in errors in ECPG translations > > > > putting into resulted sql statement unexpected characters => see fragments > > > > of sample code and ECPGdebug log where after value of rate variable the > > > > unexpected character '^A' appears > > > > > > > > Sample Code > > > > [ snip ] > > #include > > EXEC SQL INCLUDE sqlca; > > int main(int argc, char **argv) > { > EXEC SQL BEGIN DECLARE SECTION; > char *db = argv[1]; > float col1; > EXEC SQL END DECLARE SECTION; > FILE *f; > > if( (f = fopen("ecpgdebug", "w" )) != NULL ) > ECPGdebug(1, f); > > EXEC SQL CONNECT TO :db; > EXEC SQL BEGIN; > > col1 = -6e-06; > printf("col1: %f\n", col1); > EXEC SQL INSERT INTO tab1(col1) VALUES (:col1); > if( sqlca.sqlcode < 0 ) > { > fprintf(stdout, "*!*!* Error %ld: %s\n", sqlca.sqlcode, >sqlca.sqlerrm.sqlerrmc); > EXEC SQL ABORT; > EXEC SQL DISCONNECT; > return( 1 ); > } > else > { > EXEC SQL COMMIT; > EXEC SQL DISCONNECT; > return( 0 ); > } > } -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Bug #640: ECPG: inserting float numbers
Bruce, the attached source reproduces this on 7.2, I don't have a later version at hand to test if it's been fixed: createdb floattest echo "CREATE TABLE tab1(col1 FLOAT);" | psql floattest ecpg insert-float.pgc gcc insert-float.c -lecpg -lpq ./a.out floattest results in: col1: -0.06 *!*!* Error -400: 'ERROR: parser: parse error at or near "a"' in line 21. and in epcgdebug: [29189]: ECPGexecute line 21: QUERY: insert into tab1 ( col1 ) values ( -6.002122251e-06A ) on connection floattest [29189]: ECPGexecute line 21: Error: ERROR: parser: parse error at or near "a" [29189]: raising sqlcode -400 in line 21, ''ERROR: parser: parse error at or near "a"' in line 21.'. Regards, Lee Kindness. Bruce Momjian writes: > Has this been addressed? Can you supply a reproducable example? > Edward Pilipczuk wrote: > > On Monday, 22 April 2002 18:41, you wrote: > > > Edward ([EMAIL PROTECTED]) reports a bug with a severity of 1 > > > ECPG: inserting float numbers > > > Inserting records with single precision real variables having small value > > > (range 1.0e-6 or less) frequently results in errors in ECPG translations > > > putting into resulted sql statement unexpected characters => see fragments > > > of sample code and ECPGdebug log where after value of rate variable the > > > unexpected character '^A' appears > > > > > > Sample Code > > > [ snip ] #include EXEC SQL INCLUDE sqlca; int main(int argc, char **argv) { EXEC SQL BEGIN DECLARE SECTION; char *db = argv[1]; float col1; EXEC SQL END DECLARE SECTION; FILE *f; if( (f = fopen("ecpgdebug", "w" )) != NULL ) ECPGdebug(1, f); EXEC SQL CONNECT TO :db; EXEC SQL BEGIN; col1 = -6e-06; printf("col1: %f\n", col1); EXEC SQL INSERT INTO tab1(col1) VALUES (:col1); if( sqlca.sqlcode < 0 ) { fprintf(stdout, "*!*!* Error %ld: %s\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc); EXEC SQL ABORT; EXEC SQL DISCONNECT; return( 1 ); } else { EXEC SQL COMMIT; EXEC SQL DISCONNECT; return( 0 ); } } ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]