Re: [GENERAL] Can't start PostgreSQL on Fedora Core3
Lonni J Friedman wrote: selinux-policy-targeted-1.17.30-2.85 up2date insists that these packages are all up to date. And you're certain that you're using the targetted policy and not strict? To be frank, I am not. I have not the slightest idea what all that selinux is about (beside security in general). I just went with the default install and here I am. Ulrich -- Ulrich Wisser RELEVANT TRAFFIC SWEDEN AB, Riddarg 17A, SE-114 57 Sthlm, Sweden Direct (+46)86789755 || Cell (+46)704467893 || Fax (+46)86789769 http://www.relevanttraffic.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Can't start PostgreSQL on Fedora Core3
Hi Tom, sudo /sbin/restorecon -R /var/lib/pgsql worked like a charm! Thank you very much! Ulrich -- Ulrich Wisser RELEVANT TRAFFIC SWEDEN AB, Riddarg 17A, SE-114 57 Sthlm, Sweden Direct (+46)86789755 || Cell (+46)704467893 || Fax (+46)86789769 http://www.relevanttraffic.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] postgresql on gentoo
It took me a while, but I finally figured out that the citext module did not require a recompile, it just required all the compile information, specifically the config.status file, which wasn't there. After I extracted the sources and ran ./configure for postgresql, the contrib module compiled very nicely. I would think that emerge would keep all those sometimes needed configuration files in the directory with the Makefile "Sim Zacks" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I just installed postgresql on a new gentoo server using emerge and I would > like to add the citext module for a case insensitive text type. Emerge does > not have a citext option in postgresql or package. > Does anyone know how to get the citext into postgresql on gentoo and will > this break the next time I upgrade postgresql? > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Disabling triggers in a transaction
[Here's my third attempt to post this. Sorry if it's a dup. (trip?)]
If you're willing to modify your triggers you can gain per-session
control over any and all triggers and functions.
For example, suppose I have a trigger that logs certain events, but I
also want to be able to turn off logging while I embezzle the
funds^H^H^H^H^H^H^H^H do maintenance. I still want the logging trigger
to work for other clients, just not mine. I also want transaction
support, so if I disable logging, then rollback, logging will be turned
back on in my next transaction. Like this:
Usage Example:
BEGIN;
SELECT disable_logging();
UPDATE some_table ...;
if (check_error()) {
// Don't have to remember to enable_logging()
ROLLBACK;
}
SELECT enable_logging();
COMMIT;
The catch is, my logging trigger must be changed to look like this:
BEGIN
IF logging_enabled() THEN
-- Do logging
END IF;
END;
It takes advantage of the fact that temporary tables can only be seen in
the session that creates them. You create a real 'session_vars' table
with default values and a flag that can tell you if you are looking at
the real or temporary table. Then copy it into a temporary table and
reset your flag to mark it as such. You can then update other flags in
your temporary table that are only seen by the current session. So,
when you disable_logging(), you'll get FALSE from logging_enabled(), but
all other sessions will get TRUE.
---
CREATE TABLE session_vars (
id INT PRIMARY KEY,
valueBOOL NOT NULL,
description CHAR(20)
);
---
INSERT INTO session_vars(id, value, description)
VALUES (1, TRUE, 'table is non-temp');
INSERT INTO session_vars(id, value, description)
VALUES (2, FALSE, 'logging enabled');
---
CREATE FUNCTION setup_session_vars() RETURNS BOOLEAN AS '
BEGIN
-- We''ll only ever get TRUE from the real table ...
IF session_vars_is_real() THEN
EXECUTE \'CREATE TEMPORARY TABLE session_vars AS
SELECT * FROM session_vars\';
-- ... and FALSE from the temporary table
EXECUTE \'UPDATE session_vars SET value=FALSE WHERE id=1\';
END IF;
RETURN TRUE;
END;
' LANGUAGE plpgsql VOLATILE;
---
CREATE FUNCTION session_vars_is_real() RETURNS BOOLEAN AS
'SELECT value FROM session_vars WHERE id=1;'
LANGUAGE SQL STABLE;
---
CREATE FUNCTION logging_enabled() RETURNS BOOLEAN AS
'SELECT value FROM session_vars WHERE id=2;'
LANGUAGE SQL STABLE;
---
CREATE FUNCTION enable_logging() RETURNS BOOLEAN AS '
DECLARE
r RECORD;
BEGIN
PERFORM setup_session_vars();
IF NOT logging_enabled() THEN
UPDATE session_vars SET value=TRUE WHERE id=2;
END IF;
RETURN TRUE;
END;
' LANGUAGE plpgsql VOLATILE;
---
CREATE FUNCTION disable_logging() RETURNS BOOLEAN AS '
BEGIN
PERFORM setup_session_vars();
UPDATE session_vars SET value=FALSE WHERE id=2;
RETURN TRUE;
END;
' LANGUAGE plpgsql VOLATILE;
---
CREATE FUNCTION log_trigger() RETURNS trigger AS '
BEGIN
IF logging_enabled() THEN
--do_logging;
END IF;
RETURN NEW; --or something
' LANGUAGE plpgsql VOLATILE;
Hope that helps,
Adam Tomjack
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Disabling triggers in a transaction
>Net Virtual Mailing Lists wrote: >> All I did was added an extra column to my table (I called it >> "batch_process"). Then in >> the trigger do something like (in whichever function you are calling): >> >> IF NEW.batch_update IS NOT NULL AND NEW.batch_update = ''t'' THEN >> NEW.batch_process := NULL; >> RETURN NULL; >> END IF; >> .. whatever the rest of transaction is > >Why don't you just set it to false instead of NULL? Wouldn't that reduce >the condition to just "IF NEW.batch_update THEN ..."? In that case you >should default the column to false of course, or the condition will >always fail (the value being NULL). >Personally, I would use a more descriptive name for the column, >'disable_triggers' or something like that. > Yeah, that's one improvement I meant to make but just haven't gotten around to it.. It is just the way this thing got written the first time during and testing and the "oh! It worked!" realization.. ;-) >Also, I find it more convenient to use "true" and "false" instead of >having to escape "'t'" and "'f'" all the time ;) > Yeah.. ;-) >> Then when doing an insert, just: >> >> INSERT INTO table (..., batch_process) VALUES (..., 't') when you want the >> trigger not to fire... >> >> Or an update: >> >> UPDATE TABLE table SET , batch_process = 't' >> >> >> I'm not sure sure how to make it work on a function called from a delete >> trigger though.. ;-( > >The drawbacks of this method are that you'll have to modify all your >queries when you want to disable triggers (though that can usually be >solved programatically), and that only the triggers that "support" this >method of disabling will be actually disabled. > It seems like you would have to do something programatically anyways in order to say "Okay, now I want to disable the triggers -- go do something" >If you work at the same project with multiple people who all write >triggers from time to time, or when you have to deal with legacy code >from an older database, I think you'll run into trouble with the above >quicker than you'd like. However, if you manage to get this into the >design fase of a project it'll probably work just fine (the delete >problem you mentioned aside...). > I think any of the solutions I've seen mentioned so far would present the same problem. :-( >-- >Alban Hertroys >MAG Productions > >T: +31(0)53 4346874 >F: +31(0)53 4346876 >E: [EMAIL PROTECTED] >W: http://www.magproductions.nl > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostgreSQL still for Linux only?
Jim C. Nasby wrote: Personally, I find the anti-windows bias that has been shown in this thread by some developers to be disappointing. Maybe it sucks to program in, and maybe it's not as stable as unix (though I don't put much water in that argument anymore), but the fact is there's still a LOT of places that are windows shops and a LOT of people who use windows more heavily than *nix. More important, the egotism of "If you want to use PostgreSQL you better run it on what we tell you to run it on" is certain to turn people off of PostgreSQL. It will certainly turn off windows developers who might have been interested in working to improve PostgreSQL now that it runs on windows. Ok--- I will admit to a anti-Windows bias. But at least my bias is informed. In addition to my former employment at Microsoft, I have studies both types of OS's in detail. Here are some specific comments I would make: 1) I do not expect PostgreSQL to *ever* perform as well on Windows as it does on Linux. This is primarily due to the fundamentally different emphasis in kernel architecture between UNIX-style and VMS-style operating systems. Windows server applications which are process-based are always likely to underperform. Windows applications ported to Linux are similarly likely to underperform. 2) Windows stability is getting far better, but does still lag behind that of Linux. 3) I think that it is very likely that you might be legally required to get CAL's for Windows Server in order to allow the systems to access PostgreSQL. While this is not enforced by the OS, I don't know whether the EULA requires it (my guess is that it does). PostgreSQL on Windows has 2 uses. It is for developers to play around with, and it is for smaller businesses with few connections to use. One you need to scale, you will probably have to go to Linux, BSD, etc. Best Wishes, Chris Travers ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] BitTorrent tracker
Yes, that fixed it. Marc G. Fournier wrote: Not sure what I need to start on the server, so David will have to check it over ... the only thing that is in the auto-start script on the srever itself, though, I've just restarted, so try now and let me know if that was the only thing I needed ot hit ... On Thu, 10 Mar 2005, David Mitchell wrote: Hi, It would appear the bittorrent tracker is down or broken. I can't download anything from postgresql (but I can from other torrents), and I can't seed either. -- David Mitchell Software Engineer Telogis NOTICE: This message (including any attachments) contains CONFIDENTIAL INFORMATION intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 -- David Mitchell Software Engineer Telogis NOTICE: This message (including any attachments) contains CONFIDENTIAL INFORMATION intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] partitionning
Scott Marlowe <[EMAIL PROTECTED]> writes: > Yes, it is a way. It's just a less necessary one than it once was, with > hardware now able to provide the same performance increase with little > or no work on the users part. We've got to weigh the increased > complexity it would take to implement it in Postgresql and maintain it > versus the gain, and I say the gain is smaller every day. Now I think you're contradicting your argument in the other subthread. It's certainly *much* more complex to have to implement this yourself for each table than to have it as a native postgres feature. So I think you're saying using partitioning in any form, whether native or home-brew, is better because of the simplicity. But if that's the argument then you're wrong about the high end controllers making this less urgent. High end hardware controllers only make it easier to gather the kind of data that requires some form of partitioning in one form or another to make it manageable. In any case partitioning offers algorithmic improvements in performance. No matter how fast your controller is it's not going to be able to delete 100G of data and match the speed of simply dropping a partition using DDL. Partitioning is something DBAs are doing more and more often as the data sets grow. And it's something Postgres DBAs are doing more and more often as Postgres moves into problem domains that were previously the domain of Oracle and DB2 DBAs. The only choice is whether they're doing it by kludging a failure-prone and suboptimal system or whether it's built into the database in a reliable, convenient, and well designed form. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] partitionning
Scott Marlowe <[EMAIL PROTECTED]> writes: > Wait, I'm not sure I understand your point here yet. Are you saying > something along the lines of that with a 1TB storage array, and putting > all the data in one big partitions, the DBAs had problems, but when they > partitioned it down to say 10 100G partitions, and split up the data set > across it they had fewer problems? I'm not sure we're talking about the same thing here. Partitioned tables are typically used when the data falls naturally into different buckets and the different buckets need to be treated differently. Especially when all the data in a single bucket often needs to be moved en masse. A typical scenarios are for accounting systems that need to make past year's books read-only or archive detail records for past years en masse. Without partitioning you're stuck doing massive deletes to archive the old data for an entire year. The bigger your database (and I imagine if you're paying $100k+ for a storage subsystem you have a pretty massive database) the more such data you're going to be trying to delete at once. A similar situation also arises when you have to reload this data -- either from a backup or for a DSS system backing your primary database. If you want to access the data in a single normalized table you're stuck doing massive inserts to move the data around. With partitioned tables you can pull an entire partition out of the table with a single DDL command. It's instantaneous, it doesn't even have to look at the data in the partition. Similarly you can load data into your table quickly (once it's been loaded into the database that is.) I think it's obvious that these features are particularly useful for large databases. Especially ones that have the ability to grow very quickly or load lots of data from elsewhere very quickly. Ie, precisely the ones that have humongous fast storage subsystems. Yes you can implement this using views of unions in postgres. Or you can implement it using inherited tables. But implementing it by hand has disadvantages. Oracle already went through this. They had DBAs using union views as early as version 7. They even had an optimizer hack that you could enable to take advantage of it. But it was a tremendous kludge. The real support they added in 8.1 is *much* better. > Was this some kind of massive batch processing system? Actually it was a web site, so an OLTP database. About 1-3M new records per day in the most important table. Before we implemented partitioning we had a regular job that spent 10+ hours deleting old records. If it failed in any way things became very unhappy quickly. After partitioning we could create a new partition and move the old partition out of the table at peak hours. Then we could back up and drop the table containing the old partition's data at our leisure. > While I'm sure administration overhead is the major problem, I'm > wondering what other ones you've seen, i.e. performance, reliability, > user error, that are more common on a home rolled partitioning. Well we never even tried to implement it as a home brew solution. I certainly would have been pretty afraid of trying anything so complex myself with live data. > But what I'm really saying is that between good home grown partitioning > and fast hardware, the need for the pg devel team to implement > partitioning is pretty low. Ah. I thought you were saying that the fast hardware made partitioning in any form unnecessary. Not merely that it made home brew partitioning an acceptable solution. But that's a bit of a silly proviso though isn't it? I mean you could do anything with enough plpgsql code and fast enough hardware. The real question is where is the best place for this to be implemented. Issuing a single atomic command sure makes me feel much better about something than trying to set up a half dozen triggers/rules on a view and hoping I get it all set up right. Especially when you think that I'll probably have to do this for several tables at the same time. Actually I have a strong feeling what really _ought_ to happen here is that the inherited tables support in postgres, which never really worked anyways, should be deprecated and eventually removed. All that infrastructure should be repurposed into partitioned tables. That seems like it would be a nice fit. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] BitTorrent tracker
Not sure what I need to start on the server, so David will have to check it over ... the only thing that is in the auto-start script on the srever itself, though, I've just restarted, so try now and let me know if that was the only thing I needed ot hit ... On Thu, 10 Mar 2005, David Mitchell wrote: Hi, It would appear the bittorrent tracker is down or broken. I can't download anything from postgresql (but I can from other torrents), and I can't seed either. -- David Mitchell Software Engineer Telogis NOTICE: This message (including any attachments) contains CONFIDENTIAL INFORMATION intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] BitTorrent tracker
Hi, It would appear the bittorrent tracker is down or broken. I can't download anything from postgresql (but I can from other torrents), and I can't seed either. -- David Mitchell Software Engineer Telogis NOTICE: This message (including any attachments) contains CONFIDENTIAL INFORMATION intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] PostgreSQL still for Linux only?
The beauty of an open source, BSD-licensed project like PostgreSQL is the entire "Who cares?" possibility list. If you have a Windows shop and you have Windows trained personnel, then you can use PostgreSQL. If you have a Linux shop and Linux trained personnel, then you can use PostgreSQL. If you have a FreeBSD shop and FreeBSD trained personnel, then you can use PostgreSQL. I think a picture is starting to form here. Monkey-wrench time... Suppose that I have a 4-way AMD64 Windows system running PostgreSQL and even that runs out of steam. I have added as much ram as the system will hold and the load is still causing problems. Now, I can get an IBM machine running SUSE with a pile of processors and gobs of ram and scale to whatever TPS I need. And the data + schema? Dump from the Windows box, load on the IBM SUSE box. I might even be able to SLONY it over without ever going off line. IOW -- what't the whole point of open source BSD licensed projects? It's that you just do whatever you like to solve the problem in the way that is best for your organization (with your personnel and your hardware and your training and your data). And if you need to scale to somewhere else, then you can do it. It's the best of all worlds. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL still for Linux only?
Quoting "Joshua D. Drake" <[EMAIL PROTECTED]>: > > > > > >The only additional thing I would add to this if it hasn't been mentioned > >already is that 2000 had/has some major security issues and even though 2003 > is > >more secure out of the box from what I've experienced so far, I would > **never** > >trust a windows box to anything other than my LAN using private IP blocks > and if > >it has inbound access via a public IP then it would more certainly be > behind > >another firewall that is NAT'ing/Port Forwarding its traffic. > > > > > Nobody should ever put a server regardless of OS on a public IP. > It should always be firewalled/Nat/Port Forwarding. > > Sincerely, > > Joshua D. Drake > > > > > -- > Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC > Postgresql support, programming shared hosting and dedicated hosting. > +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com > PostgreSQL Replicator -- production quality replication for PostgreSQL > > As with all things technology there is an art too it as well- several ways to do things. I don't, for instance, NAT/Port forward public interfaces for Linux hosts because in my experience they can be hardened without much ambiguity to be placed there. Similarly, I don't feel the same is true with most of the windows variants so for security sake increased an network complexity is justified. My point is that along with the performance issues this thread has point out, data security is another reason to consider a non-windows platform to run your production database. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] partitionning
On Wed, 2005-03-09 at 14:47, Greg Stark wrote: > Scott Marlowe <[EMAIL PROTECTED]> writes: > > > Actually, I think it is the more common scenario of migrating off of > > oracle or db2 and onto postgresql, and bringing along the experience > > gained there over the years that has caused this refrain to sprout up > > more and more often. With a database sitting on an enterpries class > > storage subsystem with 8 gigs of battery backed cache onboard, the needs > > for partitioning are less and less necessary. Not completely so, and > > there are times when they can come in handy (i.e. when you have to "roll > > your own" on a more limited budget). > > You don't think the people using Oracle are even *more* likely to have an big > storage subsystem with gobs of cache? At my previous job we had a Hitachi > system that was really ludicrously fast. Nonetheless when we implemented > partitioning it was a real life saver for the DBAs. Their workload went from > being >50% dealing with problems with the large weekly jobs to basically being > able to ignore these jobs. In fact the job to handle the changover was moved > to the middle of the peak period because it was just more convenient that way. Wait, I'm not sure I understand your point here yet. Are you saying something along the lines of that with a 1TB storage array, and putting all the data in one big partitions, the DBAs had problems, but when they partitioned it down to say 10 100G partitions, and split up the data set across it they had fewer problems? Or are you talking more about quick access because when you have a table with three fields, and one is rather narrow, that field can be accessed much quicker because you aren't dragging around the extra fields you don't need? Was this some kind of massive batch processing system? > The bigger the storage the *more* important partitioning is. Not less. That's > why it's such a huge feature for Oracle and it's why databases used on smaller > projects don't find it a compelling feature. > > > I'm note sure what your point about purging and loading is. A properly > > built home rolled partitioning setup reqiures none of that. > > Well that's sort of the point. But home rolled partitioning setups have other > problems. That's why it would be good to have a solid implementation that > didn't have these problems. While I'm sure administration overhead is the major problem, I'm wondering what other ones you've seen, i.e. performance, reliability, user error, that are more common on a home rolled partitioning. > > I really didn't see anything in your post that argued against my point > > that a large enterprise class raid array largely eliminates the needs > > for application level partitioning of data. > > Firstly it's not application level if it's native. sorry, by application, I meant Postgresql client layer. I.e. implemented in the user layer of postgresql, not in the core of it. > Well, so you're saying that you believe me that on my 1GB database I find it > more convenient to be able to pull off 100M of data instantaneously and > without generating any garbage for vacuum to clean up. But that you don't > believe someone running a 1TB storage subsystem would appreciate the same > feature as much when they have to pull off 10GB of data because their system > is 10x faster at doing this unnecessary work than mine would be, so it only > takes 100x as much time? Why in the world would a view encased union of several tables generate garbage for a vacuum to clean up? I'd think a well rolled view/union/trigger based solution would operated identically to a postgresql internally implemented partitioning system. But what I'm really saying is that between good home grown partitioning and fast hardware, the need for the pg devel team to implement partitioning is pretty low. ---(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: [GENERAL] partitionning
Scott Marlowe <[EMAIL PROTECTED]> writes: > Actually, I think it is the more common scenario of migrating off of > oracle or db2 and onto postgresql, and bringing along the experience > gained there over the years that has caused this refrain to sprout up > more and more often. With a database sitting on an enterpries class > storage subsystem with 8 gigs of battery backed cache onboard, the needs > for partitioning are less and less necessary. Not completely so, and > there are times when they can come in handy (i.e. when you have to "roll > your own" on a more limited budget). You don't think the people using Oracle are even *more* likely to have an big storage subsystem with gobs of cache? At my previous job we had a Hitachi system that was really ludicrously fast. Nonetheless when we implemented partitioning it was a real life saver for the DBAs. Their workload went from being >50% dealing with problems with the large weekly jobs to basically being able to ignore these jobs. In fact the job to handle the changover was moved to the middle of the peak period because it was just more convenient that way. The bigger the storage the *more* important partitioning is. Not less. That's why it's such a huge feature for Oracle and it's why databases used on smaller projects don't find it a compelling feature. > I'm note sure what your point about purging and loading is. A properly > built home rolled partitioning setup reqiures none of that. Well that's sort of the point. But home rolled partitioning setups have other problems. That's why it would be good to have a solid implementation that didn't have these problems. > Witness how often OTHER issues pop up (=NULL versus IS NULL, autonomous > transactions, quotas) that are from oracle land nowadays. It isn't that > the Oracle way is always the best way, it's just what folks are often > used to. =NULL is an Access thing actually. But yes, these other features are also things that the bigger boys need. But the most common requested one these days seems to be partitioning. Maybe I'm biased though. > I really didn't see anything in your post that argued against my point > that a large enterprise class raid array largely eliminates the needs > for application level partitioning of data. Firstly it's not application level if it's native. The postgres options such as inherited tables or union views do indeed impose application level constraints, but a good native implementation is completely transparent to the programmer. Well, so you're saying that you believe me that on my 1GB database I find it more convenient to be able to pull off 100M of data instantaneously and without generating any garbage for vacuum to clean up. But that you don't believe someone running a 1TB storage subsystem would appreciate the same feature as much when they have to pull off 10GB of data because their system is 10x faster at doing this unnecessary work than mine would be, so it only takes 100x as much time? -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL still for Linux only?
Quoting "Joshua D. Drake" <[EMAIL PROTECTED]>: > Shelby Cain wrote: > > >--- "Uwe C. Schroeder" <[EMAIL PROTECTED]> wrote: > > > > > > > >>>The problem is, that it's a question of > >>> > >>> > >>perception. Most windows fans don't > >> > >> > >>>see that "their" OS is pretty instable. > >>> > >>> > > > >That may have been true in 1995. However, in this day > >and age most Windows fans don't see that their OS as > >unstable because it isn't - unless of course you are > >referring to the non-NT variations. > > > > > O.k. I don't want to start an OS war here. However > there are a couple of things I know. > > 1. As of Windows 2000, Windows is reasonably stable. > However there is a caveat, it still can not perform > under load (read slowness, possible crash) like Linux > or other UNIX variants can. > > 2. As of Windows 2003, Windows is very stable and > performs fairly well under load. However it still > can not keep up with Linux or other UNIX variants. > > The majority of the problem with Windows in these > days is people who hire other people with little > pieces of paper that say they are knowledgeable. > > A properly managed Windows server can be reliable, > can perform reasonably well, if you have the expertise > to do so. This is not that much unlike UNIX. The difference > is that UNIX requires the expertise, Windows makes you > feel like you have it when you don't. > > Sincerely, > > Joshua D. Drake > > > > > > >Regards, > > > >Shelby Cain > > > > > > > > > >__ > >Celebrate Yahoo!'s 10th Birthday! > >Yahoo! Netrospective: 100 Moments of the Web > >http://birthday.yahoo.com/netrospective/ > > > >---(end of broadcast)--- > >TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > > > > > > -- > Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC > Postgresql support, programming shared hosting and dedicated hosting. > +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com > PostgreSQL Replicator -- production quality replication for PostgreSQL > > The only additional thing I would add to this if it hasn't been mentioned already is that 2000 had/has some major security issues and even though 2003 is more secure out of the box from what I've experienced so far, I would **never** trust a windows box to anything other than my LAN using private IP blocks and if it has inbound access via a public IP then it would more certainly be behind another firewall that is NAT'ing/Port Forwarding its traffic. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL still for Linux only?
The only additional thing I would add to this if it hasn't been mentioned already is that 2000 had/has some major security issues and even though 2003 is more secure out of the box from what I've experienced so far, I would **never** trust a windows box to anything other than my LAN using private IP blocks and if it has inbound access via a public IP then it would more certainly be behind another firewall that is NAT'ing/Port Forwarding its traffic. Nobody should ever put a server regardless of OS on a public IP. It should always be firewalled/Nat/Port Forwarding. Sincerely, Joshua D. Drake -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] segmentation fault
Lonni , folks > Full meaning all the rows are being updated? What > kind of data is > this that you're updating? the fts engine using one tsvector field indexing with gist index the statement is something like this update from veryLargeTable set field = to_tsvector( coleace(field1) ); any indication to improve the one configuration the DB. thanks for your support. best regards MDC ___ 250MB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostgreSQL still for Linux only?
> I thank you all for throwing light on the question I asked. [missed it earlier, not been reading that lists mail] > I was exchanging mails with one of the developers on > PgFoundry. He made a comment and said > > 'Is anybody using PostgreSQL on Windows?'. Yes. I know of several fairly large production installations running on win32. I don't have one myself at the moment, though. > I began to wonder, was the Windows version a toy? No. It does *NOT* have the same performance as the Unix version. In some tests it comes fairly close. If your application does lots of connect/disconnects, we *know* it is *always* significantly slower. This can be mitigated by using connection pooling. Write intensive apps are significantly slower, but we hope to have that fixed in 8.0.2. The Windows version is also new. Therefor, one can expect there to be more problems with it. Both performance-wise and stability-wise. > From your views, I can conclude that I must not go near using > or deploying PostgreSQL on production Windows servers. Thanks > for that information. But I was of the opinion that > perfection comes out of practice and that certain crashes and > experimentation(s) would lead to a better product adaptation. I personally wouldn't go that far. You should be fine to deploy postgresql on win32. Just not very large installations, and you may need to pay a bit more attention to your backups. Again, there *are* production deployments on the win32 version. Some are pretty large. Several have been running since beta1 (at least one since before beta1) without significant problems. Also, keep in mind that the postgresql community does not have a lot of experience with the win32 port either. So if you run into problem, there aren't as many experienced people around. Yet. Feel free to become one. //Magnus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL still for Linux only?
[EMAIL PROTECTED] (Tope Akinniyi) writes: > I am wondering at this display of extreme Linux mentality being > displayed by the 'top bras' of the PostgreSQL community. And I ask, > are we encouraging Windows use of PostgreSQL at all? > Take a look at tools being rolled out at PgFoundry on daily basis; > all for Linux except the Windows installer. I ask myself what is > being done to encourage PostgreSQL Windows users. Nothing is > available to them except the Database and PgAdmin. No replication > tool, no this, no that. When people interested in deploying on Windows(tm) start contributing code to the projects, then the tide may "turn." Looking at what I'm working on (Slony-I), that is indeed the requirement in order for Slony-I to be supported on Windows. I don't use Windows(tm) in any context, so I lack all of all of the following prerequisites: a) Interest b) Platform knowledge c) Development tools I wouldn't oppose the notion of someone with Windows(tm) interest, Windows(tm) knowledge, and access to Windows(tm) development tools contributing support for their platform. But someone else will have to bring those things to the table. You are NOT going to be forcing me to start doing Windows(tm) development for any of my bits of the PostgreSQL software base; the only way to get my bits ported is to find some interested Windows(tm) developer. And if nobody is sufficiently interested to do so, that obviously means that there _isn't_ that much interest in supporting Windows(tm) for Slony-I. Much the same is quite likely to be true for numerous of the PgFoundry projects. > Sorry for this: Firebird provides equal tools for Linux and Windows > users. We are not the one to tell the Windows users whether they > need them. It took a LOT of years for the Windows tools to emerge; InterBase spent a number of years as a Unix-only application. > Whether Windows is bad or good; Linux is the angel and Windows the > devil is not the issue here. PostgreSQL has gone the Windows way and > must not be shown to be deficient. > > I am not holding anybody responsible, but I think we need to do a > massive re-orientation of the community not to carry the > Linux-Windows game too far. This is NOT an issue of the "goodness/badness" of Windows, and is CERTAINLY NOT a matter of Linux being considered an "angel," as numerous of the PostgreSQL developers are no more fans of Linux than they are of Windows(tm). It is nonsense to consider it some "Linux/Windows game," particularly when most of the PostgreSQL Core prefer BSD 4.4-Lite variants. [My metric there is that I have seen numerous cases of Core members who develop on FreeBSD and NetBSD, whereas I am not yet specifically aware of any that prefer Linux.] It is a matter that in order for additional applications to be deployed on Windows(tm), it is necessary to find developers that are familiar with the platform that are interested in doing the deployment. If the set of people that come from the "Windows(tm) world" are largely 'plain users' that have limited interest in helping develop improvements, then PostgreSQL will certainly remain with a STRONG Unix bias in what gets developed, and that's pretty much fair. -- let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];; http://www.ntlug.org/~cbbrowne/linuxxian.html A VAX is virtually a computer, but not quite. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostgreSQL still for Linux only?
Joshua, Very well put ! Cheers, Aly. Joshua D. Drake wrote: Shelby Cain wrote: --- "Uwe C. Schroeder" <[EMAIL PROTECTED]> wrote: The problem is, that it's a question of perception. Most windows fans don't see that "their" OS is pretty instable. That may have been true in 1995. However, in this day and age most Windows fans don't see that their OS as unstable because it isn't - unless of course you are referring to the non-NT variations. O.k. I don't want to start an OS war here. However there are a couple of things I know. 1. As of Windows 2000, Windows is reasonably stable. However there is a caveat, it still can not perform under load (read slowness, possible crash) like Linux or other UNIX variants can. 2. As of Windows 2003, Windows is very stable and performs fairly well under load. However it still can not keep up with Linux or other UNIX variants. The majority of the problem with Windows in these days is people who hire other people with little pieces of paper that say they are knowledgeable. A properly managed Windows server can be reliable, can perform reasonably well, if you have the expertise to do so. This is not that much unlike UNIX. The difference is that UNIX requires the expertise, Windows makes you feel like you have it when you don't. Sincerely, Joshua D. Drake Regards, Shelby Cain __ Celebrate Yahoo!'s 10th Birthday! Yahoo! Netrospective: 100 Moments of the Web http://birthday.yahoo.com/netrospective/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Aly Dharshi [EMAIL PROTECTED] "A good speech is like a good dress that's short enough to be interesting and long enough to cover the subject" ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] partitionning
On Wed, 2005-03-09 at 13:21, Joshua D. Drake wrote: > Scott Marlowe wrote: > > >On Wed, 2005-03-09 at 10:31, FERREIRA William (COFRAMI) wrote: > > > > > >>hi > >> > >>does the table partitionning exists under PostgreSQL ? or maybe an > >>alternative exists ? > >> > >>Oracle implements this system : it allows to spread rows on differents > >>partitions depending on an attribute. > >>For example, my application store xml documents in database, an the > >>partitionning is used to spread the differents documents on differents > >>partitions. > >> > >> > > > >Currently, doing this in PostgreSQL is more of a "roll your own" thing. > >you create the base tables on different table spaces, then create an > >updatable view with the proper triggers to make sure that the incoming > >records go to the right tables. > > > >With the advent of very large raid arrays with very fast caching > >controllers, this methodology is becoming less and less necessary. > > > > > I don't agree with this. There are many postgresql users > who have 100, 200, 600 GB databases that don't have the budget > to purchase a 20,000 array. A Good AMI Megaraid card with 512 meg of battery backed cache will work the same here. I.e. eliminate the need for partitioning. it doesn't have to cost $20,000 to do it. And let's never forget that the data is where the value is, not the hardware. Notice I wasn't saying there was never a need anyway, just that it's becoming less of an issue each day. which it is. Back in the day a decent one scsi interface RAID card with battery backed cache was >$2,000. nowadays, they've dropped to the $300 to $500 range. I can't see the cost of a DBA figuring out and implementing partitioning schemes costing less than that in time. Plus it puts your data on a more reliable system. Penny wise and pound foolish to go cheap in my opinion. Anyone keeping 600 gigs of data and trying to save $500 in hardware costs is saving costs in the wrong places, imnsho... > Table partitioning is a way to keep things efficient. That > should be regardless of technology. Yes, it is a way. It's just a less necessary one than it once was, with hardware now able to provide the same performance increase with little or no work on the users part. We've got to weigh the increased complexity it would take to implement it in Postgresql and maintain it versus the gain, and I say the gain is smaller every day. > RAM is cheap, so lets just use as much of it as we can even > if it means we swap. What does table partitioning have to do with RAM? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] segmentation fault
Lonni , folks configuration data: sorry for long response ;) Linux sume 2.6.9-gentoo-r1 #1 SMP Mon Feb 21 10:46:46 Local time zone must be set--see zic i686 Intel(R) Xeon(TM) CPU 2.80GHz GenuineIntel GNU/Linux postgres: "PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 20040623 (Gentoo Linux 3.3.4-r1, ssp-3.3.2-2, pie-8.7.6)" postgresql.conf: # - # PostgreSQL configuration file # - # # This file consists of lines of the form: # # name = value # # (The '=' is optional.) White space may be used. Comments are introduced # with '#' anywhere on a line. The complete list of option names and # allowed values can be found in the PostgreSQL documentation. The # commented-out settings shown in this file represent the default values. # # Any option can also be given as a command line switch to the # postmaster, e.g. 'postmaster -c log_connections=on'. Some options # can be changed at run-time with the 'SET' SQL command. # # This file is read on postmaster startup and when the postmaster # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use # "pg_ctl reload". #--- # CONNECTIONS AND AUTHENTICATION #--- # - Connection Settings - #tcpip_socket = false max_connections = 100 # note: increasing max_connections costs about 500 bytes of shared # memory per connection slot, in addition to costs from shared_buffers # and max_locks_per_transaction. #superuser_reserved_connections = 2 #port = 5432 #unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 # octal #virtual_host = '' # what interface to listen on; defaults to any #rendezvous_name = '' # defaults to the computer name # - Security & Authentication - #authentication_timeout = 60# 1-600, in seconds #ssl = false #password_encryption = true #krb_server_keyfile = '' #db_user_namespace = false #--- # RESOURCE USAGE (except WAL) #--- # - Memory - shared_buffers = 8192 # min 16, at least max_connections*2, 8KB each sort_mem = 8192 # min 64, size in KB #vacuum_mem = 8192 # min 1024, size in KB # - Free Space Map - #max_fsm_pages = 2 # min max_fsm_relations*16, 6 bytes each #max_fsm_relations = 1000 # min 100, ~50 bytes each # - Kernel Resource Usage - #max_files_per_process = 1000 # min 25 #preload_libraries = '' #--- # WRITE AHEAD LOG #--- # - Settings - fsync = false # turns forced synchronization on or off #wal_sync_method = fsync# the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync wal_buffers = 40# min 4, 8KB each # - Checkpoints - checkpoint_segments = 9 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 300 # range 30-3600, in seconds #checkpoint_warning = 30# 0 is off, in seconds #commit_delay = 0 # range 0-10, in microseconds #commit_siblings = 5# range 1-1000 #--- # QUERY TUNING #--- # - Planner Method Enabling - #enable_hashagg = true #enable_hashjoin = true #enable_indexscan = true #enable_mergejoin = true #enable_nestloop = true #enable_seqscan = true #enable_sort = true #enable_tidscan = true # - Planner Cost Constants - effective_cache_size = 4000 # typically 8KB each #random_page_cost = 4 # units are one sequential page fetch cost #cpu_tuple_cost = 0.01 # (same) #cpu_index_tuple_cost = 0.001 # (same) #cpu_operator_cost = 0.0025 # (same) # - Genetic Query Optimizer - #geqo = true #geqo_threshold = 11 #geqo_effort = 1 #geqo_generations = 0 #geqo_pool_size = 0 # default based on tables in statement, # range 128-1024 #geqo_selection_bias = 2.0 # range 1.5-2.0 # - Other Planner Options - #default_statistics_target = 10 # range 1-1000 #from_collapse_limit = 8 #join_collapse_limit = 8# 1 disables collapsing of explicit JOINs #--- # ERROR REPORTING AND LOGGING #--- # - Syslog - #syslog = 0 # range 0-2; 0=stdout; 1=both; 2=syslog #syslog_facility = 'LOCAL0' #s
Re: [GENERAL] segmentation fault
On Wed, 9 Mar 2005 16:26:53 -0300 (ART), marcelo Cortez <[EMAIL PROTECTED]> wrote: > Lonni , folks > > > > > You're doing this from within psql or elsewhere? > from putty terminal from windows statiom That doesn't really answer the question. How are you interfacing with the database? Are you using a client, such as psql, or something else? putty just means that you're ssh'ing to the server, but once you're there, how are you connecting to the DB and issuing this update? > > What logging level > default i' guest >From your postgresql.conf i think i saw a debug5 for min_messages. That is not typically a default for most people. Admittedly your word wrap in your MUA is rather narrow (like 35 chars) so parsing through it wasn't the easiest in the world. > > are you using? How 'massive' is this update? > full , the update statement not have filter clause. Full meaning all the rows are being updated? What kind of data is this that you're updating? -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(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: [GENERAL] segmentation fault
>From your postgresql.conf it look like you're running with a default config, which is very likely unsuitable for the dataset and/or usage patterns that you have. Granted that should not cause a seg fault, but who knows what kind of weirdness is going on behind the scenes. I didn't see any info from you on what kind of 'massive' update you're performing that triggers this behavior, or even confirmation of where you're seeing it (in psql or elsewhere?). Are you updating all of the millions of rows at once? What kind of data is this that you're updating? On Wed, 9 Mar 2005 16:22:39 -0300 (ART), marcelo Cortez <[EMAIL PROTECTED]> wrote: > Lonni , folks > > configuration data: sorry for long response ;) > > Linux sume 2.6.9-gentoo-r1 #1 SMP Mon Feb 21 10:46:46 > Local time zone > must > be set--see zic i686 Intel(R) Xeon(TM) CPU 2.80GHz > GenuineIntel > GNU/Linux > > postgres: > "PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by > GCC gcc (GCC) 3.3.4 > 20040623 (Gentoo Linux 3.3.4-r1, ssp-3.3.2-2, > pie-8.7.6)" > > postgresql.conf: > > # - > # PostgreSQL configuration file > # - > # > # This file consists of lines of the form: > # > # name = value > # > # (The '=' is optional.) White space may be used. > Comments are > introduced > # with '#' anywhere on a line. The complete list of > option names and # > allowed values can be found in the PostgreSQL > documentation. The # > commented-out settings shown in this file represent > the default values. > # > # Any option can also be given as a command line > switch to the > # postmaster, e.g. 'postmaster -c log_connections=on'. > Some options # > can > be changed at run-time with the 'SET' SQL command. > # > # This file is read on postmaster startup and when the > postmaster > # receives a SIGHUP. If you edit the file on a running > system, you have > # > to SIGHUP the postmaster for the changes to take > effect, or use > # "pg_ctl reload". > > #--- > # CONNECTIONS AND AUTHENTICATION > #--- > > # - Connection Settings - > > #tcpip_socket = false > max_connections = 100 > # note: increasing max_connections costs about > 500 bytes of > shared > # memory per connection slot, in addition to costs > from > shared_buffers # and max_locks_per_transaction. > #superuser_reserved_connections = 2 > #port = 5432 > #unix_socket_directory = '' > #unix_socket_group = '' > #unix_socket_permissions = 0777 # octal > #virtual_host = '' # what interface to > listen on; defaults > to > any #rendezvous_name = '' # defaults to the > computer name > > # - Security & Authentication - > > #authentication_timeout = 60# 1-600, in seconds > #ssl = false > #password_encryption = true > #krb_server_keyfile = '' > #db_user_namespace = false > > #--- > # RESOURCE USAGE (except WAL) > #--- > > # - Memory - > > shared_buffers = 8192 # min 16, at least > max_connections*2, > 8KB > each sort_mem = 8192 # min 64, size in > KB > #vacuum_mem = 8192 # min 1024, size in KB > > # - Free Space Map - > > #max_fsm_pages = 2 # min > max_fsm_relations*16, 6 bytes > each > #max_fsm_relations = 1000 # min 100, ~50 bytes > each > > # - Kernel Resource Usage - > > #max_files_per_process = 1000 # min 25 > #preload_libraries = '' > > #--- > # WRITE AHEAD LOG > #--- > > # - Settings - > > fsync = false # turns forced > synchronization on or > off > #wal_sync_method = fsync# the default varies > across platforms: > # fsync, fdatasync, > open_sync, or > open_datasync > wal_buffers = 40# min 4, 8KB each > > # - Checkpoints - > > checkpoint_segments = 9 # in logfile segments, > min 1, 16MB each > #checkpoint_timeout = 300 # range 30-3600, in > seconds > #checkpoint_warning = 30# 0 is off, in seconds > #commit_delay = 0 # range 0-10, in > microseconds > #commit_siblings = 5# range 1-1000 > > #--- > # QUERY TUNING > #--- > > # - Planner Method Enabling - > > #enable_hashagg = true > #enable_hashjoin = true > #enable_indexscan = true > #enable_mergejoin = true > #enable_nestloop = true > #enable_seqscan = true > #enable_sort = true > #enable_tidscan = true > > # - Planner Cost Constants - > > effective_cache_size = 4000 # typically 8KB each > #rando
Re: [GENERAL] segmentation fault
Lonni , folks > > You're doing this from within psql or elsewhere? from putty terminal from windows statiom > What logging level default i' guest > are you using? How 'massive' is this update? full , the update statement not have filter clause. > kind of parameters the update statement call fts function. > have you set for this database? next email y send this information. best regards MDC ___ 250MB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] partitionning
On Wed, 2005-03-09 at 13:07, Greg Stark wrote: > Scott Marlowe <[EMAIL PROTECTED]> writes: > > > With the advent of very large raid arrays with very fast caching > > controllers, this methodology is becoming less and less necessary. > > I think the evidence is to the contrary. Witness the rather dramatic surge in > inquiries about this on this list. A year ago there were only two or three of > us pining for this feature. Now it's a weekly refrain. > > Very large very fast raid arrays just mean that people want to gather that > much more data. They would still like to make the best use of their hardware > and not waste their resources on tremendously inefficient purging and loading > procedures when it would be possible to do these things instantaneously. That > only becomes more important as the investment they want to leverage becomes > larger. Actually, I think it is the more common scenario of migrating off of oracle or db2 and onto postgresql, and bringing along the experience gained there over the years that has caused this refrain to sprout up more and more often. With a database sitting on an enterpries class storage subsystem with 8 gigs of battery backed cache onboard, the needs for partitioning are less and less necessary. Not completely so, and there are times when they can come in handy (i.e. when you have to "roll your own" on a more limited budget). I'm note sure what your point about purging and loading is. A properly built home rolled partitioning setup reqiures none of that. it's just that whereas Oracle does it semi-automagically, the postgresql dba sets up the equivalent by hand. No purging or loading that I'm aware of is needed. Witness how often OTHER issues pop up (=NULL versus IS NULL, autonomous transactions, quotas) that are from oracle land nowadays. It isn't that the Oracle way is always the best way, it's just what folks are often used to. I really didn't see anything in your post that argued against my point that a large enterprise class raid array largely eliminates the needs for application level partitioning of data. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] partitionning
Scott Marlowe wrote: On Wed, 2005-03-09 at 10:31, FERREIRA William (COFRAMI) wrote: hi does the table partitionning exists under PostgreSQL ? or maybe an alternative exists ? Oracle implements this system : it allows to spread rows on differents partitions depending on an attribute. For example, my application store xml documents in database, an the partitionning is used to spread the differents documents on differents partitions. Currently, doing this in PostgreSQL is more of a "roll your own" thing. you create the base tables on different table spaces, then create an updatable view with the proper triggers to make sure that the incoming records go to the right tables. With the advent of very large raid arrays with very fast caching controllers, this methodology is becoming less and less necessary. I don't agree with this. There are many postgresql users who have 100, 200, 600 GB databases that don't have the budget to purchase a 20,000 array. Table partitioning is a way to keep things efficient. That should be regardless of technology. RAM is cheap, so lets just use as much of it as we can even if it means we swap. Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Pgsql dynamic statements and null values
Ragnar Hafstað wrote: > On Tue, 2005-03-08 at 16:30 -0600, Guy Rouillier wrote: >> We use a dynamic statement in a pgsql stored function to insert rows >> into a table determined at run time. After much debugging, I've >> discovered that a null incoming argument will cause the dynamic >> statement to evaluate to null. The error message emitted is "unable >> to execute null statement." > > can't you use COALESCE() ? Thanks Ragnar and Martijn for the replies. NULLIF doesn't seem applicable here as I already have a null value coming in, so I'm not comparing it to anything. I had tried COALESCE before my original post and it produced the same result: cannot execute null statement. However, your prompting motivated me to try a couple more alternatives. Of the many I tried, here is one that works: coalesce(quote_literal(inval), 'NULL') Nice to know, but given the verbosity, I think I'll stick with my check_null(inval). A worthwhile exercise, though, since I can now reduce that function to this one line. -- Guy Rouillier ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] partitionning
Scott Marlowe <[EMAIL PROTECTED]> writes: > With the advent of very large raid arrays with very fast caching > controllers, this methodology is becoming less and less necessary. I think the evidence is to the contrary. Witness the rather dramatic surge in inquiries about this on this list. A year ago there were only two or three of us pining for this feature. Now it's a weekly refrain. Very large very fast raid arrays just mean that people want to gather that much more data. They would still like to make the best use of their hardware and not waste their resources on tremendously inefficient purging and loading procedures when it would be possible to do these things instantaneously. That only becomes more important as the investment they want to leverage becomes larger. -- greg ---(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: [GENERAL] Can't start PostgreSQL on Fedora Core3
Tom Lane wrote: One problem is that selinux-policy-targeted updates don't necessarily propagate to the security labels of the individual files. Sounds like it might be a good idea to add a trigger to the PostgreSQL RPM to run restorecon when the SELinux policy is updated. -- Ian Pilcher[EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostgreSQL still for Linux only?
tony wrote: Excuse me dear sir. There seems to be about 97% of the world that runs Windows that does not give you permission to be rude to a tiny minority who just happen to have written an insanely great database that runs quite nicely on their "hobby" OSs as well as the crap you call home. If you aren't pleased with the postgresql support on Windows don't use it!!! That is your freedom. Ours is to think (maybe wrongly) that it is much better running it on the BSDs and Linux of our choice. That is our freedom. There is nothing egoist about developing a great database server on an OS with a tiny user base. The egoists are elsewhere dear sir, far from the free software developers, in the closed source world. The code is there, it is free - go and improve it. Maybe you need a dictionary to look up the word egoist? Please go and troll over at MySQL. They have a Windows version too and maybe a lot more time and patience for rude people such as yourself. Tony This thread is getting a bit carried away, don't you think? If this keeps up, these fora run the risk of turning into the gong show that the c.d.oracle.* newsgroup frequently becomes. If you think it's a legitimate flame, why not ignore it, instead of adding to the noise? ---(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: [GENERAL] PostgreSQL still for Linux only?
On Wed, 9 Mar 2005, tony wrote: > Le mercredi 09 mars 2005 à 09:47 -0800, Ben a écrit : > > Ho ho, flame on! :) > > Hear hear!!! This man is a troll if ever we have seen one. Who? Jim Nasby? He's made several helpful posts to this list in my memory, and I'm sure an archive search would turn up a lot more. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] postgresql on gentoo
Sim Zacks wrote: I just installed postgresql on a new gentoo server using emerge and I would like to add the citext module for a case insensitive text type. Emerge does not have a citext option in postgresql or package. No, because it's not part of the postgres tarball. But the Gentoo ebuilds _do_ make and install everything that's in /contrib. Does anyone know how to get the citext into postgresql on gentoo and will this break the next time I upgrade postgresql? Either download and compile it by hand as outlined in it's readme (you'll have to untar your postgres sources from /usr/portage/distfiles and copy it to the contrib dir), or write an ebuild to do it for you. But if you're going to do a lot of stuff that involves compiling postgres, I'd recommend taking postgres out of portage's control. That way you've always got your own source tree to work from and you can tweak configure however you want. Just be sure to configure with --prefix=/usr/local, so it's out the way of your portage-installed stuff. Matt ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL still for Linux only?
I thank you all for throwing light on the question I asked. I was exchanging mails with one of the developers on PgFoundry. He made a comment and said 'Is anybody using PostgreSQL on Windows?'. I began to wonder, was the Windows version a toy? I head a software development outfit in Nigeria and our environment in predominantly Windows. People using Oracle, MSSQL and the likes on Windows. I thought PostgreSQL would be a substitute for such being an Open Source believer - Till now I have deplored much of Firebird for my clients on Windows. Well, much without much headaches I must say. >From your views, I can conclude that I must not go near using or deploying PostgreSQL on production Windows servers. Thanks for that information. But I was of the opinion that perfection comes out of practice and that certain crashes and experimentation(s) would lead to a better product adaptation. But I think Tim Allen's comment is quite unexpected and unfortunate. Tim Allen wrote: >Perhaps it's a 419 :-). But if so I can't see the catch yet - must be very subtle. What is 419 about expressing a concern and an interest? So because I posted from Nigeria and my view is a bit not aligned with your own I am a fraudster? I do not think we are in a chat room. Even if you must joke you need to avoid sentimental words. Thank you all. I think the matter is closed. --- Best regards, Tope Akinniyi CEO ShepherdHill Software Lagos, Nigeria Do not forget: Jesus said, I am the way, the truth and the life. Send instant messages to your online friends http://uk.messenger.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] PostgreSQL still for Linux only?
Le mercredi 09 mars 2005 à 09:47 -0800, Ben a écrit : > Ho ho, flame on! :) Hear hear!!! This man is a troll if ever we have seen one. > > Personally, I find the anti-windows bias that has been shown in this > > thread by some developers to be disappointing. Maybe it sucks to program > > in, and maybe it's not as stable as unix (though I don't put much water > > in that argument anymore), but the fact is there's still a LOT of places > > that are windows shops and a LOT of people who use windows more heavily > > than *nix. More important, the egotism of "If you want to use PostgreSQL > > you better run it on what we tell you to run it on" is certain to turn > > people off of PostgreSQL. It will certainly turn off windows developers > > who might have been interested in working to improve PostgreSQL now that > > it runs on windows. Excuse me dear sir. There seems to be about 97% of the world that runs Windows that does not give you permission to be rude to a tiny minority who just happen to have written an insanely great database that runs quite nicely on their "hobby" OSs as well as the crap you call home. If you aren't pleased with the postgresql support on Windows don't use it!!! That is your freedom. Ours is to think (maybe wrongly) that it is much better running it on the BSDs and Linux of our choice. That is our freedom. There is nothing egoist about developing a great database server on an OS with a tiny user base. The egoists are elsewhere dear sir, far from the free software developers, in the closed source world. The code is there, it is free - go and improve it. Maybe you need a dictionary to look up the word egoist? Please go and troll over at MySQL. They have a Windows version too and maybe a lot more time and patience for rude people such as yourself. Tony ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL still for Linux only?
I don't think so, if you consider a troll to be someone who doesn't care about the topic, but rather wishes to stir up newbies and flamers. A search of the archives shows the sender has a history of asking valid questions and offering advice on-topic. That said, the result is the same. Cheers, Rick Geoffrey <[EMAIL PROTECTED]>To: pgsql-general@postgresql.org Sent by: cc: [EMAIL PROTECTED]Subject: Re: [GENERAL] PostgreSQL still for Linux only? tgresql.org 03/09/2005 11:28 AM Tom Lane wrote: > Tope Akinniyi <[EMAIL PROTECTED]> writes: > >>I am not holding anybody responsible, but I think we need to do a massive re-orientation of the community not to carry the Linux-Windows game too far. > > > This is a troll, isn't it? My thinking as well, unfortunately, has hooked some folks... -- Until later, Geoffrey ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] postgresql on gentoo
Sim Zacks wrote: > I just installed postgresql on a new gentoo server using emerge and I > would like to add the citext module for a case insensitive text type. > Emerge does not have a citext option in postgresql or package. Does > anyone know how to get the citext into postgresql on gentoo and will > this break the next time I upgrade postgresql? Not a direct answer. I use PG on AMD64 Gentoo. I wanted to use 8.0 before Gentoo incorporated it (don't know if they have yet or not.) So I downloaded the source and compiled it myself. This is very easy to do, as PG has very few dependencies. So you might want to consider that alternative. You'll lose Gentoo's management of the PG installation, but I don't find that a major issue. -- Guy Rouillier ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] segmentation fault
On Wed, 9 Mar 2005 14:28:55 -0300 (ART), marcelo Cortez <[EMAIL PROTECTED]> wrote: > > hello to all > I have the following problem, when making massive > update of a table, I received the message > "segmentation fault" without no other data of the > error in log. > Some idea of because it gives east message? > I am using > gentoo > postgres 7.4.6 > dual processor HP proliant >5 gigabyte of ram > 147 gigabytes of hd. > 47 free . > > BTW the column to update is the one of indice gist of > FTS and the table has 22 million registries. > any clue? You're doing this from within psql or elsewhere? What logging level are you using? How 'massive' is this update? What kind of parameters have you set for this database? -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL still for Linux only?
Ho ho, flame on! :) My completely annecodal experience with devs which prefer windows over posix is that the former prods things until they seem to work and accepts unexplained behavior far more readily than the latter. Do I *really* want that kind of mentality in my database devs? Anyway, I think you have the focus wrong. It's not: "run our software on what we tell you to" it's more: "we believe this platform is better than others, so we'll write our free software for that. But if you want to port it over to the platform of your choice, have fun doing that." On Wed, 9 Mar 2005, Jim C. Nasby wrote: > Personally, I find the anti-windows bias that has been shown in this > thread by some developers to be disappointing. Maybe it sucks to program > in, and maybe it's not as stable as unix (though I don't put much water > in that argument anymore), but the fact is there's still a LOT of places > that are windows shops and a LOT of people who use windows more heavily > than *nix. More important, the egotism of "If you want to use PostgreSQL > you better run it on what we tell you to run it on" is certain to turn > people off of PostgreSQL. It will certainly turn off windows developers > who might have been interested in working to improve PostgreSQL now that > it runs on windows. > -- > Jim C. Nasby, Database Consultant [EMAIL PROTECTED] > Give your computer some brain candy! www.distributed.net Team #1828 > > Windows: "Where do you want to go today?" > Linux: "Where do you want to go tomorrow?" > FreeBSD: "Are you guys coming, or what?" > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL still for Linux only?
On Wed, 9 Mar 2005 11:02:10 -0600, Jim C. Nasby <[EMAIL PROTECTED]> wrote: >... but the fact is there's still a LOT of places > that are windows shops and a LOT of people who use windows more heavily > than *nix. More important, the egotism of "If you want to use PostgreSQL > you better run it on what we tell you to run it on" is certain to turn > people off of PostgreSQL. Perhaps someone on the list who knows and uses the different operating systems could set up a lab, to compare PostgreSQL between them. Perhaps the latest Windows Server, a popular distribution of Linux, and Mac OS X? Has this already been done, with regard to performance? Doug ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] segmentation fault
hello to all I have the following problem, when making massive update of a table, I received the message "segmentation fault" without no other data of the error in log. Some idea of because it gives east message? I am using gentoo postgres 7.4.6 dual processor HP proliant 5 gigabyte of ram 147 gigabytes of hd. 47 free . BTW the column to update is the one of indice gist of FTS and the table has 22 million registries. any clue? best regards MDC ___ 250MB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [JDBC] java is locked when select for update
Whoops, posted to the wrong mailing list... but it might fit anyway. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Not use index on setof from Sql Function
On Wed, Mar 09, 2005 at 04:49:28PM +0100, Alessandro Vincelli wrote:
>
> This simple function return setof table "DatiRegione"
>
>
> CREATE OR REPLACE FUNCTION public._stato_dati_regione_rt(text)
> RETURNS SETOF "DatiRegione" AS
> '
> SELECT DISTINCT ON ("Codice_regione") dr.* FROM "DatiRegione"
> '
> LANGUAGE 'sql' VOLATILE STRICT;
You can't do an index scan on a function. As far as the executor is
concerned, a function is an opaque object. Perhaps you actually want a
view? They allow optimisations like the one you're looking for...
Hope this helps,
--
Martijn van Oosterhout http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
pgpzTMhAhFxIH.pgp
Description: PGP signature
[GENERAL] Temporary tables privileges
Hi, I'm new at pg. I'll be using tmp tables in others rdbms. An user can create your own tmp tables (grant temporary tables) but can't drop it (I don't want to grant drop privileges). Other way it's using on commit; but I can't make this work. Example: CREATE TEMPORARY TABLE tmp ON COMMIT DROP AS SELECT code FROM mytable WHERE code BETWEEN 1 AND 10; shows error near ON Thanks. Alejandro. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostgreSQL still for Linux only?
Shelby Cain wrote: --- "Uwe C. Schroeder" <[EMAIL PROTECTED]> wrote: The problem is, that it's a question of perception. Most windows fans don't see that "their" OS is pretty instable. That may have been true in 1995. However, in this day and age most Windows fans don't see that their OS as unstable because it isn't - unless of course you are referring to the non-NT variations. O.k. I don't want to start an OS war here. However there are a couple of things I know. 1. As of Windows 2000, Windows is reasonably stable. However there is a caveat, it still can not perform under load (read slowness, possible crash) like Linux or other UNIX variants can. 2. As of Windows 2003, Windows is very stable and performs fairly well under load. However it still can not keep up with Linux or other UNIX variants. The majority of the problem with Windows in these days is people who hire other people with little pieces of paper that say they are knowledgeable. A properly managed Windows server can be reliable, can perform reasonably well, if you have the expertise to do so. This is not that much unlike UNIX. The difference is that UNIX requires the expertise, Windows makes you feel like you have it when you don't. Sincerely, Joshua D. Drake Regards, Shelby Cain __ Celebrate Yahoo!'s 10th Birthday! Yahoo! Netrospective: 100 Moments of the Web http://birthday.yahoo.com/netrospective/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] partitionning
Tablespaces are the closest thing. They were introduced in 8.0: http://www.postgresql.org/docs/8.0/static/sql-createtablespace.html -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Mar 9, 2005, at 10:31 AM, FERREIRA William (COFRAMI) wrote: hi does the table partitionning exists under PostgreSQL ? or maybe an alternative exists ? Oracle implements this system : it allows to spread rows on differents partitions depending on an attribute. For example, my application store xml documents in database, an the partitionning is used to spread the differents documents on differents partitions. thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL still for Linux only?
Personally, I find the anti-windows bias that has been shown in this thread by some developers to be disappointing. Maybe it sucks to program in, and maybe it's not as stable as unix (though I don't put much water in that argument anymore), but the fact is there's still a LOT of places that are windows shops and a LOT of people who use windows more heavily than *nix. More important, the egotism of "If you want to use PostgreSQL you better run it on what we tell you to run it on" is certain to turn people off of PostgreSQL. It will certainly turn off windows developers who might have been interested in working to improve PostgreSQL now that it runs on windows. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] [JDBC] java is locked when select for update
Please post to the list too when replying... On Wed, 2005-03-09 at 17:29, Mican Bican wrote: > Thank you for your answer. The reason of so long transactions is that > I first Insert a row than get the last ID (primary key) and select it > for update from the database and wait for user to commit it.. > I need the key becouse I will insert rows with references to this new > row (like adresses to user etc.) ..so the user cann add some adresses > to a user and when he press ok I commit the statement... > I can't see why you couldn't insert the user in a first transaction and the addresses in further transactions... if the user gets deleted in the meantime by somebody else, then inserting the address will fail and you will tell the user about the error. And if you don't want other people to see the new user before it is committed with all it's addresses, then collect first the user data + all the address data, and then open a transaction and insert the user and the addresses when the user presses OK. The point is that there should not be any GUI activity between the start and end of a transaction. If you really need a lock (I doubt it) between updates, implement it using some application logic, don't use the DB row locking for long living locks. I think there is some locking helper in the contrib modules of postgres, or if not, read up on resource locking on google. Cheers, Csaba. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] partitionning
On Wed, 2005-03-09 at 10:31, FERREIRA William (COFRAMI) wrote: > hi > > does the table partitionning exists under PostgreSQL ? or maybe an > alternative exists ? > > Oracle implements this system : it allows to spread rows on differents > partitions depending on an attribute. > For example, my application store xml documents in database, an the > partitionning is used to spread the differents documents on differents > partitions. Currently, doing this in PostgreSQL is more of a "roll your own" thing. you create the base tables on different table spaces, then create an updatable view with the proper triggers to make sure that the incoming records go to the right tables. With the advent of very large raid arrays with very fast caching controllers, this methodology is becoming less and less necessary. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] unsuscribe
unsuscribe Celebrate Yahoo!'s 10th Birthday! Yahoo! Netrospective: 100 Moments of the Web
[GENERAL] partitionning
Title: iterate over refcursor hi does the table partitionning exists under PostgreSQL ? or maybe an alternative exists ? Oracle implements this system : it allows to spread rows on differents partitions depending on an attribute. For example, my application store xml documents in database, an the partitionning is used to spread the differents documents on differents partitions. thanks
Re: [GENERAL] PostgreSQL still for Linux only?
--- "Uwe C. Schroeder" <[EMAIL PROTECTED]> wrote: > > The problem is, that it's a question of > perception. Most windows fans don't > > see that "their" OS is pretty instable. That may have been true in 1995. However, in this day and age most Windows fans don't see that their OS as unstable because it isn't - unless of course you are referring to the non-NT variations. Regards, Shelby Cain __ Celebrate Yahoo!'s 10th Birthday! Yahoo! Netrospective: 100 Moments of the Web http://birthday.yahoo.com/netrospective/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL still for Linux only?
Tom Lane wrote: Tope Akinniyi <[EMAIL PROTECTED]> writes: I am not holding anybody responsible, but I think we need to do a massive re-orientation of the community not to carry the Linux-Windows game too far. This is a troll, isn't it? My thinking as well, unfortunately, has hooked some folks... -- Until later, Geoffrey ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Not use index on setof from Sql Function
This simple function return setof table "DatiRegione"
CREATE OR REPLACE FUNCTION public._stato_dati_regione_rt(text)
RETURNS SETOF "DatiRegione" AS
'
SELECT DISTINCT ON ("Codice_regione") dr.* FROM "DatiRegione"
'
LANGUAGE 'sql' VOLATILE STRICT;
When I perform query on this setof, pgsql not use the indexes on table
"DatiRegion".
This is a big problem.
##à
Query on _stato_dati_regione_rt(text) without index
QUERY PLAN
Aggregate (cost=17.50..17.51 rows=1 width=17) (actual
time=14555.295..14555.297 rows=1 loops=1)
-> Function Scan on _stato_dati_regione_rt (cost=0.00..17.50 rows=1
width=17) (actual time=14528.756..14554.753 rows=119 loops=1)
Filter: (("Categoria" = 1) AND ("Tipologia" = 'ALB'::bpchar))
Total runtime: 14561.981 ms
##
Query on "DatiRegione" with index
QUERY PLAN
Aggregate (cost=672.10..672.10 rows=1 width=17) (actual
time=8.752..8.753 rows=1 loops=1)
-> Index Scan using idx_dr_cat_tipo on "DatiRegione"
(cost=0.00..671.64 rows=181 width=17) (actual time=0.123..6.289 rows=504
loops=1)
Index Cond: (("Categoria" = 1) AND ("Tipologia" = 'ALB'::bpchar))
Total runtime: 9.565 ms
###
Can I use index on setof from _stato_dati_regione_rt(text)?
Tannks in adbace.
By,Alessandro
--
---
Alessandro Vincelli
W4B - web for business s.r.l.
Firenze
via Pellicceria 10 - 50123
E-mail: [EMAIL PROTECTED]
tel: 055-2654270
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Triggers and User Defined Trigger Functions
Csaba Nagy wrote: DELETE FROM Temp1 WHERE Test = 'test3'; ERROR: syntax error at or near "$2" at character 44 QUERY: INSERT INTO Temp2 (ID, test) VALUES ( $1 $2 ) CONTEXT: PL/pgSQL function "temp1_trigger_delete" line 2 at SQL statement LINE 1: INSERT INTO Temp2 (ID, test) VALUES ( $1 $2 ) ^^^ What did I miss? A comma in the indicated position I guess... Thanks. I'm feeling really stupid now. You may all mock me. :-) Thanks for your help, it's most appreciated. :-) Gordan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Triggers and User Defined Trigger Functions
> DELETE FROM Temp1 WHERE Test = 'test3'; > ERROR: syntax error at or near "$2" at character 44 > QUERY: INSERT INTO Temp2 (ID, test) VALUES ( $1 $2 ) > CONTEXT: PL/pgSQL function "temp1_trigger_delete" line 2 at SQL statement > LINE 1: INSERT INTO Temp2 (ID, test) VALUES ( $1 $2 ) ^^^ > > What did I miss? A comma in the indicated position I guess... HTH, Csaba. ---(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: [GENERAL] Can't start PostgreSQL on Fedora Core3
Ulrich Wisser <[EMAIL PROTECTED]> writes: > it seems selinix doesn't like postgres. Can I uninstall selinux from > fedora savely? OR is there any way to make them both work together? They should work together as long as you have the latest PG RPMs (which it seems you do) and a reasonably recent selinux-policy-targeted. One problem is that selinux-policy-targeted updates don't necessarily propagate to the security labels of the individual files. I think what you need to do here is sudo /sbin/restorecon -R /var/lib/pgsql to ensure that /var/lib/pgsql and all its contents are correctly labeled per your current installed selinux policy. The reason for thinking this is that your error message suggests that /var/lib/pgsql/data/PG_VERSION is labeled root:object_r:var_lib_t, which I think is the generic default for files under /var/lib, while in my (working;-)) install it's labeled postgresql_db_t: $ sudo ls -Z /var/lib/pgsql/data/PG_VERSION -rw--- postgres postgres root:object_r:postgresql_db_t /var/lib/pgsql/data/PG_VERSION There's some history and info about variant problems at https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=143208 regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Can't start PostgreSQL on Fedora Core3
Ulrich Wisser wrote:
Mar 9 14:20:33 localhost kernel: audit(1110374433.961:0): avc: denied
{ read } for pid=9251 exe=/usr/bin/postgres name=PG_VERSION dev=dm-1
ino=1255016 scontext=user_u:system_r:postgresql_t
tcontext=root:object_r:var_lib_t tclass=file
Try running 'restorecon -n -R -v /var/lib/pgsql'. If it suggests
changes that appear to make sense, run 'restorecon -R -v
/var/lib/pgsql'.
HTH
--
Ian Pilcher[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])
[GENERAL] postgresql on gentoo
I just installed postgresql on a new gentoo server using emerge and I would like to add the citext module for a case insensitive text type. Emerge does not have a citext option in postgresql or package. Does anyone know how to get the citext into postgresql on gentoo and will this break the next time I upgrade postgresql? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Triggers and User Defined Trigger Functions
Richard Huxton wrote: Gordan Bobic wrote: Hi, I'm trying to figure out how to do this from the documentation, but I can't figure it out. :-( Here is what I'm trying to do: CREATE TABLE MyTable ( IDbigserial unique, MyDatachar(255), PRIMARY KEY (ID) ); CREATE TABLE Archive_MyTable ( IDbigserial unique, MyDatachar(255), PRIMARY KEY (ID) ); CREATE FUNCTION MyTable_Trigger_DELETE() RETURNS ???opaque/trigger/HeapTuple??? AS ' RETURNS TRIGGER You can't use SQL as the target language, it has to be one of the procedural languages (e.g. plpgsql) Something like: CREATE FUNCTION my_trig_fn() RETURNS trigger AS ' BEGIN INSERT INTO archive_mytable (id,mydata) VALUES (OLD.id, OLD.mydata); RETURN OLD; END; ' LANGUAGE plpgsql; Thanks. :-) I did that, and I can now create the function and the trigger OK. But when the trigger fires (i.e. on DELETE), I get the following error: DELETE FROM Temp1 WHERE Test = 'test3'; ERROR: syntax error at or near "$2" at character 44 QUERY: INSERT INTO Temp2 (ID, test) VALUES ( $1 $2 ) CONTEXT: PL/pgSQL function "temp1_trigger_delete" line 2 at SQL statement LINE 1: INSERT INTO Temp2 (ID, test) VALUES ( $1 $2 ) What did I miss? Gordan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL still for Linux only?
On Tue, 2005-03-08 at 21:24, Tope Akinniyi wrote: > Hi, > > I am wondering at this display of extreme Linux mentality being > displayed by the 'top bras' of the PostgreSQL community. And I ask, > are we encouraging Windows use of PostgreSQL at all? > > Take a look at tools being rolled out at PgFoundry on daily basis; all > for Linux except the Windows installer. I ask myself what is being > done to encourage PostgreSQL Windows users. Nothing is available to > them except the Database and PgAdmin. No replication tool, no this, > no that. > > I was troubled when CommandPrompt, the leading Windows support > provider responded to a post that their plPHP is for Linux only. > > Sorry for this: Firebird provides equal tools for Linux and Windows > users. We are not the one to tell the Windows users whether they need > them. > > Whether Windows is bad or good; Linux is the angel and Windows the > devil is not the issue here. PostgreSQL has gone the Windows way and > must not be shown to be deficient. > > I am not holding anybody responsible, but I think we need to do a > massive re-orientation of the community not to carry the Linux-Windows > game too far. I think you misunderstand the basic open source model. People get an itch, they scratch it. Right now, there are very few postgresql on windows users, because it is so new. There are lots and lots of postgresql on UNIX (not just linux btw) folks. So, there are more people scratching itches on unix than on windows. As the number of Windows users grows, the number of folks who feel a need to port things originally written for unix will grow. At my last company, they brought in a (possibly clinically insane) CIO who decided that all this Unix stuff was outdated, and hey, it worked at my subdivision of 20 IT and 200 sales staff at my last company, so it ought to work here with 300 IT and 200 other folks, right? So, my buddy who is unfortunately still stuck there has had to port all of our internal apps to run on windows, and the port of postgresql to windows was a great help for him. He's one of those people we may find scratching an itch some day. But it's organic, it happens when it happens. Who knows, one day one of the core postgresql developers might be a windows expert. I'm quite certain that if you see something that doesn't work on windows, and you do make it work on windows, your patches for that something will likely be accepted with grace. But the unix users aren't going to install windows just to do it for you and the other windows users. They have other things to do. Give it time... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Can't start PostgreSQL on Fedora Core3
On Wed, 09 Mar 2005 14:38:06 +0100, Ulrich Wisser <[EMAIL PROTECTED]> wrote: > Sorry, > > of course I forgot to mention my installed version. Please find them below: > > postgresql-jdbc-7.4.7-3.FC3.1 > postgresql-odbc-7.3-8.FC3.1 > postgresql-test-7.4.7-3.FC3.1 > postgresql-devel-7.4.7-3.FC3.1 > postgresql-contrib-7.4.7-3.FC3.1 > postgresql-python-7.4.7-3.FC3.1 > postgresql-7.4.7-3.FC3.1 > postgresql-pl-7.4.7-3.FC3.1 > postgresql-server-7.4.7-3.FC3.1 > postgresql-tcl-7.4.7-3.FC3.1 > postgresql-libs-7.4.7-3.FC3.1 > postgresql-docs-7.4.7-3.FC3.1 > > selinux-policy-targeted-1.17.30-2.85 > > up2date insists that these packages are all up to date. And you're certain that you're using the targetted policy and not strict? -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(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: [GENERAL] Can't start PostgreSQL on Fedora Core3
Sorry,
of course I forgot to mention my installed version. Please find them below:
postgresql-jdbc-7.4.7-3.FC3.1
postgresql-odbc-7.3-8.FC3.1
postgresql-test-7.4.7-3.FC3.1
postgresql-devel-7.4.7-3.FC3.1
postgresql-contrib-7.4.7-3.FC3.1
postgresql-python-7.4.7-3.FC3.1
postgresql-7.4.7-3.FC3.1
postgresql-pl-7.4.7-3.FC3.1
postgresql-server-7.4.7-3.FC3.1
postgresql-tcl-7.4.7-3.FC3.1
postgresql-libs-7.4.7-3.FC3.1
postgresql-docs-7.4.7-3.FC3.1
selinux-policy-targeted-1.17.30-2.85
up2date insists that these packages are all up to date.
Ulrich
Devrim GUNDUZ wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
Are you using the latest selinux_policy_targeted package? I mean, is
your system up2date?
Regards,
On Wed, 9 Mar 2005, Ulrich Wisser wrote:
Hi,
it seems selinix doesn't like postgres. Can I uninstall selinux from
fedora savely? OR is there any way to make them both work together?
Here is my error message:
Mar 9 14:20:33 localhost kernel: audit(1110374433.961:0): avc:
denied { read } for pid=9251 exe=/usr/bin/postgres name=PG_VERSION
dev=dm-1 ino=1255016 scontext=user_u:system_r:postgresql_t
tcontext=root:object_r:var_lib_t tclass=file
Any ideas are welcome!
Thanks
Ulrich
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
- --
Devrim GUNDUZ devrim~gunduz.org, devrim~PostgreSQL.org,
devrim.gunduz~linux.org.tr
http://www.tdmsoft.com http://www.gunduz.org
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQFCLvoUtl86P3SPfQ4RAhtKAJ9PAU+3IjRq4oo032ZiXaL9omQmUgCffJ36
yTiQ1KHu33RGd6aHAdhqrWw=
=wKRE
-END PGP SIGNATURE-
--
Ulrich Wisser
RELEVANT TRAFFIC SWEDEN AB, Riddarg 17A, SE-114 57 Sthlm, Sweden
Direct (+46)86789755 || Cell (+46)704467893 || Fax (+46)86789769
http://www.relevanttraffic.com
---(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: [GENERAL] Can't start PostgreSQL on Fedora Core3
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
Are you using the latest selinux_policy_targeted package? I mean, is your
system up2date?
Regards,
On Wed, 9 Mar 2005, Ulrich Wisser wrote:
Hi,
it seems selinix doesn't like postgres. Can I uninstall selinux from fedora
savely? OR is there any way to make them both work together?
Here is my error message:
Mar 9 14:20:33 localhost kernel: audit(1110374433.961:0): avc: denied {
read } for pid=9251 exe=/usr/bin/postgres name=PG_VERSION dev=dm-1
ino=1255016 scontext=user_u:system_r:postgresql_t
tcontext=root:object_r:var_lib_t tclass=file
Any ideas are welcome!
Thanks
Ulrich
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
- --
Devrim GUNDUZ
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.tdmsoft.com http://www.gunduz.org
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQFCLvoUtl86P3SPfQ4RAhtKAJ9PAU+3IjRq4oo032ZiXaL9omQmUgCffJ36
yTiQ1KHu33RGd6aHAdhqrWw=
=wKRE
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] PostgreSQL still for Linux only?
Okay, I'll split them with you. I remember the Groton Database Corp. of
Groton Connecticut, whose marketing people didn't like the sound of
*Groton*, and renamed the company Interbase and the product InterBase (note
caps). Ashton Tate came along years later and bought the company to
increase their own salability to Borland. I bought InterBase from
Interbase Corp. in 1991 for HP-UX.
Rick
Edwin New
<[EMAIL PROTECTED]>To:
pgsql-general@postgresql.org
Sent by: cc:
[EMAIL PROTECTED]Subject: Re: [GENERAL]
PostgreSQL still for Linux only?
tgresql.org
03/09/2005 12:02 AM
I don't want to split hairs, but wasn't Firebird originally Interbase? If
so, you'll find it was originally a *nix product before it was a Windows
database (back in the Ashton-Tate days for those with long memories).
Edwin New.
-Original Message-
From: Uwe C. Schroeder [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 9 March 2005 3:49 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PostgreSQL still for Linux only?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Tuesday 08 March 2005 07:24 pm, Tope Akinniyi wrote:
> Hi,
>
> I am wondering at this display of extreme Linux mentality being displayed
> by the 'top bras' of the PostgreSQL community. And I ask, are we
> encouraging Windows use of PostgreSQL at all?
>
> Take a look at tools being rolled out at PgFoundry on daily basis; all
for
> Linux except the Windows installer. I ask myself what is being done to
> encourage PostgreSQL Windows users. Nothing is available to them except
> the Database and PgAdmin. No replication tool, no this, no that.
To be honest - I wouldn't encourage the use of PostgreSQL on Win.
Neither would I for any database or data warehouse application (which
probably
is why SAP put onto their website that they prefer linux to windows
platforms).
I think it could even damage the quite good reputation of PostgreSQL - if
your
windows box crashes and takes the DB with it - most likely it's not the
fault
of a lousy OS, nor the fault of an incompetent sysadmin who forgot to make
backups - it will be this "shitty" free database system that's to blame.
I wrote quite some software that uses postgresql - never would I tell any
customer that he could now run it on windows. As a matter of fact I put
code
like:
if os="win" {
errormessage("this software is not ported to windows yet");
exit(99);
}
into the startup routine - just to make it impossible for the customer to
run
it on windows.
> I was troubled when CommandPrompt, the leading Windows support provider
> responded to a post that their plPHP is for Linux only.
>
> Sorry for this: Firebird provides equal tools for Linux and Windows
users.
> We are not the one to tell the Windows users whether they need them.
Firebird was a DOS ISAM DB. It just made it's way to *nix a couple years
ago.
> Whether Windows is bad or good; Linux is the angel and Windows the devil
is
> not the issue here. PostgreSQL has gone the Windows way and must not be
> shown to be deficient.
The problem is, that it's a question of perception. Most windows fans don't
see that "their" OS is pretty instable. So it's not a question if the
community can do anything to make PostgreSQL look deficient - it's a
question
of what people do with it on Win. I had a similar case recently with a
customer: His MS Office suite crashed at least 3 times a day. So I switched
him to OpenOffice. Now OO crashed once after a month of perfect operation -
guess what, the customer is back to MS Office because OO crashed on him and
MS has this new version that's sooo much b
[GENERAL] Can't start PostgreSQL on Fedora Core3
Hi,
it seems selinix doesn't like postgres. Can I uninstall selinux from
fedora savely? OR is there any way to make them both work together?
Here is my error message:
Mar 9 14:20:33 localhost kernel: audit(1110374433.961:0): avc: denied
{ read } for pid=9251 exe=/usr/bin/postgres name=PG_VERSION dev=dm-1
ino=1255016 scontext=user_u:system_r:postgresql_t
tcontext=root:object_r:var_lib_t tclass=file
Any ideas are welcome!
Thanks
Ulrich
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] out of memory problem
Pruteanu Dragos wrote: Hi all, I am running Postgres on a machine with 4G of memory. When I run dbvlm=> SELECT u.email, g.email FROM dom_user u, shared_buffers = 20 sort_mem = 819200 vacuum_mem = 819200 What process led you to choose these values? Do you understand the implications of allocating 1.6GB of memory to shared buffers, along with over 800MB per sort? Any ideea what can be done? Start by fixing your configuration values - the above are unlikely to be correct. Then, check to see if all RAM is actually in use, or you have hit some system-imposed per-user limit. This might be a useful place to start: http://www.powerpostgresql.com/PerfList -- Richard Huxton Archonet Ltd ---(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: [GENERAL] row numbering
# [EMAIL PROTECTED] / 2005-03-01 17:41:46 +0100: > > > There are 5 vaccinations in a given vaccination schedule. > > > > > > Patient had 3 shots. > > > > > > I want the view to show me that shot 4 and 5 are missing > > > without having to enter the cardinality of the vaccination in > > > the original data. I don't know that much about medicine, so this might be a funny question, but do you really need to know that "shots 4 and 5 are missing", or just that the patient needs to be shot two more times, or do you really want the *application dates*? > Here is the bit of data that I forgot to mention: Those > consecutive immunization rows *are* ordered by a sort key that > the application assigns -- the date of application. So, > basically, what I want to do is the following (high level): > > 1) read given vaccinations from table > 2) order by date_given > 3) assign ordinals to the rows in the order obtained by 2) > 4) deduce missing shots by joining to another table that >defines the number of vaccinations in a schedule This is a description of steps you decided would get you to your goal. Instead of describing the steps, what's the goal? -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] out of memory problem
Hi all, I am running Postgres on a machine with 4G of memory. When I run dbvlm=> SELECT u.email, g.email FROM dom_user u, dom_member m, dom_group g andlm-> WHERE u.userid=m.userid and m.groupid=g.groupid and g.iso_language='de' and dbvlm-> m.type='n' limit 1000; ERROR: out of memory DETAIL: Failed on request of size 68. dbvlm=> The postgres settings are # - Memory - shared_buffers = 20 sort_mem = 819200 vacuum_mem = 819200 Any ideea what can be done? Thank you Dragos Pruteanu __ Celebrate Yahoo!'s 10th Birthday! Yahoo! Netrospective: 100 Moments of the Web http://birthday.yahoo.com/netrospective/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Triggers and User Defined Trigger Functions
Gordan Bobic wrote: Hi, I'm trying to figure out how to do this from the documentation, but I can't figure it out. :-( Here is what I'm trying to do: CREATE TABLE MyTable ( IDbigserial unique, MyDatachar(255), PRIMARY KEY (ID) ); CREATE TABLE Archive_MyTable ( IDbigserial unique, MyDatachar(255), PRIMARY KEY (ID) ); CREATE FUNCTION MyTable_Trigger_DELETE() RETURNS ???opaque/trigger/HeapTuple??? AS ' RETURNS TRIGGER INSERT INTO Archive_MyTable ( ID, MyData ) VALUES ( OLD.ID, OLD.MyData ); RETURN OLD; ' LANGUAGE SQL; You can't use SQL as the target language, it has to be one of the procedural languages (e.g. plpgsql) Something like: CREATE FUNCTION my_trig_fn() RETURNS trigger AS ' BEGIN INSERT INTO archive_mytable (id,mydata) VALUES (OLD.id, OLD.mydata); RETURN OLD; END; ' LANGUAGE plpgsql; You can also use many other languages for functions - tcl/perl/python (I think)/java etc. Check your language of choice supports triggers though. -- Richard Huxton Archonet Ltd ---(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
[GENERAL] Triggers and User Defined Trigger Functions
Hi, I'm trying to figure out how to do this from the documentation, but I can't figure it out. :-( Here is what I'm trying to do: CREATE TABLE MyTable ( ID bigserial unique, MyData char(255), PRIMARY KEY (ID) ); CREATE TABLE Archive_MyTable ( ID bigserial unique, MyData char(255), PRIMARY KEY (ID) ); CREATE FUNCTION MyTable_Trigger_DELETE() RETURNS ???opaque/trigger/HeapTuple??? AS ' INSERT INTO Archive_MyTable ( ID, MyData ) VALUES ( OLD.ID, OLD.MyData ); RETURN OLD; ' LANGUAGE SQL; This gives me one of the following errors: ERROR: SQL functions cannot return type opaque ERROR: SQL functions cannot return type "trigger" ERROR: type "heaptuple" does not exist What type should my function be returning? ERROR: type Then I'd like to do the following: CREATE TRIGGER MyTable_Trigger_DELETE BEFORE DELETE ON MyTable FOR EACH ROW EXECUTE PROCEDURE MyTable_Trigger_DELETE(); Can I create a trigger function like this? If not, what are my options WRT alternatives? Many thanks. Gordan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] postgresql vs mysql performance comparison
Marco Colombo wrote: If want to develop a commercial application that: - runs under Linux - I can; - uses HTTP as protocol, and connects to a GPL-ed web server - I can; - uses MySQL as a database backend - I can't, unless I rewrite the client library, or buy a commercial licence from them. Why? With PostgreSQL you don't have to thing about these issues. A big win. .TM. 1. Even microsoft client libraries are redistributable! The switch from LGPL to dual licence for the MySQL client libraries was the sole reason that I chose to switch to postgres. 2. Rick Casey wrote: >This will not answer you question, but documents some of the evidence for you: > > http://www.geocities.com/mailsoftware42/db/ These reports have many inconsistencies - speed tests were clearly done with MyISAM tables and the nature of the tests clearly is 'read' biased with little 'write' - where the MyISAM table level locking really slows up MyISAM. Also, many of the feature comparissons are for the InnodB engine or for the Beta release of the database. When Rick Schumeyer does the tests, please could you include some InnoDB vs. Postgres benchmarks. I am genuinely interested in the results. Howard Cole www.selestial.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Pgsql dynamic statements and null values
On Tue, Mar 08, 2005 at 04:30:54PM -0600, Guy Rouillier wrote: > This is such a common usage pattern that I'm pretty sure I'm missing > something basic. Pgsql provides quote_literal to aid with inserting a > literal string into a dynamically prepared statement. My opinion is > that quote_literal should handle nulls as well, but if quote_literal > can't be changed for historical reasons, then providing another function > like check_null below would be very useful. Basically, such a function > should supply the value NULL if the incoming value is null, or the > incoming value otherwise. Lookup the COALESCE and NULLIF functions. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpPUlsx6y3cH.pgp Description: PGP signature
Re: [GENERAL] Disabling triggers in a transaction
Net Virtual Mailing Lists wrote: All I did was added an extra column to my table (I called it "batch_process"). Then in the trigger do something like (in whichever function you are calling): IF NEW.batch_update IS NOT NULL AND NEW.batch_update = ''t'' THEN NEW.batch_process := NULL; RETURN NULL; END IF; .. whatever the rest of transaction is Why don't you just set it to false instead of NULL? Wouldn't that reduce the condition to just "IF NEW.batch_update THEN ..."? In that case you should default the column to false of course, or the condition will always fail (the value being NULL). Personally, I would use a more descriptive name for the column, 'disable_triggers' or something like that. Also, I find it more convenient to use "true" and "false" instead of having to escape "'t'" and "'f'" all the time ;) Then when doing an insert, just: INSERT INTO table (..., batch_process) VALUES (..., 't') when you want the trigger not to fire... Or an update: UPDATE TABLE table SET , batch_process = 't' I'm not sure sure how to make it work on a function called from a delete trigger though.. ;-( The drawbacks of this method are that you'll have to modify all your queries when you want to disable triggers (though that can usually be solved programatically), and that only the triggers that "support" this method of disabling will be actually disabled. If you work at the same project with multiple people who all write triggers from time to time, or when you have to deal with legacy code from an older database, I think you'll run into trouble with the above quicker than you'd like. However, if you manage to get this into the design fase of a project it'll probably work just fine (the delete problem you mentioned aside...). -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(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: [GENERAL] postgresql vs mysql performance comparison
On Tue, 8 Mar 2005, Scott Marlowe wrote: On Tue, 2005-03-08 at 09:06, Shelby Cain wrote: --- Howard Cole <[EMAIL PROTECTED]> wrote: Although not appropriate for a speed comparison, you might want to note that the use of Mysql versions 4.0 upward now require commercial license for clients, which are no longer LGPL, whereas Postgres is free (BSD license). This makes transactions per dollar an interesting statistic when comparing the Postgres and MySql! Reading over their site that doesn't appear true for every case. The client libraries are under the GPL and thus any application that links to them would also be covered under the GPL. No commercial license is required unless the terms of the GPL (ie: if you distribute a binary to someone you must also be willing to distribute your source code if asked) a problem. There have been some statements from MySQL in the past that implied they might be taking a narrower view of what "distribution" meant than what the GPL was saying. Also, it was impossible for PHP to be packaged with MySQL libs due to incompatibilities with the GPL'd mysql connection libs. It seems MySQL AB has clarified both on these pages: http://www.mysql.com/company/legal/licensing/ http://www.mysql.com/company/legal/licensing/foss-exception.html http://www.mysql.com/company/legal/licensing/faq.html However, Fedora Core 2 still includes MySQL V 3.xx.yy because of the issues wth V4.xx.yy's licensing. However, Suse does include the latest version. So there's some difference of opinion on the issue from different distros. Or different policies. One of the biggest problem of their dual licencing policy is that no one in really interested in provinding them with patches. In other words, they cannot accept third party contributions so easily. _My_ patches are going to be, likely, GPL-only. So they can't use them in their commercial product, unless they make two different lines (which they claim they don't), or they get a (commercial) licence from _me_ allowing _them_ to sell a work including _my_ patches. So in order to accept patches from me, they need a lot of paperwork (not to mention money, they're gonna pay for being able to sell my work). Not pratical. This is not the case of truly GPL software, such as the Linux kernel. Patches, being a derived work, are GPL and they can include them anytime. Note that client libraries are optional. As long the protocol is openly defined (we have open specs), you can write your own client layer, and still connect to the GPL server. Which is _the_ thing. Protecting the client library (switching the licence from LGPL to GPL) makes little sense, IMHO. It greatly reduces the number of potential users, and protects little value. If want to develop a commercial application that: - runs under Linux - I can; - uses HTTP as protocol, and connects to a GPL-ed web server - I can; - uses MySQL as a database backend - I can't, unless I rewrite the client library, or buy a commercial licence from them. Why? With PostgreSQL you don't have to thing about these issues. A big win. .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Pgsql dynamic statements and null values
On Tue, 2005-03-08 at 16:30 -0600, Guy Rouillier wrote: > We use a dynamic statement in a pgsql stored function to insert rows > into a table determined at run time. After much debugging, I've > discovered that a null incoming argument will cause the dynamic > statement to evaluate to null. The error message emitted is "unable to > execute null statement." can't you use COALESCE() ? gnari ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Can't delete - Need cascading update instead
Adam Tomjack wrote:
For various reasons, I can't actually delete records from my database.
Instead, I have a boolean 'active' field for each table. I need to
implement something like cascading delete, but instead of deleting, I
need to set active=false.
I've googled and haven't found a solution. I had two ideas, neither of
which worked out.
One thing I tried is to set ON DELETE CASCADE for all of my foreign key
constraints. Then I added a rule ON DELETE DO ALSO UPDATE ... and a
BEFORE DELETE trigger to stop the actual deletion. Unfortunately, that
also stops the cascade.
I'd be tempted to add triggers to the delete to copy old versions of the
data to a set of archive tables.
Alternatively, if you made "active" part of the primary and foreign-keys
on the tables concerned you could cascade updates.
My other idea involved an ON DELETE DO INSTEAD UPDATE ... rule and a
BEFORE UPDATE PL/pgSQL trigger that manually implemented the cascading.
The problem with that is that the only way I can find to generate an
approproate UPDATE or DELETE statement is to create a string and then
EXECUTE it, but I need values from the NEW or OLD records, which
apparently aren't usable from an EXECUTE statement. I'll include my
code at the end.
sql := sql || ' ' || r2.attname || '=OLD.' || r3.attname;
You can't refer to the OLD.xxx or NEW.xxx in the query-string itself,
you need to add its value. Of course, that causes problems because you
can't dynamically refer to OLD[r3.attname] or whatever syntax you'd be
tempted by.
TCL or one of the other dynamic languages is better for this. I've
attached a sample of some code and history tables that do something
similar to what you're trying to do. I don't make any great claims for
my TCL coding skills - most of it was pieced together from tutorials.
HTH
--
Richard Huxton
Archonet Ltd
-- History Tracking Trigger-Functions
--
CREATE TABLE history (
hid SERIAL UNIQUE NOT NULL,
cid int4 NOT NULL CONSTRAINT valid_client REFERENCES client ON DELETE CASCADE,
tstimestamp(0) with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
uid int4 NOT NULL CONSTRAINT valid_uid REFERENCES user_info DEFAULT app_session_int_vol('UID'),
tbl varchar(32) NOT NULL,
act char(1) NOT NULL CONSTRAINT valid_act CHECK (act IN ('U','I','D')),
PRIMARY KEY (hid)
);
CREATE TABLE history_detail (
hid integer NOT NULL CONSTRAINT valid_hid REFERENCES client_history,
col varchar(32) NOT NULL,
was text,
PRIMARY KEY (hid,col)
);
-- tcl_track_history(TABLE-NAME)
-- Set TABLE-NAME when creating the trigger. Will automatically record change
-- details in tables history/history_detail
--
CREATE OR REPLACE FUNCTION tcl_track_history() RETURNS trigger AS '
switch $TG_op {
DELETE {
if { [llength [array names OLD cid]] > 0 } {
set clival $OLD(cid)
} else {
set clival "NULL"
}
spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'D\')"
}
INSERT {
if { [llength [array names NEW cid]] > 0 } {
set clival $NEW(cid)
} else {
set clival "NULL"
}
spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'I\')"
}
UPDATE {
if { [llength [array names OLD cid]] > 0 } {
set clival $OLD(cid)
} else {
set clival "NULL"
}
set inserted_main_history_row false
foreach {col} $TG_relatts {
# First result seems to be an empty string when stepping through columns
if { $col > "" } {
# Check if OLD/NEW contain a value
if { [llength [array names OLD $col]] > 0 } {
set oldval $OLD($col)
} else {
set oldval "NULL"
}
if { [llength [array names NEW $col]] > 0 } {
set newval $NEW($col)
} else {
set newval "NULL"
}
if { $oldval != $newval } {
if { !$inserted_main_history_row } {
spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'U\')"
set inserted_main_history_row true
}
spi_exec "INSERT INTO history_detail (col,was) VALUES (\'$col\', \'[ quote $oldval ]\')"
}
}
}
}
}
return OK
' LANGUAGE pltcl;
CREATE TRIGGER history_trig AFTER INSERT OR UPDATE OR DELETE ON client FOR EACH ROW EXECUTE PROCEDURE tcl_track_history('client');
---(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: [GENERAL] Move cursor
Title: RE: [GENERAL] Move cursor does i hope that CURSOR will be fully implemented soon ? (or maybe you know an alternative for my problem ? :) ) -Message d'origine- De : Michael Fuhr [mailto:[EMAIL PROTECTED]] Envoyé : mardi 8 mars 2005 18:29 À : Tom Lane Cc : FERREIRA William (COFRAMI); pgsql-general@postgresql.org Objet : Re: [GENERAL] Move cursor On Tue, Mar 08, 2005 at 12:16:28PM -0500, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > I don't think PL/pgSQL implements cursors fully. > > Its cursor facility is certainly far weaker than what's presently in the > main SQL language. I think this is at least partly historical accident > (ie we upgraded the main language and forgot about plpgsql). Would adding MOVE to PL/pgSQL be a fairly trivial effort? If so then I'd consider having a go at it myself, unless it's something somebody else could bang out in five minutes (and was willing to do so). -- Michael Fuhr http://www.fuhr.org/~mfuhr/ This mail has originated outside your organization, either from an external partner or the Global Internet. Keep this in mind if you answer this message.
