Re: [HACKERS] Seqscan in MAX(index_column)
A general query cache is something that is fairly clean and which might help both with count(*) and other queries. Many databases has a lot of tables that are more or less stable where this would work fine. From what I have heard mysql has something like this and it works well. For tables that change a lot the the cached queries will almost always be invalid so one might want to let the user decide which tables should never be cached. It works well because MySQL doesn't have MVCC... Chris ---(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] FK type mismatches?
Neil Conway [EMAIL PROTECTED] writes: Should this produce a warning? [ foreign-key reference to column of a different datatype ] Aside from the logical inconsistency, it will also lead to poor performance since the type mismatch will prevent index scans. I've noticed a couple people have reported performance issues due to making this kind of mistake. There was some discussion of this a couple weeks back, but I don't recall any clear consensus emerging. I'm for it myself though. If we follow Peter's recently proposed guideline, this would have to be a NOTICE not a WARNING, because the command absolutely is doing what you told it to do. Peter, does that make you uncomfortable? It's not exactly the answer I would've wanted. In this context it seems like we want WARNING to mean we'll do what you told us to do, but are you really *sure* it is what you want? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Prelimiary DBT-2 Test results
On 4 Sep 2003 at 10:53, [EMAIL PROTECTED] wrote: http://developer.osdl.org/markw/44/ I threw together (kind of sloppily) a web page of the data I was starting to collect for our DBT-2 workload (TPC-C derivative) on PostgreSQL 7.3.4. Keep in mind not much database tuning has been done yet. Feel free to ask any questions. You should set effective cache size to bit more realistic than 1000. That's just 8MB. I would also suggest you setting autocommit to off, in case that makes any difference. If the application is entirely managing it's own transactions explicitly this should not make any difference. If youhave good disks like SCSI/IDE RAID or above, you can reduce random_page_cost to 2 or even less. For heavily updated systems, you should have WAL buffers bit more. I don't know exact imact of that setting though. You could try 32/64/128. On the same note, if you are getting checkpoints too frequently, you can try increasing checkpoint segments. The logs will tell as such. HTH Bye Shridhar -- QOTD: When she hauled ass, it took three trips. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] TCP/IP with 7.4 beta2 broken?
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: There's a lot of confusion around :-) Let me see if I can disentangle some of it. People seem to want two things: 1. if ip4 is being tunneled over ip6 as it is in most Linux distributions, match a corresponding 'host*' line with an ip4 address. 2. enable local connections of whatever flavor by default. Sounds right to me. Andreas has addressed item 1. I suggested an approach to item 2. The only alternative I can see is to allow ip4-only postmasters to recognize and silently drop ip6 'host*' lines. I don't like the idea of silently ignoring config lines - it seems dangerous to me. Suggestions of having initdb or something similar conditionally set the default pg_hba.conf also strike me as impractical and fragile. Bruce and I were just discussing this on the phone. It seems we have two basic approaches to problem #2. Either we hack the postmaster so that it will swallow IPv6 addresses in pg_hba.conf even without any real IPv6 support, or we make the default pg_hba.conf contents different. Neither of these is real pretty, but I am leaning to the second, because I agree with your feeling that silently ignoring config lines is a bad idea. I do not believe that there's anything fragile about having initdb make this adjustment. We can arrange for initdb to be aware of the HAVE_IPV6 compilation flag (its value can be inserted when initdb is made from initdb.sh, the same way some other configuration items are already inserted into the script). As far as I can see, HAVE_IPV6 is exactly what we want to look at to decide whether to put ::1 into pg_hba.conf. If we HAVE_IPV6, then the postmaster can parse ::1. Whether the kernel has IPv6 enabled doesn't matter --- if not, it would only mean that the postmaster will never actually see a connection from ::1; so the pg_hba.conf entry will never be matched. But it won't hurt anything. Conversely, if we don't HAVE_IPV6, we can't parse ::1 ... but we don't need to, even if the kernel has IPv6, because such a postmaster won't try to listen for AF_INET6 connections. OK, now we are getting somewhere. I see that this would work. It's a bit ugly, though - with this plan the sample file in both CVS and the installation won't necessarily be what actually get put in place. What if some clever installer/administrator deliberately alters their installed sample file? Could we get the configure script to do it instead, since it too should know about ip6 capability? (I guess then we'd have pg_hba.conf.sample.in). That strikes me as being a lot cleaner. andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [PATCHES] Warning for missing createlang
Bruce Momjian wrote: Bruce Momjian wrote: I have written a patch to issue an hint if someone tries to create a function in a language that isn't loaded into the database: test= CREATE FUNCTION xx() RETURNS INT AS ' test' select 1' test- LANGUAGE 'plpgsql'; ERROR: language plpgsql does not exist HINT: Perhaps you need to use 'createlang' to load the language into the database. I know Peter didn't like this idea in the past, but we are getting too many people who forget createlang, and with our new HINT tags, it seems appropriate. OK, Peter and Tom don't like it. :-( How about this, that also suggests you mistyped the name: HINT: Perhaps you need to use 'createlang' to load the language into the database, or you mistyped the language name. ^ Why not list out the languages we *do* know about, and tell them it's not in the list? Or is that too much work? andrew ---(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] Win32 native port
On Thursday 04 September 2003 09:22, Joerg Hessdoerfer wrote: Hi! Thanks to all who have replied (privately or via the list), it seems sometimes it's just necessary to be a bit insistant! That said, I'm positively surprised by what has been done already (especially Bruce and Marc, this is really a GoodThing to have the web page and the list). I'm currently in the process of setting up my development environment (how the heck do I get bison/flex to compile under MingW/MSYS? Oh my...), and then I'll go adventuring in the code. See the page : http://www.mingw.org/download.shtml There are msys and mingw binary packages including bison and gettext. If You have teouble with that, feel free to contact me (I have some experience with that concerning I have succesfully compile pgAdmin on MinGW. Regards ! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Win32 native port
On Thursday 04 September 2003 19:20, Peter Eisentraut wrote: Joerg Hessdoerfer writes: I'm currently in the process of setting up my development environment (how the heck do I get bison/flex to compile under MingW/MSYS? Oh my...), Use the Cygwin tools. There is no need for that, MinGW has bison and gettext packages. Regards ! ---(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] plpython
NO!!! Don't remove SD and GD!!! They are useful. I use them in several applications, primarily for running aggregates. What needs to be fixed is that the SD needs to be initialized at the start of each statement. Joe Conway just implemented this in Pl/R and Tom Lane had an idea about it too. See http:/www.varlena.com/GeneralBits/TidBits for the talk and code I gave on running aggregation with plpython at OSCON. It illustrates the initialization problem. And don't remove plpy. You can move it or replace its implementation, but do not remove it. People are really using these things. People are also depending on python's loose type conversion from strings. If you add another kind of conversion interpretation, you must keep the backward compatibility or call it something different. It seems to me is that you need to talk more to people using plpython. I am just one person. There are others. I hope I've misunderstood you about some of these things... [EMAIL PROTECTED] On Thu, Sep 04, 2003 at 03:01:57PM -0700, James Pye wrote: Greetings, I've recently been spending some quality time with the plpython module, and I think I'm well on the road to an improved version of it(although, nothing about a trusted variant). By improved, I mostly mean cleaned up, and reorganized.. Here are some of the changes that I have made in my own version: Compilation and execution have been greatly simplified and should be faster(at least execution should be). Caching of compiled code no longer references a Python dictionary(PLyProcedureCache). The handler keeps its own vector of procedure structs(should be faster, and is trivial). Removal of plpython generated dictionaries SD and GD. They don't seem be very useful, as they are forgotten when the postmaster exits and not remembered when a new one starts. SD is questionable, does/did anyone find SD very useful? GD seems almost pointless as the global keyword should be sufficient. Although, I do think there was a mention of GD being safe globals, but I don't know why it would be safer than global var. Removal of the built-in plpy python module that plpython creates. This is done because it provides interfaces to pgsql functions that I feel should be located elsewhere; elsewhere being another python module. I've already generated a preliminary interface to elog and SPI_* with SWIG that at first glance seems quite functional(it links, and is at least able to properly call elog, I haven't really tested SPI). Improvement to tracebacks, as it now NOTICE's the python tracebacks(There is already an ERROR, so I don't think WARNING is necessary). PLy_traceback, originally, seemed to ignore the tb of the PyErr_Fetch. Removal of plpython type conversion routines and data structures. This was done because I felt that there was a better way to do it. Not sure what yet, as it is one of my questions to the list, but it will probably end up being a similar implementation. I also plan to make some changes to trigger handling, but I haven't done anything worth mentioning yet.. Type conversion plpython's current type conversion implementation appears to be dependent on strings as the common format. This is fine, but not very extensible as is, unless you don't mind explicitly parsing strings inside each function that takes an unsupported data type. I was thinking that a better solution would be creating a python object type inside the database. Thus allowing users to write casts to and from non-standard or unimplemented data types with little difficulty(well, maybe some :). This would allow conversion in an extensible way, which doesn't require modification to plpython. Storage could be easily achieved by pickling the object. Another thought would be to just pass valid PyObject pointers in and out of conversion procedures, effectively disallowing storage(outside the process in which the object was created in), unless it is possible to have a persistent storage mechanism that makes it possible to go through pickle?.?..(yeah, I'm new to pgsql dev). Python PostgreSQL Interface plpython, currently, implements its own built-in module to interface with a few pgsql routines, and it works, but I feel it should be located elsewhere, as I said before. For the most part, I can only see most people using elog, and SPI within plpy, but perhaps that is too narrow of a view. Perhaps it would be useful to many to have access to some backend routines through plpy, but I'm not sure and that is why I'm asking the list. How far should such an PostgreSQL interface module go? What should its name be if full/semi-full interface is created? I was thinking simply py-pgsql as the package name, and the module name, of course, would be pgsql. What should the name be if it was only elog and SPI? py-pgspi?
Re: [HACKERS] ANONCVS? Is it being updated correctly?
--On Thursday, September 04, 2003 16:17:48 -0300 Marc G. Fournier [EMAIL PROTECTED] wrote: The full check out found them :-\ I dunno what was going on. 'k, as I said, for some reason the /projects/cvsroot itself wasn't being updated properly either, so it might be related *shrug* let me know if it happens again, that's all ... Will do. Thanks for the fix(es). LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PG7.5
As long as you're sure. Bupp Phillips [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Will this have the native Windows port? Marc G. Fournier [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On Tue, 2 Sep 2003, postgresql wrote: Hi all Can anyone tell me the approximate pg 7.5 release date? Summer of '04 ... approximate :) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Stats Collector Error 7.4beta1 and 7.4beta2
Redhat 7.1 says The file descriptor sockfd must refer to a socket. If the socket is of type SOCK_DGRAM then the serv_addr address is the address to which datagrams are sent by default, and the only address from which datagrams are received. If Looks like the test is obsolete. Any objections to remove it? Do people agree that it's a bugfix that can go into 7.4? Jan Tom Lane wrote: Kurt Roeckx [EMAIL PROTECTED] writes: Then it connects to itself. I don't get the logic behind that howver. At least on HPUX, the connect(2) man page saith If the socket is of type SOCK_DGRAM, connect() specifies the peer address to which messages are to be sent, and the call returns immediately. Furthermore, this socket can only receive messages sent from this address. The furthermore is what we are after. regards, tom lane -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] AIX 4.2.1 CVS head and SSL
I get the following errors gmake -C ecpglib all gmake[4]: Entering directory `/usr/local/postgres/pgsql/src/interfaces/ecpg/ecpg lib' ../../../../src/backend/port/aix/mkldexport.sh libecpg.a libecpg.exp gcc -O2 -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -Wl,-bnoentry - Wl,-H512 -Wl,-bM:SRE -o libecpg.so libecpg.a -L../../../../src/port -lm -L/usr/ local/ssl/lib -L../../../../src/port -L/usr/local/ssl/lib -L../pgtypeslib -lpgt ypes -L../../../../src/interfaces/libpq -lpq -Wl,-bI:../../../../src/backend/p ostgres.imp -Wl,-bE:libecpg.exp ld: 0711-224 WARNING: Duplicate symbol: .getnameinfo_all ld: 0711-224 WARNING: Duplicate symbol: .getaddrinfo_all ld: 0711-224 WARNING: Duplicate symbol: .freeaddrinfo_all ld: 0711-224 WARNING: Duplicate symbol: .rangeSockAddr ld: 0711-224 WARNING: Duplicate symbol: .SockAddr_cidr_mask ld: 0711-224 WARNING: Duplicate symbol: .pqsignal ld: 0711-224 WARNING: Duplicate symbol: .pg_char_to_encoding ld: 0711-224 WARNING: Duplicate symbol: .pg_valid_client_encoding ld: 0711-224 WARNING: Duplicate symbol: .pg_valid_server_encoding ld: 0711-224 WARNING: Duplicate symbol: .pg_char_to_encname_struct ld: 0711-224 WARNING: Duplicate symbol: .pg_encoding_to_char ld: 0711-224 WARNING: Duplicate symbol: pg_encname_tbl ld: 0711-224 WARNING: Duplicate symbol: pg_encname_tbl_sz ld: 0711-224 WARNING: Duplicate symbol: pg_enc2name_tbl ld: 0711-224 WARNING: Duplicate symbol: .DLNewElem ld: 0711-224 WARNING: Duplicate symbol: .DLNewList ld: 0711-224 WARNING: Duplicate symbol: .DLInitList ld: 0711-224 WARNING: Duplicate symbol: .DLFreeList ld: 0711-224 WARNING: Duplicate symbol: .DLInitElem ld: 0711-224 WARNING: Duplicate symbol: .DLFreeElem ld: 0711-224 WARNING: Duplicate symbol: .DLRemove ld: 0711-224 WARNING: Duplicate symbol: .DLAddHead ld: 0711-224 WARNING: Duplicate symbol: .DLAddTail ld: 0711-224 WARNING: Duplicate symbol: .DLRemHead ld: 0711-224 WARNING: Duplicate symbol: .DLRemTail ld: 0711-224 WARNING: Duplicate symbol: .DLMoveToFront ld: 0711-224 WARNING: Duplicate symbol: .pg_encoding_mblen ld: 0711-224 WARNING: Duplicate symbol: .pg_utf_mblen ld: 0711-224 WARNING: Duplicate symbol: .pg_mule_mblen ld: 0711-224 WARNING: Duplicate symbol: .pg_mic_mblen ld: 0711-224 WARNING: Duplicate symbol: .pg_encoding_max_length ld: 0711-224 WARNING: Duplicate symbol: pg_wchar_table ld: 0711-224 WARNING: Duplicate symbol: pg_utf_mblen ld: 0711-224 WARNING: Duplicate symbol: pg_mule_mblen ld: 0711-224 WARNING: Duplicate symbol: .EncryptMD5 ld: 0711-224 WARNING: Duplicate symbol: .md5_hash ld: 0711-224 WARNING: Duplicate symbol: .ParseDateTime ld: 0711-224 WARNING: Duplicate symbol: .DecodeUnits ld: 0711-224 WARNING: Duplicate symbol: .date2j ld: 0711-224 WARNING: Duplicate symbol: .j2date ld: 0711-224 WARNING: Duplicate symbol: .j2day ld: 0711-224 WARNING: Duplicate symbol: .EncodeDateOnly ld: 0711-224 WARNING: Duplicate symbol: .EncodeDateTime ld: 0711-224 WARNING: Duplicate symbol: .GetEpochTime ld: 0711-224 WARNING: Duplicate symbol: .GetCurrentDateTime ld: 0711-224 WARNING: Duplicate symbol: .DecodeDateTime ld: 0711-224 WARNING: Duplicate symbol: months ld: 0711-224 WARNING: Duplicate symbol: days ld: 0711-224 WARNING: Duplicate symbol: .tm2timestamp ld: 0711-224 WARNING: Duplicate symbol: .DecodeInterval ld: 0711-224 WARNING: Duplicate symbol: .EncodeInterval ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more information. ld: 0711-317 ERROR: Undefined symbol: .SSL_new ld: 0711-317 ERROR: Undefined symbol: .SSL_set_ex_data ld: 0711-317 ERROR: Undefined symbol: .SSL_set_fd ld: 0711-317 ERROR: Undefined symbol: .SSL_read ld: 0711-317 ERROR: Undefined symbol: .SSL_get_error ld: 0711-317 ERROR: Undefined symbol: .SSL_write ld: 0711-317 ERROR: Undefined symbol: .PEM_read_DHparams ld: 0711-317 ERROR: Undefined symbol: .DH_size ld: 0711-317 ERROR: Undefined symbol: .DH_check ld: 0711-317 ERROR: Undefined symbol: .BIO_new_mem_buf ld: 0711-317 ERROR: Undefined symbol: .PEM_read_bio_DHparams ld: 0711-317 ERROR: Undefined symbol: .BIO_free ld: 0711-317 ERROR: Undefined symbol: .DH_generate_parameters ld: 0711-317 ERROR: Undefined symbol: .SSL_get_ex_data ld: 0711-317 ERROR: Undefined symbol: .PEM_read_X509 ld: 0711-317 ERROR: Undefined symbol: .X509_free ld: 0711-317 ERROR: Undefined symbol: .PEM_read_PrivateKey ld: 0711-317 ERROR: Undefined symbol: .X509_check_private_key ld: 0711-317 ERROR: Undefined symbol: .EVP_PKEY_free ld: 0711-317 ERROR: Undefined symbol: .SSL_library_init ld: 0711-317 ERROR: Undefined symbol: .SSL_load_error_strings ld: 0711-317 ERROR: Undefined symbol: .TLSv1_method ld: 0711-317 ERROR: Undefined symbol: .SSL_CTX_new ld: 0711-317 ERROR: Undefined symbol: .SSL_CTX_load_verify_locations ld: 0711-317 ERROR: Undefined symbol: .SSL_CTX_set_verify ld: 0711-317 ERROR: Undefined symbol: .SSL_CTX_set_verify_depth ld: 0711-317 ERROR: Undefined symbol: .SSL_CTX_set_tmp_dh_callback ld: 0711-317 ERROR: Undefined symbol: .SSL_CTX_ctrl ld: 0711-317 ERROR: Undefined
Re: [HACKERS] [PATCHES] Warning for missing createlang
Andrew Dunstan [EMAIL PROTECTED] writes: Bruce Momjian wrote: HINT: Perhaps you need to use 'createlang' to load the language into the database, or you mistyped the language name. Why not list out the languages we *do* know about, and tell them it's not in the list? Or is that too much work? Seems like it would clutter the error message without really addressing Bruce's concern. I doubt that seeing the list of available languages would do much to jog a newbie's memory about needing to run createlang. We could answer my objection about the hint popping out on misspelled language names if the code were to arrange to put out the hint only when the language name is one of plpgsql, pltcl, pltclu, etc. This would have to use a hard-coded list of loadable language names, since by definition looking in pg_language won't help. It would be enough of a maintenance PITA that I don't especially want to do it ... but it would ensure that the hint is likely to be relevant. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] TCP/IP with 7.4 beta2 broken?
Andrew Dunstan wrote: Bruce and I were just discussing this on the phone. It seems we have two basic approaches to problem #2. Either we hack the postmaster so that it will swallow IPv6 addresses in pg_hba.conf even without any real IPv6 support, or we make the default pg_hba.conf contents different. Neither of these is real pretty, but I am leaning to the second, because I agree with your feeling that silently ignoring config lines is a bad idea. OK, now we are getting somewhere. I see that this would work. It's a bit ugly, though - with this plan the sample file in both CVS and the installation won't necessarily be what actually get put in place. What if some clever installer/administrator deliberately alters their installed sample file? Could we get the configure script to do it instead, since it too should know about ip6 capability? (I guess then we'd have pg_hba.conf.sample.in). That strikes me as being a lot cleaner. I talked to Tom about this, and we clearly can do it as part of the build process, rather than at initdb. Tom said postgresql.conf already doesn't match the sample file because of changes made by initdb, so he didn't see how pg_hba.conf changing during initdb would be any worse. I think either way is fine. I prefer at build, but I can see his point too. -- 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 8: explain analyze is your friend
Re: [HACKERS] TCP/IP with 7.4 beta2 broken?
Andrew Dunstan [EMAIL PROTECTED] writes: OK, now we are getting somewhere. I see that this would work. It's a bit ugly, though - with this plan the sample file in both CVS and the installation won't necessarily be what actually get put in place. Well, like I said, it's not real pretty. But the same is already true of postgresql.conf.sample --- initdb edits that. I don't see that having it edit pg_hba.conf.sample too is so bad. What if some clever installer/administrator deliberately alters their installed sample file? I don't think it would hurt them. The editing will consist of a sed script to comment or uncomment the line containg ::1, it wouldn't touch anything else. Could we get the configure script to do it instead, since it too should know about ip6 capability? (I guess then we'd have pg_hba.conf.sample.in). That strikes me as being a lot cleaner. Bruce and I talked about that alternative too, but we felt that it made more sense to keep the processing of pg_hba.conf.sample parallel to what happens to postgresql.conf.sample. Further down the road we might need initdb-time checks to decide what to do to the sample file, just as we already need for postgresql.conf.sample. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Stats Collector Error 7.4beta1 and 7.4beta2
Jan Wieck wrote: Redhat 7.1 says The file descriptor sockfd must refer to a socket. If the socket is of type SOCK_DGRAM then the serv_addr address is the address to which datagrams are sent by default, and the only address from which datagrams are received. If Looks like the test is obsolete. Any objections to remove it? Do people agree that it's a bugfix that can go into 7.4? Oh, it definitely has to be in 7.4. We can't ship it in its broken state. -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Seqscan in MAX(index_column)
Christopher Browne [EMAIL PROTECTED] writes: Wouldn't this more or less be the same thing as having a trigger that does, upon each insert/delete update pg_counts set count = count + 1 where reltable = 45232;? (... where 1 would be -1 for deletes, and where 45232 is the OID of the table...) I think that's exactly what Bruce was suggesting. A slightly more efficient variant is for each transaction to save up its net deltas, and issue a single UPDATE for each table it's touched just before it commits. But that just reduces the volume of update traffic, it doesn't fundamentally alter the concept. Technically, it seems _feasible_, albeit with the problem that it turns pg_counts into a pretty horrid bottleneck. Not to mention a likely source of deadlocks. And it still doesn't solve the fundamental objection that you can't get an MVCC-correct answer by examining the table. An idea I was toying with is to do something similar to what was just suggested to David Skoll for his stats problem: instead of using UPDATEs, use INSERTs of delta records. That is, every time a transaction is about to commit, it INSERTs into the counts table a row like 45232 +1 (I inserted one row) or 45232 -10 (I deleted ten rows). Assume that we somehow initialized the counts table with an entry 45232 total-rows for each table. Then, a COUNT(*) on table 45232 is equivalent to SELECT SUM(deltas) FROM counts WHERE reltable = 45232. As long as the number of rows you have to look at to compute this sum is smaller than the number of rows in the original table, it's a win. The cool thing about this approach is that it is actually MVCC-correct. If some transaction has committed, but is uncommitted according to your worldview, your SUM will automatically ignore its delta row. Another cool thing is that the INSERTs don't conflict with each other, so there's no contention or deadlock risk. You would periodically (perhaps during VACUUM) update the counts table with operations that are conceptually like BEGIN; INSERT INTO counts SELECT reltable, SUM(deltas) FROM counts WHERE xid GLOBALXMIN GROUP BY reltable; DELETE FROM counts WHERE xid GLOBALXMIN; COMMIT; to sweep together the past deltas from transactions that are so old no one cares about their individual effects anymore (GLOBALXMIN is the same cutoff used by VACUUM to decide it can remove a committed-dead tuple). This prevents the number of delta rows from growing indefinitely large over time. And if I have tables where I insert lots of data, but couldn't care less how many rows they have, this effort is wasted. Yes, this mechanism would be hugely expensive in any case. I can't see enabling it overall, it would have to be turned on only for specific tables by user command. It'd be interesting to try to code it as a contrib module that's fired by triggers on the tables you want to track. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [PATCHES] Warning for missing createlang
On Fri, 2003-09-05 at 09:52, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Bruce Momjian wrote: HINT: Perhaps you need to use 'createlang' to load the language into the database, or you mistyped the language name. Why not list out the languages we *do* know about, and tell them it's not in the list? Or is that too much work? Seems like it would clutter the error message without really addressing Bruce's concern. I doubt that seeing the list of available languages would do much to jog a newbie's memory about needing to run createlang. Ok, it's a hint right? Step 1 is to merge the fuzzystrmatch contrib module into main line code. Step 2 is to use that code to determine a close match. CREATE FUNCTION LANGUAGE 'plgsql'; System notices the error, and looks for languages in pg_language that have similar names to 'plgsql', finds 'plpgsql'. ERROR: Language 'plgsql' not found HINT: Perhaps you intended to use the language 'plpgsql? Lots of work (both to implement and maintain) but it would be a neat trick. signature.asc Description: This is a digitally signed message part
[HACKERS] 64-bit pgsql
Hello, I'm a beginner here at the list, my name is Daniel Pellegrini, graduation student from Brazil with no experience about pgsql. First of all I'd like to say that I have tried other lists before, but I didn't get the answer, or the complete answer. I'm doing a research about DBMSs that run on new PC 64-bit processors, like Intel Itanium and AMD Opteron. I'd like that you would help me in some questions. First, does pgsql support this architecture? According to Administrator's Guide(http://www.postgresql.org/docs/7.3/static/supported-platforms.html), there are versions, for example, for SPARC and Alpha, but it doesn't mention those I'm looking for. In case that the support exists,I'd like to know the difference between the 32-bit and the 64-bit versions, if there is some differences in semantics or something like that or it's just the implementation and the application features are transparent for the user. Thanks a lot and excuse me about my poor English, please. Daniel Pellegrini --- now, in Portuguese: Olá, Sou novo aqui na lista, meu nome é Daniel Pellegrini, sou estudante de graduação no Brasil sem experiência no pgsql. Antes de tudo, gostaria de dizer que já tentei em outras listas mas não consegui a resposta, ou a resposta completa. Estou fazendo uma pesquisa que trata sobre SGBDs que rodam nos novos processadores PC de 64 bits, como Intel Itanium e AMD Opteron. Gostaria que vocês me ajudassem em algumas questões. Primeiro, o pgsql dá suporte a essa arquitetura? De acordo com o Guia do Administrador (http://www.postgresql.org/docs/7.3/static/supported-platforms.html), existem versões, por exemplo, para SPARC e Alpha, mas não fala sobre as citadas anteriormente. Caso exista o suporte, gostaria de saber a diferença entre as versões 32 e 64 bits, se há alguma diferença na semântica ou algo do tipo ou se é só a implementação e as características da aplicação são transparentes para o usuário. Agradeço desde já a atenção. Daniel Pellegrini ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Seqscan in MAX(index_column)
Bruce Momjian [EMAIL PROTECTED] writes: The FAQ does have the example of using ORDER BY LIMIT 1 for MAX(). What we don't have a workaround for is COUNT(*). I think that will require some cached value that obeys MVCC rules of visibility. Note that that only handles min()/max() for the whole table. It doesn't handle the GROUP BY case, for that you need DISTINCT ON with an ORDER BY clause. I don't see anything special about count(*) that makes it especially amenable to optimization. In fact I think you're headed to a full updatable materialized views implementation with this approach. Materialized views are basically tables that are guaranteed to always contain the results of a view. They're constructed by executing the specified query (eg select bar,count(*) n from foo group by bar). Then updated every time the underlying tables are modified (eg insert into foo (bar) values (1) does an update foo_count_view set n = n+1 where bar=1). Then they're available for the optimizer to substitute whenever it sees an expression they can answer. (so if you do select count(*) from foo where bar=1 it gets transformed into select n from foo_count_view where bar=1). It's a big project. I think the min/max optimization is one of those things that has to happen sometime. It's something people expect to work, and as long as it doesn't the database just isn't using the data it already has as well as it could. Materialized views would be nice, Oracle has them largely because they let the Oracle techs make a *huge* increase in their spec numbers. They were at the heart of that challenge a few years ago When Ellison said he would pay a million dollars to anyone who showed that MSSQL could come within a factor of 10 of Oracle. It was impossible only because Oracle wasn't really doing the same order of work because of materialized views. But they're a would be neat kind of thing. Nobody comes to the database expecting to find them. If postgres had them it would be really really cool. But it seems like there are more important things to be working on. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 64-bit pgsql
I'm doing a research about DBMSs that run on new PC 64-bit processors, like Intel Itanium and AMD Opteron. I'd like that you would help me in some questions. First, does pgsql support this architecture? According to Administrator's Guide(http://www.postgresql.org/docs/7.3/static/supported-platforms.html), there are versions, for example, for SPARC and Alpha, but it doesn't mention those I'm looking for. In case that the support exists,I'd like to know the Opteron was not publicly accessible 7.3 release was made, thus 7.3 does not support that platform. 7.4 seems to be functional on that processor per reports so it will probably be a supported platform for this release. Download 7.4 beta 2 and run regression tests on those platforms. Report back any issues or successes. 7.4 Release candidates will come with a call for reports on platforms that pass the regression tests which are used to make up the supported platform list. difference between the 32-bit and the 64-bit versions, if there is some differences in semantics or something like that or it's just the implementation and the application features are transparent for the user. Not to the SQL interface but some of the structures used are different -- just as they may change when using different operating systems. signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Prelimiary DBT-2 Test results
Shridhar Daithankar wrote: For heavily updated systems, you should have WAL buffers bit more. I don't know exact imact of that setting though. You could try 32/64/128. On the same note, if you are getting checkpoints too frequently, you can try increasing checkpoint segments. The logs will tell as such. Only 7.4beta reports if checkpoints are too frequent. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] checkpoints too frequent
Vivek, you reported recently that increasing sort_mem and checkpoint_segments increased performance. Can you run a test to see how much of that improvement was just because of increasing checkpoint_segments? -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] TCP/IP with 7.4 beta2 broken?
Robert Creager wrote: Once upon a time (Fri, 05 Sep 2003 03:16:54 -0400) Andrew Dunstan [EMAIL PROTECTED] uttered something amazingly similar to: Could we get the configure script to do it instead, since it too should know about ip6 capability? (I guess then we'd have pg_hba.conf.sample.in). That strikes me as being a lot cleaner. What about RPM users? Cheers, Rob My recollection is that RPM builds use configure, so they would be just fine, I think. The same consideration would apply to using initdb, of course. But Tom doesn't like it so I guess it's moot. cheers andrew ---(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] 64-bit pgsql
-On [20030905 16:42], Rod Taylor ([EMAIL PROTECTED]) wrote: Download 7.4 beta 2 and run regression tests on those platforms. Report back any issues or successes. 7.4 Release candidates will come with a call for reports on platforms that pass the regression tests which are used to make up the supported platform list. Well, on Itanium2 on FreeBSD 5.1 it compiles. I just need to get the semaphores to a higher value in order to actually do an initdb. -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ In the world, there is nothing more submissive and weak than water. Yet for attacking that which is hard and strong, nothing can surpass it... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Stats Collector Error 7.4beta1 and 7.4beta2
Jan Wieck [EMAIL PROTECTED] writes: Redhat 7.1 says The file descriptor sockfd must refer to a socket. If the socket is of type SOCK_DGRAM then the serv_addr address is the address to which datagrams are sent by default, and the only address from which datagrams are received. If Looks like the test is obsolete. Any objections to remove it? Do people agree that it's a bugfix that can go into 7.4? The test is not obsolete; we understood when we wrote it that it should theoretically be redundant with the kernel-level restriction. But there may be systems out there that fail to make the check promised by the HPUX and Linux manpages. So I'd prefer to leave it in there if we can. I don't want to rip it out simply because we don't understand why it's failing on Adam's setup. What I'm wondering about is whether we are comparing the right number of bytes ... have both address structs been reported to have the same length? Maybe we need a min(). regards, tom lane ---(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] Planning to force reindex of hash indexes
Tom Lane [EMAIL PROTECTED] wrote: I've found a number of infelicities in the hash index code that can't be fixed without an on-disk format change. The biggest one is that the hashm_ntuples field in hash meta pages is only uint32, meaning that hash index space management will become confused if the number of entries exceeds 4G. I'd like to change it to a double, and clean up a couple other uglinesses at the same time. How can we avoid this kind of mess for the future ? Regards Gaetano Mendola ---(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] 64-bit pgsql
-On [20030905 17:22], Jeroen Ruigrok/asmodai ([EMAIL PROTECTED]) wrote: Well, on Itanium2 on FreeBSD 5.1 it compiles. I just need to get the semaphores to a higher value in order to actually do an initdb. Though, did 7.4 raise the bar on SysV IPC? On my other two boxes I haven't tweaked SysV IPC at all (semmni is at 10) and I get initdb. On this Itanium box when I want to initdb I get: creating template1 database in /p/scratch/asmodai/postgresql-7.4beta2/src/test/r egress/./tmp_check/data/base/1... FATAL: could not create semaphores: No space left on device DETAIL: Failed syscall was semget(4, 17, 03600). HINT: This error does *not* mean that you have run out of disk space. It occurs when either the system limit for the maximum number of semaphore sets (SEMMNI), or the system wide maximum number of semaphores (SEMMNS), would be exc eeded. You need to raise the respective kernel parameter. Alternatively, reduc e PostgreSQL's consumption of semaphores by reducing its max_connections paramet er (currently 100). -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ He who has a why to live for can bear almost any how... ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 64-bit pgsql
On Fri, 2003-09-05 at 11:29, Jeroen Ruigrok/asmodai wrote: -On [20030905 17:22], Jeroen Ruigrok/asmodai ([EMAIL PROTECTED]) wrote: Well, on Itanium2 on FreeBSD 5.1 it compiles. I just need to get the semaphores to a higher value in order to actually do an initdb. Though, did 7.4 raise the bar on SysV IPC? On my other two boxes I haven't tweaked SysV IPC at all (semmni is at 10) and I get initdb. Is this beta 1 or beta 2? Beta 1 has a bug which may require more shared resources than what is available. signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Planning to force reindex of hash indexes
Mendola Gaetano [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: I've found a number of infelicities in the hash index code that can't be fixed without an on-disk format change. How can we avoid this kind of mess for the future ? Build a time machine, go back fifteen years, wave a magic wand to increase the IQ levels of the Berkeley grad students? Sometimes we just have to change bad decisions, that's all. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] thread safety
Bruce Momjian [EMAIL PROTECTED] wrote: The thing that slows me down the most --- trips like FOSDEM. I am doing one every month or every other month. That takes 1/4 of each month. The threading discussion took 1/1000 of a month, but I do several hundred of those, so it fills up a month quickly. I understand you, I seen you there just two day and seem that you are someone that is not able to say: NO. I remember that in Bruxelles Peter Eisentraut was there too, why not found some one that replace you? Some time at least :-) Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 64-bit pgsql
Rod Taylor [EMAIL PROTECTED] writes: did 7.4 raise the bar on SysV IPC? On my other two boxes I haven't tweaked SysV IPC at all (semmni is at 10) and I get initdb. Is this beta 1 or beta 2? Beta 1 has a bug which may require more shared resources than what is available. Actually the bug is in beta2, not beta1. I'd suggest grabbing the current nightly snapshot (see /dev on the ftp servers) in preference to beta2, if you are on a machine with small SysV IPC limits. We'll probably push out a beta3 as soon as the current IPv6 mess is sorted. regards, tom lane ---(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] 64-bit pgsql
-On [20030905 17:42], Rod Taylor ([EMAIL PROTECTED]) wrote: Is this beta 1 or beta 2? Beta 1 has a bug which may require more shared resources than what is available. Sorry, beta 2. Should've made that clear. -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ Nothing is more honorable than enlightenment, nothing is more beautiful than virtue... ---(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
[HACKERS] Logging improvements and rehashing
Hello It would be very nice to create a way to log only bad queries, which's resulted in an error. This way a higher loglevel wouldn't be necessary. one other thing, by sending a SIGHUP to the postmaster it reinits it's config files. Now i've made some store functions in C, and it would be very nice, if postgres had re-opened these modules somehow. after overwriting the module with the fresh-compiled version, the first query always crashed the backend. Bye, Gergely Czuczy mailto: [EMAIL PROTECTED] PGP: http://phoemix.harmless.hu/phoemix.pgp The point is, that geeks are not necessarily the outcasts society often believes they are. The fact is that society isn't cool enough to be included in our activities. ---(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] [PATCHES] Warning for missing createlang
Tom Lane wrote:
Andrew Dunstan [EMAIL PROTECTED] writes:
Bruce Momjian wrote:
HINT: Perhaps you need to use 'createlang' to load the language into
the database, or you mistyped the language name.
Why not list out the languages we *do* know about, and tell them it's
not in the list? Or is that too much work?
Seems like it would clutter the error message without really addressing
Bruce's concern. I doubt that seeing the list of available languages
would do much to jog a newbie's memory about needing to run createlang.
We could answer my objection about the hint popping out on misspelled
language names if the code were to arrange to put out the hint only when
the language name is one of plpgsql, pltcl, pltclu, etc. This
would have to use a hard-coded list of loadable language names, since
by definition looking in pg_language won't help. It would be enough of
a maintenance PITA that I don't especially want to do it ... but it
would ensure that the hint is likely to be relevant.
OK, new output is:
test= create function xx() returns int as '
test' select 1'
test- language 'plpgsql';
ERROR: language plpgsql does not exist
HINT: You need to use 'createlang' to load the language into the database.
test= create function xx() returns int as '
test' select 1'
test- language 'XXplpgsql';
ERROR: language xxplpgsql does not exist
Patch attached.
--
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
Index: src/backend/commands/functioncmds.c
===
RCS file: /cvsroot/pgsql-server/src/backend/commands/functioncmds.c,v
retrieving revision 1.33
diff -c -c -r1.33 functioncmds.c
*** src/backend/commands/functioncmds.c 4 Aug 2003 02:39:58 - 1.33
--- src/backend/commands/functioncmds.c 5 Sep 2003 15:56:57 -
***
*** 435,444
PointerGetDatum(languageName),
0, 0, 0);
if (!HeapTupleIsValid(languageTuple))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
!errmsg(language \%s\ does not exist,
languageName)));
!
languageOid = HeapTupleGetOid(languageTuple);
languageStruct = (Form_pg_language) GETSTRUCT(languageTuple);
--- 435,458
PointerGetDatum(languageName),
0, 0, 0);
if (!HeapTupleIsValid(languageTuple))
! {
! /* Add any new languages to this list to invoke the hint. */
! if (strcmp(languageName, plperl) != 0
! strcmp(languageName, plpgsql) != 0
! strcmp(languageName, plpython) != 0
! strcmp(languageName, plr) != 0
! strcmp(languageName, plsh) != 0
! strcmp(languageName, pltcl) != 0)
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
!errmsg(language \%s\ does not exist,
languageName)));
! else
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
!errmsg(language \%s\ does not exist,
languageName),
!errhint(You need to use 'createlang' to load
the language into the database.)));
! }
!
languageOid = HeapTupleGetOid(languageTuple);
languageStruct = (Form_pg_language) GETSTRUCT(languageTuple);
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Seqscan in MAX(index_column)
Would it be possible to catch an unconstrained max(id)/min(id) and rewrite it as select id from table order by id [desc] limit1 on the fly in the parser somewhere? That would require fairly little code, and be transparent to the user. I.e. low hanging fruit. On 5 Sep 2003, Greg Stark wrote: Note that that only handles min()/max() for the whole table. It doesn't handle the GROUP BY case, for that you need DISTINCT ON with an ORDER BY clause. ---(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] Seqscan in MAX(index_column)
scott.marlowe [EMAIL PROTECTED] writes: Would it be possible to catch an unconstrained max(id)/min(id) and rewrite it as select id from table order by id [desc] limit1 on the fly in the parser somewhere? That would require fairly little code, and be transparent to the user. I.e. low hanging fruit. What if there's no index on id? Then it would actually be slower than the straightforward approach. You would have to check both versions and take the one with the lowest cost, or check before rewriting for possible paths on that column. The problem with low hanging fruit is sometimes it makes people stop looking for real solutions. And I think the real solution is worthwhile here. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 64-bit pgsql
-On [20030905 17:52], Tom Lane ([EMAIL PROTECTED]) wrote: Actually the bug is in beta2, not beta1. I'd suggest grabbing the current nightly snapshot (see /dev on the ftp servers) in preference to beta2, if you are on a machine with small SysV IPC limits. Using a snapshot of September the 4th: creating template1 database in /p/scratch/asmodai/postgresql-snapshot/src/test/r egress/./tmp_check/data/base/1... FATAL: could not create semaphores: No space left on device DETAIL: Failed syscall was semget(4, 17, 03600). HINT: This error does *not* mean that you have run out of disk space. It occurs when either the system limit for the maximum number of semaphore sets (SEMMNI), or the system wide maximum number of semaphores (SEMMNS), would be exc eeded. You need to raise the respective kernel parameter. Alternatively, reduc e PostgreSQL's consumption of semaphores by reducing its max_connections paramet er (currently 10). I mean, I just want to know if you guys still want to support the low SysV settings. If not I just need to ask the admins of the system in question to bump the values a bit. Otherwise I'll keep pestering. :) -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ In my mind nothing makes sense... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] C++ and using C functions
Vince Vielhaber [EMAIL PROTECTED] wrote:
On Tue, 2 Sep 2003, Shridhar Daithankar wrote:
On 2 Sep 2003 at 15:50, Czuczy Gergely wrote:
-BEGIN PGP SIGNED MESSAGE-
i'm using pgsql 7.3.4.
how can I fix it? i think so, i should modify the header files, i've
tried
to put it into an ``extern C {...}'' block, but i didn't solve
anything.
Try putting extern C, around include statements for libpq headers.
Is libpq++ gone?
No is still there and is working great but the OP need to write a
SP in C++.
Regards
Gaetano Mendola
---(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] [PATCHES] Warning for missing createlang
Bruce Momjian [EMAIL PROTECTED] writes: We could answer my objection about the hint popping out on misspelled language names if the code were to arrange to put out the hint only when the language name is one of plpgsql, pltcl, pltclu, etc. This would have to use a hard-coded list of loadable language names, since by definition looking in pg_language won't help. It would be enough of a maintenance PITA that I don't especially want to do it ... but it would ensure that the hint is likely to be relevant. OK, new output is: You forgot pltclu, and I believe plpython is now called plpythonu, and I'm not sure whether there's a plperlu, and if you're going to include outside-the-distro languages then I am pretty sure there's a plruby. See what I mean about the maintenance headache this will cause? BTW, duplicating the ereport is no fun. I'd suggest the coding style used in some other places, with errhint called in a conditional expression: ereport(ERROR, (errcode(ERRCODE_UNDEFINED_OBJECT), errmsg(language \%s\ does not exist, languageName), known_language(languageName) ? errhint(You need to use 'createlang' to load the language into the database.) : 0)); where known_language() is a little subroutine that has the strcmp()s. regards, tom lane ---(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] 64-bit pgsql
Jeroen Ruigrok/asmodai [EMAIL PROTECTED] writes: Using a snapshot of September the 4th: creating template1 database in /p/scratch/asmodai/postgresql-snapshot/src/test/r egress/./tmp_check/data/base/1... FATAL: could not create semaphores: No space left on device DETAIL: Failed syscall was semget(4, 17, 03600). I mean, I just want to know if you guys still want to support the low SysV settings. If not I just need to ask the admins of the system in question to bump the values a bit. If it dies even at max_connections 10, that is a *lower* setting than we ever supported before (the pre-7.4 default was 32, and you need 20 or more to run the parallel regression test). I suspect that you actually don't have SysV semaphores enabled at all on that machine. There would be no point in trying any smaller values. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] win32
Are there any web page or docs on win32 porting of pgsql ? Regards Luke
Re: [HACKERS] Planning to force reindex of hash indexes
Tom Lane [EMAIL PROTECTED] wrote: Mendola Gaetano [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: I've found a number of infelicities in the hash index code that can't be fixed without an on-disk format change. How can we avoid this kind of mess for the future ? Build a time machine, go back fifteen years, wave a magic wand to increase the IQ levels of the Berkeley grad students? :-) Sometimes we just have to change bad decisions, that's all. I don't know how much old is the code incriminated but I mean there is no way to improve the code approved ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Stats Collector Error 7.4beta1 and 7.4beta2
Tom Lane wrote: Jan Wieck [EMAIL PROTECTED] writes: Redhat 7.1 says The file descriptor sockfd must refer to a socket. If the socket is of type SOCK_DGRAM then the serv_addr address is the address to which datagrams are sent by default, and the only address from which datagrams are received. If Looks like the test is obsolete. Any objections to remove it? Do people agree that it's a bugfix that can go into 7.4? The test is not obsolete; we understood when we wrote it that it should theoretically be redundant with the kernel-level restriction. But there may be systems out there that fail to make the check promised by the HPUX and Linux manpages. So I'd prefer to leave it in there if we can. I don't want to rip it out simply because we don't understand why it's failing on Adam's setup. What I'm wondering about is whether we are comparing the right number of bytes ... have both address structs been reported to have the same length? Maybe we need a min(). I disagree. If getsockname(), getpeername() or recvfrom() return different address length's, it'd be more an indicator that the addresses ARE different anyway. Just because an IPV4 address doesn't have that feature is no reason to assume that addresses of different length are the same if they match over min() bytes. If you want to continue to check the addresses and feel that HPUX and Linux manpage claims about kernel behaviour aren't enough, then we have to make it a specific check per supported AF. For now, that'd just be AF_INET and AF_INET6, and the default case bailing out with an error. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Logging improvements and rehashing
Czuczy Gergely [EMAIL PROTECTED] writes: It would be very nice to create a way to log only bad queries, which's resulted in an error. This way a higher loglevel wouldn't be necessary. We have that (might be new for 7.4, I forget). i've made some store functions in C, and it would be very nice, if postgres had re-opened these modules somehow. See LOAD. regards, tom lane ---(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] thread safety
[ CC to advocacy.] Mendola Gaetano wrote: Bruce Momjian [EMAIL PROTECTED] wrote: The thing that slows me down the most --- trips like FOSDEM. I am doing one every month or every other month. That takes 1/4 of each month. The threading discussion took 1/1000 of a month, but I do several hundred of those, so it fills up a month quickly. I understand you, I seen you there just two day and seem that you are someone that is not able to say: NO. I can say no if this group tells me it is more important that I focus on PostgreSQL development and not on the speeches. The question is what is going to advance PostgreSQL faster. I remember that in Bruxelles Peter Eisentraut was there too, why not found some one that replace you? Some time at least :-) We do have other people doing events all the time. Comdex will be attended by a lot of our folks. We also have folks attending local user group meetings and making speeches. However, many presentations have special requirements: o full-time employed in PostgreSQL, or willing to take vacation time to make those speeches o ability to speak in front of a large groups and communicate well o ability to create long presentations o someone visible in PostgreSQL who can act as a representative No, not all events require all those items, but many do, and that's why I end up doing them. I don't mind it (it is quite interesting) but it does take me away from managing PostgreSQL. (I have also started taking my 10-year-old son with me, and that is fun.) I almost always offer to give them a local person who can make a presentation in their native language. Sometimes they accept that, but mostly they don't. :-) FYI, I am often able to merge two events into one trip. For the FOSDEM talk, I came from a talk in Japan, and after the Denmark talk in January, I will travel to Paris for a talk on February 3-5. (I will post that event to the PostgreSQL news page now.) Also, consider that I work at home, so I am home 100% of the time when I am not traveling, so the trips aren't as difficult as they would be for someone who is at work for 12 hours every weekday. -- 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 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] win32
luke wrote: Are there any web page or docs on win32 porting of pgsql ? Sure: http://candle.pha.pa.us/main/writings/pgsql/win32.html Let me add that URL to the FAQ. -- 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 8: explain analyze is your friend
Re: [HACKERS] 64-bit pgsql
-On [20030905 18:32], Tom Lane ([EMAIL PROTECTED]) wrote: If it dies even at max_connections 10, that is a *lower* setting than we ever supported before (the pre-7.4 default was 32, and you need 20 or more to run the parallel regression test). I suspect that you actually don't have SysV semaphores enabled at all on that machine. There would be no point in trying any smaller values. The SysV semaphores are enabled all right. As comparison: Itanium box running 5.1 | Athlon box running 4-STABLE kern.ipc.msgmax: 16384 | kern.ipc.msgmax: 16384 kern.ipc.msgmni: 40 | kern.ipc.msgmni: 40 kern.ipc.msgmnb: 2048 | kern.ipc.msgmnb: 2048 kern.ipc.msgtql: 40 | kern.ipc.msgtql: 40 kern.ipc.msgssz: 8 | kern.ipc.msgssz: 8 kern.ipc.msgseg: 2048 | kern.ipc.msgseg: 2048 kern.ipc.semmap: 30 | kern.ipc.semmap: 30 kern.ipc.semmni: 10 | kern.ipc.semmni: 10 kern.ipc.semmns: 60 | kern.ipc.semmns: 60 kern.ipc.semmnu: 30 | kern.ipc.semmnu: 30 kern.ipc.semmsl: 60 | kern.ipc.semmsl: 60 kern.ipc.semopm: 100| kern.ipc.semopm: 100 kern.ipc.semume: 10 | kern.ipc.semume: 10 kern.ipc.semusz: 104| kern.ipc.semusz: 92 kern.ipc.semvmx: 32767 | kern.ipc.semvmx: 32767 kern.ipc.semaem: 16384 | kern.ipc.semaem: 16384 kern.ipc.shmmax: 67108864 | kern.ipc.shmmax: 33554432 kern.ipc.shmmin: 1 | kern.ipc.shmmin: 1 kern.ipc.shmmni: 192| kern.ipc.shmmni: 192 kern.ipc.shmseg: 128| kern.ipc.shmseg: 128 kern.ipc.shmall: 8192 | kern.ipc.shmall: 8192 kern.ipc.shm_use_phys: 0| kern.ipc.shm_use_phys: 0 The same settings, except for kern.ipc.semusz and kern.ipc.shmmax, which are higher than on my box running 7.3.4. So it does seem 7.4 places higher demand than 7.3.4 on the SysV IPC, since initdb also dies on my Athlon box which normally runs a PostgreSQL 7.3.4 database. Like I said before, I am not certain if that was intentional, but if it is a side effect of some change than the docs should emphasize that for this release the SysV IPC dependencies might be a bit heavier than 7.3.4. -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ Believe those who are seeking the truth; doubt those who find it... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Seqscan in MAX(index_column)
Greg Stark [EMAIL PROTECTED] writes: scott.marlowe [EMAIL PROTECTED] writes: Would it be possible to catch an unconstrained max(id)/min(id) and rewrite it as select id from table order by id [desc] limit1 on the fly in the parser somewhere? That would require fairly little code, and be transparent to the user. I.e. low hanging fruit. What if there's no index on id? Then it would actually be slower than the straightforward approach. You would have to check both versions and take the one with the lowest cost, or check before rewriting for possible paths on that column. If the fruit were all that low-hanging, it would've been done before now, as I think this is all that people coming from other DBs expect. But as Greg points out, it's not really a trivial planner change. There are also semantic issues: how shall the planner decide which aggregates are candidates for this treatment (I don't much care for hardwiring some behavior to the names max and min) and how shall it decide which indexes match a given aggregate? In the presence of multiple operator classes for a datatype, it's not obvious whether a btree index has the same sort order that max/min need. If you dig in the pghackers archives you can find some speculation about extending aggregate definitions to associate max/min with appropriate sort operators, but no one's done the legwork to make a concrete proposal, let alone actually code it up. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Stats Collector Error 7.4beta1 and 7.4beta2
On Fri, Sep 05, 2003 at 09:35:11AM -0400, Jan Wieck wrote: Redhat 7.1 says The file descriptor sockfd must refer to a socket. If the socket is of type SOCK_DGRAM then the serv_addr address is the address to which datagrams are sent by default, and the only address from which datagrams are received. If Looks like the test is obsolete. Any objections to remove it? Do people agree that it's a bugfix that can go into 7.4? Reading SUS v2 and v3, both say: If the initiating socket is not connection-mode, then connect() sets the socket's peer address, but no connection is made. For SOCK_DGRAM sockets, the peer address identifies where all datagrams are sent on subsequent send() calls, and limits the remote sender for subsequent recv() calls. So it looks good to me. I do wonder why nobody had a problem with this before however. Kurt ---(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] psql \h alter scrolls of screen
On Thu, 2003-09-04 at 21:14, Bruce Momjian wrote:
When I do '\h alter' in psql, the content scrolls off my screen.
i think you need a bigger screen
Should we be using the pager for \h output?
in 7.3.4 we do, let me check 7.4...seems to work, though I am on beta1
on this box.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Stats Collector Error 7.4beta1 and 7.4beta2
Jan Wieck [EMAIL PROTECTED] writes: Tom Lane wrote: What I'm wondering about is whether we are comparing the right number of bytes ... have both address structs been reported to have the same length? Maybe we need a min(). I disagree. If getsockname(), getpeername() or recvfrom() return different address length's, it'd be more an indicator that the addresses ARE different anyway. Hm, good point. But I still feel that we are jumping to a conclusion without understanding what's going on. I'd like to know *why* the addresses are different on Adam's machine, before we conclude that we mustn't try to check that they are the same. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 64-bit pgsql
Jeroen Ruigrok/asmodai [EMAIL PROTECTED] writes: So it does seem 7.4 places higher demand than 7.3.4 on the SysV IPC, It should not; there is something wrong here, not merely a documentation problem. I am wondering whether your 7.4 build fails to select a TAS() implementation --- if so, it would fall back to implementing spinlocks as semaphores, which would translate to a huge increase in the number of semaphores requested, which would likely result in this symptom. Can you strace the postmaster launch and see how many semget()s it does before dying? regards, tom lane ---(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] Stats Collector Error 7.4beta1 and 7.4beta2
Tom Lane wrote: Jan Wieck [EMAIL PROTECTED] writes: Tom Lane wrote: What I'm wondering about is whether we are comparing the right number of bytes ... have both address structs been reported to have the same length? Maybe we need a min(). I disagree. If getsockname(), getpeername() or recvfrom() return different address length's, it'd be more an indicator that the addresses ARE different anyway. Hm, good point. But I still feel that we are jumping to a conclusion without understanding what's going on. I'd like to know *why* the addresses are different on Adam's machine, before we conclude that we mustn't try to check that they are the same. Agreed. We should know exactly what is happening. My only point earlier is that this has to be fixed for 7.4. -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Warning for missing createlang
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: We could answer my objection about the hint popping out on misspelled language names if the code were to arrange to put out the hint only when the language name is one of plpgsql, pltcl, pltclu, etc. This would have to use a hard-coded list of loadable language names, since by definition looking in pg_language won't help. It would be enough of a maintenance PITA that I don't especially want to do it ... but it would ensure that the hint is likely to be relevant. OK, new output is: You forgot pltclu, and I believe plpython is now called plpythonu, and I'm not sure whether there's a plperlu, and if you're going to include outside-the-distro languages then I am pretty sure there's a plruby. See what I mean about the maintenance headache this will cause? I don't mind the maintenance. I just want people to stop getting stuck creating plpsql functions. Frankly, I don't care if we only test for plpgsql (the most common case). It doesn't have to be perfect --- it only prints a HINT. I willing to put some imperfect code in there to improve usability. BTW, duplicating the ereport is no fun. I'd suggest the coding style used in some other places, with errhint called in a conditional expression: ereport(ERROR, (errcode(ERRCODE_UNDEFINED_OBJECT), errmsg(language \%s\ does not exist, languageName), known_language(languageName) ? errhint(You need to use 'createlang' to load the language into the database.) : 0)); where known_language() is a little subroutine that has the strcmp()s. Here is the new output: test= create function xx() returns int as ' test' select 1' test- language 'XXplpgsql'; ERROR: language xxplpgsql does not exist test= create function xx() returns int as ' test' select 1' test- language 'plpgsql'; ERROR: language plpgsql does not exist HINT: You need to use 'createlang' to load the language into the database. Why does the ': 0' work? I didn't figure that would work, but it does. Patch attached. -- 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 Index: src/backend/commands/functioncmds.c === RCS file: /cvsroot/pgsql-server/src/backend/commands/functioncmds.c,v retrieving revision 1.33 diff -c -c -r1.33 functioncmds.c *** src/backend/commands/functioncmds.c 4 Aug 2003 02:39:58 - 1.33 --- src/backend/commands/functioncmds.c 5 Sep 2003 17:08:48 - *** *** 435,444 PointerGetDatum(languageName), 0, 0, 0); if (!HeapTupleIsValid(languageTuple)) ereport(ERROR, (errcode(ERRCODE_UNDEFINED_OBJECT), !errmsg(language \%s\ does not exist, languageName))); ! languageOid = HeapTupleGetOid(languageTuple); languageStruct = (Form_pg_language) GETSTRUCT(languageTuple); --- 435,456 PointerGetDatum(languageName), 0, 0, 0); if (!HeapTupleIsValid(languageTuple)) + /* Add any new languages to this list to invoke the hint. */ ereport(ERROR, (errcode(ERRCODE_UNDEFINED_OBJECT), !errmsg(language \%s\ does not exist, languageName), ! (strcmp(languageName, plperl) == 0 || ! strcmp(languageName, plperlu) == 0 || ! strcmp(languageName, plpgsql) == 0 || ! strcmp(languageName, plpython) == 0 || ! strcmp(languageName, plpythonu) == 0 || ! strcmp(languageName, plr) == 0 || ! strcmp(languageName, plruby) == 0 || ! strcmp(languageName, plsh) == 0 || ! strcmp(languageName, pltcl) == 0 || ! strcmp(languageName, pltclu) == 0) ? !errhint(You need to use 'createlang' to load the language into the database.) : 0)); ! languageOid = HeapTupleGetOid(languageTuple); languageStruct = (Form_pg_language) GETSTRUCT(languageTuple); ---(end of
Re: [HACKERS] TCP/IP with 7.4 beta2 broken?
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: OK, now we are getting somewhere. I see that this would work. It's a bit ugly, though - with this plan the sample file in both CVS and the installation won't necessarily be what actually get put in place. Well, like I said, it's not real pretty. But the same is already true of postgresql.conf.sample --- initdb edits that. I don't see that having it edit pg_hba.conf.sample too is so bad. What if some clever installer/administrator deliberately alters their installed sample file? I don't think it would hurt them. The editing will consist of a sed script to comment or uncomment the line containg ::1, it wouldn't touch anything else. Could we get the configure script to do it instead, since it too should know about ip6 capability? (I guess then we'd have pg_hba.conf.sample.in). That strikes me as being a lot cleaner. Bruce and I talked about that alternative too, but we felt that it made more sense to keep the processing of pg_hba.conf.sample parallel to what happens to postgresql.conf.sample. Further down the road we might need initdb-time checks to decide what to do to the sample file, just as we already need for postgresql.conf.sample. OK, having it comment out the line for the non-ip6 case seems safe enough. Having it add the line for the ip6 case would be more dangerous ISTM. BTW, who if anyone is rewriting initdb in C? I presume we need that for windows so we are not dependent on having a working shell. andrew ---(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] [PATCHES] Warning for missing createlang
Bruce Momjian [EMAIL PROTECTED] writes: BTW, duplicating the ereport is no fun. I'd suggest the coding style used in some other places, with errhint called in a conditional expression: Why does the ': 0' work? I didn't figure that would work, but it does. The return values of the errxxx() subfunctions don't matter (they all just return zero anyway). What they do is stuff their arguments into a behind-the-scenes data structure that ereport will use when control finally gets to it. ereport is declared as taking a ... argument list, but it makes no attempt to actually look at what was passed as its arguments. So we don't really care what happens when the ?-expression test fails, as long as errhint() doesn't execute. A constant zero is about the minimum thing we can put in the else-part to keep the compiler happy. regards, tom lane ---(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] Question about Scripting in Postgresql.
well let's say that some values get crupted! what postgres does stops the process, I want it to ignore the erro and continue importing the rest of the data into my tables and sent the error to a log file. how could this be done.? I really need to find a way, any sugesstions are welcome, I even thought of garding the data, but still this is dangerous when dealing with tons of data. ThanxRichard Huxton [EMAIL PROTECTED], [EMAIL PROTECTED] wrote: On Friday 05 September 2003 00:24, Nico King wrote:[moving this to pgsql-general] The reason that I have to write a script to enter the data into the tables is that what if I have to enter 1000 lines of data into 200 rows?? here is a piece of my script that works but not when I enter lets' say a char instead of integer. = copy accounts from stdin using delimiters ','; 1,pass,mac,,, 2,pass2,mac2,ip,test 0,pass2,mac2,ip,test2 \. ===Sorry - don't understand. Assuming your values are the right type for the columns that looks OK to me. I have written a script to import some data into my database tables, with the delimiter ','. Now my ques tion is sometime the data being sent to my tables might not match the data type or be corrupted and I receive an error message. One: how could I prevent that?Don't try and put bad data into the batch. It's designed so that if you automate importing batches of data the operation isn't left in some half-done state. Two: how can I proceed with importing the rest of the data into the next record even though some are corrupted,'cause I get intrupted as soon as there is an error in inserting the data?Sounds like you want to write a small Perl script to take your data, strip out anything obviously bad and then insert it in batches. If you have a lot of bad data you can do it one row at a time, if not transactions of say 100 rows at a time might be better.-- Richard HuxtonArchonet Ltd Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software
Re: [HACKERS] TCP/IP with 7.4 beta2 broken?
Andrew Dunstan wrote: OK, having it comment out the line for the non-ip6 case seems safe enough. Having it add the line for the ip6 case would be more dangerous ISTM. BTW, who if anyone is rewriting initdb in C? I presume we need that for windows so we are not dependent on having a working shell. initdb rewrite is on my Win32 project page, but no one has offered yet. Connx offered their version done against 7.X and that is on the web page too. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 64-bit pgsql
-On [20030905 20:52], Tom Lane ([EMAIL PROTECTED]) wrote:
Alternatively, find out what symbols your compiler predeclares.
If my theory is right then your pg_config_os.h file is failing to
define HAS_TEST_AND_SET; why?
Indeed, pg_config_os.h does not set anything for __ia64__.
When I added definitions for Itanium and Opteron to the
src/include/port/freebsd.h (attached) I get the following:
Opteron:
gmake[4]: Entering directory
`/h/scratch/asmodai/postgresql-snapshot/src/backend/access/transam'
gcc -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include
-c -o clog.o clog.c
gcc -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include
-c -o transam.o transam.c
gcc -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include
-c -o varsup.o varsup.c
gcc -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include
-c -o xact.o xact.c
gcc -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include
-c -o xlog.o xlog.c
{standard input}: Assembler messages:
{standard input}:7886: Error: Incorrect register `%eax' used with `b' suffix
gmake[4]: *** [xlog.o] Error 1
Itanium:
gmake[4]: Entering directory
`/q/scratch/asmodai/postgresql-snapshot/src/backend/access/transam'
gcc -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include
-c -o clog.o clog.c
gcc -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include
-c -o transam.o transam.c
gcc -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include
-c -o varsup.o varsup.c
gcc -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include
-c -o xact.o xact.c
gcc -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include
-c -o xlog.o xlog.c
../../../../src/include/storage/s_lock.h: In function `tas':
../../../../src/include/storage/s_lock.h:125: error: inconsistent operand constraints
in an `asm'
gmake[4]: *** [xlog.o] Error 1
On the Alpha box I get 10 out of 92 regression tests failed, see
http://www.in-nomine.org/~asmodai/regressions.diff
I see some are due to locks failed, but it had as much semaphores
available as the other boxes (or as little) and could go through the
entire initdb routine.
--
Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai
PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B
http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/
The wisdom of the wise, and the experience of ages, may be preserved by
quotations...
--- freebsd.h.orig Fri Sep 5 21:38:06 2003
+++ freebsd.h Fri Sep 5 21:41:38 2003
@@ -44,5 +44,14 @@
#if defined(__powerpc__)
#define HAS_TEST_AND_SET
typedef unsigned int slock_t;
+#endif
+#if defined(__ia64__)
+#define HAS_TEST_AND_SET
+typedef unsigned int slock_t;
+#endif
+
+#if defined(__x64_64__)
+#define HAS_TEST_AND_SET
+typedef unsigned int slock_t;
#endif
---(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] Stats Collector Error 7.4beta1 and 7.4beta2
Jan Wieck [EMAIL PROTECTED] writes: Tom Lane wrote: I was about to say I give up, let's just take out the comparison. Which then get's us back to your concern about assuming that HPUX and Linux manpages can be taken as every platform will and hope all kernels will limit the sender for recv() to the connected address. Well, I'd not have cared to trust just those couple of manpages, but if it's in the Single Unix Spec then it's more likely that everyone follows it. Also, I checked my yellowing first edition of Stevens, and it says the same thing: only datagrams from this address will be received by the socket. So I'm thinking that this behavior has been passed down from the original Berkeley sockets code. Since all involved processes are children of the postmaster, we can add some other, random number based security signature into the message itself. Noone outside will know what that is, it's really hard to guess and can be checked with a few int32 compares, not even a function call required. We could do that if we're feeling paranoid, but I'm now leaning to the view that it's not worth the trouble. regards, tom lane ---(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] psql \h alter scrolls of screen
Robert Treat [EMAIL PROTECTED] writes: On Thu, 2003-09-04 at 21:14, Bruce Momjian wrote: Should we be using the pager for \h output? in 7.3.4 we do, let me check 7.4...seems to work, though I am on beta1 on this box. Hmm. I do not see the pager used for \h in either version, though it does get used for \? ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Stats Collector Error 7.4beta1 and 7.4beta2
Jan Wieck [EMAIL PROTECTED] writes: I still believe that this is just garbage in the padding bytes after the IPV4 address. Yes. I have been looking at the behavior on my own Linux box, on which it turns out stats are broken too in CVS tip. It is very clear that getsockname() is returning garbage --- it says that the address length is 16 bytes, but only the first 8 are actually used, and the values put into the other 8 bytes vary from run to run. recvfrom() also returns 16 bytes, but it seems to be careful to zero the extra space. Ugh. The reason 7.4 breaks where 7.3 worked is that 7.3 compared the addresses like this: if (fromaddr.sin_addr.s_addr != pgStatAddr.sin_addr.s_addr) continue; if (fromaddr.sin_port != pgStatAddr.sin_port) continue; where 7.4 has if (memcmp(fromaddr, pgStatAddr, fromlen)) continue; The code currently bind()'s and connect()'s explicitly to an AF_INET address. Not in 7.4. Kurt rewrote that stuff so it would still work on a machine with only IPV6 addressing. It should work for either AF_INET or AF_INET6 sockets. (I'm unconvinced that assuming localhost can be looked up is an improvement over assuming 127.0.0.1 can be used, but that's not our immediate problem.) After reading Kurt's quoting of the SUS manpage I have to agree with Tom in that we cannot skip the check entirely. It says it limits for recv() but we are using recvfrom() ... there might be a little difference on that platform ... I was about to say I give up, let's just take out the comparison. Your point is interesting but easily avoided; if we aren't going to check fromaddr anymore then there's no need to use recvfrom(), it could as well be recv() and save the kernel a few cycles. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] TCP/IP with 7.4 beta2 broken?
Andrew Dunstan [EMAIL PROTECTED] writes: BTW, who if anyone is rewriting initdb in C? I presume we need that for windows so we are not dependent on having a working shell. Not me ;-). Peter replaced a bunch of other scripts with C code for 7.4, but I dunno if he intends to tackle initdb. Someone will probably have to, I agree. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 64-bit pgsql
-On [20030905 19:12], Tom Lane ([EMAIL PROTECTED]) wrote: It should not; there is something wrong here, not merely a documentation problem. I am wondering whether your 7.4 build fails to select a TAS() implementation --- if so, it would fall back to implementing spinlocks as semaphores, which would translate to a huge increase in the number of semaphores requested, which would likely result in this symptom. Can you strace the postmaster launch and see how many semget()s it does before dying? I need to correct one statement Tom, the Athlon box had 3 semaphores in use by another application. Which I killed after that. ipcs then showed it was not using anything and initdb ran ok. The Itanium box was also not using any semaphores and failed. I also did the same on an 5.1-CURRENT Alpha box and it also worked. So I guess the problem lies in the Itanium port. Or could I miss something subtle here? -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ Distance lends enhancement to the view... ---(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] Stats Collector Error 7.4beta1 and 7.4beta2
On a second thought, I still believe that this is just garbage in the padding bytes after the IPV4 address. The code currently bind()'s and connect()'s explicitly to an AF_INET address. So all we ever should see is something from and AF_INET address. Everything else in the sin_family has to be discarded. I do not think it is allowed to bind() and connect() to an IPV4 address and then get anything other than an IPV4 address back from the system. If that is the case, the whole idea is broken. An AF_INET address now has only two relevant fields, the sin_port and sin_addr. If they are the same, everything is fine. So the correct check would be that 1. fromlen sizeof(sin_family), 2. sin_family == AF_INET, 3. sin_port and sin_addr identical. After reading Kurt's quoting of the SUS manpage I have to agree with Tom in that we cannot skip the check entirely. It says it limits for recv() but we are using recvfrom() ... there might be a little difference on that platform ... Jan Wieck wrote: Tom Lane wrote: Jan Wieck [EMAIL PROTECTED] writes: Redhat 7.1 says The file descriptor sockfd must refer to a socket. If the socket is of type SOCK_DGRAM then the serv_addr address is the address to which datagrams are sent by default, and the only address from which datagrams are received. If Looks like the test is obsolete. Any objections to remove it? Do people agree that it's a bugfix that can go into 7.4? The test is not obsolete; we understood when we wrote it that it should theoretically be redundant with the kernel-level restriction. But there may be systems out there that fail to make the check promised by the HPUX and Linux manpages. So I'd prefer to leave it in there if we can. I don't want to rip it out simply because we don't understand why it's failing on Adam's setup. What I'm wondering about is whether we are comparing the right number of bytes ... have both address structs been reported to have the same length? Maybe we need a min(). I disagree. If getsockname(), getpeername() or recvfrom() return different address length's, it'd be more an indicator that the addresses ARE different anyway. Just because an IPV4 address doesn't have that feature is no reason to assume that addresses of different length are the same if they match over min() bytes. If you want to continue to check the addresses and feel that HPUX and Linux manpage claims about kernel behaviour aren't enough, then we have to make it a specific check per supported AF. For now, that'd just be AF_INET and AF_INET6, and the default case bailing out with an error. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] initdb in C
Bruce Momjian wrote: Andrew Dunstan wrote: BTW, who if anyone is rewriting initdb in C? I presume we need that for windows so we are not dependent on having a working shell. initdb rewrite is on my Win32 project page, but no one has offered yet. Connx offered their version done against 7.X and that is on the web page too. I will have a look at doing it. I suspect even with a leg up from the ConnX stuff it might take a little while. cheers andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] C language context
Just how transient is the memory context created for a C language function call? The reason I ask is that I was getting a seg fault when I attempted to pfree something that should have been palloced. When I commented out the calls to pfree it worked fine. Most annoying ;-) I'm still trying to get to the bottom of it, but I want to know if I'm safe in relying on the context cleanup to handle things for me. It's an immutable text function returning text, and is intended for use in a functional index. cheers andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [osdldbt-general] Re: [HACKERS] Prelimiary DBT-2 Test results
On 4 Sep, Manfred Spraul wrote: [EMAIL PROTECTED] wrote: http://developer.osdl.org/markw/44/ I threw together (kind of sloppily) a web page of the data I was starting to collect for our DBT-2 workload (TPC-C derivative) on PostgreSQL 7.3.4. Keep in mind not much database tuning has been done yet. Feel free to ask any questions. The kernel readprofile output is very odd: sys_ipc receives lots of hits, but that function is a trivial multiplexer. sys_timedsemop, and try_atomic_semop got 0 hits - that's the main implementation of sysv semaphores. Could you double check your readprofile scripts? It looks like I have the system.map correct. I'll certainly keep my eyes open and ask around, but seeing poll_idle and schedule on top seem to suggest it's ok. ---(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] Stats Collector Error 7.4beta1 and 7.4beta2
Tom Lane wrote: I was about to say I give up, let's just take out the comparison. Your point is interesting but easily avoided; if we aren't going to check fromaddr anymore then there's no need to use recvfrom(), it could as well be recv() and save the kernel a few cycles. Which then get's us back to your concern about assuming that HPUX and Linux manpages can be taken as every platform will and hope all kernels will limit the sender for recv() to the connected address. Since all involved processes are children of the postmaster, we can add some other, random number based security signature into the message itself. Noone outside will know what that is, it's really hard to guess and can be checked with a few int32 compares, not even a function call required. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(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] 64-bit pgsql
Jeroen Ruigrok/asmodai [EMAIL PROTECTED] writes: So I guess the problem lies in the Itanium port. Or could I miss something subtle here? This strengthens my suspicion that we're not finding TAS code for the Itanium, but please see if you can strace or ktrace the postmaster to verify how many semget()s it tries to do. With max_connections=10 it should really do only one. Alternatively, find out what symbols your compiler predeclares. If my theory is right then your pg_config_os.h file is failing to define HAS_TEST_AND_SET; why? regards, tom lane ---(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] Logging improvements and rehashing
Tom Lane wrote: Czuczy Gergely [EMAIL PROTECTED] writes: It would be very nice to create a way to log only bad queries, which's resulted in an error. This way a higher loglevel wouldn't be necessary. We have that (might be new for 7.4, I forget). Yes, new for 7.4: log_min_error_statement. -- 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
Re: [HACKERS] Planning to force reindex of hash indexes
Mendola Gaetano wrote: Tom Lane [EMAIL PROTECTED] wrote: Mendola Gaetano [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: I've found a number of infelicities in the hash index code that can't be fixed without an on-disk format change. How can we avoid this kind of mess for the future ? Build a time machine, go back fifteen years, wave a magic wand to increase the IQ levels of the Berkeley grad students? :-) Sometimes we just have to change bad decisions, that's all. I don't know how much old is the code incriminated but I mean there is no way to improve the code approved ? I am not sure what you are asking, but the bug existed long before we got it from Berkeley, and even then, it might have slipped by us anyway without our noticing anyway --- we are never going to be perfect. -- 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 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] Warning for missing createlang
Bruce Momjian writes: I don't mind the maintenance. I just want people to stop getting stuck creating plpsql functions. Then put plpgsql in the default installation. -- Peter Eisentraut [EMAIL PROTECTED] ---(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] 64-bit pgsql
Tom Lane wrote: Jeroen Ruigrok/asmodai [EMAIL PROTECTED] writes: So I guess the problem lies in the Itanium port. Or could I miss something subtle here? This strengthens my suspicion that we're not finding TAS code for the Itanium, but please see if you can strace or ktrace the postmaster to verify how many semget()s it tries to do. With max_connections=10 it should really do only one. Alternatively, find out what symbols your compiler predeclares. If my theory is right then your pg_config_os.h file is failing to define HAS_TEST_AND_SET; why? include/storage/s_lock.h has: #if defined(__i386__) || defined(__x86_64__) /* AMD Opteron */ and #if defined(__ia64__) || defined(__ia64) -- 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 8: explain analyze is your friend
Re: [HACKERS] FK type mismatches?
Neil Conway writes: Should this produce a warning? nconway=# create table a (b int4 unique); NOTICE: CREATE TABLE / UNIQUE will create implicit index a_b_key for table a CREATE TABLE nconway=# create table c (d int8 references a (b)); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE TABLE I don't think so. We don't produce warnings in other cases of potential index mismatches either. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Warning for missing createlang
Bruce Momjian [EMAIL PROTECTED] writes: Peter Eisentraut wrote: Bruce Momjian writes: I don't mind the maintenance. I just want people to stop getting stuck creating plpsql functions. Then put plpgsql in the default installation. Fine with me. I thought others didn't want it. There are good security arguments not to have it in the default install, no? I certainly don't think that avoiding having grotty coding for a hint is an argument that trumps our past reasons not to do it. regards, tom lane ---(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] [PATCHES] Warning for missing createlang
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Peter Eisentraut wrote: Bruce Momjian writes: I don't mind the maintenance. I just want people to stop getting stuck creating plpsql functions. Then put plpgsql in the default installation. Fine with me. I thought others didn't want it. There are good security arguments not to have it in the default install, no? I certainly don't think that avoiding having grotty coding for a hint is an argument that trumps our past reasons not to do it. Yes, of course they are independent arguments. Jan isn't comfortable with it being included, and I think that is what has kept it out. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Warning for missing createlang
Peter Eisentraut wrote: Bruce Momjian writes: I don't mind the maintenance. I just want people to stop getting stuck creating plpsql functions. Then put plpgsql in the default installation. Fine with me. I thought others didn't want it. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [PATCHES] Warning for missing createlang
Tom Lane writes: There are good security arguments not to have it in the default install, no? I think last time the only reason we saw was that dump restoring would be difficult. I don't see any security reasons. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Warning for missing createlang
--On Friday, September 05, 2003 22:37:09 +0200 Peter Eisentraut [EMAIL PROTECTED] wrote: Bruce Momjian writes: I don't mind the maintenance. I just want people to stop getting stuck creating plpsql functions. Then put plpgsql in the default installation. Why don't we do that now? It would seem to me to be a good thing(tm) to have it in. LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [HACKERS] FK type mismatches?
Tom Lane writes: If we follow Peter's recently proposed guideline, this would have to be a NOTICE not a WARNING, because the command absolutely is doing what you told it to do. Peter, does that make you uncomfortable? The message itself makes me a bit uncomfortable right now, but a NOTICE absolutely not. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] FK type mismatches?
Peter Eisentraut wrote: Tom Lane writes: If we follow Peter's recently proposed guideline, this would have to be a NOTICE not a WARNING, because the command absolutely is doing what you told it to do. Peter, does that make you uncomfortable? The message itself makes me a bit uncomfortable right now, but a NOTICE absolutely not. Oh, OK, cool. -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Logging improvements and rehashing
On Fri, 2003-09-05 at 16:41, Bruce Momjian wrote:
Tom Lane wrote:
Czuczy Gergely [EMAIL PROTECTED] writes:
It would be very nice to create a way to log only bad queries, which's
resulted in an error. This way a higher loglevel wouldn't be necessary.
We have that (might be new for 7.4, I forget).
Yes, new for 7.4: log_min_error_statement.
Actually it is in 7.3 as well
7.4 does have some new options though, including log_min_messages (or is
that the same as server_min_messages in 7.3?), log_error_verbosity, and
log_min_duration_statement.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] FK type mismatches?
Peter Eisentraut wrote: Neil Conway writes: Should this produce a warning? nconway=# create table a (b int4 unique); NOTICE: CREATE TABLE / UNIQUE will create implicit index a_b_key for table a CREATE TABLE nconway=# create table c (d int8 references a (b)); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE TABLE I don't think so. We don't produce warnings in other cases of potential index mismatches either. The issue is that it isn't likely someone would be doing a foreign key mismatch, while a mismatch in a query would be more likely. We could make it a HINT and then people could configure their servers to suppress the hint if they wish. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] FK type mismatches?
On Fri, 2003-09-05 at 17:06, Peter Eisentraut wrote:
Neil Conway writes:
Should this produce a warning?
nconway=# create table a (b int4 unique);
NOTICE: CREATE TABLE / UNIQUE will create implicit index a_b_key for
table a
CREATE TABLE
nconway=# create table c (d int8 references a (b));
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE TABLE
I don't think so. We don't produce warnings in other cases of potential
index mismatches either.
In all this discussion of NOTICE vs. WARNING, can someone remind me the
logic for INFO? I can't seem to recall the differentiator there either.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Examining the output of: ldd `which postgres`
% ldd `which postgres` /usr/local/bin/postgres: libintl.so.5 = /usr/local/lib/libintl.so.5 (0x282e6000) libz.so.2 = /lib/libz.so.2 (0x282ef000) libreadline.so.4 = /lib/libreadline.so.4 (0x282fd000) libcrypt.so.2 = /lib/libcrypt.so.2 (0x28325000) libm.so.2 = /lib/libm.so.2 (0x2833e000) libutil.so.3 = /lib/libutil.so.3 (0x28357000) libc.so.5 = /lib/libc.so.5 (0x28363000) libiconv.so.3 = /usr/local/lib/libiconv.so.3 (0x2843d000) libncurses.so.5 = /lib/libncurses.so.5 (0x2852c000) Is it really necessary for postgres to be linked with ncurses (288K) and readline (156K)? It's .5M, not the end of the world, but it seems excessive. I know the postmaster has a CLI interface, but does it really require ncurses or readline? -sc -- Sean Chittenden ---(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] Logging improvements and rehashing
Robert Treat wrote: On Fri, 2003-09-05 at 16:41, Bruce Momjian wrote: Tom Lane wrote: Czuczy Gergely [EMAIL PROTECTED] writes: It would be very nice to create a way to log only bad queries, which's resulted in an error. This way a higher loglevel wouldn't be necessary. We have that (might be new for 7.4, I forget). Yes, new for 7.4: log_min_error_statement. Actually it is in 7.3 as well 7.4 does have some new options though, including log_min_messages (or is that the same as server_min_messages in 7.3?), log_error_verbosity, and log_min_duration_statement. Oh, yes, good catch. Thanks. -- 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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Examining the output of: ldd `which postgres`
Sean Chittenden wrote: % ldd `which postgres` /usr/local/bin/postgres: libintl.so.5 = /usr/local/lib/libintl.so.5 (0x282e6000) libz.so.2 = /lib/libz.so.2 (0x282ef000) libreadline.so.4 = /lib/libreadline.so.4 (0x282fd000) libcrypt.so.2 = /lib/libcrypt.so.2 (0x28325000) libm.so.2 = /lib/libm.so.2 (0x2833e000) libutil.so.3 = /lib/libutil.so.3 (0x28357000) libc.so.5 = /lib/libc.so.5 (0x28363000) libiconv.so.3 = /usr/local/lib/libiconv.so.3 (0x2843d000) libncurses.so.5 = /lib/libncurses.so.5 (0x2852c000) Is it really necessary for postgres to be linked with ncurses (288K) and readline (156K)? It's .5M, not the end of the world, but it seems excessive. I know the postmaster has a CLI interface, but does it really require ncurses or readline? -sc We add those to all links, mostly because it is too confusing to do it per link. It doesn't hurt anything because it is dynamically linked, so doesn't take any disk space, and in fact is never called. -- 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
Re: [HACKERS] FK type mismatches?
Robert Treat wrote: On Fri, 2003-09-05 at 17:06, Peter Eisentraut wrote: Neil Conway writes: Should this produce a warning? nconway=# create table a (b int4 unique); NOTICE: CREATE TABLE / UNIQUE will create implicit index a_b_key for table a CREATE TABLE nconway=# create table c (d int8 references a (b)); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE TABLE I don't think so. We don't produce warnings in other cases of potential index mismatches either. In all this discussion of NOTICE vs. WARNING, can someone remind me the logic for INFO? I can't seem to recall the differentiator there either. Right now I see INFO being used mostly for vacuum status. -- 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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] FK type mismatches?
Robert Treat writes: In all this discussion of NOTICE vs. WARNING, can someone remind me the logic for INFO? I can't seem to recall the differentiator there either. Info is something you request explicitly. In the past, the result for EXPLAIN and SHOW were sent as INFO, but now those are sent as query results, and there are in fact very few INFO instances left. Also, INFO is not affect by the log level settings. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Examining the output of: ldd `which postgres`
% ldd `which postgres` /usr/local/bin/postgres: libintl.so.5 = /usr/local/lib/libintl.so.5 (0x282e6000) libz.so.2 = /lib/libz.so.2 (0x282ef000) libreadline.so.4 = /lib/libreadline.so.4 (0x282fd000) libcrypt.so.2 = /lib/libcrypt.so.2 (0x28325000) libm.so.2 = /lib/libm.so.2 (0x2833e000) libutil.so.3 = /lib/libutil.so.3 (0x28357000) libc.so.5 = /lib/libc.so.5 (0x28363000) libiconv.so.3 = /usr/local/lib/libiconv.so.3 (0x2843d000) libncurses.so.5 = /lib/libncurses.so.5 (0x2852c000) Is it really necessary for postgres to be linked with ncurses (288K) and readline (156K)? It's .5M, not the end of the world, but it seems excessive. I know the postmaster has a CLI interface, but does it really require ncurses or readline? -sc We add those to all links, mostly because it is too confusing to do it per link. It doesn't hurt anything because it is dynamically linked, so doesn't take any disk space, and in fact is never called. My concern wasn't for disk space, but for symbol resolution times and unnecessary VM page table space. Does the backend fork() or exec() a copy of itself when a new connection comes in? I thought it was exec() for some reason. Anyway, given how easy it is to change the LDFLAGS, I was thinking about chasing down where postgres is linked and splitting apart LDFLAGS into two sets of LDFLAGS: LDFLAGS_CLI and LDFLAGS (or LDFLAGS_DAEMON, or some such). It's chump, but a few ms here and there, or a little more IO there eventually add up, especially in the arena of on connection times. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [osdldbt-general] Re: [HACKERS] Prelimiary DBT-2 Test results
Another question: Is it possible to apply patches to postgresql before a DBT-2 run, or is only patching the kernel supported? -- Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Examining the output of: ldd `which postgres`
Sean Chittenden wrote: We add those to all links, mostly because it is too confusing to do it per link. It doesn't hurt anything because it is dynamically linked, so doesn't take any disk space, and in fact is never called. My concern wasn't for disk space, but for symbol resolution times and unnecessary VM page table space. Does the backend fork() or exec() a copy of itself when a new connection comes in? I thought it was exec() for some reason. Anyway, given how easy it is to change the LDFLAGS, I was thinking about chasing down where postgres is linked and splitting apart LDFLAGS into two sets of LDFLAGS: LDFLAGS_CLI and LDFLAGS (or LDFLAGS_DAEMON, or some such). It's chump, but a few ms here and there, or a little more IO there eventually add up, especially in the arena of on connection times. Backend only forks(). I think you would be better off using Makefile macros to _remove_ those two libraries. I see this: $(filter crypt.o getaddrinfo.o inet_aton.o snprintf.o strerror.o path.o thread.o, $(LIBOBJS)) Seems you need the reverse. -- 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
Re: [HACKERS] Examining the output of: ldd `which postgres`
We add those to all links, mostly because it is too confusing to do it per link. It doesn't hurt anything because it is dynamically linked, so doesn't take any disk space, and in fact is never called. My concern wasn't for disk space, but for symbol resolution times and unnecessary VM page table space. Does the backend fork() or exec() a copy of itself when a new connection comes in? I thought it was exec() for some reason. Anyway, given how easy it is to change the LDFLAGS, I was thinking about chasing down where postgres is linked and splitting apart LDFLAGS into two sets of LDFLAGS: LDFLAGS_CLI and LDFLAGS (or LDFLAGS_DAEMON, or some such). It's chump, but a few ms here and there, or a little more IO there eventually add up, especially in the arena of on connection times. Backend only forks(). I think you would be better off using Makefile macros to _remove_ those two libraries. I see this: $(filter crypt.o getaddrinfo.o inet_aton.o snprintf.o strerror.o path.o thread.o, $(LIBOBJS)) Seems you need the reverse. Ah, well, if it fork()'s, then I don't really care. The best remaining argument for this would be to reduce the total size of a machine's VM page table size and possibly the expense of switching contexts between procs, but that's a pretty weak argument for only .5M of shared RAM. For some reason I thought it exec()'ed a child with the args necessary for it to read in a postgresql.conf. Looks like the comment in backend/storage/ipc/ipci.c is out of date then: * AttachSharedMemoryAndSemaphores * Attaches to the existing shared resources when exec()'d off * by the postmaster. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [osdldbt-general] Re: [HACKERS] Prelimiary DBT-2 Test results
On 6 Sep, Manfred Spraul wrote: Another question: Is it possible to apply patches to postgresql before a DBT-2 run, or is only patching the kernel supported? The data I reported is from a test system I'm using in our lab, so I can certainly try patches. The current state of STP only allows patches to the kernel, but we're moving in a direction so that other components, like PostgreSQL can also be patched. There is also another option. You can request hardware resources here at the OSDL and get remote access, and we'd be glad to help out set up our workload, if you want to base your tests with it. If that's something you would be interested in all you have to do is sign up as an associate of the OSDL (free): http://www.osdl.org/lab_activities/be_an_associate.html and propose a project: http://www.osdl.org/lab_activities/lab_projects/propose_a_project.html Mark ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Prelimiary DBT-2 Test results
On 4 Sep, Manfred Spraul wrote: [EMAIL PROTECTED] wrote: http://developer.osdl.org/markw/44/ I threw together (kind of sloppily) a web page of the data I was starting to collect for our DBT-2 workload (TPC-C derivative) on PostgreSQL 7.3.4. Keep in mind not much database tuning has been done yet. Feel free to ask any questions. The kernel readprofile output is very odd: sys_ipc receives lots of hits, but that function is a trivial multiplexer. sys_timedsemop, and try_atomic_semop got 0 hits - that's the main implementation of sysv semaphores. Could you double check your readprofile scripts? Someone here was kind enough to run a little test comparing profiles from 2.4.20-19 (a redhat kernel) and 2.6.0-test4-mm5. He found that the 2.6.0-test4-mm5 profile lacked sys_timedsemop and try_atomic_semop. Mark ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Notices for redundant operations
I found a few notices and warnings that inform you that the command you are executing has no effect because the object is already in the state you want it. I think these are useless, and there is also some inconsistency. Does someone want to defend keeping them? = alter table test set without oids; NOTICE: table test is already WITHOUT OIDS = alter table test cluster on test_ix; NOTICE: table test is already being clustered on index test_ix = alter domain foo set not null; NOTICE: foo is already set to NOT NULL [no such notice for altering a column, btw.] = listen foo; WARNING: already listening on foo = alter group test add user peter; WARNING: user peter is already in group test [The SQL analogue with roles does not call for a completion condition in this case.] -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Notices for redundant operations
On Sat, Sep 06, 2003 at 12:47:21AM +0200, Peter Eisentraut wrote: I found a few notices and warnings that inform you that the command you are executing has no effect because the object is already in the state you want it. I think these are useless, and there is also some inconsistency. Does someone want to defend keeping them? If people doesn't receive any message regarding the command they executed, they will execute it again, and again, and they will eventually wonder what's wrong and start investigating why nothing is happening. Warnings should be converted to notices, IMHO. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) The problem with the future is that it keeps turning into the present (Hobbes) ---(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] [PATCHES] Warning for missing createlang
On Fri, 2003-09-05 at 16:54, Bruce Momjian wrote: Peter Eisentraut wrote: Bruce Momjian writes: I don't mind the maintenance. I just want people to stop getting stuck creating plpsql functions. Then put plpgsql in the default installation. Fine with me. I thought others didn't want it. signature.asc Description: This is a digitally signed message part
