headers
Jim C. Nasby | 1 Oct 2005 01:01

Re: State of support for back PG branches

On Mon, Sep 26, 2005 at 09:07:45PM -0700, Joshua D. Drake wrote:
> 
> >A nice pg_upgrade utility would make a big difference. Clearly an
> >in-place upgrade is possible, but maintaining is hard. There are two
> >broad ways of running a pg_upgrade project - one that is entirely
> >independent of the main codebase and one that puts requirements on the
> >main codebase developers ("if you change $foo you provide code to
> >translate old $foo to new $foo"). Any feel for the relative difficulty
> >of the two approaches? And how much push-back there'd be on the latter?
> > 
> >
> You can do in place upgrades with Slony-I and Mammoth Replicator.

With a lot more effort than a dump/restore, or presumably a pg_upgrade.
I'd love to see a project that uses Slony to do an in-place migration as
easy as possible. Maybe I'll get around to it in another 5 years....
--

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby <at> pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Permalink | Reply | 0 comments |
headers
Jim C. Nasby | 1 Oct 2005 01:12

Re: State of support for back PG branches

On Mon, Sep 26, 2005 at 08:54:49PM -0700, Josh Berkus wrote:
> Tom,
> 
> >Or, as you say, we could take the viewpoint that there are commercial
> >companies willing to take on the burden of supporting back releases, and
> >the development community ought not spend its limited resources on doing
> >that.  I'm hesitant to push that idea very hard myself, because it would
> >look too much like I'm pushing the interests of my employer Red Hat
> >... but certainly there's a reasonable case to be made there.
> 
> Well, I think you know my opinion on this.  Since there *are* commercial 
> companies available, I think we should use them to reduce back-patching 
> effort.   I suggest that our policy should be:  the community will patch 
> two old releases, and beyond that if it's convenient, but no promises. 
> In other words, when 8.1 comes out we'd be telling 7.3 users "We'll be 
> patching this only where we can apply 7.4 patches.  Otherwise, better 
> get a support contract."

I agree, although I think there should be some time guarantees as well.
I like the ~3 year number that's been tossed around.

> Of course, a lot of this is up to individual initiative; if someone 
> fixes a patch so it applies back to 7.2, there's no reason not to make 
> it available.  However, there's no reason *you* should make it a priority.

Yeah, I hope that commercial interests can work together on supporting
things they want supported.
--

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby <at> pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
(Continue reading)

Permalink | Reply | 0 comments |
headers
Jim C. Nasby | 1 Oct 2005 01:14

Re: Database file compatability

On Wed, Sep 28, 2005 at 10:22:51AM -0400, Tom Lane wrote:
> "Qingqing Zhou" <zhouqq <at> cs.toronto.edu> writes:
> > "Tom Lane" <tgl <at> sss.pgh.pa.us> wrote in message 
> > There is a possible sequence like this:
> 
> > ALIGNOF_LONG        4
> > ALIGNOF_DOUBLE      8
> > MAXIMUM_ALIGNOF     8
> 
> > vs.
> 
> > ALIGNOF_LONG        8
> > ALIGNOF_DOUBLE      8
> > MAXIMUM_ALIGNOF     8
> 
> > So we should at least check ALIGNOF_LONG as well.
> 
> No, we don't need to, because we do not really care about ALIGNOF_LONG
> per se.  We don't use "long" as an on-disk datatype, precisely because
> we don't know what size it is.  We use int32 and int64.  The former has
> align 4 on all machines AFAIK, and the latter has MAXIMUM_ALIGNOF.

Is there a serious penalty associated with just checking them all? Seems
like better safe than sorry...

On a related note, are checks for endianness made as well?
--

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby <at> pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
(Continue reading)

Permalink | Reply | 0 comments |
headers
Jim C. Nasby | 1 Oct 2005 01:19

Re: PostgreSQL overall design

On Tue, Sep 27, 2005 at 07:00:14PM +0530, Gnanavel S wrote:
> On 9/27/05, Jonah H. Harris <jonah.harris <at> gmail.com> wrote:
> >
> > Were you looking for a call graph?
> 
> 
> Yes. I want to know the list and sequence of files involved during a call.

Total non-coder question, but is there an open-source utility that's
capable of generating that? Seems like a useful piece of documentation
to have. Also seems like it'd be completely impractical to maintain by
hand.
--

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby <at> pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Permalink | Reply | 0 comments |
headers
Jim C. Nasby | 1 Oct 2005 01:26

Re: effective SELECT from child tables

On Wed, Sep 28, 2005 at 10:24:18PM +0200, Martijn van Oosterhout wrote:
> On Wed, Sep 28, 2005 at 11:00:46PM +0400, Ilia Kantor wrote:
> > Your suggestion is essentially the same as mine.. 
> > There exists tableoid, pretty much suited to tell between tables in the case
> > of inheritance.. I can't see a "real" need to add a special "class
> > classifier" to each table..
> > This solution is a workaround. It will work, just can't make myself love it.
> 
> I wonder if it would be possible to tweak the constraints exclusion
> code so that if it sees something of the form "tableoid = X" to exclude
> other tables...
> 
> You know, assume each table has a constraint "tableoid = OID".
> 
> Still, it is a fairly unusual feature.

Well, it's possibly a good way to do list partitioning where you can
drop the partitioning key out of each partition, something I would love
to have for stats.distributed.net (I'm actually working on a project
that does exactly this with a UNION ALL view and rules...)
--

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby <at> pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
(Continue reading)

Permalink | Reply | 0 comments |
headers
Jim C. Nasby | 1 Oct 2005 01:30

Re: effective SELECT from child tables

On Wed, Sep 28, 2005 at 07:25:46PM +0100, Simon Riggs wrote:
> Include the Discriminator as a column in A and it will be inherited by
> all A1, A2, A3. 
> e.g. concrete_class	char(1) not null
<snip>
> This will add 1 byte per row in your superclass... and requires no

I thought char was actually stored variable-length...? I know there's a
type that actually acts like char does on most databases, but I can't
remember what it is off-hand (it should be mentioned in docs 8.3...)
--

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby <at> pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Permalink | Reply | 5 comments |
headers
Ron Peacetree | 1 Oct 2005 01:40
Picon
Favicon

Re: [PERFORM] A Better External Sort?

25MBps should not be a CPU bound limit for IO, nor should it be
an OS limit.  It should be something ~100x (Single channel RAM)
to ~200x (dual channel RAM) that.

For an IO rate of 25MBps to be pegging the CPU at 100%, the CPU
is suffering some combination of
A= lot's of cache misses ("cache thrash"), 
B= lot's of random rather than sequential IO (like pointer chasing)
C= lot's of wasteful copying
D= lot's of wasteful calculations

In fact, this is crappy enough performance that the whole IO layer
should be rethought and perhaps reimplemented from scratch.
Optimization of the present code is unlikely to yield a 100-200x
improvement.

On the HD side, the first thing that comes to mind is that DBs are
-NOT- like ordinary filesystems in a few ways:
1= the minimum HD IO is a record that is likely to be larger than
a HD sector.  Therefore, the FS we use should be laid out with
physical segments of max(HD sector size, record size)

2= DB files (tables) are usually considerably larger than any other
kind of files stored.  Therefore the FS we should use should be laid
out using LARGE physical pages.  64KB-256KB at a _minimum_.

3= The whole "2GB striping" of files idea needs to be rethought.
Our tables are significantly different in internal structure from the
usual FS entity.
(Continue reading)

Permalink | Reply | 2 comments |
headers
Jim C. Nasby | 1 Oct 2005 01:41

Re: Found small issue with OUT params

On Fri, Sep 30, 2005 at 10:53:22AM -0700, Joshua D. Drake wrote:
> >
> >So you might notice little performance hit bringing back a million rows, 
> >and most of these type of single OUT params functions only return one 
> >row/value anyway.
> >There would be zero perceivable difference in performance regardless of 
> >the extra overhead for a single value/row.
> 
> Sounds like we need a test case... up for it?

If there is a performance difference my vote is that we bite the bullet
for 8.1 and accept the performance hit rather than settle for
sub-optimal behavior. Much easier to fix the performance penalty down
the road than to fix the behavior.
--

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby <at> pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Permalink | Reply | 2 comments |
headers
Jim C. Nasby | 1 Oct 2005 01:48

Re: Query in SQL statement

On Thu, Sep 29, 2005 at 09:28:38PM +0800, Christopher Kings-Lynne wrote:
> 
> >CREATE SEQUENCE ai_id;
> >CREATE TABLE badusers (
> >  id int DEFAULT nextval('ai_id') NOT NULL,
> >  UserName varchar(30),
> >  Date  datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
> >  Reason varchar(200),
> >  Admin varchar(30) DEFAULT '-',
> >  PRIMARY KEY (id),
> >  KEY UserName (UserName),
> >  KEY Date (Date)
> >);
> >
> >
> >Am always getting foll. Errors,
> >
> >ERROR:  relation "ai_id" already exists
> >ERROR:  syntax error at or near "(" at character 240
> 
> You have just copied the Mysql code to Postgresql.  It will in no way 
> work.  Your default for 'Date' is illegal in postgresql and hence it 
> must allow NULLs.  There is no such thing as a 'datetime' type.  There 
> is no such thing as 'Key'.  Also your mixed case identifiers won't be 
> preserved.  You want:
> 
> CREATE TABLE badusers (
>   id SERIAL PRIMARY KEY,
>   UserName varchar(30),
>   Date  timestamp,
(Continue reading)

Permalink | Reply | 0 comments |
headers
Dann Corbit | 1 Oct 2005 01:52

Re: [PERFORM] A Better External Sort?

I have perused the tuple sort stuff.

The good:
The documentation of the sort algorithm from Knuth's TAOCP was
beautifully done.  Everyone who writes an algorithm should credit the
original source like this, and also where it deviates.  That was done
very nicely.

The bad:
With random access, tape style merging is not necessary.  A priority
queue based merge will be a lot faster.

The UGLY:
Please, someone, tell me I was hallucinating.  Is that code really
READING AND WRITING THE WHOLE TUPLE with every sort exchange?!  Maybe
there is a layer of abstraction that I am somehow missing.  I just can't
imagine that is what it is really doing.  If (somehow) it really is
doing that, a pointer based sort which forms a permutation based upon
the keys, would be a lot better.

The fundamental algorithm itself could also be improved somewhat.

Here is a {public domain} outline for an introspective quick sort that
Pete Filander and I wrote some time ago and contributed to FastDB.  It
is written as a C++ template, but it will take no effort to make it a
simple C routine.  It assumes that e_type has comparison operators, so
in C you would use a compare function instead.

/*
** Sorting stuff by Dann Corbit and Pete Filandr.
(Continue reading)

Permalink | Reply | 0 comments |

Gmane