Thread: 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
> -----Original Message----- > From: vikas p verma [mailto:vvicky72@rediffmail.com] > Sent: Monday, June 10, 2002 1:10 PM > To: pgsql-hackers@postgresql.org > Subject: [HACKERS] 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. > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly ECPG is single threading. Hence, tools written in ECPG are a pain in the neck if you want multiple threads of execution. I recommend against using it for any purpose except porting a single threading project that already uses embedded SQL. The embedded SQL interface for PostgreSQL is a disaster. The libpq functions are reentrant. These will be useful for just about any project. If you are populating empty tables, then use the bulk copy interface. It is orders of magnitude faster. If you are going to completely replace the data in a table, drop the table, create the table, and use the bulk copy interface.
Is libpq/PQconnectdb() reentrant? I've tried repeatedly over time and it seems to incur segfaults every single time. -d Dann Corbit wrote: >The libpq functions are reentrant. These will be useful for just about >any project. > >
Are you using crypt on the connection? Unfortunately, crypt is not reentrant. > -----Original Message----- > From: David Ford [mailto:david+cert@blue-labs.org] > Sent: Monday, June 10, 2002 6:16 PM > To: Dann Corbit > Cc: vikas p verma; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] PostGres Doubt > > > Is libpq/PQconnectdb() reentrant? I've tried repeatedly over > time and > it seems to incur segfaults every single time. > > -d > > Dann Corbit wrote: > > >The libpq functions are reentrant. These will be useful for > just about > >any project. > > > > > >
On Mon, 10 Jun 2002, Dann Corbit wrote: > If you are going to completely replace the data in a table, drop the > table, create the table, and use the bulk copy interface. Actually, that's a bad habit to get into. Views disappear, as do triggers or constraints. Better to 'truncate table' or 'delete from table'. I know, I had a bear of a time with a nightly drop table;create table;copy data in script that I forgot about and built a nice new app on views. worked fine, came in the next morning, app was down...
On Mon, Jun 10, 2002 at 08:09:57PM -0000, vikas p verma wrote: > 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. Both will work and both will be efficient. ecpg internally uses libpq to do the real work but gives you the standard SQL commands without having to learn any new library API. Michael -- Michael Meskes Michael@Fam-Meskes.De Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
On Mon, Jun 10, 2002 at 02:08:22PM -0700, Dann Corbit wrote: > ECPG is single threading. Hence, tools written in ECPG are a pain in > the neck if you want multiple threads of execution. I recommend against Did he say he wants to write a multi-threaded app? > using it for any purpose except porting a single threading project that > already uses embedded SQL. The embedded SQL interface for PostgreSQL is > a disaster. Oh, that's what I call constructive critizism. I cannot remember you filing any bug reports or asking for some special features. Wouldn't that be the first step? And not calling other people's work a disaster. > The libpq functions are reentrant. These will be useful for just about > any project. Well if they are (I never checked myself) it shouldn't be too difficult to make ecpg reentrant too. > If you are going to completely replace the data in a table, drop the > table, create the table, and use the bulk copy interface. Oh great! Talking about valuable comments. Ever bothered to even ask if they are using triggers, constraints, etc. before coming with such a proposal? Michael -- Michael Meskes Michael@Fam-Meskes.De Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
Good points; not sure why I didn't pick up on this too.
I should point out that I've seen code with heavy Oracle-isms brought
into PostgreSQL using ecpg with amazingly few changes. It is a great
piece of code; any large complaints should perhaps be directed at the
SQL standards themselves...
- Thomas
I.e. "md5" in pg_hba.conf? This is rather disappointing. Here are a few references: http://lists.initd.org/pipermail/psycopg/2002-January/000673.html http://lists.initd.org/pipermail/psycopg/2002-January/000674.html http://archives.postgresql.org/pgsql-bugs/2002-03/msg00048.php And finally.. http://archives.postgresql.org/pgsql-bugs/2002-01/msg00153.php So reentrancy in libpq basically is put on hold until 7.3. David Dann Corbit wrote: >Are you using crypt on the connection? > >Unfortunately, crypt is not reentrant. > > > >>-----Original Message----- >>From: David Ford [mailto:david+cert@blue-labs.org] >>Sent: Monday, June 10, 2002 6:16 PM >>To: Dann Corbit >>Cc: vikas p verma; pgsql-hackers@postgresql.org >>Subject: Re: [HACKERS] PostGres Doubt >> >> >>Is libpq/PQconnectdb() reentrant? I've tried repeatedly over >>time and >>it seems to incur segfaults every single time. >> >>-d >> >>Dann Corbit wrote: >> >> >> >>>The libpq functions are reentrant. These will be useful for >>> >>> >>just about >> >> >>>any project. >>> >>> >>> >>> >> >>
David Ford <david+cert@blue-labs.org> writes:
> So reentrancy in libpq basically is put on hold until 7.3.
Only if you insist on using "crypt", which is deprecated anyway.
md5 is the preferred encryption method.
My feeling about the proposed patch was that crypt is now a legacy auth
method, and it's not clear that we should create platform/library
dependencies just to support making multiple connections simultaneously
under crypt auth. (Note that *using* connections concurrently is not
at issue, only whether you can execute the authentication phase of
startup concurrently.)
regards, tom lane
> -----Original Message----- > From: Michael Meskes [mailto:meskes@postgresql.org] > Sent: Wednesday, June 12, 2002 5:41 AM > To: Dann Corbit > Cc: pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] PostGres Doubt > > > On Mon, Jun 10, 2002 at 02:08:22PM -0700, Dann Corbit wrote: > > ECPG is single threading. Hence, tools written in ECPG are > a pain in > > the neck if you want multiple threads of execution. I > recommend against > > Did he say he wants to write a multi-threaded app? Or run concurrent queries queries at the same time? Or later discover the need to do so? > > using it for any purpose except porting a single threading > project that > > already uses embedded SQL. The embedded SQL interface for > PostgreSQL is > > a disaster. > > Oh, that's what I call constructive critizism. I cannot remember you > filing any bug reports or asking for some special features. Wouldn't > that be the first step? And not calling other people's work a > disaster. I posted the problems to this list long ago. I wanted to use ECPG and discovered it was a joke. Do a search through the list and you will find a half dozen complaints. > > The libpq functions are reentrant. These will be useful > for just about > > any project. > > Well if they are (I never checked myself) it shouldn't be too > difficult > to make ecpg reentrant too. Then why not do it. I looked at doing it myself, but the implementation of embedded SQL is totally nonstandard and uses global structures and fails to use the SQLCA and SQLDA structures properly. It would be a nightmare to try and fix it. > > If you are going to completely replace the data in a table, drop the > > table, create the table, and use the bulk copy interface. > > Oh great! Talking about valuable comments. Ever bothered to > even ask if > they are using triggers, constraints, etc. before coming with such a > proposal? I would assume that they would use their brain.
I should apologize for being rather harsh about embedded SQL for PostgreSQL. To be fair, it does function and it certainly isn't trivial to implement. I am sure that those who have worked on this project have invested very many hours of blood, sweat and tears making it work. I actually spent a great deal of effort trying to write some tools using the PostgreSQL version of ECPG, and found fatal flaws that threw away a couple weeks of work. I think that is why my responses were so overwhelmingly negative. Here is what I would like to see (consider a gentle suggestion): A reentrant version of ECPG that uses SQLCA and SQLDA like Oracle or Rdb or DB/2 or any of the professional database systems.
...
> I would assume that they would use their brain.
Way uncalled for. You must have some other underlying issues to get this
bad 'tude, but please note that ad hominum attacks are *never* welcome
on this or any other PostgreSQL mailing list.
Regards.
- Thomas
Dann Corbit wrote: > > -----Original Message----- > > From: Michael Meskes [mailto:meskes@postgresql.org] > > Sent: Wednesday, June 12, 2002 5:41 AM > > To: Dann Corbit > > Cc: pgsql-hackers@postgresql.org > > Subject: Re: [HACKERS] PostGres Doubt > > > > > > On Mon, Jun 10, 2002 at 02:08:22PM -0700, Dann Corbit wrote: > > > ECPG is single threading. Hence, tools written in ECPG are > > a pain in > > > the neck if you want multiple threads of execution. I > > recommend against > > > > Did he say he wants to write a multi-threaded app? > > Or run concurrent queries queries at the same time? Or later discover > the need to do so? ... > > I posted the problems to this list long ago. I wanted to use ECPG and > discovered it was a joke. Do a search through the list and you will > find a half dozen complaints. ,.. > > Then why not do it. I looked at doing it myself, but the implementation > of embedded SQL is totally nonstandard and uses global structures and > fails to use the SQLCA and SQLDA structures properly. It would be a > nightmare to try and fix it. > > > > If you are going to completely replace the data in a table, drop the > > > table, create the table, and use the bulk copy interface. > > > > Oh great! Talking about valuable comments. Ever bothered to > > even ask if > > they are using triggers, constraints, etc. before coming with such a > > proposal? > > I would assume that they would use their brain. If you think ecpg is a joke, I think you will find PostgreSQL is a joke too. I suggest you find another database. In fact, you may find all other databases to be a joke. I suggest you write your own. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Dann Corbit wrote:
> I should apologize for being rather harsh about embedded SQL for
> PostgreSQL.
>
> To be fair, it does function and it certainly isn't trivial to
> implement. I am sure that those who have worked on this project have
> invested very many hours of blood, sweat and tears making it work.
Oh, OK. Forget what I said earlier about you writing your own database. :-)
> I actually spent a great deal of effort trying to write some tools using
> the PostgreSQL version of ECPG, and found fatal flaws that threw away a
> couple weeks of work. I think that is why my responses were so
> overwhelmingly negative.
I assume this is because you wrote your code assuming a feature was in
ecpg, but it wasn't, right?
> Here is what I would like to see (consider a gentle suggestion):
>
> A reentrant version of ECPG that uses SQLCA and SQLDA like Oracle or Rdb
> or DB/2 or any of the professional database systems.
I see on the TODO list under ECPG:
o Implement SQLDA o Add SQLSTATE
Are these related to your problem? I see SQLCA in the ecpg code
already. Is it implemented incorrectly? If so, I could use items to
add to the TODO list.
You are actually the first person to complain about this, as far as I
can remember.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> Sent: Wednesday, June 12, 2002 2:42 PM
> To: Dann Corbit
> Cc: Michael Meskes; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] PostGres Doubt
>
>
> Dann Corbit wrote:
> > I should apologize for being rather harsh about embedded SQL for
> > PostgreSQL.
> >
> > To be fair, it does function and it certainly isn't trivial to
> > implement. I am sure that those who have worked on this
> project have
> > invested very many hours of blood, sweat and tears making it work.
>
> Oh, OK. Forget what I said earlier about you writing your
> own database. :-)
>
> > I actually spent a great deal of effort trying to write
> some tools using
> > the PostgreSQL version of ECPG, and found fatal flaws that
> threw away a
> > couple weeks of work. I think that is why my responses were so
> > overwhelmingly negative.
>
> I assume this is because you wrote your code assuming a feature was in
> ecpg, but it wasn't, right?
I have written lots of programs that use embedded SQL. I have (for
instance) several ODBC drivers that use embedded SQL and C++ as part of
an ODBC driver system. I merrily coded away some stuff to do the same
thing in PostgreSQL. After all, I had already done it for several other
systems and they all worked just about the same and the effort was
minimal to change from one system to another.
So now, I started getting down to the details. One global structure...
I started a major rewrite to repair it. Then (to my abject horror) I
discovered there is no SQLCA at all. Project abandoned (actually, just
switched to libpq and everything was OK).
Yes, you are right -- I should have checked a lot more carefully before
I dove in. I would have avoided getting my bun in a knott.
> > Here is what I would like to see (consider a gentle suggestion):
> >
> > A reentrant version of ECPG that uses SQLCA and SQLDA like
> Oracle or Rdb
> > or DB/2 or any of the professional database systems.
>
> I see on the TODO list under ECPG:
>
> o Implement SQLDA
> o Add SQLSTATE
>
> Are these related to your problem? I see SQLCA in the ecpg code
> already. Is it implemented incorrectly? If so, I could use items to
> add to the TODO list.
>
> You are actually the first person to complain about this, as far as I
> can remember.
I doubt if many people are using it then. There is a NIST SQL suite
which should be run against it. Have you heard of it? It is a
standardization for embedded SQL [and other facets of the SQL langauge].
I think it would be very nice if the PostgreSQL team should try to
incorporte the whole thing as part of their validation suite. The
project the uses embedded sql is in the folder /pc under the nist main
folder. Here is an example from that project that use sqlca:
/* EMBEDDED C (file "XOP710.PC") */
/* Copyright 1994, 1995 X/Open Company Limited */
/* All rights reserved. */
/* */
/* DISCLAIMER: */
/* This program was reviewed by employees of NIST for */
/* conformance to the SQL standards. */
/* NIST assumes no responsibility for any party's use of */
/* this program. */
/* X/Open and the 'X' symbol are registered trademarks of X/Open
Company */
/* Limited in the UK and other countries.
*/
/*****************************************************************/
/* */
/* COMMENT SECTION */
/* */
/* DATE 1994/05/13 EMBEDDED C LANGUAGE */
/* X/Open SQL VALIDATION TEST SUITE V6.0 */
/* */
/* xop710.pc */
/* WRITTEN BY: Colin O'Driscoll */
/* */
/* Acceptance of correctly placed SQLCA */
/* */
/* REFERENCES */
/* X/Open CAE SQL Specification. */
/* Section 8.1.1 */
/* */
/* <embedded SQL C program> */
/* */
/* DATE PROGRAM LAST CHANGED 02/11/94 */
/* */
/*****************************************************************/
#include <stdio.h>
#include <time.h>
#include <string.h>
#include <stdlib.h>
EXEC SQL BEGIN DECLARE SECTION;
/* this line may be needed for some preprocessors */
long SQLCODE;
char SQLSTATE[6];
char uid[19];
char uidx[19];
EXEC SQL END DECLARE SECTION;
extern int AUTHID();
/* INCLUDE SQLCA placed correctly */
EXEC SQL INCLUDE sqlca;
/* variables for NOSUBCLASS() */
long norm1;
long norm2;
char ALPNUM[37];
char NORMSQ[6];
int errcnt;
/* date_time declaration */
time_t cal; long errflg;
CHCKOK ()
{
SQLSTATE[5] = '\0';
printf ("SQLSTATE should be 00000; its value is %s\n", SQLSTATE);
NOSUBCLASS();
if (strncmp (NORMSQ, "00000", 5) == 0 &&
strncmp (NORMSQ, SQLSTATE, 5) != 0)
printf ("Valid implementation defined SQLSTATE accepted.\n");
}
main()
{
strcpy(uid,"XOPEN1"); AUTHID(uid);
strcpy(uidx,"not logged in, not");
EXEC SQL SELECT USER INTO :uidx FROM XOPEN1.ECCO;
if (strncmp(uid,uidx,6) != 0) { printf("ERROR: User %s expected. User %s connected\n",uid,uidx); exit(99); }
errcnt = 0;
errflg = 0;
printf("X/OPEN Extensions SQL Test Suite, V6.0, Embedded C,
xop710.pc\n");
printf("59-byte ID\n");
printf("TEd Version #\n");
/* date_time print */
time (&cal);
printf ("\n Time Run: %s\n", ctime (&cal));
strcpy(ALPNUM, "01234ABCDEFGH56789IJKLMNOPQRSTUVWXYZ");
/******************** BEGIN TEST0710 ********************/
printf("\n TEST0710 \n"); printf(" X/O,Acceptance of correctly placed SQLCA\n");
printf(" X/OPEN SQL CAE Spec Section 8.1.1\n"); printf(" - - - - - - - - - - - - - - - - - - -\n\n");
printf("### INSERT INTO WARNING VALUES('DDDDDD',5);\n");
printf("\n\n=================================================\n");
EXEC SQL DELETE FROM WARNING;
/* initialise variables */ strcpy(SQLSTATE,"x"); sqlca.sqlcode = 5;
EXEC SQL INSERT INTO WARNING VALUES('DDDDDD',5);
printf("sqlca.sqlcode should be 0 \n"); printf("sqlca.sqlcode is %ld\n", sqlca.sqlcode);
CHCKOK(); if ((sqlca.sqlcode != 0) && (strncmp(NORMSQ,"00000",5) != 0)) { printf ("*** Problem
foundin TEST STEP NUMBER 1 *** \n"); errflg = errflg + 1; }
EXEC SQL ROLLBACK WORK; printf("\n\n=================================================\n");
if (errflg == 0) {
EXEC SQL INSERT INTO XOPEN1.TESTREPORT
VALUES('0710','pass','PC'); printf("\n\n xop710.pc *** pass *** "); }
else { EXEC SQL INSERT INTO XOPEN1.TESTREPORT
VALUES('0710','fail','PC'); errcnt = errcnt + 1; printf("\n\n xop710.pc *** fail ***
"); }
printf("\n\n=================================================\n"); printf("\n\n\n\n");
EXEC SQL COMMIT WORK;
/******************** END TEST0710 ********************/
exit(errcnt);
}
NOSUBCLASS()
{
/* This routine replaces valid implementation deifined */
/* subclasses with 000. This replacement equates valid */
/* implementation-defined subclasses with the 000 value */
/* expected by the test case; otherwise the test will */
/* fail. After calling NOSUBCLASS, NORMSQ will be tested */
/* SQLSTATE will be printed */
strcpy (NORMSQ, SQLSTATE);
norm1 = 2;
/* subclass begins in position 3 of char array NORMSQ */
for (norm2 = 13; norm2 < 37; norm2++)
/* valid subclasses begin with 5/9, I-Z, end of ALPNUM table */ { if (NORMSQ[norm1] == ALPNUM[norm2])
NORMSQ[norm1]= '0'; }
if (strncmp (NORMSQ, SQLSTATE, 5) == 0) goto P213;
/* Quit if NORMSQ is unchanged. Subclass is not impl.def */
/* Changed NORMSQ means implementation-defined subclass, */
/* so proceed to zero it out, if valid (0-9, A-Z) */
norm1 = 3;
/* examining position 4 of char array NORMSQ */
for (norm2 = 0; norm2 < 37; norm2++)
/* valid characters are 0-9 A-Z */ { if (NORMSQ[norm1] == ALPNUM[norm2]) NORMSQ[norm1]
='0'; }
norm1 = 4;
/* examining position 5 of char array NORMSQ */
for (norm2 = 0; norm2 < 37; norm2++)
/* valid characters are 0-9 A-z */ { if (NORMSQ[norm1] == ALPNUM[norm2]) NORMSQ[norm1] = '0'; }
/* implementation-defined subclasses are allowed for warnings */
/* (class = 01). These equate to successful completion */
/* SQLSTATE values of 00000. */
/* reference SQL-92. 4.28 SQL-transactions, paragraph 2 */
if (NORMSQ[0] == '0' && NORMSQ[1] == '1') NORMSQ[1] = '0';
P213: return;
}
Dann Corbit > > I assume this is because you wrote your code assuming a feature was in > > ecpg, but it wasn't, right? > > I have written lots of programs that use embedded SQL. I have (for > instance) several ODBC drivers that use embedded SQL and C++ as part of > an ODBC driver system. I merrily coded away some stuff to do the same > thing in PostgreSQL. After all, I had already done it for several other > systems and they all worked just about the same and the effort was > minimal to change from one system to another. > > So now, I started getting down to the details. One global structure... > I started a major rewrite to repair it. Then (to my abject horror) I > discovered there is no SQLCA at all. Project abandoned (actually, just > switched to libpq and everything was OK). I see SQLCA mentioned in the ecpg code. What am I not understanding? > > > Here is what I would like to see (consider a gentle suggestion): > > > > > > A reentrant version of ECPG that uses SQLCA and SQLDA like > > Oracle or Rdb > > > or DB/2 or any of the professional database systems. > > > > I see on the TODO list under ECPG: > > > > o Implement SQLDA > > o Add SQLSTATE > > > > Are these related to your problem? I see SQLCA in the ecpg code > > already. Is it implemented incorrectly? If so, I could use items to > > add to the TODO list. > > > > You are actually the first person to complain about this, as far as I > > can remember. > > I doubt if many people are using it then. There is a NIST SQL suite > which should be run against it. Have you heard of it? It is a > standardization for embedded SQL [and other facets of the SQL langauge]. > I think it would be very nice if the PostgreSQL team should try to > incorporte the whole thing as part of their validation suite. The > project the uses embedded sql is in the folder /pc under the nist main > folder. Here is an example from that project that use sqlca: Oh, that seems easy. I know Michael will know the answer. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> -----Original Message----- > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > Sent: Wednesday, June 12, 2002 3:20 PM > To: Dann Corbit > Cc: Michael Meskes; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] PostGres Doubt > > > Dann Corbit > > > I assume this is because you wrote your code assuming a > feature was in > > > ecpg, but it wasn't, right? > > > > I have written lots of programs that use embedded SQL. I have (for > > instance) several ODBC drivers that use embedded SQL and > C++ as part of > > an ODBC driver system. I merrily coded away some stuff to > do the same > > thing in PostgreSQL. After all, I had already done it for > several other > > systems and they all worked just about the same and the effort was > > minimal to change from one system to another. > > > > So now, I started getting down to the details. One global > structure... > > I started a major rewrite to repair it. Then (to my abject > horror) I > > discovered there is no SQLCA at all. Project abandoned > (actually, just > > switched to libpq and everything was OK). > > > I see SQLCA mentioned in the ecpg code. What am I not understanding? I meant to say no SQLDA (the SQLCA only has the problem of scope). > > > > Here is what I would like to see (consider a gentle suggestion): > > > > > > > > A reentrant version of ECPG that uses SQLCA and SQLDA like > > > Oracle or Rdb > > > > or DB/2 or any of the professional database systems. > > > > > > I see on the TODO list under ECPG: > > > > > > o Implement SQLDA > > > o Add SQLSTATE > > > > > > Are these related to your problem? I see SQLCA in the ecpg code > > > already. Is it implemented incorrectly? If so, I could > use items to > > > add to the TODO list. Those are precisely the missing items (along with the implementation of SQLCA -- it should not be a global object, but rather be declared and a new instance gets created). > > > You are actually the first person to complain about this, > as far as I > > > can remember. > > > > I doubt if many people are using it then. There is a NIST SQL suite > > which should be run against it. Have you heard of it? It is a > > standardization for embedded SQL [and other facets of the > SQL langauge]. > > I think it would be very nice if the PostgreSQL team should try to > > incorporte the whole thing as part of their validation suite. The > > project the uses embedded sql is in the folder /pc under > the nist main > > folder. Here is an example from that project that use sqlca: > > Oh, that seems easy. I know Michael will know the answer. Embedded SQL is subject to standard "X/Open DR": http://www.opengroup.org/sib.htm If it can pass all of the tests in the NIST validation suite, then I think that would be a great start. It is also an excellent test for all the other facets of SQL -- it tests SQL/CLI (ODBC), transact SQL, etc. Many government contracts cannot be fulfilled by products which have not been certified to pass this suite. http://www.opengroup.org/public/prods/drm4.htm At least, that used to be the case. If need be, I can supply a copy of the test suite (I can't seem to find the download link any more). Here are some other implementations: Xdb: http://www.va.pubnix.com/man/xdb/sqlref/SQLDAStructureForC_516.html Progress: http://www.progress.com/support/downloads/v91c_release_notes/esql_92.pdf Sybase: http://manuals.sybase.com/onlinebooks/group-or/org0400e/osrsp32/@Generic __BookTextView/7062 DB/2: http://publib.boulder.ibm.com/html/as400/v4r5/ic2924/index.htm?info/db2/ rbafymst222.htm Microsoft SQL*Server http://msdn.microsoft.com/library/default.asp?url=/library/en-us/esqlfor c/ec_6_erf_03_8ag5.asp Informix: http://www.informix.com/answers/english/docs/dbdk/infoshelf/esqlc/15.fm1 .html SQL/Anywhere: http://bonsai.ucdmc.ucdavis.edu/SQLHelp/00000268.htm Adabas (This one for Adabas is very nice, it has a formal grammar!): http://www.softwareag.com/adabasd/documentation/docuen/html/prceng9.htm Oracle: http://download-west.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a8986 1/pc_15ody.htm#4581 General case: http://www.fh-sbg.ac.at/~ulamec/sql/sqlda.htm An overview: http://www.cs.purdue.edu/homes/mcclure/cs448/info/oraproc.ppt
Dann Corbit wrote: > > > So now, I started getting down to the details. One global > > structure... > > > I started a major rewrite to repair it. Then (to my abject > > horror) I > > > discovered there is no SQLCA at all. Project abandoned > > (actually, just > > > switched to libpq and everything was OK). > > > > > > I see SQLCA mentioned in the ecpg code. What am I not understanding? > > I meant to say no SQLDA (the SQLCA only has the problem of scope). I have update the TODO with: o Allow multi-threaded use of SQLCA -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Wed, 2002-06-12 at 19:38, Tom Lane wrote: > David Ford <david+cert@blue-labs.org> writes: > > So reentrancy in libpq basically is put on hold until 7.3. > > Only if you insist on using "crypt", which is deprecated anyway. > md5 is the preferred encryption method. > > My feeling about the proposed patch was that crypt is now a legacy auth > method, and it's not clear that we should create platform/library > dependencies just to support making multiple connections simultaneously > under crypt auth. (Note that *using* connections concurrently is not > at issue, only whether you can execute the authentication phase of > startup concurrently.) can't this be solved by simple locking ? I know that postgres team can do locking properly ;) -------------- Hannu
On Wed, Jun 12, 2002 at 11:00:26AM -0700, Dann Corbit wrote: > Or run concurrent queries queries at the same time? Or later discover > the need to do so? I didn't say multi-threading is bad. I just don't think your answer helped him much. > I posted the problems to this list long ago. I wanted to use ECPG and > discovered it was a joke. Do a search through the list and you will > find a half dozen complaints. If you use this kind of language I wonder if anyone ever reacted on any complaint you send. > Then why not do it. I looked at doing it myself, but the implementation Gotta like that attidude. Did you read aynthing about us not wanting to make ecpg multi-threaded? > of embedded SQL is totally nonstandard and uses global structures and What's that about? Our parser is nonstandard? Please if you expect any more answers, how about adding some facts and not just talking badly about people. > > Oh great! Talking about valuable comments. Ever bothered to > > even ask if > > they are using triggers, constraints, etc. before coming with such a > > proposal? > > I would assume that they would use their brain. Why? You don't use it either. I'm sorry, but I cannot stand this kind of behaviour. Michael -- Michael Meskes Michael@Fam-Meskes.De Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
On Wed, Jun 12, 2002 at 11:46:47AM -0700, Dann Corbit wrote: > I should apologize for being rather harsh about embedded SQL for > PostgreSQL. Also about being harsh about the people? Okay, apologies accepted. > I actually spent a great deal of effort trying to write some tools using > the PostgreSQL version of ECPG, and found fatal flaws that threw away a Which ones? If it's just SQLDA, this is pretty well documented. Yes, the feature is missing, but we all have only limited time for postgresql work. > A reentrant version of ECPG that uses SQLCA and SQLDA like Oracle or Rdb > or DB/2 or any of the professional database systems. The last time I used Oracle it used SQLCA in a very similar way as ECPG does. Michael -- Michael Meskes Michael@Fam-Meskes.De Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
On Wed, Jun 12, 2002 at 05:42:24PM -0400, Bruce Momjian wrote: > You are actually the first person to complain about this, as far as I > can remember. Yup. I cannot remember any other person either. And since nobody complained, nobody worked on this. :-) Michael -- Michael Meskes Michael@Fam-Meskes.De Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
On Wed, Jun 12, 2002 at 06:19:57PM -0400, Bruce Momjian wrote: > > I doubt if many people are using it then. There is a NIST SQL suite > > which should be run against it. Have you heard of it? It is a > > standardization for embedded SQL [and other facets of the SQL langauge]. > > I think it would be very nice if the PostgreSQL team should try to > > incorporte the whole thing as part of their validation suite. The > > project the uses embedded sql is in the folder /pc under the nist main > > folder. Here is an example from that project that use sqlca: > > Oh, that seems easy. I know Michael will know the answer. Actually I didn't know that test suite. But I will surely look at it. Michael -- Michael Meskes Michael@Fam-Meskes.De Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
> -----Original Message----- > From: Michael Meskes [mailto:meskes@postgresql.org] > Sent: Thursday, June 13, 2002 3:06 AM > To: pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] PostGres Doubt > > > On Wed, Jun 12, 2002 at 11:46:47AM -0700, Dann Corbit wrote: > > I should apologize for being rather harsh about embedded SQL for > > PostgreSQL. > > Also about being harsh about the people? Okay, apologies accepted. > > > I actually spent a great deal of effort trying to write > some tools using > > the PostgreSQL version of ECPG, and found fatal flaws that > threw away a > > Which ones? If it's just SQLDA, this is pretty well > documented. Yes, the > feature is missing, but we all have only limited time for postgresql > work. Allow me to apologize again. I have clearly gotten off on the wrong foot here. In 6 months of 8 hour days, I would not be able to create a tool with the functionality that you have provided. It is an amazing piece of work. The point I was (badly) trying to make is that because of some of the limitations of PostgreSQL's ECPG it is impossible for me to use it. Now, *all* of the applications I work with are multithreading so my situation may be very different from that of some others. > > A reentrant version of ECPG that uses SQLCA and SQLDA like > Oracle or Rdb > > or DB/2 or any of the professional database systems. > > The last time I used Oracle it used SQLCA in a very similar > way as ECPG > does. You are right about Oracle. They use global variables in embedded SQL. (I did not write our company's Oracle driver.) It remains true for all the others that they are multithread capable. It is far better to not make the SQLCA and SQLDA structures global. Since Oracle's model and that of PostgreSQL are very similar (for example in concurrency), it is unsurprising that it might be chosen as a model for implementation of embedded SQL. Let me: 1. Wipe the egg off my face 2. Personally apologize to the entire list and especially to the originators of PostgreSQL's ecpg 3. Restate my opinion in a better way: "PostgreSQL's implementation of embedded SQL is very good. The grammar is complete, it is open source, and highly functional. The licensing is a dream -- useful for any sort of endeavor. There are a couple minor issues that would enhance the functionality of ecpg even more. If the SQLCA were made a local variable to the query, it would be possible to have multiple threads of execution. If PostgreSQL's ecpg were enhanced to have SQLDA structures as specified by "X/Open DR" it would enhance the functionality even further. If such features were added, it would be possible to use ecpg in multithreaded applications, in web servers, in ODBC drivers. In fact, it would become the method of choice for almost any sort of application." I am reminded of Benjamin Franklin, who once said: "You can catch more flies with a teaspoon of sugar than with a gallon of vinegar."
I'm using md5 in pg_hba.conf. That is the method, no? I'm writing a milter application which instantiates a private resource for each thread upon thread startup. I have priv->conn which I establish as priv->conn=PQconnectdb(connstr), connstr is const char *connstr="host=10.0.0.5 dbname=bmilter user=username password=password"; It segfaults depending on it's mood but it tends to happen about 50-70% of the time. I switched to PQsetdbLogin() which has worked perfectly. I don't really want to use that however, I would muchprefer using my connstr. Am I missing something? Thanks, David Tom Lane wrote: >David Ford <david+cert@blue-labs.org> writes: > > >>So reentrancy in libpq basically is put on hold until 7.3. >> >> > >Only if you insist on using "crypt", which is deprecated anyway. >md5 is the preferred encryption method. > >My feeling about the proposed patch was that crypt is now a legacy auth >method, and it's not clear that we should create platform/library >dependencies just to support making multiple connections simultaneously >under crypt auth. (Note that *using* connections concurrently is not >at issue, only whether you can execute the authentication phase of >startup concurrently.) > > regards, tom lane > >
David Ford wrote: > I'm using md5 in pg_hba.conf. That is the method, no? > > I'm writing a milter application which instantiates a private resource > for each thread upon thread startup. I have priv->conn which I > establish as priv->conn=PQconnectdb(connstr), connstr is const char > *connstr="host=10.0.0.5 dbname=bmilter user=username password=password"; > > It segfaults depending on it's mood but it tends to happen about 50-70% > of the time. I switched to PQsetdbLogin() which has worked perfectly. > I don't really want to use that however, I would much prefer using my > connstr. Wow, I am confused. md5 should be fine. Certainly sounds like there is a thread problem with PQconnectdb(). Are you using 7.2.X? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
David Ford <david+cert@blue-labs.org> writes:
> I'm using md5 in pg_hba.conf. That is the method, no?
> I'm writing a milter application which instantiates a private resource
> for each thread upon thread startup. I have priv->conn which I
> establish as priv->conn=PQconnectdb(connstr), connstr is const char
> *connstr="host=10.0.0.5 dbname=bmilter user=username password=password";
> It segfaults depending on it's mood but it tends to happen about 50-70%
> of the time.
Could you dig out ye olde gdb and figure out *why* it's segfaulting?
At the very least, give us a stack backtrace from a debug-enabled build.
regards, tom lane
pg_auth=# select version(); version ------------------------------------------------------------PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 3.0.2 Which btw has a curious grant/revoke bug. create foo; grant select on foo to bar; results in all rights being granted. You must revoke and grant again in order to get the correct rights set. If this rights bug has been fixed, I'll upgrade, but I don't consider it a big problem since I am well aware of the bug. David Bruce Momjian wrote: >David Ford wrote: > > >>I'm using md5 in pg_hba.conf. That is the method, no? >> >>I'm writing a milter application which instantiates a private resource >>for each thread upon thread startup. I have priv->conn which I >>establish as priv->conn=PQconnectdb(connstr), connstr is const char >>*connstr="host=10.0.0.5 dbname=bmilter user=username password=password"; >> >>It segfaults depending on it's mood but it tends to happen about 50-70% >>of the time. I switched to PQsetdbLogin() which has worked perfectly. >> I don't really want to use that however, I would much prefer using my >>connstr. >> >> > >Wow, I am confused. md5 should be fine. Certainly sounds like there is >a thread problem with PQconnectdb(). Are you using 7.2.X? > > >
David Ford <david+cert@blue-labs.org> writes:
> Which btw has a curious grant/revoke bug. create foo; grant select on
> foo to bar; results in all rights being granted. You must revoke and
> grant again in order to get the correct rights set.
I see no bug.
test72=# select version(); version
---------------------------------------------------------------PostgreSQL 7.2.1 on hppa-hp-hpux10.20, compiled by GCC
2.95.3
(1 row)
test72=# create user bar;
CREATE USER
test72=# create table foo (f1 int);
CREATE
test72=# grant select on foo to bar;
GRANT
test72=# \z foo
Access privileges for database "test72"Table | Access privileges
-------+----------------------------foo | {=,postgres=arwdRxt,bar=r}
(1 row)
test72=#
regards, tom lane
On Wed, Jun 12, 2002 at 03:01:31PM -0700, Dann Corbit wrote: > project the uses embedded sql is in the folder /pc under the nist main > folder. Here is an example from that project that use sqlca: Of course this file alone won't run very well, but I added enough stuff and created a database to get it running and here's my result: 1) SQLSTATE does not work, which of course is not surprising. 2) It triggered one bug in parsing octal number in single quotes. The patch was just committed. 3) I had to remove the SQLCODE definition as SQLCODE at the moment is a #define. Maybe we should change that. 4) The SQLCA test runs through just fine. Michael -- Michael Meskes Michael@Fam-Meskes.De Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
My apologies, I was too brief in my example:
heakin=> create table interviewers ( interviewer varchar );
CREATE
heakin=> insert into interviewers values ('Ryan');
INSERT 932846 1
heakin=> select * from interviewers ;interviewer
-------------Ryan
(1 row)
heakin=> \z
Access privileges for database "heakin" Table | Access privileges
-------------------+-------------------clients | {=,heakin=arwd}completed_surveys |
{=,heakin=arwd}interviewers |respondents | {=,heakin=arwd}users | {=,heakin=ar}
(5 rows)
heakin=> grant select,insert,update on interviewers to heakin;
GRANT
heakin=> \z
Access privileges for database "heakin" Table | Access privileges
-------------------+--------------------clients | {=,heakin=arwd}completed_surveys |
{=,heakin=arwd}interviewers | {=,heakin=arwdRxt}respondents | {=,heakin=arwd}users |
{=,heakin=ar}
(5 rows)
heakin=> revoke all on interviewers from heakin;
REVOKE
heakin=> \z
Access privileges for database "heakin" Table | Access privileges
-------------------+-------------------clients | {=,heakin=arwd}completed_surveys |
{=,heakin=arwd}interviewers | {=}respondents | {=,heakin=arwd}users | {=,heakin=ar}
(5 rows)
heakin=> grant select,insert,update on interviewers to heakin;
GRANT
heakin=> \z
Access privileges for database "heakin" Table | Access privileges
-------------------+-------------------clients | {=,heakin=arwd}completed_surveys |
{=,heakin=arwd}interviewers | {=,heakin=arw}respondents | {=,heakin=arwd}users | {=,heakin=ar}
(5 rows)
David
Tom Lane wrote:
>David Ford <david+cert@blue-labs.org> writes:
>
>
>>Which btw has a curious grant/revoke bug. create foo; grant select on
>>foo to bar; results in all rights being granted. You must revoke and
>>grant again in order to get the correct rights set.
>>
>>
>
>I see no bug.
>
>test72=# select version();
> version
>---------------------------------------------------------------
> PostgreSQL 7.2.1 on hppa-hp-hpux10.20, compiled by GCC 2.95.3
>(1 row)
>
>test72=# create user bar;
>CREATE USER
>test72=# create table foo (f1 int);
>CREATE
>test72=# grant select on foo to bar;
>GRANT
>test72=# \z foo
>Access privileges for database "test72"
> Table | Access privileges
>-------+----------------------------
> foo | {=,postgres=arwdRxt,bar=r}
>(1 row)
>
>test72=#
>
> regards, tom lane
>
>
Here is the complete NIST regression test: ftp://cap.connx.com/pub/chess-engines/new-approach/nist.ZIP You have to use passive ftp to get files from my site because of the firewall. The zip is about 6 megabytes compressed.
My apologies, I was too brief in my example:
heakin=> create table interviewers ( interviewer varchar );
CREATE
heakin=> insert into interviewers values ('Ryan');
INSERT 932846 1
heakin=> select * from interviewers ; interviewer
------------- Ryan
(1 row)
heakin=> \z
Access privileges for database "heakin" Table | Access privileges
-------------------+------------------- clients | {=,heakin=arwd} completed_surveys | {=,heakin=arwd}
interviewers | respondents | {=,heakin=arwd} users | {=,heakin=ar}
(5 rows)
heakin=> grant select,insert,update on interviewers to heakin;
GRANT
heakin=> \z
Access privileges for database "heakin" Table | Access privileges
-------------------+-------------------- clients | {=,heakin=arwd} completed_surveys | {=,heakin=arwd}
interviewers | {=,heakin=arwdRxt} respondents | {=,heakin=arwd} users | {=,heakin=ar}
(5 rows)
heakin=> revoke all on interviewers from heakin;
REVOKE
heakin=> \z
Access privileges for database "heakin" Table | Access privileges
-------------------+------------------- clients | {=,heakin=arwd} completed_surveys | {=,heakin=arwd}
interviewers | {=} respondents | {=,heakin=arwd} users | {=,heakin=ar}
(5 rows)
heakin=> grant select,insert,update on interviewers to heakin;
GRANT
heakin=> \z
Access privileges for database "heakin" Table | Access privileges
-------------------+------------------- clients | {=,heakin=arwd} completed_surveys | {=,heakin=arwd}
interviewers | {=,heakin=arw} respondents | {=,heakin=arwd} users | {=,heakin=ar}
(5 rows)
David
Tom Lane wrote:
>David Ford <david+cert@blue-labs.org> writes:>>>>Which btw has a curious grant/revoke bug. create foo; grant select
on>>footo bar; results in all rights being granted. You must revoke and>>grant again in order to get the correct
rightsset.>>>>>>I see no bug.>>test72=# select version();>
version>--------------------------------------------------------------->PostgreSQL 7.2.1 on hppa-hp-hpux10.20, compiled
byGCC 2.95.3>(1 row)>>test72=# create user bar;>CREATE USER>test72=# create table foo (f1 int);>CREATE>test72=# grant
selecton foo to bar;>GRANT>test72=# \z foo>Access privileges for database "test72"> Table | Access
privileges>-------+---------------------------->foo | {=,postgres=arwdRxt,bar=r}>(1 row)>>test72=#>> regards, tom
lane>>
On Wed, Jun 12, 2002 at 04:09:52PM -0700, Dann Corbit wrote: > Embedded SQL is subject to standard "X/Open DR": > http://www.opengroup.org/sib.htm > > If it can pass all of the tests in the NIST validation suite, then I > think that would be a great start. Somehow I didn't find a link to download the NIST suite. Could you tell me where to find it? Michael -- Michael Meskes Michael@Fam-Meskes.De Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
On Mon, Jun 17, 2002 at 11:27:45AM -0700, Dann Corbit wrote: > Here is the complete NIST regression test: > ftp://cap.connx.com/pub/chess-engines/new-approach/nist.ZIP > > You have to use passive ftp to get files from my site because of the > firewall. I'm pretty sure my proxy does use passive ftp, but I cannot get through to you. Are you sure you use passive ftp for incoming connections? Anyway, it seems you have to mail it. :-) Michael -- Michael Meskes Michael@Fam-Meskes.De Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
David Ford <david@blue-labs.org> writes:
> heakin=> \z
> Access privileges for database "heakin"
> Table | Access privileges
> -------------------+-------------------
> interviewers |
> heakin=> grant select,insert,update on interviewers to heakin;
> GRANT
> heakin=> \z
> Access privileges for database "heakin"
> Table | Access privileges
> -------------------+--------------------
> interviewers | {=,heakin=arwdRxt}
I take it heakin is the owner of the table in question. As such,
he implicitly has all privileges --- the initial null privilege list
is a shorthand for what you see explicitly in the second case.
The GRANT man page in current development sources has an example about
this; see the Notes section of
http://developer.postgresql.org/docs/postgres/sql-grant.html
regards, tom lane
Gotcha. 'twas the first time I encountered it, I wasn't expecting it.
Thank you for the clarification. I hadn't paid attention to that
paragraph when I read over it.
David
Tom Lane wrote:
>David Ford <david@blue-labs.org> writes:
>
>
>>heakin=> \z
>>Access privileges for database "heakin"
>> Table | Access privileges
>>-------------------+-------------------
>> interviewers |
>>
>>
>
>
>
>>heakin=> grant select,insert,update on interviewers to heakin;
>>GRANT
>>heakin=> \z
>>Access privileges for database "heakin"
>> Table | Access privileges
>>-------------------+--------------------
>> interviewers | {=,heakin=arwdRxt}
>>
>>
>
>I take it heakin is the owner of the table in question. As such,
>he implicitly has all privileges --- the initial null privilege list
>is a shorthand for what you see explicitly in the second case.
>
>The GRANT man page in current development sources has an example about
>this; see the Notes section of
>http://developer.postgresql.org/docs/postgres/sql-grant.html
>
> regards, tom lane
>
>
On Tue, Jun 18, 2002 at 03:24:57PM +0200, Michael Meskes wrote: > On Mon, Jun 17, 2002 at 11:27:45AM -0700, Dann Corbit wrote: > > Here is the complete NIST regression test: > > ftp://cap.connx.com/pub/chess-engines/new-approach/nist.ZIP > > > > You have to use passive ftp to get files from my site because of the > > firewall. > > I'm pretty sure my proxy does use passive ftp, but I cannot get through > to you. Are you sure you use passive ftp for incoming connections? > > Anyway, it seems you have to mail it. :-) For future reference (and the archives of the list) the official download site for this code is: http://www.itl.nist.gov/div897/ctg/sql_form.htm And here's the usage statement, regarding incorporation of this work into other works (in short, it's public domain) http://www.itl.nist.gov/div897/ctg/softagre.htm Ross