[BUGS] ESQL/C TRUNCATE TABLE results in DROP TABLE
Your name : Lee Kindness
Your email address : [EMAIL PROTECTED]
System Configuration
-
Architecture (example: Intel Pentium) : Intel Pentium
Operating System (example: Linux 2.0.26 ELF) : Linux 2.2.16-22, RedHat 7.0
PostgreSQL version (example: PostgreSQL-7.1.3): PostgreSQL-7.1.3
Compiler used (example: gcc 2.95.2) : RPM
Please enter a FULL description of your problem:
'TRUNCATE TABLE' in embedded SQL results in the table being dropped!
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
--
Execute the following commands:
createdb copytest
echo 'CREATE TABLE copytest (f1 INTEGER, f2 INTEGER);' psql copytest
echo 'CREATE UNIQUE INDEX copytest_idx ON copytest USING BTREE (f1, f2);' psql
copytest
ecpg copy.pgc
gcc copy.c -I /usr/include/pgsql -lecpg -lpq
./a.out
Which will result in the following output from 'a.out':
Error -400: 'ERROR: Relation 'copytest' does not exist' in line 17.
given the following source 'copytest.pgc':
#include
#include
#include
#include
EXEC SQL INCLUDE sqlca;
int main(int argc, char **argv)
{
EXEC SQL CONNECT TO copytest;
if( sqlca.sqlcode != 0 )
return( 1 );
EXEC SQL TRUNCATE TABLE copytest;
if( sqlca.sqlcode < 0 )
return( 1 );
EXEC SQL COPY copytest FROM '/tmp/copytest';
if( sqlca.sqlcode < 0 )
{
printf("Error %ld: %s\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
return( 1 );
}
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
return( 0 );
}
and the following '/tmp/copytest' (not used):
1 1
2 2
3 3
4 4
5 5
6 6
If you add an 'EXEC SQL COMMIT' after the TRUNCATE and then do a '\d'
in psql the table is not shown - it has been dropped.
If you know how this problem might be fixed, list the solution below:
-
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[BUGS] Btree index ignored on SELECT until VACUUM ANALYZE
When adding an index to a (quite large, ~2 million rows) table PostgreSQL continues to do sequential lookups until VACUUM ANALYZE is run. Naturally performance is poor. The CREATE INDEX statement takes considerable time. Seen with 7.1.3 on Intel Linux (RedHat 7.0 & 7.1 and Solaris 2.6. In the example below the data file (8 MB) can be found at: http://services.csl.co.uk/postgresql/obs.gz Consider the session below: lkind@elsick:~% createdb obs_test CREATE DATABASE lkind@elsick:~% psql obs_test obs_test=# CREATE TABLE obs (setup_id INTEGER, time REAL, value REAL, bad_data_flag SMALLINT); CREATE obs_test=# COPY obs FROM '/user/lkind/obs'; COPY obs_test=# SELECT COUNT(*) FROM obs; count - 1966593 (1 row) obs_test=# CREATE UNIQUE INDEX obs_idx ON obs USING BTREE(setup_id, time); CREATE obs_test=# EXPLAIN SELECT * FROM obs WHERE setup_id = 300 AND time = 118; NOTICE: QUERY PLAN: Seq Scan on obs (cost=0.00..42025.90 rows=197 width=14) EXPLAIN obs_test=# VACUUM ANALYZE obs ; VACUUM obs_test=# EXPLAIN SELECT * FROM obs WHERE setup_id = 300 AND time = 118; NOTICE: QUERY PLAN: Index Scan using obs_idx on obs (cost=0.00..9401.60 rows=1 width=14) EXPLAIN obs_test=# \q ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] ECPG bug in --help
Michael, hope you've had a good Christmas & all the best for the New Year... When you run 'ecpg --help' you get the following: -t turn on autocommit of transactions amongst the other options... Shouldn't this be OFF as per the documentation? Best regards, Lee. -- Lee Kindness, Senior Software Engineer, [EMAIL PROTECTED] Concept Systems Ltd., 1 Logie Mill, Edinburgh EH7 4HG, Scotland http://www.csl.co.uk/ http://services.csl.co.uk/ +44 1315575595 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] ecpg did not precompile declare cursor
This is expected behaviour, the 'real' code gets emitted when you OPEN
the cursor, i.e. you should be doing something like:
EXEC SQL DECLARE demo_cur CURSOR FOR
SELECT field1, field2
FROM test;
EXEC SQL OPEN demo_cur;
if( sqlca.sqlcode != 0 )
{
some_error();
return;
}
while( 1 )
{
EXEC SQL FETCH demo_cur INTO :field1, :field2;
if( sqlca.sqlcode < 0 )
{
some_error();
break;
}
else if( sqlca.sqlcode != 0 ) /* or == 100... */
break;
process_row();
}
EXEC SQL CLOSE demo_cur;
Bernhard Rückerl writes:
> Hello,
>
> I have downloaded postgresql 7.2 on my machine.
> Running ecpg on my .ec-file I found that ecpg did not process
> the statements "exec sql declare xxx cursor for select.
>
> The part in my .ec-file:
> if ( firstcall )
>{
>calid1 = calid;
>EXEC SQL DECLARE CURMFDPOINT CURSOR FOR SELECT STABLE_OR_INSTABLE ,
> HIERARCHY , POINT_ID , X1 , P1 , X2 , P2 FROM MANIFOLD_POINTS WHERE
> CAL_ID = :calid1;
>raiseerror( );
>firstcall = false;
>}
>
> was transformed into
> if ( firstcall )
>{
>calid1 = calid;
>/* declare CURMFDPOINT cursor for select STABLE_OR_INSTABLE , HIERARCHY ,
>POINT_ID , X1 , P1 , X2 , P2 from MANIFOLD_POINTS where CAL_ID = ? */
> #line 224 "dbcontrol.ec"
>
>raiseerror( );
>firstcall = false;
>}
> So the declare cursor statement was just commented out. As a consequence the
> programm terminated with sqlca.sqlcode=-602 when doing the according
> fetch statement.
---(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] 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]
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
[BUGS] ECPG: CREATE TABLE ... FOREIGN KEY(col) REFERENCES table(col)
Hi, When creating a table in embedded SQL, a foreign key construct like: FOREIGN KEY(col) REFERENCES table(col) is not accepted by ecpg. Consider the following code fragment (error checking omitted): EXEC SQL CREATE TABLE colscales(id INTEGER NOT NULL, name VARCHAR(64) NOT NULL, auto INTEGER NOT NULL, PRIMARY KEY(id), UNIQUE(name)); EXEC SQL CREATE TABLE colscaledata(scale_id INTEGER NOT NULL, order_id INTEGER NOT NULL, threshold FLOAT NOT NULL, colourVARCHAR(10) NOT NULL, FOREIGN KEY(scale_id) REFERENCES colscales(id)); This results in the following output from ecpg: src/colourscales_table.pc:55: ERROR: parse error, unexpected `NO', expecting `NOT' or `DEFERRABLE' or `INITIALLY' at or near "NO" This is with ecpg 2.9.0, PostgreSQL 7.2 on Linux. The same(ish) statments create the tables without problem with psql. Obviously the workaround is to use something like: FOREIGN KEY(col) REFERENCES table but this would not work if the desired column was not the primary key... BRegards, Lee Kindness. ---(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] PostgreSQL security release 7.2.2 hasn't been announced on pgsql-announce
I received the announcement today, seems it was stuck somewhere: >From [EMAIL PROTECTED] Thu Aug 29 13:48:26 2002 Return-Path: <[EMAIL PROTECTED]> Received: from internet.csl.co.uk by euphrates.csl.co.uk (8.9.3/ConceptI 2.4) id NAA11884; Thu, 29 Aug 2002 13:48:25 +0100 (BST) Received: from postgresql.org (postgresql.org [64.49.215.8]) by internet.csl.co.uk (8.12.6/8.12.6) with ESMTP id g7TCmN9m025305 for <[EMAIL PROTECTED]>; Thu, 29 Aug 2002 13:48:24 +0100 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 9A603476776; Thu, 29 Aug 2002 08:40:20 -0400 (EDT) Received: from postgresql.org (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with SMTP id 0DE58476812; Thu, 29 Aug 2002 08:38:55 -0400 (EDT) Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 78E2B475A69; Fri, 23 Aug 2002 23:22:19 -0400 (EDT) Received: from earth.hub.org (earth.hub.org [64.49.215.11]) by postgresql.org (Postfix) with ESMTP id 0DC75475A19; Fri, 23 Aug 2002 23:22:19 -0400 (EDT) Received: from earth.hub.org (earth.hub.org [64.49.215.11]) by earth.hub.org (Postfix) with ESMTP id 7A11B2CCAB2; Sat, 24 Aug 2002 00:22:17 -0300 (ADT) Message-ID: <[EMAIL PROTECTED]> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 Precedence: bulk X-Virus-Scanned: by AMaViS new-20020517 Content-Length: 1153 From: "Marc G. Fournier" <[EMAIL PROTECTED]> Sender: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED], <[EMAIL PROTECTED]>, Vince Vielhaber <[EMAIL PROTECTED]> Subject: [ANNOUNCE] PostgreSQL 7.2.2: Security Release Date: Sat, 24 Aug 2002 00:22:17 -0300 (ADT) Lee. Tom Lane writes: > Aleksander Adamowski <[EMAIL PROTECTED]> writes: > > What's the use of an announce list if it doesn't announce new releases? > > It did, according to the archives: > > http://archives.postgresql.org/pgsql-announce/2002-08/msg4.php > > However, I see from my mail logs that I never got a copy of that. > I am subscribed (just checked majordomo), and given that you have > a similar complaint it would seem pgsql-announce is pretty lossy. > > Marc, any ideas? > > 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]) ---(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] PostgreSQL security release 7.2.2 hasn't been announced on pgsql-announce
I received the announcement today, seems it was stuck somewhere: From [EMAIL PROTECTED] Thu Aug 29 13:48:26 2002 Return-Path: <[EMAIL PROTECTED]> Received: from internet.csl.co.uk by euphrates.csl.co.uk (8.9.3/ConceptI 2.4) id NAA11884; Thu, 29 Aug 2002 13:48:25 +0100 (BST) Received: from postgresql.org (postgresql.org [64.49.215.8]) by internet.csl.co.uk (8.12.6/8.12.6) with ESMTP id g7TCmN9m025305 for <[EMAIL PROTECTED]>; Thu, 29 Aug 2002 13:48:24 +0100 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 9A603476776; Thu, 29 Aug 2002 08:40:20 -0400 (EDT) Received: from postgresql.org (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with SMTP id 0DE58476812; Thu, 29 Aug 2002 08:38:55 -0400 (EDT) Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 78E2B475A69; Fri, 23 Aug 2002 23:22:19 -0400 (EDT) Received: from earth.hub.org (earth.hub.org [64.49.215.11]) by postgresql.org (Postfix) with ESMTP id 0DC75475A19; Fri, 23 Aug 2002 23:22:19 -0400 (EDT) Received: from earth.hub.org (earth.hub.org [64.49.215.11]) by earth.hub.org (Postfix) with ESMTP id 7A11B2CCAB2; Sat, 24 Aug 2002 00:22:17 -0300 (ADT) Message-ID: <[EMAIL PROTECTED]> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 Precedence: bulk X-Virus-Scanned: by AMaViS new-20020517 Content-Length: 1153 From: "Marc G. Fournier" <[EMAIL PROTECTED]> Sender: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED], <[EMAIL PROTECTED]>, Vince Vielhaber <[EMAIL PROTECTED]> Subject: [ANNOUNCE] PostgreSQL 7.2.2: Security Release Date: Sat, 24 Aug 2002 00:22:17 -0300 (ADT) Lee. Tom Lane writes: > Aleksander Adamowski <[EMAIL PROTECTED]> writes: > > What's the use of an announce list if it doesn't announce new releases? > > It did, according to the archives: > > http://archives.postgresql.org/pgsql-announce/2002-08/msg4.php > > However, I see from my mail logs that I never got a copy of that. > I am subscribed (just checked majordomo), and given that you have > a similar complaint it would seem pgsql-announce is pretty lossy. > > Marc, any ideas? > > 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]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] postmaster will not start with stale lockfile but not report why
A pet annoyance with the Linux RPMs... They are shipped with syslog enabled and postmaster sdtout/stderr redirected to /dev/null. So unless the user specifically sets up the needed info in /etc/syslog.conf then they never hear a squeak from PostgreSQL! I'm by no means an RPM expert, but judging by the installation of other packages this hopefully can be handled automatically. A suitable /etc/logrotate.d/postgresql file could be in the RPM and In the post-install script the following rough steps would take place: 1. Check if /var/log/postgresql is in /etc/syslog.conf, if not add it: local0.* /var/log/postgresql 2. Restart syslogd: /etc/init.d/syslog restart Obviously there is the added issue of cross distribution file locations (I'm coming from a Redhat perspective here) - what does the LSB/FSH say about syslog.conf and logrotate files? Guess i need to check... Lamar, would this be easy to do for the RPMs? If you could point me in the right direction I could take a look... Thanks, Lee Kindness. Tom Lane writes: > Rudolf Potucek <[EMAIL PROTECTED]> writes: > > Oct 4 14:05:45 antimony3 postgresql: Starting postgresql service: > > failed > > > Maybe, just maybe, it would be nice if the server croaked a bit more > > vebously? > > The postmaster croaks as verbosely as it can. I'll bet lunch that your > system's startup script is redirecting the postmaster's stderr to > /dev/null (or using the -S switch which has the same effect). If so, > we are not the people to complain to ... > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] Fw: CD
Monson, This mailing list and website is for the PostgreSQL database system. It is in no-way releated to the "SQL Weekend Crash Course" book or it's publishers. Perhaps you'd be better to contact the publishers: http://www.wileyeurope.com/ and search for the book title... For general PostgreSQL help in getting started there is the [EMAIL PROTECTED] mailing list, which may be of use to you in the future. Regards, Lee Kindness. > Hi, > I bought the SQL Weekend Crash Course book with a CD. The problem was, = > I could not find the folder named "Author" which contain the book material = > in the CD given. I wrote to you earlier but so far got no response. Please = > help. > > Regards, > Monson Marukatat ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
