Re: [PATCHES] [JDBC] Error in DatabaseMetaData.getColumns() with Views
I have found that the problem is worst. In the sample adjunct, you can see that error arise at the time when the view's sql text is parsed and saved in database catalog. Then generic NUMERIC type is forced for every calculated column without regard or precision. And at execute time the f2 column has varying type decimals (in row 2 you can see 4 decimals and in other rows has 3 decimals), this is not a behavior , this is an ERROR. Precision calculation in Numeric fields it's not so difficult. This problem *must* be corrected in the CREATE VIEW sentence , and I offer my collaboration for that. Regards all, Dario Fassi. Dario V. Fassi wrote: Kris Jurka wrote: On Fri, 2 Jul 2004, Dario Fassi wrote: Hi, I wish to report a erroneous information returned by DatabaseMetaData.getColumns() method. This happens with ResultSet's column (7) COLUMN_SIZE and (9) DECIMAL_DIGITS , when DatabaseMetaData.getColumns() inform about a VIEW with columns formed with : coalesce , case or numeric operations over DECIMAL fields. Suppose CREATE TABLE A ( f1 DEC(6,3), f2 dec(6,3) ); CREATE VIEW B as ( select ( f1 + f2 ) as f from a; Then DatabaseMetaData.getColumns() returns: VIEW B F NUMERIC( 65535 , -65531 ) The problem is that f1+f2 does not retain the numeric(6,3) size restriction, but turns into an unbounded plain "numeric" data type. So when retrieving this data the precision/scale are unavailable and the unreasonable values you see are returned. We could return NULL instead, but I'm not sure that would be much more helpful if the client is expecting real values. Any other ideas? Kris Jurka Yes, a few. In the tool named PgManage (come with the commercial version of Mammoth) , the information is accurate values for this MetaData, and I believe, they get that values from pqsql catalog tables and not from DatabaseMetaData interface. More even, the engine resolve the View properly and return data values properly typed (engine return 999.999 for f1+f2 and 999.99 for f1*f2 , like is expected). So, the information if know or derived en some way for the engine , and/or is contained some where in catalog's tables. I'm working in CodeGeneration tools for many DB engines (DB2, Oracle, MS-Sql, PostgreSql, etc) , and it's impossible not to use generic DatabaseMetaData interface to obtain metadata information. So this is a very important problem for me. DB2 for example do, data type escalation based on "Error propagation Theory " , that has rules (I'm not and expert in the field) like : [dec(6,a) + dec(6,b) ] - [ dec( 6, max(a,b) ) ] [ coalesce( dec(6,a) , dec(6,b) ] - [ dec[ 6, max(a,b)] ] [ case( dec(6,a) , dec(6,b), dec(6,c), dec(6,d) ] - [ dec[ 6, max(a,b,c,d)] ] [dec(6,a) * dec(6,b ) - [ dec( 6,a+b ) ] etc. This rules are taken into account in the engine data formation process, but there are stored some where ??? Thanks for your answer. Dario V. Fassi CREATE TABLE public.t ( f1 numeric(6,3), f2 numeric(6,4), f3 numeric(8,2) ) WITHOUT OIDS; insert into public.t values ( 123.123, 12.1234, 12345.12); insert into public.t values ( null, 12.1234, 12345.12); insert into public.t values ( 123.123, null, 12345.12); insert into public.t values ( 123.123, 12.1234, null); -- -- Create sql text -- create view public v as select (f1+f2+f3) as fsum, coalesce(f1,f2,f3) as fcoal, (f1*f2*f3) as fprod from public.t; -- -- Parsed sql view from catalog -- CREATE OR REPLACE VIEW public.v AS SELECT ((t.f1 + t.f2 ) + t.f3 ) AS fsum, CASE WHEN (t.f1 IS NOT NULL ) THEN t.f1 WHEN (t.f2 IS NOT NULL ) THEN t.f2 WHEN (t.f3 IS NOT NULL ) THEN t.f3 ELSE NULL::numeric END AS fcoal, ((t.f1 * t.f2 ) * t.f3 ) AS fprodFROM t; -- -- Returned Data -- select * from public.v; fsumfcoal fprod 12480.3664 123.123 18427182.594204384 null12.1234 null !!! No look good !!! null123.123 null null123.123 null -- -- -- WORKAROUND WORKAROUND WORKAROUND -- -- -- -- Create sql text with forced datatype -- create view public v2 as select cast( (f1+f2+f3)as dec(9,4) ) as fsum, cast( coalesce(f1,f2,f3)as dec(7,4) ) as fcoal, cast( (f1*f2*f3)as dec(20,9) ) as fprod from public.t; -- -- Parsed sql view from catalog -- CREATE OR REPLACE VIEW public.v2 AS SELECT (((t.f1 + t.f2 ) + t.f3))::numeric(9,4 ) AS fsum, ( CASE WHEN (t.f1 IS NOT NULL ) THEN t.f1 WHEN (t.f2 IS NOT NULL ) THEN t.f2 WHEN (t.f3 IS NOT NULL ) THEN t.f3 ELSE NULL::numeric END)::numeric(7,4 ) AS fcoal, (((t.f1 * t.f2 ) * t.f3))::numeric(20,9 ) AS fprod FROM t; -- -- Returned Data -- select * from public.v2;
Re: [PATCHES] Digital Mars C++ - Clients
Walter wrote: Bruce Momjian [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Uh, I have never heard of Digital Mars C++. I see it here though: http://www.digitalmars.com/ Having never had anyone who uses it, it seems this patch would be better kept where users of digital Mars could find it, like on their ftp server. I don't think there enough usage of Digital Mars for us to adjust our source code at this time. Do you agree? Check out the www.download.com download statistics for DMC++, which is just one portal one can download it from: http://www.download.com/sort/3150-2069-0-1-5.html? Looks good, but we have had no requests for it prior to this, and your additions aren't trivial. You also mentioned it doesn't have popen, meaning it might still be in a state of flux, meaning we would have to continue adjusting our patch --- for a compiler no one has asked about. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PATCHES] Oracle compatibility ...
This week I have been to Southern Germany. This customer has sucessfully
migrated from Oracle to PostgreSQL. With the help of a gborg package
(Oracle style data dictionary) and CREATE DOMAIN they were able to run
there current application WITHOUT modification.
There was just one thing we have encountered:
SELECT * FROM X MINUS SELECT * FROM X
does not work on PostgreSQL (we use EXCEPT instead).
This small patch fixes this problem.
Maybe it improvements can be integrated into 7.6
Regards
Hans
*** ./doc/src/sgml/keywords.sgml.orig 2004-07-08 21:11:29.694379616 +0200
--- ./doc/src/sgml/keywords.sgml 2004-07-08 21:11:12.583980792 +0200
***
*** 1785,1790
--- 1785,1796
entryreserved/entry
/row
row
+ entrytokenMINUS/token/entry
+ entryreserved/entry
+ entryreserved/entry
+ entryreserved/entry
+/row
+row
entrytokenMINUTE/token/entry
entrynon-reserved/entry
entryreserved/entry
*** ./doc/src/sgml/ref/select_into.sgml.orig 2004-07-08 21:15:47.767146592 +0200
--- ./doc/src/sgml/ref/select_into.sgml 2004-07-08 21:16:17.406640704 +0200
***
*** 27,33
[ WHERE replaceable class=PARAMETERcondition/replaceable ]
[ GROUP BY replaceable class=PARAMETERexpression/replaceable [, ...] ]
[ HAVING replaceable class=PARAMETERcondition/replaceable [, ...] ]
! [ { UNION | INTERSECT | EXCEPT } [ ALL ] replaceable class=PARAMETERselect/replaceable ]
[ ORDER BY replaceable class=PARAMETERexpression/replaceable [ ASC | DESC | USING replaceable class=PARAMETERoperator/replaceable ] [, ...] ]
[ LIMIT { replaceable class=PARAMETERcount/replaceable | ALL } ]
[ OFFSET replaceable class=PARAMETERstart/replaceable ]
--- 27,33
[ WHERE replaceable class=PARAMETERcondition/replaceable ]
[ GROUP BY replaceable class=PARAMETERexpression/replaceable [, ...] ]
[ HAVING replaceable class=PARAMETERcondition/replaceable [, ...] ]
! [ { UNION | INTERSECT | EXCEPT | MINUS} [ ALL ] replaceable class=PARAMETERselect/replaceable ]
[ ORDER BY replaceable class=PARAMETERexpression/replaceable [ ASC | DESC | USING replaceable class=PARAMETERoperator/replaceable ] [, ...] ]
[ LIMIT { replaceable class=PARAMETERcount/replaceable | ALL } ]
[ OFFSET replaceable class=PARAMETERstart/replaceable ]
*** ./doc/src/sgml/ref/select.sgml.orig 2004-07-08 21:12:47.145605240 +0200
--- ./doc/src/sgml/ref/select.sgml 2004-07-08 21:15:17.869691696 +0200
***
*** 26,32
[ WHERE replaceable class=parametercondition/replaceable ]
[ GROUP BY replaceable class=parameterexpression/replaceable [, ...] ]
[ HAVING replaceable class=parametercondition/replaceable [, ...] ]
! [ { UNION | INTERSECT | EXCEPT } [ ALL ] replaceable class=parameterselect/replaceable ]
[ ORDER BY replaceable class=parameterexpression/replaceable [ ASC | DESC | USING replaceable class=parameteroperator/replaceable ] [, ...] ]
[ LIMIT { replaceable class=parametercount/replaceable | ALL } ]
[ OFFSET replaceable class=parameterstart/replaceable ]
--- 26,32
[ WHERE replaceable class=parametercondition/replaceable ]
[ GROUP BY replaceable class=parameterexpression/replaceable [, ...] ]
[ HAVING replaceable class=parametercondition/replaceable [, ...] ]
! [ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL ] replaceable class=parameterselect/replaceable ]
[ ORDER BY replaceable class=parameterexpression/replaceable [ ASC | DESC | USING replaceable class=parameteroperator/replaceable ] [, ...] ]
[ LIMIT { replaceable class=parametercount/replaceable | ALL } ]
[ OFFSET replaceable class=parameterstart/replaceable ]
***
*** 92,99
literalINTERSECT/literal operator returns all rows that are
strictly in both result sets. The literalEXCEPT/literal
operator returns the rows that are in the first result set but
! not in the second. In all three cases, duplicate rows are
! eliminated unless literalALL/literal is specified. (See
xref linkend=sql-union endterm=sql-union-title, xref
linkend=sql-intersect endterm=sql-intersect-title, and
xref linkend=sql-except endterm=sql-except-title below.)
--- 92,102
literalINTERSECT/literal operator returns all rows that are
strictly in both result sets. The literalEXCEPT/literal
operator returns the rows that are in the first result set but
! not in the second. literalMINUS/literal behaves the same way
! as literalEXCEPT/literal. literalMINUS/literal should
! only be used for Oracle compatibility. In all three cases,
! duplicate rows are eliminated unless literalALL/literal
! is specified. (See
xref linkend=sql-union endterm=sql-union-title, xref
linkend=sql-intersect endterm=sql-intersect-title, and
xref linkend=sql-except
Re: [PATCHES] [HACKERS] Initdb error
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Magnus Hagander Sent: 06 July 2004 21:46 Cc: [EMAIL PROTECTED] Subject: [HACKERS] Initdb error Hi! I'm getting this error whenever I try to change the superuser of my database to anything != postgres. The same error shows up if I do -U someuser on Unix and someuser does not exist in /etc/passwd. Probably the same reason - since win32 will not have any users in /etc/passwd.. It works on 7.4, so probably related to the initdb-in-C rewrite? The attached patch fixes this problem. Regards, Dave. initdb.patch Description: initdb.patch ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Digital Mars C++ - Clients
Bruce Momjian [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Walter wrote: Bruce Momjian [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Uh, I have never heard of Digital Mars C++. I see it here though: http://www.digitalmars.com/ Having never had anyone who uses it, it seems this patch would be better kept where users of digital Mars could find it, like on their ftp server. I don't think there enough usage of Digital Mars for us to adjust our source code at this time. Do you agree? Check out the www.download.com download statistics for DMC++, which is just one portal one can download it from: http://www.download.com/sort/3150-2069-0-1-5.html? Looks good, but we have had no requests for it prior to this, and your additions aren't trivial. You also mentioned it doesn't have popen, meaning it might still be in a state of flux, meaning we would have to continue adjusting our patch --- for a compiler no one has asked about. Hans Oesterholt-Dijkema, who wrote the patch, is not affiliated with Digital Mars. But it's been my experience that any requests for a feature tend to be a tiny tip of the iceberg, 99% will just silently walk away from your product if it doesn't do X. And you *do* have a request, from someone who obviously cared enough to write a non-trivial patch for your product. Other products, such as wxWindows, have seen quite a lot of Digital Mars users embrace it once it started supporting DMC++. -Walter www.digitalmars.com free C/C++/D compilers ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] Updated ALTER TABLE ... SET TABLESPACE patch
Attached is an updated ALTER TABLE ... SET TABLESPACE patch. It uses the block by block copy mechanism proposed by Tom and handles i) ALTER TABLE index and ii) Copying of TOAST tables and the TOAST table's index. It doesn't handle copying of system tables (pg_largeobject) and, in the interests of code reuse, the patch fiddles with the code used by CLUSTER. This isn't great but I wanted to get a patch in before 1 July since I think the feature is very important -- even for the first release. Does this patch allow setting the tablespace of sequences as well? If so, then you will need to modify pg_dump of SERIAL sequences. Perhaps output a ALTER TABLE/SET TABLESPACE command after the CREATE TABLE definition to move the SERIAL sequence. The same argument applies if it allows moving indexes. (Unique and Primary Keys) Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Digital Mars C++ - Clients
Other products, such as wxWindows, have seen quite a lot of Digital Mars users embrace it once it started supporting DMC++. That is an extremely poor allegory. wxWindows is a GUI TOOLKIT. PostgreSQL is an independent database server. 99.999% of our windows installations will be from the binary that we will be distributing. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
