Buglist

[Vivek Khera]

Re: Buglist

>>>>> "BL" == Bo Lorentsen <bl@netgroup.dk > writes:

BL> Hi ...
BL> I'm trying to convince my boss to use posgresql (I need RI, transactions
BL> and views), but he keeps comparing the project to mysql. Until now, I
BL> found the answers to he's questions on the www.postgresql.org page, but
BL> now I'm lost :-)

My big reason to choose postgres was concurrency. My application has
transactions and updates all over the place, and even as a single
developer in the early stages, I could already see problems with
table-level locking that mysql was giving me. Who knows what would
have happened in production with hundreds of people hitting the db
simultaneously! The row-level locking in Postgres has made it
possible for an incredible number of simultaneous actions to be
carried out without any waiting for the users.

Try making a table grow beyond your file size limit in mysql. You
can't. Even if you use an OS with 64-bit file pointers (such as
FreeBSD) you can't grow your file beyond 4Gb in mysql (at least with
mysam tables -- dunno about innodb tables). In Postgres, it is
automagically handled for you.

The *only* drawbacks I find with postgres is the need to dump/restore
on major version updates and the need to vacuum the tables
regularly...

Tops on my wish list is that postgres automatically notice when a row
is no longer needed (all transactional references to it are gone) and
'free' it at that time, rather then needing a special scan to
determine the row is no longer needed and freeing it.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.c om Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Bruno Wolff III]

Re: Buglist

On Tue, Aug 19, 2003 at 19:17:31 +0530,
Shridhar Daithankar <shridhar_daith ankar@persisten t.co.in> wrote:[color=blue]
>
> Making pgsql-bugs a open to non-subscription but moderated list might be a
> good idea. It really does not matter if a bug gets filed couple of days late
> but having to have subscribe to another list could be ditterent.[/color]

All of the pgsql lists including bugs already work this way.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Joshua D. Drake]

Re: Buglist

>[color=blue]
>
>It's still bolted on. The entire concept that "transactio nal integrity
>is optional" is ludicrous, IMHO. "Integrity" and "optional" are
>contradictor y.
>[/color]

Obviously you have never voted in a major election ;)
[color=blue]
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
>[/color]



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Bruno Wolff III]

Re: Buglist

On Tue, Aug 19, 2003 at 18:01:33 +0530,
Shridhar Daithankar <shridhar_daith ankar@persisten t.co.in> wrote:[color=blue]
>
> Few major differences I can see right here. Correct me on mysql side.
>
> - WAL log
> - Transactabl DDLs
> - Nested transactions coming soon to PG
> - PITR coming soon to PG[/color]

Note that the last two are not coming soon as in 7.4, but as in maybe for 7.5.
Which I can't see being out in less than 6 months.

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

http://archives.postgresql.org

[Shridhar Daithankar]

Re: Buglist

On Tuesday 19 August 2003 21:24, Bruno Wolff III wrote:[color=blue]
> On Tue, Aug 19, 2003 at 19:17:31 +0530,
>
> Shridhar Daithankar <shridhar_daith ankar@persisten t.co.in> wrote:[color=green]
> > Making pgsql-bugs a open to non-subscription but moderated list might be
> > a good idea. It really does not matter if a bug gets filed couple of days
> > late but having to have subscribe to another list could be ditterent.[/color]
>
> All of the pgsql lists including bugs already work this way.[/color]

No.. Ocassionally when somebody cross-posts and I reply, I get 'Post stalled
for modetaro because you are not member' types error. IIRC, the SQL list is
most frequent ones. I haven't seen any other list doing such stuff..

Anyway, good to know that it's done that way..

Shridhar


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Shridhar Daithankar]

Re: Buglist

On Tuesday 19 August 2003 21:30, Bruno Wolff III wrote:[color=blue]
> On Tue, Aug 19, 2003 at 18:01:33 +0530,
>
> Shridhar Daithankar <shridhar_daith ankar@persisten t.co.in> wrote:[color=green]
> > Few major differences I can see right here. Correct me on mysql side.
> >
> > - WAL log
> > - Transactabl DDLs
> > - Nested transactions coming soon to PG
> > - PITR coming soon to PG[/color]
>
> Note that the last two are not coming soon as in 7.4, but as in maybe for
> 7.5. Which I can't see being out in less than 6 months.[/color]

Right. But as of now, we know that some code exists for each of these. Given
for how long transactions in mysql was vapourware (What, 2 years?), I think
what I am making is pretty much a statement and not a claim.

Of course, by postgresql standards, it may be bit too early to announce, I
admit...:-)

Shridhar


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

http://archives.postgresql.org

[Bruno Wolff III]

Re: Buglist

On Tue, Aug 19, 2003 at 21:27:15 +0530,
Shridhar Daithankar <shridhar_daith ankar@persisten t.co.in> wrote:[color=blue]
> On Tuesday 19 August 2003 21:24, Bruno Wolff III wrote:[color=green]
> > On Tue, Aug 19, 2003 at 19:17:31 +0530,
> >
> > Shridhar Daithankar <shridhar_daith ankar@persisten t.co.in> wrote:[color=darkred]
> > > Making pgsql-bugs a open to non-subscription but moderated list might be
> > > a good idea. It really does not matter if a bug gets filed couple of days
> > > late but having to have subscribe to another list could be ditterent.[/color]
> >
> > All of the pgsql lists including bugs already work this way.[/color]
>
> No.. Ocassionally when somebody cross-posts and I reply, I get 'Post stalled
> for modetaro because you are not member' types error. IIRC, the SQL list is
> most frequent ones. I haven't seen any other list doing such stuff..[/color]

All that message means is that your message won't be posted until a moderator
approves it.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Vivek Khera]

Re: Buglist

>>>>> "SD" == Shridhar Daithankar <shridhar_daith ankar@persisten t.co.in> writes:

SD> On Tuesday 19 August 2003 21:03, Vivek Khera wrote:[color=blue][color=green]
>> Tops on my wish list is that postgres automatically notice when a row
>> is no longer needed (all transactional references to it are gone) and
>> 'free' it at that time, rather then needing a special scan to
>> determine the row is no longer needed and freeing it.[/color][/color]

SD> Heh.. we have autovacuum right. Well it does not work the way you
SD> want but it works automatically at least.


There's a big difference between "noticing that a table needs to be
vacuumed and running it" and "automatica lly having the backend free a
row as soon as we know it is eligible to be (as would normally be
determined by vacuum)".

One of these days when I can afford a 14-hour dump/restore, I'll
upgrade to 7.4 and try autovacuum :-)

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.c om Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

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

http://archives.postgresql.org

[Vivek Khera]

Re: Buglist

>>>>> "VK" == Vivek Khera <khera@kcilink. com> writes:

One more nit:

Since the beginning of time (at least MySQL v3.22) MySQL has silently
ignored the foreign key references in table create statement. Now
that they have foreign key support (version 4.x), do they honor those
statements? Nope. You have to use their own syntax to declare your
FKs. They still silently ignore the references in the table create
statements.

Standards are a good thing, especially when everyone does NOT have
their own.



--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.c om Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

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

http://archives.postgresql.org

[Shridhar Daithankar]

Re: Buglist

On Tuesday 19 August 2003 21:43, Vivek Khera wrote:[color=blue]
> There's a big difference between "noticing that a table needs to be
> vacuumed and running it" and "automatica lly having the backend free a
> row as soon as we know it is eligible to be (as would normally be
> determined by vacuum)".[/color]

Agreed and it would be nice to have. But functionally it's *almost* the same..
[color=blue]
> One of these days when I can afford a 14-hour dump/restore, I'll
> upgrade to 7.4 and try autovacuum :-)[/color]

FYi, it runs perfectly on 7.3.x IIRC from some posts earlier..So you don't
have to wait too long..

Shridhar


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[scott.marlowe]

Re: Buglist

On Tue, 19 Aug 2003, Bruno Wolff III wrote:
[color=blue]
> On Tue, Aug 19, 2003 at 18:01:33 +0530,
> Shridhar Daithankar <shridhar_daith ankar@persisten t.co.in> wrote:[color=green]
> >
> > Few major differences I can see right here. Correct me on mysql side.
> >
> > - WAL log
> > - Transactabl DDLs
> > - Nested transactions coming soon to PG
> > - PITR coming soon to PG[/color]
>
> Note that the last two are not coming soon as in 7.4, but as in maybe for 7.5.
> Which I can't see being out in less than 6 months.[/color]

Good point. We in no way need to point to future improvements anymore to
say Postgresql is a better database than MySQL. Postgresql 7.3.4 is the
best Open Source database in my humble opinion, and the things MySQL is
missing are complex features that need to be well thought out and well
implemented. Given the haphazard approach of MySQL to standards
compliance, you are gambling your company on it's non-standard SQL
sticking around if you use it, or hundreds of man hours replacing MySQL
specific SQL if you ever convert.

Examples:

|| In Postgresql || is the concatenation operator. Why, in god's name,
is || the concatenation operator. Because the Spec says so. In MySQL
it's the OR operator, contrary to spec.

Their FKs silently fail without so much as a notice. Poof, no fks, but
they swallow the syntax as if they do.

They get precision wrong all the time. Spec says numeric(x1,y1) *
numeric(x2,y2) yeilds numeric(size_of _int_portion.y1 +y2)

But in MySQL you get a numeric(size_of _int_portion,ma x(y1,y2)).

Postgresql gives you the answer.

create table mult (i1 numeric(10,2), i2 numeric(10,3));
insert into mult values (123.33,123.354 );
select i1*i2 from mult;


MySQL output:
+-----------+
| i1*i2 |
+-----------+
| 15213.249 |
+-----------+

Postgresql output:
?column?
-------------
15213.24882

I don't know if that's changed since 4.x came out, I gave up on MySQL for
anything other than a text storage / content management engine long ago.


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

http://www.postgresql.org/docs/faqs/FAQ.html

[Matthew T. O'Connor]

Re: Buglist

On Tue, 2003-08-19 at 12:13, Vivek Khera wrote:[color=blue]
> There's a big difference between "noticing that a table needs to be
> vacuumed and running it" and "automatica lly having the backend free a
> row as soon as we know it is eligible to be (as would normally be
> determined by vacuum)".[/color]

<talking beyond my real knowledge>
Changing Postgres to perform as mentioned above is non-trivial, it would
basicially change the entire core of the system. I think this is due to
the fact that postgres uses a non-overwriting storage manager. This has
many benefits including MVCC, the primary disadvantage is that you need
a vacuum type process
</talking beyond my real knowledge>
[color=blue]
> One of these days when I can afford a 14-hour dump/restore, I'll
> upgrade to 7.4 and try autovacuum :-)[/color]

pg_autovacuum does with with 7.3.x, but the source is only included in
7.4. Just get the pg_autovacuum directory from contrib and use it.

Matthew


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Vivek Khera]

Re: Buglist

>>>>> "MTO" == Matthew T O'Connor <matthew@zeut.n et> writes:

MTO> <talking beyond my real knowledge>
MTO> Changing Postgres to perform as mentioned above is non-trivial, it would
MTO> basicially change the entire core of the system. I think this is due to
MTO> the fact that postgres uses a non-overwriting storage manager. This has
MTO> many benefits including MVCC, the primary disadvantage is that you need
MTO> a vacuum type process
MTO> </talking beyond my real knowledge>

I'm not promoting any change in the MVCC. What I'm saying is that it
would be really cool if the backend process itself could recognize
that a row is no longer referenced by any transactions upon
termination of the transaction, and release it back to the system.
This is just what vacuum does but in a batched manner. I would love
to see it incremental. This would result in pretty much near zero
internal fragmentation, I think.

How hard that is, I have no clue. Right now my DB is saturating the
disk and having to squeeze vacuum into a saturated disk bandwidth is
not pleasant. Luckily, the 14-disk raid array just
arrived... hopefully that will have higher bandwidth than the 4-disk
array... ;-)



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Bruno Wolff III]

Re: Buglist

On Tue, Aug 19, 2003 at 21:51:14 -0400,
Vivek Khera <khera@kcilink. com> wrote:[color=blue]
>
> I'm not promoting any change in the MVCC. What I'm saying is that it
> would be really cool if the backend process itself could recognize
> that a row is no longer referenced by any transactions upon
> termination of the transaction, and release it back to the system.
> This is just what vacuum does but in a batched manner. I would love
> to see it incremental. This would result in pretty much near zero
> internal fragmentation, I think.[/color]

Why do you care about about the details of the implementation (rather than
the performance)? If it were faster to do it that way, that's how it would
have been done in the first place. The cost of doing the above is almost
certainly going to be an overall performance loser.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Vivek Khera]

Re: Buglist

>>>>> "BW" == Bruno Wolff, <Bruno> writes:
[color=blue][color=green]
>> to see it incremental. This would result in pretty much near zero
>> internal fragmentation, I think.[/color][/color]

BW> Why do you care about about the details of the implementation (rather than
BW> the performance)? If it were faster to do it that way, that's how it would
BW> have been done in the first place. The cost of doing the above is almost
BW> certainly going to be an overall performance loser.

I care for the performance. And how are you so sure that it was
faster the way it is now? Are you sure it was not done this way
because of ease of implementation?

Seriously, how much slower can it be if the backend were to do the
checking for external references upon updating/deleting a row? The
cost would be distributed across time as opposed to concentrated at
once within a vacuum process. I am fairly certian it would reduce
disk bandwidth requirements since at least one necessary page will
already be in memory.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org