AW: AW: AW: [HACKERS] Re: tinterval - operator problems on AIX
Yes, the annoyance is, that localtime works for dates before 1970 but mktime doesn't. Best would probably be to assume no DST before 1970 on AIX and IRIX. That seems like a reasonable answer to me, especially since we have other platforms that behave that way. How can we do this --- just test for isdst = -1 after the call, and assume that means failure? Are you working on this, or can you point me to the parts of the code, that would need change ? Andreas
AW: [HACKERS] Re: Performance degradation in PostgreSQL 7.1beta3 vs 6.5.3
More importantly, PostgreSQL 6.5.3 works very, very well without VACUUM'ing. 6.5 effectively assumes that "foo = constant" will select exactly one row, if it has no statistics to prove otherwise. I thought we had agreed upon a default that would still use the index in the above case when no statistics are present. Wasn't it something like a 5% estimate ? I did check that behavior, since I was very concerned about that issue. Now, what is so different in his case? Andreas
AW: AW: AW: [HACKERS] Re: tinterval - operator problems on AIX
Yes, the annoyance is, that localtime works for dates before 1970 but mktime doesn't. Best would probably be to assume no DST before 1970 on AIX and IRIX. That seems like a reasonable answer to me, especially since we have other platforms that behave that way. How can we do this --- just test for isdst = -1 after the call, and assume that means failure? Are you working on this, or can you point me to the parts of the code, that would need change ? Here is a patch that should make AIX and IRIX more happy. It changes all checks for tm_isdst to (tm_isdst 0) and fixes the expected horology file for AIX. I just now realized, that the new expected file (while still bogous) is more correct than the old one. Thanks to Tom for mentioning that the hour should stay the same when subtracting days from a timestamp. Please apply. Andreas aixisdst.patch
[HACKERS] SIGNATURE for int sets (need advise)
Hi, after getting GiST works we're trying to use RD-Tree in our fulltext search application. We have universe of lexems (words in dictionaries) which is rather large, so we need some compression to effectively use RD-Tree. When we did index support for int arrays we compressed set by range sets but it's not applicable if cardinality of universe set is very high. We're thinking about algorithm of creating good signature for set of integers. This signature must follow several rules: 1). if set A is contained in set B, then sig(A) is also contained in sig(B) 2). if set C is a union of set A and set B, then sig(C) is union of sig(A) and sig(B) Also, signature should be good for effective tree construction (RD-Tree), i.e. it should be not degenerated for set size about 10^6 . We need 1) for search operation and 2) for tree contructing. Right now we implementing so-called "superimposed coding" technique (D. Knuth, vol.3) which is based on idea to hash attribute values into random k-bit codes in a b-bit field and to superimpose the codes for each attribute value in a record. This technique was proposed by Sven Helmer ("Index Structures for Databases Containing Data Items with Set-valued Attr ibutes",1997, Sven Helmer, paper is available from my gist page) to represent sets in the index structures. This technique is great because of fixed length and great speed of calculation (used only bit operations). It follows rules 1 and 2, but it's not good for big sets, because for internal nodes and especially for root (a union of sets) we get signature fully consisting of 1. We couldn't use arbitrarily long signature, because we have 8Kb limit of index page size. For signature of variable size length we don't know how to define 1) and 2) While we 're investigating the problem, I'd be glad to know some references, ideas. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Re: [HACKERS] renaming indices?
Alex Pilosov wrote: 3) index namespace should be constricted to the table on which it is indexed, since no commands to my knowledge manipulate the index without also specifying the table. How about DROP INDEX ... ? I'm not sure if this is standard SQL, maybe we should have ALTER TABLE ... DROP INDEX ... - Hannu
[HACKERS] Mysterious 7.0.3 error
Greetings! We have a script updating our database with thousands of entries on a daily basis. To speed up processing, we drop a consistency check trigger before the update and recreate it afterwards. Occasionally, we get the following, even though the database has no other live connections, and the trigger drop is the first statement: drop trigger rprices_insupdel on rprices; DROP ERROR: RelationClearRelation: relation 160298 modified while in use Any pointers most appreciated! -- Camm Maguire[EMAIL PROTECTED] == "The earth is but one country, and mankind its citizens." -- Baha'u'llah
Re: AW: [HACKERS] Re: Performance degradation in PostgreSQL 7.1beta3 vs 6.5.3
Zeugswetter Andreas SB [EMAIL PROTECTED] writes: More importantly, PostgreSQL 6.5.3 works very, very well without VACUUM'ing. 6.5 effectively assumes that "foo = constant" will select exactly one row, if it has no statistics to prove otherwise. I thought we had agreed upon a default that would still use the index in the above case when no statistics are present. Wasn't it something like a 5% estimate ? I did check that behavior, since I was very concerned about that issue. Now, what is so different in his case? The current estimate is 0.01 (1 percent). That seems sufficient to cause an indexscan on small to moderate-size tables, but apparently it is not small enough to do so for big tables. I have been thinking about decreasing the default estimate some more, maybe to 0.005. (The reason the table size matters even if you haven't done a VACUUM ANALYZE is that both plain VACUUM and CREATE INDEX will update the table-size stats. So the planner may know the correct table size but still have to rely on a default selectivity estimate. The cost functions are nonlinear, so what's "small enough" can depend on table size.) Bruce, if you'd like to experiment, try setting the attdispersion value in pg_attribute to various values, eg update pg_attribute set attdispersion = 0.005 where attname = 'foo' and attrelid = (select oid from pg_class where relname = 'bar'); Please report back on how small a number seems to be needed to cause indexscans on your tables. regards, tom lane
Re: AW: AW: AW: [HACKERS] Re: tinterval - operator problems on AIX
The correct thing to do instead of the #if defined (_AIX) would be to use something like #ifdef NO_NEGATIVE_MKTIME and set that with a configure. Thomas, are you volunteering ? Actually, I can volunteer to be supportive of your efforts ;) I'm traveling at the moment, and don't have the original thread(s) which describe in detail what we need to do for platforms I don't have. If Peter E. would be willing to do a configure test for this mktime() problem, then you or I can massage the actual code. Peter, is this something you could pick up? I do not have the original thread where Andreas describes the behavior of mktime() on his machine. Andreas, can you suggest a simple configure test to be used? - Thomas
Re: [HACKERS] Re: Performance degradation in PostgreSQL 7.1beta3 vs 6.5.3
"Robert E. Bruccoleri" wrote: explain select count(*) from comparisons_4 where code = 80003; NOTICE: QUERY PLAN: Aggregate (cost=15659.29..15659.29 rows=1 width=0) - Seq Scan on comparisons_4 (cost=0.00..15640.81 rows=7391 width=0) EXPLAIN What is the type of field "code" ? --- Hannu
Re: [HACKERS] Re: Performance degradation in PostgreSQL 7.1beta3 vs
Dear Hannu, "Robert E. Bruccoleri" wrote: explain select count(*) from comparisons_4 where code = 80003; NOTICE: QUERY PLAN: Aggregate (cost=15659.29..15659.29 rows=1 width=0) - Seq Scan on comparisons_4 (cost=0.00..15640.81 rows=7391 width=0) EXPLAIN What is the type of field "code" ? int4 Do you think that should make a difference? +--++ | Robert E. Bruccoleri, Ph.D. | Phone: 609 737 6383| | President, Congenomics, Inc. | Fax: 609 737 7528| | 114 W Franklin Ave, Suite K1,4,5 | email: [EMAIL PROTECTED]| | P.O. Box 314 | URL: http://www.congen.com/~bruc | | Pennington, NJ 08534 || +--++
AW: AW: AW: AW: [HACKERS] Re: tinterval - operator problems on AIX
The correct thing to do instead of the #if defined (_AIX) would be to use something like #ifdef NO_NEGATIVE_MKTIME and set that with a configure. Thomas, are you volunteering ? Actually, I can volunteer to be supportive of your efforts ;) I'm traveling at the moment, and don't have the original thread(s) which describe in detail what we need to do for platforms I don't have. If Peter E. would be willing to do a configure test for this mktime() problem, then you or I can massage the actual code. Peter, is this something you could pick up? I do not have the original thread where Andreas describes the behavior of mktime() on his machine. Andreas, can you suggest a simple configure test to be used? #include time.h int main() { struct tm tt, *tm=tt; int i = -5000; tm = localtime (i); i = mktime (tm); if (i != -5000) /* on AIX this check could also be (i == -1) */ { printf("ERROR: mktime(3) does not correctly support datetimes before 1970\n"); return(1); } } Andreas
Re: [HACKERS] Re: Performance degradation in PostgreSQL 7.1beta3 vs
"Robert E. Bruccoleri" wrote: Dear Hannu, "Robert E. Bruccoleri" wrote: explain select count(*) from comparisons_4 where code = 80003; NOTICE: QUERY PLAN: Aggregate (cost=15659.29..15659.29 rows=1 width=0) - Seq Scan on comparisons_4 (cost=0.00..15640.81 rows=7391 width=0) EXPLAIN What is the type of field "code" ? int4 Do you think that should make a difference? Probably not here. Sometimes it has made difference if the system does not recognize the other side of comparison (80003) as being of the same type as the index. what are the cost estimates when you run explain with seqscan disabled ? do = SET ENABLE_SEQSCAN TO OFF; see: (http://www.postgresql.org/devel-corner/docs/admin/runtime-config.htm#RUNTIME-CONFIG-OPTIMIZER) - Hannu
Re: [HACKERS] Re: tinterval - operator problems on AIX
The correct thing to do instead of the #if defined (_AIX) would be to use something like #ifdef NO_NEGATIVE_MKTIME and set that with a configure. ...Andreas, can you suggest a simple configure test to be used? #include time.h int main() { struct tm tt, *tm=tt; int i = -5000; tm = localtime (i); i = mktime (tm); if (i != -5000) /* on AIX this check could also be (i == -1) */ { printf("ERROR: mktime(3) does not correctly support datetimes before 1970\n"); return(1); } } On my Linux box, where the test passes, the compiler is happier if "i" is declared as time_t. Any problem on other platforms if we change this? - Thomas
Re: AW: AW: AW: AW: [HACKERS] Re: tinterval - operator problems onAI X
Zeugswetter Andreas SB writes: I do not have the original thread where Andreas describes the behavior of mktime() on his machine. Andreas, can you suggest a simple configure test to be used? #include time.h int main() { struct tm tt, *tm=tt; int i = -5000; tm = localtime (i); i = mktime (tm); if (i != -5000) /* on AIX this check could also be (i == -1) */ { printf("ERROR: mktime(3) does not correctly support datetimes before 1970\n"); return(1); } } You don't need to put this check into configure, you can just do the check after mktime() is used. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
RE: [HACKERS] ODBC Driver int8 Patch
-Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: 16 January 2001 16:50 To: Dave Page Cc: '[EMAIL PROTECTED]' Subject: Re: [HACKERS] ODBC Driver int8 Patch As I remember, the problem is that this makes us match the ODBC v2 spec, but then we would not match the v3 spec. Is that correct? Yes, the patch I supplied will make it correct for v2. As it stands it is correct for v3. However as the driver identifies itself as v2 (i believe now v2.5) compliant, ADO expects it to follow the v2 spec and then fails when it doesn't. The original problem was briefly discussed on the interfaces list under the thread '[INTERFACES] Problems with int8 and MS ADO/ODBC' Regards, Dave.
Re: [HACKERS] SIGTERM - elog(FATAL) - proc_exit() is probably a bad idea
"Mikheev, Vadim" [EMAIL PROTECTED] writes: It's very easy to don't notice ERROR - it's just transaction abort and transaction abort is normal thing, - but errors inside critical sections are *unexpected* things which mean that something totally wrong in code. Okay. That means we do need two kinds of critical sections, then, because the crit sections I've just sprinkled everywhere are not that critical ;-). They just want to hold off cancel/die interrupts. I'll take care of fixing what I broke, but does anyone have suggestions for good names for the two concepts? The best I could come up with offhand is BEGIN/END_CRIT_SECTION and BEGIN/END_SUPER_CRIT_SECTION, but I'm not pleased with that... Ideas? regards, tom lane
Re: [HACKERS] copy from stdin; bug?
Re On Tue, 16 Jan 2001, Tatsuo Ishii wrote: The encoding of your databases are all UNICODE. So you need to input data as UTF-8 in this case. I guess you are trying to input ISO-8859-1 encoded data that is the source of the problem. Here are possible solutions: 1) input data as UTF-8 :) 2) crete a new databse using encoidng LATIN1. createdb -E LATIN1... yes, this will be the sollution... 3) upgrade to 7.1 that has the capability to do an automatic conversion between UTF-8 and ISO-8859-1. i like to use deb packages and to use 7.1 i would have to upgrade to woody (or even sid)... thank you for your quick help!!! Udv tRehak E-Mail: Tom Rehak [EMAIL PROTECTED]
RE: [HACKERS] SIGTERM - elog(FATAL) - proc_exit() is probably a bad idea
Because I think turning an elog(ERROR) into a system-wide crash is not a good idea ;-). If you are correct that this behavior is necessary for WAL-related critical sections, then indeed we need two kinds of critical sections, one that just holds off cancel/die response and one that turns elog(ERROR) into a dangerous weapon. I'm going to wait and see Vadim's response before I do anything ... I've tried to move "dangerous" ops with non-zero probability of elog(ERROR) (eg new file block allocation) out of crit sections. Anyway we need in ERROR--STOP for safety when changes aren't logged. Vadim
Re: [HACKERS] Re: Performance degradation in PostgreSQL 7.1beta3 vs
Dear Hannu, "Robert E. Bruccoleri" wrote: Dear Hannu, "Robert E. Bruccoleri" wrote: explain select count(*) from comparisons_4 where code = 80003; NOTICE: QUERY PLAN: Aggregate (cost=15659.29..15659.29 rows=1 width=0) - Seq Scan on comparisons_4 (cost=0.00..15640.81 rows=7391 width=0) EXPLAIN What is the type of field "code" ? int4 Do you think that should make a difference? Probably not here. Sometimes it has made difference if the system does not recognize the other side of comparison (80003) as being of the same type as the index. what are the cost estimates when you run explain with seqscan disabled ? do = SET ENABLE_SEQSCAN TO OFF; see: (http://www.postgresql.org/devel-corner/docs/admin/runtime-config.htm#RUNTIME-CONFIG-OPTIMIZER) Here's the result from EXPLAIN: Aggregate (cost=19966.21..19966.21 rows=1 width=0) - Index Scan using comparisons_4_code on comparisons_4 (cost=0.00..19947.73 rows=7391 width=0) The estimates are too high. --Bob +--++ | Robert E. Bruccoleri, Ph.D. | Phone: 609 737 6383| | President, Congenomics, Inc. | Fax: 609 737 7528| | 114 W Franklin Ave, Suite K1,4,5 | email: [EMAIL PROTECTED]| | P.O. Box 314 | URL: http://www.congen.com/~bruc | | Pennington, NJ 08534 || +--++
Re: [HACKERS] SIGTERM - elog(FATAL) - proc_exit() is probably a bad idea
"Mikheev, Vadim" [EMAIL PROTECTED] writes: Because I think turning an elog(ERROR) into a system-wide crash is not a good idea ;-). If you are correct that this behavior is necessary for WAL-related critical sections, then indeed we need two kinds of critical sections, one that just holds off cancel/die response and one that turns elog(ERROR) into a dangerous weapon. I'm going to wait and see Vadim's response before I do anything ... I've tried to move "dangerous" ops with non-zero probability of elog(ERROR) (eg new file block allocation) out of crit sections. Anyway we need in ERROR--STOP for safety when changes aren't logged. Why is that safer than just treating an ERROR as an ERROR? It seems to me there's a real risk of a crash/restart loop if we force a restart whenever we see an xlog-related problem. regards, tom lane
RE: [HACKERS] DeadLockCheck is buggy
I have been studying DeadLockCheck for most of a day now, and I doubt that this is the only bug lurking in it. I think that we really ought to throw it away and start over, because it doesn't look to me at all like a standard deadlock-detection algorithm. The standard way of doing Go ahead. Throw away my code. *sniff* :-) And my changes from the days of 6.5 -:) Vadim
[HACKERS] Storing a binary file with Visual Basic and ADO
I am trying to store a binary file with Visual Basic 6.0 and ADO and I use the oid data type. The same code with Oracle and the clob type works but with PostgreSQL I receive an error saying "Multiple Step Operation generated errors. Check each status value.". I am using the ODBC drivers and I assign a String to the Oid but it fails. I have also tried with a bytes array and with variant data type but the same error happen. If I use Java and JDBC it works with byte array reading from a stream. Thank you.
Re: [HACKERS] Mysterious 7.0.3 error
Greetings, and thanks for your reply! Tom Lane [EMAIL PROTECTED] writes: Camm Maguire [EMAIL PROTECTED] writes: Greetings! We have a script updating our database with thousands of entries on a daily basis. To speed up processing, we drop a consistency check trigger before the update and recreate it afterwards. Occasionally, we get the following, even though the database has no other live connections, and the trigger drop is the first statement: drop trigger rprices_insupdel on rprices; DROP ERROR: RelationClearRelation: relation 160298 modified while in use Are you doing other schema changes (like other instances of this script) in parallel? Or vacuums of system tables? Those are the cases I recall that might trigger this problem. No, just one job, this job, at a time. Any pointers most appreciated! Live with it until 7.1 :-(. Will do. Thanks! regards, tom lane -- Camm Maguire[EMAIL PROTECTED] == "The earth is but one country, and mankind its citizens." -- Baha'u'llah
Re: [HACKERS] copy from stdin; bug?
On Wed, Jan 17, 2001 at 01:40:58AM +0100, Rehak Tamas wrote: 3) upgrade to 7.1 that has the capability to do an automatic conversion between UTF-8 and ISO-8859-1. i like to use deb packages and to use 7.1 i would have to upgrade to woody (or even sid)... Not true. There are Debian source packages, 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. (Apologies to the rest for the Debian jargon.) Nathan Myers [EMAIL PROTECTED]
Re: [HACKERS] Re: Performance degradation in PostgreSQL 7.1beta3 vs
"Robert E. Bruccoleri" wrote: what are the cost estimates when you run explain with seqscan disabled ? do = SET ENABLE_SEQSCAN TO OFF; see: (http://www.postgresql.org/devel-corner/docs/admin/runtime-config.htm#RUNTIME-CONFIG-OPTIMIZER) Here's the result from EXPLAIN: Aggregate (cost=19966.21..19966.21 rows=1 width=0) - Index Scan using comparisons_4_code on comparisons_4 (cost=0.00..19947.73 rows=7391 width=0) The estimates are too high. You could try experimenting with SET RANDOM_PAGE_COST TO x.x; from the page above RANDOM_PAGE_COST (floating point) Sets the query optimizer's estimate of the cost of a nonsequentially fetched disk page. this is measured as a multiple of the cost of a sequential page fetch. Note: Unfortunately, there is no well-defined method of determining ideal values for the family of "COST" variables that were just described. You are encouraged to experiment and share your findings. - Hannu
[HACKERS] Cursors in PL/pgSQL
Cursors are not supported in PL/pgSQL. I don't see a TODO item to fix this. Fixing the syntax to support cursors is easy. The problem then is that PL/pgSQL uses SPI, and SPI does not support cursors. In spi.c there is a bit of code for cursor support, with the comment /* Don't work currently */ Is adding cursor support to SPI a bad idea? Is adding cursor support to PL/pgSQL undesirable? Can anybody sketch the problems which would arise when adding cursor support to SPI? Thanks. Ian
Re: [HACKERS] $PGDATA/base/???
Wow, this looks great, and it worked the first time too. I will commit if no one makes objects. Is there a way to relate this to the names of the databases? Why the change? Or am I missing something key here.. See the thread on the renaming in the archives. In short, this is part of Vadim's work on WAL -- the new naming makes certain things easier for WAL. Utilities to relate the new names to the actual database/table names _do_ need to be written, however. The information exists in one of the system catalogs now -- it just has to be made accessible. Yes, I am hoping to write this utility before 7.1 final. Maybe it will have to be in /contrib. I just finished writing such an app. Take a look. It's in a format that can be put in /contrib. Let me know if you want any changes made, etc. Feel free to use any of the code you wish. http://www.crimelabs.net/postgresql.shtml - Brandon b. palmer, [EMAIL PROTECTED] pgp: www.crimelabs.net/bpalmer.pgp5 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] $PGDATA/base/???
On Wed, Jan 17, 2001 at 05:49:36PM -0500, Bruce Momjian wrote: Wow, this looks great, and it worked the first time too. I will commit if no one makes objects. I object. The code displays oids and tablenames or relnames. Oid is just the initial, default filename for tables, and may change to something other than the oid. Currently, the reindex code is the only place that could change the relfilenode without changing the oid, but I think there may be more in the future. Here's a patch to Brandon's code (completely untested, BTW): Ross *** oid2name.c.orig Wed Jan 17 17:12:05 2001 --- oid2name.c Wed Jan 17 17:27:11 2001 *** *** 331,339 /* don't exclude the systables if this is set */ if(systables == 1) ! sprintf(todo, "select oid,relname from pg_class order by relname"); else ! sprintf(todo, "select oid,relname from pg_class where relname not like 'pg_%%' order by relname"); sql_exec(conn, todo, NULL); } --- 331,339 /* don't exclude the systables if this is set */ if(systables == 1) ! sprintf(todo, "select relfilenode,relname from pg_class order by relname"); else ! sprintf(todo, "select relfilenode,relname from pg_class where relname not like 'pg_%%' order by relname"); sql_exec(conn, todo, NULL); } *** *** 348,354 todo = (char *) malloc (1024); /* get the oid and tablename where the name matches tablename */ ! sprintf(todo, "select oid,relname from pg_class where relname = '%s'", tablename); returnvalue = sql_exec(conn, todo, 1); --- 348,354 todo = (char *) malloc (1024); /* get the oid and tablename where the name matches tablename */ ! sprintf(todo, "select relfilenode,relname from pg_class where relname = '%s'", tablename); returnvalue = sql_exec(conn, todo, 1); *** *** 372,378 todo = (char *) malloc (1024); ! sprintf(todo, "select oid,relname from pg_class where oid = %i", oid); returnvalue = sql_exec(conn, todo, 1); --- 372,378 todo = (char *) malloc (1024); ! sprintf(todo, "select relfilenode,relname from pg_class where relfilenode = %i", oid); returnvalue = sql_exec(conn, todo, 1);
[HACKERS] Re: SIGTERM - elog(FATAL) - proc_exit() is probably a bad idea
I'll take care of fixing what I broke, but does anyone have suggestions for good names for the two concepts? The best I could come up with offhand is BEGIN/END_CRIT_SECTION and BEGIN/END_SUPER_CRIT_SECTION, but I'm not pleased with that... Ideas? Let CRITICAL be critical. If the other section are there just to be cautious. Then the name should represent that. While I like the BEGIN/END_OH_MY_GOD_IF_THIS_GETS_INTERRUPTED_YOU_DONT_WANT_TO_KNOW markers.. They are a little hard to work with. Possibly try demoting the NON_CRITICAL_SECTIONS to something like the following. BEGIN/END_CAUTION_SECTION, BEGIN/END_WATCH_SECTION
Re: [HACKERS] $PGDATA/base/???
I object. The code displays oids and tablenames or relnames. Oid is just the initial, default filename for tables, and may change to something other than the oid. Currently, the reindex code is the only place that could change the relfilenode without changing the oid, but I think there may be more in the future. Looks great, and I agree. Did not know that little piece of information. I have made the changed to my code, here's the new version. I have tested this one and updated the web page. - brandon b. palmer, [EMAIL PROTECTED] pgp: www.crimelabs.net/bpalmer.pgp5 oid2name-0.1.1.tar.gz
[HACKERS] Getting configure to notice link-time vs run-time failures
[EMAIL PROTECTED] writes: configure:4207: checking for inflate in -lz configure:4226: gcc -o conftest conftest.c -lz -lgen -lnsl -lsocket -ldl -lm -lreadline -ltermcap -lcurses 15 configure:4660: checking for crypt.h This doesn't tell me much. But I modified configure to exit right after this, without removing conftest*, and when I ran conftest it came back with the same message: typhoon ./conftest ld.so.1: ./conftest: fatal: libz.so: open failed: No such file or directory Killed It's strange that configure's check to see if zlib is linkable should succeed, only to have the live startup fail. This system is probaly badly misconfigured, but it would be great if configure could see that. Gene and I looked into this, and the cause of the misbehavior is this: gcc on this installation is set to search /usr/local/lib (along with the usual system library directories). libz.so and libreadline.so are indeed in /usr/local/lib, so configure's tests to see if they can be linked against will succeed. But he had LD_LIBRARY_PATH set to a list that did *not* include /usr/local/lib, so actually firing up the executable would fail. As he says, it'd be nice if configure could either prevent this or at least detect it. Not sure about a good way to do that --- any ideas? regards, tom lane
Re: [HACKERS] $PGDATA/base/???
"Ross J. Reedstrom" [EMAIL PROTECTED] writes: I object. The code displays oids and tablenames or relnames. Oid is just the initial, default filename for tables, and may change to something other than the oid. Currently, the reindex code is the only place that could change the relfilenode without changing the oid, but I think there may be more in the future. Right, relfilenode is the thing to look at, not OID. I believe we are thinking of using relfilenode updates for a number of things in the future --- CLUSTER and faster index rebuilds in VACUUM are two thoughts that come to mind ... regards, tom lane
[HACKERS] Nothing larger then int8?
I'm logging traffic to a database, so that I can do analysis on usage and whatnot, and I need something bigger then int8 :( /tmp/psql.edit.70.79087: 6 lines, 222 characters. ip | maxbytes | port |runtime ---+-+--+ 216.126.84.28 | 2169898055 | 80 | 2001-01-16 00:00:00-05 216.126.84.28 | 160579228 | 873 | 2001-01-16 00:00:00-05 216.126.84.28 | 365270 | 20 | 2001-01-16 00:00:00-05 216.126.84.28 | 196256 | 21 | 2001-01-16 00:00:00-05 216.126.84.28 | 195238 | 22 | 2001-01-16 00:00:00-05 216.126.84.28 | 182492 | 1024 | 2001-01-16 00:00:00-05 216.126.84.28 | 171155 | 143 | 2001-01-16 00:00:00-05 216.126.84.28 | -1392384544 | 80 | 2001-01-13 00:00:00-05 216.126.84.28 | -1392384544 | 80 | 2001-01-04 00:00:00-05 216.126.84.28 | -1392384544 | 80 | 2001-01-05 00:00:00-05 216.126.84.28 | -1392384544 | 80 | 2001-01-06 00:00:00-05 216.126.84.28 | -1392384544 | 80 | 2001-01-07 00:00:00-05 216.126.84.28 | -1392384544 | 80 | 2001-01-08 00:00:00-05 216.126.84.28 | -1392384544 | 80 | 2001-01-14 00:00:00-05 216.126.84.28 | -1452855018 | 80 | 2001-01-15 00:00:00-05 216.126.84.28 | -1452855018 | 80 | 2001-01-10 00:00:00-05 216.126.84.28 | -1452855018 | 80 | 2001-01-09 00:00:00-05 216.126.84.28 | -1513325492 | 80 | 2001-01-03 00:00:00-05 216.126.84.28 | -1694736914 | 80 | 2001-01-12 00:00:00-05 216.126.84.28 | -1815677862 | 80 | 2001-01-11 00:00:00-05 hub_traf_stats=# \d daily_stats Table "daily_stats" Attribute | Type| Modifier ---+---+-- ip| inet | port | integer | bytes | bigint| runtime | timestamp | do we have anything larger to work with? I've checked docs, but that looks like about it :( Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
Re: [HACKERS] Nothing larger then int8?
hrrmm ... ignore this ... I'm suspecting that what I did was copied in sum() data from an old table that had bytes declared as int4, without casting it to int8 before storing it to the new table ... if anyone is interested, here is one days worth of http traffic for the main PostgreSQL.Org server ... this doesn't include the traffic that the mirror sites absorb: 1160643846 / ( 1024 * 1024 * 1024 ) 1.08gig On Thu, 18 Jan 2001, The Hermit Hacker wrote: I'm logging traffic to a database, so that I can do analysis on usage and whatnot, and I need something bigger then int8 :( /tmp/psql.edit.70.79087: 6 lines, 222 characters. ip | maxbytes | port |runtime ---+-+--+ 216.126.84.28 | 2169898055 | 80 | 2001-01-16 00:00:00-05 216.126.84.28 | 160579228 | 873 | 2001-01-16 00:00:00-05 216.126.84.28 | 365270 | 20 | 2001-01-16 00:00:00-05 216.126.84.28 | 196256 | 21 | 2001-01-16 00:00:00-05 216.126.84.28 | 195238 | 22 | 2001-01-16 00:00:00-05 216.126.84.28 | 182492 | 1024 | 2001-01-16 00:00:00-05 216.126.84.28 | 171155 | 143 | 2001-01-16 00:00:00-05 216.126.84.28 | -1392384544 | 80 | 2001-01-13 00:00:00-05 216.126.84.28 | -1392384544 | 80 | 2001-01-04 00:00:00-05 216.126.84.28 | -1392384544 | 80 | 2001-01-05 00:00:00-05 216.126.84.28 | -1392384544 | 80 | 2001-01-06 00:00:00-05 216.126.84.28 | -1392384544 | 80 | 2001-01-07 00:00:00-05 216.126.84.28 | -1392384544 | 80 | 2001-01-08 00:00:00-05 216.126.84.28 | -1392384544 | 80 | 2001-01-14 00:00:00-05 216.126.84.28 | -1452855018 | 80 | 2001-01-15 00:00:00-05 216.126.84.28 | -1452855018 | 80 | 2001-01-10 00:00:00-05 216.126.84.28 | -1452855018 | 80 | 2001-01-09 00:00:00-05 216.126.84.28 | -1513325492 | 80 | 2001-01-03 00:00:00-05 216.126.84.28 | -1694736914 | 80 | 2001-01-12 00:00:00-05 216.126.84.28 | -1815677862 | 80 | 2001-01-11 00:00:00-05 hub_traf_stats=# \d daily_stats Table "daily_stats" Attribute | Type| Modifier ---+---+-- ip| inet | port | integer | bytes | bigint| runtime | timestamp | do we have anything larger to work with? I've checked docs, but that looks like about it :( Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
Re: [HACKERS] Nothing larger then int8?
The Hermit Hacker [EMAIL PROTECTED] writes: I'm logging traffic to a database, so that I can do analysis on usage and whatnot, and I need something bigger then int8 :( Those "maxbytes" values shure look like they're only int4. How are you calculating 'em, exactly? regards, tom lane
Re: [HACKERS] Nothing larger then int8?
The Hermit Hacker wrote: if anyone is interested, here is one days worth of http traffic for the main PostgreSQL.Org server ... this doesn't include the traffic that the mirror sites absorb: 1160643846 / ( 1024 * 1024 * 1024 ) 1.08gig Not a bad day. I've seen 100MB per day out of my http (backed by PostgreSQL since late 1997!), but the 2.5GB a day out the RealServer is the big hit -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Re: [HACKERS] Nothing larger then int8?
On Thu, 18 Jan 2001, Lamar Owen wrote: The Hermit Hacker wrote: if anyone is interested, here is one days worth of http traffic for the main PostgreSQL.Org server ... this doesn't include the traffic that the mirror sites absorb: 1160643846 / ( 1024 * 1024 * 1024 ) 1.08gig Not a bad day. I've seen 100MB per day out of my http (backed by PostgreSQL since late 1997!), but the 2.5GB a day out the RealServer is the big hit my *big* site: 11395533772/ ( 1024 * 1024 * 1024 ) 10.61gig/day :) bytes| port -+-- 11298475398 | 81 94925095 | 80 1982130 | 20 122043 | 21 26766 | 22 2340 | 137 just a small site :)
Re: [HACKERS] Nothing larger then int8?
To answer your question, wouldn't numeric(30,0) be the correct? -alex On Thu, 18 Jan 2001, The Hermit Hacker wrote: hrrmm ... ignore this ... I'm suspecting that what I did was copied in sum() data from an old table that had bytes declared as int4, without casting it to int8 before storing it to the new table ... if anyone is interested, here is one days worth of http traffic for the main PostgreSQL.Org server ... this doesn't include the traffic that the mirror sites absorb: 1160643846 / ( 1024 * 1024 * 1024 ) 1.08gig On Thu, 18 Jan 2001, The Hermit Hacker wrote: I'm logging traffic to a database, so that I can do analysis on usage and whatnot, and I need something bigger then int8 :( /tmp/psql.edit.70.79087: 6 lines, 222 characters. ip | maxbytes | port |runtime ---+-+--+ 216.126.84.28 | 2169898055 | 80 | 2001-01-16 00:00:00-05 216.126.84.28 | 160579228 | 873 | 2001-01-16 00:00:00-05 216.126.84.28 | 365270 | 20 | 2001-01-16 00:00:00-05 216.126.84.28 | 196256 | 21 | 2001-01-16 00:00:00-05 216.126.84.28 | 195238 | 22 | 2001-01-16 00:00:00-05 216.126.84.28 | 182492 | 1024 | 2001-01-16 00:00:00-05 216.126.84.28 | 171155 | 143 | 2001-01-16 00:00:00-05 216.126.84.28 | -1392384544 | 80 | 2001-01-13 00:00:00-05 216.126.84.28 | -1392384544 | 80 | 2001-01-04 00:00:00-05 216.126.84.28 | -1392384544 | 80 | 2001-01-05 00:00:00-05 216.126.84.28 | -1392384544 | 80 | 2001-01-06 00:00:00-05 216.126.84.28 | -1392384544 | 80 | 2001-01-07 00:00:00-05 216.126.84.28 | -1392384544 | 80 | 2001-01-08 00:00:00-05 216.126.84.28 | -1392384544 | 80 | 2001-01-14 00:00:00-05 216.126.84.28 | -1452855018 | 80 | 2001-01-15 00:00:00-05 216.126.84.28 | -1452855018 | 80 | 2001-01-10 00:00:00-05 216.126.84.28 | -1452855018 | 80 | 2001-01-09 00:00:00-05 216.126.84.28 | -1513325492 | 80 | 2001-01-03 00:00:00-05 216.126.84.28 | -1694736914 | 80 | 2001-01-12 00:00:00-05 216.126.84.28 | -1815677862 | 80 | 2001-01-11 00:00:00-05 hub_traf_stats=# \d daily_stats Table "daily_stats" Attribute | Type| Modifier ---+---+-- ip| inet | port | integer | bytes | bigint| runtime | timestamp | do we have anything larger to work with? I've checked docs, but that looks like about it :( Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
[HACKERS] Datetime regression tests are all failing
Your last commit seems to have broken timestamp, interval, reltime, and horology regress tests on HPUX. Minus signs are showing up in a lot of unexpected-looking places, eg *** ./expected/timestamp.outSat Nov 25 11:05:59 2000 --- ./results/timestamp.out Thu Jan 18 01:28:28 2001 *** *** 631,638 SELECT '' AS "53", d1 - timestamp '1997-01-02' AS diff FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01'; 53 | diff ! + ! | @ 9863 days 8 hours ago | @ 39 days 17 hours 32 mins 1 sec | @ 39 days 17 hours 32 mins 1.00 secs | @ 39 days 17 hours 32 mins 2.00 secs --- 631,638 SELECT '' AS "53", d1 - timestamp '1997-01-02' AS diff FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01'; 53 |diff ! + ! | @ -9863 days -8 hours ago | @ 39 days 17 hours 32 mins 1 sec | @ 39 days 17 hours 32 mins 1.00 secs | @ 39 days 17 hours 32 mins 2.00 secs Is this now the intended behavior? regards, tom lane
[HACKERS] Re: Datetime regression tests are all failing
Tom Lane wrote: Your last commit seems to have broken timestamp, interval, reltime, and horology regress tests on HPUX. Minus signs are showing up in a lot of unexpected-looking places... Is this now the intended behavior? Uh, no. Believe it or not, I had just noticed this myself, and have prepared patches to fix it up. The problem is with "traditional Postgres" interval output. The behavior before my recent patches was not correct when there was sign-mixing between fields, but the patches didn't do anything better, and as you noticed some of the regression test looks terrible. Anyway, I was just getting ready to send a note to the list to this effect. I'll try committing patches in the next few minutes, and I think the result is the cleanest interval representation we've had. I've included a few changes to the "leading sign" inclusion for the "ISO-style" interval also. There is a small chance that I won't be able to prepare good patches, since I'm currently sitting behind a firewall and can't update my CVS tree locally, but I expect to be able to do this on postgresql.org. Wish me luck ;) - Thomas
[HACKERS] GET DIAGNOSTICS SELECT PROCESSED INTO int4_variable
Does anyone know if this feature exists? If so, what version or where can a patch be obtained? Thanks --- Forwarded message follows --- Date sent: Mon, 15 Jan 2001 08:44:46 +0100 From: "J.H.M. Dassen (Ray)" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject:Re: getting number of rows updated within a procedure On Sun, Jan 14, 2001 at 23:27:06 +1300, Dan Langille wrote: I'm writing some stuff in PL/pgsql (actually, a lot of stuff). I have a question: At various times, it does UPDATEs. Is there a way to tell if the UPDATE actually affected any rows or not? I couldn't see how to get UPDATE to return anything. Quoting a recent message by Jan Wieck [EMAIL PROTECTED]: :Do a : :GET DIAGNOSTICS SELECT PROCESSED INTO int4_variable; : :directly after an INSERT, UPDATE or DELETE statement and you'll know :how many rows have been hit. : :Also you can get the OID of an inserted row with : :GET DIAGNOSTICS SELECT RESULT INTO int4_variable; HTH, Ray -- "The software `wizard' is the single greatest obstacle to computer literacy since the Mac." http://www.osopinion.com/Opinions/MichaelKellen/MichaelKellen1.html --- End of forwarded message --- -- Dan Langille The FreeBSD Diary - http://freebsddiary.org/ FreshPorts - http://freshports.org/ NZ Broadband - http://unixathome.org/broadband/
[HACKERS] Re: Datetime regression tests are all failing
Fixes committed. - Thomas Fix up "Postgres-style" time interval representation when fields have mixed-signs. Previous effort left way too many minus signs, and was at least as broken as the one before that :( Clean up "ISO-style" time interval representation to omit zero fields if there is at least one non-zero field. Supress some leading plus signs when not necessary for clarity. Replace every #ifdef __CYGWIN__ block with a cleaner TIMEZONE_GLOBAL macro defined in datetime.h.
[HACKERS] Re: [GENERAL] Slashdot and PostgreSQL
On Wednesday 17 January 2001 02:53, Alessio Bragadini wrote: Hunter Hillegas wrote: I don't think they're moving the actual Slashdot site to PostgreSQL... So do I. I think other sites based on Slashcode wanted to be able to use PostgreSQL though... That's what I will do as soon as possible, and I am trying to be involved as much as possible in the current development. I am also waiting for 7.1 to have a cleaner environment to test it. I made a board with php and postgresql. It's *terrible* code but is working at www.comptechnews.com. If anyone is interested in playing with it, I can make it available. Who knows, the code might have bugs that are very compromising! :) People might like to improve it. It consists of one php file and three sql files (tables, data, procedures). It uses PL/pgSQL and PL/TcL. You just run the tables sql, load data, then run procedures sql. Put the php file in a directory and change the pg_pconnect line to connect to the right db. The php file is 3638 lines. It tries fairly hard to be automatically moderated and to have good protection from users trying to do bad things. Code in the php and in the trigger procs provide two layers of logic that tries to ensure only correct things happen. It takes good advantage of transactions. The RAISE EXCEPTION PL/pgSQL call is used to rollback/abort things that shouldn't happen ... stuff like that. The trigger procs do recursive stuff to manage the threaded messages and topics. Again the php code is an embarrassment, but I don't care! :) -- Robert B. Easter [EMAIL PROTECTED] - -- CompTechNews Message Board http://www.comptechnews.com/ -- -- CompTechServ Tech Services http://www.comptechserv.com/ -- -- http://www.comptechnews.com/~reaster/