PostgreSQL: Sep 9, 2008

Tuesday, September 9, 2008

Re: [GENERAL] Various intermittent bugs/instability - how to debug?

On Tue, Sep 9, 2008 at 8:50 AM, Frederik Ramm
<frederik.ramm@geofabrik.de> wrote:
> Dear PostgreSQL community,
>
> I hope you can help me with a problem I'm having - I'm stuck and don't
> know how to debug this further.
>
> I have a rather large nightly process that imports a lot of data from the
> OpenStreetMap project into a PostGIS database, then proceeds doing all sorts
> of things - creating spatial indexes, computing bounding boxes, doing
> simplification of geometries, that kind of stuff. The whole job usually
> takes about five hours.
>
> I'm running this on a Quad-Core Linux (Ubuntu, PostgreSQL 8.3) machine with
> 8 GB RAM.
>
> Every other night, the process aborts with some strange error message, and
> never at the same position:
>
> ERROR: invalid page header in block 166406 of relation "node_tags"
>
> ERROR: could not open segment 2 of relation 1663/24253056/24253895 (target
> block 1421295656): No such file or directory
>
> ERROR: Unknown geometry type: 10
>
> When I continue the process after the failure, it will usually work.
>
> I know you all think "hardware problem" now. Of course this was my first
> guess as well. I ran a memory test for a night, no results; I downgraded do
> "failsafe defaults" for all BIOS timings, again no change. Ran "cpuburn" and
> all sorts of other things to grill the hardware - nothing.

You definitely are suffering from db corruption, and given the number
and differing type of errors, it would seem unlikely that pgsql has a
load of bugs only you are seeing. OTOH, if the bug is hidden deep in
postgis or something, then who knows...

I'd definitely run something like bonnie++ for a few days and see if
it gets HD errors or not.

And definitely run memtest86 for a day or so and make sure you're not
getting any errors there.

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

Re: [pgsql-www] Wiki CSS

On Tue, Sep 09, 2008 at 04:58:37PM +0100, Dave Page wrote:
> On Tue, Sep 9, 2008 at 4:51 PM, David Fetter <david@fetter.org> wrote:
> > On Tue, Sep 09, 2008 at 04:43:28PM +0100, Dave Page wrote:
> >> On Tue, Sep 9, 2008 at 4:35 PM, David Fetter <david@fetter.org> wrote:
> >> > Folks,
> >> >
> >> > Could whoever has the ability mark MediaWiki:Common.css in such
> >> > a way as I can write to it? My username is dfetter.
> >>
> >> Why?
> >
> > So I can fix up how it handles nested lists :)
>
> Why, what's wrong with them? They work fine for me.
>
> > Anyhow, it's changed, and I'm fixing it up as I write this.
>
> No, it's been changed back. We don't hand out sysop/root privs to
> anyone without good and justified reason.

Slow down, there, cowboy. I'm trying to put up some of the SQL
standard, and the usual markup won't work, as it involves lists nested
3 deep. How about reverting it again and not assuming I'm out to
destroy the site? I've got plenty of accesses a lot more privileged
than that, and I stand on my track record with same.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

Re: [GENERAL] 3 postgres processes

--- On Tue, 9/9/08, Markova, Nina <nmarkova@NRCan.gc.ca> wrote:

> From: Markova, Nina <nmarkova@NRCan.gc.ca>
> Subject: [GENERAL] 3 postgres processes
> To: pgsql-general@postgresql.org
> Date: Tuesday, September 9, 2008, 2:50 PM
> Hi,
>
> After issuning initdb and starting the postgres server, I
> checked for processes running, expecting to see just one:
>
> postgres 4926 4924 0 14:44:52 ? 0:00
> /usr/postgres/8.2/bin/postgres
> postgres 4924 1 0 14:44:52 pts/1 0:00
> /usr/postgres/8.2/bin/postgres
> postgres 4929 4850 0 14:44:56 pts/1 0:00 grep
> postgres
> postgres 4928 4850 0 14:44:56 pts/1 0:00 ps -ef
> postgres 4927 4924 0 14:44:52 ? 0:00
> /usr/postgres/8.2/bin/postgres
>
>
> Only one of them is in postmaster.pid. What the other ones
> are for?
>
> more /pg_data/postmaster.pid
> 4924
> /pg_data
> 5432001 31
>
>
> Thanks,
>

use ps auxw to see more details of the process

i think the processes are one of this

writer process
wal writer process
autovacuum launcher process
stats collector process

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

Re: [pgsql-www] Wiki CSS

On Tue, Sep 9, 2008 at 4:51 PM, David Fetter <david@fetter.org> wrote:
> On Tue, Sep 09, 2008 at 04:43:28PM +0100, Dave Page wrote:
>> On Tue, Sep 9, 2008 at 4:35 PM, David Fetter <david@fetter.org> wrote:
>> > Folks,
>> >
>> > Could whoever has the ability mark MediaWiki:Common.css in such a
>> > way as I can write to it? My username is dfetter.
>>
>> Why?
>
> So I can fix up how it handles nested lists :)

Why, what's wrong with them? They work fine for me.

> Anyhow, it's changed, and I'm fixing it up as I write this.

No, it's been changed back. We don't hand out sysop/root privs to
anyone without good and justified reason.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

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

Re: [pgsql-www] Wiki CSS

On Tue, Sep 9, 2008 at 4:49 PM, Greg Sabino Mullane <greg@turnstep.com> wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
>> Could whoever has the ability mark MediaWiki:Common.css in such a way
>> as I can write to it? My username is dfetter.
>
> Done.

Err, hang on. why does David need to edit the css, and can it be done
without handing out sysop privileges?

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

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

Re: [pgsql-www] Wiki CSS

On Tue, Sep 09, 2008 at 04:43:28PM +0100, Dave Page wrote:
> On Tue, Sep 9, 2008 at 4:35 PM, David Fetter <david@fetter.org> wrote:
> > Folks,
> >
> > Could whoever has the ability mark MediaWiki:Common.css in such a
> > way as I can write to it? My username is dfetter.
>
> Why?

So I can fix up how it handles nested lists :)

Anyhow, it's changed, and I'm fixing it up as I write this.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

Re: [GENERAL] PostgreSQL TPC-H test result?

On Tue, Sep 09, 2008 at 10:06:01PM +0800, Amber wrote:
> Yes, we don't care about the performance results, but we do care about the point that PostgreSQL can't give the correct results of TPC-H queries.
>

I have never heard a reputable source claim this. I have grave doubts
about their claim: they don't specify what implementation of TPC-H
they use. They don't actually have the right, AIUI, to claim they
tested under TPC-H, since their results aren't listed anywhere on
http://www.tpc.org/tpch/results/tpch_results.asp?orderby=dbms. It
could well be that they made up something that kinda does something
like TPC-H, tailored to how their database works, and then claimed
others can't do the job. That's nice marketing material, but it's not
a meaningful test result.

Without access to the methodology, you should be wary of accepting any
of the conclusions.

There is, I understand, an implementation of something like TPC-H that
you could use to test it yourself. http://osdldbt.sourceforge.net/.
DBT-3 is supposed to be that workload. Please note that the license
does not allow you to publish competitive tests for marketing
reasons. but you could see for yourself whether the claim is true
that way.

A
--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.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: [pgsql-www] Wiki CSS

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> Could whoever has the ability mark MediaWiki:Common.css in such a way
> as I can write to it? My username is dfetter.

Done.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200809091144
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkjGme4ACgkQvJuQZxSWSshDRgCg9qvzgR3mkkuLYqNjAPT4uItz
PWEAn2vuVdIzNKwnEecDR/sAtWFAGvW+
=u4YL
-----END PGP SIGNATURE-----

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

Re: [pgsql-www] Colons in wiki page titles, and commit fest page names

On Wed, Apr 23, 2008 at 12:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> On Tue, 22 Apr 2008, Brendan Jurd wrote:
>> So, what naming scheme should we go with? I guess I would lean
>> towards "CommitFest YYYY-MM" for easy lexical sorting.
>
> I think the year/month naming idea is fine, at least for as far ahead
> as we can see at the moment. There's no intention of allowing the
> commit fests to slip.
>

The YYYY-MM naming convention seems to have been a success, so while I
was doing some cleanup on the wiki this evening I decided to go ahead
and rename the CommitFest pages by changing the colons to spaces. For
example,

CommitFest:2008-09 => CommitFest 2008-09

In each case, there is a redirect page left behind for those who have
static bookmarks to particular commitfests.

Meanwhile, I've manually resolved double-redirects like the old
CommitFest:{March,May,July} pages and updated all the pages which
reference the commitfest pages directly.

Apart from the orphaned redirects, I don't think we have any pages
left with "CommitFest:" in the name.

Cheers,
BJ

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

Re: [pgsql-www] Wiki CSS

On Tue, Sep 9, 2008 at 4:35 PM, David Fetter <david@fetter.org> wrote:
> Folks,
>
> Could whoever has the ability mark MediaWiki:Common.css in such a way
> as I can write to it? My username is dfetter.

Why?

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

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

Re: [HACKERS] Synchronous Log Shipping Replication

On Tue, 2008-09-09 at 18:26 +0300, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > Don't understand. I am referring to the logic at the top of
> > AdvanceXLInsertBuffer(). We would need to wait for all people reading
> > the contents of wal_buffers.
>
> Oh, I see.
>
> If a slave falls behind, how does it catch up?

That is the right question.

> I guess you're saying
> that it can't fall behind, because the master will block before that
> happens. Also in asynchronous replication?

Yes, it can fall behind in async mode. sysadmin must not let it.

> And what about when the slave
> is first set up, and needs to catch up with the master?

We need an initial joining mode while they "match speed". We must allow
for the case where the standby has been recycled, or the network has
been down for a medium-long period of time.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

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

[GENERAL] PostgreSQL process architecture question.

We know PostgreSQL uses one dedicated server process to serve one client connection, what we want to know is whether PostgreSQL use multiple threads inside agents processes to take advantage of multiple CPUs. In our site we have only a few concurrent connections, so what occurs inside agent process is very important to us.

[pgsql-www] Wiki CSS

Folks,

Could whoever has the ability mark MediaWiki:Common.css in such a way
as I can write to it? My username is dfetter.

Thanks :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

Re: [GENERAL] PostgreSQL TPC-H test result?

On Tue, Sep 9, 2008 at 10:06 AM, Amber <guxiaobo1982@hotmail.com> wrote:
> Yes, we don't care about the performance results, but we do care about the point that PostgreSQL can't give the correct results of TPC-H queries.

PostgreSQL, at least in terms of the open source databases, is
probably your best bet if you are all concerned about correctness. Do
not give any credence to a vendor published benchmark unless the test
is published and can be independently verifed.

merlin

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

Re: [HACKERS] Synchronous Log Shipping Replication

Simon Riggs wrote:
> Don't understand. I am referring to the logic at the top of
> AdvanceXLInsertBuffer(). We would need to wait for all people reading
> the contents of wal_buffers.

Oh, I see.

If a slave falls behind, how does it catch up? I guess you're saying
that it can't fall behind, because the master will block before that
happens. Also in asynchronous replication? And what about when the slave
is first set up, and needs to catch up with the master?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

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

Re: [GENERAL] PostgreSQL TPC-H test result?

On Tue, Sep 9, 2008 at 7:06 AM, Amber <guxiaobo1982@hotmail.com> wrote:
> Yes, we don't care about the performance results, but we do care about the point that PostgreSQL can't give the correct results of TPC-H queries.

It would be nice to know about the data, queries, and the expected
results of their tests just so we could see this for ourselves.

--
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: [HACKERS] Verbosity of Function Return Type Checks

Volkan YAZICI wrote:
> On Mon, 8 Sep 2008, Alvaro Herrera <alvherre@commandprompt.com> writes:
> >> Modified as you suggested. BTW, will there be a similar i18n scenario
> >> for "dropped column" you mentioned below?
> >
> > Yes, you need _() around those too.
>
> For this purpose, I introduced a dropped_column_type variable in
> validate_tupdesc_compat() function:
>
> const char dropped_column_type[] = _("n/a (dropped column)");
>
> And used this in below fashion:
>
> OidIsValid(returned->attrs[i]->atttypid)
> ? format_type_be(returned->attrs[i]->atttypid)
> : dropped_column_type

I changed it to gettext_noop("the text") and _(dropped_column_type) in
errdetail, and committed it.

I'd still like to have a better way to word the message, and maybe have
this error appear in a regression test somewhere at least once ...

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [PATCHES] [PgFoundry] Unsigned Data Types [1 of 2]

Hello Tom,

On Tue, Sep 9, 2008 at 5:11 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Ryan Bradetich" <rbradetich@gmail.com> writes:
>> I am assuming you are seeing this error in the uint_test1.sql:
>> ERROR: could not find hash function for hash operator 16524
>> I can bypass the error in uint_test1.sql by disabling the hash joins.
>> I am going to dig in and figure out why the hashjoin operation is broken.
>
> Well, the cause of that one would've been marking an operator as HASHES
> without providing a hash opclass to back it up.

Actually I did provide a hash operator class in the patch:

CREATE OPERATOR CLASS uint4_ops
DEFAULT FOR TYPE uint4 USING HASH AS
OPERATOR 1 =,
FUNCTION 1 hashuint4(uint4);

This only provides the operator class for uint4 eq uint4. Jaime's test case
was uint4 eq int4 which I did not have an operator class for. I was able to fix
this test case by adding the int4 eq uint4 operator like this:

CREATE OPERATOR CLASS uint4_ops
DEFAULT FOR TYPE uint4 USING HASH FAMILY unsigned_integer_ops AS
OPERATOR 1 =,
FUNCTION 1 hashuint4(uint4);

ALTER OPERATOR FAMILY unsigned_integer_ops USING HASH ADD
OPERATOR 1 = (int4, uint4),
FUNCTION 1 hashuint4_from_int4(int4);

I tested uint4 eq int4 and int4 eq uint4 and this one additional hash operator
handles them both.

[NOTE: The other solution was to cast foo to the uint4 data type.]

I am working on adding support for the int4 eq uint2 and int4 eq uint1 cases
as well. I am running into an error when I add support for these hash operator
classes that I am not quite ready to post about yet (I want to look a
bit more first).

> IIRC the test case involved ">"? That shouldn't even be marked HASHES
> anyway ...

That error was in the uint_test2 test case Jaime provided.

This test case looks like:

drop table if exists t1_uint4;
create table t1_uint4 (f1 uint4 primary key);
insert into t1_uint4 select generate_series(1, 255);
analyze t1_uint4;
select * from t1_uint4, generate_series(1, 10) as foo where t1_uint4.f1 = foo;

Thanks,

- Ryan

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

[COMMITTERS] pgsql: Improve plpgsql's ability to report tuple incompatibility

Log Message:
-----------
Improve plpgsql's ability to report tuple incompatibility problems.

Volkan YAZICI

Modified Files:
--------------
pgsql/src/pl/plpgsql/src:
pl_exec.c (r1.219 -> r1.220)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/pl/plpgsql/src/pl_exec.c?r1=1.219&r2=1.220)

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

Re: [HACKERS] Common Table Expressions (WITH RECURSIVE) patch

> > * Aggregates allowed:
> >
> > with recursive foo(i) as
> > (values(1)
> > union all
> > select max(i)+1 from foo where i < 10)
> > select * from foo;
> >
> > Aggregates should be blocked according to the standard.
> > Also, causes an infinite loop. This should be fixed for 8.4.
>
> I will try to fix this.

We already reject:

select max(i) from foo where i < 10)

But max(i)+1 seems to slip the check. I looked into this I found the
patch tried to detect the case before analyzing(see
parser/parse_cte.c) which is not a right thing I think.

I think we could detect the case by adding more checking in
parseCheckAggregates():

/*
* Check if there's aggregate function in a recursive term.
*/
foreach(l, qry->rtable)
{
RangeTblEntry *rte = (RangeTblEntry *) lfirst(l);

if (qry->hasAggs && rte->rtekind == RTE_RECURSIVE &&
rte->self_reference)
{
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("aggregate functions in a recursive term not allowed")));
}
}

What do you think?
--
Tatsuo Ishii
SRA OSS, Inc. Japan

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

Re: [HACKERS] Synchronous Log Shipping Replication

On Tue, 2008-09-09 at 17:17 +0300, Heikki Linnakangas wrote:
> Tom Lane wrote:
> > Simon Riggs <simon@2ndQuadrant.com> writes:
> >> On Tue, 2008-09-09 at 08:24 -0400, Tom Lane wrote:
> >>> "Agreed"? That last restriction is a deal-breaker.
> >
> >> OK, I should have said *if wal_buffers are full* XLogInsert() cannot
> >> advance to a new page while we are waiting to send or write. So I don't
> >> think its a deal breaker.
> >
> > Oh, OK, that's obvious --- there's no place to put more data.
>
> Each WAL sender can keep at most one page locked at a time, right? So,
> that should never happen if wal_buffers > 1 + n_wal_senders.

Don't understand. I am referring to the logic at the top of
AdvanceXLInsertBuffer(). We would need to wait for all people reading
the contents of wal_buffers.

Currently, there is no page locking on the WAL buffers, though I have
suggested some for increasing XLogInsert() performance.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

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

Re: [GENERAL] Various intermittent bugs/instability - how to debug?

Hi,

Joshua D. Drake wrote:
> Hard drives. You said you replaced the machines and ran cpu burn etc...
> You didn't say you checked the hard drives (or replaced them).

The new machine has new hard drives. On the old machine I had a hardware
RAID5 array made up of 6x500 GB SATA with an Areca RAID controller and
Linux LVM; the new machine has 3x750 GB SATA plugged directly into the
main board (no RAID, no LVM). So even the drives are quite different,
and it's different drivers in both cases...

Bye
Frederik

--
Frederik Ramm www.geofabrik.de
Geofabrik GmbH Handelsregister: HRB Mannheim 703657
Rueppurrer Strasse 4 Geschaeftsfuehrung: Frederik Ramm
76137 Karlsruhe Tel: 0721-1803560-0
frederik.ramm@geofabrik.de Fax: 0721-1803560-9

--
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] Automated Backup On Windows

justin wrote:
> how does this page look to you guys/gals. I have never added anything
> to a WIKI before so any comments?? I left the original author stuff
> untouched my edit is appended to the top

Please use subsections to separate both methods.

Also it'd be good to mention that PGPASSWORD and .pgpass work with
either method ...

How about adding the page to the Windows category?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: [HACKERS] Common Table Expressions (WITH RECURSIVE) patch

> Hello
>
> 2008/9/9 Tatsuo Ishii <ishii@postgresql.org>:
> >> On Tue, 2008-09-09 at 13:45 +0900, Tatsuo Ishii wrote:
> >> > Thanks for the review.
> >> >
> >> > > The standard specifies that non-recursive WITH should be evaluated
> >> > > once.
> >> >
> >> > What shall we do? I don't think there's a easy way to fix this. Maybe
> >> > we should not allow WITH clause without RECURISVE?
> >>
> >> My interpretation of 7.13: General Rules: 2.b is that it should be
> >> single evaluation, even if RECURSIVE is present.
> >>
> >> The previous discussion was here:
> >>
> >> http://archives.postgresql.org/pgsql-hackers/2008-07/msg01292.php
> >>
> >> The important arguments in the thread seemed to be:
> >>
> >> 1. People will generally expect single evaluation, so might be
> >> disappointed if they can't use this feature for that purpose.
> >>
> >> 2. It's a spec violation in the case of volatile functions.
> >>
> >> 3. "I think this is a "must fix" because of the point about volatile
> >> functions --- changing it later will result in user-visible semantics
> >> changes, so we have to get it right the first time."
> >>
> >> I don't entirely agree with #3. It is user-visible, but only in the
> >> sense that someone is depending on undocumented multiple-evaluation
> >> behavior.
> >>
> >> Tom Lane said that multiple evaluation is grounds for rejection:
> >> http://archives.postgresql.org/pgsql-hackers/2008-07/msg01318.php
> >>
> >> Is there hope of correcting this before November?
> >
> > According to Tom, to implement "single evaluation" we need to make big
> > infrastructure enhancement which is likely slip the schedule for 8.4
> > release which Tom does not want.
>
> why? why don't use a materialisation?

See:
http://archives.postgresql.org/pgsql-hackers/2008-07/msg01292.php

> > So as long as Tom and other people think that is a "must fix", there
> > seems no hope probably.
> >
> > Anyway I will continue to work on existing patches...
> > --
>
> I would to see your patch in core early. I am working on grouping sets
> and I cannot finish my patch before your patch will be commited.
>
> Regards
> Pavel Stehule
>
> > Tatsuo Ishii
> > SRA OSS, Inc. Japan
> >
> >> > I will try to fix this. However detecting the query being not a
> >> > non-linear one is not so easy.
> >>
> >> If we don't allow mutual recursion, the only kind of non-linear
> >> recursion that might exist would be multiple references to the same
> >> recursive query name in a recursive query, is that correct?
> >>
> >> > > * DISTINCT should supress duplicates:
> >> > >
> >> > > with recursive foo(i) as
> >> > > (select distinct * from (values(1),(2)) t
> >> > > union all
> >> > > select distinct i+1 from foo where i < 10)
> >> > > select * from foo;
> >> > >
> >> > > This outputs a lot of duplicates, but they should be supressed
> >> > > according to the standard. This query is essentially the same as
> >> > > supporting UNION for recursive queries, so we should either fix both for
> >> > > 8.4 or block both for consistency.
> >> >
> >> > I'm not sure if it's possible to fix this. Will look into.
> >> >
> >>
> >> Can't we just reject queries with top-level DISTINCT, similar to how
> >> UNION is rejected?
> >>
> >> > > * outer joins on a recursive reference should be blocked:
> >> > >
> >> > > with recursive foo(i) as
> >> > > (values(1)
> >> > > union all
> >> > > select i+1 from foo left join (values(1)) t on (i=column1))
> >> > > select * from foo;
> >> > >
> >> > > Causes an infinite loop, but the standard says using an outer join
> >> > > in this situation should be prohibited. This should be fixed for 8.4.
> >> >
> >> > Not an issue, I think.
> >>
> >> Agreed, Andrew Gierth corrected me here.
> >>
> >> Regards,
> >> Jeff Davis
> >>
> >>
> >> --
> >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-hackers
> >
> > --
> > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-hackers
> >

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

Re: [GENERAL] Various intermittent bugs/instability - how to debug?

Frederik Ramm wrote:
> Dear PostgreSQL community,

> Every other night, the process aborts with some strange error message,
> and never at the same position:
>
> ERROR: invalid page header in block 166406 of relation "node_tags"
>
> ERROR: could not open segment 2 of relation 1663/24253056/24253895
> (target block 1421295656): No such file or directory

> What could I do to have a better chance of reproducing the error and
> ultimately identifying the component responsible? Is there some kind of
> "PostgresSQL load test", something like "cpuburn" for PostgreSQL?
>
> Have there been other reports of intermittent problems like mine, and
> does anybody have any blind guesses...?

Hard drives. You said you replaced the machines and ran cpu burn etc...
You didn't say you checked the hard drives (or replaced them).

Joshua D. Drake

>
> Thanks
> Frederik
>

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

Re: [HACKERS] Common Table Expressions (WITH RECURSIVE) patch

See:
http://archives.postgresql.org/pgsql-hackers/2008-07/msg01292.php

> >
> > So as long as Tom and other people think that is a "must fix", there
> > seems no hope probably.
> >
> > Anyway I will continue to work on existing patches...
> > --
>
> I would to see your patch in core early. I am working on grouping sets
> and I cannot finish my patch before your patch will be commited.
>
> Regards
> Pavel Stehule
>
> > Tatsuo Ishii
> > SRA OSS, Inc. Japan
> >
> >> > I will try to fix this. However detecting the query being not a
> >> > non-linear one is not so easy.
> >>
> >> If we don't allow mutual recursion, the only kind of non-linear
> >> recursion that might exist would be multiple references to the same
> >> recursive query name in a recursive query, is that correct?
> >>
> >> > > * DISTINCT should supress duplicates:
> >> > >
> >> > > with recursive foo(i) as
> >> > > (select distinct * from (values(1),(2)) t
> >> > > union all
> >> > > select distinct i+1 from foo where i < 10)
> >> > > select * from foo;
> >> > >
> >> > > This outputs a lot of duplicates, but they should be supressed
> >> > > according to the standard. This query is essentially the same as
> >> > > supporting UNION for recursive queries, so we should either fix both for
> >> > > 8.4 or block both for consistency.
> >> >
> >> > I'm not sure if it's possible to fix this. Will look into.
> >> >
> >>
> >> Can't we just reject queries with top-level DISTINCT, similar to how
> >> UNION is rejected?
> >>
> >> > > * outer joins on a recursive reference should be blocked:
> >> > >
> >> > > with recursive foo(i) as
> >> > > (values(1)
> >> > > union all
> >> > > select i+1 from foo left join (values(1)) t on (i=column1))
> >> > > select * from foo;
> >> > >
> >> > > Causes an infinite loop, but the standard says using an outer join
> >> > > in this situation should be prohibited. This should be fixed for 8.4.
> >> >
> >> > Not an issue, I think.
> >>
> >> Agreed, Andrew Gierth corrected me here.
> >>
> >> Regards,
> >> Jeff Davis
> >>
> >>
> >> --
> >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-hackers
> >
> > --
> > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-hackers
> >

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

Re: [pgsql-es-ayuda] Lectura del catalogo

Arturo Rossodivita escribió:

> Hola Alvaro Gracias por la respuesta, sabes que no encuentro el core por
> ningun lado y cuando levanto el postmaster como me dices me lanza lo
> siguiente:
>
> postgres@BD-desktop01:/usr/local/pgsql$ bin/postmaster -D data/ ulimit -c
> unlimited
> FATAL: -c unlimited requires a value

No, es asi:

$ ulimit -c unlimited
$ bin/postmaster -D data

(ulimit es una orden del shell)

--
Alvaro Herrera Developer, http://www.PostgreSQL.org/
Thou shalt check the array bounds of all strings (indeed, all arrays), for
surely where thou typest "foo" someone someday shall type
"supercalifragilisticexpialidocious" (5th Commandment for C programmers)
--
TIP 1: para suscribirte y desuscribirte, visita http://archives.postgresql.org/pgsql-es-ayuda

[GENERAL] Question about indexes

Hi all.
I usually create indexes accordingly to the queries used in my software.
This means the more often than not I have composited indexes over more than one column.
What'd be in PGSQL (v8.3+) the pros and cons of having instead only one-column indexes?
Thanks in advance.

RMP.

Re: [GENERAL] Install Postgres on a SAN volume?

On Tuesday 09 September 2008 04:37:09 Magnus Hagander wrote:
> Greg Smith wrote:
> > On Tue, 9 Sep 2008, Magnus Hagander wrote:
> >> As long as your SAN guarantees an atomic snapshot of all your data
> >> (which every SAN I've ever heard of guarantees if you're on a single
> >> volume - entry level SANs often don't have the functionality to do
> >> multi-volume atomic snapshots, though), you don't need to set up PITR
> >> for simple backups
> >
> > It's all those ifs in there that leave me still recommending it. It's
> > certainly possible to get a consistant snapshot with the right hardware
> > and setup. What concerns me about recommending that without a long list
> > of caveats is the kinds of corruption you'd get if all those conditions
> > aren't perfect will of course not ever happen during testing. Murphy
> > says that it will happen only when you find yourself really needing that
> > snapshot to work one day.
>
> Well, I agree one should be careful, but I don't see the risk if you
> just change all those ifs into a single one, which is "if all your data
> *and* WAL is on the same SAN LUN".
>
> (heck, you don't need hardware to do it, you can do software snapshot
> just fine - as long as you keep all your stuff on the same mountpoint
> there as well)
>

That's pretty key, but there can be advantages to doing it using the pitr
tools, and I think in most cases it would be hard to argue it isn't safer.

As a counter example to theo's zfs based post, I posted a linux/lvm script
that can work as the basis of a simple snapshot backup tool, available at
http://people.planetpostgresql.org/xzilla/index.php?/archives/344-ossdb-snapshot,-lvm-database-snapshot-tool.html

And yes, I prefer working on the zfs based one :-)

--
Robert Treat
http://www.omniti.com
Database: Scalability: Consulting:

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

[GENERAL] Various intermittent bugs/instability - how to debug?

Dear PostgreSQL community,

I hope you can help me with a problem I'm having - I'm stuck and
don't know how to debug this further.

I have a rather large nightly process that imports a lot of data from
the OpenStreetMap project into a PostGIS database, then proceeds doing
all sorts of things - creating spatial indexes, computing bounding
boxes, doing simplification of geometries, that kind of stuff. The whole
job usually takes about five hours.

I'm running this on a Quad-Core Linux (Ubuntu, PostgreSQL 8.3) machine
with 8 GB RAM.

Every other night, the process aborts with some strange error message,
and never at the same position:

ERROR: invalid page header in block 166406 of relation "node_tags"

ERROR: could not open segment 2 of relation 1663/24253056/24253895
(target block 1421295656): No such file or directory

ERROR: Unknown geometry type: 10

When I continue the process after the failure, it will usually work.

I know you all think "hardware problem" now. Of course this was my first
guess as well. I ran a memory test for a night, no results; I downgraded
do "failsafe defaults" for all BIOS timings, again no change. Ran
"cpuburn" and all sorts of other things to grill the hardware - nothing.

Then I bought an entirely new machine; similar setup, but using a
Gigabyte instead of Asus mainboard, different chipset, slightly faster
Quad-Core processor, and again 8 GB RAM and Ubuntu "Hardy" with
PostgresSQL 8.3 and matching PostGIS.

Believe it or not, this machine shows the *same* problems. It is not
100% reproducible, sometimes the job works fully, but every other day it
just breaks down with one of the funny messages like above. No memtest
errors here either.

Both machines are "consumer" quality, i.e. normal Intel processors and
not the "server" (Xeon) stock.

I am at a loss - how can I proceed? This looks like a hardware problem
alright, but so simliar problems on two so different machines? Is there
something wrong with Intel's Quad-Core CPUs?

What could I do to have a better chance of reproducing the error and
ultimately identifying the component responsible? Is there some kind of
"PostgresSQL load test", something like "cpuburn" for PostgreSQL?

Have there been other reports of intermittent problems like mine, and
does anybody have any blind guesses...?

Thanks
Frederik

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

[GENERAL] 3 postgres processes

Hi,

After issuning initdb and starting the postgres server, I checked for processes running, expecting to see just one:

postgres 4926 4924 0 14:44:52 ? 0:00 /usr/postgres/8.2/bin/postgres
postgres 4924 1 0 14:44:52 pts/1 0:00 /usr/postgres/8.2/bin/postgres
postgres 4929 4850 0 14:44:56 pts/1 0:00 grep postgres
postgres 4928 4850 0 14:44:56 pts/1 0:00 ps -ef
postgres 4927 4924 0 14:44:52 ? 0:00 /usr/postgres/8.2/bin/postgres

Only one of them is in postmaster.pid. What the other ones are for?

more /pg_data/postmaster.pid
4924
/pg_data
5432001 31

Thanks,

-----'\/\/\/`v^v^v^v^v^v^v----------------------------------------------------------------------------
Nina Markova,
Database Analyst / Analyst de base de données
(613) 992-3753 facsimile / télécopieur (613) 992-8836
nmarkova@nrcan.gc.ca

Geological Survey of Canada / Commission géologique du Canada
Natural Resources Canada / Ressources naturelles Canada
Government of Canada / Gouvernement du Canada
-----'\/\/\/`v^v^v^v^v^v^v----------------------------------------------------------------------------

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

Re: [HACKERS] Common Table Expressions (WITH RECURSIVE) patch

Hello

2008/9/9 Tatsuo Ishii <ishii@postgresql.org>:
>> On Tue, 2008-09-09 at 13:45 +0900, Tatsuo Ishii wrote:
>> > Thanks for the review.
>> >
>> > > The standard specifies that non-recursive WITH should be evaluated
>> > > once.
>> >
>> > What shall we do? I don't think there's a easy way to fix this. Maybe
>> > we should not allow WITH clause without RECURISVE?
>>
>> My interpretation of 7.13: General Rules: 2.b is that it should be
>> single evaluation, even if RECURSIVE is present.
>>
>> The previous discussion was here:
>>
>> http://archives.postgresql.org/pgsql-hackers/2008-07/msg01292.php
>>
>> The important arguments in the thread seemed to be:
>>
>> 1. People will generally expect single evaluation, so might be
>> disappointed if they can't use this feature for that purpose.
>>
>> 2. It's a spec violation in the case of volatile functions.
>>
>> 3. "I think this is a "must fix" because of the point about volatile
>> functions --- changing it later will result in user-visible semantics
>> changes, so we have to get it right the first time."
>>
>> I don't entirely agree with #3. It is user-visible, but only in the
>> sense that someone is depending on undocumented multiple-evaluation
>> behavior.
>>
>> Tom Lane said that multiple evaluation is grounds for rejection:
>> http://archives.postgresql.org/pgsql-hackers/2008-07/msg01318.php
>>
>> Is there hope of correcting this before November?
>
> According to Tom, to implement "single evaluation" we need to make big
> infrastructure enhancement which is likely slip the schedule for 8.4
> release which Tom does not want.

why? why don't use a materialisation?

>
> So as long as Tom and other people think that is a "must fix", there
> seems no hope probably.
>
> Anyway I will continue to work on existing patches...
> --

I would to see your patch in core early. I am working on grouping sets
and I cannot finish my patch before your patch will be commited.

Regards
Pavel Stehule

> Tatsuo Ishii
> SRA OSS, Inc. Japan
>
>> > I will try to fix this. However detecting the query being not a
>> > non-linear one is not so easy.
>>
>> If we don't allow mutual recursion, the only kind of non-linear
>> recursion that might exist would be multiple references to the same
>> recursive query name in a recursive query, is that correct?
>>
>> > > * DISTINCT should supress duplicates:
>> > >
>> > > with recursive foo(i) as
>> > > (select distinct * from (values(1),(2)) t
>> > > union all
>> > > select distinct i+1 from foo where i < 10)
>> > > select * from foo;
>> > >
>> > > This outputs a lot of duplicates, but they should be supressed
>> > > according to the standard. This query is essentially the same as
>> > > supporting UNION for recursive queries, so we should either fix both for
>> > > 8.4 or block both for consistency.
>> >
>> > I'm not sure if it's possible to fix this. Will look into.
>> >
>>
>> Can't we just reject queries with top-level DISTINCT, similar to how
>> UNION is rejected?
>>
>> > > * outer joins on a recursive reference should be blocked:
>> > >
>> > > with recursive foo(i) as
>> > > (values(1)
>> > > union all
>> > > select i+1 from foo left join (values(1)) t on (i=column1))
>> > > select * from foo;
>> > >
>> > > Causes an infinite loop, but the standard says using an outer join
>> > > in this situation should be prohibited. This should be fixed for 8.4.
>> >
>> > Not an issue, I think.
>>
>> Agreed, Andrew Gierth corrected me here.
>>
>> Regards,
>> Jeff Davis
>>
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

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

Re: [HACKERS] Common Table Expressions (WITH RECURSIVE) patch

> On Tue, 2008-09-09 at 13:45 +0900, Tatsuo Ishii wrote:
> > Thanks for the review.
> >
> > > The standard specifies that non-recursive WITH should be evaluated
> > > once.
> >
> > What shall we do? I don't think there's a easy way to fix this. Maybe
> > we should not allow WITH clause without RECURISVE?
>
> My interpretation of 7.13: General Rules: 2.b is that it should be
> single evaluation, even if RECURSIVE is present.
>
> The previous discussion was here:
>
> http://archives.postgresql.org/pgsql-hackers/2008-07/msg01292.php
>
> The important arguments in the thread seemed to be:
>
> 1. People will generally expect single evaluation, so might be
> disappointed if they can't use this feature for that purpose.
>
> 2. It's a spec violation in the case of volatile functions.
>
> 3. "I think this is a "must fix" because of the point about volatile
> functions --- changing it later will result in user-visible semantics
> changes, so we have to get it right the first time."
>
> I don't entirely agree with #3. It is user-visible, but only in the
> sense that someone is depending on undocumented multiple-evaluation
> behavior.
>
> Tom Lane said that multiple evaluation is grounds for rejection:
> http://archives.postgresql.org/pgsql-hackers/2008-07/msg01318.php
>
> Is there hope of correcting this before November?

According to Tom, to implement "single evaluation" we need to make big
infrastructure enhancement which is likely slip the schedule for 8.4
release which Tom does not want.

So as long as Tom and other people think that is a "must fix", there
seems no hope probably.

Anyway I will continue to work on existing patches...
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> > I will try to fix this. However detecting the query being not a
> > non-linear one is not so easy.
>
> If we don't allow mutual recursion, the only kind of non-linear
> recursion that might exist would be multiple references to the same
> recursive query name in a recursive query, is that correct?
>
> > > * DISTINCT should supress duplicates:
> > >
> > > with recursive foo(i) as
> > > (select distinct * from (values(1),(2)) t
> > > union all
> > > select distinct i+1 from foo where i < 10)
> > > select * from foo;
> > >
> > > This outputs a lot of duplicates, but they should be supressed
> > > according to the standard. This query is essentially the same as
> > > supporting UNION for recursive queries, so we should either fix both for
> > > 8.4 or block both for consistency.
> >
> > I'm not sure if it's possible to fix this. Will look into.
> >
>
> Can't we just reject queries with top-level DISTINCT, similar to how
> UNION is rejected?
>
> > > * outer joins on a recursive reference should be blocked:
> > >
> > > with recursive foo(i) as
> > > (values(1)
> > > union all
> > > select i+1 from foo left join (values(1)) t on (i=column1))
> > > select * from foo;
> > >
> > > Causes an infinite loop, but the standard says using an outer join
> > > in this situation should be prohibited. This should be fixed for 8.4.
> >
> > Not an issue, I think.
>
> Agreed, Andrew Gierth corrected me here.
>
> Regards,
> Jeff Davis
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

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

Re: [SQL] PL/pgSQL function syntax question?

--- On Mon, 9/8/08, Ruben Gouveia <rubes7202@gmail.com> wrote:

> From: Ruben Gouveia <rubes7202@gmail.com>
> Subject: [SQL] PL/pgSQL function syntax question?
> To: "pgsql-sql" <pgsql-sql@postgresql.org>
> Date: Monday, September 8, 2008, 9:40 PM
> i get the following error when i try and create the
> following function:
>
> Basically, i am trying to have two different dates compared
> and only the
> most recent returned to me. This seems pretty straight
> forward, what I am
> doing wrong here?
>
> create or replace function fcn_pick_date(v_dt date)
> returns date as $$
> DECLARE
> v_dt date;
> BEGIN
> for v_record in select last_periodic, last_boot
> from mediaportal
> loop
> if v_dt >= v_record.last_boot then
> v_dt := v_record.last_periodic;
> else
> v_dt := v_record.last_boot;
> end if;
> end loop;
> return (v_dt);
> END;
> $$ LANGUAGE 'plpgsql';
>
>
> ERROR: loop variable of loop over rows must be record or
> row variable at or
> near "loop" at character 195
>
> ********** Error **********
>
> ERROR: loop variable of loop over rows must be record or
> row variable at or
> near "loop"
> SQL state: 42601
> Character: 195

where you declare v_record?

i think you have declare v_record to record or var array

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

[GENERAL] Problem starting PostgreSQL in Windows 2003

Hi,

After successfully installing PostgreSQL 8.3.3-1 on Windows 2003, the service won’t start. The error I see in the Windows Event Viewer is "could not open process token: error code 5".

Any help is appreciated.

Thanks,

Moshe.

Re: [HACKERS] Synchronous Log Shipping Replication

Tom Lane wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
>> On Tue, 2008-09-09 at 08:24 -0400, Tom Lane wrote:
>>> "Agreed"? That last restriction is a deal-breaker.
>
>> OK, I should have said *if wal_buffers are full* XLogInsert() cannot
>> advance to a new page while we are waiting to send or write. So I don't
>> think its a deal breaker.
>
> Oh, OK, that's obvious --- there's no place to put more data.

Each WAL sender can keep at most one page locked at a time, right? So,
that should never happen if wal_buffers > 1 + n_wal_senders.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

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

Re: [HACKERS] Synchronous Log Shipping Replication

On Tue, 2008-09-09 at 16:05 +0200, Dimitri Fontaine wrote:
> Le mardi 09 septembre 2008, Simon Riggs a écrit :
> > If the WALWriter|Sender is available, it can begin the task immediately.
> > There is no need for it to wait if you want synchronous behaviour.
>
> Ok. Now I'm as lost as anyone with respect to how you get Group Commit :)

OK, sorry. Pls read my reply to Heikki on different subthread of this
topic, he had same question of me.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

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

Re: [pgadmin-support] Why are binary snapshots older than binary production release versions?

> I've uploaded a new win32 build. Use at your own risk :-p

> It's at http://developer.pgadmin.org/snapshots/win32/, and will hit
> the main website in an hour or three.

Thanks. I'll give it a try soon.

Regina
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.

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

Re: [GENERAL] PostgreSQL TPC-H test result?

Yes, we don't care about the performance results, but we do care about the point that PostgreSQL can't give the correct results of TPC-H queries.

--------------------------------------------------
From: "Andrew Sullivan" <ajs@commandprompt.com>
Sent: Tuesday, September 09, 2008 8:39 PM
To: <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] PostgreSQL TPC-H test result?

> On Tue, Sep 09, 2008 at 07:59:49PM +0800, Amber wrote:
>
>> I read something from
>> http://monetdb.cwi.nl/projects/monetdb/SQL/Benchmark/TPCH/index.html
>
> Given that the point of that "study" is to prove something about
> performance, one should be leery of any claims based on an "out of the
> box" comparison. Particularly since the "box" their own product comes
> out of is "compiled from CVS checkout". Their argument seems to be
> that people can learn how to drive CVS and to compile software under
> active development, but can't read the manual that comes with Postgres
> (and a release of Postgres well over a year old, at that).
>
> I didn't get any further in reading the claims, because it's obviously
> nothing more than a marketing effort using the principle that deriding
> everyone else will make them look better. Whether they have a good
> product is another question entirely.
>
> A
> --
> Andrew Sullivan
> ajs@commandprompt.com
> +1 503 667 4564 x104
> http://www.commandprompt.com/
>
> --
> 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: [HACKERS] Synchronous Log Shipping Replication

Le mardi 09 septembre 2008, Simon Riggs a écrit :
> If the WALWriter|Sender is available, it can begin the task immediately.
> There is no need for it to wait if you want synchronous behaviour.

Ok. Now I'm as lost as anyone with respect to how you get Group Commit :)
--
dim

[PERFORM] How to measure IO performance?

Hi out there,

I've some little questions, perhaps you can help me...

At the moment, we're planning our new clustered ERP system which
consists of a java application server and a postgresql database. The
hardware, which is actually used for that system isn't able to handle
the workload (2 Processors, load of 6-8 + 12GB Ram), so it is very, very
slow - and that although we already deactived a lot of stuff we normally
want to do, like a logging and something like that...
We've already choosen some hardware for the new cluster (2x quadcore
Xeon + 64GB Ram should handle that - also in case of failover when one
server has to handle both, applicaton and database! The actual system
can't do that anymore...) but I also have to choose the filesystem
hardware. And that is a problem - we know, that the servers will be fast
enough, but we don't know, how many I/O performance is needed.
At the moment, we're using a scsi based shared storage (HP MSA500G2 -
which contains 10 disks for the database - 8xdata(raid 1+0)+2x
logs(raid1) ) and we often have a lot wait I/O when 200 concurrent users
are working... (when all features we need are activated, that wait I/O
will heavy increase, we think...)
So in order to get rid of wait I/O (as far as possible), we have to
increase the I/O performance. Because of there are a lot storage systems
out there, we need to know how many I/O's per second we actually need.
(To decide, whether a storage systems can handle our load or a bigger
system is required. ) Do you have some suggestions, how to measure that?
Do you have experience with postgres on something like HP MSA2000(10-20
disks) or RamSan systems?

Best regards,
Andre

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

Re: [HACKERS] Synchronous Log Shipping Replication

Hi,

Dimitri Fontaine wrote:
> Exactly the point. The process is now already waiting in all cases, so maybe
> we could just force waiting some WALSender signal before sending the fsync()
> order, so we now have Group Commit.

A single process can only wait on either fsync() or on select(), but not
on both concurrently, because both syscalls are blocking. So mixing
these into a single process is an inherently bad idea due to lack of
parallelism.

I fail to see how log shipping would ease or have any other impact on a
Group Commit feature, which should clearly also work for stand alone
servers, i.e. where there is no WAL sender process.

Regards

Markus Wanner

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

Re: [HACKERS] Synchronous Log Shipping Replication

Hi,

ITAGAKI Takahiro wrote:
> Signals and locking, borrewed from Postgres-R, are now studied
> for the purpose in the log shipping,

Cool. Let me know if you have any questions WRT this imessages stuff.

Regards

Markus Wanner

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

Re: [GENERAL] Postgres in a solaris zone - patch 125077-02 needed

Thanks all.

I have the /usr/lib/libkrb5.so.1 but somehow the checkinstall script
can't find it (it is s10u4). As a result in the non-global zone these
<SUNWpostgr-82-client> and <SUNWpostgr-82-lib> are missing, they exist
in the global zone though.

Nina

-----Original Message-----
From: James Gates [mailto:james.gates@sun.com]
Sent: September 9, 2008 09:43
To: Markova, Nina
Cc: Greg Smith; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Postgres in a solaris zone - patch 125077-02
needed

bug_id 6546052 is already fixed in the PostgreSQL 8.2 packages on the
Solaris S10 update 4 DVD. The "Fixed in Build: s10u4_07" reference in
the bug means that it was fixed in build 7 of S10u4, which is prior to
general release. If the bug was fixed after release of S10u4 (and could
therefore affect you), it would be fixed in a build of a later update.

So I don't know of any other bugs with this particular checkinstall
script. It's looking for the /usr/lib/libkrb5.so.1 library, which was
introduced with Solaris 10 in update 4. The library was also made
available in (sparc) patch 125077-02, which was never released, but
replaced by 120011-09.

So if you are running S10u4 (or later) then you *should* have
/usr/lib/libkrb5.so.1. If you do, maybe there *is* another problem with
the package checkinstall script. Did you get the PostgreSQL 8.2 packages
from somewhere other than the S10u4 DVD?

If you're running S10u3 (or earlier), then you need to install patch
120011-09 (or later) to get /usr/lib/libkrb5.so.1.

Greg Smith wrote:
> On Mon, 8 Sep 2008, Markova, Nina wrote:
>
>> I wasn't able to find 125077-02 patch for SPARC, and contacted SUN
last
>> week - they are very slow.
>
> It wasn't released; according to
> http://www.mail-archive.com/pca@lists.univie.ac.at/msg00199.html that
> fix made its way into 120011-09 instead.
>
> I'm not so sure you actually need this though.
>
http://bugs.opensolaris.org/view_bug.do;jsessionid=1ce76d3cbf20747f874eb
1a300df?bug_id=6546052
> suggests there's actually a bug in how the checkinstall script looks
for
> the file that prevent it from installing in a zone. Looks just like
> your report. That says the s10u4_07 update fixes the problem. I
don't
> know enough about Solaris packaging to suggest exactly what to do
here,
> but that should get you moving in the right direction rather than
> chasing a non-existant patch.
>
> --
> * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore,
MD
>

--
Jim Gates Sun Microsystems
Nashua, NH, USA http://sun.com/postgresql

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

[COMMITTERS] stackbuilder - wizard: The newest Japanese po.

Log Message:
-----------
The newest Japanese po.

Modified Files:
--------------
wizard/i18n/ja_JP:
StackBuilder.po (r1.2 -> r1.3)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/stackbuilder/wizard/i18n/ja_JP/StackBuilder.po.diff?r1=1.2&r2=1.3)

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

Re: [PATCHES] hash index improving v3

On Sat, Sep 06, 2008 at 08:23:05PM -0600, Alex Hunsaker wrote:
> On Sat, Sep 6, 2008 at 1:09 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >For the convenience of anyone intending to test, here is an updated
> >patch against CVS HEAD that incorporates Alex's fix.
>
> Here are the results for a table containing 1 million entries that
> will generate hash collisions. It paints a bad picture for the patch
> but then again im not sure how relevant the issue is. For example
> yesterday I imported a table with 10 million collisions and the create
> index is still running (now at about ~18 hours). Maybe we should warn
> if there are lots of collisions when creating the index and suggest
> you use a btree? Anyway here are the results.
>
I think that the glacial speed for generating a big hash index is
the same problem that the original code faced. Because of the collisions
you are unable to achieve the correct packing that the code assumes.
This results in the splitting/copying of every page in the hash index,
a very slow proposition. I had suggested adding some additional parameters
like fillfactor to accomodate these sorts of situations. Since your test
cuts the effective fill by 2 because of the many collisions, you would
need to adjust that calculation to avoid the tremendous amount of random
I/O generated by that mis-assumption.

> ./pgbench -c1 -n -t10 -f bench_createindex.sql
> cvs head: tps = 0.002169
> v5 : tps = 0.002196
>
> pgbench -c1 -n -t1000 -f bench_bitmap.sql
> cvs head: tps = 24.011871
> v5: tps = 2.543123
>
> pgbench -c1 -n -t1000 -f bench_index.sql
> cvs head: tps = 51.614502
> v5: tps = 3.205542
>
> pgbench -c1 -n -t1000 -f bench_seqscan.sql
> cvs head: tps = 8.553318
> v5: tps = 9.836091
>
> Table created via:
> create table test_hash (num int8);
> ./hash | psql -c 'copy test_hash from stdin;'

It would be useful to have an equivalent test for the hash-only
index without the modified int8 hash function, since that would
be more representative of its performance. The collision rates
that I was observing in my tests of the old and new mix() functions
was about 2 * (1/10000) of what you test generated. You could just
test against the integers between 1 and 2000000.

Ken

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

Re: [HACKERS] Common Table Expressions (WITH RECURSIVE) patch

> 3. "I think this is a "must fix" because of the point about volatile
> functions --- changing it later will result in user-visible semantics
> changes, so we have to get it right the first time."
>
> I don't entirely agree with #3. It is user-visible, but only in the
> sense that someone is depending on undocumented multiple-evaluation
> behavior.

What makes you think it's going to be undocumented? Single versus
multiple evaluation is a keep aspect of this feature and certainly
needs to be documented one way or the other. I can't understand why
we would introduce a standard syntax with non-standard behavior, but
if we do, it certainly had better be mentioned in the documentation.

I think that the most likely result of a CTE implementation that
doesn't guarantee single evaluation is that people simply won't use
it. But anyone who does will expect that their queries will return
the same results in release N and release N+1, for all values of N.
The only way that an incompatible change of this type won't break
people's applications is if they're not using the feature in the first
place, in which case there is no point in committing it anyway.

I wonder if the whole approach to this patch is backward. Instead of
worrying about how to implement WITH RECURSIVE, maybe it would be
better to implement a really solid, spec-compliant version of WITH,
and add the RECURSIVE functionality in a later patch/release.

...Robert

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

Re: [pgsql-fr-generale] Re: [pgsql-fr-generale] Re: Pas de contrainte référentielle vers un champ non-UNIQUE :-(

Le Tue, 09 Sep 2008 14:49:46 +0200,
"Stéphane A. Schildknecht" <stephane.schildknecht@postgresqlfr.org> a
écrit :
> Stephane Bortzmeyer a écrit :
> > On Mon, Sep 08, 2008 at 05:16:30PM +0200,
> > Jean-Samuel Reynaud <reynaud@elma.fr> wrote
> > a message of 43 lines which said:
> >
> >> Cette limitation vient en partie, à mon sens, du fait que les
> >> références sont gérées dans PostgreSQL via des triggers.
> >
> > Et donc ce serait une limite d'implémentation et pas un problème
> > fondamental d'algèbre relationnelle ?
> >
> >
>
> J'ai du mal à comprendre pourquoi il y a discussion. Comment faire
> référence à une ligne qui peut ne pas être unique ?
> Une colonne ne peut pas faire référence à une valeur de colonne dans
> une ligne qui, elle, peut ne pas être unique.
>
> On parle bien de référence ici, et non de valeur.
>
Effectivement, je suis d'accord avec toi, c'est la définition de la
notion.

Si l'effet que tu souhaites est différente de cette notion, tu
peux la définir avec des triggers en base...

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

Re: [pgsql-fr-generale] Re: Pas de contrainte référentielle vers un champ non-UNIQUE :-(

On Tue, 9 Sep 2008 13:54:10 +0200, Stephane Bortzmeyer <bortzmeyer@nic.fr>
wrote:
>
>
>> Cette limitation vient en partie, à mon sens, du fait que les
>> références sont gérées dans PostgreSQL via des triggers.
>
> Et donc ce serait une limite d'implémentation et pas un problème
> fondamental d'algèbre relationnelle ?

Une clé étrangère est une référence à un enregistrement, et non pas
à une valeur. L'enregistrement est identifié au moins pas un clé unique,
ou mieux encore par un clé primaire.

Ca fait bien parti des définitions fondamentales des bases de données
relationelles, ça n'est pas un problème d'implémentation dans
PostgreSQL.

--
Sébastien

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

Re: [GENERAL] Postgres in a solaris zone - patch 125077-02 needed

If you're running S10u3 (or earlier), then you need to install patch
120011-09 (or later) to get /usr/lib/libkrb5.so.1.

Greg Smith wrote:
> On Mon, 8 Sep 2008, Markova, Nina wrote:
>
>> I wasn't able to find 125077-02 patch for SPARC, and contacted SUN last
>> week - they are very slow.
>
> It wasn't released; according to
> http://www.mail-archive.com/pca@lists.univie.ac.at/msg00199.html that
> fix made its way into 120011-09 instead.
>
> I'm not so sure you actually need this though.
> http://bugs.opensolaris.org/view_bug.do;jsessionid=1ce76d3cbf20747f874eb1a300df?bug_id=6546052
> suggests there's actually a bug in how the checkinstall script looks for
> the file that prevent it from installing in a zone. Looks just like
> your report. That says the s10u4_07 update fixes the problem. I don't
> know enough about Solaris packaging to suggest exactly what to do here,
> but that should get you moving in the right direction rather than
> chasing a non-existant patch.
>
> --
> * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
>

--
Jim Gates Sun Microsystems
Nashua, NH, USA http://sun.com/postgresql

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

Tuesday, September 9, 2008

Blog Archive