Re: DBD::Pg 1.20 uncompatible with Postgres 7.3?
On Thursday, December 5, 2002, at 09:44 AM, Jeff Boes wrote: So what's the answer? At present, we cannot upgrade to PostgreSQL 7.3 because the DBD code will quit working. I could produce a patch that solves our problem, but I have no facilities or expertise to verify that it will work for everyone. Yeah, but it would be a start. Patches warmly welcomed. Regards, David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED]
Re: DBD::Pg 1.20 uncompatible with Postgres 7.3?
On Thu, 2002-12-05 at 12:39, David Wheeler wrote: > I chatted with Bruce Momjian about this a bit, too. My idea: PostgreSQL > should provide functions that do this sort of thing, so that psql, > DBD::Pg, org.postgresql.jdbc, and PostgreSQL ODBC can just use the > functions without worrying about the version numbers. > > But then, somebody's gotta write the functions, contributed them to the > project, get them committed, and then wait for 7.4 to take advantage of > them. Still, it could eventually make such driver metadata requirements > much more portable across later PostgreSQL versions. > So what's the answer? At present, we cannot upgrade to PostgreSQL 7.3 because the DBD code will quit working. I could produce a patch that solves our problem, but I have no facilities or expertise to verify that it will work for everyone. -- Jeff Boes vox 616.226.9550 ext 24 Database Engineer fax 616.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise
Re: DBD::Pg 1.20 uncompatible with Postgres 7.3?
On Wednesday, December 4, 2002, at 11:40 AM, Jeff Boes wrote: I received the following communication from Tom Lane, who is active in PostgreSQL development: ... without so much as a "ta-ta" in the release notes. Hmph. Well, is there any hope that a search-and-replace will convert old source code to using the new table? It shouldn't be a difficult change. Be aware that pg_constraint has more stuff in it than pg_relcheck did, so you'll want to add "WHERE contype = 'c'" to be sure you only see the rows corresponding to the old pg_relcheck table. I chatted with Bruce Momjian about this a bit, too. My idea: PostgreSQL should provide functions that do this sort of thing, so that psql, DBD::Pg, org.postgresql.jdbc, and PostgreSQL ODBC can just use the functions without worrying about the version numbers. But then, somebody's gotta write the functions, contributed them to the project, get them committed, and then wait for 7.4 to take advantage of them. Still, it could eventually make such driver metadata requirements much more portable across later PostgreSQL versions. Best, David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED]
Re: DBD::Pg 1.20 uncompatible with Postgres 7.3?
I received the following communication from Tom Lane, who is active in PostgreSQL development: > ... without so much as a "ta-ta" in the release notes. Hmph. Well, is > there any hope that a search-and-replace will convert old source code to > using the new table? It shouldn't be a difficult change. Be aware that pg_constraint has more stuff in it than pg_relcheck did, so you'll want to add "WHERE contype = 'c'" to be sure you only see the rows corresponding to the old pg_relcheck table. regards, tom lane "Poets have been mysteriously silent on the subject of cheese." --- G. K Chesterton ___ Jeff Boes <>< [EMAIL PROTECTED]
Getting Oracle version number via DBI (was: DBD::Pg 1.20 uncompatible with Postgres 7.3?)
On Wed, Dec 04, 2002 at 08:20:52AM +1100, Steve Baldwin wrote: > I'm sure this has been asked many times before, but Tim's reply here > prompted me to ask again. Is there any way to access the DB version (as > per your example of get_info(18) below) from DBI. For example as an > attribute of the database handle. I've consulted the manual, but > couldn't find any reference to this. We currently have a requirement to > support Oracle 7.3 and 9i, and in some cases the SQL we execute needs to > be different (to take advantage of 9i features). At present we obtain > the version ourselves by issuing : > "select banner from v$version", and parsing the appropriate bits out. I > reckon it would be nicer (and presumably more portable) if the DBD layer > could expose this info. > > Whadayareckon ? $dbh->get_info(18) is supported in the next release (thanks to Steffen Goeldner). Tim. p.s. Don't forget to change the subject line when changing the topic
RE: DBD::Pg 1.20 uncompatible with Postgres 7.3?
On Tue, 2002-12-03 at 16:20, Steve Baldwin wrote: > I'm sure this has been asked many times before, but Tim's reply here > prompted me to ask again. Is there any way to access the DB version (as > per your example of get_info(18) below) from DBI. For example as an > attribute of the database handle. I've consulted the manual, but > couldn't find any reference to this. We currently have a requirement to > support Oracle 7.3 and 9i, and in some cases the SQL we execute needs to > be different (to take advantage of 9i features). At present we obtain > the version ourselves by issuing : > "select banner from v$version", and parsing the appropriate bits out. I > reckon it would be nicer (and presumably more portable) if the DBD layer > could expose this info. Well, the easiest way would be to roll your own extended class from DBI, and just add the version as a new method. -- Jeff Boes vox 616.226.9550 ext 24 Database Engineer fax 616.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise
RE: DBD::Pg 1.20 uncompatible with Postgres 7.3?
I'm sure this has been asked many times before, but Tim's reply here prompted me to ask again. Is there any way to access the DB version (as per your example of get_info(18) below) from DBI. For example as an attribute of the database handle. I've consulted the manual, but couldn't find any reference to this. We currently have a requirement to support Oracle 7.3 and 9i, and in some cases the SQL we execute needs to be different (to take advantage of 9i features). At present we obtain the version ourselves by issuing : "select banner from v$version", and parsing the appropriate bits out. I reckon it would be nicer (and presumably more portable) if the DBD layer could expose this info. Whadayareckon ? -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 4 December 2002 7:47 AM To: David Wheeler Cc: Jeff Boes; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: DBD::Pg 1.20 uncompatible with Postgres 7.3? On Tue, Dec 03, 2002 at 10:56:47AM -0800, David Wheeler wrote: > On Tuesday, December 3, 2002, at 10:34 AM, Jeff Boes wrote: > > >Nope, not quite: > > > >$ grep pg_ Pg.pm > > > >of Pg.pm 1.13 will show you that it relies on pg_database, pg_user, > >pg_class, pg_description, pg_attribute, pg_type, pg_index, > >pg_attrdef, and pg_relcheck. All but the last one are still in 7.3. > >table_attributes is present in 1.13, and uses most of the listed > >tables. > > Apparently, pg_relcheck was merged into another table in 7.3. Bummer > for backwards compatibility, that. I'll try to figure out how the new > query should work in the next few days, but if anyone beats me to it, > patches welcome. > > I guess this also means that DBD::Pg really needs to be changed to > always detect the version of the database it connects to so that it can > do the right thing. Tim, is there a DBI-standard way to story/provide > an RDBMS version number? $version = get_info(18); # SQL_DBMS_VER But note that the result needs to be formatted in a particular way: e.g. 06.50.0201 Google found http://www.4d.com/ACIDOC/CMU/CMU11922.HTM saying: SQL_DBMS_VER (ODBC 1.0) A character string indicating the version of the DBMS product accessed by the driver. The version is of the form ##.##., where the first two digits are the major version, the next two digits are the minor version, and the last four digits are the release version. The driver must render the DBMS product version in this form, but can also append the DBMS product-specific version as well. For example, "04.01. Rdb 4.1". You might find this interesting: http://www.geocrawler.com/archives/3/105/2001/1/0/5030630/ Driver authors are reminded that the DBI docs say: Because some DBI methods make use of get_info(), drivers are strongly encouraged to support I the following very minimal set of information types to ensure the DBI itself works properly: Type NameExample A Example B -- 17 SQL_DBMS_NAME 'ACCESS' 'Oracle' 18 SQL_DBMS_VER'03.50.' '08.01.0721' 29 SQL_IDENTIFIER_QUOTE_CHAR '`' '"' 41 SQL_CATALOG_NAME_SEPARATOR '.' '@' 114 SQL_CATALOG_LOCATION1 2 Tim.
Re: DBD::Pg 1.20 uncompatible with Postgres 7.3?
On Tue, Dec 03, 2002 at 10:56:47AM -0800, David Wheeler wrote: > On Tuesday, December 3, 2002, at 10:34 AM, Jeff Boes wrote: > > >Nope, not quite: > > > >$ grep pg_ Pg.pm > > > >of Pg.pm 1.13 will show you that it relies on pg_database, pg_user, > >pg_class, pg_description, pg_attribute, pg_type, pg_index, pg_attrdef, > >and pg_relcheck. All but the last one are still in 7.3. > >table_attributes is present in 1.13, and uses most of the listed > >tables. > > Apparently, pg_relcheck was merged into another table in 7.3. Bummer > for backwards compatibility, that. I'll try to figure out how the new > query should work in the next few days, but if anyone beats me to it, > patches welcome. > > I guess this also means that DBD::Pg really needs to be changed to > always detect the version of the database it connects to so that it can > do the right thing. Tim, is there a DBI-standard way to story/provide > an RDBMS version number? $version = get_info(18); # SQL_DBMS_VER But note that the result needs to be formatted in a particular way: e.g. 06.50.0201 Google found http://www.4d.com/ACIDOC/CMU/CMU11922.HTM saying: SQL_DBMS_VER (ODBC 1.0) A character string indicating the version of the DBMS product accessed by the driver. The version is of the form ##.##., where the first two digits are the major version, the next two digits are the minor version, and the last four digits are the release version. The driver must render the DBMS product version in this form, but can also append the DBMS product-specific version as well. For example, "04.01. Rdb 4.1". You might find this interesting: http://www.geocrawler.com/archives/3/105/2001/1/0/5030630/ Driver authors are reminded that the DBI docs say: Because some DBI methods make use of get_info(), drivers are strongly encouraged to support I the following very minimal set of information types to ensure the DBI itself works properly: Type NameExample A Example B -- 17 SQL_DBMS_NAME 'ACCESS' 'Oracle' 18 SQL_DBMS_VER'03.50.' '08.01.0721' 29 SQL_IDENTIFIER_QUOTE_CHAR '`' '"' 41 SQL_CATALOG_NAME_SEPARATOR '.' '@' 114 SQL_CATALOG_LOCATION1 2 Tim.
Re: DBD::Pg 1.20 uncompatible with Postgres 7.3?
On Tuesday 03 December 2002 19:34, Jeff Boes wrote: (B> On Tue, 2002-12-03 at 12:16, Ian Barwick wrote: (B> > AFAICT DBD::Pg versions prior to 1.20 don't query any pg_ tables (B> > and _seem_ not to break anywhere when talking to 7.3 . I think (B> > $dbh->table_attributes() etc. were added in 1.20. (B> > (B> > You may have more luck with DBD::PG 1.13 for the moment. (B> (B> Nope, not quite: (B> (B> $ grep pg_ Pg.pm (B> (B> of Pg.pm 1.13 will show you that it relies on pg_database, pg_user, (B> pg_class, pg_description, pg_attribute, pg_type, pg_index, pg_attrdef, (B> and pg_relcheck. All but the last one are still in 7.3. (B> table_attributes is present in 1.13, and uses most of the listed tables. (B (BAh, yes, apologies, please regard my last statement, somewhere along (Bthe line I seem to have got hold of the wrong end of a large stick (B(note to self: need holiday). (B (B;-) (B (BIan Barwick ([EMAIL PROTECTED]
Re: DBD::Pg 1.20 uncompatible with Postgres 7.3?
On Tuesday, December 3, 2002, at 10:34 AM, Jeff Boes wrote: Nope, not quite: $ grep pg_ Pg.pm of Pg.pm 1.13 will show you that it relies on pg_database, pg_user, pg_class, pg_description, pg_attribute, pg_type, pg_index, pg_attrdef, and pg_relcheck. All but the last one are still in 7.3. table_attributes is present in 1.13, and uses most of the listed tables. Apparently, pg_relcheck was merged into another table in 7.3. Bummer for backwards compatibility, that. I'll try to figure out how the new query should work in the next few days, but if anyone beats me to it, patches welcome. I guess this also means that DBD::Pg really needs to be changed to always detect the version of the database it connects to so that it can do the right thing. Tim, is there a DBI-standard way to story/provide an RDBMS version number? Thanks, David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED]
Re: DBD::Pg 1.20 uncompatible with Postgres 7.3?
On Tue, 2002-12-03 at 12:16, Ian Barwick wrote: > AFAICT DBD::Pg versions prior to 1.20 don't query any pg_ tables > and _seem_ not to break anywhere when talking to 7.3 . I think > $dbh->table_attributes() etc. were added in 1.20. > > You may have more luck with DBD::PG 1.13 for the moment. > Nope, not quite: $ grep pg_ Pg.pm of Pg.pm 1.13 will show you that it relies on pg_database, pg_user, pg_class, pg_description, pg_attribute, pg_type, pg_index, pg_attrdef, and pg_relcheck. All but the last one are still in 7.3. table_attributes is present in 1.13, and uses most of the listed tables. -- Jeff Boes vox 616.226.9550 ext 24 Database Engineer fax 616.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise
Re: DBD::Pg 1.20 uncompatible with Postgres 7.3?
On Tuesday 03 December 2002 17:32, Jeff Boes wrote:
> On Tue, 2002-12-03 at 11:12, Jeff Boes wrote:
> > We may have uncovered an incompatibility with DBD::Pg (version 1.20 is
> > the latest we have) and the newly-released PostgreSQL 7.3.
> >
> > It appears that the DESTROY method for database and/or statement handles
> > is referencing a system table ("pg_relcheck") that no longer exists.
> > Attached is the trace that illustrates the problem.
>
> Oops. The problem is still there, but I've narrowed it down; it's not
> in the DESTROY method at all, but in $dbh->table_attributes:
This also happens sometings when connecting to 7.3 with a pre 7.3 psql
and using \d to get table descriptions. Some pg_ tables have changed
in 7.3, so applications which query them may break like that.
AFAICT DBD::Pg versions prior to 1.20 don't query any pg_ tables
and _seem_ not to break anywhere when talking to 7.3 . I think
$dbh->table_attributes() etc. were added in 1.20.
You may have more luck with DBD::PG 1.13 for the moment.
Ian Barwick
[EMAIL PROTECTED]
Re: DBD::Pg 1.20 uncompatible with Postgres 7.3?
On Tuesday, December 3, 2002, at 08:32 AM, Jeff Boes wrote: Oops. The problem is still there, but I've narrowed it down; it's not in the DESTROY method at all, but in $dbh->table_attributes: Use of uninitialized value in split at /usr/lib/perl5/site_perl/5.6.1/i686-linux/DBD/Pg.pm line 576. ERROR: current transaction is aborted, queries ignored until end of transaction block Are you using transactions somewhere? Can you send a code snippet that generates this error? Thanks, David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED]
Re: DBD::Pg 1.20 uncompatible with Postgres 7.3?
On Tue, 2002-12-03 at 11:12, Jeff Boes wrote:
> We may have uncovered an incompatibility with DBD::Pg (version 1.20 is
> the latest we have) and the newly-released PostgreSQL 7.3.
>
> It appears that the DESTROY method for database and/or statement handles
> is referencing a system table ("pg_relcheck") that no longer exists.
> Attached is the trace that illustrates the problem.
Oops. The problem is still there, but I've narrowed it down; it's not
in the DESTROY method at all, but in $dbh->table_attributes:
Use of uninitialized value in split at
/usr/lib/perl5/site_perl/5.6.1/i686-linux/DBD/Pg.pm line 576.
ERROR: current transaction is aborted, queries ignored until end of
transaction block
--
Jeff Boes vox 616.226.9550 ext 24
Database Engineer fax 616.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
DBD::Pg 1.20 uncompatible with Postgres 7.3?
We may have uncovered an incompatibility with DBD::Pg (version 1.20 is
the latest we have) and the newly-released PostgreSQL 7.3.
It appears that the DESTROY method for database and/or statement handles
is referencing a system table ("pg_relcheck") that no longer exists.
Attached is the trace that illustrates the problem.
-> ping for DBD::Pg::db (DBI::db=HASH(0x8aca764)~0x8aca7d0)
dbd_db_ping
<- ping= 1 at Table.pm line 1475
-> prepare for DBD::Pg::db (DBI::db=HASH(0x8aca764)~0x8aca7d0 'SELECT * FROM
job_methods LIMIT 1')
dbd_st_prepare: statement = >SELECT * FROM job_methods LIMIT 1<
dbd_st_preparse: statement = >SELECT * FROM job_methods LIMIT 1<
<- prepare= DBI::st=HASH(0x8bf3694) at DBConnection.pm line 635
-> execute for DBD::Pg::st (DBI::st=HASH(0x8bf3694)~0x8bf3868)
dbd_st_execute
dbd_st_execute: statement = >SELECT * FROM job_methods LIMIT 1<
<- execute= 1 at Table.pm line 1321
-> errstr for DBD::Pg::st (DBI::st=HASH(0x8bf3694)~0x8bf3868)
<- errstr= undef at Table.pm line 1322
-> FETCH for DBD::Pg::st (DBI::st=HASH(0x8bf3868)~INNER 'NAME_lc')
dbd_st_FETCH
1 -> FETCH for DBD::Pg::st (DBI::st=HASH(0x8bf3868)~INNER 'NAME')
dbd_st_FETCH
1 <- FETCH= [ 'method_id' 'method_name' ] at Table.pm line 1337
<- FETCH= [ 'method_id' 'method_name' ] at Table.pm line 1337
-> DESTROY for DBD::Pg::st (DBI::st=HASH(0x8bf3868)~INNER)
dbd_st_finish
dbd_st_destroy
<- DESTROY= undef at Table.pm line 1341
-> table_attributes for DBD::Pg::db (DBI::db=HASH(0x8aca764)~0x8aca7d0
'job_methods')
1 -> selectall_arrayref in DBD::_::db for DBD::Pg::db (DBI::db=HASH(0x8aca7d0)~INNER
'select a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef,
a.attnum
from pg_attribute a,
pg_class c,
pg_type t
where c.relname = ?
and a.attrelid = c.oid
and a.attnum >= 0
and t.oid = a.atttypid
order by 1
' undef 'job_methods')
2 -> prepare for DBD::Pg::db (DBI::db=HASH(0x8aca7d0)~INNER 'select a.attname,
t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum
from pg_attribute a,
pg_class c,
pg_type t
where c.relname = ?
and a.attrelid = c.oid
and a.attnum >= 0
and t.oid = a.atttypid
order by 1
' undef)
dbd_st_prepare: statement = >select a.attname, t.typname, a.attlen, a.atttypmod,
a.attnotnull, a.atthasdef, a.attnum
from pg_attribute a,
pg_class c,
pg_type t
where c.relname = ?
and a.attrelid = c.oid
and a.attnum >= 0
and t.oid = a.atttypid
order by 1
<
dbd_st_preparse: statement = >select a.attname, t.typname, a.attlen, a.atttypmod,
a.attnotnull, a.atthasdef, a.attnum
from pg_attribute a,
pg_class c,
pg_type t
where c.relname = ?
and a.attrelid = c.oid
and a.attnum >= 0
and t.oid = a.atttypid
order by 1
<
dbd_preparse scanned 1 distinct placeholders
2 <- prepare= DBI::st=HASH(0x8bf5520) at DBI.pm line 1251
-> execute for DBD::Pg::st (DBI::st=HASH(0x8bf5520)~0x8adbb64 'job_methods')
dbd_bind_ph
bind :p1 <== 'job_methods' (type 0)
dbd_st_rebind
bind :p1 <== 'job_methods' (size 11/12/0, ptype 4, otype 1043)
dbd_st_execute
dbd_st_execute: statement = >select a.attname, t.typname, a.attlen, a.atttypmod,
a.attnotnull, a.atthasdef, a.attnum
from pg_attribute a,
pg_class c,
pg_type t
where c.relname = 'job_methods'
and a.attrelid = c.oid
and a.attnum >= 0
and t.oid = a.atttypid
order by 1
<
<- execute= 2 at DBI.pm line 1253
-> fetchall_arrayref for DBD::Pg::st (DBI::st=HASH(0x8bf5520)~0x8adbb64 undef
undef)
1 -> fetch for DBD::Pg::st (DBI::st=HASH(0x8adbb64)~INNER)
dbd_st_fetch
1 <- fetch= [ 'method_id' 'int4' '4' '-1' '1' '0' '1' ] row1 at DBI.pm line 1577
1 -> fetch for DBD::Pg::st (DBI::st=HASH(0x8adbb64)~INNER)
dbd_st_fetch
1 <- fetch= [ 'method_name' 'text' '-1' '-1' '1' '0' '2' ] row2 at DBI.pm line 1577
1 -> fetch for DBD::Pg::st (DBI::st=HASH(0x8adbb64)~INNER)
dbd_st_fetch
1 <- fetch= undef row2 at DBI.pm line 1577
<- fetchall_arrayref= [ ARRAY(0x8adbb7c) ARRAY(0x8bf5418) ] row2 at DBI.pm line
1261
-> DESTROY for DBD::Pg::st (DBI::st=HASH(0x8adbb64)~INNER)
dbd_st_destroy
<- DESTROY= undef at perl5db.pl line 1487
1 <- selectall_arrayref= [ ARRAY(0x8adbb7c) ARRAY(0x8bf5418) ] at Pg.pm line 552
1 ->
