[GENERAL] Ubuntu, Postgresql-8.2 and remote access thru pg_hba.conf
Hi,I just want to say that ubuntu team done an excelent job integration the penultimate version of postgresql in spite of the fact that I was running 7.4.7 it manages to install 8.2 in paralel without affecting my production version. There is one single thing that bothers me big time - I cannot connect to 8.2 from my vista box.I was able to do so to 7.4.7 using Vista and I'm still able to do so on the very same box!Here's some fact, I know people overhere like facts0. I want to connect from within my network ( only from one computer) to the remote box that runs ubuntu1. If I run a telnet on the box that runs the 8.2 to the port number I get a connection, and the postgredsql-8.2 log shows the connection (modified the postmaster.conf to log extra)2. Connection remotely from pgadmin it says connection fail and no other info. In postgresql logs there is no trace of the pgadmin connectionI assume that the 8.2 doesn't allow connection from my vista IP address3. My pg_hba.conf looks like below, I have an entry with IPV6 in there but it didn't work either.# Database administrative login by UNIX socketslocal all postgres ident sameuser# TYPE DATABASEUSERCIDR-ADDRESS METHOD# local is for Unix domain socket connections onlylocal all all ident sameuser# IPv4 local connections:host all all 127.0.0.1/32trusthostall all 192.168.1.103/32trust# IPv6 local connections:hostall all ::1/128 trustWhat do you suggest to try next.Thanks,MC
Re: [GENERAL] Normal distribution et al.?
Jan Danielsson wrote: Andrej Ricnik-Bay wrote: On 6/18/07, Jan Danielsson [EMAIL PROTECTED] wrote: UPDATE foo SET value=value+normdistsample(10, 0.2) WHERE id=1; Something like this? http://www.joeconway.com/plr/ That looks too good to be true. Many thanks! See http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut01 for a new intro, pretty basic, but a good place to start Brent Wood ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Ubuntu, Postgresql-8.2 and remote access thru pg_hba.conf
duh!listen_addresses = '*'I must add that having to modify both files for remote access is a bit misleading, and I didn't set at least 4 postgresql servers before...From: [EMAIL PROTECTED]: [EMAIL PROTECTED]: [GENERAL] Ubuntu, Postgresql-8.2 and remote access thru pg_hba.confDate: Mon, 18 Jun 2007 01:09:18 -0500 Hi,I just want to say that ubuntu team done an excelent job integration the penultimate version of postgresql in spite of the fact that I was running 7.4.7 it manages to install 8.2 in paralel without affecting my production version. There is one single thing that bothers me big time - I cannot connect to 8.2 from my vista box.I was able to do so to 7.4.7 using Vista and I'm still able to do so on the very same box!Here's some fact, I know people overhere like facts0. I want to connect from within my network ( only from one computer) to the remote box that runs ubuntu1. If I run a telnet on the box that runs the 8.2 to the port number I get a connection, and the postgredsql-8.2 log shows the connection (modified the postmaster.conf to log extra)2. Connection remotely from pgadmin it says connection fail and no other info. In postgresql logs there is no trace of the pgadmin connectionI assume that the 8.2 doesn't allow connection from my vista IP address3. My pg_hba.conf looks like below, I have an entry with IPV6 in there but it didn't work either.# Database administrative login by UNIX socketslocal all postgres ident sameuser# TYPE DATABASEUSERCIDR-ADDRESS METHOD# local is for Unix domain socket connections onlylocal all all ident sameuser# IPv4 local connections:host all all 127.0.0.1/32trusthostall all 192.168.1.103/32trust# IPv6 local connections:hostall all ::1/128 trustWhat do you suggest to try next.Thanks,MC
Re: [GENERAL] is it possible to recover more than one recordset or cursor from a function?
guillermo arias wrote: is it possible to recover more than one recordset or cursor from a function? I use to do it in ms sql server, but it is a mistery for me in postgre. CREATE FUNCTION returns2cursors(OUT c1 refcursor, OUT c2 refcursor) LANGUAGE plpgsql AS Yours, Laurenz Albe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] is it possible to recover more than one recordset or cursor from a function?
2007/6/18, Albe Laurenz [EMAIL PROTECTED]: guillermo arias wrote: is it possible to recover more than one recordset or cursor from a function? I use to do it in ms sql server, but it is a mistery for me in postgre. CREATE FUNCTION returns2cursors(OUT c1 refcursor, OUT c2 refcursor) LANGUAGE plpgsql AS or CREATE FUNCTION ... RETURNS SETOF CURSOR BEGIN ... END you can find notice about it in documentation http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html regards Pavel Stehule ---(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] Apparent Wraparound?
On Jun 13, 2:35 pm, [EMAIL PROTECTED] wrote: On Jun 8, 3:23 pm, [EMAIL PROTECTED] (Alvaro Herrera) wrote: Gunther Mayer wrote: Hi there, I just found the following message in my logs: Jun 8 10:38:38 caligula postgres[56868]: [1-1] : LOG: could not truncate directory pg_subtrans: apparent wraparound Should I be worried or can I just ignore this one? My database is still small (a pg_dumpall bzippe'd is still around 500KB) so I doubt that I'm affected by any transaction id wraparound problems. I also vacuum analyze once a day and have pg_autovacuum turned on. What version are you running? This seems to match the description of a bug fixed for 8.2 and 8.1.5: I noticed the same message in my logfiles (once on each of two servers). I'm running 8.1.8, and the server's been running flawless for about 2 months. Gerhard Can someone tell me if I should be concerned about this log entry ? My database is quite large (~ 2G in PGDATA) regards Gerhard ---(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] statistics on CRUD operations
Hi there, Is somewhere a system table providing statistic counters of CRUD operations against custom databases ? TIA, Sabin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pgadmin3 1.6.3 problem with geom fields
Pedro Doria Meunier wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi All, I've installed pgadmin3 1.6.3 from rpm, under Fedora 7 It runs ok, *except* when the tables have geometry fields! :O When one tries to open/view the table it takes **forever** to display the table's records! (?) Issue being discussed with Pedro on the pgadmin-support list: http://www.pgadmin.org/archives/pgadmin-support/2007-06/msg00046.php Regards, Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] statistics on CRUD operations
On Mon, 2007-06-18 at 12:35 +0300, Sabin Coanda wrote: Is somewhere a system table providing statistic counters of CRUD operations against custom databases ? pg_stat_user_tables http://www.postgresql.org/docs/8.2/static/monitoring-stats.html#MONITORING-STATS-VIEWS -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_restore out of memory
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... Have I encountered a bug? Looked at the record in question and the length of the long column in that row is 5753 (84MB). Any suggestions? ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] standard LOB support
Hi, I'm developing a software that supports several RDBMSs including PostgreSQL. The software needs an ability to handle large objects and now it uses 'bytea' datatype for binary data and 'text' for text data. But for portability, I'd rather use BLOB and CLOB defined by the SQL standards indeed. Is there any plan to support BLOB and CLOB in future releases? Thanks in advance, ebi ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] statistics on CRUD operations
Simon Riggs [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On Mon, 2007-06-18 at 12:35 +0300, Sabin Coanda wrote: Is somewhere a system table providing statistic counters of CRUD operations against custom databases ? pg_stat_user_tables http://www.postgresql.org/docs/8.2/static/monitoring-stats.html#MONITORING-STATS-VIEWS That's exactly what I need, but I found the signification of the columns is not trivial, so I'd appreciate very much some more details about them, please. Regards, Sabin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Ubuntu, Postgresql-8.2 and remote access thru pg_hba.conf
On 6/18/07, MC Moisei [EMAIL PROTECTED] wrote: Hi, I just want to say that ubuntu team done an excelent job integration the penultimate version of postgresql in spite of the fact that I was running 7.4.7 it manages to install 8.2 in paralel without affecting my production version. There is one single thing that bothers me big time - I cannot connect to 8.2 from my vista box. I was able to do so to 7.4.7 using Vista and I'm still able to do so on the very same box! Here's some fact, I know people overhere like facts 0. I want to connect from within my network ( only from one computer) to the remote box that runs ubuntu 1. If I run a telnet on the box that runs the 8.2 to the port number I get a connection, and the postgredsql-8.2 log shows the connection (modified the postmaster.conf to log extra) 2. Connection remotely from pgadmin it says connection fail and no other info. In postgresql logs there is no trace of the pgadmin connection I assume that the 8.2 doesn't allow connection from my vista IP address 3. My pg_hba.conf looks like below, I have an entry with IPV6 in there but it didn't work either. # Database administrative login by UNIX sockets local all postgres ident sameuser # TYPE DATABASEUSERCIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all ident sameuser # IPv4 local connections: hostall all 127.0.0.1/32trust hostall all 192.168.1.103/32trust # IPv6 local connections: hostall all ::1/128 trust What do you suggest to try next. Thanks, MC since you have 2 postgresql servers in the same machine postgresql listens on port 5432 for the first installed postgresql and in port 5433 for the second installed postgresql try using port 5433 for postgresql 8.2 -- Leonel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
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
[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] 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] 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] 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
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
[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] 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 table | 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 TABLE where CONDITION; copy tmp_copy_table to 'FULLPATH'; 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] 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
[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/
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/
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] 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: 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] 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
[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] 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/
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
[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)
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)
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] [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] 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
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: [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] [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)
[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
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)
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] [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: [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
[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] 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
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] [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] 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] [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] [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] [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
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] 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
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
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
-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] 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 query expression, and the only way to put one of those into a FROM-list is to wrap it with parens (making it a subquery) and then put an alias after it. This is because a FROM-list is a list of table references, which have the syntax table reference ::= table name [ [ AS ] correlation name [ left paren derived column list right paren ] ] | derived table [ AS ] correlation name [ left paren derived column list right paren ] | joined table derived table ::= table subquery 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
[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] [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
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] 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] [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
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: [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_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: [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] [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
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] 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
[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
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 shut
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
[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 pid 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] 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 pid 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
[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: 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
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] 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] 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] 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
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
[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] 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
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?
[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] 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] 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
[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] 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] 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
[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/
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.cchildkey as key FROM m.cChildtable r LEFT JOIN cmaintable d ON d.mainkey=r.cchildkey WHERE d.mainkey IS NULL; update m.cChildtable SET cchildkey=NULL FROM mydel WHERE cchildkey IS NOT NULL AND m.cChildtable.cchildkey= mydel.key; Hope this will run fast (will test tomorrow). My original skript UPDATE m.cChildtable SET cchildkey=NULL WHERE cchildkey IS NOT NULL AND cchildkey NOT IN (SELECT mainkey FROM cmaintable); 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] 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
[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
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] 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
[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] 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
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
