Re: [GENERAL] Trigger function that works with both updates and deletes?
Perfect, thanks Michael Fuhr wrote: > > On Mon, Jun 18, 2007 at 06:07:37PM -0700, novnov wrote: >> First, when a record is being deleted, OLD refers to the rec just deleted >> (or about to be deleted)? > > Correct. > >> Second, while I could write two trigger functions, one dealing with >> add/update, the other with deletes, it's probably neater to have a single >> trigger function and have it discriminate "am I being called for a >> delete, >> or an add/update?" I don't know how to determine the type record change. > > In PL/pgSQL you can use TG_OP. See "Trigger Procedures" in the > documentation: > > http://www.postgresql.org/docs/8.2/interactive/plpgsql-trigger.html > > -- > Michael Fuhr > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match > > -- View this message in context: http://www.nabble.com/Trigger-function-that-works-with-both-updates-and-deletes--tf3943732.html#a11189206 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Apparent Wraparound?
On Jun 18, 10:44 pm, [EMAIL PROTECTED] (Alvaro Herrera) wrote: > > Please check "MultiXact" id consumption. Do you mean that your server > has crashed? > How do I check MilitXact id consumption ? Is it "Latest checkpoint's NextXID:" in the output of pg_controldata ? transaction id consumption is ~ 35 per day (extrapolated from 16 hours). No, the Server has been up for about 2 month (regular reboot due to kernel and postgresql upgrade to 8.1.8), was running before for 7 month flawless, and before that i migrated from 8.0 to 8.1.4 with a complete dump/restore). pg_multixact/members shows: -rw--- 1 postgres postgres 8192 Aug 30 2006 pg_multixact/offsets shows: -rw--- 1 postgres postgres 8192 Apr 13 15:03 pg_controldata shows: pg_control version number:812 Catalog version number: 200510211 Database system identifier: 4969068620492422615 Database cluster state: in production pg_control last modified: Tue Jun 19 08:00:36 2007 Current log file ID: 10 Next log file segment:102 Latest checkpoint location: A/65362150 Prior checkpoint location:A/652E091C Latest checkpoint's REDO location:A/65362150 Latest checkpoint's UNDO location:0/0 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 99983151 Latest checkpoint's NextOID: 7758309 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Time of latest checkpoint:Tue Jun 19 08:00:36 2007 Maximum data alignment: 4 Database block size: 8192 Blocks per segment of large relation: 131072 Bytes per WAL segment:16777216 Maximum length of identifiers:64 Maximum columns in an index: 32 Date/time type storage: floating-point numbers Maximum length of locale name:128 LC_COLLATE: C LC_CTYPE: C regards Gerhard ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Regression - Query requires full scan, GIN doesn't support it
Hi. I recently converted a load of GIST indexes used by tsearch2 to GIN, as my app is read heavy. We now occasionally get the exception: Query requires full scan, GIN doesn't support it Thankfully it is happening very rarely, but as the querys are generated from user input we can't stop this entirely. Is this a permanent limitation of GIN, or is a fix possible? Is a fix being worked on? If a fix is forthcoming, will it be available in the 8.2 series or only 8.3+? (It certainly looks like a bug to me, as the relevant queries work if the index is dropped, and queries working or failing depending on the existence of an index seems rather wrong to me.) Only relevant discussion on this I can find is http://archives.postgresql.org/pgsql-hackers/2007-01/msg01581.php. There appear to be no replies visible though :-( -- Stuart Bishop <[EMAIL PROTECTED]> http://www.stuartbishop.net/ signature.asc Description: OpenPGP digital signature
Re: [GENERAL] help with libpq program
DAnn My c code is one layer for wrap libpq.dll functions i'm using function like start with PGresult *PQexec(PGconn *conn, const char *command); command like 'copy foo from stdin '; int PQputCopyData(PGconn *conn, const char *buffer, int nbytes); (many times) int PQputCopyEnd(PGconn *conn, const char *errormsg); I was sucessfull with append CSV delimiter if not specified is tab character (from documentation) The problem was numeric formats, i can't find solution for this but with CSV append was succesfull. Thank for responses Best regards MDC --- Dann Corbit <[EMAIL PROTECTED]> escribió: > Where is your actual copy statement? > What is your field delimiter? > > Why not post the actual C code for your program, if > it is not too long? > > I guess from what you have posted that the delimiter > you supplied does not match the delimiter from your > copy statement. > > > -Original Message- > > From: [EMAIL PROTECTED] > [mailto:pgsql-general- > > [EMAIL PROTECTED] On Behalf Of marcelo Cortez > > Sent: Saturday, June 16, 2007 9:04 PM > > To: pgsql-general@postgresql.org > > Subject: [GENERAL] help with libpq program > > > > > > folks > > > > i need help with libpq program ,i made on C > program > > for > > wrapper libpq.dll program , the routine failing is > > copy from stdin interface. > > > > PQputCopyData return 1 (AKA ok) > > PQputCopyEnd return 1 (AKA ok) > > but nothing is append to database. > > tailing log file > > > > invalid input syntax for integer: "3hello > world > > 4.5 > > " > > CONTEXT: COPY foo, line 1, column a: "3 > hello > > world 4.5 > > " > > STATEMENT: copy foo from stdin > > > > data seems to be correct > > "3\ hello world \ 4.5\n" > > "\\.\n" > > > > database ( is for one example found at google) > > create table foo (a int4, b char(16), d float8); > > copy foo from stdin; > > > > "3\ hello world \ 4.5\n" > > "\\.\n" > > > > I'm wrong? > > > > what is way to diagnose? > > any sugestion are welcomed > > best regards > > > > MDC > > > > PD: any example are welcomed too. > > > > > > > > > > > > > > > > __ > > Preguntá. Respondé. Descubrí. > > Todo lo que querías saber, y lo que ni imaginabas, > > está en Yahoo! Respuestas (Beta). > > ¡Probalo ya! > > http://www.yahoo.com.ar/respuestas > > > > > > ---(end of > broadcast)--- > > TIP 6: explain analyze is your friend > > ---(end of > broadcast)--- > TIP 1: 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 > __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] VACUUM ANALYZE extremely slow
On Jun 17, 2007, at 2:15 PM, Sergei Shelukhin wrote: This is my first (and, by the love of the God, last) project w/pgsql and everything but the simplest selects is so slow I want to cry. This is especially bad with vacuum analyze - it takes several hours for a database of mere 15 Gb on a fast double-core server w/2Gb of RAM and virtually no workload at the moment. Maintenance work mem is set to 512 Mb. I have a 23 GB PG 8.1 db running on a 3 year old dual processor Dell running Red Hat Linux, 2GB RAM. My tests ran on our production database while it was running (albeit not under a heavy load) with no obvious slowdown for users using our application. PG was using 1.2% of the CPU, and noted the server had been running for 322 days. I'm not sure if there's a way to make vacuum use more processor time to speed it up or not. "ANALYZE" took 1 minute 14 seconds. "VACUUM" took significantly longer at 24 minutes 22 seconds. I noted that we hadn't set maintenance_work_mem so it presumably is using the default of 16384 (16MB). I'm sure much depends on how many tables, how many deleted rows, etc. are in your db, but "several hours" certainly is nothing like what we see. David ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Server and Client configuration.
I am new to Postgresql Database. My setup is backend is postgresql database, frontend is Java(JDBC). I installed the postgres in windows platform. Now I want to setup server and client configuration. Kindly guide me how to set the configuration parameters, in server and client machines. Waiting for your fav reply. Thanks & Regards Jayakumar M DISCLAIMER: This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Core reported from vaccum function.
Mavinakuli, Prasanna (STSD) wrote: > Hello All, > > We are getting the following core more oftenly.But We don't have a test > case where it is guaranteed to dump this core.We are using 7.4.2 version > postgres and if any one of you aware about some bug fixes happened > around this problem.Please let us know. There are about 15 rounds of bug fixes released after that version. Get 7.4.17 and try again. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] VACUUM ANALYZE extremely slow
Sergei Shelukhin escribió: > The same database running on mysql on basically the same server used > to run optimize table on every table every half an hour without any > problem, I am actually pondering scraping half the work on the > conversion and stuff and going back to mysql but I wonder if there's > some way to improve it. Why waste time on it? Just move it to mysql since it's so good, and be done with it. -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1", W 73º 13' 56.4" "Someone said that it is at least an order of magnitude more work to do production software than a prototype. I think he is wrong by at least an order of magnitude." (Brian Kernighan) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] VACUUM ANALYZE extremely slow
On Jun 17, 2007, at 2:15 PM, Sergei Shelukhin wrote: This is my first (and, by the love of the God, last) project w/pgsql and everything but the simplest selects is so slow I want to cry. This is especially bad with vacuum analyze - it takes several hours for a database of mere 15 Gb on a fast double-core server w/2Gb of RAM and virtually no workload at the moment. Maintenance work mem is set to 512 Mb. Is there any way to speed up ANALYZE? Without it all the queries run so slow that I want to cry after a couple of hours of operation and with it system has to go down for hours per day and that is unacceptable. There's no need to stop the database to run vacuum or analyze (heck, with autovacuum in modern versions of postgresql you can get away without running them at all, sometimes). I suspect you're doing the wrong thing (perhaps running vacuum full, rather than plain vacuum). Can you tell us what version of postgresql you're running and how you're vacuuming it (what commands you're running)? Cheers, Steve ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Trigger function that works with both updates and deletes?
> Second, while I could write two trigger functions, one dealing with > add/update, the other with deletes, it's probably neater to > have a single > trigger function and have it discriminate "am I being called > for a delete, > or an add/update?" I don't know how to determine the type > record change. I use trigger arguments and determine what to do by: if TG_ARGV[0] = 'value' then end if; Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pg_restore out of memory
Francisco Reyes <[EMAIL PROTECTED]> writes: > Also the error is about running out of memory when trying to allocate 84MB. > The default FreeBSD limit is 512MB so 84MB is well below that. Well, no, it's not. I traced through a test case involving loading a multi-megabyte text value, and what I find is that there are actually five concurrently allocated multi-megabyte areas: * copy's line buffer * copy's field buffer * textin's result value * heap_form_tuple result * output workspace for toast_compress_datum What's more, because the line and field buffers are StringInfos that are intended for reuse across multiple lines/fields, they're not simply made equal to the exact size of the big field. They're rounded up to the next power-of-2, ie, if you've read an 84MB field during the current COPY IN then they'll be 128MB apiece. In short, COPY is going to need 508MB of process-local RAM to handle this row. That's on top of the few megabytes of random housekeeping info that a backend keeps around. And it's entirely likely that your 450MB of shared buffers (plus whatever else is in your shared memory area) gets counted against each process' ulimit, too. In short, you need a bigger per-process memory allowance. BTW: I think if you were using different client and server encodings there would be yet a sixth large buffer involved, for the output of pg_client_to_server. Basically Postgres is designed on the assumption that you have room for multiple copies of the widest field you want to process. I have not bothered to see how many copies of the field would be involved in a "SELECT * FROM ..." operation, but I can assure you it'd be several. If you can't afford a factor of 5 or 10 headroom on your widest fields, you should look at storing them as large objects so you can store and fetch them a chunk at a time. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] help with libpq program
Where is your actual copy statement? What is your field delimiter? Why not post the actual C code for your program, if it is not too long? I guess from what you have posted that the delimiter you supplied does not match the delimiter from your copy statement. > -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-general- > [EMAIL PROTECTED] On Behalf Of marcelo Cortez > Sent: Saturday, June 16, 2007 9:04 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] help with libpq program > > > folks > > i need help with libpq program ,i made on C program > for > wrapper libpq.dll program , the routine failing is > copy from stdin interface. > > PQputCopyData return 1 (AKA ok) > PQputCopyEnd return 1 (AKA ok) > but nothing is append to database. > tailing log file > > invalid input syntax for integer: "3hello world > 4.5 > " > CONTEXT: COPY foo, line 1, column a: "3hello > world 4.5 > " > STATEMENT: copy foo from stdin > > data seems to be correct > "3\ hello world \ 4.5\n" > "\\.\n" > > database ( is for one example found at google) > create table foo (a int4, b char(16), d float8); > copy foo from stdin; > > "3\ hello world \ 4.5\n" > "\\.\n" > > I'm wrong? > > what is way to diagnose? > any sugestion are welcomed > best regards > > MDC > > PD: any example are welcomed too. > > > > > > > > __ > Preguntá. Respondé. Descubrí. > Todo lo que querías saber, y lo que ni imaginabas, > está en Yahoo! Respuestas (Beta). > ¡Probalo ya! > http://www.yahoo.com.ar/respuestas > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 1: 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] Trigger function that works with both updates and deletes?
On Mon, Jun 18, 2007 at 06:07:37PM -0700, novnov wrote: > First, when a record is being deleted, OLD refers to the rec just deleted > (or about to be deleted)? Correct. > Second, while I could write two trigger functions, one dealing with > add/update, the other with deletes, it's probably neater to have a single > trigger function and have it discriminate "am I being called for a delete, > or an add/update?" I don't know how to determine the type record change. In PL/pgSQL you can use TG_OP. See "Trigger Procedures" in the documentation: http://www.postgresql.org/docs/8.2/interactive/plpgsql-trigger.html -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] VACUUM ANALYZE extremely slow
This is my first (and, by the love of the God, last) project w/pgsql and everything but the simplest selects is so slow I want to cry. This is especially bad with vacuum analyze - it takes several hours for a database of mere 15 Gb on a fast double-core server w/2Gb of RAM and virtually no workload at the moment. Maintenance work mem is set to 512 Mb. Is there any way to speed up ANALYZE? Without it all the queries run so slow that I want to cry after a couple of hours of operation and with it system has to go down for hours per day and that is unacceptable. The same database running on mysql on basically the same server used to run optimize table on every table every half an hour without any problem, I am actually pondering scraping half the work on the conversion and stuff and going back to mysql but I wonder if there's some way to improve it. ---(end of broadcast)--- TIP 1: 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] Dynamic Log tigger (plpgsql)
Hi I want to implement a trigger-function witch can fill the following table. Each data manipulation (INSERT, UPDATE or DELETE) gets logged. The function should work as trigger on diffrent tables. CREATE TABLE logtable ( operationCHAR(6) CHECK (change_type IN ('DELETE', 'INSERT', 'UPDATE')), tablenameVARCHAR, rowidINTEGER, - touched_columns VARCHAR[] ); My Problem is in the last Column (touched_columns). If it was an UPDATE Operation, I just need to know witch columns changed. (I am not iterrestet in the old or new value) => IF OLD.columnName != NEW.columnName, it has changed. My Question: How can I do "OLD.columnName != NEW.columnName" if I don't know what the columnNames are at Compile Time? I have the columnName in a variable. Thx for help. Noah ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] fsync error when restoring from archived xlogs
Hi All, Trying to do a PITR on postgres 8.1.8; I've restored the last full backup of the cluster dir and put the required WAL files into place. Yet when I attempt the recovery I see the following: Jun 18 15:44:11 postgres[29730]: [3-1] LOG: starting archive recovery Jun 18 15:44:11 postgres[29730]: [4-1] LOG: restore_command = "cp / var/lib/pgsql/backups/oldwal/%f %p" Jun 18 15:44:11 postgres[29730]: [5-1] LOG: recovery_target_time = 2007-06-16 22:00:00+09:30 Jun 18 15:44:11 postgres[29730]: [6-1] LOG: restored log file "00010002.00DD29D8.backup" from archive Jun 18 15:44:11 postgres[29730]: [7-1] LOG: restored log file "00010002" from archive Jun 18 15:44:11 postgres[29730]: [8-1] LOG: checkpoint record is at 0/2DD29D8 Jun 18 15:44:11 postgres[29730]: [9-1] LOG: redo record is at 0/2DD29D8; undo record is at 0/0; shutdown FALSE Jun 18 15:44:11 postgres[29730]: [10-1] LOG: next transaction ID: 4236; next OID: 32591 Jun 18 15:44:11 postgres[29730]: [11-1] LOG: next MultiXactId: 1; next MultiXactOffset: 0 Jun 18 15:44:11 postgres[29730]: [12-1] LOG: automatic recovery in progress Jun 18 15:44:11 postgres[29730]: [13-1] LOG: redo starts at 0/2DD2A1C Jun 18 15:44:13 postgres[29730]: [14-1] LOG: restored log file "00010003" from archive ... Jun 18 15:44:39 postgres[29730]: [49-1] LOG: restored log file "00010026" from archive Jun 18 15:44:39 postgres[29730]: [50-1] LOG: recovery stopping before commit of transaction 1809419, time 2007-06-16 22:24:47 CST Jun 18 15:44:39 postgres[29730]: [51-1] LOG: redo done at 0/264A55A0 Jun 18 15:44:39 postgres[29730]: [52-1] LOG: selected new timeline ID: 2 Jun 18 15:44:39 postgres[29730]: [53-1] LOG: archive recovery complete Jun 18 15:44:39 postgres[29730]: [54-1] LOG: could not fsync segment 0 of relation 1663/42607/44092: No such file or directory Jun 18 15:44:39 postgres[29730]: [55-1] PANIC: storage sync failed on magnetic disk: No such file or directory Jun 18 15:44:39 postgres[29726]: [2-1] LOG: startup process (PID 29730) was terminated by signal 6 Jun 18 15:44:39 postgres[29726]: [3-1] LOG: aborting startup due to startup process failure Any ideas? I'm not a subscriber so please cc: me on any responses. Regards, Tom -- Tom Lanyon Systems Administrator NetSpot Pty Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] persistent db connections in PHP
This seems to be a problem with PHP, or at least my set up. I'm writing pages in basically the same way. Each page has an include at the top that gets you a database session. The function, either pg_connect() or mysql_connect(), is supposed to either create a new connection, or return your existing one. So after I have a connection, I can navigate to other pages, reload or post to the current one, trigger the x_connect(), and get the session I created earlier. In my Mysql site, if I create temporary tables, I still have access to them after I have traversed a mysql_connect. So it looks like PHP is giving me the connection I had when I created the temp tables. However, with this new Postgres site, I don't have access to my temp tables after I've traversed another pg_connect. So PHP is either creating a new connection, or giving me another session, not the one which I created my tables in. Steve ---(end of broadcast)--- TIP 1: 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] help with libpq program
folks i need help with libpq program ,i made on C program for wrapper libpq.dll program , the routine failing is copy from stdin interface. PQputCopyData return 1 (AKA ok) PQputCopyEnd return 1 (AKA ok) but nothing is append to database. tailing log file invalid input syntax for integer: "3hello world 4.5 " CONTEXT: COPY foo, line 1, column a: "3hello world 4.5 " STATEMENT: copy foo from stdin data seems to be correct "3\ hello world \ 4.5\n" "\\.\n" database ( is for one example found at google) create table foo (a int4, b char(16), d float8); copy foo from stdin; "3\ hello world \ 4.5\n" "\\.\n" I'm wrong? what is way to diagnose? any sugestion are welcomed best regards MDC PD: any example are welcomed too. __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Setting variable
Hello, I need to know why can't I do it? CREATE OR REPLACE FUNCTION lost_hours_temp(date) RETURNS text AS $BODY$ DECLARE START_DATE date; END_DATE date; QUERY text; BEGIN START_DATE := $1; END_DATE := START_DATE - interval '3 year'; The last line (END_DATE := START_DATE - interval '3 year';) generate the following error: ERROR: operator is not unique: "unknown" / "unknown" SQL state: 42725 Hint: Could not choose a best candidate operator. You may need to add explicit type casts. Context: PL/pgSQL function "lost_hours_temp" line 10 at assignment How can I solve it? Thanks ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] how to speed up query
CREATE TEMP TABLE mydel AS SELECT r.dokumnr FROM rid r LEFT JOIN dok d USING (dokumnr) WHERE d.dokumnr IS NULL; DELETE FROM rid USING mydel WHERE rid.dokumnr =mydel.dokumnr; drop table mydel; As I mentioned when I proposed it, the temp table may not even be necessary. The important part is the LEFT JOIN instead of the NOT IN (as Martijn has explained). You could try the direct approach ... DELETE FROM rid USING ( SELECT r.dokumnr FROM rid r LEFT JOIN dok d USING (dokumnr) WHERE d.dokumnr IS NULL) x WHERE rid.dokumnr = x.dokumnr; ... and see which runs faster. Probably it does not make much of a difference. Thank you. I changed my DELETE commands to use internal table. This works fast. I tried to change my update commands also to use internal table. However, this causes update command to run 310 minutes: update bilkaib SET cr4objekt=NULL FROM ( SELECT r.cr4objekt as key FROM bilkaib r LEFT JOIN yksus4 d ON d.YKSUS =r.cr4objekt WHERE d.YKSUS IS NULL) mydel WHERE cr4objekt IS NOT NULL AND bilkaib.cr4objekt= mydel.key; No idea why this does not work fast like in DELETE command. cr4objekt type is CHAR(10) maybe this makes internal table slow. So I changed my script to DROP TABLE if exists mydel; CREATE TEMP TABLE mydel AS SELECT r.<> as key FROM <> r LEFT JOIN <> d ON d.<>=r.<> WHERE d.<> IS NULL; update <> SET <>=NULL FROM mydel WHERE <> IS NOT NULL AND <>.<>= mydel.key; Hope this will run fast (will test tomorrow). My original skript UPDATE <> SET <>=NULL WHERE <> IS NOT NULL AND <> NOT IN (SELECT <> FROM <>); runs 27 minutes in some cases. If the temp table works for you, you might be interested in a new feature of 8.2: CREATE TEMP TABLE AS ...ON COMMIT DROP; http://www.postgresql.org/docs/current/static/sql-createtableas.html Per Tom remart , I removed transactions. Now every statement runs in separate transaction. In this case ON COMMIT DROP is useless. ON COMMIT DROP exists in 8.1 also. 8.2 adds DROP IF EXISTS. explain analyze SELECT r.dokumnr FROM rid r LEFT JOIN dok d USING (dokumnr) WHERE d.dokumnr IS NULL returns "Hash Left Join (cost=7760.27..31738.02 rows=1 width=4) (actual time=2520.904..2520.904 rows=0 loops=1)" " Hash Cond: (r.dokumnr = d.dokumnr)" " Filter: (d.dokumnr IS NULL)" " -> Seq Scan on rid r (cost=0.00..17424.24 rows=202424 width=4) (actual time=0.032..352.225 rows=202421 loops=1)" " -> Hash (cost=6785.01..6785.01 rows=56101 width=4) (actual time=211.150..211.150 rows=56079 loops=1)" "-> Seq Scan on dok d (cost=0.00..6785.01 rows=56101 width=4) (actual time=0.021..147.805 rows=56079 loops=1)" "Total runtime: 2521.091 ms" If the indices are present (and visible) at the time of execution, as you described it, we should be seeing index scans on dok_dokumnr_idx and rid_dokumnr_idx instead of sequential scans. That's what I get on a similar query in one of my databases: EXPLAIN ANALYZE SELECT a.adr_id FROM cp.adr a LEFT JOIN cp.kontakt k USING (adr_id) WHERE k.adr_id IS NULL; Merge Left Join (cost=0.00..1356.31 rows=10261 width=4) (actual time=0.096..56.759 rows=3868 loops=1) Merge Cond: ("outer".adr_id = "inner".adr_id) Filter: ("inner".adr_id IS NULL) -> Index Scan using adr_pkey on adr a (cost=0.00..947.54 rows=10261 width=4) (actual time=0.012..23.118 rows=10261 loops=1) -> Index Scan using kontakt_adr_id_idx on kontakt k (cost=0.00..295.47 rows=7011 width=4) (actual time=0.007..13.299 rows=7011 loops=1) Total runtime: 58.510 ms I have no idea why my query plan shows hash and your plan show merge. My primary key (dokumnr is of type integer). Maybe this selects hash plan. For my big database I got the following plan: explain analyze SELECT r.dokumnr FROM rid r LEFT JOIN dok d USING (dokumnr) WHERE d.dokumnr IS NULL 1 Hash Left Join (cost=7759.44..31738.44 rows=1 width=4) (actual time=112.572..761.121 rows=3 loops=1) 2Hash Cond: (r.dokumnr = d.dokumnr) 3Filter: (d.dokumnr IS NULL) 4-> Seq Scan on rid r (cost=0.00..17424.64 rows=202464 width=4) (actual time=0.007..175.538 rows=202424 loops=1) 5-> Hash (cost=6784.64..6784.64 rows=56064 width=4) (actual time=111.296..111.296 rows=56079 loops=1) 6 -> Seq Scan on dok d (cost=0.00..6784.64 rows=56064 width=4) (actual time=0.005..58.686 rows=56079 loops=1) 7 Total runtime: 761.311 ms Since there are a lot of rows (202424 swown), this select command must use indexes. Without indexes it is not possible toobtain speed of 0.7 seconds. Andrus. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Core reported from vaccum function.
Hello All, We are getting the following core more oftenly.But We don't have a test case where it is guaranteed to dump this core.We are using 7.4.2 version postgres and if any one of you aware about some bug fixes happened around this problem.Please let us know. Thanks, Prasanna. Core was generated by `postmaster'. Program terminated with signal 10, Bus error. BUS_ADRALN - Invalid address alignment #0 0x449c210:0 in HeapTupleSatisfiesNow+0xb0 () (gdb) bt #0 0x449c210:0 in HeapTupleSatisfiesNow+0xb0 () #1 0x40ec3f0:0 in heap_fetch+0x6f0 () #2 0x41c1940:0 in analyze_rel+0x1540 () #3 0x42351d0:0 in vacuum+0x370 () #4 0x436adb0:0 in ProcessUtility+0xb00 () #5 0x4367b50:0 in PortalRunUtility+0x1c0 () #6 0x4368600:0 in PortalRun+0x950 () #7 0x435eab0:0 in exec_simple_query+0x530 () #8 0x4364550:0 in PostgresMain+0x45a0 () #9 0x4301c50:0 in ServerLoop+0x15e0 () #10 0x4306050:0 in PostmasterMain+0x2050 () #11 0x42858c0:0 in main+0x470 () ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Trigger function that works with both updates and deletes?
Most of the trigger fuctions I've written work on new and updated records referencing NEW. etc. I will need some of the trigger functions to work with record deletions too. First, when a record is being deleted, OLD refers to the rec just deleted (or about to be deleted)? Second, while I could write two trigger functions, one dealing with add/update, the other with deletes, it's probably neater to have a single trigger function and have it discriminate "am I being called for a delete, or an add/update?" I don't know how to determine the type record change. -- View this message in context: http://www.nabble.com/Trigger-function-that-works-with-both-updates-and-deletes--tf3943732.html#a11186941 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] statistics monitoring performance improvment -- 8.1 as well as 8.2 ?
On Jun 18, 2007, at 16:23 , [EMAIL PROTECTED] wrote: Did those improvements ever get applied to postgres 8.1.y ? perhaps in 8.1.8 or 8.1.9 ? Check the release notes for those versions, or possibly CVS. Most likely not, as usually only bug fixes are back patched. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] statistics monitoring performance improvment -- 8.1 as well as 8.2 ?
Last year, there was a problem involving stats_command_string in early 8.1.x http://archives.postgresql.org/pgsql-bugs/2006-01/msg00151.php I see mentions of performance improvements in the statistics collector for 8.2.x http://www.postgresql.org/docs/8.2/static/release-8-2.html "Improve performance of statistics monitoring, especially stats_command_string (Tom, Bruce) This release enables stats_command_string by default, now that its overhead is minimal." Did those improvements ever get applied to postgres 8.1.y ? perhaps in 8.1.8 or 8.1.9 ? thank you, - Scott Bjerke DBA, intercasting corporation ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] time without time zone
On Monday 18 June 2007 21:15, Tom Lane wrote: > Garry Saddington <[EMAIL PROTECTED]> writes: > > Can anyone explain why time has todays date and time zone? > > Works for me: > > regression=# insert into periods values(1,1,'now','now'); > INSERT 0 1 > regression=# select * from periods; > periodid | periodnumber | periodstart | periodend > --+--++ > 1 |1 | 16:13:14.35962 | 16:13:14.35962 > (1 row) > > I speculate that you are trying to display the table in some client > software that doesn't know the time datatype and is forcibly converting > it to something it does know. > > regards, tom lane Yes, you are correct I am in Zope using ZpsycopgDA. Just tried on the command line and the behaviour is correct. Time to ask elsewhere, thanks. regards garry ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] unexpected shutdown
> [EMAIL PROTECTED] writes: >> My database has shutdown several times in the last couple days. I have >> no >> idea why. I am running centos and I have not rebooted the server or >> made >> any configuration changes. > > So in particular, you didn't disable memory overcommit? > >> LOG: server process (PID 501) was terminated by signal 9 > > If you didn't issue a manual kill -9, then this is almost certainly a > trace of the kernel OOM killer at work. Google for "OOM kill" to learn > more, or see "memory overcommit" in the PG docs. > > Memory overcommit is evil on a server. > > regards, tom lane > You guys were right :Jun 17 11:04:57 kernel: Out of Memory: Killed process 24928 (postmaster). I did not disable memory overcommit. I guess this is something I will have to do. I have actually never seen this before or heard of memory overcommit. I am surprised a setting like this comes enabled by default. I read a bit about it and it seems to make sense to disable it, but from practical experience do you know of any negative side effects? ---(end of broadcast)--- TIP 1: 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] Apparent Wraparound?
[EMAIL PROTECTED] wrote: > Aha, google thinks it's wise to make the last postings (probably if > more than n ?) show only the poster name and make the name clickable. > Not very userfriendly :-( but now i know it ;-) I don't very much understand what you mean. I do see that you said "I noticed the same message" that Gunther Meyer was reporting but you weren't very explicit. I feared that the directory mentioned was different. > Sorry if that wasn't clear. I'm getting the same log entry as the > original > poster, i.e.: LOG: could not truncate directory "pg_subtrans": > apparent > wraparound. Ok. > I'm just running an analysis of the "daily transaction id consumption" > on my databases to see, if the uptime of the server matches with the > time the wraparound was logged. Please check "MultiXact" id consumption. Do you mean that your server has crashed? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Pg_standby and shutting down the warm standby
On Mon, 2007-06-18 at 13:26 -0400, Woody Woodring wrote: > I am wondering if there is a proper procedure for shutting down the > warm_standby server (8.2.4)? I am using pg_standby as my restore script in > my testing: > > [EMAIL PROTECTED] cat recovery.conf > restore_command = 'pg_standby -m -d -s 5 -w 0 -t /tmp/pgsql.trigger.5432 > /usr/local2/pg_archive %f %p 2>> standby.log' > > My issue is it looks like the when the warm_standby comes back up, it is > looking for a file that has already been loaded (and deleted). It was > looking for log '*30' when it was shut down, but upon startup again it is > looking for '*2F'. The -m command is not supported in the most recent version of pg_standby because it results in the error your point out. The latest version has a -k option that works around this error and a server patch is in the queue for 8.3 that will allow a more flexible approach to this. I'll add a --version option to pg_standby to allow us to discuss which version is in use, to avoid such issues in future. Thanks, -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Apparent Wraparound?
Aha, google thinks it's wise to make the last postings (probably if more than n ?) show only the poster name and make the name clickable. Not very userfriendly :-( but now i know it ;-) Sorry if that wasn't clear. I'm getting the same log entry as the original poster, i.e.: LOG: could not truncate directory "pg_subtrans": apparent wraparound. I'm just running an analysis of the "daily transaction id consumption" on my databases to see, if the uptime of the server matches with the time the wraparound was logged. Gerhard ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] postgresql and solaris 10: pitch to sysadmins
guys need to pitch postgresql to some hard-to-budge solaris sysadmins- they don't even know about the postgresql-solaris 10 package, just used to oracle and don't want to break their backs over postgresql. plus i don't know enough slony yet. can someone point me to some standard backup/restore etc sysadmin stuff/scripts/processes? also what's best left to the sysadmins that i shouldn't take responsibility for? any trainings/books for sysadmins/myself? thanks, jzs ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] unexpected shutdown
On 6/18/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: My database has shutdown several times in the last couple days. I have no idea why. [...] LOG: server process (PID 501) was terminated by signal 9 If this is Linux, check the kernel log (typically /var/log/kern.log, or run dmesg) and look for lines like these, which indicate that the kernel forcibly killed the process: May 22 12:43:24 sultan kernel: [232933.420709] Out of Memory: Killed process 5345 (postgres). Alexander. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_restore out of memory
Francisco Reyes <[EMAIL PROTECTED]> writes: > The issue I am trying to figure is which limit.. the OS limit is set to > 1.6GB. I am now trying to increase my shared_buffers. So far have them at > 450MB and it is still failing. For this problem, increasing shared_buffers is either useless or downright counterproductive. It cannot increase the amount of temporary workspace a particular backend can grab, and it might decrease it. regards, tom lane ---(end of broadcast)--- TIP 1: 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] unexpected shutdown
[EMAIL PROTECTED] writes: > My database has shutdown several times in the last couple days. I have no > idea why. I am running centos and I have not rebooted the server or made > any configuration changes. So in particular, you didn't disable memory overcommit? > LOG: server process (PID 501) was terminated by signal 9 If you didn't issue a manual kill -9, then this is almost certainly a trace of the kernel OOM killer at work. Google for "OOM kill" to learn more, or see "memory overcommit" in the PG docs. Memory overcommit is evil on a server. regards, tom lane ---(end of broadcast)--- TIP 1: 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] time without time zone
Garry Saddington <[EMAIL PROTECTED]> writes: > Can anyone explain why time has todays date and time zone? Works for me: regression=# insert into periods values(1,1,'now','now'); INSERT 0 1 regression=# select * from periods; periodid | periodnumber | periodstart | periodend --+--++ 1 |1 | 16:13:14.35962 | 16:13:14.35962 (1 row) I speculate that you are trying to display the table in some client software that doesn't know the time datatype and is forcibly converting it to something it does know. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] time without time zone
Garry Saddington wrote: This is a select on table periods defined as such: CREATE TABLE periods ( periodid serial NOT NULL, periodnumber integer NOT NULL, periodstart time without time zone, periodend time without time zone, PRIMARY KEY (periodid) ) PeriodidPeriodnumberPeriodstart Periodend 6 1 2007/06/18 09:00:00 GMT+0 2007/06/18 09:30:00 GMT+0 7 2 2007/06/18 09:30:00 GMT+0 2007/06/18 10:00:00 GMT+0 Can anyone explain why time has todays date and time zone? I am confused, I only want time, such as: 13:00:00 You don't say what version you're running, but I can't reproduce this here on 8.2 - are you sure that table definition is right? CREATE TABLE timetest (t1 time, t2 time without time zone, t3 timestamp without time zone); INSERT INTO timetest values (now(),now(),now()); SELECT * FROM timetest; t1| t2| t3 -+-+ 21:12:30.346289 | 21:12:30.346289 | 2007-06-18 21:12:30.346289 (1 row) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] unexpected shutdown
[EMAIL PROTECTED] writes: could do logging wise on the postgres side to get a better indication of what is happening? You can increase the levels of loggin and redirect std_error to a file. Something along the lines of log_destination = 'stderr' log_filename = 'postgresql-%Y-%m-%d.log' log_error_verbosity = verbose log_min_error_statement = debug1 log_min_messages = info ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] time without time zone
This is a select on table periods defined as such: CREATE TABLE periods ( periodid serial NOT NULL, periodnumber integer NOT NULL, periodstart time without time zone, periodend time without time zone, PRIMARY KEY (periodid) ) PeriodidPeriodnumberPeriodstart Periodend 6 1 2007/06/18 09:00:00 GMT+0 2007/06/18 09:30:00 GMT+0 7 2 2007/06/18 09:30:00 GMT+0 2007/06/18 10:00:00 GMT+0 8 3 2007/06/18 10:00:00 GMT+0 2007/06/18 10:30:00 GMT+0 9 4 2007/06/18 10:30:00 GMT+0 2007/06/18 11:00:00 GMT+0 10 5 2007/06/18 11:30:00 GMT+0 2007/06/18 12:00:00 GMT+0 11 6 2007/06/18 13:00:00 GMT+0 2007/06/18 13:30:00 GMT+0 12 7 2007/06/18 13:30:00 GMT+0 2007/06/18 14:00:00 GMT+0 13 8 2007/06/18 14:00:00 GMT+0 2007/06/18 14:30:00 GMT+0 Can anyone explain why time has todays date and time zone? I am confused, I only want time, such as: 13:00:00 regards garry ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] unexpected shutdown
> <[EMAIL PROTECTED]> writes: > >> My database has shutdown several times in the last couple days. I have >> no >> idea why. I am running centos and I have not rebooted the server or >> made >> any configuration changes. I am running postgres 8.2 and it has been >> stable since I installed it about 5 months ago. The databases crashes >> and >> so my software application goes down. When I restart my application >> everything seems to work fine. But then it crashes again, something >> appears to be corrupt. Here are my logs: >> >> >> LOG: server process (PID 501) was terminated by signal 9 > > Signal 9 is SIGKILL which means something outside Postgres is killing > Postgres > processes. Either something is doing kill -9 of a Postgres pid. > > There used to be some OSes that recorded a SIGKILL process was killed > because > it had run out of memory, but I'm not sure Linux would report it as a > SIGKILL. > What does dmesg say, it doesn't have any OOM messages does it? > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > > Thanks for the replies... The box is very secure and I think I can safely say no one did a kill -9 on the postgres process. The java application that accesses postgres does sometimes have memory issues but i am surprised this would affect postgres.I am surprised linux allowed one process to affect the other like that. Should i be increasing postgres memory parameters or do you think this might just indicate the box is overloaded? Is there anything i could do logging wise on the postgres side to get a better indication of what is happening? thanks ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] unexpected shutdown
<[EMAIL PROTECTED]> writes: > My database has shutdown several times in the last couple days. I have no > idea why. I am running centos and I have not rebooted the server or made > any configuration changes. I am running postgres 8.2 and it has been > stable since I installed it about 5 months ago. The databases crashes and > so my software application goes down. When I restart my application > everything seems to work fine. But then it crashes again, something > appears to be corrupt. Here are my logs: > > > LOG: server process (PID 501) was terminated by signal 9 Signal 9 is SIGKILL which means something outside Postgres is killing Postgres processes. Either something is doing kill -9 of a Postgres pid. There used to be some OSes that recorded a SIGKILL process was killed because it had run out of memory, but I'm not sure Linux would report it as a SIGKILL. What does dmesg say, it doesn't have any OOM messages does it? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: 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 out of memory
Tom Lane writes: Keep in mind though that the COPY process is going to involve several working copies of that data (at least four that I can think of --- line input buffer, field input buffer, constructed text object, and constructed tuple). Will this be for the shared_buffers memory? I'm also not clear on whether the 512MB limit you refer to will count the PG shared memory area The OS limit is set to 1.6GB. I increased the shared_buffers to 450MB and it still failed. hundred meg off the top of what a backend can allocate as temporary workspace. Is there anything I can change in my log settings so I can produce something which will help you narrow down this problem? So it seems entirely likely to me that you'd need a ulimit above 512MB to push around 84MB fields. The issue I am trying to figure is which limit.. the OS limit is set to 1.6GB. I am now trying to increase my shared_buffers. So far have them at 450MB and it is still failing. Will also try the setting Vivek suggested although for that may need to restart the machine. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] unexpected shutdown
On 6/18/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: My database has shutdown several times in the last couple days. I have no idea why. I am running centos and I have not rebooted the server or made any configuration changes. I am running postgres 8.2 and it has been stable since I installed it about 5 months ago. The databases crashes and so my software application goes down. When I restart my application everything seems to work fine. But then it crashes again, something appears to be corrupt. Here are my logs: LOG: server process (PID 501) was terminated by signal 9 LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. FATAL: the database system is in recovery mode FATAL: the database system is in recovery mode LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2007-06-17 10:55:32 PDT LOG: checkpoint record is at 0/72F41748 LOG: redo record is at 0/72F41748; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 0/2638157; next OID: 52761 LOG: next MultiXactId: 4; next MultiXactOffset: 7 LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 0/72F41790 LOG: redo is not required LOG: database system is ready LOG: server process (PID 13904) was terminated by signal 9 LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2007-06-18 10:09:51 PDT LOG: checkpoint record is at 0/73609D18 LOG: redo record is at 0/73609D18; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 0/2645768; next OID: 52761 LOG: next MultiXactId: 4; next MultiXactOffset: 7 LOG: database system was not properly shu
[GENERAL] unexpected shutdown
My database has shutdown several times in the last couple days. I have no idea why. I am running centos and I have not rebooted the server or made any configuration changes. I am running postgres 8.2 and it has been stable since I installed it about 5 months ago. The databases crashes and so my software application goes down. When I restart my application everything seems to work fine. But then it crashes again, something appears to be corrupt. Here are my logs: LOG: server process (PID 501) was terminated by signal 9 LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. FATAL: the database system is in recovery mode FATAL: the database system is in recovery mode LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2007-06-17 10:55:32 PDT LOG: checkpoint record is at 0/72F41748 LOG: redo record is at 0/72F41748; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 0/2638157; next OID: 52761 LOG: next MultiXactId: 4; next MultiXactOffset: 7 LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 0/72F41790 LOG: redo is not required LOG: database system is ready LOG: server process (PID 13904) was terminated by signal 9 LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2007-06-18 10:09:51 PDT LOG: checkpoint record is at 0/73609D18 LOG: redo record is at 0/73609D18; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 0/2645768; next OID: 52761 LOG: next MultiXactId: 4; next MultiXactOffset: 7 LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zer
Re: [GENERAL] pg_restore out of memory
Francisco Reyes <[EMAIL PROTECTED]> writes: > Also the error is about running out of memory when trying to allocate 84MB. > The default FreeBSD limit is 512MB so 84MB is well below that. Keep in mind though that the COPY process is going to involve several working copies of that data (at least four that I can think of --- line input buffer, field input buffer, constructed text object, and constructed tuple). I'm also not clear on whether the 512MB limit you refer to will count the PG shared memory area, but if so that could easily be a couple hundred meg off the top of what a backend can allocate as temporary workspace. So it seems entirely likely to me that you'd need a ulimit above 512MB to push around 84MB fields. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
On Mon, Jun 18, 2007 at 02:38:32PM -0400, Andrew Sullivan wrote: > I've picked -advocacy. Actually, I _had_ picked advocacy, but had an itchy trigger finger. Apologies, all. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 1: 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] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
On Mon, Jun 18, 2007 at 02:16:56PM -0400, Jonah H. Harris wrote: > pgsql-advocacy... your thoughts? I've picked -advocacy. > > I think the Oracle discussion is over, David T. just needs URL references > IMHO. I don't think we can speak about Oracle; if we were licenced, we'd be violating it, and since we're not, we can't possibly know about it, right ;-) But there are some materials about why to use Postgres on the website: http://www.postgresql.org/about/advantages A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jonah H. Harris wrote: > Certainly, but can one expect to get a realistic answer to an, "is > Oracle fearing something" question on he PostgreSQL list? Or was it > just a backhanded attempt at pushing the topic again? My vote is for > the latter; it served no purpose other than to push the > competitiveness topic again. Well, I'm a cynic at heart, really. So there was no bad intend behind it. And it was a nice comment, because I would base it on my personal experiences with certain vendors, it wouldn't be near as nice. The original question was about comparisons between PG and Oracle. Now, I could answer this question from my personal experiences with the product and support. That would be way more stronger worded than my small cynic question. Another thing, Joshua posted a guesstimate that PG can compete in 90-95% cases with Oracle. Because Oracle insists on secrecy, I'm somehow inclined to believe the side that talks openly. And while I don't like to question Joshua's comment, I think he overlooked one set of problems, namely the cases where Oracle is not able to compete with PG. It's hard to quantify how many of these cases there are performance-wise, well, because Oracle insists on that silly NDA, but there are clearly cases where PG is superior. Andreas -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGds8WHJdudm4KnO0RAvb0AJ4gBec4yikrAOvDi5C3kc5NLGYteACghewU PkfrnXgCRfZlEdeMA2DZGTE= =BpUw -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] pg_restore out of memory
On Jun 18, 2007, at 2:10 PM, Francisco Reyes wrote: Also the error is about running out of memory when trying to allocate 84MB. The default FreeBSD limit is 512MB so 84MB is well below that. Try being less stingy than 128Mb for your stack. The default stack is 512Mb. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] [GENERAL] Setting Variable - (Correct)
On Jun 18, 2007, at 12:11 , Ranieri Mazili wrote: Look, I did a UNION, exist other way to do it better? Considering your aggregates are different, you shouldn't really union them. In the upper query of the union, you've got production_period (which is actually a date that represents the beginning of a month- long period), id_production_area, and an aggregate using sum as total_production_hours. In the lower query of the union, you've got production_period (a date representing the beginning of a year-long period), id_production_area, and a aggregate representing monthly average hours as total_production_hours. These are logically two separate results, and should not be unioned. It's easier to see if the columns are renamed appropriately: SELECT production_month, id_production_area, monthly_production_hours ... UNION SELECT production_year, id_production_area, monthly_average_production_hours ... You can see that they're different. One consequence of this is that for the query you have, you'll have more than on column with a date '-01-01': is this a production_month or a production_year? I guess I'd split it into two queries (and rename the columns). You might also be able to join the to queries so you get a result something like SELECT production_year , production_month , id_production_area , monthly_production_hours , monthly_average_production_hours Each month for the entire three-year range would be listed, and the production_year and monthly_production_hours would be repeated for each month of the year. Yet another way to do it would be to create a view for production_month, id_production_area, and monthly_production_hours (with no restriction on date range), and then call the view twice: once for the monthly figures for a year: SELECT production_month, id_production_area, monthly_production_hours FROM monthly_production WHERE production_month BETWEEN date_trunc('month', ? - interval '1 year') AND date_trunc('month', ?); and once more for the yearly figures for the past three: SELECT date_trunc('year', production_month) as production_year , sum(production_month) as number_of_months -- so you can see if you have a full twelve-months , id_production_area , average(monthly_production_hours) FROM monthly_production WHERE date_trunc('year', production_month) GROUP BY -- left as an exercise for the reader :) Note that if you don't have any lost hours for a given year, you may have some surprising results. You might want to look at generate_series or some other solution for generating a full list of months for you to join against. By the way, if you're going to do a lot of the same date_trunc work, you might want to create some functions that do this for you, e.g. (untested), CREATE FUNCTION trunc_year(date) RETURNS date IMMUTABLE LANGUAGE sql AS $_$ select date_trunc('year', $1)::date $_$; CREATE FUNCTION truc_years_ago(date, integer) RETURNS date IMMUTABLE LANGUAGE sql AS $_$ select date_trunc('year', $1 - $2 * INTERVAL '1 year')::date $_$: Note that foo::date is PostgreSQL-specific for CAST(foo AS DATE). Anyway, hope that gives you something to think about. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_resetxlog command not found
"Matt Bartolome" <[EMAIL PROTECTED]> writes: > I am doing some experimentation with a WAL archiving HA setup. I tried > turning to the pg_resetxlog command after removing some corrupted > files and it is not installed using the ubuntu dapper 8.1 package. Maybe they put it in some subpackage you didn't install? A very long time ago (7.2 days) it was one of our contrib modules, so it's conceivable that someone might be packaging it in postgresql-contrib still. I didn't think ubuntu was around that long though. I believe most packagers these days put it in the -server subpackage. If they really didn't include it anywhere, get a better Linux distro. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
On 6/18/07, Andrew Sullivan <[EMAIL PROTECTED]> wrote: It would appear that this was the flame-fest that was predicted. Particularly as this has been copied to five lists. If you all want to have an argument about what Oracle should or should not do, could you at least limit it to one list? Yeah, Josh B. asked it to be toned down to the original list which should've been involved. Which I think should be pgsql-admin or pgsql-advocacy... your thoughts? I think the Oracle discussion is over, David T. just needs URL references IMHO. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_resetxlog command not found
After poking around some more the command is located in /usr/lib/postgresql/8.1/bin/pg_resetxlog on ubuntu. On 6/18/07, Matt Bartolome <[EMAIL PROTECTED]> wrote: I am doing some experimentation with a WAL archiving HA setup. I tried turning to the pg_resetxlog command after removing some corrupted files and it is not installed using the ubuntu dapper 8.1 package. How do I get this command installed, or is there some other way to repair a database that is missing the pg_xlog directory files? I find it odd that the pg_resetxlog command is just missing... I have postgres installed from source on my laptop and it is there. Thanks, Matt ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] pg_restore out of memory
Vivek Khera writes: But that is a false assertion that the limit is 512Mb. On a random system of mine running FreeBSD/i386 it shows the default data limit as 1Gb, and on a random FreeBSD/amd64 box I see it at about 32Gb. I do no global tweaking of the size limits. Understood. I only showed "limit" because it was asked. I already set /boot/loader.conf to 1600MB. Also the error is about running out of memory when trying to allocate 84MB. The default FreeBSD limit is 512MB so 84MB is well below that. At this point this is basically stopping me from loading a table and so far I have not been able to get any insight into how this could be fixed. I wonder if there is any additional debuging I can turn on to help better troubleshoot this. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
All, On Mon, Jun 18, 2007 at 07:50:22PM +0200, Andreas Kostyrka wrote: [something] It would appear that this was the flame-fest that was predicted. Particularly as this has been copied to five lists. If you all want to have an argument about what Oracle should or should not do, could you at least limit it to one list? A -- Andrew Sullivan | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(end of broadcast)--- TIP 1: 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] pg_resetxlog command not found
I am doing some experimentation with a WAL archiving HA setup. I tried turning to the pg_resetxlog command after removing some corrupted files and it is not installed using the ubuntu dapper 8.1 package. How do I get this command installed, or is there some other way to repair a database that is missing the pg_xlog directory files? I find it odd that the pg_resetxlog command is just missing... I have postgres installed from source on my laptop and it is there. Thanks, Matt ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Using the query INTERSECTion
Vincenzo Romano <[EMAIL PROTECTED]> writes: > But now I have one more thing. The following command will fail with > a syntax error: > SELECT * FROM (SELECT 1 ) a INTERSECT (SELECT 2 ) b; > Because of the second (harmless) table alias. > In my mind it should work. Or not? Not. INTERSECT is not like JOIN from a syntactic perspective. According to the SQL spec, "something INTERSECT something" is a , and the only way to put one of those into a FROM-list is to wrap it with parens (making it a ) and then put an alias after it. This is because a FROM-list is a list of s, which have the syntax ::= [ [ AS ] [] ] | [ AS ] [] | ::= This works: SELECT * FROM ((SELECT 1 ) INTERSECT (SELECT 2 )) a; Aliases on the INTERSECT inputs don't work (and wouldn't have any real use if they did). Your original example is actually getting parsed as (SELECT * FROM (SELECT 1 ) a) INTERSECT (SELECT 2 ) b; which is OK, if redundant, up to the extraneous "b". regards, tom lane ---(end of broadcast)--- TIP 1: 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] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jonah H. Harris wrote: > > All of us have noticed the anti-MySQL bashing based on problems with > MySQL 3.23... Berkus and others (including yourself, if I am correct), > have corrected people on not making invalid comparisons against > ancient versions. I'm only doing the same where Oracle, IBM, and > Microsoft are concerned. > My, my, I fear my asbestos are trying to feel warm inside ;) Well, there is not much MySQL bashing going around. And MySQL 5 has enough "features" and current MySQL AB support for it is so "good", that there is no need to bash MySQL based on V3 problems. MySQL5 is still a joke, and one can quite safely predict the answers to tickets, with well over 50% guess rate. (Hint: I don't consider the answer: "Redo your schema" to be a satisfactory answer. And philosophically, the query optimizer in MySQL is near perfect. OTOH, considering the fact that many operations in MySQL still have just one way to execute, it's easy to choose the fastest plan, isn't it *g*) Andreas -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGdsgCHJdudm4KnO0RAg2oAKCdabTyQCcK8eC0+ErVJLlX59nNjgCfQjaO hhfSxBoESyCU/mTQo3gbQRM= =RqB7 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: 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 out of memory
On Jun 15, 2007, at 8:24 AM, Francisco Reyes wrote: Understood. But at least it shows that the program was already above the default of 512MB limit of the operating system. But that is a false assertion that the limit is 512Mb. On a random system of mine running FreeBSD/i386 it shows the default data limit as 1Gb, and on a random FreeBSD/amd64 box I see it at about 32Gb. I do no global tweaking of the size limits. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
On 6/18/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: Yeah funny how you didn't do that ;) (of course neither did I). I agree, an oops on my part :) It is amazing how completely misguided you are in this response. I haven't said anything closed minded. I only responded to your rather antagonistic response to a reasonably innocuous question of: "As a cynic, I might ask, what Oracle is fearing? " I wasn't responding to you, just to the seemingly closed-mindedness of the original question/statement. We're all aware of the reasons, for and against, proprietary system licenses prohibiting benchmarking. It is a good question to ask, and a good question to discuss. Certainly, but can one expect to get a realistic answer to an, "is Oracle fearing something" question on he PostgreSQL list? Or was it just a backhanded attempt at pushing the topic again? My vote is for the latter; it served no purpose other than to push the competitiveness topic again. I haven't seen any bashing going on yet. Shall we start with the closed mindedness and unfairness of per cpu license and support models? Not preferably, you make me type too much :) -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] pg_restore out of memory
Tom Lane writes: If that actually is the length of the line, the only answer is to raise the memory ulimit setting the postmaster runs under. Did another test to try to see if the problem is that row or the size of the row. Another record of greater size also failed. Any ideas what this 84MB limit could be from? I have shared_buffers at 450MB maintenance_work_mem = 64MB (which I increased to 100MB with the same result) OS limit for applications at 1.6GB. Also when I start postgresql I see several postgresql processes using 400M+ so I don't see how it could be the Os limit. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
Jonah H. Harris wrote: On 6/18/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: Depends? How many times are you going to antagonize the people that ask? As many times as necessary. Funny how the anti-proprietary-database arguments can continue forever and no one brings up the traditional RTFM-like response of, "hey, this was already discussed in thread XXX, read that before posting again." Yeah funny how you didn't do that ;) (of course neither did I). 1. It has *nothing* to do with anti-commercial. It is anti-proprietary which is perfectly legitimate. As long as closed-mindedness is legitimate, sure. It isn't closed minded to consider anti-proprietary a bad thing. It is an opinion and a valid one. One that many have made part of their lives in a very pro-commercial and profitable manner. 2. Oracle, Microsoft, and IBM have a "lot" to fear in the sense of a database like PostgreSQL. We can compete in 90-95% of cases where people would traditionally purchase a proprietary system for many, many thousands (if not hundreds of thousands) of dollars. They may well have a lot to fear, but that doesn't mean they do; anything statement in that area is pure assumption. 95% of life is assumption. Some of it based on experience, some of it based on pure conjecture, some based on all kinds of other things. I'm in no way saying we can't compete, I'm just saying that the continued closed-mindedness and inside-the-box thinking only serves to perpetuate malcontent toward the proprietary vendors by turning personal experiences into sacred-mailing-list gospel. It is amazing how completely misguided you are in this response. I haven't said anything closed minded. I only responded to your rather antagonistic response to a reasonably innocuous question of: "As a cynic, I might ask, what Oracle is fearing? " It is a good question to ask, and a good question to discuss. All of us have noticed the anti-MySQL bashing based on problems with MySQL 3.23... Berkus and others (including yourself, if I am correct), have corrected people on not making invalid comparisons against ancient versions. I'm only doing the same where Oracle, IBM, and Microsoft are concerned. I haven't seen any bashing going on yet. Shall we start with the closed mindedness and unfairness of per cpu license and support models? Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
Joshua D. Drake wrote: 2. Oracle, Microsoft, and IBM have a "lot" to fear in the sense of a database like PostgreSQL. We can compete in 90-95% of cases where people would traditionally purchase a proprietary system for many, many thousands (if not hundreds of thousands) of dollars. Well, I'm sure that is part of it, perhaps the major part. But part of also is likely to be avoiding every shlub with a computer doing some off-the-wall comparison showing X to be 1000 times "better" than Oracle, SQL Server or DB2; then the corresponding vendor has to spend endless time and money refuting all these half-baked comparisons. -- Guy Rouillier ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 PFC wrote: > >> 2. Oracle, Microsoft, and IBM have a "lot" to fear in the sense of a >> database like PostgreSQL. We can compete in 90-95% of cases where >> people would traditionally purchase a proprietary system for many, >> many thousands (if not hundreds of thousands) of dollars. > > Oracle also fears benchmarks made by people who don't know how to > tune Oracle properly... Well, bad results are as interesting as good results. And this problems applies to all other databases. Andreas -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGdsXdHJdudm4KnO0RArTkAKCZs6ht4z0lb2zHtr5MfXj8CsTZdQCgmwE5 JAD6Hkul1iIML42GO1vAM0c= =FMRt -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [PERFORM] [ADMIN] Postgres VS Oracle
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jonah H. Harris wrote: > On 6/18/07, Andreas Kostyrka <[EMAIL PROTECTED]> wrote: >> As a cynic, I might ask, what Oracle is fearing? > > As a realist, I might ask, how many times do we have to answer this > type of anti-commercial-database flamewar-starting question? > Well, my experience when working with certain DBs is much like I had some years ago, when I was forced to work with different SCO Unix legacy boxes. "Why do I have to put up with this silliness?", and with databases there is no way to get a sensible tool set by "shopping around" and installing GNU packages en masse :( Furthermore not being allowed to talk about performance is a real hard misfeature, like DRM. Consider: 1.) Performance is certainly an important aspect of my work as a DBA. 2.) Gaining experience as a DBA is not trivial, it's clearly a discipline that cannot be learned from a book, you need experience. As a developer I can gain experience on my own. As a DBA, I need some nice hardware and databases that are big enough to be nontrivial. 3.) The above points make it vital to be able to discuss my experiences. 4.) Oracle's license NDA makes exchanging experience harder. So as an endeffect, the limited number of playing grounds (#2 above) keeps hourly rates for DBAs high. Oracle's NDA limits secondary knowledge effects, so in effect it keeps the price for Oracle knowhow potentially even higher. Or put bluntly, the NDA mindset benefits completly and only Oracle, and is a clear drawback for customers. It makes Oracle-supplied consultants "gods", no matter how much hot air they produce. They've got the benefit of having internal peer knowledge, and as consumer there is not much that I can do counter it. I'm not even allowed to document externally the pitfalls and experiences I've made, so the next poor sob will walk on the same landmine. Andreas -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGdsT5HJdudm4KnO0RAoASAJ9b229Uhsuxn9qGfU5I0QUfTC/dqQCfZK/b 65XQFcc0aRBVptxW5uzLejY= =UIF6 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Using the query INTERSECTion
On Monday 18 June 2007 19:27:35 Martijn van Oosterhout wrote: > On Mon, Jun 18, 2007 at 04:10:41PM +0200, Vincenzo Romano wrote: > > Hello everyone. > > > > In order to build some dynamic queries (EXECUTE under PL/PgSQL) > > I'm taking in consideration to use the INTERSECT operator in > > order to split a WHERE-condition in a static one and a dynamic > > one to be built at runtime. > > The INTERSECT will almost certainly be slower, basically because > all the joins will have to be processed twice. Also, the results > won't be quite the same, especially with respect to duplicate > records and NULLs. > > Have a nice day, I think you are right, but I could rely on the cache to be affective and thus relieving the performance loss. But now I have one more thing. The following command will fail with a syntax error: SELECT * FROM (SELECT 1 ) a INTERSECT (SELECT 2 ) b; Because of the second (harmless) table alias. In my mind it should work. Or not? -- Vincenzo Romano -- Maybe Computer will never become as intelligent as Humans. For sure they won't ever become so stupid. [VR-1988] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
On 6/18/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: Depends? How many times are you going to antagonize the people that ask? As many times as necessary. Funny how the anti-proprietary-database arguments can continue forever and no one brings up the traditional RTFM-like response of, "hey, this was already discussed in thread XXX, read that before posting again." 1. It has *nothing* to do with anti-commercial. It is anti-proprietary which is perfectly legitimate. As long as closed-mindedness is legitimate, sure. 2. Oracle, Microsoft, and IBM have a "lot" to fear in the sense of a database like PostgreSQL. We can compete in 90-95% of cases where people would traditionally purchase a proprietary system for many, many thousands (if not hundreds of thousands) of dollars. They may well have a lot to fear, but that doesn't mean they do; anything statement in that area is pure assumption. I'm in no way saying we can't compete, I'm just saying that the continued closed-mindedness and inside-the-box thinking only serves to perpetuate malcontent toward the proprietary vendors by turning personal experiences into sacred-mailing-list gospel. All of us have noticed the anti-MySQL bashing based on problems with MySQL 3.23... Berkus and others (including yourself, if I am correct), have corrected people on not making invalid comparisons against ancient versions. I'm only doing the same where Oracle, IBM, and Microsoft are concerned. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
PFC wrote: 2. Oracle, Microsoft, and IBM have a "lot" to fear in the sense of a database like PostgreSQL. We can compete in 90-95% of cases where people would traditionally purchase a proprietary system for many, many thousands (if not hundreds of thousands) of dollars. Oracle also fears benchmarks made by people who don't know how to tune Oracle properly... Yes that is one argument that is made (and a valid one) but it is assuredly not the only one that can be made, that would be legitimate. Joshua D. Drake ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Using the query INTERSECTion
On Mon, Jun 18, 2007 at 04:10:41PM +0200, Vincenzo Romano wrote: > Hello everyone. > > In order to build some dynamic queries (EXECUTE under PL/PgSQL) > I'm taking in consideration to use the INTERSECT operator in order > to split a WHERE-condition in a static one and a dynamic one to be > built at runtime. The INTERSECT will almost certainly be slower, basically because all the joins will have to be processed twice. Also, the results won't be quite the same, especially with respect to duplicate records and NULLs. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
[GENERAL] Pg_standby and shutting down the warm standby
I am wondering if there is a proper procedure for shutting down the warm_standby server (8.2.4)? I am using pg_standby as my restore script in my testing: [EMAIL PROTECTED] cat recovery.conf restore_command = 'pg_standby -m -d -s 5 -w 0 -t /tmp/pgsql.trigger.5432 /usr/local2/pg_archive %f %p 2>> standby.log' My issue is it looks like the when the warm_standby comes back up, it is looking for a file that has already been loaded (and deleted). It was looking for log '*30' when it was shut down, but upon startup again it is looking for '*2F'. Thanks, Woody Standby.log Trigger file : /tmp/pgsql.trigger.5432 Waiting for WAL file : /usr/local2/pg_archive/0001002E WAL file path: 0001002E Restoring to... : pg_xlog/RECOVERYXLOG Sleep interval : 5 seconds Max wait interval: 0 forever Command for restore : mv /usr/local2/pg_archive/0001002E pg_xlog/RECOVERYXLOG WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... . . . WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... running restore : success Trigger file : /tmp/pgsql.trigger.5432 Waiting for WAL file : /usr/local2/pg_archive/0001002F WAL file path: 0001002F Restoring to... : pg_xlog/RECOVERYXLOG Sleep interval : 5 seconds Max wait interval: 0 forever Command for restore : mv /usr/local2/pg_archive/0001002F pg_xlog/RECOVERYXLOG WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... . . . WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... running restore : success Trigger file : /tmp/pgsql.trigger.5432 Waiting for WAL file : /usr/local2/pg_archive/00010030 WAL file path: 00010030 Restoring to... : pg_xlog/RECOVERYXLOG Sleep interval : 5 seconds Max wait interval: 0 forever Command for restore : mv /usr/local2/pg_archive/00010030 pg_xlog/RECOVERYXLOG WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... . . . WAL file not present yet. Checking for trigger file... Trigger file : /tmp/pgsql.trigger.5432 Waiting for WAL file : /usr/local2/pg_archive/0001.history WAL file path: 0001.history Restoring to... : pg_xlog/RECOVERYHISTORY Sleep interval : 5 seconds Max wait interval: 0 forever Command for restore : mv /usr/local2/pg_archive/0001.history pg_xlog/RECOVERYHISTORY running restore : history file not found Trigger file : /tmp/pgsql.trigger.5432 Waiting for WAL file : /usr/local2/pg_archive/0001002F WAL file path: 0001002F Restoring to... : pg_xlog/RECOVERYXLOG Sleep interval : 5 seconds Max wait interval: 0 forever Command for restore : mv /usr/local2/pg_archive/0001002F pg_xlog/RECOVERYXLOG WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... iGLASS Networks 211-A S. Salem St Apex NC 27502 (919) 387-3550 x813 www.iglass.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
2. Oracle, Microsoft, and IBM have a "lot" to fear in the sense of a database like PostgreSQL. We can compete in 90-95% of cases where people would traditionally purchase a proprietary system for many, many thousands (if not hundreds of thousands) of dollars. Oracle also fears benchmarks made by people who don't know how to tune Oracle properly... ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
Jonah H. Harris wrote: On 6/18/07, Andreas Kostyrka <[EMAIL PROTECTED]> wrote: As a cynic, I might ask, what Oracle is fearing? As a realist, I might ask, how many times do we have to answer this type of anti-commercial-database flamewar-starting question? Depends? How many times are you going to antagonize the people that ask? 1. It has *nothing* to do with anti-commercial. It is anti-proprietary which is perfectly legitimate. 2. Oracle, Microsoft, and IBM have a "lot" to fear in the sense of a database like PostgreSQL. We can compete in 90-95% of cases where people would traditionally purchase a proprietary system for many, many thousands (if not hundreds of thousands) of dollars. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] Setting Variable - (Correct)
Original Message Subject: Re:[SQL] [GENERAL] Setting Variable - (Correct) From: Michael Glaesemann <[EMAIL PROTECTED]> To: Ranieri Mazili <[EMAIL PROTECTED]> Date: 18/6/2007 13:50 [Please reply to the list so that others may benefit from and participate in the discussion.] On Jun 18, 2007, at 11:32 , Ranieri Mazili wrote: Thanks a lot for your prompt reply. You query is perfect for my problem, but I need another thing with it, I need to return the sum of production_hours of each month of the current year, and I need to return too the average of the 3 past years, can I do all in only one query or I need to do a UNION with another query? Glad you found it helpful. What have you tried so far? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: 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 Look how I did: SELECT date_trunc('month', production.production_date)::date AS production_period , product.id_production_area , sum(production_hours) as total_production_hours FROM production JOIN product USING (id_product) WHERE lost_hours = 'S' AND date_trunc('month', production.production_date)::date BETWEEN date_trunc('month', CAST('2007-06-18' AS date) - (EXTRACT(MONTH FROM CAST('2007-06-18' AS date))-1) * interval '1 month')::date AND date_trunc('month', CAST('2007-06-18' AS date))::date GROUP BY production_period, id_production_area UNION SELECT date_trunc('year', production.production_date)::date AS production_period , product.id_production_area , sum(production_hours)/12 as total_production_hours FROM production JOIN product USING (id_product) WHERE lost_hours = 'S' AND date_trunc('year', production.production_date)::date BETWEEN date_trunc('year', CAST('2007-06-18' AS date) - 3 * interval '1 year')::date AND date_trunc('year', CAST('2007-06-18' AS date) - 1 * interval '1 year')::date GROUP BY production_period, id_production_area ORDER BY production_period DESC I changed the "?" for values to test. Look, I did a UNION, exist other way to do it better? Thanks ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [PERFORM] [ADMIN] Postgres VS Oracle
On 6/18/07, Andreas Kostyrka <[EMAIL PROTECTED]> wrote: As a cynic, I might ask, what Oracle is fearing? As a realist, I might ask, how many times do we have to answer this type of anti-commercial-database flamewar-starting question? -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] [GENERAL] Setting Variable - (Correct)
[Please reply to the list so that others may benefit from and participate in the discussion.] On Jun 18, 2007, at 11:32 , Ranieri Mazili wrote: Thanks a lot for your prompt reply. You query is perfect for my problem, but I need another thing with it, I need to return the sum of production_hours of each month of the current year, and I need to return too the average of the 3 past years, can I do all in only one query or I need to do a UNION with another query? Glad you found it helpful. What have you tried so far? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [PERFORM] [ADMIN] Postgres VS Oracle
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 It's even harder, as Oracle disallows publishing benchmark figures in their license. As a cynic, I might ask, what Oracle is fearing? Andreas Jonah H. Harris wrote: > On 6/18/07, David Tokmatchi <[EMAIL PROTECTED]> wrote: >> Scalability ? Performance? Benchmark ? Availability ? Architecture ? >> Limitation : users, volumes ? Resouces needed ? Support ? > > Aside from the Wikipedia database comparison, I'm not aware of any > direct PostgreSQL-to-Oracle comparison. > -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGdrfHHJdudm4KnO0RAqKQAJ96t7WkLG/VbqkWTW60g6QC5eU4HgCfShNd o3+YPVnPJ2nwXcpi4ow28nw= =1CwN -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [GENERAL] Setting Variable - (Correct)
Original Message Subject: Re:[SQL] [GENERAL] Setting Variable - (Correct) From: Michael Glaesemann <[EMAIL PROTECTED]> To: Michael Glaesemann <[EMAIL PROTECTED]> Date: 18/6/2007 13:15 On Jun 18, 2007, at 10:17 , Michael Glaesemann wrote: Looking over your function, I'm a little confused about what you're trying to do. I'm guessing the (final) result you're trying to get is the number of hours lost for each product per area per month for the three years prior to the provided date. Or, rather, the number of hours lost per production area per month for the three years prior to the provided date. SELECT date_trunc('month', production.production_date)::date AS production_period , product.id_production_area , sum(production_hours) as total_production_hours FROM production JOIN product USING (id_product) WHERE lost_hours = 'S' AND date_trunc('month', a.production_date)::date BETWEEN date_trunc('month', ? - 3 * interval '1 year')::date AND date_trunc('month', ?)::date; Looks like I forgot the GROUP BY clause: GROUP BY production_period, id_production_area Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Thanks a lot for your prompt reply. You query is perfect for my problem, but I need another thing with it, I need to return the sum of production_hours of each month of the current year, and I need to return too the average of the 3 past years, can I do all in only one query or I need to do a UNION with another query? More one time, thanks a lot for your help. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [ADMIN] Postgres VS Oracle
This document: http://www-css.fnal.gov/dsg/external/freeware/mysql-vs-pgsql.html could answer some of your questions. Igor From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David Tokmatchi Sent: Monday, June 18, 2007 11:55 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; pgsql-general@postgresql.org; [EMAIL PROTECTED] Subject: [ADMIN] Postgres VS Oracle Hello from Paris I am DBA for Oracle and beginner on Postgres. For an company in France, I must make a comparative study, between Postgres and Oracle. Can you send any useful document which can help me. Scalability ? Performance? Benchmark ? Availability ? Architecture ? Limitation : users, volumes ? Resouces needed ? Support ? Regards cordialement david tokmatchi +33 6 80 89 54 74
Re: [GENERAL] Setting Variable - (Correct)
On Jun 18, 2007, at 10:17 , Michael Glaesemann wrote: Looking over your function, I'm a little confused about what you're trying to do. I'm guessing the (final) result you're trying to get is the number of hours lost for each product per area per month for the three years prior to the provided date. Or, rather, the number of hours lost per production area per month for the three years prior to the provided date. SELECT date_trunc('month', production.production_date)::date AS production_period , product.id_production_area , sum(production_hours) as total_production_hours FROM production JOIN product USING (id_product) WHERE lost_hours = 'S' AND date_trunc('month', a.production_date)::date BETWEEN date_trunc('month', ? - 3 * interval '1 year')::date AND date_trunc('month', ?)::date; Looks like I forgot the GROUP BY clause: GROUP BY production_period, id_production_area Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [ADMIN] Postgres VS Oracle
On 6/18/07, David Tokmatchi <[EMAIL PROTECTED]> wrote: Scalability ? Performance? Benchmark ? Availability ? Architecture ? Limitation : users, volumes ? Resouces needed ? Support ? Aside from the Wikipedia database comparison, I'm not aware of any direct PostgreSQL-to-Oracle comparison. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Atomicity in DB transactions (Rollback related)
Jasbinder Singh Bali escribió: > Could you please give me some quick and helpful pointers for SPI programing > in pl/perl? http://www.postgresql.org/docs/8.2/static/plperl-database.html -- Alvaro Herrera http://www.flickr.com/photos/alvherre/ "Puedes vivir solo una vez, pero si lo haces bien, una vez es suficiente" ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Atomicity in DB transactions (Rollback related)
Could you please give me some quick and helpful pointers for SPI programing in pl/perl? Thanks, Jas On 6/18/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote: Jasbinder Singh Bali escribió: > Hi, > I have a Pl/Perlu function in which I have a statement like this: > > *** > my $query_tbl_l_header = $dbh->prepare("SELECT > sp_insert_tbl_l_header(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); > > my $exec_l_from > =$query_tbl_l_header->execute($unmask_id,$from,$to,$sender,$subject,$replyto,$cc,$bcc,$messageid,$inreplyto,$reference,$mversion,$con_type,$con_id,$con_des,$con_enc,$con_length,$con_dis); > *** You have a PL/PerlU opening an independent transaction via DBI? That's a bad idea and the explanation to your problem. You should be using SPI instead; there are methods for this in PL/Perl. -- Alvaro Herrera http://www.advogato.org/person/alvherre "Hay que recordar que la existencia en el cosmos, y particularmente la elaboración de civilizaciones dentro de él no son, por desgracia, nada idílicas" (Ijon Tichy)
[GENERAL] Postgres VS Oracle
Hello from Paris I am DBA for Oracle and beginner on Postgres. For an company in France, I must make a comparative study, between Postgres and Oracle. Can you send any useful document which can help me. Scalability ? Performance? Benchmark ? Availability ? Architecture ? Limitation : users, volumes ? Resouces needed ? Support ? Regards cordialement david tokmatchi +33 6 80 89 54 74
Re: [GENERAL] Atomicity in DB transactions (Rollback related)
Jasbinder Singh Bali escribió: > Hi, > I have a Pl/Perlu function in which I have a statement like this: > > *** > my $query_tbl_l_header = $dbh->prepare("SELECT > sp_insert_tbl_l_header(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); > > my $exec_l_from > =$query_tbl_l_header->execute($unmask_id,$from,$to,$sender,$subject,$replyto,$cc,$bcc,$messageid,$inreplyto,$reference,$mversion,$con_type,$con_id,$con_des,$con_enc,$con_length,$con_dis); > *** You have a PL/PerlU opening an independent transaction via DBI? That's a bad idea and the explanation to your problem. You should be using SPI instead; there are methods for this in PL/Perl. -- Alvaro Herrerahttp://www.advogato.org/person/alvherre "Hay que recordar que la existencia en el cosmos, y particularmente la elaboración de civilizaciones dentro de él no son, por desgracia, nada idílicas" (Ijon Tichy) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pg_restore out of memory
Tom Lane writes: Looked at the record in question and the length of the long column in that row is 5753 (84MB). If that actually is the length of the line, the only answer is to raise the memory ulimit setting the postmaster runs under. The memory limit is 1.6GB. /boot/loader.conf kern.ipc.semmni=256 kern.ipc.semmns=512 kern.ipc.semmnu=256 kern.maxdsiz="1600MB" #1.6GB kern.dfldsiz="1600MB" #1.6GB kern.maxssiz="128M" # 128MB Also I have several postgress processes in the 400M+ size as reported by top Report from limit: cputime unlimited filesize unlimited datasize 2097152 kbytes stacksize131072 kbytes coredumpsize unlimited memoryuseunlimited <--- vmemoryuse unlimited descriptors 11095 memorylocked unlimited maxproc 5547 sbsize unlimited ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Atomicity in DB transactions (Rollback related)
Hi, I have a Pl/Perlu function in which I have a statement like this: *** my $query_tbl_l_header = $dbh->prepare("SELECT sp_insert_tbl_l_header(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); my $exec_l_from =$query_tbl_l_header->execute($unmask_id,$from,$to,$sender,$subject,$replyto,$cc,$bcc,$messageid,$inreplyto,$reference,$mversion,$con_type,$con_id,$con_des,$con_enc,$con_length,$con_dis); *** even if this execute, that calls a function sp_insert_tbl_l_header, fails, subsequent trasactions continue without failing the whole perl function there and then and makes the Db inconsistent. Shouldn't the whole function fail and exit at that particular failure and don't continue? Please let me know how do these transactions work in postgres. Thanks, Jas
Re: [GENERAL] Apparent Wraparound?
[EMAIL PROTECTED] wrote: > On Jun 18, 11:08 am, [EMAIL PROTECTED] wrote: > > On Jun 13, 2:35 pm, [EMAIL PROTECTED] wrote: > > > > Can someone tell me if I should be concerned about this log entry ? My > > database is quite large (~ 2G in PGDATA) > > BTW, I do not use autovacuum, and run vacuumdb on a weekly basis. Ok, here is what I can tell you: 1. this message can only appear during checkpoint. 2. this message, by itself, is harmless. All it says is that it tried to truncate (meaning, removing files previous to the segments in active use) the multixact system (directory PGDATA/pg_multixact) and it couldn't find an appropriate truncating point. 3. If it cannot find a truncating point, it logs this message and then moves the "already truncated" point to the requested truncating point. This means that some files might remain on disk. This is harmless because they will be overwritten when the numbering mechanism wraps around and creates the same files again. Make sure we are actually talking about the same log message: it must mention the directory "pg_multixact". The only situation in which this could be an actual problem is when the numbering is actually wrapping around very quickly, i.e. faster than checkpoints. If you are using lots of multixacts then this may be possible -- I am not sure. You use multixacts by creating shared tuple locks, which in turn are created when foreign keys are checked by more than one process at the same time. Questions: - what files are actually in PGDATA/pg_multixact/offsets and members? - do the multixact counters increase quickly? You can check them with pg_controldata -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Intervals (was: [GENERAL] DeadLocks..., DeadLocks...)
Tom Allison wrote: > I have a question though. > I noticed a particular format for identifying dates like: > now()-'3 days'::interval; > > What's '::interval' and why should I use it? Intervals are convenient, simply said. They are a special type dealing with date calculations relative to a given date. Basically they move calculation of relative dates to the database server instead of the programmer (always a good thing IMO). Next to that, they're much more readable compared to the alternative (which is in fact an implicit interval type measured in days, I suppose). Compare: SELECT now() + INTERVAL '1 month'; SELECT now() + CASE WHEN extract('month' from now()) IN (1, 3, 5, 7, 8, 10, 12) THEN 31 WHEN ...etc... END or: SELECT now() + INTERVAL '3 weeks - 5 days' SELECT now() + 16; The only drawback I know is that various query engines (ie. PHP's pg_ functions) don't know how to handle intervals. Suffice to say, I'm a big fan of the interval type. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Setting Variable - (Correct)
On Jun 18, 2007, at 9:34 , Ranieri Mazili wrote: Hello, I'm trying do the following function: CREATE OR REPLACE FUNCTION lost_hours_temp(date) RETURNS text AS $BODY$ DECLARE START_DATE date; END_DATE date; QUERY text; BEGIN START_DATE := $1; END_DATE := START_DATE - interval '3 year'; WHILE EXTRACT(YEAR FROM START_DATE) = EXTRACT(YEAR FROM END_DATE)+3 LOOP QUERY := 'SELECTCAST(EXTRACT(YEAR FROM A.production_date) || '/' || EXTRACT(MONTH FROM A.production_date) AS TEXT) as date, SUM(production_hours) AS production_hours, B.id_production_area FROM production A, product B WHERE EXTRACT(MONTH FROM production_date) = EXTRACT (MONTH FROM ' || START_DATE || ') AND EXTRACT(YEAR FROM A.production_date) = EXTRACT (YEAR FROM ' || START_DATE || ') AND lost_hours = ' || 'S' ||' AND A.id_product = B.id_product GROUP BY id_production_area, date'; START_DATE := START_DATE - interval '1 month'; END LOOP; RETURN QUERY; END; $BODY$ LANGUAGE 'plpgsql'; My problem is into WHILE, I'm trying to concatenate variables with the string, but I guess that it's generating an error. It looks like you've got a number of problems here, but overall it appears you're approaching this from the wrong way. What's the final result you want? I doubt it's just a query string. You probably want to run this query somewhere, and you can do this from within PL/ pgSQL. You may want to look up set returning functions. Looking over your function, I'm a little confused about what you're trying to do. I'm guessing the (final) result you're trying to get is the number of hours lost for each product per area per month for the three years prior to the provided date. You should be able to do this in just a single SQL query, something like: SELECT date_trunc('month', production.production_date)::date AS production_period , product.id_production_area , sum(production_hours) as total_production_hours FROM production JOIN product USING (id_product) WHERE lost_hours = 'S' AND date_trunc('month', a.production_date)::date BETWEEN date_trunc('month', ? - 3 * interval '1 year')::date AND date_trunc('month', ?)::date; Things that were puzzling to me about your code: * START_DATE is *after* END_DATE (END_DATE := START_DATE - INTERVAL '3 year') * WHILE EXTRACT(YEAR FROM START_DATE) = EXTRACT(YEAR FROM END_DATE)+3 LOOP will only be true for a limited number of months, not over the whole three-year range. The idea of three years has no real meaning in the query after this point. Anyway, hope this helps. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Apparent Wraparound?
On Jun 18, 11:08 am, [EMAIL PROTECTED] wrote: > On Jun 13, 2:35 pm, [EMAIL PROTECTED] wrote: > > Can someone tell me if I should be concerned about this log entry ? My > database is quite large (~ 2G in PGDATA) > > regards > Gerhard BTW, I do not use autovacuum, and run vacuumdb on a weekly basis. regards Gerhard ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [SQL] Setting variable
On Jun 18, 2007, at 9:29 , Ranieri Mazili wrote: CREATE OR REPLACE FUNCTION lost_hours_temp(date) RETURNS text AS $BODY$ DECLARE START_DATE date; END_DATE date; QUERY text; BEGIN START_DATE := $1; END_DATE := START_DATE - interval '3 year'; The last line (END_DATE := START_DATE - interval '3 year';) generate the following error: ERROR: operator is not unique: "unknown" / "unknown" SQL state: 42725 Hint: Could not choose a best candidate operator. You may need to add explicit type casts. Context: PL/pgSQL function "lost_hours_temp" line 10 at assignment Note that the error is at line 10. You've only shown lines 1 through 7 of the function body, so you haven't actually shown us where the error is. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: 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 out of memory
Francisco Reyes <[EMAIL PROTECTED]> writes: > Tried a pg_dump without -Fc to see if I could get that one table loaded. > Still failed. > psql:message-attachments-2007-06-15.sql:2840177: ERROR: out of memory > DETAIL: Failed on request of size 5765. > CONTEXT: COPY message_attachments, line 60660: "27202907225017 > research/crew holds.sit sit 5753t 1 > U3R1ZmZJdCAoYykxOTk3LTIwMDIgQWxhZGRpbiBTeX..." > Looked at the record in question and the length of the long column in that > row is 5753 (84MB). If that actually is the length of the line, the only answer is to raise the memory ulimit setting the postmaster runs under. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Setting Variable - (Correct)
Hello, I'm trying do the following function: CREATE OR REPLACE FUNCTION lost_hours_temp(date) RETURNS text AS $BODY$ DECLARE START_DATE date; END_DATE date; QUERY text; BEGIN START_DATE := $1; END_DATE := START_DATE - interval '3 year'; WHILE EXTRACT(YEAR FROM START_DATE) = EXTRACT(YEAR FROM END_DATE)+3 LOOP QUERY := 'SELECTCAST(EXTRACT(YEAR FROM A.production_date) || '/' || EXTRACT(MONTH FROM A.production_date) AS TEXT) as date, SUM(production_hours) AS production_hours, B.id_production_area FROM production A, product B WHERE EXTRACT(MONTH FROM production_date) = EXTRACT(MONTH FROM ' || START_DATE || ') AND EXTRACT(YEAR FROM A.production_date) = EXTRACT(YEAR FROM ' || START_DATE || ') AND lost_hours = ' || 'S' ||' AND A.id_product = B.id_product GROUP BY id_production_area, date'; START_DATE := START_DATE - interval '1 month'; END LOOP; RETURN QUERY; END; $BODY$ LANGUAGE 'plpgsql'; My problem is into WHILE, I'm trying to concatenate variables with the string, but I guess that it's generating an error. What's the correct form to concatenate strings with query in my case? Thanks ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Setting variable
Hello, I need to know why can't I do it? CREATE OR REPLACE FUNCTION lost_hours_temp(date) RETURNS text AS $BODY$ DECLARE START_DATE date; END_DATE date; QUERY text; BEGIN START_DATE := $1; END_DATE := START_DATE - interval '3 year'; The last line (END_DATE := START_DATE - interval '3 year';) generate the following error: ERROR: operator is not unique: "unknown" / "unknown" SQL state: 42725 Hint: Could not choose a best candidate operator. You may need to add explicit type casts. Context: PL/pgSQL function "lost_hours_temp" line 10 at assignment How can I solve it? Thanks ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Command line export or copy utility?
Reece Hart writes: On Tue, 2007-05-22 at 18:07 -0400, Francisco Reyes wrote: Does anyone know of any export or copy utility that runs on FreeBSD? I basically need a program that will connect to one database, do a select and copy the result to a second database. Two options: 1) if you want a whole table or schema, a pipe works nicely: eg$ pg_dump -t | psql 2) As of 8.2, you can formulate COPY commands with subqueries. For example: eg$ psql -c 'COPY (SELECT origin_id,origin FROM origin WHERE is_public order by 1) TO STDOUT' eg$ psql -c 'COPY (SELECT x FROM a WHERE x%2=1) TO STDOUT' \ | psql -c 'COPY a FROM STDIN;' For the archives. If using a version prior to 8.2 one can do from within psql: select * into temporary table tmp_copy_table from where ; copy tmp_copy_table to ''; This is primarily when one is trying to copy a subset of data. If doing the full table then, as Reece mentioned, pg_dump is the best route. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Using the query INTERSECTion
Hello everyone. In order to build some dynamic queries (EXECUTE under PL/PgSQL) I'm taking in consideration to use the INTERSECT operator in order to split a WHERE-condition in a static one and a dynamic one to be built at runtime. Instead of SELECT * FROM joinedtables WHERE static_cond AND dynamic_cond; I could use: SELECT * FROM joinedtables WHERE static_cond INTERSECT SELECT * FROM joinedtables WHERE dynamic_cond I'm wondering what'd be the difference in efficiency between these two queries. Is there any advise? Many thanks in advance. -- Vincenzo Romano -- Maybe Computer will never become as intelligent as Humans. For sure they won't ever become so stupid. [VR-1988] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Loop through all views with PHP
am Mon, dem 18.06.2007, um 14:59:34 +0200 mailte Stefan Schwarzer folgendes: > Hi there, > > my app is creating views for a certain task; now, I would like to run > on a regular basis a script which deletes these views. As they are > named with the date/hour/min/sec-appendix to make each view unique, I > don't know the names myself (Ok, I could stock the names in a > separate table as well). > > Is there any way via PHP to loop through the whole set of views to > delete those with a specific name? You can scripting this, http://people.planetpostgresql.org/greg/index.php?/archives/38-Scripting-with-psql.html#extended. Modify the query there, change "WHERE relkind = 'r'" and compare with 'v' (VIEW). Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Loop through all views with PHP
Stefan Schwarzer writes: Is there any way via PHP to loop through the whole set of views to delete those with a specific name? See pg_views. In particular the viewname column. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Loop through all views with PHP
Hi there, my app is creating views for a certain task; now, I would like to run on a regular basis a script which deletes these views. As they are named with the date/hour/min/sec-appendix to make each view unique, I don't know the names myself (Ok, I could stock the names in a separate table as well). Is there any way via PHP to loop through the whole set of views to delete those with a specific name? Thanks for any help. Stef ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Exec a text variable as select
am Mon, dem 18.06.2007, um 10:14:32 -0300 mailte Ranieri Mazili folgendes: > Hello, > > I'm creating a function that will create a select statement into a > while, this select will be stored into a text variable, after while ends > I need to execute this query stored into variable, on SQLSERVER I can do: > EXEC(text_variable) > How can I do this on Postgres? With EXECUTE. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Exec a text variable as select
Hello, I'm creating a function that will create a select statement into a while, this select will be stored into a text variable, after while ends I need to execute this query stored into variable, on SQLSERVER I can do: EXEC(text_variable) How can I do this on Postgres? I appreciate any help Thanks ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] What O/S or hardware feature would be useful for databases?
On 6/17/07, Greg Smith <[EMAIL PROTECTED]> wrote: On Sat, 16 Jun 2007, Ron Johnson wrote: > Anyway... databases are always(?) IO bound. I'd try to figure out how to > make a bigger hose (or more hoses) between the spindles and the mobo. What I keep waiting for is the drives with flash memory built-in to mature. I would love to get reliable writes that use the drive's cache for instant fsyncs, instead of right now where you have to push all that to the controller level. I don't think flash is the answer here...you should be looking at 'PRAM', i think. Solid state disks are coming very soon but flash is barely faster than traditional disks for random writes. (much faster for random reads however). Maybe this will change...flash is improving all the time. Already, the write cycle problem has been all but eliminated for the higher grade flash devices. That being said, it's pretty clear to me we are in the last days of the disk drive. When solid state drives become prevalent in server environments, database development will enter a new era...physical considerations will play less and less a role in how systems are engineered. So, to answer the OP, my answer would be to 'get rid of the spinning disk!' :-) merlin ---(end of broadcast)--- TIP 1: 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