Announcing PgSQL - a Python DB-API 2.0 compliant interface to PostgreSQL

Billy G. Allie writes:

PgSQL v1.0 has been released. This is the first public release of PgSQL.
It is available at http://sourceforge.net/projects/pgsql.

Sounds interesting, but isn't "pgsql" an extremely unfortunate choice of
name, given that it's already used as an abbreviation for "PostgreSQL"?

--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/

I need to know how this is different than our current python interface,
PyGreSQL.

-- Start of PGP signed section.

Announce: First public release of PgSQL Version 1.0
===================================================

PgSQL v1.0 has been released. This is the first public release of PgSQL.
It is available at http://sourceforge.net/projects/pgsql.

PgSQL is a package of two (2) modules that provide a Python DB-API 2.0
compliant interface to PostgreSQL databases. The first module, libpq,
exports the PostgreSQL C API to Python. This module is written in C and
can be compiled into Python or can be dynamically loaded on demand. The
second module, PgSQL, provides the DB-API 2.0 compliant interface and
support for various PostgreSQL data types, such as INT8, NUMERIC, MONEY,
BOOL, ARRAYS, etc. This module is written in Python and works with
PostgreSQL 6.5.2 or later and Python 1.5.2 or later.

PostgreSQL is a sophisticated Object-Relational DBMS, supporting almost all
SQL constructs, including sub-selects, transactions, and user-defined types
and functions. It is the most advanced open-source database available
anywhere More information about PostgreSQL can be found at the PostgreSQL
home page at http://www.postgresql.org.

Python is an interpreted, interactive, object-oriented programming lang-
uage. It combines remarkable power with very clear syntax. It has
modules, classes, exceptions, very high level dynamic data types, and
dynamic typing. There are interfaces to many system calls and libraries,
as well as to various windowing systems (X11, Motif, Tk, Mac, MFC). New
builtin modules are easily written in C or C++. Python is also usable as
an extension language for applications that need a programmable interface.
Python is copyrighted but freely usable and distributable, even for
commercial use. More information about Python can be found on the Python
home page at http://www.python.org.

--
____ | Billy G. Allie | Domain....: Bill.Allie@mug.org
| /| | 7436 Hartwell | Compuserve: 76337,2061
|-/-|----- | Dearborn, MI 48126| MSN.......: B_G_Allie@email.msn.com
|/ |LLIE | (313) 582-1540 |

-- End of PGP section.

-- 
  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 Mon, 9 Oct 2000, Bruce Momjian wrote:

I need to know how this is different than our current python interface,
PyGreSQL.

Is this a product of pgsql.com?

Vince.

-- Start of PGP signed section.

Announce: First public release of PgSQL Version 1.0
===================================================

PgSQL v1.0 has been released. This is the first public release of PgSQL.
It is available at http://sourceforge.net/projects/pgsql.

PgSQL is a package of two (2) modules that provide a Python DB-API 2.0
compliant interface to PostgreSQL databases. The first module, libpq,
exports the PostgreSQL C API to Python. This module is written in C and
can be compiled into Python or can be dynamically loaded on demand. The
second module, PgSQL, provides the DB-API 2.0 compliant interface and
support for various PostgreSQL data types, such as INT8, NUMERIC, MONEY,
BOOL, ARRAYS, etc. This module is written in Python and works with
PostgreSQL 6.5.2 or later and Python 1.5.2 or later.

PostgreSQL is a sophisticated Object-Relational DBMS, supporting almost all
SQL constructs, including sub-selects, transactions, and user-defined types
and functions. It is the most advanced open-source database available
anywhere More information about PostgreSQL can be found at the PostgreSQL
home page at http://www.postgresql.org.

Python is an interpreted, interactive, object-oriented programming lang-
uage. It combines remarkable power with very clear syntax. It has
modules, classes, exceptions, very high level dynamic data types, and
dynamic typing. There are interfaces to many system calls and libraries,
as well as to various windowing systems (X11, Motif, Tk, Mac, MFC). New
builtin modules are easily written in C or C++. Python is also usable as
an extension language for applications that need a programmable interface.
Python is copyrighted but freely usable and distributable, even for
commercial use. More information about Python can be found on the Python
home page at http://www.python.org.

--
____ | Billy G. Allie | Domain....: Bill.Allie@mug.org
| /| | 7436 Hartwell | Compuserve: 76337,2061
|-/-|----- | Dearborn, MI 48126| MSN.......: B_G_Allie@email.msn.com
|/ |LLIE | (313) 582-1540 |

-- End of PGP section.

--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================

Nope, neigher PyGreSQL nor ""PGSQL"" are products of
Pgsql.com (aka PostgreSQL Inc)On Mon, 9 Oct 2000, Vince Vielhaber wrote:

On Mon, 9 Oct 2000, Bruce Momjian wrote:

I need to know how this is different than our current python interface,
PyGreSQL.

Is this a product of pgsql.com?

Vince.

Jeff MacDonald,

-----------------------------------------------------
PostgreSQL Inc | Hub.Org Networking Services
jeff@pgsql.com | jeff@hub.org
www.pgsql.com | www.hub.org
1-902-542-0713 | 1-902-542-3657
-----------------------------------------------------
Facsimile : 1 902 542 5386
IRC Nick : bignose

On Tue, 10 Oct 2000, Jeff MacDonald wrote:

Nope, neigher PyGreSQL nor ""PGSQL"" are products of
Pgsql.com (aka PostgreSQL Inc)On Mon, 9 Oct 2000, Vince Vielhaber wrote:

I knew PyGreSQL wasn't but PgSQL appeared a bit misleading. Thanks
for clearing that up. I guess on the website I should call it
PgSQL - NA

Vince.

On Mon, 9 Oct 2000, Bruce Momjian wrote:

I need to know how this is different than our current python interface,
PyGreSQL.

Is this a product of pgsql.com?

Vince.

Jeff MacDonald,

-----------------------------------------------------
PostgreSQL Inc | Hub.Org Networking Services
jeff@pgsql.com | jeff@hub.org
www.pgsql.com | www.hub.org
1-902-542-0713 | 1-902-542-3657
-----------------------------------------------------
Facsimile : 1 902 542 5386
IRC Nick : bignose

--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================

Bruce Momjian writes:

I need to know how this is different than our current python interface,
PyGreSQL.

PgSQL is a package of two (2) modules that provide a Python DB-API 2.0
compliant interface to PostgreSQL databases.

DB-API is the standard database interface for Python. Kind of like
DBD/DBI for Perl.

The existing one is hand-crafted, kind of like the Pg Perl module.

--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/

Actually with version 3.0 of PyGreSQL there is a
DB-API v2.0 compliant wrapper pgdb.py.

--- Peter Eisentraut <peter_e@gmx.net> wrote:

Bruce Momjian writes:

I need to know how this is different than our

current python interface,

PyGreSQL.

PgSQL is a package of two (2) modules that

provide a Python DB-API 2.0

compliant interface to PostgreSQL databases.

DB-API is the standard database interface for
Python. Kind of like
DBD/DBI for Perl.

The existing one is hand-crafted, kind of like the
Pg Perl module.

--
Peter Eisentraut peter_e@gmx.net
http://yi.org/peter-e/

__________________________________________________
Do You Yahoo!?
Get Yahoo! Mail - Free email you can access from anywhere!
http://mail.yahoo.com/

Peter Eisentraut wrote:

Billy G. Allie writes:

PgSQL v1.0 has been released. This is the first public release of PgSQL.
It is available at http://sourceforge.net/projects/pgsql.

Sounds interesting, but isn't "pgsql" an extremely unfortunate choice of
name, given that it's already used as an abbreviation for "PostgreSQL"?

--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/

PgSQL is the name of the module you import in Python to access a PostgreSQL
database from Python using the DB-API 2.0. The project name on SourceForge
is "Python Interface to PostgreSQL". Of course, it there is too much heart
burn with the modules name, I can change it.
___________________________________________________________________________
____ | Billy G. Allie | Domain....: Bill.Allie@mug.org
| /| | 7436 Hartwell | Compuserve: 76337,2061
|-/-|----- | Dearborn, MI 48126| MSN.......: B_G_Allie@email.msn.com
|/ |LLIE | (313) 582-1540 |

"Billy G. Allie" <Bill.Allie@mug.org> writes:

Peter Eisentraut wrote:

Sounds interesting, but isn't "pgsql" an extremely unfortunate choice of
name, given that it's already used as an abbreviation for "PostgreSQL"?

PgSQL is the name of the module you import in Python to access a PostgreSQL
database from Python using the DB-API 2.0. The project name on SourceForge
is "Python Interface to PostgreSQL". Of course, it there is too much heart
burn with the modules name, I can change it.

FWIW, my initial reaction was the same as Peter's: that name is certain
to cause confusion. I don't want to try to force you to change it, but
I think it's not a good choice.

Don't have a better alternative to offer offhand, however.

regards, tom lane

Tom Lane wrote:

"Billy G. Allie" <Bill.Allie@mug.org> writes:

Peter Eisentraut wrote:

Sounds interesting, but isn't "pgsql" an extremely unfortunate choice of
name, given that it's already used as an abbreviation for "PostgreSQL"?

PgSQL is the name of the module you import in Python to access a PostgreSQL
database from Python using the DB-API 2.0. The project name on SourceForge
is "Python Interface to PostgreSQL". Of course, it there is too much heart
burn with the modules name, I can change it.

FWIW, my initial reaction was the same as Peter's: that name is certain
to cause confusion. I don't want to try to force you to change it, but
I think it's not a good choice.

Don't have a better alternative to offer offhand, however.

regards, tom lane

I couldn't think of a better alternative, either. That's why the module
name is PgSQL. Of course, seeing 'import PgSQL' in the python code seems
to imply loading code to access PostgreSQL. It's kind of self-documenting,
don'y you think? :-)

I am, however, open to suggestions to a better alternative if one can be
offered.

I am, however, open to suggestions to a better alternative if one can be
offered.
___________________________________________________________________________
____ | Billy G. Allie | Domain....: Bill.Allie@mug.org
| /| | 7436 Hartwell | Compuserve: 76337,2061
|-/-|----- | Dearborn, MI 48126| MSN.......: B_G_Allie@email.msn.com
|/ |LLIE | (313) 582-1540 |

The project name on SourceForge is "Python Interface to PostgreSQL".

How about PIPgSQL or piPgSQL?

Regards,
//Dave

On Tue, 10 Oct 2000, Billy G. Allie wrote:

Tom Lane wrote:

"Billy G. Allie" <Bill.Allie@mug.org> writes:

Peter Eisentraut wrote:

Sounds interesting, but isn't "pgsql" an extremely unfortunate choice of
name, given that it's already used as an abbreviation for "PostgreSQL"?

PgSQL is the name of the module you import in Python to access a PostgreSQL
database from Python using the DB-API 2.0. The project name on SourceForge
is "Python Interface to PostgreSQL". Of course, it there is too much heart
burn with the modules name, I can change it.

FWIW, my initial reaction was the same as Peter's: that name is certain
to cause confusion. I don't want to try to force you to change it, but
I think it's not a good choice.

Don't have a better alternative to offer offhand, however.

regards, tom lane

I couldn't think of a better alternative, either. That's why the module
name is PgSQL. Of course, seeing 'import PgSQL' in the python code seems
to imply loading code to access PostgreSQL. It's kind of self-documenting,
don'y you think? :-)

D'Arcy's is PyGreSQL ...

I am, however, open to suggestions to a better alternative if one can be
offered.

I am, however, open to suggestions to a better alternative if one can be
offered.
___________________________________________________________________________
____ | Billy G. Allie | Domain....: Bill.Allie@mug.org
| /| | 7436 Hartwell | Compuserve: 76337,2061
|-/-|----- | Dearborn, MI 48126| MSN.......: B_G_Allie@email.msn.com
|/ |LLIE | (313) 582-1540 |

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

On Wed, 11 Oct 2000, Dnesbitt@encryptix.com wrote:

The project name on SourceForge is "Python Interface to PostgreSQL".

How about PIPgSQL or piPgSQL?

Perhaps Pi2PgSQL, or PySQL_ba

--
Sincerely etc.,

NAME Christopher Sawtell
CELL PHONE 021 257 4451
ICQ UIN 45863470
EMAIL csawtell @ xtra . co . nz
CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz

-->> Please refrain from using HTML or WORD attachments in e-mails to me <<--

On Wed, 11 Oct 2000, Christopher Sawtell wrote:

On Wed, 11 Oct 2000, Dnesbitt@encryptix.com wrote:

The project name on SourceForge is "Python Interface to PostgreSQL".

How about PIPgSQL or piPgSQL?

Perhaps Pi2PgSQL, or PySQL_ba

PySQL_ba? *grin*

On Tue, 10 Oct 2000, The Hermit Hacker wrote:

On Wed, 11 Oct 2000, Christopher Sawtell wrote:

On Wed, 11 Oct 2000, Dnesbitt@encryptix.com wrote:

The project name on SourceForge is "Python Interface to PostgreSQL".

How about PIPgSQL or piPgSQL?

Perhaps Pi2PgSQL, or PySQL_ba

PySQL_ba? *grin*

Pyg ??

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================

Christopher Sawtell wrote:

On Wed, 11 Oct 2000, Dnesbitt@encryptix.com wrote:

The project name on SourceForge is "Python Interface to PostgreSQL".

How about PIPgSQL or piPgSQL?

Perhaps Pi2PgSQL, or PySQL_ba

PyGSQyl.

Anagram fun on "PostgreSQL":
PostgreSQL=
Eg: Ports SQL
Gets Pro SQL
Got reps, SQL?

"Great Bridge" = Bigger Trade :-)
"Tuple TOASTer" = Alert! Pest out! (The pest being the 8K limit).
"Outer Joins" = Join so true.
"My new job" = Enjoy BMW.
"Open Source" = Our one spec.
"Linux versus FreeBSD" = Feud reruns vex bliss.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Two things.

Firstly, when dealing with a large ResultSet (about 120000 rows), I get a
null pointer exception on the line:
wasNullFlag = (this_row[columnIndex - 1] == null);
Whenever I call getString(), has anyone else had this? And does anybody
have a solution?

Secondly, I've not seen it mentioned on here but the jdbc driver will
sometimes throw a bad time stamp exception when you use getTimeStamp() on
times which have are accurate to more than a second, this is the patch we
use to fix it.

Hope it is of some use to somebody,

Michael Stephenson

*** src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java	Fri May 12
20:54:22 2000
--- src/interfaces/jdbc/org/postgresql/jdbc2/ResultSetPatch.java
Mon Sep 25 15:36:46 2000
***************
*** 439,445 ****
      if(s==null)
  	return null;

! SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd
HH:mm:sszzz");

      try {
  	return new Timestamp(df.parse(s).getTime());
--- 439,456 ----
      if(s==null)
  	return null;

! SimpleDateFormat df = null;
! if (s.length()>21 && s.indexOf('.') != -1) {
! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSzzz");
! } else if (s.length()>19 && s.indexOf('.') == -1) {
! df = new SimpleDateFormat("yyyy-MM-dd HH:MM:sszzz");
! } else if (s.length()>19 && s.indexOf('.') != -1) {
! df = new SimpleDateFormat("yyyy-MM-dd HH:MM:ss.SS");
! } else if (s.length()>10 && s.length()<=18) {
! df = new SimpleDateFormat("yyyy-MM-dd HH:MM:ss");
! } else {
! df = new SimpleDateFormat("yyyy-MM-dd");
! }

try {
return new Timestamp(df.parse(s).getTime());

On Wed, 11 Oct 2000, Michael Stephenson wrote:

Two things.

Firstly, when dealing with a large ResultSet (about 120000 rows), I get a
null pointer exception on the line:
wasNullFlag = (this_row[columnIndex - 1] == null);
Whenever I call getString(), has anyone else had this? And does anybody
have a solution?

Are you getting any out of memory errors at all?

The problem with the current implementation is that it reads the entire
result into memory, so 120000 rows may be filling up your VM's memory
(defaults to about 16Mb).

Does it occur if you add the -mx argument to java, ie:

java -mx 64m uk.org.retep.sql.RetepSQL

I'm in the design stage of implementing a version of ResultSet that will
use cursors, to limit how much is loaded in memory at a time.

Secondly, I've not seen it mentioned on here but the jdbc driver will
sometimes throw a bad time stamp exception when you use getTimeStamp() on
times which have are accurate to more than a second, this is the patch we
use to fix it.

This was fixed a few weeks ago and should be in the current CVS already.

peter

--
Peter T Mount peter@retep.org.uk http://www.retep.org.uk
PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
Java PDF Generator http://www.retep.org.uk/pdf/

Peter Mount wrote:

On Wed, 11 Oct 2000, Michael Stephenson wrote:

Two things.

Firstly, when dealing with a large ResultSet (about 120000 rows), I get a
null pointer exception on the line:
wasNullFlag = (this_row[columnIndex - 1] == null);
Whenever I call getString(), has anyone else had this? And does anybody
have a solution?

Are you getting any out of memory errors at all?

The problem with the current implementation is that it reads the entire
result into memory, so 120000 rows may be filling up your VM's memory
(defaults to about 16Mb).

Does it occur if you add the -mx argument to java, ie:

java -mx 64m uk.org.retep.sql.RetepSQL

I'm in the design stage of implementing a version of ResultSet that will
use cursors, to limit how much is loaded in memory at a time.

The problem with that is the same problem I ran into with locking.
cursors need to be in a BEGIN END block, and other calls from different
Statement objects using the same db connectioni will interfere. Make
sure it is clearly documented that that will not be thread safe (unless
postgres gets named locks by then).

On Wed, 11 Oct 2000, Joseph Shraibman wrote:

Peter Mount wrote:

On Wed, 11 Oct 2000, Michael Stephenson wrote:

Two things.

Firstly, when dealing with a large ResultSet (about 120000 rows), I get a
null pointer exception on the line:
wasNullFlag = (this_row[columnIndex - 1] == null);
Whenever I call getString(), has anyone else had this? And does anybody
have a solution?

Are you getting any out of memory errors at all?

The problem with the current implementation is that it reads the entire
result into memory, so 120000 rows may be filling up your VM's memory
(defaults to about 16Mb).

Does it occur if you add the -mx argument to java, ie:

java -mx 64m uk.org.retep.sql.RetepSQL

I'm in the design stage of implementing a version of ResultSet that will
use cursors, to limit how much is loaded in memory at a time.

The problem with that is the same problem I ran into with locking.
cursors need to be in a BEGIN END block, and other calls from different
Statement objects using the same db connectioni will interfere. Make
sure it is clearly documented that that will not be thread safe (unless
postgres gets named locks by then).

Yes, there is a problem with multiple statements and transactions on the
same connection when it comes to thread safety.

The problem as I see it is two fold.

1: How to deal with two statements within one transaction. Related to this
is the metadata methods that issue queries, how to deal with them while
within a transaction.

2: Currently the JDBC Specs only have transactions supported at the
Connection level, so I can't see how they thought that Statements could
possibly run within their own transactions, unless they thought that a
workaround of this is the use of batches.

Peter

--
Peter T Mount peter@retep.org.uk http://www.retep.org.uk
PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
Java PDF Generator http://www.retep.org.uk/pdf/

Peter Mount wrote:
...

Yes, there is a problem with multiple statements and transactions on the
same connection when it comes to thread safety.

The problem as I see it is two fold.

1: How to deal with two statements within one transaction. Related to this
is the metadata methods that issue queries, how to deal with them while
within a transaction.

2: Currently the JDBC Specs only have transactions supported at the
Connection level, so I can't see how they thought that Statements could
possibly run within their own transactions, unless they thought that a
workaround of this is the use of batches.

Ah, that probably explains why I've seen "tuple arrived before metadata"
messages when I've got several apps talking through CORBA to a java app
that connects to postgres. Do I need to synchronize both inserts and
queries at the java app level to prevent this? (I was hoping that
the BEGIN/END block in a transaction would be sufficient, but this makes
it sound as though it isn't.)

--
Steve Wampler- SOLIS Project, National Solar Observatory
swampler@noao.edu

Steve Wampler wrote:

Peter Mount wrote:
...

Yes, there is a problem with multiple statements and transactions on the
same connection when it comes to thread safety.

The problem as I see it is two fold.

1: How to deal with two statements within one transaction. Related to this
is the metadata methods that issue queries, how to deal with them while
within a transaction.

2: Currently the JDBC Specs only have transactions supported at the
Connection level, so I can't see how they thought that Statements could
possibly run within their own transactions, unless they thought that a
workaround of this is the use of batches.

Ah, that probably explains why I've seen "tuple arrived before metadata"
messages when I've got several apps talking through CORBA to a java app
that connects to postgres. Do I need to synchronize both inserts and
queries at the java app level to prevent this? (I was hoping that
the BEGIN/END block in a transaction would be sufficient, but this makes
it sound as though it isn't.)

It isn't. I wish there were online mail archives. But anyway the
reason it isn't is that if two statements use the same connection, when
one of them calls enters a transaction the other one does too. So if
you do:
1) BEGIN;
2) SELECT blah FROM tablea FOR UPDATE;
3) UPDATE tablea SET blah = newblah;

since both statements are using the same connection they ARE NOT LOCKED
FROM EACH OTHER, and when one calls and END; or COMIT; both of them exit
their transactions. That is why I'm using a connection pool now when I
have to do locking. And if Peter wants to use cursors behind the scenes
it will be even worse, because the cursors themselves need to be in a
BEGIN; END;, and what happens if they user thinks he is in a transaction
but the cursor ended it for him a while ago? Named transactions would
help with this, but the real answer would be to be able to have more
then one connection to a backend (maybe tunnelled over on TCP/IP link).
Right now each new connection requires forking off another backend,
which makes it impractical to connect whenever you have a new
transaction to do.

On Wed, 11 Oct 2000, Steve Wampler wrote:

Peter Mount wrote:
...

Yes, there is a problem with multiple statements and transactions on the
same connection when it comes to thread safety.

The problem as I see it is two fold.

1: How to deal with two statements within one transaction. Related to this
is the metadata methods that issue queries, how to deal with them while
within a transaction.

2: Currently the JDBC Specs only have transactions supported at the
Connection level, so I can't see how they thought that Statements could
possibly run within their own transactions, unless they thought that a
workaround of this is the use of batches.

Ah, that probably explains why I've seen "tuple arrived before metadata"
messages when I've got several apps talking through CORBA to a java app
that connects to postgres. Do I need to synchronize both inserts and
queries at the java app level to prevent this? (I was hoping that
the BEGIN/END block in a transaction would be sufficient, but this makes
it sound as though it isn't.)

I think you may need to, although the existing thread locking in the
driver should prevent this. BEGIN/END is protecting the tables, but the
"tuple arrived before metadata" message is from the network protocol
(someone correct me at any point if I'm wrong).

What happens at the moment is that when a query is issued by JDBC, a lock
is made against the network connection, and then the query is issued. Once
everything has been read, the lock is released. This mechanism should
prevent any one thread using the same network connection as another which
is already using it.

Is your corba app under heavy load when this happens, or can it happen
with say 2-3 apps running?

Peter

--
Peter T Mount peter@retep.org.uk http://www.retep.org.uk
PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
Java PDF Generator http://www.retep.org.uk/pdf/

On Wed, 11 Oct 2000, Joseph Shraibman wrote:

[snip]

It isn't. I wish there were online mail archives. But anyway the
reason it isn't is that if two statements use the same connection, when
one of them calls enters a transaction the other one does too. So if
you do:
1) BEGIN;
2) SELECT blah FROM tablea FOR UPDATE;
3) UPDATE tablea SET blah = newblah;

since both statements are using the same connection they ARE NOT LOCKED
FROM EACH OTHER, and when one calls and END; or COMIT; both of them exit
their transactions. That is why I'm using a connection pool now when I
have to do locking. And if Peter wants to use cursors behind the scenes
it will be even worse, because the cursors themselves need to be in a
BEGIN; END;, and what happens if they user thinks he is in a transaction
but the cursor ended it for him a while ago?

We already have this problem with the two MetaData interfaces. Some of
those methods issue their own queries, and if they fail while within the
client's app's transaction all hell can break loose. This is one of the
reasons why I'm intending that the cursor based ResultSet only gets used
if the client has requested one.

Named transactions would help with this, but the real answer would be
to be able to have more then one connection to a backend (maybe
tunnelled over on TCP/IP link).

Named transactions would help with both of these problems. Not knowing
enough of how the existing transaction mechanism works internally, I'd say
it's easer to do than tunnelling.

Right now each new connection requires forking off another backend,
which makes it impractical to connect whenever you have a new
transaction to do.

Yes, that's the last thing we want, especially while we are trying to
optimise things...

Peter

--
Peter T Mount peter@retep.org.uk http://www.retep.org.uk
PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
Java PDF Generator http://www.retep.org.uk/pdf/

Peter Mount wrote:

On Wed, 11 Oct 2000, Steve Wampler wrote:

Ah, that probably explains why I've seen "tuple arrived before metadata"
messages when I've got several apps talking through CORBA to a java app
that connects to postgres. Do I need to synchronize both inserts and
queries at the java app level to prevent this? (I was hoping that
the BEGIN/END block in a transaction would be sufficient, but this makes
it sound as though it isn't.)

I think you may need to, although the existing thread locking in the
driver should prevent this. BEGIN/END is protecting the tables, but the
"tuple arrived before metadata" message is from the network protocol
(someone correct me at any point if I'm wrong).

What happens at the moment is that when a query is issued by JDBC, a lock
is made against the network connection, and then the query is issued. Once
everything has been read, the lock is released. This mechanism should
prevent any one thread using the same network connection as another which
is already using it.

Is your corba app under heavy load when this happens, or can it happen
with say 2-3 apps running?

I'm not sure how to define heavy load, but I'd say yes - there were about
10 processes (spread across 3 machines) all talking corba to the app with
the jdbc app to postgres. Two apps was doing block inserts while another 8
were doing queries. I think there were around 100000 entries added in a
20-25minute time span, and there would have been queries accessing most
of those during the same period (the DB acts both as an archive and as
a cache between an instrument and the processes that analyze the instrument's
data).

--
Steve Wampler- SOLIS Project, National Solar Observatory
swampler@noao.edu

On Thu, 12 Oct 2000, Steve Wampler wrote:

Peter Mount wrote:

On Wed, 11 Oct 2000, Steve Wampler wrote:

Ah, that probably explains why I've seen "tuple arrived before metadata"
messages when I've got several apps talking through CORBA to a java app
that connects to postgres. Do I need to synchronize both inserts and
queries at the java app level to prevent this? (I was hoping that
the BEGIN/END block in a transaction would be sufficient, but this makes
it sound as though it isn't.)

I think you may need to, although the existing thread locking in the
driver should prevent this. BEGIN/END is protecting the tables, but the
"tuple arrived before metadata" message is from the network protocol
(someone correct me at any point if I'm wrong).

What happens at the moment is that when a query is issued by JDBC, a lock
is made against the network connection, and then the query is issued. Once
everything has been read, the lock is released. This mechanism should
prevent any one thread using the same network connection as another which
is already using it.

Is your corba app under heavy load when this happens, or can it happen
with say 2-3 apps running?

I'm not sure how to define heavy load, but I'd say yes - there were about
10 processes (spread across 3 machines) all talking corba to the app with
the jdbc app to postgres. Two apps was doing block inserts while another 8
were doing queries. I think there were around 100000 entries added in a
20-25minute time span, and there would have been queries accessing most
of those during the same period (the DB acts both as an archive and as
a cache between an instrument and the processes that analyze the instrument's
data).

Hmmm, I think you may want to look at using a connection pool, especially
with 100k entries. I've just looked through my Corba books, and they all
seem to use some form of pool, so perhaps that's the assumed best way to
do it.

Peter

--
Peter T Mount peter@retep.org.uk http://www.retep.org.uk
PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
Java PDF Generator http://www.retep.org.uk/pdf/

Peter Mount wrote:

On Wed, 11 Oct 2000, Joseph Shraibman wrote:

[snip]

It isn't. I wish there were online mail archives. But anyway the
reason it isn't is that if two statements use the same connection, when
one of them calls enters a transaction the other one does too. So if
you do:
1) BEGIN;
2) SELECT blah FROM tablea FOR UPDATE;
3) UPDATE tablea SET blah = newblah;

since both statements are using the same connection they ARE NOT LOCKED
FROM EACH OTHER, and when one calls and END; or COMIT; both of them exit
their transactions. That is why I'm using a connection pool now when I
have to do locking. And if Peter wants to use cursors behind the scenes
it will be even worse, because the cursors themselves need to be in a
BEGIN; END;, and what happens if they user thinks he is in a transaction
but the cursor ended it for him a while ago?

We already have this problem with the two MetaData interfaces. Some of
those methods issue their own queries, and if they fail while within the
client's app's transaction all hell can break loose. This is one of the
reasons why I'm intending that the cursor based ResultSet only gets used
if the client has requested one.

Named transactions would help with this, but the real answer would be
to be able to have more then one connection to a backend (maybe
tunnelled over on TCP/IP link).

Named transactions would help with both of these problems. Not knowing
enough of how the existing transaction mechanism works internally, I'd say
it's easer to do than tunnelling.

I don't think tunnelling would be that hard, just add a number to say
which connection this query is for. The real reason it won't be done is
that the coders who coded the backend don't want to share a backends
with more than one connection to keep a segfault (or other error) from
one connection taking down all the others. I see the logic in that, but
there is a compromise solution of allowing one client to have more than
one connection (with locks for each connection being seperate) over the
same TCP/IP socket so only one client is talking with one backend
process.

The project name on SourceForge is "Python Interface to PostgreSQL".

How about PIPgSQL or piPgSQL?

Perhaps Pi2PgSQL, or PySQL_ba

or PyPgSQL?

Do we get a reward if you choose our name? ;)

The project name on SourceForge is "Python Interface to PostgreSQL".

How about PIPgSQL or piPgSQL?

Perhaps Pi2PgSQL, or PySQL_ba

or PyPgSQL?
Do we get a reward if you choose our name? ;)

No...but if this thread doesn't die soon, Marc will have to create a new
mailing list for it.

Joel

I'm sorry to bother the list with this question - I know people are
always asking it. I thought I understood how to do this, but my code
doesn't work.

I am trying to save a java object in a database using the setBytes()
method of PreparedStatement - I don't want to use the large object manager
because I want this to be somewhat portable.

The code below gives me the error:
Exception: FastPath protocol error: Z :: FastPath protocol error: Z
As soon as I call setBytes()

I thought this only came when you forget to call "setAutoCommit(false)".
Can anyone please tell me what I'm doing wrong.

Thanks a lot, Rob

-------------------- PSQLTest.java -----------------------

import java.sql.* ;
import java.io.* ;
import java.util.* ;

public class PSQLTest {

public PSQLTest() { }

public static void main(String[] args) {

Vector vec = new Vector() ;
for (int i=0; i<10; ++i)
vec.addElement(new Integer(i+5)) ;

Connection conn = null ;
try {
Class.forName("postgresql.Driver") ;
conn = DriverManager.getConnection
("jdbc:postgresql://127.0.0.1:5432/rob", "rob", "") ;
conn.setAutoCommit(false);

byte[] bytes ;
ByteArrayOutputStream out = new ByteArrayOutputStream() ;
ObjectOutputStream objOut = new ObjectOutputStream(out) ;
objOut.writeObject(vec) ;
objOut.flush() ;
bytes = out.toByteArray() ;
objOut.close() ;

PreparedStatement ps = conn.prepareStatement
("insert into vectors (name, id) values ( ?, ?)") ;

ps.setString(1, "Vector name") ;
ps.setBytes(2, bytes) ;
ps.executeUpdate() ;
ps.close() ;

conn.commit() ;
} catch (Exception e) {
System.out.println("Exception: "+e+" :: "+e.getMessage());
e.printStackTrace() ;
}

}

}

Vince Vielhaber wrote:

On Tue, 10 Oct 2000, The Hermit Hacker wrote:

On Wed, 11 Oct 2000, Christopher Sawtell wrote:

On Wed, 11 Oct 2000, Dnesbitt@encryptix.com wrote:

The project name on SourceForge is "Python Interface to PostgreSQL".

How about PIPgSQL or piPgSQL?

Perhaps Pi2PgSQL, or PySQL_ba

PySQL_ba? *grin*

Pyg ??

PyLephant

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

On Mon, 23 Oct 2000, Jan Wieck wrote:

Vince Vielhaber wrote:

On Tue, 10 Oct 2000, The Hermit Hacker wrote:

On Wed, 11 Oct 2000, Christopher Sawtell wrote:

On Wed, 11 Oct 2000, Dnesbitt@encryptix.com wrote:

The project name on SourceForge is "Python Interface to PostgreSQL".

How about PIPgSQL or piPgSQL?

Perhaps Pi2PgSQL, or PySQL_ba

PySQL_ba? *grin*

Pyg ??

PyLephant

I like that!

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================