Re: AW: [HACKERS] Shutdown term
[ Charset ISO-8859-1 unsupported, converting... ] Peter Eisentraut [EMAIL PROTECTED] writes: The connection was terminated. The connection has been terminated. ?? And make the postmaster print out The system is shutting down. before it sends out the SIGTERM's. I like above. Imho it is sufficient if postmaster writes the "The system is shutting down." to the log. Clients get the other message. That is how I interpreted Peter's message also. OK, I phoned Tom and we agreed on this wording: This connection has been terminated by the administrator Comments? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Re: Week number
On Wed, Mar 14, 2001 at 02:50:35PM +, Thomas Lockhart wrote:
First day in week is Monday in ISO week.
Thomas, we have ISO week-of-year (IW in to_char or 'week' in date_part),
but we haven't ISO day-of-week (may be as 'ID' for to_char).
TODO for 7.2?
..but in ISO is 0-6; 0=Mon
I've been ignoring this until now, hoping no one would notice ;)
Unix day-of-week starts on Sunday, not Monday, which is what
date_trunc('dow',...) returns. Presumably this is modeled on the
traditional notion (at least in the US; I suspect this is true in most
European countries at least) of Sunday being "the first day of week".
The implementation predates our support of ISO dates so it was not an
issue then.
date_part() is modeled on Ingres' implementation, but my old Ingres
manual indicates that 'dow' is not one of the options.
Should we change the definition of "dow", or implement another choice,
say "idow"?
Yes, I agree with new "idow" for date_part() and 'ID' for to_char() stuff.
My note grow up when I do SQL query that say something like:
"2001-03-12 is begin of week and it's second day of week" .. this sound
very curious :-)
test=# select to_char('2001-03-12'::date, 'IW Dth Day');
to_char
--
11 2nd Monday
(1 row)
Karel
--
Karel Zak [EMAIL PROTECTED]
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [HACKERS] database died
"Martin A. Marques" [EMAIL PROTECTED] writes: CheckPoint Data Base: fork failed: Not enough space [ whereupon postmaster quits ] Any idea on this? I think the the postmaster shouldn't die, at least it's what I first thought. I agree. Dying if the startup subjob fails is one thing, but dying because a routine checkpoint fails is another. The code is treating those two cases alike however ... will change it. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
AW: [HACKERS] Re: Week number
Unix day-of-week starts on Sunday, not Monday, which is what
date_trunc('dow',...) returns. Presumably this is modeled on the
traditional notion (at least in the US; I suspect this is true in most
European countries at least) of Sunday being "the first day of week".
Germany and Austria have Monday as first day of week, I think most of
Europe also.
Andreas
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Re: AW: Shutdown term
OK, I phoned Tom and we agreed on this wording:
This connection has been terminated by the administrator
Comments?
This connection has been terminated by an administrator
(there may be more than one...) :)
Other than that it's informative enough.
OTOH, I had a small thought on this.
If you had a messaging scheme to print to clients when a signal was
received, is there the possibility of more informative messages perhaps
that could be sent by the pg_ctl program through the postmaster (or
backends) on shutdowns? This would allow for some decent scripting. For
example, the database is shutdown without the system going down or the
whole system is going down for maintenance or scheduled reboot.
It may seem stupid but I was thinking the reason could be an argument to
the pg_ctl program with a default of (Database Shutdown).
pg_ctl stop --message="System going down for a reboot"
or
pg_ctl stop -msg "System upgrade. System will be available again at 5:00am"
The client would receive
The connection has been terminated
[System Shutdown|Database Shutdown|Unknown Reason|"some string as an argument"]
Also, it allows for more informative messages.
Scheduled downtime (System will be online again at {whenever})
Idle Timeout
You are using too much CPU...
You are using too little CPU...
These message can be set by the scripts for "run level" changes and the like.
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[HACKERS] pgmonitor patch for query string
I would like to apply the following patch to the CVS tree. It allows
pgmonitor to show query strings even if the backend is not compiled with
debug symbols.
It does this by creating a global variable 'debug_query_string' and
assigning it when the query begins and clearing it when the query ends.
It needs to be a global symbol so gdb can find it without debug symbols.
Seems like a very safe patch, and it allows pgmonitor to be much more
useful until we get a shared memory solution in 7.2.
Is this OK with everyone?
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Index: src/backend/tcop/postgres.c
===
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.211
diff -c -r1.211 postgres.c
*** src/backend/tcop/postgres.c 2001/03/14 15:14:35 1.211
--- src/backend/tcop/postgres.c 2001/03/14 16:29:26
***
*** 74,79
--- 74,81
extern int optind;
extern char *optarg;
+ char *debug_query_string; /* used by pgmonitor */
+
/*
* for ps display
*/
***
*** 621,626
--- 623,630
List *parsetree_list,
*parsetree_item;
+ debug_query_string = query_string; /* used by pgmonitor */
+
/*
* Start up a transaction command. All queries generated by the
* query_string will be in this same command block, *unless* we find
***
*** 855,860
--- 859,866
*/
if (xact_started)
finish_xact_command();
+
+ debug_query_string = NULL; /* used by pgmonitor */
}
/*
***
*** 1718,1723
--- 1724,1731
if (sigsetjmp(Warn_restart, 1) != 0)
{
+ debug_query_string = NULL; /* used by pgmonitor */
+
/*
* NOTE: if you are tempted to add more code in this if-block,
* consider the probability that it should be in AbortTransaction()
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[HACKERS] Re: AW: Shutdown term
Thomas Swan writes: It may seem stupid but I was thinking the reason could be an argument to the pg_ctl program with a default of (Database Shutdown). pg_ctl stop --message="System going down for a reboot" or pg_ctl stop -msg "System upgrade. System will be available again at 5:00am" I foresee a PQmotd(PGconn *) function ... ;-) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Re: Week number
"Peter" == Peter Eisentraut [EMAIL PROTECTED] writes: Peter The POSIX numbering (0-6) is actually pretty slick because Peter it allows both versions to work: In the U.S. (e.g.) you get Peter a natural order starting at 0, in Germany (e.g.) you get Peter Monday as #1. Oracle's to_char() supports format IW for the ISO week of the year, but there is no equivalent ID for the ISO day of the week. Perhaps this should be a PostgreSQL extension? roland -- PGP Key ID: 66 BC 3B CD Roland B. Roberts, PhD RL Enterprises [EMAIL PROTECTED] 76-15 113th Street, Apt 3B [EMAIL PROTECTED] Forest Hills, NY 11375 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: AW: [HACKERS] Re: Week number
"AZ" == Zeugswetter Andreas SB [EMAIL PROTECTED] writes:
Unix day-of-week starts on Sunday, not Monday, which is what
date_trunc('dow',...) returns. Presumably this is modeled on
the traditional notion (at least in the US; I suspect this is
true in most European countries at least) of Sunday being "the
first day of week".
AZ Germany and Austria have Monday as first day of week, I think
AZ most of Europe also.
I believe the goal was to have a to_char() that was complete and
Oracle-compatible. Perhaps we need to also have a trunc() which is
Oracle compatible. I haven't been playing with 7.1beta, but 7.0
trunc() doesn't like timestamps. In Oracle, I can say
select trunc(sysdate) - trunc(sysdate,'ww') + 1 from dual;
to get Monday=1.
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
[EMAIL PROTECTED] 76-15 113th Street, Apt 3B
[EMAIL PROTECTED] Forest Hills, NY 11375
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [HACKERS] pgmonitor patch for query string
The Hermit Hacker [EMAIL PROTECTED] writes: I'm saying no because it doesn't fix any known bugs, it *adds* another feature ... we are *months* too late in the cycle for that ... I thought it was a pretty good idea even without any consideration for Bruce's monitor program. The advantage is that one would be able to extract the current query from a crashed backend's core dump, even if the backend had been compiled without debug symbols. Right now you can only find out the query if you had compiled with debug, because you have to be able to look at local variables of functions. And there are an awful lot of people who don't use debug-enabled builds... Given that and the low-risk nature of the patch, I vote for it. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] pgmonitor patch for query string
This doesn't tell you whether the query is still running, but ps tells you that. In fact, it might be an idea to add a logging option that prints something like "query finished in xxx ms". We actually have something similar hidden under show_query_stats, but the formatting needs to be made more convenient and possibly less verbose. But at least this way you have it for the record, and not only on the screen. I *definitely* like this one ... I've been doing wrappers around my pg_exec() calls in PHP to do some stats generation to work on "slow queries", but having it in the backend would be more exact ... and easier to use then having to modify your apps ... Added to TODO: * Allow logging of query durations -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Re: Week number
On Wed, Mar 14, 2001 at 04:54:54PM +0100, Zeugswetter Andreas SB wrote:
Unix day-of-week starts on Sunday, not Monday, which is what
date_trunc('dow',...) returns. Presumably this is modeled on the
traditional notion (at least in the US; I suspect this is true in most
European countries at least) of Sunday being "the first day of week".
Germany and Austria have Monday as first day of week, I think most of
Europe also.
it is all relative.
most western calendars that i have seen show "Sun Mon Tue Wed Thu Fri Sat".
the concept of "first" day of week is a bit muddied.
many christian-influenced places would consider Sunday to be the "first"
day of the week, but monday being the "first" business day of the week.
i have seen calendars which use "Mon Tue Wed Thu Fri Sat Sun", and i have
worked with people where saturday was the first day of business. and also
places where sunday is the first day of business.
so, suffice to say, there is no "proper" first day of the week.
as such, the unix day of week pegs sunday as day 0, your code should just
use that index. since almost all cultures have now adapted to a 7 day week
and a 365 day year, there shouldn't bee too much confusion.
--
[ Jim Mercer [EMAIL PROTECTED] ]
[ Reptilian Research -- Longer Life through Colder Blood ]
[ aka[EMAIL PROTECTED] +1 416 410-5633 ]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Re: Week number
Jim Mercer writes: most western calendars that i have seen show "Sun Mon Tue Wed Thu Fri Sat". Most *English* calendars you have seen, I suppose. In Germany there is no such possible calendar. If you printed a calendar that way, it would be considered a printo. The same is true in most parts of the continent. The POSIX numbering (0-6) is actually pretty slick because it allows both versions to work: In the U.S. (e.g.) you get a natural order starting at 0, in Germany (e.g.) you get Monday as #1. so, suffice to say, there is no "proper" first day of the week. There is a proper ISO first day of the week. In many parts of Europe, the day of the week + week of the year are real, official concepts. E.g., you would mark business transactions as "week x, day y" instead of with a date (notice how this simplifies arithmetic). Without trying to push through my cultural bias, I think these applications should have some priority over making up a solution that satisfies everybody but doesn't actually suit any real application. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Re: AW: Shutdown term
At 3/14/2001 11:13 AM, Peter Eisentraut wrote: Thomas Swan writes: It may seem stupid but I was thinking the reason could be an argument to the pg_ctl program with a default of (Database Shutdown). pg_ctl stop --message="System going down for a reboot" or pg_ctl stop -msg "System upgrade. System will be available again at 5:00am" I foresee a PQmotd(PGconn *) function ... ;-) Well, I also thought you could use the same method to do a warning. pg_ctl --message="Database going offline in 5 minutes" or something along those lines... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: Week number
traditional notion (at least in the US; I suspect this is true in most European countries at least) of Sunday being "the first day of week". I believe that in most European countries, Monday is the first day of the week. -- Kaare Rasmussen--Linux, spil,--Tlf:3816 2582 Kaki Datatshirts, merchandize Fax:3816 2501 Howitzvej 75 ben 14.00-18.00Email: [EMAIL PROTECTED] 2000 FrederiksbergLrdag 11.00-17.00 Web: www.suse.dk ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Re: database died
"Martin A. Marques" [EMAIL PROTECTED] writes: I agree. Dying if the startup subjob fails is one thing, but dying because a routine checkpoint fails is another. The code is treating those two cases alike however ... will change it. Just happend again. At this moment the postgres on that machine is not in production, but should be in a short future. Is there a chance of getting some kind of patch, or maybe changing some configuratin parameters of the OS or the postmaster? The fix is in CVS, pull it out if you need it: http://www.postgresql.org/cgi/cvsweb.cgi/pgsql/src/backend/postmaster/postmaster.c regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Re: Small bug in pg_dump
Philip Warner [EMAIL PROTECTED] writes: Fixed done... Only part of the way there: pg_dump is still pretty seriously broken for mixed-case table names. Observe: regression=# create table "Foo" (z int); CREATE regression=# \q $ pg_dump -a -t '"Foo"' regression -- -- Selected TOC Entries: -- -- -- Data for TOC Entry ID 1 (OID 1845087) TABLE DATA "Foo" -- \connect - postgres -- Disable triggers UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = '"Foo"'; COPY "Foo" FROM stdin; \. -- Enable triggers UPDATE pg_class SET reltriggers = (SELECT count(*) FROM pg_trigger where pg_class.oid = tgrelid) WHERE relname = '"Foo"'; $ These UPDATEs will certainly not work. On digging into the code, the problem seems to be one of premature optimization: fmtId() is applied to the table name when the ArchiveEntry is created, rather than when printing out from an ArchiveEntry. So there is no way to get the undecorated table name for use in these commands. It seems to me that you should remove the fmtId() from calls to ArchiveEntry. Then add it back where an archive entry's name is being printed (and quoting is appropriate). It might even make sense for an ArchiveEntry to store both forms of the name, and then using code could just select the form wanted instead of calling fmtId repeatedly. Not sure. BTW, making the -t switch compare to the unquoted name would probably also fix the bizarre need for '"Foo"' exhibited above. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] rtrim giving weird result
I'm running Postgres 7.0.3 on a RedHat Linux 6.1. For some reason, rtrim is giving me an incorrect result: db01=# SELECT tablename FROM pg_tables WHERE tablename LIKE '%_opto' AND tablename NOT LIKE 'pg%' ORDER BY tablename ASC ; tablename - center_out_opto circles_opto ellipse_opto ex_ellipse_opto figure8_opto ro_ellipse_opto (6 rows) Now I want to return the same thing only with the trailing '_opto' removed: db01=# SELECT rtrim(tablename, '_opto') FROM pg_tables WHERE tablename LIKE '%_opto' AND tablename NOT LIKE 'pg%' ORDER BY tablename ASC ; rtrim center_ou === NOTE: the trailing 't' is missing circles ellipse ex_ellipse figure8 ro_ellipse (6 rows) However, as you can see, the 'center_out' table is missing the last 't'. If I exclude the '_': db01=# SELECT rtrim(tablename, 'opto') FROM pg_tables WHERE tablename LIKE '%_opto' AND tablename NOT LIKE 'pg%' ORDER BY tablename ASC ; rtrim - center_out_ === 't' shows up again circles_ ellipse_ ex_ellipse_ figure8_ ro_ellipse_ (6 rows) The 't' is back. Is there something that I'm doing wrong with my query here? Thanks. -Tony ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] rtrim giving weird result
"G. Anthony Reina" [EMAIL PROTECTED] writes: I'm running Postgres 7.0.3 on a RedHat Linux 6.1. For some reason, rtrim is giving me an incorrect result: No, you have an incorrect understanding of rtrim. The second argument is a set of removable characters, not a string to be matched. AFAIK we are following Oracle in defining it that way ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Union on view and..
Hi, all, Could somebody tell me if there is a work around to create "union on view" (which seems not implemented in the postgres yet) ? Also, is there any alternative query that can do: select * from (select * from table); I could not find an answer from the old archieve, and sorry if this has been answered previously. (I am new here :) Regards, Jae ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Union on view and..
If you're willing to wait or use the betas, 7.1 should probably do both of these. (Won't quite make toast though). [Although I believe the second'll be something like: select * from (select * from table) alias;] On Wed, 14 Mar 2001, Jae-Woong Hwnag wrote: Hi, all, Could somebody tell me if there is a work around to create "union on view" (which seems not implemented in the postgres yet) ? Also, is there any alternative query that can do: select * from (select * from table); I could not find an answer from the old archieve, and sorry if this has been answered previously. (I am new here :) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] rtrim giving weird result
The second parameter to "rtrim" is interpreted as a set of characters and
rtrim:
"Returns string with final characters removed after the last character not
in set"
So rtrim("center_out_opto", "_opto") returns
"center_ou"
because "u" is not in the set {o, p, t, _} but all the characters after it
are.
rtrim("center_out_opto", "pot_") will produce the same thing.
- Original Message -
From: "G. Anthony Reina" [EMAIL PROTECTED]
To: "[EMAIL PROTECTED]" [EMAIL PROTECTED]
Sent: Wednesday, March 14, 2001 9:14 PM
Subject: [HACKERS] rtrim giving weird result
I'm running Postgres 7.0.3 on a RedHat Linux 6.1. For some reason, rtrim
is giving me an incorrect result:
db01=# SELECT tablename FROM pg_tables WHERE tablename LIKE '%_opto' AND
tablename NOT LIKE 'pg%' ORDER BY tablename ASC ;
tablename
-
center_out_opto
circles_opto
ellipse_opto
ex_ellipse_opto
figure8_opto
ro_ellipse_opto
(6 rows)
Now I want to return the same thing only with the trailing '_opto'
removed:
db01=# SELECT rtrim(tablename, '_opto') FROM pg_tables WHERE tablename
LIKE '%_opto' AND tablename NOT LIKE 'pg%' ORDER BY tablename ASC ;
rtrim
center_ou ===
NOTE: the trailing 't' is missing
circles
ellipse
ex_ellipse
figure8
ro_ellipse
(6 rows)
However, as you can see, the 'center_out' table is missing the last 't'.
If I exclude the '_':
db01=# SELECT rtrim(tablename, 'opto') FROM pg_tables WHERE tablename
LIKE '%_opto' AND tablename NOT LIKE 'pg%' ORDER BY tablename ASC ;
rtrim
-
center_out_
=== 't' shows up again
circles_
ellipse_
ex_ellipse_
figure8_
ro_ellipse_
(6 rows)
The 't' is back.
Is there something that I'm doing wrong with my query here?
Thanks.
-Tony
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
