Fwd: [GENERAL] Postgres forums ... take 2

[Elliot Chance]

On 16/11/2010, at 2:01 PM, Craig Ringer wrote:

> On 15/11/10 17:37, Thom Brown wrote:
> 
>> That's actually some good work you've done there!  I didn't know phpBB
>> supported bidirectional mailing list support.
> 
> Yikes. Neither did I. I've always seen phpBB as the barren wasteland of
> web forums - forums full of half-page animated GIF signatures separating
> single lines of text, some kind of content-free zone of minimum
> information density. Maybe it can be configured to be better than that
> after all.
> 
> How does it handle threading? Will forum threads be properly threaded?
> And will replies have the correct In-Reply-To:  header so they
> get threaded correctly?

It uses the message-id and in-reply-to header information, so a change in email 
subject will not effect the flow of the thread. However not all emails stick by 
those rules, a few emails do not carry the header information in which case the 
script strips the subject (to remove "Re:", "[GENERAL]") and then matches the 
topic by name as a fall back.

Email header information can be sketchy at the best of times, but this does a 
pretty good job at making sure almost all of the messages are handled 
correctly. In some rare cases when the in-reply-to is missing and the subject 
has changed it will create a new thread, but a forum moderator can click a 
button to merge the threads and all is fixed.

> 
> Have you been in touch with the Pg list admins to make sure they're cool
> with this?

At this point its a good idea, who is the best person(s) to contact? I want to 
make sure anything I do does not in any way reflect badly on the community or 
seem like i'm doing anything dishonest.

> 
>> A few points though.  I think we'd need to disable smileys, bbcode, any
>> form of rich text formatting, flash or embedded images.
> 
> Mostly agreed. Limiting signatures to 4/5 lines would be nice too.

All forum software limits the size of a signature to stop people abusing it. As 
the signature is a separate entity the mail that comes from the forum can 
contain or ignore the signature.

> 
> Limited HTML is really useful on web forums, though, as it allows you to
> delineate code from other text. Unless the whole forum is set to
> monospaced text with preserved whitespace, that's necessary to ensure
> that code samples are readable.

That's one thing that can't be fixed when incoming emails are converted to 
forums posts the code blocks appear as normal text.

There is no use importing huge archives seeing as most of it is either too old 
to be relevant, bloat the forum and most people don't bother using a search 
anyway before posting. But it may be alright to say import the last few months 
of data (that include product release announcements, active bugs etc.) I have 
written another script which imports the mbox files just as a proof of concept:
http://forums.postgresql.com.au/viewforum.php?f=34
Those 7 threads were imported from a single months mbox file.

> 
> -- 
> Craig Ringer
> 
> Tech-related writing: http://soapyfrogs.blogspot.com/


-- 
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] Adding data from mysql to postgresql periodically

[Robert Hodges]

On Nov 14, 2010, at 4:44 AM PST, franrtorres77 wrote:

> 
> Hi there
> 
> I need to add periodically some data from a remote mysql database into our
> postgresql database. So, does anyone know how to do it having in mind that
> it must be runned every minute or so for adding new records to the
> postresql?
> 

Have you looked at Tungsten to replicate from MySQL to PostgreSQL?  Linas 
Virbalas and I did a talk on using Tungsten to replicate out of MySQL and into 
PostgreSQL at PG-West.  (URL:  
https://www.postgresqlconference.org/content/liberating-your-data-mysql-cross-database-replication-rescue)
  Linas posted the slides but I don't see them on the PG-West site yet.  If you 
send a mail off-list we would be happy to send slides.   Linas will be doing a 
similar talk at PG-Day EU in Stuttgart next month. 

The software we discussed is open source and available from SourceForge.net at 
http://sourceforge.net/projects/tungsten/.  We are doing some commercial work 
to replicate into Greenplum and will keep building out the generic MySQL to 
PostgreSQL replication along the way. 

Cheers, Robert Hodges

Disclosure:  Linas and I both work on Tungsten for Continuent. 
-- 
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] Postgres forums ... take 2

[Craig Ringer]

On 15/11/10 17:37, Thom Brown wrote:

> That's actually some good work you've done there!  I didn't know phpBB
> supported bidirectional mailing list support.

Yikes. Neither did I. I've always seen phpBB as the barren wasteland of
web forums - forums full of half-page animated GIF signatures separating
single lines of text, some kind of content-free zone of minimum
information density. Maybe it can be configured to be better than that
after all.

How does it handle threading? Will forum threads be properly threaded?
And will replies have the correct In-Reply-To:  header so they
get threaded correctly?

Have you been in touch with the Pg list admins to make sure they're cool
with this?

> A few points though.  I think we'd need to disable smileys, bbcode, any
> form of rich text formatting, flash or embedded images.

Mostly agreed. Limiting signatures to 4/5 lines would be nice too.

Limited HTML is really useful on web forums, though, as it allows you to
delineate code from other text. Unless the whole forum is set to
monospaced text with preserved whitespace, that's necessary to ensure
that code samples are readable.

-- 
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/

-- 
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] Postgres forums ... take 2

[Elliot Chance]

I have made some major changes "beta2"

1. For now the forums have been set to read only, this is to prevent anyone 
posting a response (as it doesn't send emails back to the mailing list yet.)

2. Added a bunch of new forums to match the mailing lists, also have subscribed 
to all main mailing list with the following map:

pgsql-admin => General > Server Administration & Maintenance
pgsql-advocacy => News > Advocacy & Media
pgsql-announce => News > News & Announcements
pgsql-bugs => Development > Bugs & Testing
pgsql-docs => Development > Documentation
pgsql-general => General > Other
pgsql-jdbc => Languages > Java
pgsql-jobs => Other > Commercial & Jobs
pgsql-novice => General > Newbie Section
pgsql-odbc => Languages > ODBC / Other
pgsql-performance => General > Performance & Benchmarking
pgsql-php => Languages > PHP
pgsql-sql => Languages > SQL
pgsql-students => Other > Education & Certification

This is visa-vers so forum topics posted on Languages > SQL will be posted to 
pgsql-sql, etc.

3. A rewrite of the mail to forums script so that it uses a MIME parser which 
handles messy emails, quotations and multipart emails as it should now.

4. The mail parser uses the correct "in-reply-to" to match up discussion 
threads rather than simply stripping the email subject.

Extra thoughts;

It would not be practical for the forums to create a dummy mailing list email 
address per person or forum, however theres needs to be a robust way to make 
sure the topics/threads and posts match up with the threads and emails in the 
mailing list. The problem I see is that replies to the forum are not 
technically replies via email and so they will not carry the unique 
"in-reply-to" identifier. I believe this can be fixed by spoofing the 
in-reply-to from the forum, so that each forum reply will drop in the 
in-reply-to manually. I will do some testing on my own address before any 
messages are sent to the real mailing lists of course.

Stripping bbcode, smilies, HTML or whatever is very simple, nothing to worry 
about there.

Since the php piping script had so many changes, I wiped the contents of the 
forum to run it all again. So any URLs you had that point to specific thread 
IDs probably wont work any more, but as you can see the forums seems to be 
doing what it should:
http://forums.postgresql.com.au

There needs to be more forum mapping from specific forums to mailing lists, for 
example "Languages > Perl" to the closest mailing list which might be 
pgsql-general. However once the topic is created in a forum all the responses 
will stay in that forum, so even though people reply on the pgsql-general 
mailing list the replies appear under Languages > Perl.

The infrastructure exists to create as many forum mappings as needed, and I 
could add post processing. So for example an email to pgsql-general with the 
title "perl won't connect" will recognise "perl" and move it to the Languages > 
Perl.


On 15/11/2010, at 9:42 PM, Magnus Hagander wrote:

> On Mon, Nov 15, 2010 at 11:08, Elliot Chance  wrote:
>> On 15/11/2010, at 8:37 PM, Thom Brown wrote:
>>> I know this is a sensitive issue with some people, i've made sure no
>>> information is posted thats not already currently being indexed by google.
>>> 
>>> The only maintenance I can see is that all new topics are pushed into the
>>> General > Other category as the script can't differentiate what category it
>>> should in fact belong to, once the topic is moved it will stay there. This
>>> shouldn't be a real problem as theres not many new topics being created on
>>> any given day.
>> 
>> Elliot,
>> 
>> That's actually some good work you've done there!  I didn't know phpBB
>> supported bidirectional mailing list support.
>> 
>> It doesn't. I have a subscription address that is piped into a PHP script
>> that uses the phpBB3 APIs to do all you see.
> 
> That sounds scary :-) Particularly given attachments and such. but if
> it works
> 
> 
>> A few points though.  I think we'd need to disable smileys, bbcode, any form
>> of rich text formatting, flash or embedded images.  In short, plain text
>> only, which is the policy on the mailing list.  I think it would be more
>> useful if each forum directly corresponded to a mailing list too.  What I
>> mean is that if there was a forum on the site which didn't match to a
>> mailing list, only forum users could use it.
>> 
>> If someone were to send a reply on the forum all the bbcode would be
>> stripped before emailing it to the mailing list to keep the mailing list
>> "pure." Is that what you mean?
> 
> Personally, my thoughts are that if we want lists mirrored to a forum,
> they should look the same in both cases. Which means they should be
> stripped in the forums *as well*. but since I wouldn't be using the
> forums, my view should perhaps not be paid attention to around that.
> But there should *definitely* not be any bbcode going to the
> mailinglists.
> 
> 
>> Also, if someone registers on the forum, do they get

Re: [GENERAL] when postgres failed to recover after the crash...

[Craig Ringer]

On 15/11/10 19:59, anypossibility wrote:
> Gabriele, 
> Thank you for the link. I downloaded the book and read the chapter. Very
> useful information.
> 
> Craig,
> The storage type is SAN over AFP. 

I very, very strongly suggest getting your SAN host to export an iSCSI
volume to mount directly on your mac instead. Using PostgreSQL over AFP
is (as far as I know) not regularly tested, and it's certainly not a
good idea.

-- 
Craig Ringer

-- 
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] escape character for 'psql -c ' command

[Derrick Rice]

Short answer: for simple commands, you can use shell-escaping of a
double-quoted string.

psql -c "\\copy \"Table\" from 'text file'"

Note: double \\ is intentional. You need to escape the backslash, which
normally escapes other special characters, like $ and ". Watch out for other
special characters though, which is why I prefer the long answer...


Long answer: A *nix shell will concatenate string literals that are
immediately following each other, even when they aren't the same type
(single quoted or double quoted).  So the following:

"abc"'def'hij"  (reads: double quote, abc, double quote, single quote, def,
single quote, double quote, hij, double quote)

is "abc" + 'def' + "hij" or "abcdefhij" to the shell

So if you have a single-quoted string, to insert a single quote you (1) stop
the single quoted string (2) start a double-quoted string (3) write a single
quote as the content of the double-quoted string (4) stop the double-quoted
string (5) restart the single quoted string.  All without any spaces (unless
they are inside either the double or single quoted strings as part of your
content).

You can obviously insert 2 consecutive single quotes within a single
double-quoted string - or any characters... just be aware you are in
double-quotes now, so you need to escape special characters or go back to
single quotes.

Your example:

psql -c ' Copy "Table" from '"'"'text file'"'"

Derrick

On Mon, Nov 15, 2010 at 6:17 PM, Gary Fu  wrote:

> Hi,
>
> How do I escape both " and ' to be used in the 'psql -c ' command ?
> For example, how to make the psql command {\copy "Table" from 'txt_file'}
> to be used in the psql with -c option (psql -c) ?
> The "Table" has to be double quoted here.
>
> Thanks,
> Gary
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] escape character for 'psql -c ' command

[Gary Fu]

Hi,

How do I escape both " and ' to be used in the 'psql -c ' command ?
For example, how to make the psql command {\copy "Table" from 
'txt_file'} to be used in the psql with -c option (psql -c) ?

The "Table" has to be double quoted here.

Thanks,
Gary

--
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] The first dedicated PostgreSQL forum

[Lee Hachadoorian]

If anyone's interested, I've started accessing the postgres list through
gmane.org (along with several other mailing lists I subscribe to). It's
gives you the choice of reading the list as a threaded archive, a blog,
or through an NNTP newsreader or an RSS feed. Everyone chooses their
preferred interface, the community is not fractured by interface preference.

--Lee

On 11/15/2010 03:13 PM, Joshua D. Drake wrote:
> On Mon, 2010-11-15 at 21:06 +0100, Magnus Hagander wrote:
> 
>> I do think that the PostgreSQL lists are available there. And other
>> than that, they are on markmail.org, Nabble, etc. AFAIK several of
>> those allow both reading and posting. Is there actually something
>> about these interfaces that people find *missing*, or can we easily
>> solve this whole thing by more clearly telling people that these
>> options exist?
> 
> Mainly I think it is the user interface and the fact that they are
> external. They don't look, act or feel like forums. Shrug. Further they
> aren't part of postgresql.org so nobody knows the level of real support
> they are going to get.
> 
> JD
> 
> 

-- 
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center

-- 
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] v9 deployment advise

[Jeff Davis]

On Mon, 2010-11-15 at 13:25 -0700, Gauthier, Dave wrote:

> Are there any serious known bugs left in 9.0.1 that will be addressed
> in 9.0.2 (if there even will be a 9.0.2)?

Take a look here:

http://git.postgresql.org/gitweb?p=postgresql.git;a=log;h=refs/heads/REL9_0_STABLE

at everything that happened after 9.0.1. It's up to you whether you
consider those serious or not, but I don't think there are any major
issues that should prevent deployment.

Regards,
Jeff Davis


-- 
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] Expected frequency of auto_vacuum activity

[Dave Jennings]

Hi Matthew,

Yes, I do have one query that creates a temporary table and that query 
runs reasonably often, so that's a good explanation for the catalog tables.


Your query works perfectly on 8.4 and the ratio of dead rows to live 
rows is generally quite small so it looks like everything is working as 
it should.


Thanks very much for the queries and the explanation. Perfect.

Dave.

On 15/11/10 19:57, Matthew Walden wrote:

Dave,

Does your application use temporary tables?  This may explain high
autovacuum activity in the catalog tables.

With regards to your tables, I wrote this very script to give me an
indication of the amount of required activity on the user tables.  I
deliberately keep this one simple and it doesn't include the associated
TOAST rows but you get the idea (it works on 9.0 you may need to test on 8).

select relid,
schemaname,
relname,
n_live_tup as live_rows,
n_dead_tup as dead_rows,
round(cast(n_dead_tup as decimal)/cast(n_live_tup as decimal),3)
fraction_changed,
to_char(greatest(last_
autovacuum,last_vacuum),'DD/MM/ HH24:MI:SS') last_vacuum,
to_char(greatest(last_autoanalyze,last_analyze),'DD/MM/
HH24:MI:SS') last_analyze
from pg_stat_all_tables a
where schemaname not in ('pg_catalog','pg_toast','information_schema');

select name,
setting
from pg_settings
where name in
('autovacuum_vacuum_threshold','autovacuum_vacuum_scale_factor','autovacuum_analyze_scale_factor','autovacuum_analyze_threshold');

If your tables are particularly large then the number of dead rows may
not qualify autovacuum_vacuum_scale_factor.  What is the ratio between
live and dead rows?


On Mon, Nov 15, 2010 at 6:55 PM, Dave Jennings
mailto:d...@ambientindustries.com>> wrote:

Hi there,

I'm wondering if I'm seeing the appropriate amount of vacuuming in
my 8.4 database.

I have a database with about twenty tables ranging from small,
mostly static, tables to tables with tens or hundreds of thousands
of rows and a fair number of inserts and updates (but very few deletes).

I have auto_vacuum with the default values (except
"log_autovacuum_min_duration=0") but when I check the logs I see
lots of vacuuming of a few pg_catalog tables - every 30 mins or so -
but very few vacuums of my public tables. Just two occurences in the
last week.

According to pg_stat_all_tables only two tables in the public schema
have ever been auto-vacuumed.

Is this expected?

Thanks,

Dave.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




--
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] Expected frequency of auto_vacuum activity

[Matthew Walden]

Dave,

Does your application use temporary tables?  This may explain high
autovacuum activity in the catalog tables.

With regards to your tables, I wrote this very script to give me an
indication of the amount of required activity on the user tables.  I
deliberately keep this one simple and it doesn't include the associated
TOAST rows but you get the idea (it works on 9.0 you may need to test on 8).

select relid,
   schemaname,
   relname,
   n_live_tup as live_rows,
   n_dead_tup as dead_rows,
   round(cast(n_dead_tup as decimal)/cast(n_live_tup as decimal),3)
fraction_changed,
   to_char(greatest(last_
autovacuum,last_vacuum),'DD/MM/ HH24:MI:SS') last_vacuum,
   to_char(greatest(last_autoanalyze,last_analyze),'DD/MM/
HH24:MI:SS') last_analyze
from pg_stat_all_tables a
where schemaname not in ('pg_catalog','pg_toast','information_schema');

select name,
   setting
from pg_settings
where name in
('autovacuum_vacuum_threshold','autovacuum_vacuum_scale_factor','autovacuum_analyze_scale_factor','autovacuum_analyze_threshold');

If your tables are particularly large then the number of dead rows may not
qualify autovacuum_vacuum_scale_factor.  What is the ratio between live and
dead rows?


On Mon, Nov 15, 2010 at 6:55 PM, Dave Jennings
wrote:

> Hi there,
>
> I'm wondering if I'm seeing the appropriate amount of vacuuming in my 8.4
> database.
>
> I have a database with about twenty tables ranging from small, mostly
> static, tables to tables with tens or hundreds of thousands of rows and a
> fair number of inserts and updates (but very few deletes).
>
> I have auto_vacuum with the default values (except
> "log_autovacuum_min_duration=0") but when I check the logs I see lots of
> vacuuming of a few pg_catalog tables - every 30 mins or so - but very few
> vacuums of my public tables. Just two occurences in the last week.
>
> According to pg_stat_all_tables only two tables in the public schema have
> ever been auto-vacuumed.
>
> Is this expected?
>
> Thanks,
>
> Dave.
>
> --
> 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] Indexes on individual columns of composite primary key

[Matthew Walden]

"Apparently (after reading the documentation link provided by Tom) there can
be value to indexes when accessed with leading columns missing".

That is a new one on me too - interesting.

I suppose it comes down to testing at the end of the day - if you "set
enable_seqscan to false" and "EXPLAIN ANALYSE" your query then you will see
whether the indexes you create are used.  Whether they are useful will
require you to set enable_seqscan back to true and see whether the optimizer
chooses to use them (but that will change as your data does).

[GENERAL] v9 deployment advise

[Gauthier, Dave]

I will be deploying Postgres v9 on Linux-64, Sles10 sometime next week.  This 
will be a production system, so 9.1-alpha2 is probaly off the table.

Are there any serious known bugs left in 9.0.1 that will be addressed in 9.0.2 
(if there even will be a 9.0.2)?

Thanks in Advance!

Re: [GENERAL] Indexes on individual columns of composite primary key

[Dann Corbit]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Dann Corbit
Sent: Monday, November 15, 2010 12:21 PM
To: 'Dan Halbert'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Indexes on individual columns of composite primary key

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Dan Halbert
Sent: Monday, November 15, 2010 12:01 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Indexes on individual columns of composite primary key


I have a table with four columns. Three of those columns are defined as the 
composite primary key. Does it make sense to create indexes on any or all of 
those three columns individually for performance reasons? PG does let me create 
the indexes. But perhaps it's redundant, since there's an implicitly-created 
index for the composite primary key.

>>

It depends upon your access patterns.

Consider a key on:

CREATE UNIQUE INDEX fourcols ON mytable(col1, col2, col3, col4);

If you always query in this way:

SELECT * FROM mytable WHERE col1 = ?, col2 = ?, col3 = ?, col4 = ?

Or possibly:

SELECT * FROM mytable WHERE col1 = ?, col2 = ?, col3 = ?

Or possibly:

SELECT * FROM mytable WHERE col1 = ?, col2 = ?

Or possibly:

SELECT * FROM mytable WHERE col1 = ?

Then there is no value in creating an index on the other columns.  If (on the 
other hand) you often query like this:

SELECT * FROM mytable WHERE col2 = ?

Or possibly:

SELECT * FROM mytable WHERE col4 = ?

Then it makes sense to create an index on col2 and an index on col4.  The 
composite index will remain useful as long as the most significant columns are 
provided.

<<

Apparently (after reading the documentation link provided by Tom) there can be 
value to indexes when accessed with leading columns missing.

Aside:

I think there is a possible bug in the documentation.  This link:

http://www.postgresql.org/docs/9.0/static/indexes-unique.html

Says that only btree indexes can be unique.  But Hash indexes seem an obvious 
exception to me, or am I overlooking something?

Re: [GENERAL] Indexes on individual columns of composite primary key

[Dann Corbit]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Dan Halbert
Sent: Monday, November 15, 2010 12:01 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Indexes on individual columns of composite primary key


I have a table with four columns. Three of those columns are defined as the 
composite primary key. Does it make sense to create indexes on any or all of 
those three columns individually for performance reasons? PG does let me create 
the indexes. But perhaps it's redundant, since there's an implicitly-created 
index for the composite primary key.

>>

It depends upon your access patterns.

Consider a key on:

CREATE UNIQUE INDEX fourcols ON mytable(col1, col2, col3, col4);

If you always query in this way:

SELECT * FROM mytable WHERE col1 = ?, col2 = ?, col3 = ?, col4 = ?

Or possibly:

SELECT * FROM mytable WHERE col1 = ?, col2 = ?, col3 = ?

Or possibly:

SELECT * FROM mytable WHERE col1 = ?, col2 = ?

Or possibly:

SELECT * FROM mytable WHERE col1 = ?

Then there is no value in creating an index on the other columns.  If (on the 
other hand) you often query like this:

SELECT * FROM mytable WHERE col2 = ?

Or possibly:

SELECT * FROM mytable WHERE col4 = ?

Then it makes sense to create an index on col2 and an index on col4.  The 
composite index will remain useful as long as the most significant columns are 
provided.

<<

Re: [GENERAL] Considering Solid State Drives

[Greg Smith]

Allan Kamau wrote:

I am now thinking of investing in a SSD (Solid State Drive), and maybe
choosing between "Crucial Technology 256GB Crucial M225 Series
2.5-Inch Solid State Drive (CT256M225)" and "Intel X25-M G2 (160GB) -
Intel MLC".


Both of these are worthless for database applications if you care about 
your data.  In order to perform well, SSDs need to have a write cache to 
buffer small writes.  For PostgreSQL to work as intended, that write 
cache needs to be non-volatile.  It is not in either of those drives.  I 
hear tales of lost PostgreSQL data on Intel SSDs every month, the 
database is lucky to survive a single power outage.


The only relatively inexpensive SSD we've heard about on the Performance 
list that's survived all of the durability on crash tests thrown at it 
is the OCZ Vertex 2 Pro; see 
http://archives.postgresql.org/pgsql-performance/2010-07/msg00449.php 
for a summary.  That avoids this problem by having an Ultracapacitor 
integrated with the drive, to allow orderly processing of the write 
cache if power is lost.  There are other SSD devices that are similarly 
reliable, but the costs are quite a bit higher.


More background about this topic at 
http://wiki.postgresql.org/wiki/Reliable_Writes


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services and Supportwww.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books


--
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] Indexes on individual columns of composite primary key

[Matthew Walden]

Dan,

It depends on your application.  There is no point in creating an index with
the same 3 columns in the primary key (in the same order).

If you have an index on COL1, COL2 and COL3 (in that order) then if you have
a query such as SELECT COL1, COL2, COL3 from T1 then the index will be
considered.  Same if you have a query with the same columns but different
order ie SELECT COL2, COL1, COL3 from T1 and if you just select the first
column ie SELECT COL1 from T1.  The index won't be considered if you have a
query such as SELECT COL2 FROM T1 so if your application does this you may
wish to consider such indexes.

Also bear in mind the order of which you create the index or primary key.
They should be ordered by uniqueness starting with the most unique.

On Mon, Nov 15, 2010 at 8:01 PM, Dan Halbert  wrote:

> I have a table with four columns. Three of those columns are defined as the
> composite primary key. Does it make sense to create indexes on any or all of
> those three columns individually for performance reasons? PG does let me
> create the indexes. But perhaps it's redundant, since there's an
> implicitly-created index for the composite primary key.
>
>
>
> Thanks,
>
> Dan
>

Re: [GENERAL] Indexes on individual columns of composite primary key

[Tom Lane]

"Dan Halbert"  writes:
> I have a table with four columns. Three of those columns are defined
> as the composite primary key. Does it make sense to create indexes on
> any or all of those three columns individually for performance
> reasons?

Please see
http://www.postgresql.org/docs/9.0/static/indexes.html
particularly sections 11.3 and 11.5.

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] The first dedicated PostgreSQL forum

[Joshua D. Drake]

On Mon, 2010-11-15 at 21:06 +0100, Magnus Hagander wrote:

> I do think that the PostgreSQL lists are available there. And other
> than that, they are on markmail.org, Nabble, etc. AFAIK several of
> those allow both reading and posting. Is there actually something
> about these interfaces that people find *missing*, or can we easily
> solve this whole thing by more clearly telling people that these
> options exist?

Mainly I think it is the user interface and the fact that they are
external. They don't look, act or feel like forums. Shrug. Further they
aren't part of postgresql.org so nobody knows the level of real support
they are going to get.

JD


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


-- 
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] The first dedicated PostgreSQL forum

[Richard Broersma]

On Sat, Nov 13, 2010 at 4:33 PM, Craig Ringer
 wrote:
> It can. The glassfish forums are bidirectionally gatewayed to the mailing
> list in just such a manner, and it works astonishingly well. Before using
> those I would've been opposed to the idea, but now ... it doesn't seem so
> bad.

The google mailing lists also have this bidirectional feature.

-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

-- 
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] The first dedicated PostgreSQL forum

[Magnus Hagander]

On Sun, Nov 14, 2010 at 02:01, Dennis Gearon  wrote:
> One thing that makes me agree with this is the antiquated format of the digest
> emails.

I agree - I thought nobody used that anymore :-)


> I hate google mail, but I love the google groups application. If postgres had 
> a
> combination online forum/google groups thing (if there is such a thing),
> communication would be much improved.

Personally, I hate the google groups format. But that's beside the
point here - I do realize that others prefer it.

I do think that the PostgreSQL lists are available there. And other
than that, they are on markmail.org, Nabble, etc. AFAIK several of
those allow both reading and posting. Is there actually something
about these interfaces that people find *missing*, or can we easily
solve this whole thing by more clearly telling people that these
options exist?


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Indexes on individual columns of composite primary key

[Dan Halbert]

I have a table with four columns. Three of those columns are defined as the 
composite primary key. Does it make sense to create indexes on any or all of 
those three columns individually for performance reasons? PG does let me create 
the indexes. But perhaps it's redundant, since there's an implicitly-created 
index for the composite primary key.
 
Thanks,
Dan

Re: [GENERAL] The first dedicated PostgreSQL forum

[Greg Smith]

Tom Lane wrote:

I'm not nearly as concerned about whether there are forums as about
having "rogue" forums outside the postgresql.org domain.  People could
misperceive such things as having some official status


That the site is now mirroring recent news from postgresql.org doesn't 
help with that.  I find it hard to get too excited about yet another 
forum style discussion area when there's already more PostgreSQL talk on 
http://stackoverflow.com/ than I have time to keep up with.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services and Supportwww.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books


--
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] Backends "hanging" with strace showing selects?

[hubert depesz lubaczewski]

On Mon, Nov 15, 2010 at 02:52:16PM -0500, Tom Lane wrote:
> hubert depesz lubaczewski  writes:
> > hi
> > had strange situation today.
> 
> > very high load, cpu saturated (and this machine has lots of cores).
> 
> > i straced one of backends that was using lots of cpu (it was doing some
> > select, but I don't know what as i wasn't able to start psql).
> 
> > strace looked like this:
> > select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
> > select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
> > select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
> > select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
> 
> That suggests a lot of contention for a spinlock, but without any
> information about what the system was really doing, it's hard to go
> further than that.

we had ~ 700 active connections, but it is virtually impossible to tell
what they were doing, as I couldn't connect to get pg_stat_activity.

Best regards,

depesz

-- 
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

-- 
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] Expected frequency of auto_vacuum activity

[Matthew Walden]

Dave,

Does your application use temporary tables?  This may explain high
autovacuum activity in the catalog tables.

With regards to your tables, I wrote this very script to give me an
indication of the amount of required activity on the user tables.  I
deliberately keep this one simple and it doesn't include the associated
TOAST rows but you get the idea (it works on 9.0 you may need to test on 8).

select relid,
   schemaname,
   relname,
   n_live_tup as live_rows,
   n_dead_tup as dead_rows,
   round(cast(n_dead_tup as decimal)/cast(n_live_tup as decimal),3)
fraction_changed,
   to_char(greatest(last_
autovacuum,last_vacuum),'DD/MM/ HH24:MI:SS') last_vacuum,
   to_char(greatest(last_autoanalyze,last_analyze),'DD/MM/
HH24:MI:SS') last_analyze
from pg_stat_all_tables a
where schemaname not in ('pg_catalog','pg_toast','information_schema');

select name,
   setting
from pg_settings
where name in
('autovacuum_vacuum_threshold','autovacuum_vacuum_scale_factor','autovacuum_analyze_scale_factor','autovacuum_analyze_threshold');

If your tables are particularly large then the number of dead rows may not
qualify autovacuum_vacuum_scale_factor.  What is the ratio between live and
dead rows?


On Mon, Nov 15, 2010 at 6:55 PM, Dave Jennings
wrote:

> Hi there,
>
> I'm wondering if I'm seeing the appropriate amount of vacuuming in my 8.4
> database.
>
> I have a database with about twenty tables ranging from small, mostly
> static, tables to tables with tens or hundreds of thousands of rows and a
> fair number of inserts and updates (but very few deletes).
>
> I have auto_vacuum with the default values (except
> "log_autovacuum_min_duration=0") but when I check the logs I see lots of
> vacuuming of a few pg_catalog tables - every 30 mins or so - but very few
> vacuums of my public tables. Just two occurences in the last week.
>
> According to pg_stat_all_tables only two tables in the public schema have
> ever been auto-vacuumed.
>
> Is this expected?
>
> Thanks,
>
> Dave.
>
> --
> 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] postgreSQL-devel 8.3.8

[Devrim GÜNDÜZ]

On Mon, 2010-11-15 at 13:53 +, Malm Paul wrote:
> I need to download postgreSQL develope package for ver. 8.3.8, but I
> can not find the ...i386.rpm anywhere.
> 
> Please, could someone guide me to the right place? 

Which distro? If RHEL 5, then:

http://yum.pgsqlrpms.org/8.3/redhat/rhel-5-i386/postgresql-8.3.8-1PGDG.rhel5.i386.rpm

Regards,
-- 
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] Backends "hanging" with strace showing selects?

[Tom Lane]

hubert depesz lubaczewski  writes:
> hi
> had strange situation today.

> very high load, cpu saturated (and this machine has lots of cores).

> i straced one of backends that was using lots of cpu (it was doing some
> select, but I don't know what as i wasn't able to start psql).

> strace looked like this:
> select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)

That suggests a lot of contention for a spinlock, but without any
information about what the system was really doing, it's hard to go
further than that.

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] index row requires 10040 bytes, maximum size is 8191

[Tom Lane]

akp geek  writes:
> What we do with the text that I have mentioned is, we have
> search functionality on the text. The user enters some keywords and then the
> application should be able to search for all the text that matches the key
> word.

Well, a btree index on the entire string is approximately useless for
that anyway.  Look into the text search functionality.

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

[GENERAL] Backends "hanging" with strace showing selects?

[hubert depesz lubaczewski]

hi
had strange situation today.

very high load, cpu saturated (and this machine has lots of cores).

i straced one of backends that was using lots of cpu (it was doing some
select, but I don't know what as i wasn't able to start psql).

strace looked like this:
select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)

i.e. lots (literally hundreds) of such messages. very quickly adding new ones.

pg version is:
 PostgreSQL 8.3.12 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 
20080704 (Red Hat 4.1.2-48)


I know it's not much of information, but perhaps it will ring someones bell, 
and there will be ready answer what went wrong?

Best regards,

depesz


-- 
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

-- 
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] Schema tool

[Aram Fingal]

On Nov 11, 2010, at 4:56 PM, Thomas Kellerer wrote:
> 
> Actually I think it would be worthwhile documenting your experience in the 
> PostgreSQL Wiki as well:
> 
> http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools

Thanks, I will post something there after I have done some more investigation.  
I notice that some of the ones mentioned here are on there but with only a line 
or two of description.  I'll also see about putting something about PGnJ.  
That's a free, generic query tool which I have been using with PostgreSQL.


-Aram

Re: [GENERAL] Schema tool

[Aram Fingal]

Thanks, each of you for all the suggestions on schema generating tools.  The 
idea is to have something which will connect to the database and automatically 
make a schema from what you've got.  Here's what I have had a chance to 
evaluate so far...

DBVisualizer - It does a good job with the default placement of tables and 
links and some nice features for what to include/exclude from the diagram.   
The ability to select specific tables is very handy since I have a few tables 
which I plan to get rid of as soon as I have the data moved to a different 
database.  Also, part of the point of what I'm doing is to support certain 
other people who will want to write queries.  They each have specific interests 
and don't need all the tables.  I will probably make more than one simplified 
version of the schema, each focused on a specific set of data.  

Drawbacks:  It missed one foreign key relationship.  I don't know why but that 
link is missing from the diagram.  SQL Power Architect (see below) did see and 
diagram this relationship.  Links get a little jumbled when I move things 
around a little and then switch back and forth between different kinds of views 
(hierarchic, organic, orthogonal, etc.)  That's not too difficult to fix 
because all you have to do is drag each table slightly and the links get 
redrawn in a more optimal way.  It doesn't have the turkey foot (or whatever 
you call it) to indicate a many-to-one relationship.  I suppose that's not a 
big deal since the relationships are pretty obvious in my database, with each 
foreign key link going to the primary key of another table.  One very minor 
thing is that there are some places where I want to indicate that a foreign key 
is specified with "on update cascade." I will have to export to graphics and 
add that note to the diagram.  

The one big thing is that it displays views as disconnected objects.  Views are 
very important for this project and it would be good to indicate where their 
contents are coming from.   I'm not sure how best to diagram that since many of 
the view columns are taking several columns from various tables and performing 
a mathematical computation.  To understand what is in each column of the view, 
you need to see both the inputs and the formula.  

SQL Power Architect - also free and open source - This was not actually 
suggested on this list but some of the suggestions led me to the right key 
words to do another search and find it.  As I understand, the main point of 
this tool is to transfer data from one database instillation to another.  For 
example, if you want to migrate data from MS SQL Server to PostgreSQL, this is 
a tool you should look at.  Deriving a schema is just one element of this.  You 
connect to the source database and it reverse engineers it for you and shows 
you the results.  You make changes and selections of what you want to move, 
etc.  Then you hit "Forward Engineer" and it moves the data to your target, 
according to your edited schema.  The advantage is that it shows lots of 
information and looks to be good for figuring out environments with lots of 
different kinds of databases all over the place. It has more features to change 
appearance of the diagrams than DbVisualizer does, including colors of the text 
and the table, itself.  It can export to HTML.  

Drawbacks:  It does not put the tables in a convenient layout by default.  You 
can't easily exclude objects but you can delete them from the diagram.  Links 
point to any place on the tables and not the specific columns which they refer 
to/from.  You can drag the links around so that they do point to the exact 
column but this can be frustrating drudge work since each drag moves both ends 
of the link and you can easily mess up corrections you made earlier.  For some 
reason it didn't detect the correct datatypes for many rows and just put "CLOB" 
where it should be INTEGER, DATE, etc.  SQL Power Architect also displays views 
as disconnected objects.

SchemaSpy looks to be a good option since they mention that they had to do some 
custom work to support views but I'm having trouble getting it to work at this 
point.

dbWrench by Nizana looks interesting but it's commercial and I want to see if a 
free option will work first.  I'm not totally against spending money but I have 
seen situations where free stuff ends up being as good or better than 
commercial.  

MicroOLAP Database Designer and PostgreSQL Maestro are Windows only, unless I 
missed something.  I do have both WINE and a Windows VM under VirtualBox but I 
would prefer something Mac native.  

I haven't had a chance to check out Mogwai yet.

-Aram
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Expected frequency of auto_vacuum activity

[Dave Jennings]

Hi there,

I'm wondering if I'm seeing the appropriate amount of vacuuming in my 
8.4 database.


I have a database with about twenty tables ranging from small, mostly 
static, tables to tables with tens or hundreds of thousands of rows and 
a fair number of inserts and updates (but very few deletes).


I have auto_vacuum with the default values (except 
"log_autovacuum_min_duration=0") but when I check the logs I see lots of 
vacuuming of a few pg_catalog tables - every 30 mins or so - but very 
few vacuums of my public tables. Just two occurences in the last week.


According to pg_stat_all_tables only two tables in the public schema 
have ever been auto-vacuumed.


Is this expected?

Thanks,

Dave.

--
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] postgreSQL-devel 8.3.8

[Willy-Bas Loos]

On Mon, Nov 15, 2010 at 2:53 PM, Malm Paul  wrote:
>I need to download postgreSQL develope package for ver. 8.3.8

Why 8.3.8?
8.3.10 is the same, only with issues fixed.
http://www.postgresql.org/ftp/binary/v8.3.10/linux/rpms/

hth

WBL
--
"Patriotism is the conviction that your country is superior to all
others because you were born in it." -- George Bernard Shaw

-- 
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] index row requires 10040 bytes, maximum size is 8191

[akp geek]

Thanks for all your valuable thoughts . It took me a while to read all your
suggestions , still trying to understand it fully.

What we do with the text that I have mentioned is, we have
search functionality on the text. The user enters some keywords and then the
application should be able to search for all the text that matches the key
word.

On Sat, Nov 13, 2010 at 7:21 PM, Craig Ringer
wrote:

> On 11/13/2010 11:15 AM, Tom Lane wrote:
>
>> "Joshua D. Drake"  writes:
>>
>>> On Sat, 2010-11-13 at 09:48 +0800, Craig Ringer wrote:
>>>
 Thoughts, folks? Does this matter in practice, since anything you'd want
 to index will in practice be small enough or a candidate for full-text
 indexing?

>>>
>>  I have run into this problem maybe 3 times in my whole career, precisely
>>> because if you are dealing with text that big, you move to full text
>>> search.
>>>
>>
>> Yeah, the real question here is exactly what do you think a btree index
>> on a large text column will get you?
>>
>
> About the only useful case I can see is with text data of very irregular
> size. The vast majority is small, but there are a few massively bigger
> items. It'd be nice if the index method had a fallback for items too big to
> index in this case, such as a prefix match and heap recheck.
>
> Of course, I've never run into this in practice, and if I did I'd be
> wondering if I had my schema design quite right. I can't imagine that the
> mostly aesthetic improvement of eliminating this indexing limitation would
> be worth the effort. I'd never ask or want anyone to waste their time on it,
> and don't intend to myself. Most of the interesting "big text" indexing
> problems are solved by tsearch and/or functional indexes.
>
> --
> Craig Ringer
>

[GENERAL] figuring out a streaming replication failure

[Scott Ribe]

The standby log:

->  2010-11-14 17:40:16 MST - 887 -LOG:  database system was shut down in 
recovery at 2010-11-14 17:40:10 MST
->  2010-11-14 17:40:16 MST - 887 -LOG:  entering standby mode
->  2010-11-14 17:40:16 MST - 887 -LOG:  consistent recovery state reached at 
3/3988FF8
->  2010-11-14 17:40:16 MST - 887 -LOG:  redo starts at 3/3988F68
->  2010-11-14 17:40:16 MST - 887 -LOG:  invalid record length at 3/3988FF8
->  2010-11-14 17:40:16 MST - 885 -LOG:  database system is ready to accept 
read only connections
->  2010-11-14 17:40:16 MST - 890 -LOG:  streaming replication successfully 
connected to primary
->  2010-11-15 02:24:26 MST - 890 -FATAL:  could not receive data from WAL 
stream: FATAL:  requested WAL segment 000100030004 has already been 
removed

->  2010-11-15 02:24:26 MST - 887 -LOG:  unexpected pageaddr 2/B9BF2000 in log 
file 3, segment 4, offset 12525568
->  2010-11-15 02:24:27 MST - 2790 -LOG:  streaming replication successfully 
connected to primary
->  2010-11-15 02:24:27 MST - 2790 -FATAL:  could not receive data from WAL 
stream: FATAL:  requested WAL segment 000100030004 has already been 
removed

->  2010-11-15 02:24:32 MST - 2791 -LOG:  streaming replication successfully 
connected to primary
->  2010-11-15 02:24:32 MST - 2791 -FATAL:  could not receive data from WAL 
stream: FATAL:  requested WAL segment 000100030004 has already been 
removed

...

Now, the standby is geographically isolated from the master, so it's over an 
internet connection, so it's not a shock that with a large enough update and 
wal_keep_segments not large enough, speed of disk would outrun speed of network 
sufficiently for this to happen.

But as far as I know there was almost no write activity at 2am, no active users 
at all, no batch processing. There is a pg_dumpall that kicks off at 2am and 
these errors start about the same time that it finished. I also did the 
original synch and standby launch immediately after a mass update before 
autovacuum had a chance to run, so at some point there would be a lot of tuples 
marked dead.

wal_keep_segments was at 64, the first segment still around was 
000100030010, checkpoint_segments was 16. In the midst of the 
pg_dumpall the master logs do show messages about checkpoint flushing too 
often. The 70ish log segments still around show mod times right around 2:23, 
progressing a second or so each, whereas they were created over a much longer 
period going back to the day before.

1 question: what happened here? Why were log files created the day before 
updated?

1 suggestion: would it be possible to not delete wal segments that are needed 
by a currently attached standby?

-- 
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice





-- 
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] Trouble Accessing Schema-Qualified Table

[Tom Lane]

bricklen  writes:
> On Mon, Nov 15, 2010 at 8:35 AM, Jerry Richards
>  wrote:
>> 
>> teo=# select * from sip_presence('ts_sofia_internal') where
>> sip_presence.sip_user='1003';
>> 
>> ERROR:  function sip_presence(unknown) does not exist

> The problem isn't the schema name, it is with the parameter you are
> using in your function.

While Jerry didn't say so in so many words, I suppose that the query
worked for him before he changed the search path.  In that case the
problem *is* the schema name: his function is in a schema that is no
longer in the search path.  He can either qualify the function name:

select * from other_schema.sip_presence('ts_sofia_internal') where

or list both schemas in his search_path so that the function can still
be found without a schema name attached.

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] Basic Tutorials for 9.0

[Vincent Veyron]

Le lundi 15 novembre 2010 à 08:17 +0100, Alban Hertroys a écrit :
> > 
> > I have only used MS Access (for years).  My difficulties are very basic.
> > When I said I can't view the data in the data base, I meant basically - with
> > any method.  The psql help shows many ommands for displaying.  My basic
> > difficulties are: Choosing the right one(s), determining whether I have used
> > it correctly, knowing whether I have actually put data in the db.  
> >

to work with psql you need three things :
1- a basic knowledge of relational databases concepts 
2- some familiarity with a command line interface
3- much reading of the excellent postgresql documentation

Working with psql is very similar to typing some SQL in a querydef, and
clicking on 'Execute' to see the result, except you do it for everything
(data inserts, exports, relations, etc...), and then type 'Enter'. 

So as noted in the doc, to add a primary key to a table, instead of
opening the table in creation mode and clicking on the little key, you
type :
ALTER TABLE distributors ADD PRIMARY KEY (dist_id);

(see http://www.postgresql.org/docs/9.0/static/sql-altertable.html)

It's disconcerting when you're used to a GUI , but one gets to like it.
Just learn the sql (it's very similar to Access (Jet's) SQL), you'll
find that you have more control in the end.

there are GUI available for PostgreSQL (for instance : pgadmin), but I
never tried them.

> 
> > A pointer to a detailed tutorial would be great.

Read the doc, more.

-- 
Vincent Veyron
http://marica.fr/
Logiciel de gestion des dossiers de contentieux et d'assurance pour le service 
juridique


-- 
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] Trouble Accessing Schema-Qualified Table

[bricklen]

On Mon, Nov 15, 2010 at 8:35 AM, Jerry Richards
 wrote:
>
> teo=# select * from sip_presence('ts_sofia_internal') where
> sip_presence.sip_user='1003';
>
> ERROR:  function sip_presence(unknown) does not exist
>
> LINE 1: select * from sip_presence('ts_sofia_internal') where sip_pr...
>   ^
> HINT:  No function matches the given name and argument types. You might need
> to add explicit type casts.

The problem isn't the schema name, it is with the parameter you are
using in your function. Since you don't show the definition of your
function, I'm going to take a guess and assume your input
'ts_sofia_internal' is a TEXT type, so you might do something like:

select * from sip_presence('ts_sofia_internal'::TEXT) where ...

If not "TEXT" type, check your function to see what it expects the type to be

\df sip_presence

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Trouble Accessing Schema-Qualified Table

[Jerry Richards]

Hello,

I need to access a database using two different schemas.  During initialization 
the default schema is set as follows:

set schema 'ucm';

Then later, I need to get data from a table in another schema 
'ts_sofia_internal', so I tried the following, but it returns an error as shown:

teo=# select * from sip_presence('ts_sofia_internal') where 
sip_presence.sip_user='1003';
ERROR:  function sip_presence(unknown) does not exist
LINE 1: select * from sip_presence('ts_sofia_internal') where sip_pr...
  ^
HINT:  No function matches the given name and argument types. You might need to 
add explicit type casts.
teo=#

Does anyone know how I can do this?  By the way, I'm using odbc to connect to 
the database.

Thanks,
Jerry

Re: [GENERAL] identifying local connections

[Scott Ribe]

On Nov 15, 2010, at 9:05 AM, Steve Clark wrote:

> netstat -an will do it on linux.
> 
> sockstat will do it on FreeBSD.
> 
> What's OS X ? ;-) 

Apple must use very different option switches for their netstat, because I see 
no way to get PIDs (which seems like a pretty big oversight to me), and -an 
would not make sense:

-a  Include directory entries whose names begin with a dot (.).
-n  Display user and group IDs numerically, rather than converting to
 a user or group name in a long (-l) output.  This option turns on
 the -l option.

;-)

-- 
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice





-- 
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] identifying local connections

[Scott Ribe]

On Nov 15, 2010, at 9:24 AM, Tom Lane wrote:

> In that case see lsof --- you can match up the ends of the connection
> using the hex value in the "device" column.
> 
>   regards, tom lane
> 

Yes, that works. Match "Address" from netstat to "DEVICE" in lsof.

Thanks. 

-- 
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice





-- 
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] identifying local connections

[Tom Lane]

Scott Ribe  writes:
> On Nov 15, 2010, at 8:50 AM, Tom Lane wrote:
>> netstat will probably work for this, depending on what platform you're on.

> OS X. I can see the connections, but I don't see an option to display PIDs.

In that case see lsof --- you can match up the ends of the connection
using the hex value in the "device" column.

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] identifying local connections

[Steve Clark]

On 11/15/2010 11:00 AM, Scott Ribe wrote:

On Nov 15, 2010, at 8:50 AM, Tom Lane wrote:

   

netstat will probably work for this, depending on what platform you're on.
 

OS X. I can see the connections, but I don't see an option to display PIDs.

   

netstat -an will do it on linux.

sockstat will do it on FreeBSD.

What's OS X ? ;-)

--
Stephen Clark
*NetWolves*
Sr. Software Engineer III
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.cl...@netwolves.com
http://www.netwolves.com

Re: [GENERAL] identifying local connections

[Scott Ribe]

On Nov 15, 2010, at 8:50 AM, Tom Lane wrote:

> netstat will probably work for this, depending on what platform you're on.

OS X. I can see the connections, but I don't see an option to display PIDs.

-- 
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice





-- 
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] identifying local connections

[Tom Lane]

Scott Ribe  writes:
> Assume I have a local process which leaves a transaction open & idle for an 
> extended period of time. Is there any way to identify the local process 
> connected to a particular backend?

netstat will probably work for this, depending on what platform you're on.

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

[GENERAL] identifying local connections

[Scott Ribe]

Assume I have a local process which leaves a transaction open & idle for an 
extended period of time. Is there any way to identify the local process 
connected to a particular backend?

pg_stat_activity is fine for TCP connections but does not provide useful 
identifying information for domain socket connections.

I just upgraded to 9, and will implement set application_name in my various 
server daemons, but was wondering if there's a way to identify this process 
right now.

-- 
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice





-- 
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] Adding data from mysql to postgresql periodically

[Vick Khera]

On Sun, Nov 14, 2010 at 7:44 AM, franrtorres77  wrote:
> I need to add periodically some data from a remote mysql database into our
> postgresql database. So, does anyone know how to do it having in mind that
> it must be runned every minute or so for adding new records to the
> postresql?

People have written systems to replicate data from various
heterogeneous databases.  Usually they take the form of writing
insert/update/delete triggers on each and every table. This trigger
will write the change log into another table (either full row, or just
the fields changed depending on how clever you think you want to be).
Then a daemon program that runs continuously reads that log table and
applies the changes to the destination system then deletes the log
rows it just processed.

The trick is getting the ordering of your operations correct, and
using transactions to ensure you don't miss/lose data while applying
it to the destination system.

I have seen a presentation by OmniTI where they built such a system to
replicate from postgres to oracle.  Also, the slony1 replication
software for postgres operates basically this way, but is only for
replication to another postgres instance.  You could review the slony
software for details on how it ensures correctness of order of
operations.

-- 
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] where is pg_stat_activity (and others) in the documentation?

[Vick Khera]

On Mon, Nov 15, 2010 at 5:15 AM, Willy-Bas Loos  wrote:
> I was looking for what exactly "waiting" means in pg_stat_activity.

waiting for a lock.

-- 
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] Suggested swap size for new db?

[Vick Khera]

On Sun, Nov 14, 2010 at 10:34 PM, Evan D. Hoffman
 wrote:
> as part of another project) running on CentOS 5.4 or 5.5.  I know the
> old rule of thumb that your swap partition/disk should be equal to the
> physical memory, but when dealing with memory sizes greater than ~16
> GB that starts to seem strange to me; and now with 96 GB of physical
> memory I'm starting to wonder if I'd be better off forgoing swap
> altogether for the new database.
>

The "old" rule is swap = 3x RAM. But those were the days of 64MB RAM
being an extravagance. :)

What I currently do is try to make swap = RAM + delta, so that if
(when?) the machine panics I can get a useful kernel panic core dump.
Given that boot drives are ~1TB these days, that's not a big deal.

Machines running large RAM like that should never swap, else the point
of having that much RAM is lost, and you probably needed more RAM to
handle your workload than you anticipated.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] postgreSQL-devel 8.3.8

[Malm Paul]

Hi,
I need to download postgreSQL develope package for ver. 8.3.8, but I can not 
find the ...i386.rpm anywhere.

Please, could someone guide me to the right place?
Kind regards,
Paul

-- 
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] generate query string in trigger func

[Pavel Stehule]

Hello

2010/11/15 Willy-Bas Loos :
> Hi,
>
> I'm experimenting with partitioning.
> I have split up my original table into 15 parts and i have written a trigger
> that will handle the INSERTs.
> I didn't want to write the same insert statement 15 times, so i thought it
> would be a good thing to just dynamically build the insert statement in the
> trigger function, concatenating the partition name to the sql.
>
> But all these syntaxes didn't work.
> Is that some restriction of trigger functions?
> I don't know yet if it would really be faster, i'd want to test that (it
> probably is slower because of the extra string processing).
>
> Must i use an IF statement for each table in the partitioned table? (why?)
>
> Cheers,
>
> WBL
>
>
> CREATE OR REPLACE FUNCTION grid_cells_insert_trigger()
> RETURNS TRIGGER AS $$
> BEGIN
>     EXECUTE 'INSERT INTO grid_cells_'||NEW.grdid||' VALUES (NEW.*)';
>     RETURN NULL;
> END;
> $$
> LANGUAGE plpgsql;
>
>

you can try

CREATE OR REPLACE FUNCTION grid_cells_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
  EXECUTE 'INSERT INTO ' || quote_ident('grid_cells_' || new.grid) ||
' VALUES($1.*)' USING new;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

it working on my 9.0

>
> Below goes wrong because grdcellocid and locid can be NULL
> The query string will be NULL if i concat a null value to it, so i need to
> use coalesce on potential null attributes
> I've tried several syntaxes: 'NULL', 'NULL::integer', '\N', '\\N', it's all
> bad:
> ERROR:  invalid input syntax for integer: "NULL::integer"
> But i am not quoting these values inside the query string, it seems like
> there is some quote_literal() active in trigger functions?
>

use a quote_nullable function instead - or better USING clause

Regards

Pavel Stehule

>
>
> CREATE OR REPLACE FUNCTION grid_cells_insert_trigger()
> RETURNS TRIGGER AS $$
> BEGIN
>     EXECUTE 'INSERT INTO grid_cells_'||NEW.grdid||' VALUES
> ('||NEW.grdcelid||','||NEW.grdid||','||NEW.x||','||NEW.y||','||NEW.taxid||','||NEW.yearstart||','||NEW.yearstop||','||NEW.count_rejected||','||NEW.count_submitted||','||NEW.count_ind_validated||','||NEW.count_pop_validated||','||coalesce(NEW.locid,
> '\\N')||','||coalesce(NEW.grdcellocid,
> '\\N')||','||NEW.count_exotic||','||NEW.created||' );';
>     RETURN NULL;
> END;
> $$
> LANGUAGE plpgsql;
>
> --
> "Patriotism is the conviction that your country is superior to all others
> because you were born in it." -- George Bernard Shaw
>

-- 
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] when postgres failed to recover after the crash...

[anypossibility]

Gabriele, Thank you for the link. I downloaded the book and read the chapter. 
Very useful information.


Craig,
The storage type is SAN over AFP. 


Unfortunately, it has been a week or so since the crash. We were able to 
recover lost data from last backup (a few hours old) but next time, I will copy 
entire data directory before restart the server (thank you for the advice).


Here is the copy from pg_log:


Log from 09:19 PM (First time the volume was disconnected)
LOG:  database system was interrupted; last known up at 2010-11-10 21:01:40 MST
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  record with zero length at 4C/EAA135CC
LOG:  redo is not required
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started




Log from 09:27 PM (When volume was re-mounted)
LOG:  database system was interrupted; last known up at 2010-11-10 21:19:13 MST
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  record with zero length at 4C/EAA1360C
LOG:  redo is not required
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
LOG:  received smart shutdown request
LOG:  autovacuum launcher shutting down
LOG:  shutting down
LOG:  database system is shut down 



















 On Sun, 14 Nov 2010 18:40:29 -0800 Gabriele Bartolini 
 wrote  

Hi, 
> In general, it's a really bad idea to run PostgreSQL (or any other 
> database) over file-level network storage like SMB/AFP/CIFS/NFS. 
> Block-level network storage like iSCSI is generally OK, depending on the 
> quality of the drivers in target and initiator. 
 
What Craig says it is true and it might be worth reading the free 
chapter about "Database Hardware" from Greg's book on high performance, 
which you can download from 
http://blog.2ndquadrant.com/en/2010/10/postgresql-90-high-performance.html 
 
> Before you do anything more, make a COMPLETE COPY of the entire data 
> directory, including the pg_clog, pg_xlog, etc directories as well as 
> the main data base storage. Put it somewhere safe and do not touch it 
> again, because it might be critical for recovery. 
Yes, also in case you had any tablespace do not forget about them. But a 
cold backup in this cases it is always a good thing. 
 
Ciao, 
Gabriele 
 
-- 
 Gabriele Bartolini - 2ndQuadrant Italia 
 PostgreSQL Training, Services and Support 
 gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it 
 
 
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) 
To make changes to your subscription: 
http://www.postgresql.org/mailpref/pgsql-general 
 

[GENERAL] generate query string in trigger func

[Willy-Bas Loos]

Hi,

I'm experimenting with partitioning.
I have split up my original table into 15 parts and i have written a trigger
that will handle the INSERTs.
I didn't want to write the same insert statement 15 times, so i thought it
would be a good thing to just dynamically build the insert statement in the
trigger function, concatenating the partition name to the sql.

But all these syntaxes didn't work.
Is that some restriction of trigger functions?
I don't know yet if it would really be faster, i'd want to test that (it
probably is slower because of the extra string processing).

Must i use an IF statement for each table in the partitioned table? (why?)

Cheers,

WBL


CREATE OR REPLACE FUNCTION grid_cells_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
EXECUTE 'INSERT INTO grid_cells_'||NEW.grdid||' VALUES (NEW.*)';
RETURN NULL;
END;
$$
LANGUAGE plpgsql;



Below goes wrong because grdcellocid and locid can be NULL
The query string will be NULL if i concat a null value to it, so i need to
use coalesce on potential null attributes
I've tried several syntaxes: 'NULL', 'NULL::integer', '\N', '\\N', it's all
bad:
ERROR:  invalid input syntax for integer: "NULL::integer"
But i am not quoting these values inside the query string, it seems like
there is some quote_literal() active in trigger functions?



CREATE OR REPLACE FUNCTION grid_cells_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
EXECUTE 'INSERT INTO grid_cells_'||NEW.grdid||' VALUES
('||NEW.grdcelid||','||NEW.grdid||','||NEW.x||','||NEW.y||','||NEW.taxid||','||NEW.yearstart||','||NEW.yearstop||','||NEW.count_rejected||','||NEW.count_submitted||','||NEW.count_ind_validated||','||NEW.count_pop_validated||','||coalesce(NEW.locid,
'\\N')||','||coalesce(NEW.grdcellocid,
'\\N')||','||NEW.count_exotic||','||NEW.created||' );';
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

-- 
"Patriotism is the conviction that your country is superior to all others
because you were born in it." -- George Bernard Shaw

Re: [GENERAL] Adding data from mysql to postgresql periodically

[franrtorres77]

Hello again.

Well, the data that I want to import is some satellite coordinates saved in
mysql , the fields are X and Y and want to import them into a postgis table
for converting these coordinates to a normal latitude and longitude points
because in mysql are saved in Utm format.

I have foound an example for connecting to mysql using perl, but i would
prefer using a query in postgresql but I think it will be necessary firstly
create a linking connection with the mysql db. I did it with sql server but
in postgresql dont know how to do it.

The other way to do it, exporting the data to a csv file, would be an
acceptable solution, and also an easiest way to perform the operation.
-- 
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Adding-data-from-mysql-to-postgresql-periodically-tp3264392p3265406.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
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] Postgres forums ... take 2

[Magnus Hagander]

On Mon, Nov 15, 2010 at 11:08, Elliot Chance  wrote:
> On 15/11/2010, at 8:37 PM, Thom Brown wrote:
>> I know this is a sensitive issue with some people, i've made sure no
>> information is posted thats not already currently being indexed by google.
>>
>> The only maintenance I can see is that all new topics are pushed into the
>> General > Other category as the script can't differentiate what category it
>> should in fact belong to, once the topic is moved it will stay there. This
>> shouldn't be a real problem as theres not many new topics being created on
>> any given day.
>
> Elliot,
>
> That's actually some good work you've done there!  I didn't know phpBB
> supported bidirectional mailing list support.
>
> It doesn't. I have a subscription address that is piped into a PHP script
> that uses the phpBB3 APIs to do all you see.

That sounds scary :-) Particularly given attachments and such. but if
it works


> A few points though.  I think we'd need to disable smileys, bbcode, any form
> of rich text formatting, flash or embedded images.  In short, plain text
> only, which is the policy on the mailing list.  I think it would be more
> useful if each forum directly corresponded to a mailing list too.  What I
> mean is that if there was a forum on the site which didn't match to a
> mailing list, only forum users could use it.
>
> If someone were to send a reply on the forum all the bbcode would be
> stripped before emailing it to the mailing list to keep the mailing list
> "pure." Is that what you mean?

Personally, my thoughts are that if we want lists mirrored to a forum,
they should look the same in both cases. Which means they should be
stripped in the forums *as well*. but since I wouldn't be using the
forums, my view should perhaps not be paid attention to around that.
But there should *definitely* not be any bbcode going to the
mailinglists.


> Also, if someone registers on the forum, do they get a major domo
> registration email?  And if so, would this be set to receive no emails upon
> registration?  I'm not clear as to how this step would work because, at the
> moment, mailing list subscribers have to subscribe on a list-by-list basis.
> So registration to the forum site wouldn't necessarily mean they'd want to
> join any particular mailing list.  Similarly, could they unregister easily?
> And anyone who attempts to post to a mailing list they aren't subscribed to
> requires moderation, so we don't wish to exacerbate this.
>
> No they are not registered on the mailing list, but they actually don't need
> to be, let me explain:
> 1. John Smith has a postgres related question and finds the forums, he signs
> up and posts his question.
> 2. His post is then emailed to the mailing list under a generic registered
> address like "mailingl...@postgresql.com.au"

This part I really don't like. It should at least be posted with some
kind of uniquely identifiable pass-through address, if not the users
own address (make that an option?). Like
magnus-hagander-...@forums.whatever


> 3. Bob House reads Johns question on the mailing list and simply sends an
> email reply.
> 4. The email reply is piped into the forum and matches the topic based on
> the email subject (thats how it currently does it.)

You really should be matching on the response headers rather than
subject... Or at least both.



-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] where is pg_stat_activity (and others) in the documentation?

[Willy-Bas Loos]

Hi,

I can't find pg_stat_activity in the the documentation.
There are many pg_stat_xxx tables and views, but their columns are not
described, or so it seems.
Maybe that is a bug in the documentation, then.

I was looking for what exactly "waiting" means in pg_stat_activity. In the
9.0.1 documentation, it is mentioned that it shows the "waiting status" but
that doesn't really help.
http://www.postgresql.org/docs/current/static/monitoring-stats.html#MONITORING-STATS-VIEWS

I was looking for the answer for postgresql version 8.3 (it might differ).

Cheers,

Willy-Bas Loos

-- 
"Patriotism is the conviction that your country is superior to all others
because you were born in it." -- George Bernard Shaw

Fwd: [GENERAL] Postgres forums ... take 2

[Elliot Chance]

On 15/11/2010, at 8:37 PM, Thom Brown wrote:

> On 15 November 2010 08:34, Elliot Chance  wrote:
> Hi again,
> 
> I've taken in all the feedback about http://forums.postgresql.com.au and the 
> general consensus is that nobody wants a separate entity - a few people 
> mentioned that if it was interoperable with the mailing list that it would be 
> better. So I did.
> 
> The concept goes like this;
> 1. Any posts to the general mailing list will be picked up by the forum, the 
> email data is converted and posted on the forum, for example;
> http://forums.postgresql.com.au/viewtopic.php?f=30&t=39
> 2. Any reply to the forum will do the reverse and send the post back to the 
> mailing list as a reply.
> 
> This means the forum can be fully controlled through the mailing list without 
> the need to visit the forums directly. However those people who prefer to use 
> a forum interface can, and those messages are relayed back through the 
> mailing list to get answered.
> 
> Step 1 is complete (might need a little tweaking, i've only tried it with a 
> couple of topics.) Step 2 I haven't begun - wanted to get some more feedback.
> 
> All the forum topics and posts are back-dated to match the emails, which 
> means it would be *theoretically* possible to load in the entire postgres 
> mailing list archive but I wouldn't do that on a server that couldn't handle 
> that much data.
> 
> Disclaimer about user names:
> User names are registered automatically based on the unique email address of 
> the person emailing the response. Each user is given a random 8 character 
> password. You can use the recover password page to login to your account and 
> change your user name to anything you want, the only important thing is that 
> your email address matches.
> 
> I know this is a sensitive issue with some people, i've made sure no 
> information is posted thats not already currently being indexed by google.
> 
> The only maintenance I can see is that all new topics are pushed into the 
> General > Other category as the script can't differentiate what category it 
> should in fact belong to, once the topic is moved it will stay there. This 
> shouldn't be a real problem as theres not many new topics being created on 
> any given day.
> 
> Elliot,
> 
> That's actually some good work you've done there!  I didn't know phpBB 
> supported bidirectional mailing list support.

It doesn't. I have a subscription address that is piped into a PHP script that 
uses the phpBB3 APIs to do all you see.

> 
> I think, however, that having such a forum at a .com.au address isn't 
> particularly desirable, as it implies it's regional.  If others are happy for 
> you to work on this, it might be an idea to speak to the existing web team to 
> see if they are able to provide you with pointers and possibly resources to 
> get such a thing up and running.  It would be nice, for example, to have 
> forums.postgresql.org set up.

I was just amazed that postgresql.com.au was available (in australia you need a 
registered company to get a .com.au address so that's why.)

At the moment its running on mysql (I know, but they don't support postgres) 
but it will work with postgres. The forum software, database and scripts I've 
written are all portable so theres no reason why it couldn't be moved to 
another domain any time in the future. Obviously at the time I couldn't use 
forums.postgresql.org.

Lets see how it goes, if it does turn out to be useful then we'll have a chat 
to the developers.

> 
> A few points though.  I think we'd need to disable smileys, bbcode, any form 
> of rich text formatting, flash or embedded images.  In short, plain text 
> only, which is the policy on the mailing list.  I think it would be more 
> useful if each forum directly corresponded to a mailing list too.  What I 
> mean is that if there was a forum on the site which didn't match to a mailing 
> list, only forum users could use it.

If someone were to send a reply on the forum all the bbcode would be stripped 
before emailing it to the mailing list to keep the mailing list "pure." Is that 
what you mean?

> 
> Also, if someone registers on the forum, do they get a major domo 
> registration email?  And if so, would this be set to receive no emails upon 
> registration?  I'm not clear as to how this step would work because, at the 
> moment, mailing list subscribers have to subscribe on a list-by-list basis.  
> So registration to the forum site wouldn't necessarily mean they'd want to 
> join any particular mailing list.  Similarly, could they unregister easily?  
> And anyone who attempts to post to a mailing list they aren't subscribed to 
> requires moderation, so we don't wish to exacerbate this.

No they are not registered on the mailing list, but they actually don't need to 
be, let me explain:
1. John Smith has a postgres related question and finds the forums, he signs up 
and posts his question.
2. His post is then emailed to the mailing list under a 

Re: [GENERAL] Postgres forums ... take 2

[Thom Brown]

On 15 November 2010 08:34, Elliot Chance  wrote:

> Hi again,
>
> I've taken in all the feedback about http://forums.postgresql.com.au and
> the general consensus is that nobody wants a separate entity - a few people
> mentioned that if it was interoperable with the mailing list that it would
> be better. So I did.
>
> The concept goes like this;
> 1. Any posts to the general mailing list will be picked up by the forum,
> the email data is converted and posted on the forum, for example;
> http://forums.postgresql.com.au/viewtopic.php?f=30&t=39
> 2. Any reply to the forum will do the reverse and send the post back to the
> mailing list as a reply.
>
> This means the forum can be fully controlled through the mailing list
> without the need to visit the forums directly. However those people who
> prefer to use a forum interface can, and those messages are relayed back
> through the mailing list to get answered.
>
> Step 1 is complete (might need a little tweaking, i've only tried it with a
> couple of topics.) Step 2 I haven't begun - wanted to get some more
> feedback.
>
> All the forum topics and posts are back-dated to match the emails, which
> means it would be *theoretically* possible to load in the entire postgres
> mailing list archive but I wouldn't do that on a server that couldn't handle
> that much data.
>
> Disclaimer about user names:
> User names are registered automatically based on the unique email address
> of the person emailing the response. Each user is given a random 8 character
> password. You can use the recover password page to login to your account and
> change your user name to anything you want, the only important thing is that
> your email address matches.
>
> I know this is a sensitive issue with some people, i've made sure no
> information is posted thats not already currently being indexed by google.
>
> The only maintenance I can see is that all new topics are pushed into the
> General > Other category as the script can't differentiate what category it
> should in fact belong to, once the topic is moved it will stay there. This
> shouldn't be a real problem as theres not many new topics being created on
> any given day.
>

Elliot,

That's actually some good work you've done there!  I didn't know phpBB
supported bidirectional mailing list support.

I think, however, that having such a forum at a .com.au address isn't
particularly desirable, as it implies it's regional.  If others are happy
for you to work on this, it might be an idea to speak to the existing web
team to see if they are able to provide you with pointers and possibly
resources to get such a thing up and running.  It would be nice, for
example, to have forums.postgresql.org set up.

A few points though.  I think we'd need to disable smileys, bbcode, any form
of rich text formatting, flash or embedded images.  In short, plain text
only, which is the policy on the mailing list.  I think it would be more
useful if each forum directly corresponded to a mailing list too.  What I
mean is that if there was a forum on the site which didn't match to a
mailing list, only forum users could use it.

Also, if someone registers on the forum, do they get a major domo
registration email?  And if so, would this be set to receive no emails upon
registration?  I'm not clear as to how this step would work because, at the
moment, mailing list subscribers have to subscribe on a list-by-list basis.
So registration to the forum site wouldn't necessarily mean they'd want to
join any particular mailing list.  Similarly, could they unregister easily?
And anyone who attempts to post to a mailing list they aren't subscribed to
requires moderation, so we don't wish to exacerbate this.

But that's a nice start. :)

Cheers

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Re: [GENERAL] ipv4 data type does not allow to use % assubnet mask delimiter

[Andrus]

Possibly someday the standard will actually standardize the things,
and then maybe we can work with them usefully ...



From http://tools.ietf.org/html/rfc4007#section-11.2


implementation SHOULD
  support the following format:

   %

  where

  is a literal IPv6 address,

  is a string identifying the zone of the address, and

 `%' is a delimiter character to distinguish between  and
 .

so this is clearly standardized.
It should be mentioned in docs that PostgreSql does not support ipv6 and 
attempt to use will cause crash.


Andrus. 



--
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] Why facebook used mysql ?

[Fredric Fredricson]

On 11/09/2010 06:01 PM, Andy wrote:

MySQL is GPL'd, just like Linux is.

Well it is and it isn't. A couple of years ago when I was involved with 
choosing DB for a (proprietary) application we could not figure MySQLs 
license out. It was GPL'd but at the same time if you wanted to use it 
commercially you had to pay. As far as we could tell you should not need 
LGPL to make calls to a database.


We choose Postgresql based on features (MySQL did not have stored 
procedures back then) so we never resolved the license "dilemma" but it 
sure looked strange.


/Fredric

PS. The license cost as such would not be prohibitive but in our case we 
did not want the administration that follows licenses. The application 
was a industrial machine and there where several thousands already out 
that that would be upgraded and a couple of hundreds a year produced.
<>
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] POstgresql 9.0.1 FTS NOTICE: word is too long to be indexed

[AI Rumman]

I getting error in Postgresql 9.0.1.
select description ,to_tsvector(description) from crm where crmid = 1;
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.

Any idea pelase how to solve it.

[GENERAL] Postgres forums ... take 2

[Elliot Chance]

Hi again,

I've taken in all the feedback about http://forums.postgresql.com.au and the 
general consensus is that nobody wants a separate entity - a few people 
mentioned that if it was interoperable with the mailing list that it would be 
better. So I did.

The concept goes like this;
1. Any posts to the general mailing list will be picked up by the forum, the 
email data is converted and posted on the forum, for example;
http://forums.postgresql.com.au/viewtopic.php?f=30&t=39
2. Any reply to the forum will do the reverse and send the post back to the 
mailing list as a reply.

This means the forum can be fully controlled through the mailing list without 
the need to visit the forums directly. However those people who prefer to use a 
forum interface can, and those messages are relayed back through the mailing 
list to get answered.

Step 1 is complete (might need a little tweaking, i've only tried it with a 
couple of topics.) Step 2 I haven't begun - wanted to get some more feedback.

All the forum topics and posts are back-dated to match the emails, which means 
it would be *theoretically* possible to load in the entire postgres mailing 
list archive but I wouldn't do that on a server that couldn't handle that much 
data.

Disclaimer about user names:
User names are registered automatically based on the unique email address of 
the person emailing the response. Each user is given a random 8 character 
password. You can use the recover password page to login to your account and 
change your user name to anything you want, the only important thing is that 
your email address matches.

I know this is a sensitive issue with some people, i've made sure no 
information is posted thats not already currently being indexed by google.

The only maintenance I can see is that all new topics are pushed into the 
General > Other category as the script can't differentiate what category it 
should in fact belong to, once the topic is moved it will stay there. This 
shouldn't be a real problem as theres not many new topics being created on any 
given day.

Cheers,
Elliot
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general