Re: [GENERAL] shadowing (like IB/Firebird)
On 4/27/04 11:48 PM, "David Garamond" <[EMAIL PROTECTED]> wrote: > Does this mean software RAID is actually safer than hardware RAID? > (Since the OS and processor is usually more reliable than a disc > controller). I'm not sure I would jump to that conclusion. If a controller went bad and trashed a disk below a software RAID, who knows how the RAID software would handle it? The only point I was making is that RAID usually, but not always, protects against hardware failures. There are a number of things it doesn't protect against. I'd guess in the last 15 years or so I've seen a RAID not prevent data loss on a hardware failure an average of every 2-3 years +/-. The most recent was a couple of weeks ago when 2 disks failed (the conjecture is that one died and took out the other, but no one knows for sure). Then there's all the software failures - program errors, corrupted file systems (we had that about a month ago on a ReiserFS), etc. Wes ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] shadowing (like IB/Firebird)
[EMAIL PROTECTED] wrote: died it caused corruption elsewhere. I have also seen (a couple of times) a controller go bad and proceed to write garbage all over the disks. The mirroring worked quite well - we had a very nice file system full of mirrored garbage. Does this mean software RAID is actually safer than hardware RAID? (Since the OS and processor is usually more reliable than a disc controller). -- dave ---(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] Cannot open relation pg_cast_source_target_index
Devrim GUNDUZ <[EMAIL PROTECTED]> writes: > I've just upgraded a production database from 7.3.4 to 7.4.2. > I 'sometimes' get the following error while executing a PHP code: > "mdfd_getrelnfd cannot open relation pg_cast_source_target_index" There is no such message text in 7.4.*. Better double-check that upgrade procedure... (Actually, there is *no* version that produces that error message without including a kernel error message to go with; why are you failing to provide the most important part of the message?) 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] WAL details
Simon Windsor wrote: > Hi > > I want to be preserve a log of all SQL actions that change a database, (ie > INSERT, UPDATE, DELETE, CREATE), to provide a full audit trail of the date, > and to allow the recreation of a database at any time. > > I can achieve this wth ORACLE(recover and others) and MySQL(mysqlbinlog) quite > easily. How do I achieve this with Postgres? Use log_statement. In 7.5 you can log only data modification queries. z -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Arbitrary precision modulo operation
Alvaro Herrera wrote: On Mon, Apr 26, 2004 at 12:48:45PM -0700, Dann Corbit wrote: Maple output: y := 123456789012345678901234567890 mod 123; y := 117 PgSQL 7.3.6 gives the right answer (117), 7.4 gets it wrong (-6). Most likely a bug was introduced when NUMERIC was rewritten. Strange it hasn't been noticed before. mod(x, y) is computed as x - trunc(x / y) * y in the mod_var() function (I think). However, it appears that the division operator itself is rounding up, such that the trunc() function (which ought to round down) does no good as a round up has already occurred. Thus, the value of (x / y) is 1 too large, and so x % y is actually giving you (x % y) - y, a negative number. I tried looking at how the division actually works, but it is over my head at least for the 30 minute perusal. Regards, Paul Tillotson --- [EMAIL PROTECTED] paul]$ bc bc 1.06 Copyright 1991-1994, 1997, 1998, 2000 Free Software Foundation, Inc. This is free software with ABSOLUTELY NO WARRANTY. For details type `warranty'. 11 / 6 18518518518518518 [EMAIL PROTECTED] bin]$ ./psql -U postgres template1 Welcome to psql 7.4.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit template1=# select 11::numeric / 6; ?column? --- 18518518518518519 (1 row) template1=# select 11 / 6; ?column? --- 18518518518518518 (1 row) template1=# select version(); version - PostgreSQL 7.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2 20031022 (Red Hat Linux 3.3.2-1) (1 row) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Question
Jerry: I bet you are using Microsoft Access to view the data. From what I remember, Access prints #Deleted if the record that you try to update gets deleted (or if the primary key for the record gets updated) while you are looking at it. In other words, if you are looking at the record that has custid = 317, cust_name = 'John P Smith' and you type "John Q Smith" in the cust_name, field, then ACCESS tries to do UPDATE table SET cust_name = 'John Q Smith' WHERE custid = 317 behind your back. If it finds that no rows were updated, then it concludes that the row with custid = 317 must not exist anymore, and prints #Deleted in all of the fields for that row. (It may also periodically check to see that a record is still available, in which case the #Deleted might appear without you trying to edit anything.) Does this table ever get updated or deleted from? If so, check who else is deleting or updating while you are looking at the data. You should make a careful check to see if this is the case before assuming that this is a problem with postgres. Regards, Paul Tillotson On Fri, 23 Apr 2004, Jerry Robertson wrote: We have been running Postgre for over a year and are very pleased with its ease of use and performance. We have encountered one problem that has been minor until today and that is: Occasionally a row in a table get populated with #Deleted comments. The entire row can not be deleted. Generally this does not get displayed, however, today it is. Can you help? Are these rows that maybe your application is doing this to, or are these rows that are getting deleted in postgresql but still somehow showing up? It sounds like you've either found a very rare occuring bug (no one else has reported anything like this that I've seen) or maybe are having some kind of hardware issue where bits aren't getting set quite right. When postgresql "deletes" a tuple prior to it being vacuumed, it simply sets a time stamp on it that vacuum can use to see it is "deleted" and no longer visible. If you are actually getting back tuples with the words #Deleted in them, then I would guess you have an application error. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(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] BLOB help needed...
Hi, i wrote a php skript to test this, works beautiful, you can download the script at http://www.erdtrabant.de/index.php?i=500200104 volker Guy Fraser wrote: If you are using php, the two functions below should help. http://ca.php.net/manual/en/function.pg-escape-bytea.php http://ca.php.net/manual/en/function.pg-unescape-bytea.php Taber, Mark wrote: We’re implementing our first PostgreSQL database, and enjoying it very much. However, we have a table that will store binary image files (pie charts, etc.) for later display on a dynamic webpage. While we’re putting together our prototype application, I’ve been asked by the programmers (I’m the DBA) to “put the images in the database.” I can see how to do this using Large Objects, but then getting them out again seems problematic, and the documentation is a bit sketchy. Would BYTEA columns be better? However, it seems to me that there is no easy way using psql to load images into a BYTEA column. Any help would be greatly appreciated. Regards, Mark Taber State of California Department of Finance Infrastructure & Architecture Unit 916.323.3104 x 2945 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] locking question
I have a question about locks. I have a stats table that get updated when some other table changes. Sometimes that other table is updated a 2nd time before the first stats update is finished which causes an error. I've tried using 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE' but get 'could not serialize access due to concurrent update' If i try 'READ COMMITED' i get primary key failures. This seems like it's a pretty common thing, and I'l like to be able to do this without having to write code to check for the 'could not serialize due to concurrent update' error and re-run the query. I don't have much experience with locking, because I haven't really needed to use it. Any advice would be greatly helpful. Belew is basically the transaction I'm running -- it fails when a 2nd one starts while the 1st is still running. BEGIN WORK delete from blah_stats where id = 1 insert into blah_stats select id,count(*) from blah where id = 1 group by id COMMIT WORK Regards, Brian Hirt ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Cannot open relation pg_cast_source_target_index
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, I've just upgraded a production database from 7.3.4 to 7.4.2. I 'sometimes' get the following error while executing a PHP code: "mdfd_getrelnfd cannot open relation pg_cast_source_target_index" Quoted 'sometimes', since I really get this error rarely! This is a simple SELECT query. What are the possible causes of this? Google didn't help me... Regards, - -- Devrim GUNDUZ devrim~gunduz.org devrim.gunduz~linux.org.tr http://www.TDMSoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFAjuantl86P3SPfQ4RAnRnAJwJq3mcVopahOeFkkEY0YOje2sQ1wCdE0w4 0sWo73dZNp/H3eMMTiBpAD8= =Zm9+ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Question
"scott.marlowe" <[EMAIL PROTECTED]> writes: > When postgresql "deletes" a tuple prior to it being vacuumed, it simply > sets a time stamp on it that vacuum can use to see it is "deleted" and no > longer visible. If you are actually getting back tuples with the words > #Deleted in them, then I would guess you have an application error. The application in question is almost certainly Microsoft Access--it has its own weird idea of how to track deleted rows in an ODBC table. The '#Deleted' phenomenon with Access is fairly common and well documented. -Doug ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Question
On Fri, 23 Apr 2004, Jerry Robertson wrote: > We have been running Postgre for over a year and are very pleased with its ease of > use and performance. > > We have encountered one problem that has been minor until today and that is: > > Occasionally a row in a table get populated with #Deleted comments. The entire row > can not be deleted. Generally > this does not get displayed, however, today it is. Can you help? Are these rows that maybe your application is doing this to, or are these rows that are getting deleted in postgresql but still somehow showing up? It sounds like you've either found a very rare occuring bug (no one else has reported anything like this that I've seen) or maybe are having some kind of hardware issue where bits aren't getting set quite right. When postgresql "deletes" a tuple prior to it being vacuumed, it simply sets a time stamp on it that vacuum can use to see it is "deleted" and no longer visible. If you are actually getting back tuples with the words #Deleted in them, then I would guess you have an application error. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] WAL details
Hi I want to be preserve a log of all SQL actions that change a database, (ie INSERT, UPDATE, DELETE, CREATE), to provide a full audit trail of the date, and to allow the recreation of a database at any time. I can achieve this wth ORACLE(recover and others) and MySQL(mysqlbinlog) quite easily. How do I achieve this with Postgres? Simon -- Simon Windsor Eml: [EMAIL PROTECTED] Tel: 01454 617689 Mob: 07960 321599 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. Mailscanner thanks transtec Computers for their support. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] composite type and assignment in plpgsql
Ivan Sergio Borgonovo wrote: On Tue, 27 Apr 2004 10:12:13 -0700 thisSession := ( ''t'', md5( now( ) || rand( ) ) ); - md5 takes TEXT as an argument, not a numeric type Since it works you surely fixed my code but this should't be an issue since I tried test1=# select md5( now( ) || random( ) ); md5 -- 154e804967451148bba5f28e044be828 (1 row) and test1=# select md5( random( ) ); md5 -- 31313f537b69d5ffe61be024a40b807e (1 row) and they worked. Yeah, they worked for me too. I was just looking at the docs and saw the TEXT argument. and yeah I messed up remembering mySQL code and wrote rand( ) inspite of random( ) Can't user composite type be initialized in a shortest way? eg. ( ( ), ( ), , ( ), , , ( ), ...) I thought they could. I saw a similar syntax somewhere in the docs. Am I daydreaming? I don't know. One more thing about the first example presented in this page: http://www.postgresql.org/docs/7.4/static/plpgsql-expressions.html I just tried create or replace function GetSessionID( integer ) returns tSession as ' declare thisSession tSession; begin thisSession.ty_Found := ''t''; thisSession.ty_Session := now( ); return thisSession; end; ' language plpgsql; and it returns execution time not "plan" time. Does "plan" time is strictly referred to SQL statements? I'm not sure I understand what you're asking here. CURRENT_TIMESTAMP and now() return the start time of the current transaction, would that be the "plan" time? The timeofday() function returns the "wall clock" time and advances during transactions. I think that this would be the "execution" time. Hope that helps Ron ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] composite type and assignment in plpgsql
On Tue, 27 Apr 2004 10:12:13 -0700 Ron St-Pierre <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo wrote: > > --HERE!!! > > thisSession := ( ''t'', md5( now( ) || rand( ) ) ); > > > - md5 takes TEXT as an argument, not a numeric type Since it works you surely fixed my code but this should't be an issue since I tried test1=# select md5( now( ) || random( ) ); md5 -- 154e804967451148bba5f28e044be828 (1 row) and test1=# select md5( random( ) ); md5 -- 31313f537b69d5ffe61be024a40b807e (1 row) and they worked. and yeah I messed up remembering mySQL code and wrote rand( ) inspite of random( ) Can't user composite type be initialized in a shortest way? eg. ( ( ), ( ), , ( ), , , ( ), ...) I thought they could. I saw a similar syntax somewhere in the docs. Am I daydreaming? One more thing about the first example presented in this page: http://www.postgresql.org/docs/7.4/static/plpgsql-expressions.html I just tried create or replace function GetSessionID( integer ) returns tSession as ' declare thisSession tSession; begin thisSession.ty_Found := ''t''; thisSession.ty_Session := now( ); return thisSession; end; ' language plpgsql; and it returns execution time not "plan" time. Does "plan" time is strictly referred to SQL statements? thanks for your help ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Installation Postgresql
On Tue, 27 Apr 2004 19:08:12 +0200 Frank Finner <[EMAIL PROTECTED]> sat down, thought long and then wrote: > After having installed the SuSE shipped PostgreSQL packages, you simply have > to start the database as root with "rcpostgresql start". If the database is > not initialized, the startup script will do this for you when you start it the > first time. ... this should read "database ENGINE" or "database SERVER" both times, not just "database"... Regards, Frank ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] BLOB help needed...
If you are using php, the two functions below should help. http://ca.php.net/manual/en/function.pg-escape-bytea.php http://ca.php.net/manual/en/function.pg-unescape-bytea.php Taber, Mark wrote: We’re implementing our first PostgreSQL database, and enjoying it very much. However, we have a table that will store binary image files (pie charts, etc.) for later display on a dynamic webpage. While we’re putting together our prototype application, I’ve been asked by the programmers (I’m the DBA) to “put the images in the database.” I can see how to do this using Large Objects, but then getting them out again seems problematic, and the documentation is a bit sketchy. Would BYTEA columns be better? However, it seems to me that there is no easy way using psql to load images into a BYTEA column. Any help would be greatly appreciated. Regards, Mark Taber State of California Department of Finance Infrastructure & Architecture Unit 916.323.3104 x 2945 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] composite type and assignment in plpgsql
Ron St-Pierre wrote: Ivan Sergio Borgonovo wrote: what's wrong with this? create type tSession as ( ty_found boolean, ty_Session char(32) ); create or replace function GetSessionID( integer ) returns tSession as ' declare thisSession tSession; begin --HERE!!! thisSession := ( ''t'', md5( now( ) || rand( ) ) ); - md5 takes TEXT as an argument, not a numeric type - assign each variable of type tSession to its corresponding value: thisSession.ty_found := ''t''; thisSession.ty_session := md5(CAST((now( )) AS TEXT)); I haven't looked up the rand() function, but you can see from this how you would cast it and now() to text. return thisSession; end; ' language plpgsql; thx ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings And then you can get the results: select * from getsessionid(1); imperial=# select * from getsessionid(1); ty_found |ty_session --+-- t| cf76cca2b562a0ead48d3eb3810f51cc (1 row) hth Ron In the above reply, I forgot to mention that you are not using the integer you are passing in as an argument. If you need it (rand()?) you'll have to declare it: myInt ALIAS FOR $1; or use it explicitly with just the name: $1 Ron ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] BLOB help needed...
We’re implementing our first PostgreSQL database, and enjoying it very much. However, we have a table that will store binary image files (pie charts, etc.) for later display on a dynamic webpage. While we’re putting together our prototype application, I’ve been asked by the programmers (I’m the DBA) to “put the images in the database.” I can see how to do this using Large Objects, but then getting them out again seems problematic, and the documentation is a bit sketchy. Would BYTEA columns be better? However, it seems to me that there is no easy way using psql to load images into a BYTEA column. Any help would be greatly appreciated. Regards, Mark Taber State of California Department of Finance Infrastructure & Architecture Unit 916.323.3104 x 2945
Re: [GENERAL] Unicode encoding
William Sweet wrote: > support is enabled. Now, I'd like to only store Unicode chars in my > PostgreSQL dbs. I hear there are 3 ways to accomplish this: > > 1) during PostgreSQL configure/build (installation level) > 2) during initdb (cluster level) > 3) CREATE DATABASE (db level) Each one of these only sets the default for the one below it. > ...but there are some "not-so-happy" stories on the net. For > instance, "it's not 'true' Unicode support when implemented at the db > level", That is bogus. > or "sorting and regex do not work properly with a cluster > level implementation", That is true. etc. I've read the v7.3 Admin Guide section > 7.2 Multibyte support... sounds reasonable. So my question is, what > is the official way to enable "true" Unicode storage and retrieval, > so that LIKE, sorting, and regex in perl::DBI work properly? Sorting will not work correctly with Unicode. > I am a > tad concerned also that I don't see PostgreSQL mentioned on the > Unicode products page; http://www.unicode.org/onlinedat/products.html Well, we're also not listed on the ISO 8859 products page, but I don't think that matters. :-) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] composite type and assignment in plpgsql
Ivan Sergio Borgonovo wrote: what's wrong with this? create type tSession as ( ty_found boolean, ty_Session char(32) ); create or replace function GetSessionID( integer ) returns tSession as ' declare thisSession tSession; begin --HERE!!! thisSession := ( ''t'', md5( now( ) || rand( ) ) ); - md5 takes TEXT as an argument, not a numeric type - assign each variable of type tSession to its corresponding value: thisSession.ty_found := ''t''; thisSession.ty_session := md5(CAST((now( )) AS TEXT)); I haven't looked up the rand() function, but you can see from this how you would cast it and now() to text. return thisSession; end; ' language plpgsql; thx ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings And then you can get the results: select * from getsessionid(1); imperial=# select * from getsessionid(1); ty_found |ty_session --+-- t| cf76cca2b562a0ead48d3eb3810f51cc (1 row) hth Ron ---(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] Installation Postgresql
On Mon, 26 Apr 2004 10:22:36 -0700 Andrew Ayers <[EMAIL PROTECTED]> sat down, thought long and then wrote: > Bill Moran wrote: > > Stephen Salbod wrote: > > > >> My background is Windows and I just switched, yesterday to, Suse Linux > >> Professional 9.0. And I have a class project due Monday, which is on > >> why select postgresql as your DBMS. I am trying to install postgresql > > Let this be a lesson - never change your environment if it is "crunch > time", unless you have no other choice. I don't know your situation, but > it sounds like you picked the worst time to begin learning Linux... > > >> and going nuts. I installed the postgresql package on to my machine. I > >> am following the short version installation instructions, but I got > >> stuck at the first step: ./configure . I've looked for it on my system > >> but to no avail. I just want to get postgresql up and running. Every > >> book I turn to has this configure file. I know it is my window > >> background that is messing me up--I can't think Linux right now. I > >> would appreciate if someone can give me hand so I can have a good > >> experience installing postgresql . Hi, if you are so short in time, go sit at your compi, open yast, install the SuSE shipped rpm´s. Don´t even think about compiling and installing PostgreSQL from the sources, it is easy, if you know about SuSE (or other type) Linux, but if you come from the MS Windows side of life, even don´t know about things like Cygwin on MS Windows - don´t. Leave that for a quiet moment without pressure. It´s to much to learn about Linux and PostgreSQL at the same time within less than one week. After having installed the SuSE shipped PostgreSQL packages, you simply have to start the database as root with "rcpostgresql start". If the database is not initialized, the startup script will do this for you when you start it the first time. After this you can do everything what´s written in the PostgreSQL documentation about an _installed_ PostgreSQL system - create users, databases, tables, queries, database applications... If you want to start PostgreSQL as a "service" everytime you boot, go to/etc/init.d and say "insserv postgresql". Or do this also inside yast. You will not be on the bleeding edge of PostgreSQL databases (I think it is 7.4.0 or 7.4.1 what´s shipped with SuSE 9.0), but you will have a working database system out of the box. As soon as you know more about the structures and features of Linux and Unix systems as a whole (for example, how to install executables out of a source tarball with configure - make - make install and so on), you should go and download the PostgreSQL sources and install PostgreSQL from these, because usually the distribution shipped releases are one or two steps behind. Good luck! Frank. ---(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] shadowing (like IB/Firebird)
David Garamond wrote: > Actually, what is needed is: > > - an exact mirror at all times; > - a very simple, straightforward, and fast way to failover; > > done by software. http://www.drbd.org/ works well for us and can be set up quickly and from commodity parts. ---(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] pg_restore problem
[EMAIL PROTECTED] wrote: > pg_dumpall > backup > from 7.4 trying: pg_restore backup > results in postgres' [Archiver]s suspicion that "backup" > was not a valid archive. What you backup by pg_dumpall is to be restored using psql, not pg_restore. Read the man pages of pg_dump and pg_dumpall to confirm what is to be used to restore the various forms of backups. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] shadowing (like IB/Firebird)
On 4/26/04 3:25 PM, "Glen Parker" <[EMAIL PROTECTED]> wrote: > Sounds an aweful lot like RAID level one :-) Why would a DB system need to > do what RAID already does quite well? One case I can think of is where the shadow is on a separate system (e.g. a SAN or NetApps, another linux box, etc.). RAID doesn't protect you against certain types of hardware failure. We recently lost a RAID 5 due to a double disk failure. We've had high end boxes lose a RAID when just one disk went out (theoretically shouldn't happen) - apparently when the disk died it caused corruption elsewhere. I have also seen (a couple of times) a controller go bad and proceed to write garbage all over the disks. The mirroring worked quite well - we had a very nice file system full of mirrored garbage. Of course, none of these protect you against an errant application that did a 'delete from' instead of 'delete from where'... Wes ---(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] questions on rules
On Apr 26, 2004, at 3:12 PM, Timothy Perrigo wrote: I'm trying to set up some basic rules to log inserts, updates, and deletes to tables in an inheritance hierarchy (by inserting records into a log table), and I've got a couple of questions. (1) Is it possible to create a rule on a base table and have it operate for all derived tables? I'd like to just create 3 rules (insert/update/delete) on the base table and have them apply to all inherited tables. Can this be done? I've never tried this myself, but I feel pretty good about saying the answer is "NO". :( Most other postgres features (esp. triggers) don't inherit either. (2) I've got a very simple update rule-- create rule log_updates as on update to foo do insert into audit_log(table_oid, id, log_what) values (foo.tableoid, NEW.foo_id, 'U'); Ever just tried to do this from psql: SELECT foo.tableoid; You get a resultset with a row for every row in table foo. That's essentially what your INSERT statement is doing. It's as if you wrote: INSERT INTO audit_log(table_oid, id, what) SELECT tableoid, NEW.foo_id, 'U' FROM foo; What you want to do in your rule, I think, is something like this: INSERT INTO audit_log(table_oid, id, what) values ( (select tableoid from foo limit 1), NEW.foo_id, 'U'); There might be a different way to lookup the tableoid for table "foo", but it would likely require using 'foo' as a quoted string against a query in pg_class, so the above might make things clearer. eric ps, never knew about the "tableoid" field until just now. how interesting. I had hoped that this would create a single entry in my audit_log table for each row updated. However, it seems to fire for each record in the "foo" table, even if the update affected only one row! What am I doing wrong? Any help would be very much appreciated. Thanks! Tim Perrigo ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Problem installing postgresql 7.3.6 on Redhat 7.3
Dear group, I would like to know where I could find rpms for Redhat 7.3. I have looked at the downloads page and I see only source rpms and rpms for Redhat 9 and FC1. I did download the src rpm and when I run the rpm -ba postgresql.spec I get the following error message: configure: error: could not find function 'krb5_encrypt' required for Kerberos 5 error: Bad exit status from /var/tmp/rpm-tmp.93268 (%build) RPM build errors: Bad exit status from /var/tmp/rpm-tmp.93268 (%build) How do I get over this. Regards, Shan. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] shadowing (like IB/Firebird)
At 09:53 AM 4/27/2004 -0400, Bruce Momjian wrote: > > > Actually, what is needed is: > > > > > > - an exact mirror at all times; > > > - a very simple, straightforward, and fast way to failover; > > > > > > done by software. They can do hardware mirroring, or software/OS mirroring. Why put that in the database too? Seems like it would just complicate our code with little payback. I agree. This is best done by the various RAID solutions out there. One of the things Postgresql lacks would be the clustering stuff where you either run one database on multiple machines or a cluster of multiple databases on multiple machines that work together, in order to either get better scalability/performance and/or availability. I don't really know what would be good ways to do these - hope someone figures them out. Still, there's always the expensive "Big box" option where you put postgresql on one of those big fault-tolerant servers. Even so, the limit on how big the "Big box" can get is probably a lot lower than how big a cluster can get. Link. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] composite type and assignment in plpgsql
what's wrong with this? create type tSession as ( ty_found boolean, ty_Session char(32) ); create or replace function GetSessionID( integer ) returns tSession as ' declare thisSession tSession; begin --HERE!!! thisSession := ( ''t'', md5( now( ) || rand( ) ) ); return thisSession; end; ' language plpgsql; thx ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] shadowing (like IB/Firebird)
Robert Treat wrote: > On Mon, 2004-04-26 at 23:43, Bruce Momjian wrote: > > David Garamond wrote: > > > >>Now suppose /disk1 fails, one of the shadow can be configured to > > > >>immediately take over as the master database, without any down time. We > > > >>can then add /disk4/dbname.fdb, for instance, to become a new shadow. > > > >> > > > >>Alternatively, when a shadow fails, IB/Firebird can refuse further > > > >>transactions until there is another shadow coming up, so the database is > > > >>shadowed all the time. > > > > > > > > No, we don't have plans to do that. We will allow continuous logging so > > > > a tar backup plus this log will bring you up to current. > > > > > > What about the future synchronous replication or clustering? Will this > > > feature do what shadowing in IB/FB does? Cause I've met a couple of > > > people that really love this feature and they cling to FB because of this. > > > > > > Actually, what is needed is: > > > > > > - an exact mirror at all times; > > > - a very simple, straightforward, and fast way to failover; > > > > > > done by software. > > > > I recommend they keep clinging. :-) > > > > Should we recommend they submit a patch instead? :-) > > Seriously though this sounds like it could be an extension of the > tablespaces implementation couldn't it? They can do hardware mirroring, or software/OS mirroring. Why put that in the database too? Seems like it would just complicate our code with little payback. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] questions on rules
It seems that triggers are not inherited, so to get the functionality I want I'll have to create a trigger for each table. If anyone knows another way, please let me know! After you pointed me in the right direction, I was able to create a trigger procedure which can be called from triggers on various tables and will log the operation (including the affected table's oid and name). The procedure is listed below. Thanks for the help! Tim create or replace function add_log_entry() returns TRIGGER as ' BEGIN insert into audit_log(table_oid, table_name, id, operation) values (TG_RELID, TG_RELNAME, NEW.id, TG_OP); return NEW; END; ' language 'plpgsql'; On Apr 27, 2004, at 8:18 AM, Richard Huxton wrote: On Tuesday 27 April 2004 13:40, Timothy Perrigo wrote: Thanks for the reply. Do you know if triggers defined on a base table fire for operations on inherited tables? (I.e., if I have an after insert trigger on table "base", and a table "derived" that inherits from base, will inserts into derived cause the trigger on base to fire?) Hmm - don't know this I'm afraid. If so (this is the behavior I would like), is there a way to get the tableoid of the table which caused the trigger to fire? Here I can help. Check the plpgsql section of the manuals, and there you'll find a list of special variables available to trigger functions. These include table and trigger name. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Temp table problem.
SELECT oid,relname FROM pg_class WHERE oid>=1; gives me this output. oid| relname . . . - 125538806 | tmp_table1 . . . 125538808 | pg_toast_125538806 125538810 | pg_toast_125538806_index . . . 125538811 | pg_toast_125538804 125538813 | pg_toast_125538804_index . . . 149064745 | pg_toast_149064743 149064747 | pg_toast_149064743_index . . . 165058651 | pg_toast_165058647 165058653 | pg_toast_165058647_index . . . So we can see that the problem i had with temp table creating and cache maybe lay in this output. As we can see tmp_table1 have oid. But we don't have any name, which reference to tables with oid's 149064743 and 165058647, which cause errors i have in my serverlog file. So the next question is what to do to make database look correct. Suppose I can do someting like that: DELETE FROM pg_class WHERE oid = 149064745; DELETE FROM pg_class WHERE oid = 149064747; DELETE FROM pg_class WHERE oid = 165058651; DELETE FROM pg_class WHERE oid = 165058653; DELETE FROM pg_index WHERE indexrelid = 149064747; DELETE FROM pg_index WHERE indexrelid = 165058653; would it be correct or any toher suggestions ? Juris Krumins SIA KOMIN Sistemas Administrator e-pasts:[EMAIL PROTECTED] mob tel: 9719772 tel:7505574 fax:7282590 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] shadowing (like IB/Firebird)
On Mon, 2004-04-26 at 23:43, Bruce Momjian wrote:
> David Garamond wrote:
> > >>Now suppose /disk1 fails, one of the shadow can be configured to
> > >>immediately take over as the master database, without any down time. We
> > >>can then add /disk4/dbname.fdb, for instance, to become a new shadow.
> > >>
> > >>Alternatively, when a shadow fails, IB/Firebird can refuse further
> > >>transactions until there is another shadow coming up, so the database is
> > >>shadowed all the time.
> > >
> > > No, we don't have plans to do that. We will allow continuous logging so
> > > a tar backup plus this log will bring you up to current.
> >
> > What about the future synchronous replication or clustering? Will this
> > feature do what shadowing in IB/FB does? Cause I've met a couple of
> > people that really love this feature and they cling to FB because of this.
> >
> > Actually, what is needed is:
> >
> > - an exact mirror at all times;
> > - a very simple, straightforward, and fast way to failover;
> >
> > done by software.
>
> I recommend they keep clinging. :-)
>
Should we recommend they submit a patch instead? :-)
Seriously though this sounds like it could be an extension of the
tablespaces implementation couldn't it?
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
