Thread: How to convert rows into HTML columns?

Hi there,

I feel like a brain-washed person being slowly re-socialized. After
changing from an Excel-like database design to a "central table" one
(see here: http://archives.postgresql.org/pgsql-general/2007-10/
msg00132.php ) I am struggling with multiple problems...

One is now the HTML table display of a simple SELECT :

SELECT
        d.year,
        d.value,
        c.name AS country_name
FROM
        public_one_table.data AS d
LEFT JOIN
        countries AS c ON c.id = id_country
WHERE
        id_variable = 1644 AND
        (year = '2000' OR year = '1999' OR year = '1998')
ORDER BY
        country_name ASC

Before I was used that the yearly values were all to be found in a
single SQL row; now for each year of each country I have a separate
row. How do I convert that into a single (HTML) row again?

And the next question coming up is: How should my query look like so
that I can sort the (HTML) table by a specific year in ascending or
descending order? So, that it doesn't display it by the country names
alphabetical order, but by, say 1998?

Thanks for any help!

Stef

am  Fri, dem 05.10.2007, um  8:20:32 +0200 mailte Stefan Schwarzer folgendes:
> Before I was used that the yearly values were all to be found in a
> single SQL row; now for each year of each country I have a separate
> row. How do I convert that into a single (HTML) row again?

You can use conditionals like in my simple example:

test=*# select * from vals;
 year |  val
------+--------
 1998 |  10.00
 1998 |  20.00
 1998 |  25.00
 1999 |  30.00
 1999 |  39.00
 2000 | 100.00
 2000 |  99.00
 2000 |  98.00
(8 rows)

test=*# select
  sum(case when year=1998 then val else 0 end) as "1998",
  sum(case when year=1999 then val else 0 end) as "1999",
  sum(case when year=2000 then val else 0 end) as "2000"
  from vals;
 1998  | 1999  |  2000
-------+-------+--------
 55.00 | 69.00 | 297.00
(1 row)


>
> And the next question coming up is: How should my query look like so
> that I can sort the (HTML) table by a specific year in ascending or
> descending order? So, that it doesn't display it by the country names
> alphabetical order, but by, say 1998?

If you have only one row, how would you sort this result? ;-)


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

>>
>> And the next question coming up is: How should my query look like so
>> that I can sort the (HTML) table by a specific year in ascending or
>> descending order? So, that it doesn't display it by the country names
>> alphabetical order, but by, say 1998?
>
> If you have only one row, how would you sort this result? ;-)

With the "old" design I had up to 240 rows - for each country/
territory one row. Now, I have 240*num_years rows.

The user should be able to sort then the resulting table by country
name as well as by any year in ASC or DESC order.

am  Fri, dem 05.10.2007, um  9:13:10 +0200 mailte Stefan Schwarzer folgendes:
> >>
> >>And the next question coming up is: How should my query look like so
> >>that I can sort the (HTML) table by a specific year in ascending or
> >>descending order? So, that it doesn't display it by the country names
> >>alphabetical order, but by, say 1998?
> >
> >If you have only one row, how would you sort this result? ;-)
>
> With the "old" design I had up to 240 rows - for each country/
> territory one row. Now, I have 240*num_years rows.


Maybe i don't understand you.
I extend the table:

test=# select * from vals ;
 year |  val   | country
------+--------+---------
 1998 |  10.00 | a
 1999 |  30.00 | a
 2000 | 100.00 | a
 1998 |  20.00 | b
 1999 |  39.00 | b
 2000 |  99.00 | b
 1998 |  25.00 | c
 2000 |  98.00 | c
(8 rows)


Now, select for every country and every year all in one row (in the
example only for 1998 and country a and b, i'm to lazy):

test=*# select
  sum(case when year=1998 and country='a' then val else 0 end) as "1998_a",
  sum(case when year=1998 and country='b' then val else 0 end) as "1998_b",
  sum(case when year=1999 then val else 0 end) as "1999",
  sum(case when year=2000 then val else 0 end) as "2000"
  from vals;
 1998_a | 1998_b | 1999  |  2000
--------+--------+-------+--------
  10.00 |  20.00 | 69.00 | 297.00
(1 row)


you can see: sort by year and country, and all in one line ;-)

Or, one row for every country, ordered by the country's name:

test=*# select country,
  sum(case when year=1998 then val else 0 end) as "1998",
  sum(case when year=1999 then val else 0 end) as "1999",
  sum(case when year=2000 then val else 0 end) as "2000"
  from vals
  group by country
  order by country;
 country | 1998  | 1999  |  2000
---------+-------+-------+--------
 a       | 10.00 | 30.00 | 100.00
 b       | 20.00 | 39.00 |  99.00
 c       | 25.00 |     0 |  98.00
(3 rows)



Btw.: i'm subscribed on the list, please no extra CC to me.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

On Oct 5, 2007, at 1:20 , Stefan Schwarzer wrote:

> Before I was used that the yearly values were all to be found in a
> single SQL row; now for each year of each country I have a separate
> row. How do I convert that into a single (HTML) row again?

Take a look at the crosstab functions in contrib/tablefunc.

> And the next question coming up is: How should my query look like
> so that I can sort the (HTML) table by a specific year in ascending
> or descending order? So, that it doesn't display it by the country
> names alphabetical order, but by, say 1998?

Same way you would otherwise -- ORDER BY -- after using crosstab.

Michael Glaesemann
grzm seespotcode net

Quick request to the group: we have several members who include bogus or
"humorous" X-Message-Flag headers in their email messages.  Could I
request that you _please_ turn them off?  Because they come through as
flagged messages in Outlook,  it throws off my email rules processing
and the messages end up into the wrong groups.  (With the volume of
email these days, I definitely need all the assistance I can get from
things like rules processing to attempt to stay on top of it.)

(Yes, I understand people's personal preferences for not liking Windows
or Outlook or Microsoft, but that isn't going to change the applications
that I need to use for my day-to-day work.  Feel free to continue
posting your feelings in email signatures though -- never can tell if it
will indeed influence a change ...)

Much appreciated!

- Bill

On Oct 5, 2007, at 9:05 , Bill Bartlett wrote:

> Quick request to the group: we have several members who include
> bogus or
> "humorous" X-Message-Flag headers in their email messages.  Could I
> request that you _please_ turn them off?

In all practicality, a request like this is futile:
* Given the number of people on the list, you'll never get everybody
to remove "bogus"
X-Message-Flag headers
* Given it's an X- header, doesn't that mean the meaning of the value
is implementation dependent? What's "bogus" wrt Outlook may not be
wrt another mail system or client
* Doesn't this indicate that Outlook is broken (for some values of
broken)?

>   Because they come through as
> flagged messages in Outlook,  it throws off my email rules processing
> and the messages end up into the wrong groups.  (With the volume of
> email these days, I definitely need all the assistance I can get from
> things like rules processing to attempt to stay on top of it.)

I sympathize. there *is* a lot of email traffic these days (and not
just from the lists). But rather than request that others bend to
your rules, I'd think a better solution would be to find (or develop)
tools that do what you want. Whether that means better rule handling
or better understanding of various headers, it sounds like Outlook
isn't doing the job for you. Perhaps a hybrid approach would be
helpful: use another email client for mailing lists and Outlook
otherwise.

Hope this gives you some ideas.

Michael Glaesemann
grzm seespotcode net

On 10/5/07, Michael Glaesemann <grzm@seespotcode.net> wrote:
>
> On Oct 5, 2007, at 9:05 , Bill Bartlett wrote:

> * Given it's an X- header, doesn't that mean the meaning of the value
> is implementation dependent? What's "bogus" wrt Outlook may not be
> wrt another mail system or client
> * Doesn't this indicate that Outlook is broken (for some values of
> broken)?

Ummm.  Given that some may contain the group all, I guess that's
technically correct. :)

It's not the only area in which Lookout! make it obvious it's broken.

am  Fri, dem 05.10.2007, um 10:05:32 -0400 mailte Bill Bartlett folgendes:
> Quick request to the group: we have several members who include bogus or
> "humorous" X-Message-Flag headers in their email messages.  Could I
> request that you _please_ turn them off?  Because they come through as

Do you mean me?


> flagged messages in Outlook,  it throws off my email rules processing
> and the messages end up into the wrong groups.  (With the volume of

This header is a special Outlook-Feature. If this header realy make
problems in _this_ software, then i think, _this_ software is broken.
But hey, tell news ;-)

And yes: there are any email-software available, without problems.
Including Windows.


> (Yes, I understand people's personal preferences for not liking Windows
> or Outlook or Microsoft, but that isn't going to change the applications
> that I need to use for my day-to-day work.  Feel free to continue

Okay, i like this list and i like the people here. I will try to disable
this special header line for this and only this list (okay, for all
postgresql-lists). Let me try, i'm not sure how to disable this
header-line only for *@postgresql.org.

Please, don't beat me if this header are still in this message, the
problem is work in progress... i'm not sure if i change the config
properly.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

> -----Original Message-----
> From: Michael Glaesemann [mailto:grzm@seespotcode.net]
> Sent: Friday, October 05, 2007 10:45 AM
> To: Bill Bartlett
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Request: Anyone using bogus /
> "humorous" X-Message-Flag headers, could we please turn them off
>
>
>
> On Oct 5, 2007, at 9:05 , Bill Bartlett wrote:
>
> > Quick request to the group: we have several members who include
> > bogus or
> > "humorous" X-Message-Flag headers in their email messages.  Could I
> > request that you _please_ turn them off?
>
> In all practicality, a request like this is futile:
> * Given the number of people on the list, you'll never get everybody
> to remove "bogus"
> X-Message-Flag headers
> * Given it's an X- header, doesn't that mean the meaning of
> the value
> is implementation dependent? What's "bogus" wrt Outlook may not be
> wrt another mail system or client
> * Doesn't this indicate that Outlook is broken (for some values of
> broken)?

Actually, no -- this is why I listed a specific X- header (
X-Message-Flag ) rather than simply saying "Hey, would everyone please
turn off their X-headers".  This specific X- header is designed to have
Outlook "flag" a message and display an extra line of text with the flag
comment above the email.  Since this is generally only used to flag
messages for followup actions, having messages come across with the
header already embedded in them simply serves to add an extra
distraction to the already-too-many bits into which I need to slice my
time.  (Not that I want more time for things like my having to spend 3
hrs yesterday regenerating sequences after diagnosing a database crash
[bad disk controller on an old SuSE box] and restoring from backup, but
that's my real world...)

> >   Because they come through as
> > flagged messages in Outlook,  it throws off my email rules
> processing
> > and the messages end up into the wrong groups.  (With the volume of
> > email these days, I definitely need all the assistance I
> can get from
> > things like rules processing to attempt to stay on top of it.)
>
> I sympathize. there *is* a lot of email traffic these days (and not
> just from the lists). But rather than request that others bend to
> your rules, I'd think a better solution would be to find (or
> develop)
> tools that do what you want. Whether that means better rule handling
> or better understanding of various headers, it sounds like Outlook
> isn't doing the job for you. Perhaps a hybrid approach would be
> helpful: use another email client for mailing lists and Outlook
> otherwise.

See above -- the problem isn't with Outlook at a mail client. Outlook is
doing exactly what it's supposed to do when it sees this X- header:
highlighting it and flagging the message for special handling.  The
issue is with the headers being used (or misused) as they are.  Believe
me, I'm not defending Outlook; however, that's what I (and many other
people) use -- it's just a tool to get a job done.

(For all you non-Outlook people out there, since you aren't seeing the
messages anyway, most of them are generally humorous messages like '
"Windows" is not the answer. "Windows" is the question and the answer is
"no"! ' or ' Really, I'm not out to destroy Microsoft. That will just be
a completely unintentional side effect. (Linus Torvalds) ' Yes, they
were funny 2 years ago when I first saw them, but now it's gotten a bit
old -- sorry.  [No, I'm not meaning to single out Andreas -- his just
happened to be the first two that I saw in my "listserv items of useful
Postgresql stuff to keep for reference" folder.])

>
> Hope this gives you some ideas.
>
> Michael Glaesemann
> grzm seespotcode net
>
>

On Oct 5, 2007, at 10:56 , Bill Bartlett wrote:

>> * Given it's an X- header, doesn't that mean the meaning of
>> the value
>> is implementation dependent? What's "bogus" wrt Outlook may not be
>> wrt another mail system or client
>> * Doesn't this indicate that Outlook is broken (for some values of
>> broken)?
>
> Actually, no -- this is why I listed a specific X- header (
> X-Message-Flag ) rather than simply saying "Hey, would everyone please
> turn off their X-headers".  This specific X- header is designed to
> have
> Outlook "flag" a message and display an extra line of text with the
> flag
> comment above the email.

If it were a specific *Outlook* header, shouldn't it be namespaced?
e.g., X-Outlook-Message-Flag? Might not other email clients operate
differently when they encounter the X-Message-Flag? For example, in
your message, I see an X-MSMail-Priority flag, which I assume is a
Microsoft-specific X-header. (I may very well be interpreting this
wrong: brief googling didn't turn up anything, and I'm not an email
guru.)

Anyway, that's all I have to say on the topic: I suggested
alternatives, and as it's not a problem for me — and I don't believe
my emails include X-Message-Flag headers so I'm not contributing to
your problem — hope you find a solution that works for you.

Michael Glaesemann
grzm seespotcode net

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of A. Kretschmer
> Sent: Friday, October 05, 2007 10:57 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Request: Anyone using bogus /
> "humorous" X-Message-Flag headers, could we please turn them off
>
>
> am  Fri, dem 05.10.2007, um 10:05:32 -0400 mailte Bill
> Bartlett folgendes:
> > Quick request to the group: we have several members who
> include bogus
> > or "humorous" X-Message-Flag headers in their email
> messages.  Could I
> > request that you _please_ turn them off?  Because they come
> through as
>
> Do you mean me?

Not specifically -- yours just happened to be the one that triggered it,
but no, I see this in postings from other people as well.  (Didn't mean
to single you out though.)

> > flagged messages in Outlook,  it throws off my email rules
> processing
> > and the messages end up into the wrong groups.  (With the volume of
>
> This header is a special Outlook-Feature. If this header
> realy make problems in _this_ software, then i think, _this_
> software is broken. But hey, tell news ;-)

See other reply -- I'm not complaining that Outlook is broken but rather
that the misuse of this flag slows down my workflow.  The issue is that
because this flag is useful when used properly (e.g.: for mail rules
like "take all messages flagged for followup and move them to specific
other folders for different types of followup"), having the flag be
added to emails just for the sake of putting in a "humorous" message
just gets in the way.  (I keep needing to go fish the
incorrectly-flagged messages back out of the various followup folders
before I can completely follow the threads in the Postgres listservs.)

> And yes: there are any email-software available, without
> problems. Including Windows.

Hmm ... I still use "Pine" on some of my really old Linux boxes -- does
this count?  (Useful for reading CRON output...)

> > (Yes, I understand people's personal preferences for not liking
> > Windows or Outlook or Microsoft, but that isn't going to change the
> > applications that I need to use for my day-to-day work.
> Feel free to
> > continue
>
> Okay, i like this list and i like the people here. I will try
> to disable this special header line for this and only this
> list (okay, for all postgresql-lists). Let me try, i'm not
> sure how to disable this header-line only for *@postgresql.org.
>
> Please, don't beat me if this header are still in this
> message, the problem is work in progress... i'm not sure if i
> change the config properly.

Yes, it's working properly now.  MUCH thanks!

> 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 3: Have you checked our extensive FAQ?
>
               http://www.postgresql.org/docs/faq

On 10/5/07, Bill Bartlett <bbartlett@softwareanalytics.com> wrote:
>
> Hmm ... I still use "Pine" on some of my really old Linux boxes -- does
> this count?  (Useful for reading CRON output...)

If you need / want the familiar interface of pine on a modern linux
box, look for cone.

Note that there's also an open source implementation of pico called nano.

Just FYI.  I use both, quite a bit.

> -----Original Message-----
> From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
> Sent: Friday, October 05, 2007 12:30 PM
> To: Bill Bartlett
> Cc: A. Kretschmer; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Request: Anyone using bogus /
> "humorous" X-Message-Flag headers, could we please turn them off
>
>
> On 10/5/07, Bill Bartlett <bbartlett@softwareanalytics.com> wrote:
> >
> > Hmm ... I still use "Pine" on some of my really old Linux boxes --
> > does this count?  (Useful for reading CRON output...)
>
> If you need / want the familiar interface of pine on a modern
> linux box, look for cone.
>
> Note that there's also an open source implementation of pico
> called nano.
>
> Just FYI.  I use both, quite a bit.
>

I just starting using nano, although I still tend to use vi a lot more
than I should.  (Never got into the whole emacs thing tho.)

I hadn't heard of cone, so I'll have to take a look for that.  Thanks
for the tip!  (Long live the console!)

- Bill

Bill Bartlett <bbartlett@softwareanalytics.com> schrieb:

>
>
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of A. Kretschmer
> > Sent: Friday, October 05, 2007 10:57 AM
> > To: pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] Request: Anyone using bogus /
> > "humorous" X-Message-Flag headers, could we please turn them off
> >
> >
> > am  Fri, dem 05.10.2007, um 10:05:32 -0400 mailte Bill
> > Bartlett folgendes:
> > > Quick request to the group: we have several members who
> > include bogus
> > > or "humorous" X-Message-Flag headers in their email
> > messages.  Could I
> > > request that you _please_ turn them off?  Because they come
> > through as

Outlook is a PITA. As you can see, it breaks the lines in quotings. Hard
to read, because any mail readers can colored different quoting planes.
Disgusting.

> See other reply -- I'm not complaining that Outlook is broken but rather
> that the misuse of this flag slows down my workflow.  The issue is that

I think, you have any other problems with this crappy software. For
instance, Outlook can't group messages by Message-IDs (Treading). For
mailing-lists IMHO a no-go.


> > And yes: there are any email-software available, without
> > problems. Including Windows.
>
> Hmm ... I still use "Pine" on some of my really old Linux boxes -- does
> this count?  (Useful for reading CRON output...)

Pine isn't a solution, mutt is a solution for instance. KMail is a
solution, and Thunderbird, both for Linux and Windows.
(and any other software, i'm a mutt-user)


> > Please, don't beat me if this header are still in this
> > message, the problem is work in progress... i'm not sure if i
> > change the config properly.
>
> Yes, it's working properly now.  MUCH thanks!

No problem, but i'm not sure, if this the right way. YOU can't force all
other people to omit things that make problems in your crappy software.
And: YOU renounce for nice feature in modern software...


(sorry, english isn't my native language and i know my english is bad)


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of
> Andreas Kretschmer
> Sent: Friday, October 05, 2007 12:55 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Request: Anyone using bogus /
> "humorous" X-Message-Flag headers, could we please turn them off
>
>
> Bill Bartlett <bbartlett@softwareanalytics.com> schrieb:
>
> >
> >
> > > -----Original Message-----
> > > From: pgsql-general-owner@postgresql.org
> > > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of
> A. Kretschmer
> > > Sent: Friday, October 05, 2007 10:57 AM
> > > To: pgsql-general@postgresql.org
> > > Subject: Re: [GENERAL] Request: Anyone using bogus /
> > > "humorous" X-Message-Flag headers, could we please turn them off
> > >
> > >
> > > am  Fri, dem 05.10.2007, um 10:05:32 -0400 mailte Bill
> > > Bartlett folgendes:
> > > > Quick request to the group: we have several members who
> > > include bogus
> > > > or "humorous" X-Message-Flag headers in their email
> > > messages.  Could I
> > > > request that you _please_ turn them off?  Because they come
> > > through as
>
> Outlook is a PITA. As you can see, it breaks the lines in
> quotings. Hard
> to read, because any mail readers can colored different
> quoting planes.
> Disgusting.

Agreed - never said it wasn't.  But putting time into changing to
something else is time that I just won't have for the foreseeable
future.

> > See other reply -- I'm not complaining that Outlook is
> broken but rather
> > that the misuse of this flag slows down my workflow.  The
> issue is that
>
> I think, you have any other problems with this crappy software. For
> instance, Outlook can't group messages by Message-IDs (Treading). For
> mailing-lists IMHO a no-go.

Agreed - but I'm not debating the merits of Outlook.  It's a tool that
does a job, and for all its faults, for what I need right now and for
the very limited time I have avaiable, it does the job.

> > > And yes: there are any email-software available, without
> > > problems. Including Windows.
> >
> > Hmm ... I still use "Pine" on some of my really old Linux
> boxes -- does
> > this count?  (Useful for reading CRON output...)
>
> Pine isn't a solution, mutt is a solution for instance. KMail is a
> solution, and Thunderbird, both for Linux and Windows.
> (and any other software, i'm a mutt-user)

Again, for what I need and how I need to use it, pine is a perfectly
good solution that works (although in looking at cone, that definitely
looks like a much better solution).  For the specific cases where I use
pine, neither KMail nor Thunderbird is a workable solution at all, since
I need to use a console-mode mail reader running on remote servers being
accessed via SSH and without being able to forward X-Windows so anything
graphical is out.  (I always need to match an appropriate solution to
the problem, and part of that is realizing that one single solution does
not always fit every problem.)

> > > Please, don't beat me if this header are still in this
> > > message, the problem is work in progress... i'm not sure if i
> > > change the config properly.
> >
> > Yes, it's working properly now.  MUCH thanks!
>
> No problem, but i'm not sure, if this the right way. YOU
> can't force all
> other people to omit things that make problems in your crappy
> software.
> And: YOU renounce for nice feature in modern software...

Never mind -- it's not that big a deal, certainly not as much as
everyone has turned it into.  I assumed that this would be a simple
request about a simple problem, but I didn't realize I'd have to spend
so much time trying to justify to so many people on the listserv the
tools I have to use to do my job.  (btw: The heading is back on in this
reply, so there must be some other setting somewhere in your mail
program that needs to be tweaked.  But don't worry about trying to
change anything else -- my effort involved in correcting the misdirected
emails is probably less than your effort in trying to configure
different headers for different groups, so I wouldn't want you to waste
any more time on it.)

Anyway - to those people still slogging through this thread who haven't
moved on to something more productive: no more emails on this please;
I'm done with this subject.  (Makes me have to think twice about raising
any _real_ issues though, like why my VACUUMs periodically keep getting
into lock contentions with my JDBC connections and ultimately causing me
to have to shut down Postgres w/ "pg_ctl stop -m immediate", or how to
properly handle postmaster errors that don't appear to be documented
anywhere except in the source code...  [No, it's not the absolutely most
recent version of Postgres; No, I can't upgrade it.])

>
> (sorry, english isn't my native language and i know my english is bad)
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a
> completely
> unintentional side effect.
> (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly."
>   (unknow)
> Kaufbach, Saxony, Germany, Europe.              N 51.05082°,
> E 13.56889°
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
               http://www.postgresql.org/docs/faq

> (Makes me have to think twice about raising
> any _real_ issues though, like why my VACUUMs periodically keep
> getting
> into lock contentions with my JDBC connections and ultimately
> causing me
> to have to shut down Postgres w/ "pg_ctl stop -m immediate", or how to
> properly handle postmaster errors that don't appear to be documented
> anywhere except in the source code...  [No, it's not the absolutely
> most
> recent version of Postgres; No, I can't upgrade it.])

This isn't fair, Bill. Your original question concerned posts your
email client has trouble processing, which isn't the primary topic of
the list. You also knew that it was somewhat contentious, given that
you've made comments such as "Believe me, I'm not defending Outlook",
so you probably weren't overly surprised at some of the responses you
got. Also note that the responses you got were attempts to solve your
problem: I don't see any that only belittled your choice of software.

If you *are* having issues with PostgreSQL, it would behoove you to
at least post them and see what kind of response you get, rather than
judge the list as a whole due to the response you got to an off-topic
post. Many of the people on the lists have been here for years and
have gotten lots of helpful advice, which is why they've stuck
around, and are many others that are happy to share their advice and
experience. You never know: you might be pleasantly surprised.

Best,

Michael Glaesemann
grzm seespotcode net

> -----Original Message-----
> From: Michael Glaesemann [mailto:grzm@seespotcode.net]
> Sent: Friday, October 05, 2007 3:25 PM
> To: Bill Bartlett
> Cc: 'Andreas Kretschmer'; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Request: Anyone using bogus /
> "humorous" X-Message-Flag headers, could we please turn them off
>
>
> > (Makes me have to think twice about raising
> > any _real_ issues though, like why my VACUUMs periodically keep
getting
> > into lock contentions with my JDBC connections and ultimately
causing me
> > to have to shut down Postgres w/ "pg_ctl stop -m immediate", or how
to
> > properly handle postmaster errors that don't appear to be documented
> > anywhere except in the source code...  [No, it's not the absolutely

> > most recent version of Postgres; No, I can't upgrade it.])
>
> This isn't fair, Bill. Your original question concerned posts your
> email client has trouble processing, which isn't the primary
> topic of the list. You also knew that it was somewhat contentious,
given that
> you've made comments such as "Believe me, I'm not defending
> Outlook", so you probably weren't overly surprised at some of the
> responses you got. Also note that the responses you got were attempts
to
> solve your problem: I don't see any that only belittled your choice of
software.
>

Most people didn't completely read my email and thus unfortunately
completely missed the point, in many cases seemingly because they were
too quick to jump on my use of Outlook as an email client (thus assuming
I was just one of "those" terrible horrible know-nothing Windows users).
It's obvious from 3 years of reading these forums that there is a "Linux
Postgres users good, Windows Postgres users bad" bias by many (but not
all) of the members rather than them realizing that " 'we' are all
Postgres users, 'those' other horrible people are MySQL / Oracle / etc
users".  I forgot that by posting about a header flag which could be
traced back to a Microsoft product, I'd be stepping right smack into
that muck.

Rereading my original email, you can see that the problem I was trying
to address was not my choice of email software but rather that several
people in these Postgres listservs (like other people in other
listservs) were intentionally misusing a specific header flag that is
used by specific email programs (only the various "Outlook"-related
ones, as far as I've been able to determine) to highlight messages for
special handling.  Granted that they were using it for "joking"
purposes, but after a while, all jokes get old.  (After spending 4 days
in a row dealing with significant Postgres system crashes on several
different servers, "old" just happened to rub me the wrong way whereas I
normally just grit my teeth and ignore it.)  After researching this flag
further (after seeing the feedback I got on this forum), I've discovered
that this type of misuse is frequently used, and even recommended on
many Linux-oriented web sites as a means, to annoy Outlook-based users
(as a means to annoy Windows users).  As I mentioned above, I think in a
forum such as this, where "we" need to all be Postgres users, I don't
think it's appropriate to intentionally annoy any of our group.  (That
may not have been the intent, but after seeing the many Linux-oriented
web sites and forums recommending its use for specifically this purpose,
now it does annoy me even more.)

(Just for the record, not that I should have to justify my background
and biases [or hopefully lack thereof] to the group: I gave up fighting
platform wars a LOOONG time ago, back when I used to try to get the
corporate world to bring in Macs instead of Windows 3.x.  Now I
generally use the best tool for the job, or the tools I have to use when
that's not an option.  Insofar as systems and OS's, I am currently
handling 140+ servers running a pretty much even split between Win2K3
and various versions of Linux (primarily several SuSE 9.x versions, a
few Red Hats and at least one Debian), with VMware instances of 2 SuSE
"servers" running inside this specific XP development desktop, managing
pretty much everything remotely via SSH and bash (via Cygwin on the
Windows servers).  I may be using Windows on my desktop, but I don't
think I'd put myself into the category of being merely one of "those
terrible horrible know-nothing Windows users".)

>
> If you *are* having issues with PostgreSQL, it would behoove you to
> at least post them and see what kind of response you get,
> rather than judge the list as a whole due to the response you got to
an
> off-topic post. Many of the people on the lists have been here for
years and
> have gotten lots of helpful advice, which is why they've stuck
> around, and are many others that are happy to share their advice and
> experience. You never know: you might be pleasantly surprised.

I agree that the majority of the responses that I've seen over the last
3 years of reading several of the Postgres listservs were indeed
attempts to help (including several replies that I've posted to people's
problems).  However, I've also seen too many times where instead of the
reply being an attempt to help the person solve their problem given
their situation, the responses instead were of the "you're a fool for
using that version / that OS / that database schema / that program
design / etc" variety.  (Notice that all the responses that I got other
than Andreas's offer to turn off his use of that header flag were
"change your email program", even though my email program is working
exactly as it is supposed to.  Helpful if the program was the issue, but
instead the replies missed the point.)

I think this leads to people not asking questions because they don't
want to deal with having to justify _why_ they are having the problem or
_why_ they are still using the Postgres version or OS version or tools
that they are using or _why_ their program is updating data that way.
(I have one coworker in exactly this situation.  He used to be a regular
reader of several Postgres listservs and an occasional poster, but after
receiving too many responses of the "change your program because CLEARLY
it's wrong" variety, where the person posting the response had no idea
of why the program was written that way or didn't care that it needed to
operate that way to fulfill a business need, he stopped reading all
Postgres listservs.  He no longer posts requests for assistance because
he thinks its more aggravating than it's worth, but he now also just
assumes that when Postgres breaks, it's just because the database
doesn't work properly.)

To be fair to these listservs though, they are MUCH better than many in
terms of providing support, and I can't remember the last time I saw a
flame war erupt.  (But I'm still not going to post the problem I've been
hitting recently running Postgres 7.4.1 on a SuSE 9.0 box -- somehow I
think the first few responses might be "get OFF that version".
[Hopefully will upgrade very soon, but can't just yet on that specific
machine.  Meanwhile it's a production server so I'll just have to deal
with it...])

>
> Best,
>
> Michael Glaesemann
> grzm seespotcode net
>
>

On 10/8/07, Bill Bartlett <bbartlett@softwareanalytics.com> wrote:
>
> Most people didn't completely read my email and thus unfortunately
> completely missed the point, in many cases seemingly because they were
> too quick to jump on my use of Outlook as an email client (thus assuming
> I was just one of "those" terrible horrible know-nothing Windows users).

Just to be fair, this can also affect evolution email client on linux as well.

But I quit using it a while back in favor of thunderbird so haven't
seen these headers cause problems in quite some time.

> It's obvious from 3 years of reading these forums that there is a "Linux
> Postgres users good, Windows Postgres users bad" bias by many (but not
> all) of the members rather than them realizing that " 'we' are all
> Postgres users

I wouldn't quite go that far, but there are definitely plenty of
people of the attitude of "you chose windows, you deal with it"
attitude.  I tend to be that way on very specific windows just because
I don't know enough about it to be helpful, but I generally just keep
that to myself.

> To be fair to these listservs though, they are MUCH better than many in
> terms of providing support, and I can't remember the last time I saw a
> flame war erupt.

Even when they do, they're usually pretty polite flame wars. :)

>  (But I'm still not going to post the problem I've been
> hitting recently running Postgres 7.4.1 on a SuSE 9.0 box -- somehow I
> think the first few responses might be "get OFF that version".
> [Hopefully will upgrade very soon, but can't just yet on that specific
> machine.  Meanwhile it's a production server so I'll just have to deal
> with it...])

You'll notice that most of the time that that's posted, it's also
accompanied by other advice.  But running 7.4.1 is like driving a
pinto in a demolition derby, you're just asking for big trouble, and
no one wants to see you burst into flames, metaphorically speaking.

I wonder if there's a way of having your MTA scrape the headers of
incoming mail so those x-headers can be removed.  I'd imagine it's a
simple hack in sendmail / qmail etc... on unix.  Don't know about
Windows.

"Scott Marlowe" <scott.marlowe@gmail.com> writes:

> On 10/8/07, Bill Bartlett <bbartlett@softwareanalytics.com> wrote:
>>
>>  (But I'm still not going to post the problem I've been
>> hitting recently running Postgres 7.4.1 on a SuSE 9.0 box -- somehow I
>> think the first few responses might be "get OFF that version".
>> [Hopefully will upgrade very soon, but can't just yet on that specific
>> machine.  Meanwhile it's a production server so I'll just have to deal
>> with it...])

Why can't you? We're talking about order of 5 minutes downtime in exchange you
avoid the risk of several data loss, data corruption, server cash, and
security holes.

Actually there are a few changes in 7.4.x that could require more work to
upgrade. If your database locale is something like Hungarian or if you have
invalidly encoded UTF8 strings in your database you might have some issues
having upgraded.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com

am  Mon, dem 08.10.2007, um 13:05:50 -0400 mailte Bill Bartlett folgendes:
> > > (Makes me have to think twice about raising
> > > any _real_ issues though, like why my VACUUMs periodically keep
> getting
> > > into lock contentions with my JDBC connections and ultimately
> causing me
> > > to have to shut down Postgres w/ "pg_ctl stop -m immediate", or how
> to
> > > properly handle postmaster errors that don't appear to be documented
> > > anywhere except in the source code...  [No, it's not the absolutely
>
> > > most recent version of Postgres; No, I can't upgrade it.])
> >
> > This isn't fair, Bill. Your original question concerned posts your
> > email client has trouble processing, which isn't the primary
> > topic of the list. You also knew that it was somewhat contentious,
> given that
> > you've made comments such as "Believe me, I'm not defending
> > Outlook", so you probably weren't overly surprised at some of the
> > responses you got. Also note that the responses you got were attempts
> to
> > solve your problem: I don't see any that only belittled your choice of
> software.
> >
>
> Most people didn't completely read my email and thus unfortunately
> completely missed the point, in many cases seemingly because they were
> too quick to jump on my use of Outlook as an email client (thus assuming
> I was just one of "those" terrible horrible know-nothing Windows users).

I can speak only for myself, but when i see damaged mails (with, for
instance, broken quoting-line as you can see above, then i think:
'apparently a windoze-user'.

I don't need to know the headers.


> It's obvious from 3 years of reading these forums that there is a "Linux
> Postgres users good, Windows Postgres users bad" bias by many (but not
> all) of the members rather than them realizing that " 'we' are all
> Postgres users, 'those' other horrible people are MySQL / Oracle / etc

No, sorry. We are all PG-users. Nothing else.


> Rereading my original email, you can see that the problem I was trying
> to address was not my choice of email software but rather that several
> people in these Postgres listservs (like other people in other
> listservs) were intentionally misusing a specific header flag that is
> used by specific email programs (only the various "Outlook"-related

Outlook isn't a email-programm, it's a PITA. Nothing else. Okay, maybe a
PIM. But not an email-software, for this case it can't work. At least in
a useful way.


> normally just grit my teeth and ignore it.)  After researching this flag
> further (after seeing the feedback I got on this forum), I've discovered
> that this type of misuse is frequently used, and even recommended on
> many Linux-oriented web sites as a means, to annoy Outlook-based users
> (as a means to annoy Windows users).  As I mentioned above, I think in a
> forum such as this, where "we" need to all be Postgres users, I don't

You should realize: Postings/Mailings generated with OjE are 'Broken by
Design'. I don't need to see the headers to recognize, that the
posting/email was generated with this pice of broken software, i can see
it in the body. Let me say the same with other words: not the
linux-users smells, the posting/emails, generated with M$-software,
smells! Thats the point.


> (Just for the record, not that I should have to justify my background
> and biases [or hopefully lack thereof] to the group: I gave up fighting
> platform wars a LOOONG time ago, back when I used to try to get the

Again: i don't want a fight (or war) on OS or email-client, but, please,
realise: for mailings-list there are good and not-so-good software
available. And all M$-shit isn't suitable. Rule of thumb.


> Windows servers).  I may be using Windows on my desktop, but I don't
> think I'd put myself into the category of being merely one of "those
> terrible horrible know-nothing Windows users".)

Noboday said that. But again, realise, Outlook and OE isn't a good
email-software.  It's shit, straightforward.


> > at least post them and see what kind of response you get,
> > rather than judge the list as a whole due to the response you got to
> an
> > off-topic post. Many of the people on the lists have been here for
> years and
> > have gotten lots of helpful advice, which is why they've stuck

Can you please see this shit of broken lines? This is from YOU!


Again: i've cut off the X- - header. But i wish, all users here are
using functioning email-software. It makes more easily for all. And
again: Outlook and OE isn't a functioning email-software. Obviously.


And sorry for my broken english...


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A. Kretschmer wrote:
> am  Mon, dem 08.10.2007, um 13:05:50 -0400 mailte Bill Bartlett folgendes:
>>>> (Makes me have to think twice about raising
>>>> any _real_ issues though, like why my VACUUMs periodically keep
>> getting
>>>> into lock contentions with my JDBC connections and ultimately
>> causing me
>>>> to have to shut down Postgres w/ "pg_ctl stop -m immediate", or how

Hello,

Discussions on the validity or non validity of Microsoft software are
not for this list.

Further, requesting that people change their behavior wholesale because
of a particular piece of software's limitations are also not for this
list, unless that software is PostgreSQL.

May we please get this back on topic.

Joshua D. Drake



- --

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
            UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHCnkmATb/zqfZUUQRAtsyAKCmvc8VF01cLNtm3SztzwxeeaHoMQCcC9yC
tCyMXOlZMZ5LWL9GnJB+OTQ=
=z4dp
-----END PGP SIGNATURE-----

--- "A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote:
> > > at least post them and see what kind of response you get,
> > > rather than judge the list as a whole due to the response you got to
> > an
> > > off-topic post. Many of the people on the lists have been here for
> > years and
> > > have gotten lots of helpful advice, which is why they've stuck
>
> Can you please see this shit of broken lines? This is from YOU!

I use yahoo. I guess that yahoo's web-based client must be broken also, since I've seem email I've
posted has produce broken lines.  I am not sure if this was already mentioned, does anyone know of
non-html windows email clients that work well for this mailing list?

Regards,
Richard Broersma Jr.

am  Mon, dem 08.10.2007, um 12:00:30 -0700 mailte Richard Broersma Jr folgendes:
> --- "A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote:
> > > > at least post them and see what kind of response you get,
> > > > rather than judge the list as a whole due to the response you got to
> > > an
> > > > off-topic post. Many of the people on the lists have been here for
> > > years and
> > > > have gotten lots of helpful advice, which is why they've stuck
> >
> > Can you please see this shit of broken lines? This is from YOU!
>
> I use yahoo. I guess that yahoo's web-based client must be broken also, since I've seem email I've
> posted has produce broken lines.  I am not sure if this was already mentioned, does anyone know of
> non-html windows email clients that work well for this mailing list?

As far i can see your mails are correct. But one exclusion: please no
CC: to the sender, i'm reading the list.

But yes: thats all isn't realy a problem and that has nothing to do with
our topic. Please, no war about email-software. We are all PG-users and
the idea behind the list is to help together and not to fight against.
Peace and end of discussion about this, okay?


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

A. Kretschmer wrote:

> As far i can see your mails are correct. But one exclusion: please no
> CC: to the sender, i'm reading the list.

The additional CC: to sender is customary on these lists.

--
Alvaro Herrera                         http://www.flickr.com/photos/alvherre/
"How amazing is that? I call it a night and come back to find that a bug has
been identified and patched while I sleep."                (Robert Davidson)
               http://archives.postgresql.org/pgsql-sql/2006-03/msg00378.php

On 10/8/07, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
> am  Mon, dem 08.10.2007, um 12:00:30 -0700 mailte Richard Broersma Jr folgendes:
> > --- "A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote:
> > > > > at least post them and see what kind of response you get,
> > > > > rather than judge the list as a whole due to the response you got to
> > > > an
> > > > > off-topic post. Many of the people on the lists have been here for
> > > > years and
> > > > > have gotten lots of helpful advice, which is why they've stuck
> > >
> > > Can you please see this shit of broken lines? This is from YOU!
> >
> > I use yahoo. I guess that yahoo's web-based client must be broken also, since I've seem email I've
> > posted has produce broken lines.  I am not sure if this was already mentioned, does anyone know of
> > non-html windows email clients that work well for this mailing list?
>
> As far i can see your mails are correct. But one exclusion: please no
> CC: to the sender, i'm reading the list.
>
> But yes: thats all isn't realy a problem and that has nothing to do with
> our topic. Please, no war about email-software. We are all PG-users and
> the idea behind the list is to help together and not to fight against.
> Peace and end of discussion about this, okay?

Actually CC to the sender is the norm on this list.  I believe there
is a majordomo command that will cause it to change the reply to all
headers such that you won't get the CC.

The reason for the CC to sender is that someone may be reading the
list who isn't necessarily subscribed to it, from such things as being
CC'd from another list, etc...

If they replied only to the list, you might not see their reply for
several hours while it was being moderated.

More info available by sending an email to majordomo@postgresql.org
with help in the body...

"Alvaro Herrera" <alvherre@commandprompt.com> writes:

> A. Kretschmer wrote:
>
>> As far i can see your mails are correct. But one exclusion: please no
>> CC: to the sender, i'm reading the list.
>
> The additional CC: to sender is customary on these lists.

Generally it's just how Internet mailing lists work.

There's some header you can add to your own emails to request mailers not add
it. But I don't remember what it is.

You can also tell majordomo not to forward you messages on the list to which
you were cc'd.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com

am  Mon, dem 08.10.2007, um 15:46:04 -0500 mailte Scott Marlowe folgendes:
> Actually CC to the sender is the norm on this list.  I believe there

Okay, no problem. I'm knowing other lists like the german debian user
list and there is this CC to the sender unwanted.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net