Re: [HACKERS] We are not following the spec for HAVING without GROUP BY
What that means is that neither the HAVING clause nor the targetlist can use any ungrouped columns except within aggregate calls; that is, select col from tab having 21 Informix: select tabname from systables having 2 1; 294: The column (tabname) must be in the GROUP BY list. select tabname from systables group by 1 having 2 1; all rows returned select tabname from systables group by 1 having 1 2; no rows found Andreas ---(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] Runtime accepting build discrepancies
Thomas, I worked on this and created some interface for decoupling java datatypes and their representations. In my implementation the mapping is N:N, so it is not directly applicable to your schema, but perhaps you can use some piece of it. I am not ready with all default data types, but the most important types are ready. http://cvs.plj.codehaus.org/pl-j/src/interfaces/org/pgj/typemapping/ Also, on stored procedure javadoc tags, could you take a look at this link: http://docs.codehaus.org/display/PLJ/Developer+tools I am really interersted in your opinion. Ragards, Laszlo On Thu, 10 Mar 2005, Thomas Hallgren wrote: Tom Lane wrote: Why is PL/Java dependent on the internal representation of any particular datatype? Seems like this is a symptom of bad PL design more than anything else. I didn't see any other way of doing it short of using string conversions. That doesn't seem very optimal. Java's internal representation of time is millisecs so I have code in place that looks like this (t in this case is a TimeADT): #ifdef HAVE_INT64_Time mSecs = t / 1000;/* Convert to millisecs */ if(tzAdjust) mSecs += Timestamp_getCurrentTimeZone() * 1000;/* Adjust from local time to UTC */ #else if(tzAdjust) t += Timestamp_getCurrentTimeZone();/* Adjust from local time to UTC */ t *= 1000.0;/* Convert to millisecs */ mSecs = (jlong)floor(t); #endif I'm of course interested in improving it. Especially if you consider this bad PL design. What do you suggest I do instead? The dynamic loader doesn't detect this and I bet there's a ton of combinations that will link just fine but perhaps crash (badly) in runtime. I would like to detect discrepancies like this during runtime somehow. I feel that it's either that or stop providing pre-built binaries altogether. I realize that I can't be the only one with this problem. How is this normally handled? If you want you can look into pg_control to see how the database is set up. That would cover this. Thanks (I'd still appreciate an alternative suggestion on the above though). Regards, Thomas Hallgren ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go 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] Runtime accepting build discrepancies
Laszlo, I worked on this and created some interface for decoupling java datatypes and their representations. In my implementation the mapping is N:N, so it is not directly applicable to your schema, but perhaps you can use some piece of it. I am not ready with all default data types, but the most important types are ready. http://cvs.plj.codehaus.org/pl-j/src/interfaces/org/pgj/typemapping/ I can't find anything in your typemapping package that would solve this problem. I'm faced with coercing Datum instances returned by SPI_getbinval in the server into their Java correspondance where the binary representation will vary depending on how PostgreSQL is compiled. Let's assume I have a TIMETZOID as the type. I must then use the following calls to get the time. TimeTzADT* tza = DatumGetTimeTzADTP(arg); TimeADT t = tza-time + tza-zone; /* Convert to UTC */ The catch is that depending on the setting of macro HAVE_INT64_TIMESTAMP the TimeADT will be a typedef for either an int64 or a double. In case of int64 the representation is in microsecs but if it's a double the value is seconds (with fractions of course). If PL/Java is compiled with a different setting of this macro, it will think a double representing seconds is an int64 containing millisecs or vice versa. The solution is probably to make PL/Java insensitive to this macro and instead consult the GUC variable integer_datetimes and use my own variations of TimeTzADT and TimeADT. How do PL/J address this problem? - thomas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] TODO item: support triggers on columns
Hello, I'd like to start working on the following TODO item: Referential Integrity / Support triggers on columns Is somebody else already working on this? Bye :) Chris. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [ADMIN] Too frequent warnings for wraparound failure
I wrote: Milen A. Radev [EMAIL PROTECTED] writes: I review the log every morning. In the beginning I got wraparound failure warnings every third day. But from a week I got those warnings every day. Well we have one table in one database where there are a lot of inserts, but not that many - around 30-40 thousand per day. Are you really doing half a billion transactions a day? I thought of another mechanism that wouldn't require such a preposterous load, only half a billion transactions since initdb. (How old is this installation, anyway, and which PG version?) If you are creating new databases every day and you do it by cloning template0, then the new databases would come into existence with datfrozenxid equal to template0's. Once template0 is more than half a billion transactions old, you'd start seeing the warning. This is relatively harmless, but probably we should try to mask it. We could make CREATE DATABASE set datfrozenxid to current time when cloning a database that has datallowconn false, on the assumption that the source DB is entirely frozen and so there's nothing to vacuum yet. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] We are not following the spec for HAVING without GROUP BY
I wrote: This is quite clear that the output of a HAVING clause is a grouped table no matter whether the query uses GROUP BY or aggregates or not. What that means is that neither the HAVING clause nor the targetlist can use any ungrouped columns except within aggregate calls; that is, select col from tab having 21 is in fact illegal per SQL spec, because col isn't a grouping column (there are no grouping columns in this query). Actually, it's even more than that: a query with HAVING and no GROUP BY should always return 1 row (if the HAVING succeeds) or 0 rows (if not). If there are no aggregates, the entire from/where clause can be thrown away, because it can have no impact on the result! Would those of you with access to other DBMSes try this: create table tab (col integer); select 1 from tab having 1=0; select 1 from tab having 1=1; insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; select 1 from tab having 1=1; I claim that a SQL-conformant database will return 0, 1, 0, and 1 rows from the 4 selects --- that is, the contents of tab make no difference at all. (MySQL returns 0, 0, 0, and 2 rows, so they are definitely copying our mistake...) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Raw size
Hi, i have a table: create table triples( att0 varchar(1000), att1 int4, att2 varchar(20), att3 varchar(1000) ) My table has 990 raws. The (possibly wrong) way, with wich i compute the size of the table is: att0: 1000 * 1 Byte + 4 = 1004 Bytes att2: 20 * 1 Byte + 4 = 24 Bytes att3: 1000 * 1 Byte + 4 = 1004 2032 Bytes + 40 (for oid) = 2072 Bytes 990 * 2072 = 2,051,280 Bytes BUT after clustering triples according to an index on att1: select relname, relpages from pg_class ; relname | relpages -+-- triples | 142 (8KB/buffer) 142 * 8 * 1024 = 1,163,264 Bytes Is there any compression or what? ---(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: [HACKERS] We are not following the spec for HAVING without GR
Title: RE: [HACKERS] We are not following the spec for HAVING without GROUP BY Would those of you with access to other DBMSes try this: snip Results for Microsoft SQL Server 2000 - 8.00.944 (Intel X86): --- (0 row(s) affected) --- 1 (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) --- (0 row(s) affected) --- 1 (1 row(s) affected) So it looks like MS is following the standard.
Re: [HACKERS] Raw size
Title: RE: [HACKERS] Raw size
990 * 2072 = 2,051,280 Bytes
BUT after clustering triples according to an index on att1:
snip
142 * 8 * 1024 = 1,163,264 Bytes
Is there any compression or what?
varchar means 'character varying'. What varies is the length. So a varchar(1000) with 'foo' in it only takes a few bytes ('foo' plus length info) instead of 1000 bytes.
If you really want a fixed-length field, nchar or char should do what you want.
Re: [HACKERS] We are not following the spec for HAVING without GROUP
Tom Lane wrote: Would those of you with access to other DBMSes try this: create table tab (col integer); select 1 from tab having 1=0; select 1 from tab having 1=1; insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; select 1 from tab having 1=1; I claim that a SQL-conformant database will return 0, 1, 0, and 1 rows from the 4 selects --- that is, the contents of tab make no difference at all. (MySQL returns 0, 0, 0, and 2 rows, so they are definitely copying our mistake...) regards, tom lane From SQL server 2000 with a service pack, I get: zero rows from the first query (having 1=0); one row, col value 1, from second query (having 1=1); ...run inserts... zero rows from the third query (having 1=0); one row, col value 1, from forth query (having 1=1); K. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] We are not following the spec for HAVING without GROUP BY
On Oracle 9.2 you get 0, 0, 0, and 2 rows. --Barry SQL create table tab (col integer); Table created. SQL select 1 from tab having 1=0; no rows selected SQL select 1 from tab having 1=1; no rows selected SQL insert into tab values (1); 1 row created. SQL insert into tab values (2); 1 row created. SQL select 1 from tab having 1=0; no rows selected SQL select 1 from tab having 1=1; 1 -- 1 1 SQL exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production JServer Release 9.2.0.1.0 - Production -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Thursday, March 10, 2005 9:45 AM To: pgsql-hackers@postgresql.org; pgsql-bugs@postgresql.org Subject: Re: [HACKERS] We are not following the spec for HAVING without GROUP BY I wrote: This is quite clear that the output of a HAVING clause is a grouped table no matter whether the query uses GROUP BY or aggregates or not. What that means is that neither the HAVING clause nor the targetlist can use any ungrouped columns except within aggregate calls; that is, select col from tab having 21 is in fact illegal per SQL spec, because col isn't a grouping column (there are no grouping columns in this query). Actually, it's even more than that: a query with HAVING and no GROUP BY should always return 1 row (if the HAVING succeeds) or 0 rows (if not). If there are no aggregates, the entire from/where clause can be thrown away, because it can have no impact on the result! Would those of you with access to other DBMSes try this: create table tab (col integer); select 1 from tab having 1=0; select 1 from tab having 1=1; insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; select 1 from tab having 1=1; I claim that a SQL-conformant database will return 0, 1, 0, and 1 rows from the 4 selects --- that is, the contents of tab make no difference at all. (MySQL returns 0, 0, 0, and 2 rows, so they are definitely copying our mistake...) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] [HACKERS] We are not following the spec for HAVING without
Tom Lane wrote: Would those of you with access to other DBMSes try this: create table tab (col integer); select 1 from tab having 1=0; select 1 from tab having 1=1; insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; select 1 from tab having 1=1; I claim that a SQL-conformant database will return 0, 1, 0, and 1 rows Not that this means much, but I'll mention it for the sake of completeness: SQLite 3.0.8 disallows all of the above SELECT statements: sqlite create table tab (col integer); sqlite select 1 from tab having 1=0; SQL error: a GROUP BY clause is required before HAVING sqlite select 1 from tab having 1=1; SQL error: a GROUP BY clause is required before HAVING sqlite insert into tab values(1); sqlite insert into tab values(2); sqlite select 1 from tab having 1=0; SQL error: a GROUP BY clause is required before HAVING sqlite select 1 from tab having 1=1; SQL error: a GROUP BY clause is required before HAVING -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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: [pgsql-hackers-win32] [HACKERS] snprintf causes regression tests to fail
On Wed, 9 Mar 2005 22:51:27 -0500 (EST), Bruce Momjian pgman@candle.pha.pa.us wrote: What do you think about it? Shall I abandon FreeBSD and go ahead Incorporating Trio? Yes, maybe just add the proper %$ handling from Trio to what we have now. Adding proper %$ from Trio will require too much effort. I would rather not do it. Not because I am lazy but because: 1) Trio team seem to be very serious about standards, update the library as soon as new standards come out: quote Trio fully implements the C99 (ISO/IEC 9899:1999) and UNIX98 (the Single Unix Specification, Version 2) standards, as well as many features from other implementations, e.g. the GNU libc and BSD4. /quote 2) If we integrate the whole library in source code we will not have to maintain it and will rely on Trio team for bug fixes and updates. Integrating it will be very easy since all of the functions begin with trio_. I used it instead of the src/port/snrpintf.c one and it passes regression tests under Win32 just fine. The downside is that Trio library is rather big. It is 3 .c and 6 .h files totalling 11556 lines. Compiled it is 71224 bytes not stripped and 56204 bytes stripped on Solaris 10 for x86, 32-bit. Even for a shared library it will probably be too much. Trio has a lot of string handling functions which are probably not necessary. Would you like me to try to remove everything unnecessary from it or we will settle with the full version? Regards, Nicolai Tufar ---(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
[HACKERS] PostgreSQL pam ldap document
All, I visited #postgresql @ FreeNode and asked about how to make pg use pam about a week ago (specifically I wanted to auth against LDAP). I was told to figure it out and write a doc... Here is my attempt at doing so: http://itc.musc.edu/wiki/PostGreSQL Please review for accuracy and/or proofreading. Thanks, Adrian ---(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] We are not following the spec for HAVING without GROUP BY
On Thu, Mar 10, 2005 at 12:44:50PM -0500, Tom Lane wrote: Would those of you with access to other DBMSes try this: DB2/LINUX 8.1.6 create table tab (col integer); select 1 from tab having 1=0; 1 --- 0 record(s) selected. select 1 from tab having 1=1; 1 --- 1 1 record(s) selected. insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; 1 --- 0 record(s) selected. select 1 from tab having 1=1; 1 --- 1 1 record(s) selected. -joh ---(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: [HACKERS] We are not following the spec for HAVING without GROUP BY
On Thu, 10 Mar 2005 12:44:50 -0500, Tom Lane [EMAIL PROTECTED] wrote: Would those of you with access to other DBMSes try this: On informix 9.21.UC4 create table tab (col integer); select 1 from tab having 1=0; returns no rows select 1 from tab having 1=1; returns no rows insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; returns no rows select 1 from tab having 1=1; returns 2 rows regards, Jaime Casanova ---(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: [pgsql-hackers-win32] [HACKERS] snprintf causes regression tests
Nicolai Tufar wrote: On Wed, 9 Mar 2005 22:51:27 -0500 (EST), Bruce Momjian pgman@candle.pha.pa.us wrote: What do you think about it? Shall I abandon FreeBSD and go ahead Incorporating Trio? Yes, maybe just add the proper %$ handling from Trio to what we have now. Adding proper %$ from Trio will require too much effort. I would rather not do it. Not because I am lazy but because: 1) Trio team seem to be very serious about standards, update the library as soon as new standards come out: quote Trio fully implements the C99 (ISO/IEC 9899:1999) and UNIX98 (the Single Unix Specification, Version 2) standards, as well as many features from other implementations, e.g. the GNU libc and BSD4. /quote 2) If we integrate the whole library in source code we will not have to maintain it and will rely on Trio team for bug fixes and updates. Integrating it will be very easy since all of the functions begin with trio_. I used it instead of the src/port/snrpintf.c one and it passes regression tests under Win32 just fine. The downside is that Trio library is rather big. It is 3 .c and 6 .h files totalling 11556 lines. Compiled it is 71224 bytes not stripped and 56204 bytes stripped on Solaris 10 for x86, 32-bit. Even for a shared library it will probably be too much. Trio has a lot of string handling functions which are probably not necessary. Would you like me to try to remove everything unnecessary from it or we will settle with the full version? Please see my posting about using a macro for snprintf. If the current implementation of snprintf is enough for our existing translation users we probably don't need to add anything more to it because snprintf will not be exported to client applications. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Raw size
On Thu, 10 Mar 2005 20:07:13 +0200 (EET), Ioannis Theoharis [EMAIL PROTECTED] wrote: Hi, i have a table: create table triples( att0 varchar(1000), att1 int4, att2 varchar(20), att3 varchar(1000) ) My table has 990 raws. The (possibly wrong) way, with wich i compute the size of the table is: att0: 1000 * 1 Byte + 4 = 1004 Bytes i don't know what the varchar size is in byte but i think is not 1 per character. IIRC, it varies on diferent encodings. regards, Jaime Casanova ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [pgsql-hackers-win32] Repleacement for src/port/snprintf.c
Would you please check current CVS? I think I addressed most of these issues already. --- Nicolai Tufar wrote: On Mon, Feb 21, 2005 at 10:53:08PM -0500, Bruce Momjian wrote: Applied. Thanks a lot. The patch attached solves the tread safety problem. Please review it before applying, I am not sure I am doing the right thing On Tue, 22 Feb 2005 19:57:15 +0100, Kurt Roeckx [EMAIL PROTECTED] wrote: The configure test is a little broken. It needs to quote the $'s. I've rewritten the test a little. This one needs applying too. $'s do get scrambled. Best regards, Nicolai. [ Attachment, skipping... ] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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
Re: [HACKERS] We are not following the spec for HAVING without GROUP
Tom Lane wrote: Would those of you with access to other DBMSes try this: create table tab (col integer); select 1 from tab having 1=0; select 1 from tab having 1=1; insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; select 1 from tab having 1=1; I claim that a SQL-conformant database will return 0, 1, 0, and 1 rows from the 4 selects --- that is, the contents of tab make no difference at all. (MySQL returns 0, 0, 0, and 2 rows, so they are definitely copying our mistake...) Firebird 1.5.1 FreeBSD 5.3 Database: test SQL drop table tab; SQL create table tab (col integer); SQL select 1 from tab having 1=0; SQL select 1 from tab having 1=1; 1 SQL insert into tab values(1); SQL insert into tab values(2); SQL select 1 from tab having 1=0; SQL select 1 from tab having 1=1; 1 SQL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [pgsql-hackers-win32] [HACKERS] snprintf causes regression tests
On Thu, 10 Mar 2005 16:26:47 -0500 (EST), Bruce Momjian pgman@candle.pha.pa.us wrote: Please see my posting about using a macro for snprintf. If the current implementation of snprintf is enough for our existing translation users we probably don't need to add anything more to it because snprintf will not be exported to client applications. Oh, Bruce. It will be the best solution. I was worried about the problems with my modifications to snprintf.c Tom Lane pointed out. But if we really separate snprintf() used by messages and snprintf() used by the like of src/backend/utils/adt/int8.c then we are safe. We can claim current release safe and I will modify src/port/snprintf.c at my leisure later. I will try out your modifications tomorrow. It is late here and I have a PostgreSQL class to to teach tomorrow ;) I still think that it is more convenient to rip off current implementation of snprintf.c and replace it with a very much stripped down of Trio's one. I will work on it and try to get a patch in one week's time. Thank you all for your patience. Best regards, Nicolai Tufar -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] We are not following the spec for HAVING without GROUP BY
Barry Lind [EMAIL PROTECTED] writes: On Oracle 9.2 you get 0, 0, 0, and 2 rows. Really!? Well, we always knew they were a bit standards-challenged ;-). I have more faith in DB2 being an accurate implementation of the spec. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Runtime accepting build discrepancies
Thomas Hallgren wrote: Laszlo, I worked on this and created some interface for decoupling java datatypes and their representations. In my implementation the mapping is N:N, so it is not directly applicable to your schema, but perhaps you can use some piece of it. I am not ready with all default data types, but the most important types are ready. http://cvs.plj.codehaus.org/pl-j/src/interfaces/org/pgj/typemapping/ I can't find anything in your typemapping package that would solve this problem. I'm faced with coercing Datum instances returned by SPI_getbinval in the server into their Java correspondance where the binary representation will vary depending on how PostgreSQL is compiled. IMHO this is why decoupling is good and neccesary. If one configures the RDBMS to use different another of data, then I simply replace a couple of lines in the data mapping configuration. In the case of custom datatypes in PostgreSQL, the same happens. This is no code modification nor recomplitation in PL-J, only a reconfiguration. This is why I have sent that link, but this configuration file fragment may explain it better: typemapper map type db=timestamp class=org.pgj.typemapping.postgres.PGTimestamp/ !-- type db=timestamp class=org.pgj.typemapping.postgres.PGTimestampINT64/ -- Let's assume I have a TIMETZOID as the type. I must then use the following calls to get the time. TimeTzADT* tza = DatumGetTimeTzADTP(arg); TimeADT t = tza-time + tza-zone; /* Convert to UTC */ The catch is that depending on the setting of macro HAVE_INT64_TIMESTAMP the TimeADT will be a typedef for either an int64 or a double. In case of int64 the representation is in microsecs but if it's a double the value is seconds (with fractions of course). If PL/Java is compiled with a different setting of this macro, it will think a double representing seconds is an int64 containing millisecs or vice versa. The solution is probably to make PL/Java insensitive to this macro and instead consult the GUC variable integer_datetimes and use my own variations of TimeTzADT and TimeADT. How do PL/J address this problem? - thomas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Regards, László Hornyák software developer begin:vcard fn;quoted-printable:L=C3=A1szl=C3=B3 Horny=C3=A1k n;quoted-printable;quoted-printable:Horny=C3=A1k;L=C3=A1szl=C3=B3 adr;dom:;;;Budapest email;internet:[EMAIL PROTECTED] title:Software developer tel;cell:+36-70-368-39-01 x-mozilla-html:FALSE url:http://www.codehaus.org/~kocka/ version:2.1 end:vcard ---(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: [pgsql-hackers-win32] [HACKERS] snprintf causes regression tests to fail
Bruce Momjian pgman@candle.pha.pa.us writes: Please see my posting about using a macro for snprintf. If the current implementation of snprintf is enough for our existing translation users we probably don't need to add anything more to it because snprintf will not be exported to client applications. The CVS-tip implementation is fundamentally broken and won't work even for our internal uses. I've not wasted time complaining about it because I thought we were going to replace it. If we can't find a usable replacement then we're going to have to put a lot of effort into fixing what's there. On the whole I think the effort would be better spent importing someone else's solution. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [pgsql-hackers-win32] [HACKERS] snprintf causes regression tests
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Please see my posting about using a macro for snprintf. If the current implementation of snprintf is enough for our existing translation users we probably don't need to add anything more to it because snprintf will not be exported to client applications. The CVS-tip implementation is fundamentally broken and won't work even for our internal uses. I've not wasted time complaining about it because I thought we were going to replace it. If we can't find a usable replacement then we're going to have to put a lot of effort into fixing what's there. On the whole I think the effort would be better spent importing someone else's solution. Oh, so our existing implementation doesn't even meet our needs. OK. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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 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] Runtime accepting build discrepancies
Laszlo Hornyak wrote: IMHO this is why decoupling is good and neccesary. If one configures the RDBMS to use different another of data, then I simply replace a couple of lines in the data mapping configuration. In the case of custom datatypes in PostgreSQL, the same happens. This is no code modification nor recomplitation in PL-J, only a reconfiguration. This is why I have sent that link, but this configuration file fragment may explain it better: typemapper map type db=timestamp class=org.pgj.typemapping.postgres.PGTimestamp/ !-- type db=timestamp class=org.pgj.typemapping.postgres.PGTimestampINT64/ -- Sure Laszlo. That solves everything. But where do you get the information on what to comment out and what to use in the first place? Regards, Thomas Hallgren ---(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: [HACKERS] Raw size
BUT after clustering triples according to an index on att1: select relname, relpages from pg_class ; relname | relpages -+-- triples | 142 (8KB/buffer) 142 * 8 * 1024 = 1,163,264 Bytes Is there any compression or what? Yes, there is: http://www.postgresql.org/docs/8.0/interactive/storage-toast.html Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [pgsql-hackers-win32] [HACKERS] snprintf causes regression
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Please see my posting about using a macro for snprintf. If the current implementation of snprintf is enough for our existing translation users we probably don't need to add anything more to it because snprintf will not be exported to client applications. The CVS-tip implementation is fundamentally broken and won't work even for our internal uses. I've not wasted time complaining about it because I thought we were going to replace it. If we can't find a usable replacement then we're going to have to put a lot of effort into fixing what's there. On the whole I think the effort would be better spent importing someone else's solution. Oh, so our existing implementation doesn't even meet our needs. OK. Wasn't the issue about odd behavior of the Win32 linker choosing the wrong vnsprintf? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] fool-toleranced optimizer
Greg Stark wrote: Kevin Brown [EMAIL PROTECTED] writes: Hence, it makes sense to go ahead and run the query, but issue a warning at the very beginning, e.g. WARNING: query JOINs tables list of tables without otherwise referencing or making use of those tables. This may cause excessively poor performance of the query. Well the problem with a warning is what if it *is* intentional? It's not ok to fill my logs up with warnings for every time the query is executed. That just forces me to turn off warnings. WARNING is probably the wrong level (I wasn't thinking in terms of PG logging, though I probably should have been). What about NOTICE? Basically, you want something that will alert the interactive user that what they're doing is likely to be stupid, but at the same time won't be a burden on the system or the DBA... It would be ok to have an option to block cartesian joins entirely. I might even choose to run with that enabled normally. I can always disable it for queries I know need cartesion joins. Which wouldn't work all that well for people who are trying to write their software in a reasonably portable fashion, unfortunately. However, the number of people who care would now be much smaller. For that matter, I wonder whether it's time to consider an option to disable implicit (ie, pre-ansi join syntax) joins entirely. It seems like lots of shops are likely imposing coding standards that require ansi join syntax anyways. In environments like that you would expect a CROSS JOIN b not just select * from a,b anyways. Shops like that might appreciate the ability to enforce a blanket coding standard on that point and get protection from accidental cartesian joins as a side benefit. That could be handy, but of course it should default to off, which with respect to cross joins would unfortunately wind up benefitting only those people who already are potentially aware of the issue and care about it (or, at least, those people who have DBAs that care about it). -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] SQL99 Hierarchical queries
Hi Evgen, How's the syncing with HEAD going? Cheers, Chris Evgen Potemkin wrote: Ok, I'm started porting it to 8.0.1 and will fix this also. By the way, did you know any test suit for such queries? To make some regression test. Regards, Evgen I tested you patch, and it's good work. I would all methods in PostgreSQL. I found query which kill backand WITH t AS ( SELECT 0::int AS i UNION ALL SELECT i + 1 FROM t WHERE i 100) SELECT * FROM t; Regards Pavel Stehule ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [pgsql-hackers-win32] [HACKERS] snprintf causes regression tests
[EMAIL PROTECTED] writes: Please see my posting about using a macro for snprintf. Wasn't the issue about odd behavior of the Win32 linker choosing the wrong vnsprintf? You're right, the point about the macro was to avoid linker weirdness on Windows. We need to do that part in any case. I think Bruce confused that issue with the one about whether our version supported %n$ adequately ... which it doesn't just yet ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [pgsql-hackers-win32] [HACKERS] snprintf causes regression
[EMAIL PROTECTED] wrote: Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Please see my posting about using a macro for snprintf. If the current implementation of snprintf is enough for our existing translation users we probably don't need to add anything more to it because snprintf will not be exported to client applications. The CVS-tip implementation is fundamentally broken and won't work even for our internal uses. I've not wasted time complaining about it because I thought we were going to replace it. If we can't find a usable replacement then we're going to have to put a lot of effort into fixing what's there. On the whole I think the effort would be better spent importing someone else's solution. Oh, so our existing implementation doesn't even meet our needs. OK. Wasn't the issue about odd behavior of the Win32 linker choosing the wrong vnsprintf? Ah, but with my new patch to be applied tomorrow to use macros and rename to pg_snprintf there no longer is any conflict with the system versions of these functions. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [pgsql-hackers-win32] [HACKERS] snprintf causes regression tests to fail
Tom Lane wrote: The CVS-tip implementation is fundamentally broken and won't work even for our internal uses. I've not wasted time complaining about it because I thought we were going to replace it. If we can't find a usable replacement then we're going to have to put a lot of effort into fixing what's there. On the whole I think the effort would be better spent importing someone else's solution. Bruce Momjian wrote: Oh, so our existing implementation doesn't even meet our needs. OK. Very well, I too, tend to think that importing some else's solution is the way to go. Tom, have you looked at Trio? If it is fine with you too, I will strip it to the bare minimum needed for snprintf(), vsnprintf() and printf() by Saturday. Regards, Nicolai Tufar ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [pgsql-hackers-win32] [HACKERS] snprintf causes regression tests to fail
Nicolai Tufar [EMAIL PROTECTED] writes: Very well, I too, tend to think that importing some else's solution is the way to go. Tom, have you looked at Trio? I have not seen it ... but if it looks reasonable to you, have a go at it. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [pgsql-hackers-win32] [HACKERS] snprintf causes regression tests to fail
On Fri, 11 Mar 2005 01:14:31 -0500, Tom Lane wrote: Nicolai Tufar [EMAIL PROTECTED] writes: Very well, I too, tend to think that importing some else's solution is the way to go. Tom, have you looked at Trio? I have not seen it ... but if it looks reasonable to you, have a go at it. It looks reasonable to me. It claims to: fully implement C99 (ISO/IEC 9899:1999) and UNIX98 (the Single Unix Specification, Version 2) standards, as well as many features from other implementations, e.g. the GNU libc and BSD4. I compiled and run regression tests with it used instead of our current implementation and it worked fine under win32 and Solaris x86. The only problem is that it is 11000 lines as oposed to our curent implementation's 600. I will remove everything unnecessary and submit a patch for consideration. Regards, Nicolai Tufar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
