PostGres Doubt

-----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:

Show quoted text

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.

Show quoted text

-----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:

Show quoted text

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, Pennsylvania 19026

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, Pennsylvania 19026

-----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 found in 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, Pennsylvania 19026

-----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, Pennsylvania 19026

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!

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

-----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 much prefer using my
connstr.

Am I missing something?

Thanks,
David

Tom Lane wrote:

Show quoted text

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, Pennsylvania 19026

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:

Show quoted text

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:

Show quoted text

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
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

Show quoted text

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:

Show quoted text

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