Re: [GENERAL] SRF, JDBC and result info
Thank you very much for your reply. The thing is that my SRF is written in C, not plpgsql, but I'll look into RAISE NOTICE anyway.(I think there is something equevalent in libpq) Thanks again, Ntinos Katsaros Kris Jurka writes: On Tue, 8 Mar 2005 [EMAIL PROTECTED] wrote: Hi everybody! I have an SRF which is called from a JAVA app with JDBC. Everything works fine and I want now to be able to pass some result-related info to my app. It is not about the format of the results (ResultSetMetaData) or something like that. Is it possible to return some string (or other type of)info together with the result tuples (even if it requiers some hacking i.e. there is no provision for something like that)? Any ideas? The only idea that comes to mind is using RAISE NOTICE in your plpgsql function and Statement or ResultSet .getWarnings() on the Java side to retrieve that info. There really isn't any other out of band data path. Kris Jurka ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Problem with inherited table, can you help?...
On Fri, 11 Mar 2005 03:39 am, Michael Fuhr wrote: > On Thu, Mar 10, 2005 at 01:31:21AM -0800, Net Virtual Mailing Lists wrote: [snip] > > Some have suggested that PostgreSQL should use a weaker lock on the > referenced key, but that hasn't been implemented yet. > Are there actually any problems with only getting a AccessShareLock? Regards Russell Smith ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] SRF, JDBC and result info
On Tue, 8 Mar 2005 [EMAIL PROTECTED] wrote: > Hi everybody! > > I have an SRF which is called from a JAVA app with JDBC. Everything > works fine and I want now to be able to pass some result-related info to > my app. It is not about the format of the results (ResultSetMetaData) or > something like that. > > Is it possible to return some string (or other type of)info together with > the result tuples (even if it requiers some hacking i.e. there is no > provision for something like that)? Any ideas? > The only idea that comes to mind is using RAISE NOTICE in your plpgsql function and Statement or ResultSet .getWarnings() on the Java side to retrieve that info. There really isn't any other out of band data path. Kris Jurka ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL still for Linux only?
Jim C. Nasby wrote: On Thu, Mar 10, 2005 at 11:22:59AM -0600, Scott Marlowe wrote: This is the second problem. Windows simply has problems that cause data relibility problems that may or may not be surmountable in the future. Do you have any references to these problems? I've seen several people mention things like this in passing, but I have yet to see any specifics. I deal with clients who use all variations of windows OSs. I've previously worked for a large company who used both Unix and Windows servers. In every case, the Windows boxes were/are more susceptible to simply locking up or crashing. When your only resolution is to power cycle the server, you're going to trash your database. I've seen it on xp, nt, 200?... I don't do development on Windows boxes anymore. It's just too frustrating with the stability issues. -- Until later, Geoffrey ---(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] the impact of encoding on performance.
Michael Ben-Nes <[EMAIL PROTECTED]> writes: >> The drawback of using locales other than C or POSIX in PostgreSQL is >> its performance impact. It slows character handling and prevents >> ordinary indexes from being used by LIKE. For this reason use locales >> only if you actually need them. > What is the impact of the locale on the server ? is it irelevant, small > or huge ? > Encoding of the DB impact performance too ? UTF8, 8859-8 ? These aren't really separable since you generally don't get to choose the encoding independently of the locale. I'm working on some simple benchmarking consisting of running mysql's sql-bench against a PG 8.0.1 server on a Fedora Core 3 machine. Mostly I'm interested in understanding in detail why sql-bench makes us look so bad, but as long as I'm at it it can provide one datapoint in answer to your question. In two runs that were identical except one used en_US.utf8 locale and UTF8 encoding while the other used C locale and SQL-ASCII encoding, most of the tests didn't show any meaningful difference, but a couple of tests showed as much as a 2X advantage for C locale. These were tests that were heavily dependent on comparison of strings, such as a SELECT COUNT(DISTINCT foo) across a large table. So it would depend on your workload. Certainly it's possible that locale would make a big difference to you, but it might not. Also, this all depends quite a bit on how efficiently your libc implements strcoll() for non-C locales. I believe there are some platforms out there that are much slower than glibc, and would have a correspondingly higher penalty for using a non-C locale. You could investigate this by timing "sort" on a large file in both locales. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PostgreSQL still for Linux only?
> From: Tope Akinniyi > experiences. As an IT organisation that wants to stay in business you need to give to people what they wants. I think that is the basis of service. I have some deployments of PostgreSQL on Windows servers. I must admit that we have not had any problems so far. Dear Tope, My apologies that I cannot answer your questions directly, hopefully someone else will on the list. Understand that this is not really that much of a cultural issue. Both Linux and Postgres are born from interational cooperation. Even in the United States, windows use is pervasive, with very little support or desire for Linux (or other non-windows operating systems). The long history of Posix systems in the United States is really limited to educational, research institutions and a very small percentage of commercial enterprises. Linux has changed this a little over the last 5 years or so. But I personally know dozens of IT professionals local to my area and only one of them is what I would call a linux expert. This same ratio applies to the end user market. If what your customers really want is reliablity and replication options, then that currently conflicts with Windows and Postgres. Noone can really guarantee that will change. But I submit that if you really want to acheive excellence in the IT business you will educate yourself and then your customers about using Linux for dedicated database services. You will realize high reliability and easy maintenance for very low per user cost as compared to just about anything else. You may want to contact the folks at this web address for local linux support. http://nglug.org/ In any case I wish you the best of luck in your business. Best regards, Jim Wilson ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostgreSQL still for Linux only?
On Thu, 2005-03-10 at 15:45, Jim C. Nasby wrote: > On Thu, Mar 10, 2005 at 11:22:59AM -0600, Scott Marlowe wrote: > > > 2. This response is alarming: > > > Tom Lane wrote in digest V1.5092: > > > >We are supporting Windows as a Postgres platform for the benefit of > > > developers who want to > > > >do testing on their laptops (and for reasons best known to themselves > > > feel a need to run >Windows on their laptops). > > > > This is the second problem. Windows simply has problems that cause data > > relibility problems that may or may not be surmountable in the future. > > Do you have any references to these problems? I've seen several people > mention things like this in passing, but I have yet to see any > specifics. I'd have to look through the -hackers list and a few other places, but what I remember seeing was problems in the general area of unreliable journaling / disk syncing et. al. It's been a while. Plus my experience has been that Windows often behaves in unpredictable ways when it's running under a heavy load, so I'd expect race conditions to show up under those circumstances, and possibly corrupt data. It's certainly been a problem for most large SQL Server installations I've dealt with. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PostgreSQL still for Linux only?
On Thu, 2005-03-10 at 16:19 +, Tope Akinniyi wrote: > Hi all, > --- cut --- > > I sought Windows replication tool for and could not get. I checked > PgFoundry and the one there put a banner and said NOT FOR WINDOWS. > Then I said is this PostgreSQL for Windows a joke? That prompted > my post - IS POSTGRESQL FOR LINUX ONLY? Have you tried to setup the PostgreSQL server on a Linux computer (with replication) and use it via PostgreSQL clients running on Windows(tm) computers. This way your clients will still have the OS they are use to and the database server will be running on the best OS for it. --- cut --- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL still for Linux only?
On Thu, Mar 10, 2005 at 11:22:59AM -0600, Scott Marlowe wrote: > > 2. This response is alarming: > > Tom Lane wrote in digest V1.5092: > > >We are supporting Windows as a Postgres platform for the benefit of > > developers who want to > > >do testing on their laptops (and for reasons best known to themselves > > feel a need to run >Windows on their laptops). > > This is the second problem. Windows simply has problems that cause data > relibility problems that may or may not be surmountable in the future. Do you have any references to these problems? I've seen several people mention things like this in passing, but I have yet to see any specifics. -- 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
[GENERAL] Fwd: IO Timeout
I have a question about IO timeouts: We are using the 3ware escalade 9500S series of cards, and we had a drive failure this morning. Apparnetly the card waits 30 seconds for the drive to respond, and if it doesn't, it put's the drive in a fail state. Postgres it seems didn't wait 30 seconds before it decided that the system was upset, and put the database in maintainence mode. Is there a way to increase to IO wait timeout so this doesn't happen? Alex Turner netEconomist ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL still for Linux only?
On Wed, Mar 09, 2005 at 05:51:43PM -0800, Chris Travers wrote: > Jim C. Nasby wrote: > 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. This is akin to saying that an application written to use MySQL will never perform well on PostgreSQL. It depends on *how* the code is written. If your SQL is tuned to one database, it will likely have performance issues on other databases. Likewise, a process-based server will perform poorly on Windows, while a threaded server will not. This is an implimentation choice. There's no reason why PostgreSQL on windows *has* to be process based (though of course there would be serious technical issues with changing it). Of course, by simply hand waving and saying "it can never be better", it never will be better. -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] keeping track of when a row was last modified
On Mar 10, 2005, at 4:15 PM, Sally Sally wrote: Does postgres automatically keep track of when a row was last modified? Thanks No. If you look on the SQL list in the past ten-20 minutes, there has been a discussion about this issue. Generally, you can use a trigger to update a timestamp when a row is touched. Sean ---(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] keeping track of when a row was last modified
Nope, although there are plenty of trigger-based examples of doing so in the archives. -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 10, 2005, at 3:15 PM, Sally Sally wrote: Does postgres automatically keep track of when a row was last modified? Thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] keeping track of when a row was last modified
On Thu, Mar 10, 2005 at 09:15:28PM +, Sally Sally wrote: > Does postgres automatically keep track of when a row was last modified? No, but you can set up a trigger to do it. The "Trigger Procedures" section of the PL/pgSQL chapter in the documentation has an example: http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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, Mar 09, 2005 at 09:47:17AM -0800, Ben wrote: > 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? Of course not, and I don't think there's any risk of this happening. Are you aware that every patch submitted for inclusion goes through a code review? It's very insightful to see the discussion and mentality on the -hackers list; data integrity is always the absolute number 1 priority. Anyone who wants to code for PostgreSQL who doesn't share that priority won't last long at all. > 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." With the attitude of "Windows can not be made to reliably run a database", how many developers do you think will be attracted? -- 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 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] keeping track of when a row was last modified
Does postgres automatically keep track of when a row was last modified? Thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL still for Linux only?
On Wed, Mar 09, 2005 at 11:39:53AM -0600, Doug Hall wrote: > 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? There is a perftest project on either pgfoundry or gborg that has been doing performance testing. I think it's all being done on linux right now, but it would certainly be interesting to compare linux, freebsd, and windows. Unfortunately, there's no way to do an apples-to-apples (pun intended) comparison with OS X, since not all of the OS's will run on the same hardware. Linux will run on Power, though, as will OpenBSD. I think FreeBSD's support is still pretty bare, but I'm not certain. -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] New user: Windows, Postgresql, Python
Paul Moore <[EMAIL PROTECTED]> writes: > I suppose my first (lazy) question is, is there a Python 2.4 > compatible plpython.dll available anywhere? Alternatively, is there a > way I can build one for myself? I'm happy enough doing my own build > (I have mingw and msys available), but I'd rather not build the whole > of postgresql if possible, just for the sake of one DLL Actually, I had a go and was surprised to find that the build was pretty simple. Once I'd got a build, copying the plpython DLL (which is built with a different name, AFAICT) over the one installed by the binary installer seems to work fine. Is that OK, or could I hit problems later? Paul. -- Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something. -- Robert Heinlein ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL still for Linux only?
...it will be the first time they have seen your name... ...with your first email have criticised the project... Check the archives. This poster has been active on the list for awhile. Cheers, Rick Richard Huxton To: Tope Akinniyi <[EMAIL PROTECTED]> Sent by: cc: pgsql-general@postgresql.org [EMAIL PROTECTED]Subject: Re: [GENERAL] PostgreSQL still for Linux only? tgresql.org 03/10/2005 01:31 PM Tope Akinniyi wrote: > Hi all, > > In my country Nigeria (and even African continent), we do not eat > what the western world eat. We wear different styles of cloths. In > the same vein, our computerisation culture is different. > > I must submit that computers became popular in Nigeria by Windows > desktop system. While the western world were exposed to *NIX from the > beginning, we were introduced to computing via DOS and later Windows. > That is our IT antecedent and culture. People use database engines > such as Oracle, Firebird, Sybase, mySQL, etc on Windows here and they > manage them and survive. If because you want to recommend PostgreSQL, > you insist on Non-Windows OS, the first question clients ask you is > why is your own different? Why must I switch from Windows to *NIX > because of your PostgreSQL? You might end up not succeeding in that > bid. And we are used to the blue screen (crashes) and each IT house > in Nigeria has gone the extra mile to ensure the safety of the > operations of its clients. Everyone is a product of his environment, > peculiarities and experiences. Not that different from Europe, or I'd guess the U.S. - in many small businesses "computers" mean "Windows". Certainly five year ago customers looked at you funny if you wanted to run on Linux/*BSD. > As an IT organisation that wants to stay in business you need to give > to people what they wants. I think that is the basis of service. I > have some deployments of PostgreSQL on Windows servers. I must admit > that we have not had any problems so far. > > Notwithstanding, due efforts must be made to protect your clients' > operations whether you use Windows or Posix. In that regards, I > thought of reducing the risk factor by implementing replication on > some of the servers. > > I sought Windows replication tool for and could not get. I checked > PgFoundry and the one there put a banner and said NOT FOR WINDOWS. > Then I said is this PostgreSQL for Windows a joke? That prompted my > post - IS POSTGRESQL FOR LINUX ONLY? > > Now, as the CEO of an IT organisation, I want to draft my final > blueprint on PostgreSQL. I need your advice on this. > > 1. If I can manage it, can I continue to use PostgreSQL on Windows > and watch as it evolves? I recognise the points certain respondents > made on earlier; which was PostgreSQL on Windows is still a baby boy, > do not expect it to walk like a man or expect it to possess the > features of a man. Nobody can stop you using PostgreSQL. Ever. Or from giving it away, making changes, selling it etc. > 2. This response is alarming: Tom Lane wrote in digest V1.5092: > >> We are supporting Windows as a Postgres platform for the benefit of >> developers who want to do testing on their laptops (and for reasons >> best known to themselves feel a need to run >Windows on their >> laptops). > > > a. Who are the 'we' Tom is talking about? In an email in the public lists we = Tom > b. Is he speaking for > PostgreSQL Developers and the entire PostgreSQL community? Official pronouncements from "core" will be marked as such. No-one speaks for the "entire" PostgreSQL community. You're part of that community, just by virtue of downloading a copy and subscribing to the lists. > c
Re: [GENERAL] error codes in log file?
On Thu, Mar 10, 2005 at 01:58:52PM -0500, David Parker wrote: > Is there a postgresql.conf directive (7.4.5) which will result in error > codes being emitted in the log file? I am thinking of the error codes > in: > http://www.postgresql.org/docs/7.4/interactive/errcodes-appendix.html. You could set log_error_verbosity to 'verbose', either cluster-wide in postgresql.conf, for a particular database with ALTER DATABASE, for a particular user with ALTER USER, or just for a particular session with SET. Verbose logging might show more than you want, but it'll show the error codes. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PostgreSQL still for Linux only?
Tope, As someone who's been on these lists for several years now, I can honestly say they're among the friendliest and most helpful I've found. i can certainly echo that sentiment. from what i can tell, Tom in particular (since he's been 'called out' here) has (most of the time ...) the patience and good will of a saint ... you can do a LOT worse (e.g. a 17-yr old "CustSvc" rep @ M$) than having someone like him -- and many others -- here to interact with. in the end, there's lots of good & bad DB software on all platforms. imho, its adoption for business purposes ONLY makes sense if there's strong support for it. that support -- whehter it be 'run time' or 'development' can come from (a) your own org (b) help from others on this list (c) formal support from the likes of Command Prompt (unabshed free plug, Josh) when i wear my casual/individual user hat, i depend on this list when people actually (bother to) answer my (sometimes misguided) questions ;-) when wearing my business hat, i NEVER deploy a pgsql solution -- or any other db for that matter -- without some internal ((a)) competence/support if ONLY to have someone to adequately interact with this list (b), and professionally contracted support (c). just my $0.02 ... richard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] error codes in log file?
Is there a postgresql.conf directive (7.4.5) which will result in error codes being emitted in the log file? I am thinking of the error codes in: http://www.postgresql.org/docs/7.4/interactive/errcodes-appendix.html. Or are these just client-interface errors? - DAP--David Parker Tazz Networks (401) 709-5130
Re: [GENERAL] postgres 7.4 build for win
I went for 8.0.1. Now compiling that (I need to compile it with enable-thread-safety) it throws "THREAD-SAFETY requires POSIX signals". Has this issue been looked upon earlier .. I am not totally sure if we need the thread-safey flag on windows or not, but it would be nice to know if someone has seen this earlier.. Thanks.. Amin On Thu, 10 Mar 2005 19:10:05 +0100 "Magnus Hagander" <[EMAIL PROTECTED]> wrote: Hi, I am trying to build postgres 7.4.5 on windows (200) on mingw/msys env. I have installed win32-pthreads to enable-thread safet feature. Couple of issues that I wanted to check:. 7.4 is not supported natively on win32. If you need it on windows, you need to use cygwin. If you can, go to 8.0.1 which is natively supported. (No, it's not just a matter of finding the right way to compile it. There is a *lot* of code changes between 7.4 and 8.0 for win32 compatibility) //Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL still for Linux only?
Tope Akinniyi wrote: Hi all, In my country Nigeria (and even African continent), we do not eat what the western world eat. We wear different styles of cloths. In the same vein, our computerisation culture is different. I must submit that computers became popular in Nigeria by Windows desktop system. While the western world were exposed to *NIX from the beginning, we were introduced to computing via DOS and later Windows. That is our IT antecedent and culture. People use database engines such as Oracle, Firebird, Sybase, mySQL, etc on Windows here and they manage them and survive. If because you want to recommend PostgreSQL, you insist on Non-Windows OS, the first question clients ask you is why is your own different? Why must I switch from Windows to *NIX because of your PostgreSQL? You might end up not succeeding in that bid. And we are used to the blue screen (crashes) and each IT house in Nigeria has gone the extra mile to ensure the safety of the operations of its clients. Everyone is a product of his environment, peculiarities and experiences. Not that different from Europe, or I'd guess the U.S. - in many small businesses "computers" mean "Windows". Certainly five year ago customers looked at you funny if you wanted to run on Linux/*BSD. As an IT organisation that wants to stay in business you need to give to people what they wants. I think that is the basis of service. I have some deployments of PostgreSQL on Windows servers. I must admit that we have not had any problems so far. Notwithstanding, due efforts must be made to protect your clients' operations whether you use Windows or Posix. In that regards, I thought of reducing the risk factor by implementing replication on some of the servers. I sought Windows replication tool for and could not get. I checked PgFoundry and the one there put a banner and said NOT FOR WINDOWS. Then I said is this PostgreSQL for Windows a joke? That prompted my post - IS POSTGRESQL FOR LINUX ONLY? Now, as the CEO of an IT organisation, I want to draft my final blueprint on PostgreSQL. I need your advice on this. 1. If I can manage it, can I continue to use PostgreSQL on Windows and watch as it evolves? I recognise the points certain respondents made on earlier; which was PostgreSQL on Windows is still a baby boy, do not expect it to walk like a man or expect it to possess the features of a man. Nobody can stop you using PostgreSQL. Ever. Or from giving it away, making changes, selling it etc. 2. This response is alarming: Tom Lane wrote in digest V1.5092: We are supporting Windows as a Postgres platform for the benefit of developers who want to do testing on their laptops (and for reasons best known to themselves feel a need to run >Windows on their laptops). a. Who are the 'we' Tom is talking about? In an email in the public lists we = Tom > b. Is he speaking for PostgreSQL Developers and the entire PostgreSQL community? Official pronouncements from "core" will be marked as such. No-one speaks for the "entire" PostgreSQL community. You're part of that community, just by virtue of downloading a copy and subscribing to the lists. > c. Does this mean that PostgreSQL for Windows is just a toy or model - Oh do not take it serious? Or is the Windows version by design a miniature of the *NIX version, lacking the requisite mechanism of a reliable database? The core of PostgreSQL is the same in both versions. It is the connection to the operating-system that differs. There has been a lot of work put in to get it running on Windows (otherwise it would have happened before version 8). It will take time to understand how best to adapt to this new environment, and it may be that *nix systems are always better to run on. > d. And does that mean the developers can decide to withdraw > development and support for the Windows version anytime they so wish? Yes. Short of kidnapping them and torturing them, no-one can force them to work. However, some of them get paid to work on PostgreSQL, and all of them are interested in it. I am not against Linux or any Posix for any reason. In fact one of my two office servers run Mandrake Linux. But I am grateful that PostgreSQL recognises the fact that we all can and will not be in the same boat. So it is good to support many boats. It can also be bad - the more time spent supporting Windows, the less time is spent working on PostgreSQL itself. Tom lane's post is worrisome to me. It bothers on consistency. Would PostgreSQL be consistent for Windows? If not, I think at this stage I can easily roll back and migrate my clients back to other Windows Database system where I feel I will be secured for some time to come as using PostgreSQL does not affect much of my operations. I am just expanding my varieties. Sorry - I'm not sure I understand this paragraph. The code for PostgreSQL is the same in both cases - is that what you mean? I think managing PostgreSQL on OS I desire should be my own duty. The point is that Postgre
Re: [GENERAL] Process list in PostgreSQL 8.0
Ericson Smith <[EMAIL PROTECTED]> writes: > 11679 ? S 0:00 postgres: iacm didit 216.187.113.78(56640) idle > 11680 ? S 0:07 postgres: iacm didit 216.187.113.78(56641) idle > 11863 ? S 0:00 postgres: iacm didit 216.187.113.89(41860) idle > 11864 ? R 0:09 postgres: iacm didit 216.187.113.89(41861) INSERT > 11906 ? S 0:00 postgres: iacm didit 24.189.251.57(49023) idle > Just wondering what the values in parenthesis are and how can we use 'em. Client-side port numbers, IIRC. So you can figure out which connection is which ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] postgres 7.4 build for win
>Hi, > >I am trying to build postgres 7.4.5 on windows (200) on >mingw/msys env. I have installed win32-pthreads to >enable-thread safet feature. Couple of issues that I >wanted to check:. 7.4 is not supported natively on win32. If you need it on windows, you need to use cygwin. If you can, go to 8.0.1 which is natively supported. (No, it's not just a matter of finding the right way to compile it. There is a *lot* of code changes between 7.4 and 8.0 for win32 compatibility) //Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Process list in PostgreSQL 8.0
Hi All, We upgraded to Postgres 8.0 on a Linux (RHEL) machine yesterday and notice a slightly different list when looking at the processes (ps). 11679 ? S 0:00 postgres: iacm didit 216.187.113.78(56640) idle 11680 ? S 0:07 postgres: iacm didit 216.187.113.78(56641) idle 11863 ? S 0:00 postgres: iacm didit 216.187.113.89(41860) idle 11864 ? R 0:09 postgres: iacm didit 216.187.113.89(41861) INSERT 11906 ? S 0:00 postgres: iacm didit 24.189.251.57(49023) idle Just wondering what the values in parenthesis are and how can we use 'em. -- Warmest regards, Ericson Smith Programmer Did-it Search Marketing Raise Your Expectations... --- http://www.did-it.com 55 Maple Avenue, 3rd Floor Rockville Center, NY 11570 P: 800.932.7761 Ext 237 E: [EMAIL PROTECTED] Jupiter Research rates Did-it the #1 Search Engine Marketing agency for market suitability in Search Engine Marketing Agency Constellation Report begin:vcard fn:Ericson Smith n:Smith;Ericson org:Did-it.com;Tech adr:Suite 304;;55 Maple Avenue;Rockville Center;NY;11570;USA email;internet:[EMAIL PROTECTED] title:Tracking Specialist tel;work:516-255-0500 tel;fax:516-255-0509 x-mozilla-html:FALSE url:http://www.did-it.com version:2.1 end:vcard ---(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] postgres 7.4 build for win
Hi Amin, maybe I'm little off but why don't you try the 8.0.1 version which should run on Windows platform? Miroslav Šulc Amin Abdulghani wrote: Hi, I am trying to build postgres 7.4.5 on windows (200) on mingw/msys env. I have installed win32-pthreads to enable-thread safet feature. Couple of issues that I wanted to check:. i) The configure was --enablethread-safety flag for the platform (it was complaining CANNOT enable threads on your platform). I copied src/template/linux to src/template/win32 (most of the contents seemed thread related) and the error was gone. Does this fix seem reasonable. ii) I am now getting an error that postgres doesnt have native spinlock on this platform and to disable the spinlock. Is this the expected behavior for the platform. Thanks.. Amin ---(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 begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] postgres 7.4 build for win
Hi, I am trying to build postgres 7.4.5 on windows (200) on mingw/msys env. I have installed win32-pthreads to enable-thread safet feature. Couple of issues that I wanted to check:. i) The configure was --enablethread-safety flag for the platform (it was complaining CANNOT enable threads on your platform). I copied src/template/linux to src/template/win32 (most of the contents seemed thread related) and the error was gone. Does this fix seem reasonable. ii) I am now getting an error that postgres doesnt have native spinlock on this platform and to disable the spinlock. Is this the expected behavior for the platform. Thanks.. Amin ---(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] normal user dump gives error because of plpgsql
Tom Lane wrote: John Sidney-Woollett <[EMAIL PROTECTED]> writes: I'm pretty sure I had the same problem when using pg_restore. If pl/pgsql is installed in template1, then the restore fails. And I couldn't find any solution to this on the list either. You're supposed to restore into a database cloned from template0, not template1. That's interesting because I normally create my databases using template1 so that I don't have to install pl/pgsql before I start adding pgsql functions. So what you're saying is that if you know you're just about to restore into a new DB (using pg_restore), then create the new DB from template0 instead of template1. I've just spotted the Notes section for pg_restore in the 7.4.x docs which confirms this. Strange that I didn't see it before... need to RTFM better! Thanks. John Sidney-Woollett ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL still for Linux only?
On Thu, 2005-03-10 at 10:19, Tope Akinniyi wrote: > Hi all, Howdy. Glad to have you on the lists. > 1. If I can manage it, can I continue to use PostgreSQL on Windows and > watch as it evolves? I recognise the points certain respondents made > on earlier; which was PostgreSQL on Windows is still a baby boy, do > not expect it to walk like a man or expect it to possess the features > of a man. That's the first problem. PostgreSQL is new on windows. > 2. This response is alarming: > Tom Lane wrote in digest V1.5092: > >We are supporting Windows as a Postgres platform for the benefit of > developers who want to > >do testing on their laptops (and for reasons best known to themselves > feel a need to run >Windows on their laptops). This is the second problem. Windows simply has problems that cause data relibility problems that may or may not be surmountable in the future. > a. Who are the 'we' Tom is talking about? The PostgreSQL Global Development Group, I'd suppose. That's the core team that makes the big decisions. > c. Does this mean that PostgreSQL for Windows is just a toy or model - > Oh do not take it serious? Or is the Windows version by design a > miniature of the *NIX version, lacking the requisite mechanism of a > reliable database? That would be a bit harsh. It's more a combination of several things. 1: Windows / postgresql is quite a bit slower than unix / postgresql. 2: The Windows port is known to have a few issues with heavy load on Windows. 3: PostgreSQL on Windows is a new port, and therefore needs a bit of a shakedown cruise before anyone can definitively say it's stable, fast and reliable. > d. And does that mean the developers can decide to withdraw > development and support for the Windows version anytime they so wish? They could, but I'm not sure they would. It's really up to the folks who developed the port to windows to keep it working and up to date. IF some basic core part of postgresql was changed, and that broke the windows port, and no one was willing or able to fix it, then yes, I guess the port might be abandoned. But that's no more likely for Windows than any other semi-obscure platform that postgresql runs on like AIX or SCO unix. > Tom lane's post is worrisome to me. It bothers on consistency. Would > PostgreSQL be consistent for Windows? If not, I think at this stage I > can easily roll back and migrate my clients back to other Windows > Database system where I feel I will be secured for some time to come > as using PostgreSQL does not affect much of my operations. I am just > expanding my varieties. Any new port of a database to a new operating system presents the possibility that some corner case that no one has tested before will pop up and corrupt your data at some point. So, from that perspective, PostgreSQL on windows is not considered 100% reliable yet. Not because of a lot of known problems, but because of a lack of heavy testing in a large and diverse group of production environments. > Off the topic: > --> > Uwe C. Schroeder wrote: > >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 do not seem to be comfortable with this "Windows will spoil > PostgreSQL reputation position" as posted by Schroeder. Is PostgreSQL > the only database engine running on Windows? There are million of > licences of Oracle, mySQL, Sybase, etc for Windows servers. But those databases have years to get shaken down into shape. PostgreSQL is new on that platform, so caution is a good thing there. > I will appreciate your kind response on this before I finally take my > decision on whether to continue with PostgreSQL for Windows for now. I encourage you to keep using it, and contribute in any way you can. PostgreSQL has one of the most active and helpful user communities there is around any open source project. And it's a great database to boot. I never thought your post was a troll, by the way. I just thought you weren't very familiar with the whole "PostgreSQL ported to Windows" set of issues and therefore phrased your questions in ways that made some eyebrows pop up. Welcome to the community! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] normal user dump gives error because of plpgsql
John Sidney-Woollett <[EMAIL PROTECTED]> writes: > I'm pretty sure I had the same problem when using pg_restore. If > pl/pgsql is installed in template1, then the restore fails. > And I couldn't find any solution to this on the list either. You're supposed to restore into a database cloned from template0, not template1. > Can pg_restore be made to ignore the error? It does, at least since 8.0. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] normal user dump gives error because of plpgsql
I'm pretty sure I had the same problem when using pg_restore. If pl/pgsql is installed in template1, then the restore fails. And I couldn't find any solution to this on the list either. I definitely want pl/pgsql in template1 so that any databases I create have access to pl/pgsql without having to explicitly install it in each new db I create in the cluster. Recreating the database using psql dbname < dump.sql does work OK, but not pg_restore. Can pg_restore be made to ignore the error? John Sidney-Woollett Martijn van Oosterhout wrote: On Thu, Mar 10, 2005 at 01:31:26PM +0100, Janning Vygen wrote: Hi, i have a normal user with rights to create a db. template1 contains language plpgsql. the user wants to - dump his db - drop his db - create it again - and use the dump file to fill it. it gives errors because of CREATE LANGUAGE statements inside the dump. I don't beleive there is. But it's not really needed, you get and error and the restore continues. The only thing different is that you don't get a message, the result is the same. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Problem with inherited table, can you help?...
On Thu, Mar 10, 2005 at 01:31:21AM -0800, Net Virtual Mailing Lists wrote: > 1. set search_path=jl_site2,public; > 2. BEGIN; > 3. INSERT INTO locations (user_id, city, state_id, zip, country_id, > loc_type, deleted, entered_dt) VALUES (37613, 'Glendale', 3, '85301', 1, > 'secondary', 'f', now()); > > .. at this point connection #2 is blocked until I either commit or > rollback the in-process transaction in connection As you guessed, connection #2 is blocked because of a concurrent transaction inserting another record with the same country_id foreign key. PostgreSQL acquires a row-level lock on the referenced key to ensure that it doesn't change while the referencing transaction remains open. Unfortunately it's an exclusive lock, which causes other transactions to block when they try to lock the same row. > More importantly what I can do about this. The countries/states table are > basically static and won't change, but I want the constraint check in > place because it just seems like a good practice. Constraints are indeed good practice. This has come up before, and one possibility is to make the foreign key constraint deferrable and defer its integrity checks so they aren't made until commit time. This has problems of its own, however: you won't detect referential integrity violations until the transaction commits, so you won't get an error for the specific statement that caused the violation. Some have suggested that PostgreSQL should use a weaker lock on the referenced key, but that hasn't been implemented yet. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Loading of native libraries in PLJAVA
Nageshwar,
This is not the right forum for PL/Java issues. You should use mailing
list at [EMAIL PROTECTED]
We are facing 2 problems in loading files from Pljava.
1. Loading Native C library from Pljava trigger.
From Pljava trigger, we were trying to load native
library, Using System.loadLibrary(""), which is
specified in the LD_LIBRARY_PATH.
>
The likely cause of this is that this library in turn have dependencies
to something not specified in the LD_LIBRARY_PATH or that you have some
mismatch in function naming. That in turn should be apparent from the
exception message (can't tell since you didn't convey it here).
Also specified the library file under /var/lib/pljava
where all the pljava specifc "*.so" files are located, which has an
entry in the postgresql.conf -
dynamic_library_path for "/var/lib/pljava",
Both theses cases failed to load native library.
we are getting this error in both cases-
UnSatisfiedLinkedError
>
The dynamic_library_path is only used by the PostgreSQL backend when it
loads a module. It has no effect whatsoever on the System.loadLibrary
method.
2. Loading Properties file from trigger. (using
Properties.load())
We are unable to load properties file from
Properties.load("").
we have included properties file in jar file and
installed the jar and set the jar file in the classpath using
"sqlj.set_classpath".
To my knowledge there's Properties.load method that takes a String argument.
You have to use the Class.getResourceAsString() in order to obtain an
InputStream for a resource stored in a jar file. The class in question
should be in the same package as the resource (i.e. your properties file).
Regards,
Thomas Hallgren
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL still for Linux only?
Hi all, In my country Nigeria (and even African continent), we do not eat what the western world eat. We wear different styles of cloths. In the same vein, our computerisation culture is different. I must submit that computers became popular in Nigeria by Windows desktop system. While the western world were exposed to *NIX from the beginning, we were introduced to computing via DOS and later Windows. That is our IT antecedent and culture. People use database engines such as Oracle, Firebird, Sybase, mySQL, etc on Windows here and they manage them and survive. If because you want to recommend PostgreSQL, you insist on Non-Windows OS, the first question clients ask you is why is your own different? Why must I switch from Windows to *NIX because of your PostgreSQL? You might end up not succeeding in that bid. And we are used to the blue screen (crashes) and each IT house in Nigeria has gone the extra mile to ensure the safety of the operations of its clients. Everyone is a product of his environment, peculiarities and experiences. As an IT organisation that wants to stay in business you need to give to people what they wants. I think that is the basis of service. I have some deployments of PostgreSQL on Windows servers. I must admit that we have not had any problems so far. Notwithstanding, due efforts must be made to protect your clients' operations whether you use Windows or Posix. In that regards, I thought of reducing the risk factor by implementing replication on some of the servers. I sought Windows replication tool for and could not get. I checked PgFoundry and the one there put a banner and said NOT FOR WINDOWS. Then I said is this PostgreSQL for Windows a joke? That prompted my post - IS POSTGRESQL FOR LINUX ONLY? Now, as the CEO of an IT organisation, I want to draft my final blueprint on PostgreSQL. I need your advice on this. 1. If I can manage it, can I continue to use PostgreSQL on Windows and watch as it evolves? I recognise the points certain respondents made on earlier; which was PostgreSQL on Windows is still a baby boy, do not expect it to walk like a man or expect it to possess the features of a man. 2. This response is alarming: Tom Lane wrote in digest V1.5092:>We are supporting Windows as a Postgres platform for the benefit of developers who want to>do testing on their laptops (and for reasons best known to themselves feel a need to run >Windows on their laptops). a. Who are the 'we' Tom is talking about?b. Is he speaking for PostgreSQL Developers and the entire PostgreSQL community?c. Does this mean that PostgreSQL for Windows is just a toy or model - Oh do not take it serious? Or is the Windows version by design a miniature of the *NIX version, lacking the requisite mechanism of a reliable database?d. And does that mean the developers can decide to withdraw development and support for the Windows version anytime they so wish? I am not against Linux or any Posix for any reason. In fact one of my two office servers run Mandrake Linux. But I am grateful that PostgreSQL recognises the fact that we all can and will not be in the same boat. So it is good to support many boats. Tom lane's post is worrisome to me. It bothers on consistency. Would PostgreSQL be consistent for Windows? If not, I think at this stage I can easily roll back and migrate my clients back to other Windows Database system where I feel I will be secured for some time to come as using PostgreSQL does not affect much of my operations. I am just expanding my varieties. I think managing PostgreSQL on OS I desire should be my own duty. The point is that PostgreSQL can be available for what I choose to use it for and where I choose to use it. Managing failure points of my OS should be left to my technical expertise. Well if I can get some support from some sources, fine. Off the topic:-->Uwe C. Schroeder wrote:>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 do not seem to be comfortable with this "Windows will spoil PostgreSQL reputation position" as posted by Schroeder. Is PostgreSQL the only database engine running on Windows? There are million of licences of Oracle, mySQL, Sybase, etc for Windows servers. The company that uses them are up and running; not as if only organisations running DB on Posix are existing. Who blames mySQL or Oracle when it crashes on Windows OS? If PostgreSQL cannot thrive where others thrive, it will be quite unfortunate. You cannot shut yourself indoors because you anticipate a rainfall (that might not come). What would be the empirical basis for our judgement if PostgreSQL is not used on Windows? Crashing MS Office on Windows is a different situation from what you would get running PostgreSQL. I do often
Re: [GENERAL] postgres 8 settings
"vinita bansal" <[EMAIL PROTECTED]> writes: > Do these settings seem fine or I am making some mistake. These settings when > used with Postgres 7.4 gave me good results but they don't seem to work with > Postgres 8.0. Am I missing out on something?? Define "don't seem to work", please. Offhand the only thing that comes to mind is that the GEQO parameters changed meaning a bit in 8.0; you shouldn't blindly set geqo_effort the same as you used to. But with geqo_threshold so high it may not matter ... do you even have any queries with more than 25 tables? Also, it's just plain bizarre to have work_mem larger than maintenance_work_mem; I cannot imagine any sane reason to do that. Are you sure that work_mem = 128M is really a safe setting on your hardware? If you have a good number of clients all concurrently doing complicated queries, you could easily find yourself pushed into swapping. I concur with Mike's thought that shared_buffers = 10 is on the high side. wal_buffers = 1000 strikes me as a waste of RAM too; do you have any proof that it helps to set it above 10? Do you really think it's a good idea to disable the bgwriter? Especially with such a long intercheckpoint time? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Loading of native libraries in PLJAVA
Title: Loading of native libraries in PLJAVA
We are facing 2 problems in loading files from Pljava.
1. Loading Native C library from Pljava trigger.
From Pljava trigger, we were trying to load native library, Using System.loadLibrary(""), which is
specified in the LD_LIBRARY_PATH.
Also specified the library file under /var/lib/pljava where all the pljava specifc "*.so" files are located, which has an entry in the postgresql.conf - dynamic_library_path for "/var/lib/pljava",
Both theses cases failed to load native library.
we are getting this error in both cases- UnSatisfiedLinkedError
2. Loading Properties file from trigger. (using Properties.load())
We are unable to load properties file from Properties.load("").
we have included properties file in jar file and installed the jar and set the jar file in the classpath using "sqlj.set_classpath".
Any light on this.
Thnx
Rao
Re: [GENERAL] partitionning
On Wed, 2005-03-09 at 17:29, Greg Stark wrote: > Scott Marlowe <[EMAIL PROTECTED]> writes: > > 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. Well, this is PostgreSQL, an extensible database, so the answer, to me, is to implement it with a simple set of UDFs in userland as a proof of concept. much like the materialized views recently discussed here. After that, if someone thinks the basic concept is sound, it should get migrated into the backend. > 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. Sure, I'd love that too. But I think it's a bit too far ahead of the game right now. > 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. I would imagine that both might be saved by the same task. i.e. if indexes and triggers could span across multiple tables etc... then partitioned tables would be a pretty simple view creation. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgres db failure
marcelo Cortez wrote: hi folks the postgres fail with follow mwssage pg_dump: dumpClasses(): SQL command failed pg_dump: Error message from server: ERROR: xlog flush request 190/3F08779C is not satisfied --- flushed only to 190/3FD0 CONTEXT: writing block 268606 of relation 17142/17501 What happened to cause this? What version are you running? What platform are you running on? Have you taken full backups of *all* your pgsql files? -- Richard Huxton Archonet Ltd ---(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?
While we run PostgreSQL on Free-BSD for our production systems, we have 'demo' laptop windows XP systems that contain the entire server architecture (application server, database, win32 client, etc). Sure is handy to be able to run PostgreSQL on windows and not have to change anything.. 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. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] postgres db failure
hi folks the postgres fail with follow mwssage pg_dump: dumpClasses(): SQL command failed pg_dump: Error message from server: ERROR: xlog flush request 190/3F08779C is not satisfied --- flushed only to 190/3FD0 CONTEXT: writing block 268606 of relation 17142/17501 any clue? best regards MDc __ Correo Yahoo! Espacio para todos tus mensajes, antivirus y antispam ¡gratis! ¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] postgres 8 settings
On Thu, 10 Mar 2005 09:58:02 +, vinita bansal <[EMAIL PROTECTED]> wrote: > Hi, > > I have a 64 bit Opteron m/c with 32GB RAM and ~500GB HardDrive. The database > size is ~45GB. > I've got a similar box, but with only 16G RAM. What is the storage subsystem, fibre channel or SCSI? Also, what OS? > I am using the following values in postgresql.conf: > > shared_buffers = 10 Seems high. I did some testing with my real data and found that anything of 15000 wasn't really gaining my anything. This is an 8.x config file (maintenance_work_mem vs. sort_mem), and pg 8+ can actually make do with smaller shared_buffers because of the ARC (soon to be 2Q) buffer management algorithm. Unless your working set PER QUERY is enormous I would suggest lowering this. > work_mem = 128000 That's fine, but you may need to bump it up if (as above) you have individual queries that sort/group huge rowsets. > maintenance_work_mem = 10 Remember to pump this way up when building very large indexes. > max_fsm_pages = 20 Should probably be bigger. Mine is 200 (2 million). > bgwriter_percent = 0 > bgwriter_maxpages = 0 > fsync = false *KLAXON SOUNDS* Unless you dislike having your data around after power/hardware anomalies you'd better turn that on! > wal_buffers = 1000 > checkpoint_segments = 2048 This will require 16 * ((2 * 1000) + 1) MB of drive space... 100 should be fine, really. (next setting related) > checkpoint_timeout = 3600 Conventional wisdom is not to set this higher than 1800. It also means that you can cut your checkpoint_segments in half (more or less). > effective_cache_size = 184 > random_page_cost = 2 This might be low, but it depends on your storage subsystem. Is it fibre channel? > geqo_threshold = 25 Wide queries, eh? > geqo_effort = 1 > stats_start_collector = false > stats_command_string = false > If you want to use pg_autovacuum then you will need to turn the stats stuff back on, including row statistics. Plus it's a big help in debugging. > Do these settings seem fine or I am making some mistake. These settings when > used with Postgres 7.4 gave me good results but they don't seem to work with > Postgres 8.0. Am I missing out on something?? > > Regards, > Vinita Bansal > > _ > Click, Upload, Print. http://www.kodakexpress.co.in?soe=4956 Deliver in > India. > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] pl sql to check if table of table_name exists
Title: RE: [GENERAL] pl sql to check if table of table_name exists Hi Sim Thanks for your input. Kind Regards,Shaun Clements -Original Message-From: Sim Zacks [mailto:[EMAIL PROTECTED]Sent: 10 March 2005 02:47 PMTo: pgsql-general@postgresql.orgSubject: Re: [GENERAL] pl sql to check if table of table_name exists I'm glad to hear you got it working. In explanation to my response: the pg_class internal table lists all the relationships in the database. relkind='r' means that the relation you are looking for is a table (relation), I believe that will also find views. relname is the name of the object if your table is called parts select * from pg_class where relkind='r' and relname='parts' will give you the pg_class record for the table if it exists and nothing if it doesn't. you could also do a select count(*) or select 1 In any case if there is a resultset the table exists and if there is no resultset the the table does not. Using the pg_tables view is a better idea in any case, as it is cleaner. Sim "Shaun Clements" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... Hi Sim Thanks for your response. I had it working from a previous post by Adam Tomjack. -- A list of tables: SELECT schemaname, tablename FROM pg_tables; -- Returns true if a table exists: SELECT count(*)>0 FROM pg_tables WHERE schemaname='...' AND tablename='...' Your response does not work for me. Perhaps you can explain the posted command * from pg_class where relkind='r' and relname=your_tablename Kind Regards,Shaun Clements -Original Message-From: Sim Zacks [mailto:[EMAIL PROTECTED]Sent: 10 March 2005 01:24 PMTo: pgsql-general@postgresql.orgSubject: Re: [GENERAL] pl sql to check if table of table_name exists i mean select * from pg_class where relkind='r' and relname=your_tablename "Sim Zacks" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... select your_tablename from pg_class where relkind='r' "Shaun Clements" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... Hi Hate to ask, but it isnt obvious to me from the documentation. How do I perform a query in pgplsql, to check it a table exists of a particular name. Thanks in advance Kind Regards, Shaun Clements
Re: [GENERAL] pl sql to check if table of table_name exists
Title: RE: [GENERAL] pl sql to check if table of table_name exists I'm glad to hear you got it working. In explanation to my response: the pg_class internal table lists all the relationships in the database. relkind='r' means that the relation you are looking for is a table (relation), I believe that will also find views. relname is the name of the object if your table is called parts select * from pg_class where relkind='r' and relname='parts' will give you the pg_class record for the table if it exists and nothing if it doesn't. you could also do a select count(*) or select 1 In any case if there is a resultset the table exists and if there is no resultset the the table does not. Using the pg_tables view is a better idea in any case, as it is cleaner. Sim "Shaun Clements" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... Hi Sim Thanks for your response. I had it working from a previous post by Adam Tomjack. -- A list of tables: SELECT schemaname, tablename FROM pg_tables; -- Returns true if a table exists: SELECT count(*)>0 FROM pg_tables WHERE schemaname='...' AND tablename='...' Your response does not work for me. Perhaps you can explain the posted command * from pg_class where relkind='r' and relname=your_tablename Kind Regards,Shaun Clements -Original Message-From: Sim Zacks [mailto:[EMAIL PROTECTED]Sent: 10 March 2005 01:24 PMTo: pgsql-general@postgresql.orgSubject: Re: [GENERAL] pl sql to check if table of table_name exists i mean select * from pg_class where relkind='r' and relname=your_tablename "Sim Zacks" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... select your_tablename from pg_class where relkind='r' "Shaun Clements" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... Hi Hate to ask, but it isnt obvious to me from the documentation. How do I perform a query in pgplsql, to check it a table exists of a particular name. Thanks in advance Kind Regards, Shaun Clements
Re: [GENERAL] normal user dump gives error because of plpgsql
On Thu, Mar 10, 2005 at 01:31:26PM +0100, Janning Vygen wrote: > Hi, > > i have a normal user with rights to create a db. template1 contains language > plpgsql. the user wants to > - dump his db > - drop his db > - create it again > - and use the dump file to fill it. > > it gives errors because of CREATE LANGUAGE statements inside the dump. I don't beleive there is. But it's not really needed, you get and error and the restore continues. The only thing different is that you don't get a message, the result is the same. -- 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. pgpKYmUaMgkAp.pgp Description: PGP signature
Re: [GENERAL] Locale problem
go wrote: Hi ! Im running PG 7.23 now with text fields in CP866. I tried to restore dump from 7.23 into PG 8.01 with following settings : - initdb --locale ru_RU - createdb -E ALT. all works great , but sort order in text fields is wrong Both on Linux and Windows you should use for your databases the same encoding as your system supports. On Linux you can see your locales using 'locale -a'. On my Linux I have these ru_RU.*: ru_RU ru_RU.iso88595 ru_RU.koi8r ru_RU.utf8 I would try 'initdb --locale ru_RU.utf8' (or any other with specified encoding) and set the same encoding for the database (= in this example 'UNICODE'). Then the sorting should be correct. I'm just a newbie for PostgreSQL so somebody might explain it more precisely. People, help pls to megrate to PG 8.01 properly Thanks, and have a nice day!! Igor Miroslav Šulc begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] normal user dump gives error because of plpgsql
Hi, i have a normal user with rights to create a db. template1 contains language plpgsql. the user wants to - dump his db - drop his db - create it again - and use the dump file to fill it. it gives errors because of CREATE LANGUAGE statements inside the dump. How can i prevent that the dump contains CREATE LANGUAGE statements. They are not needed if template1 contains the language, right? I didnt found anythng in the archives even though i am sure not to be the first one having this problem. kind regards, janning here is what i did with 7.4.6: +++ AS DB SUPERUSER # createlang plpgsql template1 # createuser -Ad testuser CREATE USER # su testuser +++ AS TESTUSER $ createdb $ pg_dump -O -x > /tmp/dump.sql $ dropdb $ createdb $ psql testuser < /tmp/dump.sql SET SET SET ERROR: permission denied for language c ERROR: must be superuser to create procedural language ERROR: must be owner of schema public kind regards, janning -- PLANWERK 6 websolutions Herzogstraße 85, 40215 Düsseldorf Tel.: 0211-6015919 Fax: 0211-6015917 http://www.planwerk6.de/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] pl sql to check if table of table_name exists
Title: RE: [GENERAL] pl sql to check if table of table_name exists Hi Sim Thanks for your response. I had it working from a previous post by Adam Tomjack. -- A list of tables: SELECT schemaname, tablename FROM pg_tables; -- Returns true if a table exists: SELECT count(*)>0 FROM pg_tables WHERE schemaname='...' AND tablename='...' Your response does not work for me. Perhaps you can explain the posted command * from pg_class where relkind='r' and relname=your_tablename Kind Regards,Shaun Clements -Original Message-From: Sim Zacks [mailto:[EMAIL PROTECTED]Sent: 10 March 2005 01:24 PMTo: pgsql-general@postgresql.orgSubject: Re: [GENERAL] pl sql to check if table of table_name exists i mean select * from pg_class where relkind='r' and relname=your_tablename "Sim Zacks" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... select your_tablename from pg_class where relkind='r' "Shaun Clements" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... Hi Hate to ask, but it isnt obvious to me from the documentation. How do I perform a query in pgplsql, to check it a table exists of a particular name. Thanks in advance Kind Regards, Shaun Clements
Re: [GENERAL] pl sql to check if table of table_name exists
Title: RE: [GENERAL] pl sql to check if table of table_name exists i mean select * from pg_class where relkind='r' and relname=your_tablename "Sim Zacks" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... select your_tablename from pg_class where relkind='r' "Shaun Clements" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... Hi Hate to ask, but it isnt obvious to me from the documentation. How do I perform a query in pgplsql, to check it a table exists of a particular name. Thanks in advance Kind Regards, Shaun Clements
Re: [GENERAL] pl sql to check if table of table_name exists
Title: RE: [GENERAL] pl sql to check if table of table_name exists select your_tablename from pg_class where relkind='r' "Shaun Clements" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... Hi Hate to ask, but it isnt obvious to me from the documentation. How do I perform a query in pgplsql, to check it a table exists of a particular name. Thanks in advance Kind Regards, Shaun Clements
Re: [GENERAL] PostgreSQL still for Linux only?
Joshua D. Drake wrote: Nobody should ever put a server regardless of OS on a public IP. It should always be firewalled/Nat/Port Forwarding. Except for the firewall/Nat server, of course :D -- Alban Hertroys ---(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] Locale problem
Hi ! Im running PG 7.23 now with text fields in CP866. I tried to restore dump from 7.23 into PG 8.01 with following settings : - initdb --locale ru_RU - createdb -E ALT. all works great , but sort order in text fields is wrong People, help pls to megrate to PG 8.01 properly Thanks, and have a nice day!! Igor ---(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] postgres 8 settings
Hi, I have a 64 bit Opteron m/c with 32GB RAM and ~500GB HardDrive. The database size is ~45GB. I am using the following values in postgresql.conf: shared_buffers = 10 work_mem = 128000 maintenance_work_mem = 10 max_fsm_pages = 20 bgwriter_percent = 0 bgwriter_maxpages = 0 fsync = false wal_buffers = 1000 checkpoint_segments = 2048 checkpoint_timeout = 3600 effective_cache_size = 184 random_page_cost = 2 geqo_threshold = 25 geqo_effort = 1 stats_start_collector = false stats_command_string = false Do these settings seem fine or I am making some mistake. These settings when used with Postgres 7.4 gave me good results but they don't seem to work with Postgres 8.0. Am I missing out on something?? Regards, Vinita Bansal _ Click, Upload, Print. http://www.kodakexpress.co.in?soe=4956 Deliver in India. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Disabling triggers in a transaction
I just got this rather wild idea, don't really have the opportunity to
think it through thoroughly right now...
Say, you create a table with a set of triggers that have some method of
checking whether triggers should be enabled in this session, and
containing some "stubs" where actual trigger implementations could be
inserted.
Wouldn't it work if you create all your tables inheriting that "special"
table? This way you would automagically have all your tables understand
the disabling of triggers in a session without having to worry about it.
Triggers on those tables would have to be activated through the stubs,
instead of by the usual means.
I am kind of curious where this could lead... :P
Adam Tomjack wrote:
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
--
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] pl sql to check if table of table_name exists
Shaun Clements wrote: Hi Hate to ask, but it isnt obvious to me from the documentation. How do I perform a query in pgplsql, to check it a table exists of a particular name. Check the manual. There are two ways to d othis. You could query the data catalogs directly (something like count(*) from pg_class where relname = $1), but this is not preferred because you have the possibilities that the data catalogs will be changed in the future. The better way to do this is to query the information schema. I forget the table name but it may be something like (select count(*) from information_schema.tables where table_name = $1). the structure of the information schema is defined in the SQL standards and will be stable between versions. Best Wishes, Chris Travers Metatron Technology COnsulting Thanks in advance Kind Regards, Shaun Clements ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Problem with inherited table, can you help?...
I have the following three tables and my inserts are blocking each other
in a way I just can't understand Can someone point me in the
direction as to what is causing this?
jobs=> \d master.locations
Table "master.locations"
Column|Type |
Modifiers
-+-
+
location_id | integer | not null default
nextval('master.locations_location_id_seq'::text)
user_id | integer |
addr1 | character varying(50) |
addr2 | character varying(50) |
city| character varying(50) | not null
state_id| integer |
state_other | character varying(50) |
country_id | integer |
zip | character varying(35) | not null
loc_type| character varying(9)|
deleted | boolean | not null
entered_dt | timestamp without time zone | not null
updated_dt | timestamp without time zone |
Check constraints:
"locations_loc_type" CHECK (loc_type::text = 'primary'::text OR
loc_type::text = 'secondary'::text)
jobs=> \d jl_site1.locations
Table "jl_site1.locations"
Column|Type |
Modifiers
-+-
+
location_id | integer | not null default
nextval('master.locations_location_id_seq'::text)
user_id | integer |
addr1 | character varying(50) |
addr2 | character varying(50) |
city| character varying(50) | not null
state_id| integer |
state_other | character varying(50) |
country_id | integer |
zip | character varying(35) | not null
loc_type| character varying(9)|
deleted | boolean | not null
entered_dt | timestamp without time zone | not null
updated_dt | timestamp without time zone |
Indexes:
"locations_pkey" primary key, btree (location_id)
"locations_location_id_key" unique, btree (location_id)
"locations_country_id_idx" btree (country_id)
"locations_state_id_idx" btree (state_id)
"locations_user_id_idx" btree (user_id)
"locations_zip_idx" btree (zip)
Check constraints:
"locations_loc_type" CHECK (loc_type::text = 'primary'::text OR
loc_type::text = 'secondary'::text)
Foreign-key constraints:
"$3" FOREIGN KEY (user_id) REFERENCES jl_site1.customer(id) ON UPDATE
CASCADE ON DELETE CASCADE
"$2" FOREIGN KEY (country_id) REFERENCES countries(country_id) ON
DELETE RESTRICT
"$1" FOREIGN KEY (state_id) REFERENCES states(state_id) ON DELETE RESTRICT
Inherits: locations
jobs=> \d jl_site2.locations
Table "jl_site2.locations"
Column|Type |
Modifiers
-+-
+
location_id | integer | not null default
nextval('master.locations_location_id_seq'::text)
user_id | integer |
addr1 | character varying(50) |
addr2 | character varying(50) |
city| character varying(50) | not null
state_id| integer |
state_other | character varying(50) |
country_id | integer |
zip | character varying(35) | not null
loc_type| character varying(9)|
deleted | boolean | not null
entered_dt | timestamp without time zone | not null
updated_dt | timestamp without time zone |
Indexes:
"locations_pkey" primary key, btree (location_id)
"locations_location_id_key" unique, btree (location_id)
"locations_country_id_idx" btree (country_id)
"locations_state_id_idx" btree (state_id)
"locations_user_id_idx" btree (user_id)
"locations_zip_idx" btree (zip)
Check constraints:
"locations_loc_type" CHECK (loc_type::text = 'primary'::text OR
loc_type::text = 'secondary'::text)
Foreign-key constraints:
"$3" FOREIGN KEY (user_id) REFERENCES jl_site2.customer(id) ON UPDATE
CASCADE ON DELETE CASCADE
"$2" FOREIGN KEY (country_id) REFERENCES countries(country_id) ON
DELETE RESTRICT
"$1" FOREIGN KEY (state_id) REFERENCES states(state_id) ON DELETE RESTRICT
Inherits: locations
(NOTE: at this point, hopefull it is clear that both jl_site1 and
jl_site2 inherit the master.locations table)
In connection #1, I do:
1. set search_path=jl_s
Re: [GENERAL] partitionning
Greg Stark wrote: 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. I don't know about deprecating inheritance, but I agree with pretty much everything Greg has said on this thread. In particular, I have felt for several years now that the inheritance infrastructure could be used to implement table partitioning. We're using inheritance for DIY table partitioning on very expensive storage hardware (~$500K), and we'd be dead in the water without it. Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] pl sql to check if table of table_name exists
Title: RE: [GENERAL] pl sql to check if table of table_name exists Much appreciated. Thanks Kind Regards, Shaun Clements -Original Message- From: Adam Tomjack [mailto:[EMAIL PROTECTED]] Sent: 10 March 2005 11:04 AM To: Shaun Clements Cc: postgresql Subject: Re: [GENERAL] pl sql to check if table of table_name exists Shaun Clements wrote: > Hi > > Hate to ask, but it isnt obvious to me from the documentation. > How do I perform a query in pgplsql, to check it a table exists of a > particular name. > > Thanks in advance > > Kind Regards, > Shaun Clements > -- A list of tables: SELECT schemaname, tablename FROM pg_tables; -- Returns true if a table exists: SELECT count(*)>0 FROM pg_tables WHERE schemaname='...' AND tablename='...' -- Here's an untested function: CREATE OR REPLACE FUNCTION table_exists(TEXT, TEXT) RETURNS BOOLEAN AS ' DECLARE r RECORD; BEGIN SELECT INTO r count(*)>0 AS exists FROM pg_tables WHERE schemaname='$1' AND tablename='$2' RETURN r.exists; END; ' LANGUAGE plpgsql STABLE; Check out http://www.postgresql.org/docs/8.0/static/catalogs.html for more info. Adam
Re: [GENERAL] pl sql to check if table of table_name exists
Title: RE: [GENERAL] pl sql to check if table of table_name exists Hi Hate to ask, but it isnt obvious to me from the documentation. How do I perform a query in pgplsql, to check it a table exists of a particular name. Thanks in advance Kind Regards, Shaun Clements
[GENERAL] the impact of encoding on performance.
Hi All Snip <<< The drawback of using locales other than C or POSIX in PostgreSQL is its performance impact. It slows character handling and prevents ordinary indexes from being used by LIKE. For this reason use locales only if you actually need them. snip; What is the impact of the locale on the server ? is it irelevant, small or huge ? Encoding of the DB impact performance too ? UTF8, 8859-8 ? Thanks -- -- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 Cel: 972-52-8555757 Fax: 972-4-6990098 http://www.canaan.net.il -- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
