Thread: JDBC now needs updates for large objects
Peter,
As of current sources, large objects no longer occupy tables named
'xinvNNNN' nor indexes named 'xinxNNNN'. Therefore, it'd be appropriate
to remove the special tests that exclude tables/indices named that way
from the tests in DatabaseMetaData.java. I have not done this because
I'm not in a position to test changes to the JDBC driver; would you
please add it to your todo list?
regards, tom lane
I was going to redo those queries this coming weekend anyhow (as thats when I'll be getting some spare time next), as there are still some problems with the existing ones. Any other "minor" changes I should keep an eye out for? Idea: As we have this type of query in more than one part of the source tree (ie: psql, jdbc, probably odbc), should we have a section in the documentation containing common queries, like: retrieving a list of tables, views etc? I haven't seen a definitive one in there, but it would be useful, and have the other ones in the source be based on that one? Every time a change to the system tables is made, unless everyone who maintains the code that's dependent on it hears about it, the queries can quickly get out of sync. Peter -- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: petermount@maidstone.gov.uk WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Tuesday, October 24, 2000 2:58 AM To: Peter Mount Cc: pgsql-interfaces@postgreSQL.org; pgsql-hackers@postgreSQL.org Subject: JDBC now needs updates for large objects Peter, As of current sources, large objects no longer occupy tables named 'xinvNNNN' nor indexes named 'xinxNNNN'. Therefore, it'd be appropriate to remove the special tests that exclude tables/indices named that way from the tests in DatabaseMetaData.java. I have not done this because I'm not in a position to test changes to the JDBC driver; would you please add it to your todo list? regards, tom lane
Yes, the joins were one of the reasons I was going to do it. If no one starts a list by Saturday, then I'll start one when I go through JDBC. Peter -- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: petermount@maidstone.gov.uk WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Tuesday, October 24, 2000 1:44 PM To: Peter Mount Cc: pgsql-interfaces@postgresql.org; pgsql-hackers@postgresql.org Subject: Re: [INTERFACES] RE: JDBC now needs updates for large objects Peter Mount <petermount@maidstone.gov.uk> writes: > Idea: As we have this type of query in more than one part of the source tree > (ie: psql, jdbc, probably odbc), should we have a section in the > documentation containing common queries, like: retrieving a list of tables, > views etc? That's a good thought. It'd be a useful practice to review such standard queries from time to time anyway. For example, now that outer joins work, a lot of psql's backslash-command queries could be simplified (don't need the UNION ALL WITH SELECT NULL hack). Anyone have time to work up a list? regards, tom lane
Peter Mount <petermount@maidstone.gov.uk> writes:
> Idea: As we have this type of query in more than one part of the source tree
> (ie: psql, jdbc, probably odbc), should we have a section in the
> documentation containing common queries, like: retrieving a list of tables,
> views etc?
That's a good thought. It'd be a useful practice to review such
standard queries from time to time anyway. For example, now that
outer joins work, a lot of psql's backslash-command queries could
be simplified (don't need the UNION ALL WITH SELECT NULL hack).
Anyone have time to work up a list?
regards, tom lane
> -----Original Message----- > From: Peter Mount [mailto:petermount@maidstone.gov.uk] > Sent: 24 October 2000 12:43 > To: 'Tom Lane'; Peter Mount > Cc: pgsql-interfaces@postgreSQL.org; pgsql-hackers@postgreSQL.org > Subject: RE: [INTERFACES] RE: JDBC now needs updates for > large objects > > > Yes, the joins were one of the reasons I was going to do it. > > If no one starts a list by Saturday, then I'll start one when > I go through > JDBC. > > Peter I have done some work like this for the next release of pgAdmin (mainly views to allow easy reporting on db objects) - if you want copies of what I have let me know, it might be a starting point. Dave.
sorry, i must have missed something. when did outer join's start working? is there a patch that i can build? tonys. > That's a good thought. It'd be a useful practice to review such > standard queries from time to time anyway. For example, now that > outer joins work, a lot of psql's backslash-command queries could > be simplified (don't need the UNION ALL WITH SELECT NULL hack). > > Anyone have time to work up a list? > > regards, tom lane
"Tony Simopoulos" <karkalis@earthling.net> writes:
> i must have missed something. when did outer join's start working?
They're in current CVS (7.1-to-be). CVS tip is pretty unstable at
the moment with WAL stuff going on, but you could use it as a playpen
installation. Or wait for 7.1 beta, which should be out real soon now.
regards, tom lane
Yes please. It would be a good place to start from existing views. Peter -- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: petermount@maidstone.gov.uk WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council -----Original Message----- From: Dave Page [mailto:dpage@vale-housing.co.uk] Sent: Wednesday, October 25, 2000 9:29 AM To: 'pgsql-interfaces@postgresql.org' Subject: RE: [INTERFACES] RE: JDBC now needs updates for large objects > -----Original Message----- > From: Peter Mount [mailto:petermount@maidstone.gov.uk] > Sent: 24 October 2000 12:43 > To: 'Tom Lane'; Peter Mount > Cc: pgsql-interfaces@postgreSQL.org; pgsql-hackers@postgreSQL.org > Subject: RE: [INTERFACES] RE: JDBC now needs updates for > large objects > > > Yes, the joins were one of the reasons I was going to do it. > > If no one starts a list by Saturday, then I'll start one when > I go through > JDBC. > > Peter I have done some work like this for the next release of pgAdmin (mainly views to allow easy reporting on db objects) - if you want copies of what I have let me know, it might be a starting point. Dave.
> -----Original Message----- > From: Peter Mount [mailto:petermount@maidstone.gov.uk] > Sent: 26 October 2000 08:32 > To: 'Dave Page'; 'pgsql-interfaces@postgresql.org' > Subject: RE: [INTERFACES] RE: JDBC now needs updates for > large objects > > > Yes please. It would be a good place to start from existing views. > > Peter > Sorry for the delay Peter, I've been in other offices hanging around for BT engineers! There are various views below *which work for me*. They are designed to allow pgAdmin users to generate reports of the objects in their databases. Note that I've had to use functions quite a lot to avoid outer joins and unions, and that the comments column(s) in each view are retrieved from either pg_description or pgadmin_desc depending on whether the oid of the object is above 18655 (the last possible non-system oid from my experiments). Regards, Dave.
Attachment
Prefixed with PM: -- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: petermount@maidstone.gov.uk WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council -----Original Message----- From: Dave Page [mailto:dpage@vale-housing.co.uk] Sent: Tuesday, October 31, 2000 12:46 PM To: 'Peter Mount'; 'pgsql-interfaces@postgresql.org' Subject: RE: [INTERFACES] RE: JDBC now needs updates for large objects > -----Original Message----- > From: Peter Mount [mailto:petermount@maidstone.gov.uk] > Sent: 26 October 2000 08:32 > To: 'Dave Page'; 'pgsql-interfaces@postgresql.org' > Subject: RE: [INTERFACES] RE: JDBC now needs updates for > large objects > > > Yes please. It would be a good place to start from existing views. > > Peter > Sorry for the delay Peter, I've been in other offices hanging around for BT engineers! PM: I know the feeling. Anyhow, I've had problems with the storms yesterday morning (had no power first thing, couldn't get into work etc), so when the power came back on, I finally started on them. PM: Started on the query to get details on the columns, and I'm seeing some wierd results. The original query retrieved the oid of the column type, and jdbc then queried for the type name as a separate query (yes, I'm going to try to amalgamate these). Anyhow, on different tables, and it appears to be random, but if it occurs for a table, it always occurs) the first query returns the type name not the oid. I'm going to try to get a fresh cvs out due to the make problems I was having, just incase it's something unusual with the working 7.1 backend I have. There are various views below *which work for me*. They are designed to allow pgAdmin users to generate reports of the objects in their databases. Note that I've had to use functions quite a lot to avoid outer joins and unions, and that the comments column(s) in each view are retrieved from either pg_description or pgadmin_desc depending on whether the oid of the object is above 18655 (the last possible non-system oid from my experiments). PM: Yes, I currently implement this by issuing a separate query on pg_description. Once I get the existing queries working I'm going to try to use an outer join. Regards, Dave.