Re: [GENERAL] How do I install pl/perl
At 13:06 29/11/00 -0500, Robert B. Easter wrote: >When you compiled PostgreSQL, you have to give ./configure --with-perl so it >will make the .so file its looking for. See ./configure --help next time. > We installed from RPM not source. Do we have to do a re-install from source to get this working ? Steve -- thorNET - Internet Consultancy, Services & Training Phone: 01454 854413 Fax: 01454 854412 http://www.thornet.co.uk
[GENERAL] calling plsql functions
I would like to create a pgplsql function that return a record set. After I spent a couple hours learning how to write plsql functions, but now I'm stuck again. I know how to create the plsql function, but how do I call plsql functions? Any ideas? wooi.
Re: [GENERAL] Trigger firing order
"Alex Bolenok" <[EMAIL PROTECTED]> writes: > peroon=# INSERT INTO t_foo (foo_value) VALUES (2000); > NOTICE: fn_foo_ains: Start > NOTICE: fn_foo_ains: End > NOTICE: fn_bar_ains: Start > NOTICE: fn_bar_ains: End Looking at the code, it seems that all AFTER triggers are implicitly handled as DEFERRED triggers, ie, they're queued up and executed at end of statement. This seems wrong to me --- DEFERRED mode is useful, certainly, but it shouldn't be the only form of AFTER trigger. Also, it'd seem to me that DEFERRED mode ought to mean defer till end of transaction, not just end of statement... Jan, any comments here? regards, tom lane
Re: [GENERAL] Bug? 'psql -l' in pg_ctl?
* Tom Lane <[EMAIL PROTECTED]> [001129 20:22]: > > I'd lean towards a pg_ping (Peter E., any comment here?) > > > Really we'd need to change the postmaster too, because what we need to > > do is send a query "are you ready to accept connections?" that the > > postmaster will answer without an authentication exchange. AFAIR this > > is *not* immediately evident from the postmaster's current behavior --- > > I think it will challenge you for a password even before the startup > > subprocess is done. > > I fixed that today; if the database status is not open-for-business, > the postmaster will tell you so right away instead of making you go > through the authentication protocol first. So a pg_ping could be > written that just sends a connection request packet and sees what > comes back. > > However, if we're running in TRUST or IDENT mode, it's possible that > that technique will lead to launching a backend to no purpose. So > maybe we ought to extend the postmaster protocol to have a "query > status" packet type. Thoughts? I'd also like to see a protocol extension or some such to maybe collect SNMP or other statistical data that could be used later for tuning. If we do a protocol change, let's make it extensible LER > > regards, tom lane -- 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
Re: [GENERAL] Bug? 'psql -l' in pg_ctl?
> I'd lean towards a pg_ping (Peter E., any comment here?) > Really we'd need to change the postmaster too, because what we need to > do is send a query "are you ready to accept connections?" that the > postmaster will answer without an authentication exchange. AFAIR this > is *not* immediately evident from the postmaster's current behavior --- > I think it will challenge you for a password even before the startup > subprocess is done. I fixed that today; if the database status is not open-for-business, the postmaster will tell you so right away instead of making you go through the authentication protocol first. So a pg_ping could be written that just sends a connection request packet and sees what comes back. However, if we're running in TRUST or IDENT mode, it's possible that that technique will lead to launching a backend to no purpose. So maybe we ought to extend the postmaster protocol to have a "query status" packet type. Thoughts? regards, tom lane
Re: [GENERAL] Unanswered questions about Postgre
>> How _much_ longer? (Sorry if it's a FAQ, in that case, I'd appreciate >> a pointer/URL). > Dunno, but I've been using 7.1devel for ~2 months, and so far, > longer rows seem to work fine. > More information on the TOAST project is at > http://www.postgresql.org/projects/devel-toast.html I think I pontificated about this a month or two back, so check the archives; but the short answer is that the effective limit under TOAST is not on the total amount of data in a row, but just on the number of columns. The master copy of the row still has to fit into a block. Worst case, suppose every one of your columns is "wide" and so gets pushed out to BLOB storage. The BLOB pointer that still has to fit into the main row takes 32 bytes. With a maximum main row size of 8K, you can have about 250 columns. In practice, probably some of your columns would be ints or floats or booleans or something else that takes up less than 32 bytes, so the effective limit is probably order-of-magnitude-of 1000 columns in a table. If that seems too small, maybe you need to rethink your database design ;-) There's also a 1G limit on the size of an individual BLOB that can be part of a row. regards, tom lane
Re: [GENERAL] Unanswered questions about Postgre
On 30 Nov 2000, at 1:24, Igor V. Rafienko wrote: > on Nov 29, 2000, 19:17, Joel Burton std::cout'ed: > > [snip] > > | > 5) BLOB Support. > | > | Keep in mind the pgsql 7.1 (about to go beta tomorrow, if I heard | > right) will support much longer row sizes than 8k. Doesn't remove | > the needs for blobs for many of us, but fixed my problems. > > > How _much_ longer? (Sorry if it's a FAQ, in that case, I'd appreciate > a pointer/URL). Dunno, but I've been using 7.1devel for ~2 months, and so far, longer rows seem to work fine. More information on the TOAST project is at http://www.postgresql.org/projects/devel-toast.html -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
[GENERAL] indices
Hi, I had learned in theory that Hash indices are used for "=" and B-tree for "<" ,">". explain command doesn't tell us which index it is using. Hash or Btree? Also, should a following query "id < 1243" invoke a index ? (assuming there is an index on id). I have seen Postgres using Seq scan. Is sequential scan done afer getting the first page for "1243"? Sandeep
Re: [GENERAL] Unanswered questions about Postgre
on Nov 29, 2000, 19:17, Joel Burton std::cout'ed: [snip] | > 5) BLOB Support. | | Keep in mind the pgsql 7.1 (about to go beta tomorrow, if I heard | right) will support much longer row sizes than 8k. Doesn't remove | the needs for blobs for many of us, but fixed my problems. How _much_ longer? (Sorry if it's a FAQ, in that case, I'd appreciate a pointer/URL). ivr -- Intelligence est rélative. Par rapport à T*, c'est un génie. -- James Kanze sur "Smart Pointer"
Re: [GENERAL] Unanswered questions about Postgre
[re: question #4, speed/vacuuming] > Do > people need to vaccume their databases hourly? Can you vaccume while > a database is in use? Any discussion on this curious phenomenon would > be appreciated. It still boggles me. I vacuum twice a day, once in the dead of night, once around lunch. Yes, you can vacuum while the db is in use, but many locks (for updates, inserts, etc.) will hold up the vacuum. > 5) BLOB Support. Keep in mind the pgsql 7.1 (about to go beta tomorrow, if I heard right) will support much longer row sizes than 8k. Doesn't remove the needs for blobs for many of us, but fixed my problems. I believe the docs discussing the c-level interfaces talk about lo creation and such. Have you looked in the low-level docs in the programmer/developer manuals? I have only played w/blobs; others can speak better about their use/limitations, but if I have it correct: . blobs cannot be dumped . blobs are not normally vacuumed So, for most of us, I think the TOAST feature of 7.1 that allows >8k row sizes is much nicer. (Unless, of course, you really want to store binary data, not just long text fields.) Good luck, -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] Warning: Don't delete those /tmp/.PGSQL.* files
GH <[EMAIL PROTECTED]> writes: > running this script with "start" causes the postgres server to start, > run out of files, and then shutdown. Postgres is useable until it runs > out of files and shuts down. Continuing on that line of thought --- it seems like this must be an indication of a file-descriptor leak somewhere. That is, some bit of code forgets to close a file it opened. Cycle through that bit of code enough times, and the kernel stops being willing to give you more file descriptors. If this is correct, we could probably identify the leak by knowing what file is being opened multiple times. Can you run 'lsof' or some similar tool to check for duplicate descriptors being held open by the postmaster? I recall that we have fixed one or two leaks of this kind in the past, but I don't recall details, nor which versions the fixes first appeared in. regards, tom lane
Re: [GENERAL] how to determine what a process is doing
Alex Howansky <[EMAIL PROTECTED]> writes: > Is there any way to determine exactly what a postgres process is > doing at any time? The output from the ps command only shows "INSERT" or > "SELECT", and not the full query string. There isn't any really nice solution at the moment, but you could run the postmaster with -d2 to cause writing of all queries to the postmaster's log file (ie, its stdout/stderr). You'd probably also want to compile with ELOG_TIMESTAMPS defined (see include/config.h) to get timestamps and process PIDs included in the log. That'd give you info to correlate against what "top" shows. regards, tom lane
Re: [GENERAL] ExecRestrPos: node type 18 not supported
"kurt miller" <[EMAIL PROTECTED]> writes: > I'm getting these log entries: > ExecRestrPos: node type 18 not supported > ExecMarkPos: node type 18 not supported You're hitting the nested-mergejoin bug that was discovered awhile ago. Aside from the log messages, you are probably getting wrong answers :-(. Update to 7.0.3 to get the fix. regards, tom lane
[GENERAL] how to determine what a process is doing
I've looked in the docs, Bruce's book, and the list archives, but I've been unable to find an answer to this. Any help would be greatly appreciated. I have a database front-ended by a web site. All queries (apart from a few cron jobs and developer's manual tests) come from the web site through PHP. Lately, my database server's load average has been spiking badly. There may be 50 concurrent queries running, and top shows that maybe three or four of them are really sucking up the horsepower. I'd like to find out what these hog processes are actually processing -- but it could be any one of a few hundred different queries. Is there any way to determine exactly what a postgres process is doing at any time? The output from the ps command only shows "INSERT" or "SELECT", and not the full query string. TIA, -- Alex Howansky Wankwood Associates http://www.wankwood.com/
[GENERAL] ExecRestrPos: node type 18 not supported
I'm getting these log entries: ExecRestrPos: node type 18 not supported ExecMarkPos: node type 18 not supported Any idea what they mean? TIA, -km _ Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com
Re: [GENERAL] Bug? 'psql -l' in pg_ctl?
Peter Eisentraut <[EMAIL PROTECTED]> writes: >> Really we'd need to change the postmaster too, because what we need to >> do is send a query "are you ready to accept connections?" that the >> postmaster will answer without an authentication exchange. > ISTM that the rejection of a client with authentication failure is a > pretty good indicator that you're accepting connections. Well, no, it means that the postmaster is alive. It doesn't mean the database is open for business yet --- the startup subprocess might still be running. I've just tweaked the postmaster so that startup/shutdown state is checked immediately upon receiving the startup-request packet, and if there's a database-state reason for rejecting the connection, that will happen before going through the authentication protocol. This should make it easier to write a pg_ping. regards, tom lane
Re: [GENERAL] Database cluster?
> > I am considering splitting the database into tables residing on separate > > machines, and connect them on one master node. > > > > The question I have is: > > > > 1) How can I do this using PostgreSQL? > > You can't. I'll jump in with a bit more info. Splitting tables across multiple machines would do nothing more than make the entire system run at a snail's pace. Yes, it would slow it down immensely, because you just couldn't move data between machines quickly enough. Why? Well, whenever you join two tables that are on different machines, the tables have to go across whatever sort of connection you have between the machines. Even if you use gigabit ethernet, you are still running at a mere fraction of the bandwidth of the computer's internal bus - and at orders of magnitude greater latency. You'd have lots of CPU's sitting around, doing absolutely nothing, waiting for data to come across the wire. There are alternatives, such as IP-over-SCSI. That reduces the latency of ethernet quite a bit, and gives you much more bandwidth (say, up to 160 megabytes/second). However, that's still a pittance compared to the main system bus inside your computer. That's one of the greatest hurdles to distributed computing. That's why the applications that are best adapted to distributed computing are those that don't require much data over the wire - which certainly doesn't apply to databases. : ) steve
