Re: [GENERAL] Logging Parameter Values
On Thu, 19 Jun 2008, Tom Lane [EMAIL PROTECTED] writes: Volkan YAZICI [EMAIL PROTECTED] writes: # tail -n 2 /srv/pg/pg_log/2008-06-19_141725.log migration_test postgres 2008-06-19 17:58:05.185 EEST LOG: duration: 2315.420 ms statement: EXECUTE foo(1000); migration_test postgres 2008-06-19 17:58:05.185 EEST DETAIL: prepare: PREPARE foo (int) AS SELECT S.i * T.i FROM generate_series(1, $1) AS S(i), generate_series(1, $1) AS T(i); Hmm, we're not on the same page here. I thought you were talking about protocol-level parameters. In the above example, the parameter values are shown in the EXECUTE statement, so what else do you need? Umm... Bogus test case. CL-USER (postmodern:connect-toplevel migration_test postgres nil 192.168.1.160) ; No value CL-USER (and (postmoder:query (concatenate 'string SELECT S.i * T.* FROM generate_series(1, $1) AS S (i), generate_series(1, $2) AS T (i)) 1000 1000) nil) NIL # tail /srv/pg/pg_log/2008-06-19_141725.log -n 2 migration_test postgres 2008-06-20 09:02:33.695 EEST LOG: duration: 4419.475 ms execute unnamed: SELECT S.i * T.* FROM generate_series(1, $1) AS S (i), generate_series(1, $2) AS T (i) migration_test postgres 2008-06-20 09:02:33.695 EEST DETAIL: parameters: $1 = '1000', $2 = '1000' Hrm... Now what might have caused the logged statements with missing parameter values I pasted. I was probably looking at past log files belongs to some other logging configuration. Excuse me for the noise. And thanks for the prompt reply. Regards. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Losing data
On Friday 20 June 2008 05:26, Robert Treat wrote: On Thursday 19 June 2008 14:06:38 Garry Saddington wrote: In any case, however, if PostgreSQL reported the transaction complete and the machine didn't experience any hardware problems (like sudden power or disk failure), I would certainly not suspect PostgreSQL as the source of the problem. What has happened to the reports then? I have used this combination of Zope and Postgres for 5 years with no problems like this before and we have written one complete set of reports on this server in the past 6 weeks. The problem seems to have started last friday, when reports started to go missing. Out of curiosity, what is your vacuum strategy? We back up and vacuum at the same time each day. garry -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Need to update all entries of one table based on an earlier backup
For reasons best left unmentioned, I need to update entries in a table from a backup; I need to do all entries. For reasons eluding my sleep deprived eyes this fails in every variation I can think of: update foo set foo.foo_name2=foo_old.foo_name2 where foo.foo_id = foo_old.foo_id; ERROR: missing FROM-clause entry for table foo_old Could someone please hit me with a clue-stick ? This is crucial and yet I am stumbling over something. Since I am not updating foo_old I am baffled as to what this messsage really means. I've tried where foo_old.foo_id = foo.foo_id ... same message. TIA, Greg Williamson Senior DBA DigitalGlobe Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.)
Re: [GENERAL] Need to update all entries of one table based on an earlier backup
Hi! You're missing a table declaration for the table foo_old. You might try this: update foo set foo.foo_name2= (SELECT foo_old.foo_name2 FROM foo_old where foo.foo_id = foo_old.foo_id); Kind regards Markus Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Gregory Williamson Gesendet: Freitag, 20. Juni 2008 12:30 An: pgsql-general@postgresql.org Betreff: [GENERAL] Need to update all entries of one table based on an earlier backup For reasons best left unmentioned, I need to update entries in a table from a backup; I need to do all entries. For reasons eluding my sleep deprived eyes this fails in every variation I can think of: update foo set foo.foo_name2=foo_old.foo_name2 where foo.foo_id = foo_old.foo_id; ERROR: missing FROM-clause entry for table foo_old Could someone please hit me with a clue-stick ? This is crucial and yet I am stumbling over something. Since I am not updating foo_old I am baffled as to what this messsage really means. I've tried where foo_old.foo_id = foo.foo_id ... same message. TIA, Greg Williamson Senior DBA DigitalGlobe Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.) Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276
Re: [GENERAL] Need to update all entries of one table based on an earlier backup
Thomas Pundt wrote: On Freitag, 20. Juni 2008, Gregory Williamson wrote: ...snip... Does update foo set foo.foo_name2 = foo_old.foo_name2 from foo_old where foo.foo_id = foo_old.foo_id; work for you? Thanks to all for pointing out the FROM clause.! UPDATE foo set foo_name2 = foo_old.foo_name2 FROM foo_old WHERE foo.foo_id = foo_old.foo_id; did the trick. For some reason I keep forgetting that bit. Especially when under stress in the wee hours of the morn. Back to sleep ... ZZZ And thanks again ! GSW
Re: [GENERAL] Need to update all entries of one table based on an earlier backup
On Freitag, 20. Juni 2008, Gregory Williamson wrote: | For reasons best left unmentioned, I need to update entries in a table from | a backup; I need to do all entries. | | For reasons eluding my sleep deprived eyes this fails in every variation I | can think of: | | update foo set foo.foo_name2=foo_old.foo_name2 where foo.foo_id = | foo_old.foo_id; ERROR: missing FROM-clause entry for table foo_old Does update foo set foo.foo_name2 = foo_old.foo_name2 from foo_old where foo.foo_id = foo_old.foo_id; work for you? | Could someone please hit me with a clue-stick ? This is crucial and yet I | am stumbling over something. Since I am not updating foo_old I am baffled | as to what this messsage really means. I've tried where foo_old.foo_id = | foo.foo_id ... same message. Ciao, Thomas -- Thomas Pundt [EMAIL PROTECTED] http://rp-online.de/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] test aggregate functions without a dummy table
Hi, I want to test the behavior of an an aggregate without creating a dummy table for it. But the code for it is horrible. Is there no simpler way? select max(foo) from (select 1 as foo union select 2 as foo)bar; thx
Re: [GENERAL] renumber table
David Spadea wrote:
Steve,
I'd just like to add that I agree with Scott that this is asking for
trouble if the field being renumbered is used as a foreign key
somewhere. If you have no way of changing this logic, you should at
least look into 'on delete cascade' and 'on update cascade' on your
dependent tables. You can expect performance to suffer if the dependent
tables are large, but at least you don't lose referential integrity.
Dave
On Thu, Jun 19, 2008 at 7:07 PM, David Spadea [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Steve,
Here's your problem and its solution as I understand it:
-- Given an example table like this (data isn't too important --
just the sequencing)
create table meh
(
idserial primary key
, word varchar(10)
);
-- Populate it with data
insert into meh (word) values
('one'),
('two'),
('three'),
('four'),
('five'),
('six'),
('seven'),
('eight'),
('nine'),
('ten');
-- Delete a row from the middle of the table
delete from meh where id = 5;
-- Renumber all of the rows ABOVE the deleted row
-- This will maintain sequencing. This assumes that no gaps existed
prior to the delete of this row,
-- and that only one row was deleted.
update meh
set id = id - 1
where id 5;
At this point, if you've got a sequence that populates the id field,
you'll need to set its nextval.
Dave
On Thu, Jun 19, 2008 at 7:54 AM, Steve Clark [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Scott Marlowe wrote:
On Wed, Jun 18, 2008 at 3:50 PM, Steve Clark
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote:
I realize this is certainly not the best design - but at
this point in time
it can't be changed. The table
is rarely updated and never concurrently and is very
small, typically less
than 100 rows so there really is
no performance impact.
Then the easiest way to renumber a table like that is to do
something like:
create temp sequence myseq;
update table set idfield=nextval('myseq');
and hit enter.
and pray. :)
Hi Scott,
I am not sure that will do what I want. As an example
suppose I have 5 rows and the idfield is 1,2,3,4,5
now row 1 is updated, not the idfield but another column, then
row 3 is deleted.
Now I would like to renumber them such that 1 is 1, 2 is 2, 4
is 4 , 5 is 4.
I don't think what you wrote will necessarily keep them in the
same relative order that they
were before.
Regards,
Steve
--
Sent via pgsql-general mailing list
(pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thanks to all that replied.
I agree the design wasn't the best and we had a function similar to what
you describe to keep things in order. Problem was we had a foreign key
constraint that caused a row
to be deleted, because the foreign key was deleted when it shouldn't have been.
So now the table
row numbering was messed up. It really doesn't cause a problem but when the
table information gets
displayed it uses the row num for access to the table and looked wierd with the
gaps in the numbering.
I took the easy way out and before displaying the table I check to see if
max(row_num) is not equal to
count(*) then I renumber it in the php script that displays it using a loop.
Thanks again.
Steve
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to UPDATE in ROW-style?
Hello. How could I write something like: DECLARE r table%ROWTYPE; ... UPDATE table SET (table.*) = (SELECT r.*) WHERE id = r.id; *This *syntax is invalid, and I cannot find another proper way to do it without explicit enumeration of table's columns. I don't want to explicitly specify table's column to minimize later refactoring. P.S. The corresponding INSERT operator works fine: DECLARE r table%ROWTYPE; ... INSERT INTO table (SELECT r.*); Please say if a similar syntax for UPDATE exists.
Re: [GENERAL] Easiest way to copy table from one db to another?
On Wed, Jun 18, 2008 at 4:08 PM, Scott Marlowe [EMAIL PROTECTED] wrote: On Wed, Jun 18, 2008 at 1:48 PM, Kynn Jones [EMAIL PROTECTED] wrote: What's the simplest way to copy a table from one database to another one running on the same server? Easiest way to me: pg_dump -t tablename dbname | psql otherdbname Thanks! Kynn
[GENERAL] Connection to second database on server
Hello everyone, I already asked about that a couple of days ago, but didn't get an satisfying solution for my problem which is following: I need to create a view that does a query on a second database on the same PostgreSQL server. dblink seems to be the only (???) solution for doing so. The problems are: Referring to dblink documentation I'll have to hardcode (uaah!!)username and password. 1.) Hence, everyone who could see the view definition e.g. in pgAdmin will be able to read the username and password (for the second database). 2.) If I have multiple postgres users with different rights they will all be treated as that one hard-coded user for the second database when querying the view. Someone suggested to set up a pgpass file so the query can get these dynamically. However a pgpass file is also not secure as username and password are stored in plain text, and problem #2 won't be solved, too. Does anyone have an idea how to better set up a database view for viewing records from another database? MSSQL for instance allows schema prefixes for using other databases of the same server, the current user information is being used to connect to this database as well. If dblink would allow to omit user and pwd and then use the current connection information to connect to the second database, this would perfectly do the job! Thank you very much for your help. Best regards, H. Muster -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] A plpgsql unidentifiable problem.
Easy: you've got 3 Ifs without END IF: IF uppergt = 'BOD' THEN RETURN 0 ; IF uppergt = 'MOD' THEN RETURN 86400/2 ; IF uppergt = 'EOD' THEN RETURN 86399 ; Igor -Original Message- From: Ralph Smith [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2008 3:19 PM To: pgsql-general@postgresql.org Subject: A plpgsql unidentifiable problem. I'm baffled and have tried various variations but still nogo. From PgAdmin III I get: --- ** Error ** ERROR: syntax error at or near ; SQL state: 42601 Character: 19001 -- referring to the semi-colon after the 'END' statement. *** When I check this out in PgAdmin III. --- CREATE OR REPLACE FUNCTION time_to_utime(given_time varchar) RETURNS INT AS $$ DECLARE uppergt varchar ; colon1int ; colon2int ; digitsA varchar ; digitsB varchar ; digitsC varchar ; numberA int ; numberB int ; numberC int ; resultint ; BEGIN IF check_time(given_time) = FALSE THEN RAISE NOTICE 'The time passed into function time_to_utime is not in a valid format.' ; END IF ; -- uppergt := upper(given_time) ; IF uppergt = 'BOD' THEN RETURN 0 ; IF uppergt = 'MOD' THEN RETURN 86400/2 ; IF uppergt = 'EOD' THEN RETURN 86399 ; -- colon1:=strpos(invar,':') ; colon2:=colon1+strpos(substring(invar from colon1+1),':') ; digitsA := split_part(invar,':',1) ; numberA := to_number(digitsA,'99') ; digitsB := split_part(invar,':',2) ; numberB := to_number(digitsB,'99') ; digitsC := split_part(invar,':',3) ; numberC := to_number(digitsC,'99') ; result := 3600*numberA + 60*numberB + numberC ; RETURN result ; END ; $$ LANGUAGE PLPGSQL ;/* time_to_utime */ Any clues? THANKS! Ralph Smith -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connection to second database on server
Hi, Hermann. The best solution in my opinion would be using users name and password to connect to database. That way you can decide (grant) what tables can be accessed by this user and there is no password you have to hide from him (or others). So use stored procedure returning set of records instead ot view and pass the password as a parameter. I know this is not too good, but its the best I can come up with. On the other hand you can configure postgreSQL to trust connections from localhost. That way you can exclude password from the connect string. Any way - I would not advice you to go that path because of security reasons. Julius Tuskenis Hermann Muster rašė: Hello everyone, I already asked about that a couple of days ago, but didn't get an satisfying solution for my problem which is following: I need to create a view that does a query on a second database on the same PostgreSQL server. dblink seems to be the only (???) solution for doing so. The problems are: Referring to dblink documentation I'll have to hardcode (uaah!!)username and password. 1.) Hence, everyone who could see the view definition e.g. in pgAdmin will be able to read the username and password (for the second database). 2.) If I have multiple postgres users with different rights they will all be treated as that one hard-coded user for the second database when querying the view. Someone suggested to set up a pgpass file so the query can get these dynamically. However a pgpass file is also not secure as username and password are stored in plain text, and problem #2 won't be solved, too. Does anyone have an idea how to better set up a database view for viewing records from another database? MSSQL for instance allows schema prefixes for using other databases of the same server, the current user information is being used to connect to this database as well. If dblink would allow to omit user and pwd and then use the current connection information to connect to the second database, this would perfectly do the job! Thank you very much for your help. Best regards, H. Muster -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connection to second database on server
In response to Hermann Muster [EMAIL PROTECTED]: Hello everyone, I already asked about that a couple of days ago, but didn't get an satisfying solution for my problem which is following: I need to create a view that does a query on a second database on the same PostgreSQL server. dblink seems to be the only (???) solution for doing so. The problems are: Referring to dblink documentation I'll have to hardcode (uaah!!)username and password. 1.) Hence, everyone who could see the view definition e.g. in pgAdmin will be able to read the username and password (for the second database). 2.) If I have multiple postgres users with different rights they will all be treated as that one hard-coded user for the second database when querying the view. Someone suggested to set up a pgpass file so the query can get these dynamically. However a pgpass file is also not secure as username and password are stored in plain text, and problem #2 won't be solved, too. Does anyone have an idea how to better set up a database view for viewing records from another database? MSSQL for instance allows schema prefixes for using other databases of the same server, the current user information is being used to connect to this database as well. I feel this paragraph encapsulates your problem. To summarize: you're doing it wrong. Don't take this as an attack, it's not. It's a statement that PostgreSQL handles this kind of thing differently than MySQL, and if you try to do it the MySQL way, you're going to hit these kinds of problems. The PostgreSQL way to do it is to create schemas within a single database, you can then use roles to set permissions, use search_path to determine what users see by default, and schema-qualify when needed. If you can't migrate your setup to use schemas, then I expect anything else you do will feel sub-optimal, as PostgreSQL is designed to use schemas for this sort of thing. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] test aggregate functions without a dummy table
Willy-Bas Loos [EMAIL PROTECTED] writes: I want to test the behavior of an an aggregate without creating a dummy table for it. But the code for it is horrible. Is there no simpler way? select max(foo) from (select 1 as foo union select 2 as foo)bar; Perhaps VALUES? regression=# select max(foo) from (values(1,2),(3,4),(5,6)) as v(foo,bar); max - 5 (1 row) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connection to second database on server
On Fri, Jun 20, 2008 at 8:06 AM, Bill Moran [EMAIL PROTECTED] wrote: In response to Hermann Muster [EMAIL PROTECTED]: Hello everyone, I already asked about that a couple of days ago, but didn't get an satisfying solution for my problem which is following: I need to create a view that does a query on a second database on the same PostgreSQL server. dblink seems to be the only (???) solution for doing so. The problems are: Referring to dblink documentation I'll have to hardcode (uaah!!)username and password. 1.) Hence, everyone who could see the view definition e.g. in pgAdmin will be able to read the username and password (for the second database). 2.) If I have multiple postgres users with different rights they will all be treated as that one hard-coded user for the second database when querying the view. Someone suggested to set up a pgpass file so the query can get these dynamically. However a pgpass file is also not secure as username and password are stored in plain text, and problem #2 won't be solved, too. Does anyone have an idea how to better set up a database view for viewing records from another database? MSSQL for instance allows schema prefixes for using other databases of the same server, the current user information is being used to connect to this database as well. I feel this paragraph encapsulates your problem. To summarize: you're doing it wrong. Don't take this as an attack, it's not. It's a statement that PostgreSQL handles this kind of thing differently than MySQL, and if you try to do it the MySQL way, you're going to hit these kinds of problems. Point of order, he was talking about MSSQL, not MySQL, but your point is not changed by that at all. The PostgreSQL way to do it is to create schemas within a single database, you can then use roles to set permissions, use search_path to determine what users see by default, and schema-qualify when needed. If you can't migrate your setup to use schemas, then I expect anything else you do will feel sub-optimal, as PostgreSQL is designed to use schemas for this sort of thing. Note that this is also the Oracle way of doing things. I much prefer schemas to cross database work myself. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] test aggregate functions without a dummy table
On Fri, Jun 20, 2008 at 10:11:08AM -0400, Tom Lane wrote: Willy-Bas Loos [EMAIL PROTECTED] writes: I want to test the behavior of an an aggregate without creating a dummy table for it. But the code for it is horrible. Is there no simpler way? select max(foo) from (select 1 as foo union select 2 as foo)bar; Perhaps VALUES? regression=# select max(foo) from (values(1,2),(3,4),(5,6)) as v(foo,bar); Or perhaps using a set-returning function like generate_series(): test= select max(foo) from generate_series(1, 100) as g(foo); max - 100 (1 row) -- Michael Fuhr -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Timezone issue - Is it me or is this a massive bug?
I have a server of which the OS timezone is set to Pacific time (currently -7). I run the following query on it SELECT now(), now() AT TIME ZONE 'GMT+10:00', now() AT TIME ZONE 'GMT-10:00', now() AT TIME ZONE 'Australia/Melbourne' I would expect this to return: * column 1 - the current time in the pacific (-7) - 2008-06-20 13:09:39.245641-07 * column 2 - the GMT +10 - 2008-06-21 06:09:39.245641 * column 3 - the GMT -10 - 2008-06-20 10:09:39.245641 * column 4 - the current time in Melbourne Australia - 2008-06-21 06:09:39.245641 Instead it returns: * column 1 - the current time in the pacific (-7) (2008-06-20 13:09:39.245641-07 - CORRECT) * column 2 - the current time MINUS 10 (2008-06-20 10:09:39.245641 - WRONG) * column 3 - the current time PLUS 10 (2008-06-21 06:09:39.245641 - WRONG) * column 4 - the current time in Melbourne Australia (2008-06-21 06:09:39.245641 - CORRECT) Am I missing something obvious? Seems when I specify GMT+10:00 it returns GMT-10:00 and vice versa. Note that column 2 3 are timestamp withOUT timezone while 1 4 are timestamp WITH timezone. But I still see this as totally wrong. Regards, Collin Peters -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Forcibly vacating locks
Laurent Birtz wrote: No. The closest thing we have is log_lock_waits in 8.3. I wonder if you could hack up something to monitor the server logs for such messages and cancel the queries. Assuming I can monitor the logs in this way, how would I cancel the queries (or lack thereof, in the case of a client that sits doing nothing with a held lock)? Use log_line_prefix to get the process id in the log line, then use pg_cancel_backend() on the process id. 2) Is there any hostility about the notion of implementing this feature into Postgres? Probabably --- it seems like a narrow use case. I'll consider this to be the definite answer unless I hear a dissenting opinion in the next few days. Yea, I might be wrong. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Timezone issue - Is it me or is this a massive bug?
On Friday 20 June 2008 1:19 pm, Collin Peters wrote: I have a server of which the OS timezone is set to Pacific time (currently -7). I run the following query on it SELECTnow(), now() AT TIME ZONE 'GMT+10:00', now() AT TIME ZONE 'GMT-10:00', now() AT TIME ZONE 'Australia/Melbourne' I would expect this to return: * column 1 - the current time in the pacific (-7) - 2008-06-20 13:09:39.245641-07 * column 2 - the GMT +10 - 2008-06-21 06:09:39.245641 * column 3 - the GMT -10 - 2008-06-20 10:09:39.245641 * column 4 - the current time in Melbourne Australia - 2008-06-21 06:09:39.245641 Instead it returns: * column 1 - the current time in the pacific (-7) (2008-06-20 13:09:39.245641-07 - CORRECT) * column 2 - the current time MINUS 10 (2008-06-20 10:09:39.245641 - WRONG) * column 3 - the current time PLUS 10 (2008-06-21 06:09:39.245641 - WRONG) * column 4 - the current time in Melbourne Australia (2008-06-21 06:09:39.245641 - CORRECT) Am I missing something obvious? Seems when I specify GMT+10:00 it returns GMT-10:00 and vice versa. Note that column 2 3 are timestamp withOUT timezone while 1 4 are timestamp WITH timezone. But I still see this as totally wrong. Regards, Collin Peters See this message for the explanation: http://archives.postgresql.org/pgsql-bugs/2008-04/msg00077.php -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] System in Recovery Mode But No Activity
We had a run away process on our database box that used up all the
physical and all the virtual memory (swap). This caused the RedHat
Linux oom-killer to kill many processes, including some Postgres ones.
Postgres went into a funky state after that time:
2008-06-20 14:19:10 CDT [unknown] LOG: invalid length of startup packet
2008-06-20 14:20:50 CDT ERROR: canceling autovacuum task
2008-06-20 14:20:50 CDT CONTEXT: automatic vacuum of table
lms_nna.pg_catalog.pg_listener
2008-06-20 14:21:48 CDT ERROR: canceling autovacuum task
2008-06-20 14:21:49 CDT CONTEXT: automatic vacuum of table
lms_infiniti.pg_catalog.pg_listener
2008-06-20 14:27:33 CDT WARNING: worker took too long to start; cancelled
2008-06-20 14:45:53 CDT LOG: server process (PID 22435) was
terminated by signal 9: Killed
...
2008-06-20 14:50:28 CDT dbname 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.
2008-06-20 14:50:28 CDT dbname HINT: In a moment you should be able
to reconnect to the database and repeat your command.
...
2008-06-20 14:50:36 CDT dbname FATAL: the database system is in recovery mode
Now any attempt to connect to the database yield the message the
database system is in recovery mode.
The processes that are still running are:
postgres 31103 0.0 0.0 149024 492 ?SApr19 8:18
/opt/lms/64/postgres/8.3/bin/postmaster -p 6544 -D /pgdata/8.3/data
postgres 31109 0.0 0.0 65408 104 ?Ss Apr19 1:54 \_
postgres: logger process
postgres 32645 0.0 0.0 152708 564 ?Ss Jun15 3:14 \_
postgres: slony databasename 172.16.172.246(41114) idle in
transaction
postgres 1179 0.0 0.0 152708 348 ?Ss Jun15 3:30 \_
postgres: slony databasename 172.16.172.246(41138) idle in
transaction
I executed strace and gdb on each process except for 31109 (the logger process)
Process 31103 attached - interrupt to quit # Main postmaster
select(6, [3 5], NULL, NULL, {15, 795000} unfinished ...
Process 31103 detached
[EMAIL PROTECTED] 64]$ strace -p 32645 # connection #1
Process 32645 attached - interrupt to quit
futex(0x3954d32930, FUTEX_WAIT, 2, NULL unfinished ...
Process 32645 detached
[EMAIL PROTECTED] 64]$ strace -p 1179 # connection #2
Process 1179 attached - interrupt to quit
futex(0x3954d32930, FUTEX_WAIT, 2, NULL unfinished ...
Process 1179 detached
I then used gdb to gather more information:
#
# For main postmaster process
#
[EMAIL PROTECTED] 64]$ gdb 31103
GNU gdb Red Hat Linux (6.3.0.0-1.153.el4_6.2rh)
Copyright 2004 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type show copying to see the conditions.
There is absolutely no warranty for GDB. Type show warranty for details.
This GDB was configured as x86_64-redhat-linux-gnu...31103: No such
file or directory.
[EMAIL PROTECTED] 64]$ gdb /opt/lms/postgres-8.3_64/bin/postmaster 31103
This GDB was configured as x86_64-redhat-linux-gnu...(no debugging
symbols found)
Using host libthread_db library /lib64/tls/libthread_db.so.1.
Attaching to program: /opt/lms/64/postgres/8.3/bin/postmaster, process 31103
Reading symbols from /usr/lib64/libxslt.so.1...(no debugging symbols
found)...done.
Loaded symbols for /usr/lib64/libxslt.so.1
Reading symbols from /usr/lib64/libxml2.so.2...(no debugging symbols
found)...done.
Loaded symbols for /usr/lib64/libxml2.so.2
Reading symbols from /lib64/libcrypt.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libcrypt.so.1
Reading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libdl.so.2
Reading symbols from /lib64/tls/libm.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/tls/libm.so.6
Reading symbols from /lib64/tls/libc.so.6...
(no debugging symbols found)...done.
Loaded symbols for /lib64/tls/libc.so.6
Reading symbols from /usr/lib64/libz.so.1...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libz.so.1
Reading symbols from /lib64/tls/libpthread.so.0...(no debugging
symbols found)...done.
[Thread debugging using libthread_db enabled]
[New Thread 182894088192 (LWP 31103)]
Loaded symbols for /lib64/tls/libpthread.so.0
Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging
symbols found)...done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
Reading symbols from /lib64/libnss_files.so.2...(no debugging symbols
found)...done.
Loaded symbols for /lib64/libnss_files.so.2
0x003954bc0125 in __select_nocancel () from /lib64/tls/libc.so.6
#
# For connection no. 1
#
[EMAIL PROTECTED] 64]$ gdb /opt/lms/postgres-8.3_64/bin/postmaster 32645
This GDB was configured as x86_64-redhat-linux-gnu...(no debugging
symbols found)
Using host libthread_db library /lib64/tls/libthread_db.so.1.
Attaching to program:
Re: [GENERAL] System in Recovery Mode But No Activity
On Fri, Jun 20, 2008 at 7:12 PM, John Cheng [EMAIL PROTECTED] wrote: We had a run away process on our database box that used up all the physical and all the virtual memory (swap). This caused the RedHat Linux oom-killer to kill many processes, including some Postgres ones. Postgres went into a funky state after that time: SNIP I think the fact that a process used up all the available memory (physical and virtual) caused Postgres to go into a weird state. Now it will not respond to kill, or pg_ctl for shutdown. Would the right thing to do be using kill -9 to stop the server? When you say it won't respond to pg_ctl for shutdown, have you tried the three options for the -m switch in order? Are you running a pretty recent pg version? Which one? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] System in Recovery Mode But No Activity
I am running postgresql 8.3, I was not aware of the 3 options (smart, fast, or immediate). So it used the default - fast. The state of the server when I sent this e-mail was that there were two remaining connections/postgres subprocesses. I used kill -9 to stop those two subprocesses. Then postgres was able to stop normally. After that, I restarted postgresql normally and it went into recovery mode for about 30 seconds. After that, it started to behave normally again. On Fri, Jun 20, 2008 at 9:34 PM, Scott Marlowe [EMAIL PROTECTED] wrote: On Fri, Jun 20, 2008 at 7:12 PM, John Cheng [EMAIL PROTECTED] wrote: We had a run away process on our database box that used up all the physical and all the virtual memory (swap). This caused the RedHat Linux oom-killer to kill many processes, including some Postgres ones. Postgres went into a funky state after that time: SNIP I think the fact that a process used up all the available memory (physical and virtual) caused Postgres to go into a weird state. Now it will not respond to kill, or pg_ctl for shutdown. Would the right thing to do be using kill -9 to stop the server? When you say it won't respond to pg_ctl for shutdown, have you tried the three options for the -m switch in order? Are you running a pretty recent pg version? Which one? -- - John L Cheng -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] System in Recovery Mode But No Activity
On Fri, Jun 20, 2008 at 7:56 PM, John Cheng [EMAIL PROTECTED] wrote: I am running postgresql 8.3, I was not aware of the 3 options (smart, fast, or immediate). So it used the default - fast. The state of the server when I sent this e-mail was that there were two remaining connections/postgres subprocesses. I used kill -9 to stop those two subprocesses. Then postgres was able to stop normally. After that, I restarted postgresql normally and it went into recovery mode for about 30 seconds. After that, it started to behave normally again. Yeah, there's some danger in kill -9 to the postgres processes, but I've had to killall -9 -f postgres once or twice in the distant past. The -m immediate is the signal just this side of kill -9, forget the name. You should be ok. Definitely look into what's causing the oom killer to come out and play, and look at turning off overcommit (I think the setting is 2 to turn it off) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
