Re: [HACKERS] Roadmap for FE/BE protocol redesign
Justin Clift wrote: [ ... ] The problem Dave is suggesting this as a first attempt at a solution for is that with ODBC, a frontend (i.e. OpenOffice) asks the ODBC driver which columns are NULLable, etc. And the ODBC driver is getting the info wrong, then passing back the incorrect info. And that's probably why OpenOffice doesn't allow you to update a view, event if there are rules allowing this from psql ... This, in my book is an EPITA for end users ... So, when a person goes to insert a row into a table with a SERIAL/SEQUENCE based column, OpenOffice has been told the column isn't NULLable and forces the user to enter a value. Voila, it doesn't work with sequences. :( It's likely possible to add to the ODBC driver some way of getting the info right, but Dave is also looking for a way of making this easier into the future for similar problems. i.e. Let the database explicitly have info about what each column can do. I'd second that, even if it takes time ... Emmanuel Charpentier -- Emmanuel Charpentier ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] May I have som syntactic sugar, please ?
The recent addition of drop column makes possible to change column type. E. g. : I have written a small utility reading Medline queries results and dumping them in a Postgres database for further analysis. [ Two remarks before starting ; 1) I *know* that I'm reinventing the wheel and that you can get those results in a nice XML format ; but the problem is that I do not (yet) know XLM, and have no time writing and using an XML parser, while I know how to parse a simple line-oriented format ; and 2) no, the possible fields are not a known fixed set : Medline has alreadu changer it's formats thrice in the las two years, so the reading routines have to be dynamic. Alas ... ] These results are a field-by-field enumeration. i.e eachrecord is a set of tag - Value pairs (well, not exactly : some fields have more than one line, but I'm scketchy ...). Some fields may be missing, some other may be repeated. The obvious solution is to create a temporary table with a set of keys identifying record, tag and file source and a value column, which has better to be of text type. It is almost trivial to create (programactically) the structure of the main table (a record identifier, one or two columns identifying the source, an a column for each field present at most once in the DB) and the auxilliary tables containing repeated fields. But this table has initially all values text, which is a) Horribly inefficient, b) hard to use from outer programs (M$ tools will choke on an indexed text field) and c) quite inadapted to certain queries (e. g. comparisons of dates. Furthermore, this kind of modifications cannot always be made t read-time. It happens that only the *semantics* of the problem at hand allows to choose the right* column type. In my example, the date of last Medline indexing is formally a timestamp ; but for all practical purposes, a date is more than sufficient. drop columns allows to fix this dynmically, along the line of alter table x add column new_y newtype; update x set new_y=y::newtype, alter table x drop column y; alter table x rename column new_y to y;. This has two side effects : The first one is unimportant (at least from a relational point of view) : the order of columns in the table is lost. The other one is *most* important : any Postgres object using the column definition (I'm thinking of indexes, views and rules but there might be others) will have to be redefined. What I'm thinking of would be something along the line of alter table x alter column y retype to newtype, doing essentially the same as above, but *keeping the old column id* and *propagating* changes as needed in defined view and indices (maybe with a cascade option, deleting unupdtable objects as needed ?). Am I dreaming awake ? Or is this implementable ? An sufficiently generally useful ? Comments ? Emmanuel Charpentier -- Emmanuel Charpentier ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [Fwd: Backporting parts of databases from a 7.3 server
Tom Lane wrote: Emmanuel Charpentier [EMAIL PROTECTED] writes: However, this does *not* work between a 7.3-generated dump and a 7.2 production server. The archiver complaints of an 'unknown archive format : 0' (I'm quoting this from the top of my head : my production server is not reachable from the place I'm writing this). In general, dumps from newer versions make use of SQL features that are not in older versions; so it's more or less hopeless to expect backwards compatibility of dumps. I'm not sure why pg_dump's archive header format changed, but even without that you'd be facing SQL-level compatibility issues. You could perhaps have some success by dumping as a text-format dump (not -Fc or -Ft) and then editing the resulting file to dumb the SQL down to 7.2's level. That's what I did ... I had little to no issues with the resulting SQL, but emacs'ing my way in a database dump was ... strange ! Thanks a lot ! Emmanuel Charpentier PS : Tom, I'm Cc'ing you, but I'm not sure that this answer will reach you directly. Your spam filter asininely believes that anybody running SMTP on a dynamically assigned IP is a spammer ! -- Emmanuel Charpentier ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] [Fwd: Backporting parts of databases from a 7.3 server to 7.2 : How?]
Posted about 2 weeks to the General and Questions lists. Got no answers and found no workaround (yet !). Any ideas ? Emmanuel Charpentier PS : If possible, Please Cc: to [EMAIL PROTECTED] : I'm reading the list through the news server, and nor very often ... Original Message Subject: Backporting parts of databases from a 7.3 server to 7.2 : How ? Date: Mon, 20 Jan 2003 19:31:53 +0100 From: Emmanuel Charpentier [EMAIL PROTECTED] Organization: Hub.Org Networking Services (http://www.hub.org) Newsgroups: comp.databases.postgresql.general,comp.databases.postgresql.questions Dear list(s), I have a test machine, which I recently upgraded to PostgreSQL 7.3, and a production machine, which for now will stay at 7.2 (I have some issues with ODBC access to 7.3 with the current driver). I have no problem exporting databases (or parts of them) from the 7.2 to the 7.3 machine. For example production$ pg_dump -F c -f mybase.dump mybase test$ pg_restore -c mybase.dump or even test$ pg_restore -l mybase.dump | grep VIEW mybase.views test$ pg_restore -L mybase.views mybase.dump The latter one is not really useful. However, the reverse operation (i.e. restoring on the production system a set of views created on the test machine) is actually a useful one : it allows me to get from the production database a snapshot of data, work on it on the test machine, creating useful views in the process, and restoring them without cloberring (possibly updated) data. The same could be said of function, indexes, triggers, rules and so on ... However, this does *not* work between a 7.3-generated dump and a 7.2 production server. The archiver complaints of an 'unknown archive format : 0' (I'm quoting this from the top of my head : my production server is not reachable from the place I'm writing this). The only workaround I could come up with so far was to (watch it !) ! 1) create a -F c dump 2) pg_restore -l to get a list of the objects 3) looping through this list, pg_dump -F p -t each and every view, appending the proceeds to a single SQL file, which can be played back to the pproduction server. Not fun, and hardly generalisable ... Questions : 1) Is that a bug or a feature ? 2) Is there a workaround (e. g. by telling the 7.3 pg_dump to use a 7.2-recognized format ) ? 3) Do you have other suggestions (short of upgrading the production server to 7.3, which I plan to do when my issues with ODBC access will be solved). Sincerely, Emmanuel Charpentier PS : Would you be so kind as to Cc: me your answers : I'm on the lists in a no-mail mode and read it through the news interface. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [mail] Re: Win32 port patches submitted
Mingw and mingw-ported tools ? That's a nice small and cozy unix-like envoronment on tom of Windows. Add it emacs, and windoww becomes almost tolerable ... Emmanuel Charpentier [ Back to lurking ... ] Brian Bruns wrote: Problem is, nobody builds packages on windows anyway. They just all download the binary a guy (usually literally one guy) built. So, let's just make sure that one guy has cygwin loaded on his machine and we'll be all set. /tougue in cheek Sorry, couldn't help myself...Seriously, it's a cultural thing, I wouldn't plan on a mighty hoard of windows database developers who are put off by loading cygwin. I do wonder what the requirements are for building commercial db's that run on unix and windows. I imagine they are similarly off-putting if it were an option. On Tue, 21 Jan 2003, Al Sutton wrote: I would back keeping the windows specific files, and if anything moving the code away from using the UNIX like programs. My reasoning is that the more unix tools you use for compiling, the less likley you are to attract existing windows-only developers to work on the code. I see the Win32 patch as a great oppertunity to attract more eyes to the code, and don't want the oppertunity to be lost because of the build requirements. Al. - Original Message - From: Peter Eisentraut [EMAIL PROTECTED] To: Jan Wieck [EMAIL PROTECTED] Cc: Postgres development [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 5:40 PM Subject: [mail] Re: [HACKERS] Win32 port patches submitted Jan Wieck writes: I just submitted the patches for the native Win32 port of v7.2.1 on the patches mailing list. I'm concerned that you are adding all these *.dsp files for build process control. This is going to be a burden to maintain. Everytime someone changes an aspect of how a file is built the Windows port needs to be fixed. And since the tool that operates on these files is probably not freely available this will be difficult. I don't see a strong reason not to stick with good old configure; make; make install. You're already requiring various Unix-like tools, so you might as well require the full shell environment. A lot of the porting aspects such as substitute implemenations of the C library functions could be handled nearly for free using the existing infrastructure and this whole patch would become much less intimidating. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [mail] Re: Win32 port patches submitted
Jan Wieck wrote: Emmanuel Charpentier wrote: Mingw and mingw-ported tools ? That's a nice small and cozy unix-like envoronment on tom of Windows. Add it emacs, and windoww becomes almost tolerable ... How good is the debugging support under mingW? Is it at least comparable to using gdb under unix? If not, you might find yourself all of the sudden out in cold ... gdb has been ported to mingw. There even exist some interfaces to graphical IDEs (while I don't really care for that). Another point : this environment is 100% free. You don't have to use proprietary tools. This might be a point in some environments. Emmanuel Charpentier ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [mail] Re: Win32 port patches submitted
Dann Corbit wrote: [ ... ] GDB works fine. Some of the other tools don't work right (e.g. sed is broken). Recent fixes exist, but I didn't check all of them. WorksForMe(TM), but my projects are *much* simpler ... Emmanuel Charpentier ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Survey results from the PostgreSQL portal page
Gavin Sherry wrote: On Mon, 20 Jan 2003, Christopher Kings-Lynne wrote: I wonder why people ask for better documentation. I think the documentation is really good. Ever read Oracle stuff? *ugh*. Ever read MySQL docs - *hack*!! The documentation definately needs work -- particularly client library documentation and PL/PgSQL. I want to work on this when I get time. Case in point : in 7.3, the ODBC driver documentation (which was terse and somewhat outdated, to begin with ...) has disappeared from the main tree. You have to go to GBorg to find (some) relevant information (and no examples, BTW). But to find an information I really needed, I had to use ... the driver source, fer Crissakes !! I felt back in '74, when I tried to learn Fortran. [ BTW : note to Hiroshi Inoue : Thank you ! I partially solved by problem, and think a real solution is bound to to undoing some 7.2 to 7.3 modifications ...] The same could be said of the JDBC driver, btw, while it's doc is still in the main doc tree. This one is one of my pet peeves at the moment ... Emmanuel Charpentier ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] default to WITHOUT OIDS? Possible related problem
Tom Lane wrote: Daniel Kalchev [EMAIL PROTECTED] writes: If ever this happens, same should be considered for tables created via the SELECT INTO statement. These are in many cases 'temporary' in nature and do not need OIDs (while making much use of the OIDs counter). SELECT INTO does create tables without OIDs, as of 7.3. We've already had complaints about that ;-) I very recently updated one of my servers to 7.3.1. Various MS tools have started to give me guff when trying to access views in databases on that server through ODBC. Especially, MS Query (yes, I have some Excel users needing that) started complaining that this table has no OID, which really means that the ODBC driver complaints that ... Is that a side effect of the above problem ? Sincerely, Emmanuel Charpentier ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Open 7.3 issues
Christopher Kings-Lynne wrote: [ ... ] What about this. 1. Implement pg_get_foreignkey_def() or whatever 2. Adjust pg_dump to dump foreign keys using an ALTER statement 3. Back port the above to rel 7_2_2 4. Release a 7.2.2 version and ask that people upgrade to that version and do a dump before they upgrade to 7.3. 5. All is well, plus ppl who don't want to switch to 7.3 on production get all sorts of useful bug fixes as well. If we have to have a 7.2.2 release, I'd like to suggest to backport create or replace view , it it doesn't use any 7.3-specific feature. This one is my pet peeve at the moment ... ;-) Would give a *lot* of end-user functionality (and I am a and-user, after all), at little cost, if I'm not mistaken ...). -- Emmanuel Charpeentier ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Possible enhancement : replace view ?
Dear all, The current implementation of views uses OIDs, not table/view names. As a consequence, when you happen to replace (drop then create) an underlying table or view, you also have to drop and recreate all views using this table|view (and this recursively, of course ...). I stumbled on this while doing repeat analyses (involving repeated uses of aggregation) of the same dataset using slight variations of the subset of interest. When my dataset was small, I used to do that in (yuck !) MS-Access by creating a view defining the subset of interest, then creating views based on this view, and so on... Now that my dataset is too large to be Access-manageable, I migrated it to PostgreSQL (which, BTW, gave me nice performance enhancements), but I had to change my working habits. I have now to create a script defining my views, then to run it at each and every variation of the subset of interest ... To be able to conserve existing views would definitely be a bonus. Of course, the overhead is necessary to handle the general case. However, there is a special case where this is unnecessary : when the new table or view class definition is a (possibly improper) subclass of the original one, or, if you prefer, when the column set of the new definition is a (possibly improper) superset of the old one. For tables, this case is already handled by a judicious use of alter table, at least in its present form (adding DROP COLUMN, which might be an interesting feature for other reasons, entails the risk of invalidating existing views ...). However, there is currently no easily reachable way to do that for a view (I suppose that the special case of modifying the definition of a view creating the same columns in the old and new definitions might be possible with a clever (ab)use of system catalogs, but I tend to be *very* wary of such hacks ...). Of course, I am aware that view definitions aren't just stored, but that a lot of rewriting is involved before storing the actual execution plan.Modifying a view definition would entail re-processing of other view definitions. But so is the case with the modification of a table ... What do you think ? -- Emmanuel Charpentier ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Possible enhancement : replace view ?
Hannu Krosing wrote: On Wed, 2002-08-14 at 04:08, Emmanuel Charpentier wrote: Dear all, ... Of course, I am aware that view definitions aren't just stored, but that a lot of rewriting is involved before storing the actual execution plan.Modifying a view definition would entail re-processing of other view definitions. But so is the case with the modification of a table ... What do you think ? I'm trying to propose a scenario where 1. The SELECT clause defining the view is preserved 2. DROP of undrlying table/column will _not_ drop the view, but just mark it dirty 3. Using the view checks for the dirty flag and if it is set tries to recreate the view from its plaintext definition. I might be dense, but why not try to recreate it directly after the table/column modification ? --- Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Possible enhancement : replace view ?
Hannu Krosing wrote:
On Wed, 2002-08-14 at 04:23, Emmanuel Charpentier wrote:
Hannu Krosing wrote:
I'm trying to propose a scenario where
1. The SELECT clause defining the view is preserved
2. DROP of undrlying table/column will _not_ drop the view, but just
mark it dirty
3. Using the view checks for the dirty flag and if it is set tries to
recreate the view from its plaintext definition.
I might be dense, but why not try to recreate it directly after the
table/column modification ?
If it is a DROP TABLE/CREATE TABLE sequence you have no idea that you
have to recreate a view.
Right. But I was wary of delaying recreation : views are more often than
not created by programmers/DBAs/someone somewhat competent in DB design
and use, in order to be used by people not necessarily aware of the real
struxture of data (that's the whole point of having views, BTW).
Delaying recreation entails the risk of overlooking a problem and
getting a nice phone call at 2 AM from the maintainance guy stuttering
that he can no longer access its (vital, of course) data ...
Tradeoffs, again ... What about emitting warnings after table drop
(easy)/creation (not so easy !) ?
BTW : since drop column and alter various attributes (not null, primary
key, etc ...) will be possible, shoudn't the need to drop/recteate a
table drastically decrease ?
E. g. : I recently created a log table wit a field date timestamptz
default now(), only to discover that, due to current limitations of the
ODBC driver, I should have used timestamptz[0] (ODBC doesn't like
fraction of seconds in datetime). I kludged away bby updating (set
date=date_trunc('second',date)) and altering default to
date_trunc('second',date) (Yuck !), but the real solution would have
been of course to recreate the column with the right attribute, which
currently involves dropping/recreating the table, therefore losing all
defined views.
What a ten-thumbs programmer such as me would love to see in such a
scenario would be something along the lines of :
# Create table T (date as timestamp defailt now(), ...) ...;
CREATE
# Create view X as select date, ... from T join ...;
CREATE
# Create view Y as select anthing but date ... from T where ...;
CREATE
Create view Z as select date, ... from T join ...;
# CREATE
Create view U as select ... from Z left outer join ...;
---
--- Insert data here
---
...
---
--- Later ! Insert ODBC epiphany here
---
# alter table T add column newdate timestamptz[0];
ALTER --- I can't remember the exact acknowledgement sent for alter column
update T set newdate=date;
UPDATE (somenumber) 0
alter table T rename column date to olddate;
ALTER --- ditto
WARNING : View X might have become invalid. Please check it or drop it !
WARNING : View Z might have become invalid. Please check it or drop it !
WARNING : View U might have become invalid. Please check it or drop it !
alter table T rename newdate to date;
ALTER --- ditto;
WARNING : View X successfully recreated from it's original SQL
definition. Please check it or drop it !
WARNING : View Z successfully recreated from it's original SQL
definition. Please check it or drop it !
WARNING : View U successfully recreated from it's original SQL
definition. Please check it or drop it !
Alter table T drop column olddate;
ALTER
Exercise left for the reader : what about inheritance ?
Another exercise : what about adding/dropping indices (indexes ?) ?
Your thoughs ?
__
Emmanuel Charpentier
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Current ODBC driver(s) problems with 7.1
Dear list, I have made some progress about the current state of the ODBC drivers. I have tried three ODBC drivers : The original ODBC river, as compiled by Oliver Elphick in the Debian 7.1beta4 packages : this one is utterly broken : trying to use it leads to nothing : no activity is loged neither in syslog nor in postgres.log with -d2. Nick Gorham says it's because the driver and the driver manager wait mutually for each other, IIRC. The same driver patched (how ?) by Nick Gorham has some basic functionality : it can query the DB in arbitrary ways and is able to do other basic things. However, it has other problems. It displays only tables, not views, and has some serious limitations on system tables. Nick Gorham's unixODBC driver. This ione has only basic functionality : it can connect and query the backend, but only with a hand-crafted query. No way to get the list of tables, nor metadata. In the first case, I can do nothing : I'm reluctant to try to rebuild the Debian packages from source (I don't kniow how to do this from the sources and Oliver's patches). It follows that I can't do that for the second either. However, the problems exhibited by the second and third drivers are of the same nature : the SQL queries sent by them to get thje metadata are no longer valid for 7.1, since the system tables have undergo a lot of changes. I will try to fix the third and publish my result and changes, hoping to see them ported on the first one. Any thoughs ? And, BTW, where can I find the docs of the 7.0 system tables ? I know where the 7.1 docs are ... Sincerely yours, Emmanuel Charpentier
Re: [HACKERS] Release in 2 weeks ...
The Hermit Hacker wrote:
Morning all ...
Are there any major outstandings that ppl have on their plates,
that should prevent a release? I'd like to put out an RC1 by Friday this
week, with a full release schedualed for March 15th ... this would give
Thomas his two weeks for the docs freeze ...
Basically, RC1 would say to ppl that we're ready to release, there
will be no more core changes that will require an initdb ... feel
comfortable using this version in production, with the only major changes
between now and release being docs related ...
Does this work? Or is there something earth-shattering that still
has to be done?
Yep ! As of beta4, the ODBC driver is still seriously broken (the
original libpsqlodbc.so.0.26 doesn't even connect. A version patched by
Nick Gorham allows some connectivity (you can query the DB), but still
has some serious breakage (i. e. no "obvious" ways to see views from
StarOffice or MS-Access)).
And I have not yet had any opportunity to test the JDBC driver.
[ Explanation : I follow the Debian packages prepared by Oliver Elphick,
I'm not versed enough in Debian to recreate those packages myself, and I
do *not* want to break Debian dependencies by installing Postgres "The
Wrong Way (TM)". Hence, I'm stuck with beta4, a broken ODBC and no JDBC.
Unless some kind soul can send me a JD. 1.1 .jar file ...
Furthermore, I've had some serious hardware troubles (a dying IDE disk).
I wasn't even able to fulfill Tom Lane's suggestion to try to add -d2 to
my postmaster to debug the ODBC connection. I'll try to do that Real
Soon Now (TM, again), but not for now : my day-work backlog is ...
impressive. ]
These issues might seem small change to you die-hard plpgsql hackers. To
a lmot of people using Postgres for everyday office work through "nice"
interface, it's bread-and-butter, and these issues *should* be fixed
*before* release ...
[ crawling back under my rock ... ]
Emmanuel Charpentier
[HACKERS] ODBC protocol changes in 7.1 ? And to pgaccess ?
I have a problem with 7.1 beta 4
Setup :
Debian 2.3(?) (unstable)
Kernel 2.18pre23 (the stock Debian kernel)
PostgreSQL 7.1beta4 as packaged for Debian by Oliver Elphick
unixODBC 2.0.3 compiled from unixODBC.org's sources against the installed and
working PG 7.1b4.
The problem is that an ODBC connection (from StarOffice or unixODBC's
Datamanager tool) is correctly opened, but no tables show up.
This problem seems limited : the ODBC driver has some function, as proved by
an attempt to use the RODBC package of the R statistical program :
ch1-odbcConnect("Essai1","","") -- username and password masked
sqlTables(ch1)
[1] -1 -- That means "error". However :
sqlQuery(ch1,"select * from tablea;") -- send a query absed on previous knowledge
keyaval -- Result is OK.
11 Prem's
22 Deuz
33 Troiz
Warning message:-- Not unusual when reading text fields with RODBC.
a column has been truncated in: sqlGetResults(channel, errors = errors, ...)
odbcClose(ch1)
[1] 1
So my question is : what has changed in the protocol ?
And, BTW : what has happened to pgaccess ? Views doesn't show up, while psql
sees them.
--
Emmanuel Charpentier
Re: [HACKERS] Re: MySQL and BerkleyDB (fwd)
Peter Eisentraut wrote:
The Hermit Hacker writes:
Is anyone looking at doing this? Is this purely a MySQL-ism, or is it
something that everyone else has except us?
It's not required by SQL, that's for sure. I think in 7.2 we'll tackle
schema support, which will accomplish the same thing. Many people
(including myself) are of the opinion that not allowing cross-db access is
in fact a feature.
I am of the inverse opinion : cross-DB joining is the only reasonable
way to cope with the unfortunate, disgracious, unreasonable, but quite
inescapable real-life fact that all data do not live in the same server
in any but the smallest sites ...
I recently did a plea in this list ("A post-7.1 wishlist") in this
direction, and got an answer (Peter Einstraut ?) that was more or less
on the lines of "over our dead bodies !" ... Sigh ...
However, I *think* that it could be done by another tool, such as
Easysoft's (Nick Gorham's, I think) SQL Engine, which allows for joins
between any ODBC-reachable tools. This tool is unreasonably expensive
for private use ($800 + $200/year mandatory maintainance). A PostgreSQL
alternative would be, IMSAO, a huge benefit, even huger if able to
cross-join with ODBC data sources ...
M$ Access has this, since version 1, and that's a hell of a handy
feature for a lot of cases involving management of multiple data sources
...
why not just build PgSQL, and have transaction support *with* subselects
and everything else that mySQL doesn't have?
I'd *love* to use PgSQL, but it doesn't support cross-DB joins (or at
least I couldn't figure out how to do it.) MySQL handles this, so
I'm using MySQL and would also like to have transaction support...
I have to tell that my daily work involves this kind of problems, with
data sources ranging from SAS datasets under MVS/XA to Excel files to
Oracle databases to younameit ... That's the kind of problem I would
*love* to have PostgreSQL to cope with, and *not* M$ Access ...
[ Back to lurking mode ... ]
E. Charpentier
--
Emmanuel Charpentier
Re: [HACKERS] copy from stdin; bug?
Nathan Myers wrote: [ ... ] Not true. There are Debian source packages, Where are they ? I'm *quite* interested ! and taking the source package from Debian 2.x, x2 (woody/sid), you can easily build it on Debian 2.2 (potato). In fact, it seems likely that a 2.2 (potato) packaging of 7.1 should be available from somebody else anyhow. Oliver, do you plan to make the woody 7.1 package depend on any other package versions not in potato? If not, you can just use the 7.1 package directly on your Debian 2.2 system. Oliver Elphick seems awfully busy and once said that 7.1 required a *lot* of packaging ... Better not bug him right now ... -- Emmanuel Charpentier
Re: [HACKERS] A post-7.1 wish-list.
Horst Herb wrote: On Sunday 07 January 2001 21:31, Emmanuel Charpentier wrote: [ ... ] Excuse me, but where has MS Access competence? It is a pretty useless data lottery with an admittedly very capable easy user interface. The odds of data corruption can't possibly be higher with any other system (I used to develop hospital information systems prototyping with Access). Competence : in this context, the ability to define a correct data structure and the views used tu access and update it. The "easy to use" interface is a big incentive to end users to define things properly, instead of relying to the horrible "Excel sheet with forms" I used to have to cope with. In a heavy multi-user environment, MS Access might be a "data lottery". I have no direct experience of this. In my low-use environment, I had no real reason to complain about data corruption : my problems were more bound to the lack of protection of the MS Windows environment, and were solved my migrating data files on a proper server. Performance, however, can be poor. I have in mind some examples involving Cartesian products or remerging that were hell to compute :=(( ... [ ... ] According to the current documentation, views are read.only. This implies some grunt work when creating update forms for the kind of low-use applications I have to manage. You can implement it yourself through trigger functions, no big deal. Grunt work, as I said. And we are awfully short on time for doing this. But, typical applications nowadays interact with the user through dialogues with text entry fields. Your client has to process this information anyway. Just a minimum, none if possible ... Our processing is made mostly *post* entry. Doesn't make much of a difference then to split the information to the tables it belongs to instead of just putting it into your view. "Just putting it into my view" is a hell of a lot faster and a hell of a lot less work. And I (and my assistants) are paid to work on our data, not to write programs to access them ... Data entry and maintainance are an ancilliary task. Quite often, two or more distinct applications have to use common data. My favourite example is again medical : two othewise unrelated applications might have to use a common medical thesaurus. The obvious solution (including the medical thesaurus tables in each and every application) leads to awful consistency problems. Working this way can be properly done only with replication, which is not yet available ??? why? You are working with a client-server system, and you can have any number of databases on a postgres server. Nope : the thesaurus data might not be ours, and live on a server we can *read*, not *write to*, and certainly not program to our heart's content, and definitively *not* with PostgreSQL. Do you really think I can request our financial department to throw off their beloved Oracle databases, end result of tens of years of work, and switch to our preffered PostgreSQL server ? And do you think I could put sensitive medical information on a server accessible to people not cleared to view any medical data ? If so, you'd better think again before proposing gnumed ... For reasons that should be obvious, our data live in a server accessible to a small number of microcomputers in our department. And there is a *lot* of good and bad reasons for which they will stay here. For example, our database authority (the french CNIL) would have a seizure if those data were to be migrated on a non-restricted server. Furthermore, we might have to use *several* unrelated database not belonging to us ... The "one server serves all client" is totally unapplicable to our problems .. I'm looking for a practical solution, not for an excuse to an administrative war, that I'd loose, btw ... No need for replication for this purpose. in PostgreSQL. Furthermore, most applications will use only one or two views of the thesaurus, while the thesaurus might be both large and complex. That does not matter for the application (client). It will be blissfully unaware of the size or complexity of your thesaurus. A microcomputer client having to manage the huge size of the original database would be painfully aware of this ... and the users too ... That non-solution, therefore, would imply migrating our medical data to exactly one central server, which is, again, unacceptable. Another "obvious solution" (delegating the use of the thesaurus to the client application) is also a non-solution : how do you join your data and the thesaurus data ? The usual way as in any relational data base: by referencing the information. That makes the client application a relational RDBMS with capability to access more than one database at a time. This was M
Re: [HACKERS] A post-7.1 wish-list.
Peter Eisentraut wrote: Emmanuel Charpentier writes: 1) Updatable views. You can make rules updateable by attaching appropriate rules to them. The transparent implementation of updateable views would essentially do that. It's a planned feature but I don't know of anyone who has made it his priority. I'd try my hand at it, given enough time ... which I'm awfully short on. Which is one of my reasons to have that feature : not having to do grunt work. 2) External database or table access. Accessing more than one database from a connection will not happen, at least as long has the current mindset of the developers persists. SQL schema support is planned for 7.2, which will address the same area, however. I fail to see how schema support would help me accessing data residing on different servers (that cannot be moved, for a lot of good and bad reasons). Could you please amplify ? -- Emmanuel Charpentier
[HACKERS] A post-7.1 wish-list.
Dear list, According to this list's content, the upcoming 7.1 release appears to be in good progress. This version will bring a *lot* on necessary features for some database work : unions and subselects in views, and the long-awaited outer joins. In other words, while 7.0 was a large step in *performance* terms, 7.1 will be a huge advance in *competence*. These advances will allow me to use PostgreSQL for some work I had to do until now with (gasp !) MS-Access (which has poor performance but good competence). And get rid of the damn MS-Windows envoronment for good ! This leads me to express two whishes for future PotgreSQL developments. These ideas are inpired by my daily work and might or might not be of great usefulness for other uses. My daily work (biostatistics) involves managing a lot of small but complex databases : those are mainly medical records, created for a study's purposes, wich have a low volume (a dozen or two of tables having some dozens to some thousands rows) but might have a deeply nested and irregular structure (not all patients recorded need to have records of all histories and procedures involved). As a consequence, I am much more interested in competence than in performance, and so is my wishlist. Keep that in mind when reading what follows. 1) Updatable views. == According to the current documentation, views are read.only. This implies some grunt work when creating update forms for the kind of low-use applications I have to manage. I know that computing the "updatability" of a view is not a trivial problem. Furthermore, even when a view is indeed updatable, the update/append algorithm is not easy to compute. These problems are even harder in multi-user mode. And I do not have any idea of the feasibility of such updates in an OO database, where inheritance concerns will interfere. However, such updatable views would greatly simplify the end-user work for creating and maintaining these records (at least when no inheritance is involved. I am not able to state the usefulness of such "updatable views" in more mainstream applications. I note, however, that most "brand-name" RDBMSes ofer that. Your thoughs ? 2) External database or table access. Quite often, two or more distinct applications have to use common data. My favourite example is again medical : two othewise unrelated applications might have to use a common medical thesaurus. The obvious solution (including the medical thesaurus tables in each and every application) leads to awful consistency problems. Working this way can be properly done only with replication, which is not yet available in PostgreSQL. Furthermore, most applications will use only one or two views of the thesaurus, while the thesaurus might be both large and complex. Another "obvious solution" (delegating the use of the thesaurus to the client application) is also a non-solution : how do you join your data and the thesaurus data ? The ability to "attach" (MS-Access parlance) a table or a view from another database is quite helpful. And I think that it has a lot of applications outside my (quite limited) realm. For example, two different departments of the same company might have needs for two different management applications, while having to use/update the same company-wide accounting records. I don't se the "replication" solution as a good one (data duplication should be considered harmful in any circumstances). This could be implemented in different ways. From the easiest to the hardest : - Attachment of Postgres databases running on the same server : relatively easy. The data structures (internal representation) are known, there is a guarantee of consistency in user identification, security information is also consistent. - Attachment of Postgres databases running on another server. There, while the data structures are known, the user and security informations can be inconsistent and have to be managed "by hand". - Attachment of other databases. Ouch : this one is hard. One have to rely on the information made available by the other database server. And there lies a problem : there is no universal standard for this. ... or there is ? Two bridges come to mind. Using ODBC or JDBC, provided the "other" RDBMS has that, allows to use some standard information : at the very minimum, table names, attribute names and type, and updatability. In most cases, you will also be able to know whether indices are available for such and such columns. This minimal set of information allows you to use these external tables in your own joins. And, provided that you have update rights, the ability to use them as "native" tables. Of course, the use of these bridges involve some (maybe quite serious) performance loss. But then again, I'm less interested in performance than in competence ... What do you think ? Emmanuel Charpentier -- Emmanuel Charpentier
Re: [HACKERS] Re: Beta2 ... ?
Tom Lane wrote: Lamar Owen [EMAIL PROTECTED] writes: I am inclined to wait until a Release Candidate, if we have one this go around, is available before releasing RPM's, but my mind can be changed :-) Please do make beta RPMs available. Seems to me that there's a fair-size population of potential beta testers that we're shutting out of the process if we don't put out RPMs. Losing available beta testing work is not a good project management practice ... I'd like to argue for .deb Debian packages as well, for similar reasons. But I'm aware that those are harder to produce, and that Oliver Elphick is almost alone on this task. -- Emmanuel Charpentier
[HACKERS] NULLS and : Discrepancies ?
Sorry for intruding, but the following question did not get much attention on the "General" list. However, I still need the answer ... /LurkingMode NewbieMode Could some kind soul explain this to me ? test1=# select distinct "Cle" from "Utilisateurs"; Cle - 1 2 3 4 (4 rows) test1=# select distinct "CleUtil" from "Histoires"; CleUtil - 1 (2 rows) -- Uuhhh ! test1=# select count(*) as NbRec from "Histoires" where "CleUtil" is null; nbrec --- 2 (1 row) -- Ah Ahh ... I have NULLs. test1=# select distinct "Cle" from "Utilisateurs" where "Cle" in test1-# (select distinct "CleUtil" from "Histoires"); Cle - 1 (1 row) -- That's OK ... test1=# select distinct "Cle" from "Utilisateurs" where "Cle" not in test1-# (select distinct "CleUtil" from "Histoires"); Cle - (0 rows) -- That's definitively *NOT* OK ! However test1=# select distinct "Cle" from "Utilisateurs" where "Cle" not in test1-# (select distinct "CleUtil" from "Histoires" where "CleUtil" is not null); Cle - 2 3 4 (3 rows) -- That's what I expected in the first place. Could someone explain to me why not eliminating nulls destroys the potential results of the query ? In other words, for any X not null, X not in (some NULLs) is false. /NewbieMode LurkingMode Emmanuel Charpentier
Re: [HACKERS] Tuple data
Hiroshi Inoue wrote : [ ... ] Column order isn't essential in rdbms. Nitpicking A relation (a table) is a subset of the Cartesain cross-product of the definition domains of the attributes (columns). Cartesian product being a commutative operation, "order of columns" does not really exists. Period. If you impose an order relationship, you *add* inforation to the structure. That may be OK, but you can't rely on relational algebra to guarantee your results. You'll have to manage it yourself. (And, yes, there is relevant algebra for this, too ...). /Nitpicking Isn't it well known that it's not preferable to use 'select *','insert' without column list etc.. in production applications ? 100% agreed. Such a notation is an abbreviation. Handy, but dangerous. IMHO, such checking can (should ?) be done by an algorithm checking for column *names* before sending the "insert" command. A partial workaround : inserting in a view containing only the relevant columns, in a suitable (and known) order. [ Back to lurking ... ]
