Re: [HACKERS] leakproof

2012-02-19 Thread Don Baccus

On Feb 19, 2012, at 5:42 PM, Tom Lane wrote:

 Robert Haas robertmh...@gmail.com writes:
 Having now spent far too much time in bed with that patch, I'm feeling
 like the concept that we are really looking for there is what some
 languages call pure - that is, there must be no side effects,
 whether by throwing exceptions or otherwise.
 
 Hmm, pure doesn't sound bad to me.  Nice and short.
 

Technically, pure is stronger than has no side effects:

http://en.wikipedia.org/wiki/Pure_function

Result can't depend on state (for instance, database contents), either.  This 
is the typical definition used in functional programming.

gcc extends this to allow use of global variables in a pure function (the 
stricter definition is met by const functions).  PG has immutable, so a 
slightly weaker pure probably wouldn't be terribly confusing given the gcc 
precedent (probably across their family of compilers).

D adopts the stricter definition of pure. 

So there's some confusion around the term.

But …

I picked up this thread after leakproof was settled on and was curious as to 
what leakproof was supposed to be as I didn't read the earlier posts.  I 
assumed it meant doesn't leak memory, which seems admirable and typical and 
not needful of an attribute on the function declaration.

pure is definitely less confusing IMO, if it's congruent with the weaker 
sense of pure that's found in some languages/implementations.


Don Baccus
http://donb.photo.net
http://birdnotes.net
http://openacs.org







-- 
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] leakproof

2012-02-19 Thread Don Baccus

On Feb 19, 2012, at 7:24 PM, Tom Lane wrote:

 Don Baccus dhog...@pacifier.com writes:
 On Feb 19, 2012, at 5:42 PM, Tom Lane wrote:
 Hmm, pure doesn't sound bad to me.  Nice and short.
 
 Technically, pure is stronger than has no side effects:
 http://en.wikipedia.org/wiki/Pure_function
 Result can't depend on state (for instance, database contents), either.  
 This is the typical definition used in functional programming.
 
 Well, that condition is subsumed in our idea of an immutable function.

Yes, I said that myself, perhaps you didn't bother to read closely?


 It's not clear to me whether pure/leakproof functions are meant to be a
 strict subset of immutable functions

Superset, not subset, unless my guessing is wrong.  How could pure be a 
subset of immutable?

OK, at this point, proponents will explain why ...

But if you're not clear as to what a leakproof function is meant to be. then 
I suggest the definition must be defined very clearly, so everyone understands 
what it is meant to be.

 , but if they are then they meet
 this stricter definition.  On the other hand, if pure/leakproof functions
 don't have to be immutable but only stable, then the stricter definition
 corresponds to pure immutable.  That still doesn't sound too bad, as
 long as we define our terms clearly in the docs.

Sure, let those making the proposal make things clear.

Just speaking as a gadfly who's not posted here for probably close on 10 years …



Don Baccus
http://donb.photo.net
http://birdnotes.net
http://openacs.org







-- 
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] MySQL search query is not executing in Postgres DB

2012-02-18 Thread Don Baccus

On Feb 18, 2012, at 12:57 PM, Rob Wultsch wrote:
 
 Where first_name is string the queries above have very different
 behaviour in MySQL. The first does a full table scan and coerces
 first_name to an integer (so '5adfs' - 5) 

Oh my, I can't wait to see someone rise to the defense of *this* behavior!


Don Baccus
http://donb.photo.net
http://birdnotes.net
http://openacs.org







-- 
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] MySQL search query is not executing in Postgres DB

2012-02-18 Thread Don Baccus

On Feb 18, 2012, at 1:43 PM, Christopher Browne wrote:

 On Sat, Feb 18, 2012 at 4:12 PM, Don Baccus dhog...@pacifier.com wrote:
 
 On Feb 18, 2012, at 12:57 PM, Rob Wultsch wrote:
 
 Where first_name is string the queries above have very different
 behaviour in MySQL. The first does a full table scan and coerces
 first_name to an integer (so '5adfs' - 5)
 
 Oh my, I can't wait to see someone rise to the defense of *this* behavior!
 
 I can see a use, albeit a clumsy one, to the notion of looking for values
   WHERE integer_id_column like '1%'
 
 It's entirely common for companies to organize general ledger account
 numbers by having numeric prefixes that are somewhat meaningful.
 
 A hierarchy like the following is perfectly logical:
 -  to 0999 :: Cash accounts [1]

I asked earlier if anyone would expect 01 like '0%' to match …

Apparently so!

Your example is actually a good argument for storing account ids as text, 
because '' like '0%' *will* match.

I'd think it nearly insane if someone was expecting '3%' to match not
only the '3000 thru 3999' series, but also '300 to 399' and 30 to 39
and 3.

How is PG supposed to know that integers compared to strings are always to be 
padded out to precisely 4 digits?


Don Baccus
http://donb.photo.net
http://birdnotes.net
http://openacs.org







-- 
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] MySQL search query is not executing in Postgres DB

2012-02-17 Thread Don Baccus

On Feb 17, 2012, at 11:28 AM, Christopher Browne wrote:
 And I have *zero* confidence that for PostgreSQL to rule out LIKE
 '1%' is preventing those designs from getting built...
 
 When confronted by a difficult problem, solve it by reducing it to the
 question, How would the Lone Ranger handle this?

He would turn to Tonto, who undoubtably would advise:

If you want to treat an integer like a string, figure out how to convert your 
integer into a string …

The original query strikes me as being similar to expecting the Lone Ranger's 
six-shooter to be capable of shooting Tonto's arrows.


Don Baccus
http://donb.photo.net
http://birdnotes.net
http://openacs.org







-- 
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] MySQL search query is not executing in Postgres DB

2012-02-17 Thread Don Baccus

On Feb 17, 2012, at 4:12 PM, Josh Berkus wrote:

 On 2/17/12 12:04 PM, Robert Haas wrote:
 The argument isn't about whether the user made the right design
 choices; it's about whether he should be forced to insert an explicit
 type cast to get the query to do what it is unambiguously intended to
 do. 
 
 I don't find INTEGER LIKE '1%' to be unambiguous.
 
 Prior to this discussion, if I had run across such a piece of code, I
 couldn't have told you what it would do in MySQL without testing.

If someone showed it to me without mention MySQL I'd say:

oh, it's an error.

 
 What *does* it do in MySQL?

And knowing it's MySQL … oh, probably *not* an error, but like you … I'd be 
mystified.

Should 01 like '0%' match?


Don Baccus
http://donb.photo.net
http://birdnotes.net
http://openacs.org







-- 
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] OOP real life example (was Re: Why is MySQL more

2002-08-14 Thread Don Baccus

Hannu Krosing wrote:

 I guess what he meant was that you were arguing for arguments sake (mine
 is better than yours! Yes it is! Yes it is! ...)

That's the dictionary definition of the phrase.

 and not to get to some
 solution,

and that's the source of the frustration.  I only re-subscribed to the 
list because we at OpenACS had examined PG's OO extensions quite 
thoroughly before rejecting the current implementation as being not 
useful for our work, and I thought our reasoning might be of interest.

 dismissing perfectly good arguments with a simplenot true
 statements and suggesting people to read heavy books with the claim that
 the truth is somewhere in there ;) 

and that's what's I mean when I say he's been arguing from authority.


-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])



Re: [HACKERS] Inheritance

2002-08-14 Thread Don Baccus

Bruce Momjian wrote:
 Christopher Kings-Lynne wrote:
 
1. The current implementation is broken.

2. We have no proper description of how a fixed implementation
should work.

Surely 99% of the implementation problems could be solved with an index type
that can span tables?
 
 
 Right.  Instead of talking in circles, let's figure out how to do it.
 If the issue is only sequence numbers, can we force a column to _only_
 get values from the sequence counter,

Even if primary keys were forced to be generated from a sequence (a very 
artificial restriction), unique constraints are also implemented by 
index.  And people also join on columns other than their primary key so 
will want indexes on these columns to span tables, also.


-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org


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

http://www.postgresql.org/users-lounge/docs/faq.html



Re: [HACKERS] OOP real life example (was Re: Why is MySQL more

2002-08-13 Thread Don Baccus

Greg Copeland wrote:
 On Tue, 2002-08-13 at 00:16, Curt Sampson wrote:
 
I will revise my opinion the instant someone shows me something that I
can't do relationally, or is easy to implement with inheritance, and
difficult with relational methods.

The traditional view approach requires unnecessary joins, and there's no 
getting around it.

And yes I know he's not reading my mail and no, don't bother repeating 
this to him, he'll just continue to ignore the point.

-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])



Re: [HACKERS] OOP real life example (was Re: Why is MySQL more

2002-08-13 Thread Don Baccus

Bruce Momjian wrote:
 It is hard to argue with this logic.

If he were actually making a technical argument I might actually agree 
with you myself.

Thus far all he's done is argue from authority, and in tight circles to 
boot.

Which means the term is an accurate description of his behavior ...

Here's a lengthier and polite description - he's trying to impress us 
with his brilliance which several of us are just too dense to recognize 
on our own.

-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]



Re: [HACKERS] OOP real life example (was Re: Why is MySQL more chosen

2002-08-12 Thread Don Baccus

Curt Sampson wrote:
 On Sun, 11 Aug 2002, Don Baccus wrote:
 
 
Oh? Ok, please translate the following into equivalant SQL that
does not use a view:
...

Granulize GRANT to the table column level.
 
 
 Can you please show me the code for that? After all, I showed you
 all of my code when doing equivalants.

Obviously it would require extending SQL, but since you in part argue 
that SQL sucks in regard to the relational model this shouldn't matter, 
right?   You're arguing the superiority of the relational model as 
described by DD over other models, non-relational SQL (which all agree 
has weaknesses) and most likely God.

So don't flip-flop between the oh, SQL sucks think about the relational 
model and SQL doesn't support that.  Pick one or the other.  Argue 
SQL or DD/relational model.

It's not hard to propose *extensions* to SQL that would allow granting 
of perms on a column rather than table level.

 Or are you saying that it's syntactic sugar only in some imaginary
 version of postgres that does not exist?

Sort of like the idealized relational model that isn't implemented by 
SQL nor PG, but yet you reference again and again when it suits you to 
ignore the shortcomings of SQL92?

Sure.

Sorry, for a moment I thought you were interested in a meaningful 
discussion rather than a dick-waving contest but I was wrong.

I give up.  Your right hand waves your dick more frequently and with 
much more vigor than mine.  This has nothing to do with with anything I 
care about, though.


-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org


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



Re: [HACKERS] OOP real life example (was Re: Why is MySQL more chosen

2002-08-12 Thread Don Baccus

Tom Lane wrote:
 Curt Sampson [EMAIL PROTECTED] writes:
 
On Sun, 11 Aug 2002, Don Baccus wrote:

Granulize GRANT to the table column level.

 
Can you please show me the code for that?
 
 
 It's required by the SQL spec.  PG hasn't got it, but the spec is
 perfectly clear about how it should be done.
 
 I think this is really a bit irrelevant to the thread topic, though.

As far as the last goes, not really.  Curtis argues from false premises, 
and this is one.  If it were the only false premise he argues from, 
sure, I'd agree it's irrelevant but sadly Curtis argues from false 
premises by default.

-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org


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



Re: [HACKERS] [SECURITY] DoS attack on backend possible (was: Re:

2002-08-12 Thread Don Baccus

Gavin Sherry wrote:

 As to your other point -- that this bug in the data/time code actually
 *reflects* the quality and reliability of the database itself -- you've
 really gone too far. The best software has bugs.

For example, in the current version of Oracle 9i, if a client (say 
SQL*Plus) is running on a linux box and talking to Oracle running on a 
Solaris box, executes the following:

create table foo(i integer primary key, bar blob);

... then later does ...

update foo set bar=empty_blob() where i = some key value

The Oracle server on Solaris crashes.  *the whole thing* BANG! 
Shot-to-the-head-dead.  Not the user's client - the server.

This means that any user with the right to update a single table with a 
blob can crash Oracle at will.

What does this say about Oracle's overall reliability?

As Gavin says all software has bugs.  Most of PG's bugs are far less 
spectacular than the Oracle bug I mention here.

Overall I rate PG and Oracle as being about equivalent in terms of bugs.

-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org


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

http://www.postgresql.org/users-lounge/docs/faq.html



Re: [HACKERS] OOP real life example (was Re: Why is MySQL more

2002-08-12 Thread Don Baccus

Curt Sampson wrote:

 ... the bugs in the postgres implementation
 of table inheritance, I've found the relational model much easier
 to use for solving problems.

No one has argued that the shortcomings (not bugs, really, just things 
left out) makes the current implementation of very limited utility.  As 
I mention this is exactly why we choose not to use it at OpenACS.

On the other hand at least we took the time to understand how it 
actually does work before criticizing it.

It's a pity, as I pointed out the reduction in joins alone would really 
be great.


-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org


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

http://archives.postgresql.org



Re: [HACKERS] Table Inheritance Discussion

2002-08-11 Thread Don Baccus

Curt Sampson wrote:

 The last question comes up because, during the conversation up to this
 point, we seem to have implicitly accepted that table inheritance is
 an object-oriented way of doing things. Thinking further on this,
 however, I've decided that it's not in fact object-oriented at all.

It's just type extensibility, really.

As to why, again there's an efficiency argument, as I said earlier some 
joins can be avoided given PG's implementation of this feature:

dotlrn=# create table foo(i integer primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
'foo_pkey' for table 'foo'
CREATE
dotlrn=# create table bar(j integer) inherits (foo);
CREATE
dotlrn=# explain select * from bar;
NOTICE:  QUERY PLAN:

Seq Scan on bar  (cost=0.00..20.00 rows=1000 width=8)

EXPLAIN
...

dotlrn=# create table foo(i integer primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
'foo_pkey' for table 'foo'
CREATE
dotlrn=# create table bar(i integer references foo primary key, j integer);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
'bar_pkey' for table 'bar'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY 
check(s)
CREATE
dotlrn=# create view foobar as select foo.*, bar.j from foo, bar;
CREATE

dotlrn=# explain select * from foobar;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..30020.00 rows=100 width=8)
   -  Seq Scan on foo  (cost=0.00..20.00 rows=1000 width=4)
   -  Seq Scan on bar  (cost=0.00..20.00 rows=1000 width=4)

EXPLAIN

There's also some error checking (using my inherited example):

dotlrn=# drop table foo;
ERROR:  Relation bar inherits from foo
dotlrn=#

Which doesn't exist in the view approach in PG at least (I'm unclear on 
standard SQL92 and of course this says nothing about the relational 
model in theory, just PG and perhaps SQL92 in practice).

-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org


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

http://archives.postgresql.org



Re: [HACKERS] OOP real life example (was Re: Why is MySQL more chosen

2002-08-11 Thread Don Baccus

Curt Sampson wrote:

 The problem is, table inheritance is just syntatic sugar for creating
 separate tables, and a view that does a UNION SELECT on them all
 together, projecting only the common columns.

I've been wanting to point out that SQL views are really, when 
scrutinized, just syntactic sugar ...



-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])



Re: [HACKERS] OOP real life example (was Re: Why is MySQL more chosen

2002-08-11 Thread Don Baccus

Curt Sampson wrote:
 On Sun, 11 Aug 2002, Don Baccus wrote:
 
 
I've been wanting to point out that SQL views are really, when
scrutinized, just syntactic sugar ...
 
 
 Oh? Ok, please translate the following into equivalant SQL that
 does not use a view:
 
 CREATE TABLE t1 (key serial, value1 text, value2 text);
 CREATE VIEW v1 AS SELECT key, value1 FROM t1;
 GRANT SELECT ON v1 TO sorin;

Granulize GRANT to the table column level.   Then GRANT SELECT perms 
for the user on every column from the two tables that happen to be 
included in the view.

Yes, it's awkward.   So are the VIEW-based replacements for PG's type 
extensibility features.


-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org


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

http://archives.postgresql.org



Re: [HACKERS] Why is MySQL more chosen over PostgreSQL?

2002-08-08 Thread Don Baccus

Curt Sampson wrote:
 On 8 Aug 2002, Hannu Krosing wrote:
 
 
The main difference (in the inheritance part) is that a relation does
not have one fixed set of fields, but can have any additional fields
added in inherited tables and still be part of to the base table as
well.
 
 
 This is trivial to do with a view.

And views of this sort are trivial to do using PG's OO extensions.

I think I see a trend in this thread.  Why not give it up, dude?


-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org


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

http://www.postgresql.org/users-lounge/docs/faq.html



Re: [HACKERS] Why is MySQL more chosen over PostgreSQL?

2002-08-08 Thread Don Baccus

Curt Sampson wrote:
 On Thu, 8 Aug 2002, Don Baccus wrote:
 
 
And views of this sort are trivial to do using PG's OO extensions.
 
 
 So long as you don't mind them being broken, yeah. But hell, when someone
 asks for a unique constraint, they probably don't really mean it, do they?

Good grief, we all agree that they're currently broken and need to be 
fixed someday.

Give it up.  You're being a boor.


-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org


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



Re: [HACKERS] Table inheritance versus views

2002-08-03 Thread Don Baccus


 On Fri, 2002-08-02 at 22:39, [EMAIL PROTECTED] wrote:
 
On 29 Jul 2002 18:27:40 MDT, the world broke into rejoicing as
Stephen Deasey [EMAIL PROTECTED]  said:

Curt Sampson wrote:

I'm still waiting to find out just what advantage table inheritance
offers. I've asked a couple of times here, and nobody has even
started to come up with anything.

Table inheritance offers data model extensibility.  New (derived) tables
can be added to the system, and will work with existing code that
operates on the base tables, without having to hack up all the code.

But it kind of begs the question of why you're creating the new table in
the first place.

The new table certainly _won't_ work with existing code, at least from
the perspective that the existing code doesn't _refer_ to that table.

Since OpenACS has been brought up in this thread, I thought I'd join the 
list for a day or two and offer my perspective as the project manager.

1. Yes, we use views in our quasi-object oriented data model.  They're 
automatically generated when content types are built by the content 
repository, for instance.

2. Yes, you can model anything you can model with PG's OO extensions 
using views.  If you haven't implemented some way to generate the view 
automatically then a bit more work is required compared to using PG's OO 
extensions.

3. The view approach requires joins on all the subtype tables.  If I 
declare type 'foo' then the view that returns all of foo's columns joins 
on all the subtype tables, while in the PG OO case all of foo's columns 
are stored in foo meaning I can get them all back with a simple query on 
the table.  The PG OO approach can be considerably more efficient than 
the view approach, and this is important to some folks, no matter how 
many appeals to authority are made to various bibles on relational 
theory written by Date and Darwen.

4. The killer that makes the current implementation unusable for us is 
the fact that there's no form of indexing that spans all the tables 
inherited from a base type.  This means there's no cheap enforcement of 
uniqueness constraints across a set of object types, among other things. 
  Being able to inherit indexes and constraints would greatly increase 
the utility of PG's OO extensions.

5. If PG's OO extensions included inheritance of indexes and 
constraints, there's no doubt we'd use them in the OpenACS project, 
because when researching PG we compared datamodels written in this style 
vs. modelling the object relationships manually with automatically 
generated views.  We found the datamodel written using PG's OO 
extensions not only potentially more efficient, but more readable as well.

As far as whether or not there's a significant maintenance cost 
associated with keeping the existing OO stuff in PG, Tom Lane's voice is 
authorative while, when it comes to PG internals, Curt Sampson doesn't 
know squat.


-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org


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



[HACKERS] SAP-DB

2001-04-28 Thread Don Baccus
Hi guys,
> 
> I've used the open source SAPDB and the performance is pretty damned
> impressive. However, 'open source' in application to it is somewhat
> deceptive, since you have to make it with SAP's proprietary build
> tools/environment.
> 
> In my opinion, however, it would be worth closely auditing SAP DB to see
> what postgres can learn.

I downloaded it.  The directories are two characters in length, the
files are numbers, and it is a mixture of C++, Python, and Pascal.  Need
I say more.  :-)


I swore I'd never post to the hackers list again, but this is an  amazing
statement by Bruce.

Boy, the robustness of the software is determined by the number of characters
in the directory name? 

By the languages used?

Have you considered that the development tools may
be abstracting out the directory names in their development  environment?


Someone else dissed this release because you need their development tools.

Well, guess what big boys, the development tools are being released  open
source, too.  And SAP has a history of giving you sources (not fiche or
whatever) of their licensed technology so this is a fairly easy step  for
them.  

Not by the fact that SAP is a monster company, with a monster customer base,
with a DB engine hardly used over here but actually quite popular in Germany?

Quite popular in exactly the kind of enterprise environments that PG has yet
to crack and, if you dismiss this offering with silly hand-waving, may never
crack?

Have you ever heard of Adabas?

If you don't believe that SAP and SAP-DB are real, go talk to your fellow
Great Bridge employee Jan Wieck.

OK, I'll unsubscribe now ... I'm still a fan of PG, but not stupid enough to 
dismiss a robust, industrial-strength RDMBS system based on naive and
uneducated criticism.

PG has a lot to offer, and the upscale is still amazingly positive judging
by the pace of development over the past two years.  This is hardly a basis
for hand-waving SAP DB into MySQL-land, however.  I like PG, I will continue
to personally use PG, and I will support SAP DB along with Oracle and PG with
OpenACS 4.x.

And I would expect most of my clients using that toolkit to use Oracle,
with SAP DB coming in second, and PG third ...

I'm not trying to demotivate or discourage the PG crowd.  However, when you're
in a competitive battle the best prescription for getting your bell run is to
taunt and tease competitors who actually are in better shape than you.

And ... SAP DB is, in many ways important to the enterprise organization.

They may have an inferior page-locking concurrency scheme, I need to check on
this, but in many enterprise-level commercial environments this isn't such a big deal.
Since your (Bruce's) hopes for a wealthy future depends on GB IPO'ing which
will only come with significant penetration of the enterprise commercial environment,
I humbly suggest you don't write them off quite so quickly.

SAP is, after all, a very successful company.







- Don Baccus, Portland OR [EMAIL PROTECTED]>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net. 

Re: [HACKERS] beta testing version

2000-12-03 Thread Don Baccus

At 11:00 PM 12/2/00 -0800, Vadim Mikheev wrote:
 There is risk here.  It isn't so much in the fact that PostgreSQL, Inc
 is doing a couple of modest closed-source things with the code.  After
 all, the PG community has long acknowleged that the BSD license would
 allow others to co-op the code and commercialize it with no obligations.
 
 It is rather sad to see PG, Inc. take the first step in this direction.
 
 How long until the entire code base gets co-opted?

I totaly missed your point here. How closing source of ERserver is related
to closing code of PostgreSQL DB server? Let me clear things:

(not based on WAL)

That's wasn't clear from the blurb.

Still, this notion that PG, Inc will start producing closed-source products
poisons the well.  It strengthens FUD arguments of the "open source can't
provide enterprise solutions" variety.  "Look, even PostgreSQL, Inc realizes
that you must follow a close sourced model in order to provide tools for
the corporate world."




- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] beta testing version

2000-12-03 Thread Don Baccus

At 01:06 PM 12/3/00 +0100, Peter Eisentraut wrote:

 Open source software is a
privilege,

I admit that I don't subscribe to Stallman's "source to software is a
right" argument.  That's far off my reality map.

 and nobody has the right to call someone "irresponsible"
because they want to get paid for their work and don't choose to give away
their code.

However, I do have the right to make such statements, just as you have the
right to disagree.  It's called the first amendment in my country.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] beta testing version

2000-12-03 Thread Don Baccus

At 03:35 PM 11/30/00 -0800, Nathan Myers wrote:
On Thu, Nov 30, 2000 at 07:02:01PM -0400, The Hermit Hacker wrote:
 
 v7.1 should improve crash recovery ...
 ... with the WAL stuff that Vadim is producing, you'll be able to
 recover up until the point that the power cable was pulled out of 
 the wall.

Please do not propagate falsehoods like the above.  It creates
unsatisfiable expectations, and leads people to fail to take
proper precautions and recovery procedures.  

Yeah, I posted similar stuff to the PHPbuilder forum in regard to
PG.

The logging in 7.1 protects transactions against many sources of 
database crash, but not necessarily against OS crash, and certainly
not against power failure.  (You might get lucky, or you might just 
think you were lucky.)  This is the same as for most databases; an
embedded database that talks directly to the hardware might be able
to do better.  

Let's put it this way ... Oracle, a transaction-safe DB with REDO
logging, has for a very long time implemented disk mirroring.  Now,
why would they do that if you could pull the plug on the processor
and depend on REDO logging to save you?

And even then you're expected to provide adequate power backup to
enable clean shutdown.

The real safety you get is that your battery sez "we need to shut
down!" but has enough power to let you.  Transactions in progress
aren't logged, but everything else can tank cleanly, and your DB is
in a consistent state.  

Mirroring protects you against (some) disk drive failures (but not
those that are transparent to the RAID controller/driver - if your
drive writes crap to the primary side of the mirror and no errors
are returned to the hardware/driver, the other side of the mirror
can faithfully reproduce them on the mirror!)

But since drives contain bearings and such that are much more likely
to fail than electronics (good electronics and good designs, at least),
mechanical failure's more likely and will be known to whatever is driving
the drive.  And you're OK then...



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] beta testing version

2000-12-02 Thread Don Baccus

At 05:42 PM 12/2/00 +0100, Peter Eisentraut wrote:
Don Baccus writes:

 Exactly what is PostgreSQL, Inc doing in this area?

Good question...  See http://www.erserver.com/.

"Advanced Replication and Distributed Information capabilities are also under 
development to meet specific
 business and competitive requirements for both PostgreSQL, Inc. and clients. Several 
of these enhanced
 PostgreSQL, Inc. developments may remain proprietary for up to 24 months, with 
availability limited to
 clients and partners, in order to assist us in recovering development costs and 
continue to provide funding
 for our other Open Source contributions. "

Boy, I can just imagine the uproar this statement will cause on Slashdot when
the world finds out about it.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] beta testing version

2000-12-02 Thread Don Baccus

At 03:51 PM 12/2/00 -0600, Ross J. Reedstrom wrote:

"We expect to have the source code tested and ready to contribute to
the open source community before the middle of October. Until that time
we are considering requests from a number of development companies and
venture capital groups to join us in this process."

Where's the damn core code? I've seen a number of examples already of
people asking about remote access/replication function, with an eye
toward implementing it, and being told "PostgreSQL, Inc. is working
on that". It's almost Microsoftesque: preannounce future functionality
suppressing the competition.

Well, this is just all 'round a bad precedent and an unwelcome path
for PostgreSQL, Inc to embark upon.

They've also embarked on one fully proprietary product (built on PG),
which means they're not an Open Source company, just a sometimes Open
Source company.

It's a bit ironic to learn about this on the same day I learned that
Solaris 8 is being made available in source form.  Sun's slowly "getting
it" and moving glacially towards Open Source, while PostgreSQL, Inc.
seems to be drifting in the opposite direction.

if I absolutely need
something that's only in CVS right now, I can bite the bullet and use
a snapshot server. 

This work might be released as Open Source, but it isn't an open development
scenario.  The core work's not available for public scrutiny, and the details
of what they're actually up don't appear to be public either.

OK, they're probably funding Vadim's work on WAL, so the idictment's probably
not 100% accurate - but I don't know that.  

I'd be really happy with someone reiterating the commitment to an
open release, and letting us all know how badly the schedule has
slipped. Remember, we're all here to help! Get everyone stomping bugs
in code you're going to release soon anyway, and concentrate on the
quasi-propriatary extensions.

Which makes me wonder, is Vadim's time going to be eaten up by working
on these quasi-proprietary extensions that the rest of us won't get
for two years unless we become customers of Postgres, Inc?

Will Great Bridge step to the plate and fund a truly open source alternative,
leaving us with a potential code fork?  If IB gets its political problems
under control and developers rally around it, two years is going to be a
long time to just sit back and wait for PG, Inc to release eRServer.

These developments are a major annoyance.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] RI Types

2000-12-02 Thread Don Baccus

At 06:27 PM 12/2/00 -0500, Michael Fork wrote:
I am trying to set the update and delete rules that are returned from the
ODBC driver and the spec has the following to say:

SQL_NO_ACTION: If a delete of a row in the referenced table would cause a
"dangling reference" in the referencing table (that is, rows in the
referencing table would have no counterparts in the referenced table),
then the update is rejected. (This action is the same as the SQL_RESTRICT
action in ODBC 2.x.)

What I need to know is if RI_FKey_noaction_del and RI_FKey_restrict_del
procedures are functionally the same. The ODBC (which I would hope
conforms to SQL 9x) spec has 4 types of RI (CASCADE, NO_ACTION, SET_NULL,
SET_DEFAULT), and Postgres appears to have 5 (RI_FKey_cascade_del,
RI_FKey_noaction_del, RI_FKey_restrict_del, RI_FKey_setdefault_del,
RI_FKey_setnull_del), which leads me to belive that restrict and noaction
are the same thing, and the one that is used depends on what the user puts
in the REFERENCES line.

Am I correct?

"RESTRICT" is a SQL3 thing, an extension to SQL92.  It appears that the
intent is that restrict should happen BEFORE the delete goes chunking
its way through the tables, while noaction tries to delete then rolls
back and gives an error if necessary.

The final table entries are exactly the same for the RESTRICT and NOACTION
cases, so the semantics in the sense of the transformation that occurs on
the database are equivalent.  

Currently, PG treats NOACTION and RESTRICT as being the same, they're
separated in the code with a comment to that effect, i.e. the code for
NOACTION is duplicated for RESTRICT (in part to make it clear that
in the future we might want to implement RESTRICT more efficiently if
anyone figures out how).



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] beta testing version

2000-12-02 Thread Don Baccus

At 09:56 PM 12/2/00 -0700, Ron Chmara wrote:
...

And I really havn't seen much in the way of full featured products, complete
with printed docs, 24 hour support, tutorials, wizards, templates, a company
to sue if the code causes damage, GUI install, setup, removal, etc. etc. etc.

Want to make money from open source? Well, you have to find, or build,
a _product_. Right now, there are no OS db products that can compare to oh,
an Oracle product, a MSSQL product. There may be superior code, but that
doesn't make a difference in business. Business has very little to do
with building the perfect mousetrap, if nobody can easily use it.

Which of course is the business model - certainly not a "zero revenue" model
as Thomas arrogantly suggests - which OSS service companies are following.

They provide the cocoon around the code.

I buy RH releases from Fry's.  Yes, I could download, but the price is such
that I'd rather just go buy the damned release CDs.  I don't begrudge it,
they're providing me a real SERVICE, saving me time, which saves me dollars
in opportunity costs (given my $200/hr customer billing rate).  They make
money buy publishing releases, I still get all the sources.  We all win.

It is not a bad model.  

Question - if this model sucks, then certainly PG, Inc's net revenue last
year was greater than any true open source software company's?  I mean, let's
see that slam against the "zero revenue business model" be proven by showing
us some real numbers.  

Just what was PG, Inc's net revenue last year, and just how does their mixed
revenue model stack up against the OSS world?

(NOT the .com world, which is in a different business, no matter what Thomas
wants to claim).



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] beta testing version

2000-12-02 Thread Don Baccus

At 04:42 AM 12/3/00 +, Thomas Lockhart wrote:
 This statement of yours kinda belittles the work done over the past
 few years by volunteers.

imho it does not,

Sure it does.  You in essence are saying that "advanced replication is so
hard that it could only come about if someone were willing to finance a
PROPRIETARY solution.  The PG developer group couldn't manage it if
it were done Open Source".

In other words, it is much harder than any of the work done by the
same group of people before they started working on proprietary 
versions.

And that the only way to get them doing their best work is to put them
on proprietary, or "semi-proprietary" projects, though 24 months from
now, who's going to care?  You've opened the door to IB prominence, not
only shooting PG's open source purity down in flames, but probably PG, Inc's
as well - IF IB can figure out their political problems.  

IB, as it stands, is a damned good product in many ways ahead of PG.  You're
giving them life by this approach, which is a kind of bizarre businees strategy.

 I *am* one of those volunteers

Yes, I well remember you screwing up PG 7.0 just before beta, without bothering
to test your code, and leaving on vacation.  

You were irresponsible then, and you're being irresponsible now.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] beta testing version

2000-12-02 Thread Don Baccus

At 09:29 PM 12/2/00 -0800, Adam Haberlach wrote:
 Red herring, and you know it.  The question isn't whether or not your business
 generates income, but how it generates income.

   So far, Open Source doesn't.  The VA Linux IPO made ME some income,
but I'm not sure that was part of their plan...

VA Linux is a HARDWARE COMPANY.  They sell servers.  "We've engineered 2U
performance into a 1U box" is their current line.

Dell probably makes more money on their Linux server offerings (I have to
admit that donb.photo.net is running on one of their PowerEdge servers) than
VA Linux does.

If I can show you a HARDWARE COMPANY that is diving on selling MS NT servers,
will you agree that this proves that the closed source and open source models
both must be wrong, because HARDWARE COMPANIES based on each paradigm are
losing money???
  The .com'ers are trying it at the moment, and there seems to be
 a few flaws... ;)
 
 That's a horrible analogy, and I suspect you know it, but at least it is
 original.

   It wasn't an analogy.

Sure it is.  Read, damn it.  First he makes the statement that a business
based on open source is, by definition, a zero-revenue company then he
raises the spectre of .com companies (how many of them are open source?)
as support for his argument.  

OK, it's not an analogy, it's a disassociation with reality.  Feel better?

   In any case, can we create pgsql-politics so we don't have to go over
this issue every three months? 

Maybe you don't care about the open source aspect of this, but as a user
with about 1500 Open Source advocates using my code, I do.  If IB comes 
forth in a fully Open Source state my user base will insist I switch.

And I will.

And I'll stop telling the world that MySQL sucks, too.  Or at least that
they suck worse than the PG world :)

There is risk here.  It isn't so much in the fact that PostgreSQL, Inc
is doing a couple of modest closed-source things with the code.  After
all, the PG community has long acknowleged that the BSD license would
allow others to co-op the code and commercialize it with no obligations.

It is rather sad to see PG, Inc. take the first step in this direction.

How long until the entire code base gets co-opted?

(Yeah, that's extremist, but seeing PG, Inc. lay down the formal foundation
for such co-opting by taking the first step might well make the potential
reality become real.  It certainly puts some of the long-term developers
in no position to argue against such a co-opted snitch of the code).

I have to say I'm feeling pretty silly about raising such an effort to
increase PG awareness in mindshare vs. MySQL.  I mean, if PG, Inc's 
efforts somehow delineate the hopes and goals of the PG community, I'm
fairly disgusted.  





- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] beta testing version

2000-12-01 Thread Don Baccus

At 12:55 AM 12/1/00 -0800, Nathan Myers wrote:

Many people hope to run PostgreSQL 24x7x365.  With vacuuming, you 
might just as well shut down afterward; but when that goes away 
(in 7.2?), when will you get the chance to take your backups?  
Clearly we need either another form of snapshot backup that can 
be taken with the database running, and compatible with the 
current WAL (or some variation on it); or, we need another kind 
of log, in addition to the WAL.

Vadim's not ignorant of such matters, when he says "make a copy
of the files" he's not talking about using tar on a running
database.  BAR tools are needed, as Vadim has pointed out here in
the past.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] beta testing version

2000-12-01 Thread Don Baccus

At 11:06 PM 11/30/00 -0800, Vadim Mikheev wrote:
 As for replaying logs against a restored snapshot dump... AIUI, a 
 dump records tuples by OID, but the WAL refers to TIDs.  Therefore, 
 the WAL won't work as a re-do log to recover your transactions 
 because the TIDs of the restored tables are all different.   

True for current way of backing up - ie saving data in "external"
(sql) format. But there is another way - saving data files in their
natural (binary) format. WAL records may be applyed to
such dump, right?

Right.  That's what's missing in PG 7.1, the existence of tools to
make such backups.  

Probably the best answer to the "what does WAL get us, if it doesn't
get us full recoverability" questions is to simply say "it's a prerequisite
to getting full recoverability, PG 7.1 sets the foundation and later
work will get us there".



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] beta testing version

2000-12-01 Thread Don Baccus

At 12:30 AM 12/1/00 -0800, Ian Lance Taylor wrote:
For example, I would hope that EMC
disk systems handle power loss gracefully.

They must, their marketing literature says so :)

  But if you buy ordinary
off the shelf PC hardware, you really do need to arrange for a UPS,
and some sort of automatic shutdown if the UPS is running low.

Which is what disk subsystems like those from EMC do for you.  They've
got build-in battery backup that lets them guarantee (assuming the
hardware's working right) that in the case of a power outage, all blocks
the operating system thinks have been written will in actuality be written
before the disk subsystem powers itself down.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] beta testing version

2000-12-01 Thread Don Baccus

At 11:02 AM 12/1/00 -0800, Nathan Myers wrote:
On Fri, Dec 01, 2000 at 06:39:57AM -0800, Don Baccus wrote:
 
 Probably the best answer to the "what does WAL get us, if it doesn't
 get us full recoverability" questions is to simply say "it's a 
 prerequisite to getting full recoverability, PG 7.1 sets the foundation 
 and later work will get us there".

Not to quibble, but for most of us, the answer to Don's question is:
"It gives a ~20x speedup over 7.0."  That's pretty valuable to some of us.
If it turns out to be useful for other stuff, that's gravy.

Oh, but given that power failures eat disks anyway, you can just run PG 7.0
with -F and be just as fast as PG 7.1, eh?  With no theoretical loss in
safety?  Where's your faith in all that doom and gloom you've been 
spreading? :) :)

You're right, of course, we'll get roughly -F performance while maintaining
a much more comfortable level of risk than you get with -F.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] beta testing version

2000-12-01 Thread Don Baccus

At 12:56 PM 12/1/00 -0800, Nathan Myers wrote:

(Remember, we're talking about what you could do *now*, with 7.1.
Presumably with 7.2 other options will open.)

Maybe *you* are :)  Seriously, I'm thinking out loud about future
possibilities.  Putting a lot of work into building up a temporary
solution on top of 7.1 doesn't make a lot of sense, anyone wanting
to work on such things ought to think about 7.2, which presumably will
beta sometime mid-2001 or so???

And I don't think there are 7.1 hacks that are simple ... could be
wrong, though.

I don't know of any way to synchronously transfer the WAL, currently.

Nope.

Anyway, I would expect doing it to interfere seriously with performance.

Yep.  Anyone here have experience with replication and Oracle or others?
I've heard from one source that setting it up reliabily in Oracle and
getting the switch from the dead to the backup server working properly was
something of a DBA nightmare, but that's true of just about anything in
Oracle.  Once it was up, it worked reliably, though (also typical
of Oracle).

The "wait to log a 'commit' locally until after the remote site acks that
it got the WAL" is (akin to) the familiar two-phase commit.

Right.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] COPY BINARY is broken...

2000-12-01 Thread Don Baccus

At 03:05 PM 12/1/00 -0800, Alfred Perlstein wrote:

How about adding COPY XML?
(kidding of course about the XML, but it would make postgresql more
buzzword compliant :) )

Hey, we could add a parser and call the module MyXML ...



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] SQL 'in' vs join.

2000-11-30 Thread Don Baccus

At 08:37 AM 11/30/00 -0500, mlw wrote:
 mlw wrote:
 
  Why is a "select * from table1 where field in (select field from table2
  where condition )"
 
  is so dramatically bad compared to:
 
  "select * from table1, table2 where table1.field = table2.field and
  condition"

Now, given the two components, each with very low costs, it chooses to
do a sequential scan on the table. I don't get it. I have have been
having no end of problems with Postgres' optimizer. It just seems to be
brain dead at times. It is a huge point of frustration to me. I am tied
to postgres in my current project, and I fear that I will not be able to
implement certain features because of this sort of behavior.

But but but ...

Not only is the join faster, but it is more readable and cleaner SQL as
well.  I would never write the query in its first form.  I'd change the
second one slightly to "select table1.* from ...", though, since those
are apparently the only fields you want.

The optimizer should do a better job on your first query, sure, but why
don't you like writing joins?




- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] Please advise features in 7.1 (SUMMARY)

2000-11-30 Thread Don Baccus

At 05:24 AM 11/30/00 +, Thomas Lockhart wrote:
 Is "if" clause support in PG?
 for example:
 "drop table aa if exist"
 "insert into aa values(1) if not exists select * from aa where i=1"

No. afaict it is not in any SQL standard, so is unlikely to get much
attention from developers.

The insert, at least, can be written in standard SQL anyway...



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] SQL 'in' vs join.

2000-11-30 Thread Don Baccus

At 10:52 AM 11/30/00 -0500, Tom Lane wrote:
Don Baccus [EMAIL PROTECTED] writes:
 The optimizer should do a better job on your first query, sure, but why
 don't you like writing joins?

The join wouldn't give quite the same answers.  If there are multiple
rows in table2 matching a particular table1 row, then a join would give
multiple copies of the table1 row, whereas the WHERE foo IN (sub-select)
way would give only one copy.  SELECT DISTINCT can't be used to fix
this, because that would eliminate legitimate duplicates from identical
table1 rows.

Hmmm...I was presuming that "field" was a primary key of table1, so
such duplicates wouldn't exist (and SELECT DISTINCT would weed out
duplicates from table2 if "field" isn't a primary key of table2, i.e.
if table2 has a many-to-one relationship to table1).  For many-to-many
relationships yes, you're right, the "in" version returns a different
result.

Now that the executor understands about multiple join rules (for
OUTER JOIN support), I've been thinking about inventing a new join rule
that says "at most one output row per left-hand row" --- this'd be sort
of the opposite of the LEFT OUTER JOIN rule, "at least one output row
per left-hand row" --- and then transforming IN (sub-select) clauses 
that appear at the top level of WHERE into this kind of join.  Won't
happen for 7.1, though.

Same trick could be used for some classes of queries which do a SELECT DISTINCT
on the results of a join, too ...



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] beta testing version

2000-11-30 Thread Don Baccus

At 07:02 PM 11/30/00 -0400, The Hermit Hacker wrote:

v7.1 should improve crash recovery for situations like this ... you'll
still have to do a recovery of the data on corruption of this magnitude,
but at least with the WAL stuff that Vadim is producing, you'll be able to
recover up until the point that the power cable was pulled out of the wall

No, WAL won't help if an actual database file is corrupted, say by a
disk drive hosing a block or portion thereof with zeros.  WAL-based
recovery at startup works on an intact database.

Still, in the general case you need real backup and recovery tools.
Then you can apply archives of REDOs to a backup made of a snapshot
and rebuild up to the last transaction.   As opposed to your last
pg_dump.

So what about mirroring (RAID 1)?  As the docs tell ya, that protects
you against one drive failing but not against power failure, which can
cause bad data to be written to both mirrors if both are actively 
writing when the plug is pulled.

Power failures are evil, face it! :)



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] beta testing version

2000-11-30 Thread Don Baccus

At 05:15 PM 11/30/00 -0800, Nathan Myers wrote:

As for replaying logs against a restored snapshot dump... AIUI, a 
dump records tuples by OID, but the WAL refers to TIDs.  Therefore, 
the WAL won't work as a re-do log to recover your transactions 
because the TIDs of the restored tables are all different.   

Actually, the dump doesn't record tuple OIDs (unless you specifically
ask for them), it just dumps source sql.  When this gets reloaded
you get an equivalent database, but not the same database, that you
started out with.

That's why I've presumed you can't run the WAL against it.

If you and I are wrong I'd love to be surprised!



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] beta testing version

2000-11-28 Thread Don Baccus

At 04:17 PM 11/28/00 +0800, xuyifeng wrote:
Hi,

   how long is PG7.1 already in beta testing? can it be released before Christmas day?
   can PG7.1  will recover database from system crash?

This guy's a troll from the PHP Builder's site (at least, Tim Perdue and I suspect this
due to some posts he made in regard to Tim's SourceForge/Postgres article).

Since he's read Tim's article, and at least some of the follow-up posts (given that
he's posted responses himself), he should know by now that PG 7.1 is still in a 
pre-beta
state and won't be released before Christmas day.  I also posted a fairly long answer
to a question Tim's posted at phpbuilder.com regarding recoverability and this guy's
undoubtably read it, too.

Have I forgotten anything, xuyifeng?



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] Question about Oracle compatibility

2000-11-28 Thread Don Baccus

At 09:59 AM 11/28/00 +, Pete Forman wrote:
Mario Weilguni writes:
  This gets really bad when the actual data is coming from a
  webinterface, I've to handle 2 different queries for the case empty
  string and non-empty string.

In their documentation both Oracle 7 and 8 state:

Oracle currently treats a character value with a length of zero
as null.  However, this may not continue to be true in future
releases, and Oracle recommends that you do not treat empty
strings the same as NULLs.

Yeah, but this is harder than it sounds!  NULL and '' are indistinguishable
in queries, so how do you treat them differently?  Has to be in the 
application code, I guess.




- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



RE: [HACKERS] Constraint names using 'user namespace'?

2000-11-28 Thread Don Baccus

At 02:18 PM 11/28/00 +0800, Christopher Kings-Lynne wrote:
 As for the treading-on-user-namespace issue, we already do that for all
 implicitly created indexes (see UNIQUE, PRIMARY KEY, etc).  I'd prefer
 to treat named constraints consistently with that long-established
 practice until we have a better idea that can be implemented uniformly
 across that whole set of constructs.  (Once we have schemas, for
 example, it might be practical to give indexes a separate namespace
 from tables, which'd help a lot.)

Surely the best way to do it would be to make the unique and primary key
implicitly created indices totally invisible to the user.  Or at least add a
'system' flag to their entries in the pg_indexes table.  Create a
pg_constraint table instead that people can use to find constraints.

Oracle has a "user_constraints" table.  Explicitly named contraints have
that name entered into the user's namespace, implicitly named constraints
get stuffed into "sys" in the form "sys.cn", where "n" is drawn
from some system sequence.

In Oracle you NEED the user_constraints table, particularly for RI constraint
errors, because their wonderful error messages just give you the RI constraint
name.  If you've not given it a meaningful name yourself, which typically one
doesn't ("integer references some_table"), you need to do a select on the
user_constraints table to see what went wrong.

Keep PG's superior error messages no matter what else is done :)

The above is offered as a datapoint, that's all.

To support this, dropping unique and pk constraints would no longer be
possible (and _should_ no longer be possible) with a CREATE/DROP INDEX
command, and instead would be achieved with a functional ALTER TABLE
ADD/DROP CONSTRAINT statement.

This is essentially the case in Oracle, though I suspect you could dig
around, find the name of the unannounced unique index, and drop it by
hand if you wanted.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] Re: [NOVICE] Re: re : PHP and persistent connections

2000-11-27 Thread Don Baccus

At 10:46 AM 11/27/00 -0600, Ross J. Reedstrom wrote:
Uh, Don?
Not all the world's a web page, you know. Thatkind of thinking is _so_
mid 90's ;-) Dedicated apps that talk directly the user seem to be making
a comeback, due to a number of factors.  They can have much cleaner user
interfaces, for example.

Of course.  But the question's been raised in the context of a web server,
and I've answered in context.

I've been trying to move the discussion offline to avoid clogging
the hackers list with this stuff but some of the messages have escaped
my machine with my forgetting to remove pg_hackers from the distribution
list.  I'll try to be more diligent if the discussion continues.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] Question about Oracle compatibility

2000-11-27 Thread Don Baccus

At 12:39 PM 11/27/00 -0500, Alex Perel wrote:
On Mon, 27 Nov 2000, Mario Weilguni wrote:

 Sorry if I'm posting to the wrong list, but I don't know which list is 
 appropriate for this question.
 
 I've a question concerning compatibilty Postgres - Oracle. In Oracle, 
 empty strings and null are basicly the same, but it does not seem to 
 be under Postgres, making migration a pain.
 

Actually, they aren't the same at all under Oracle or under Postgres.

A null represents a lack of data, whereas an empty string is represents
data of zero length and zero content. Null is a state and not a value.

Unfortunately Mario's entirely correct (I use Oracle...)

insert into foo (some_string) values ('');

will insert a NULL, not an empty string, into the column some_string.

What you are probably seeing is a difference in table layout that sets
a default value of '' for the particular column you're touching. You can 
have postgres do the same by specifying DEFAULT '' when you create your
table (or you could ALTER it in..).

Using "DEFAULT ''" might help some, but he specifically mentioned inserting
form data from a web page, and in this case he'll have to check the string
and explicitly insert NULL (or write a trigger for each table that does
the check and the resulting massage of the value) or rewrite his queries
to treat empty string as being the same as NULL explicitly.

Null values are actually quite important because they tell you when you 
don't have data. An empty tring means something is there, whereas a null
in the same place means complete absense of all data.

Absolutely right, and Oracle's misimplementation truly sucks.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] 8192 BLCKSZ ?

2000-11-27 Thread Don Baccus

At 08:39 PM 11/27/00 -0500, Bruce Momjian wrote:
[ Charset ISO-8859-1 unsupported, converting... ]
 If it breaks anything in PostgreSQL I sure haven't seen any evidence -- the
 box this database is running on gets hit pretty hard and I haven't had a
 single ounce of trouble since I went to 7.0.X

Larger block sizes mean larger blocks in the cache, therefore fewer
blocks per megabyte.  The more granular the cache, the better.

Well, true, but when you have 256 MB or a half-gig or more to devote to
the cache, you get plenty of blocks, and in pre-PG 7.1 the 8KB limit is a
pain for a lot of folks.

Though the entire discussion's moot with PG 7.1, with the removal of the
tuple-size limit, it has been unfortunate that the fact that a blocksize
of up to 32KB can easily be configured at build time hasn't been printed
in a flaming-red oversized font on the front page of www.postgresql.org.

THE ENTIRE WORLD seems to believe that PG suffers from a hard-wired 8KB
limit on tuple size, rather than simply defaulting to that limit.  When
I tell the heathens that the REAL limit is 32KB, they're surprised, amazed,
pleased etc.

This default has unfairly contributed to the poor reputation PG has suffered
from for so long due to widespread ignorance that it's only a default, easily
changed.

For instance the November Linux Journal has a column on PG, favorable but
mentions the 8KB limit as though it's absolute.  Tim Perdue's article on
PHP Builder implied the same when he spoke of PG 7.1 removing the limit.

Again, PG 7.1 removes the issue entirely, but it is ironic that so many
people had heard that PG suffered from a hard-wired 8KB limit on tuple
length...



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] 8192 BLCKSZ ?

2000-11-27 Thread Don Baccus

At 09:30 PM 11/27/00 -0500, Bruce Momjian wrote:

 Well, true, but when you have 256 MB or a half-gig or more to devote to
 the cache, you get plenty of blocks, and in pre-PG 7.1 the 8KB limit is a
 pain for a lot of folks.

Agreed.  The other problem is that most people have 2-4MB of cache, so a
32k default would be too big for them.

I've always been fine with the default, and in fact agree with it.  The
OpenACS project recommends a 16KB default for PG 7.0, but that's only so
we can hold reasonable-sized lzText strings in forum tables, etc.

I was only lamenting the fact that the world seems to have the impression
that it's not a default, but rather a hard-wired limit.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] Full text Indexing -out of contrib and into main..

2000-11-27 Thread Don Baccus

At 11:06 PM 11/27/00 -0400, The Hermit Hacker wrote:
On Mon, 27 Nov 2000, Bruce Momjian wrote:

 OK, can someone collect suggestions, add the code, and integrate it for
 7.1?

too late in cycle ... 

Yes...



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] Re: [NOVICE] Re: re : PHP and persistent connections

2000-11-25 Thread Don Baccus

At 12:07 AM 11/26/00 -0500, Alain Toussaint wrote:

how about having a middle man between apache (or aolserver or any other
clients...) and PosgreSQL ??

that middleman could be configured to have 16 persistant connections,every
clients would deal with the middleman instead of going direct to the
database,this would be an advantage where multiple PostgreSQL server are
used...

Well, this is sort of what AOLserver does for you without any need for
middlemen.  

Again, reading stuff like this makes me think "ugh!"

This stuff is really pretty easy, it's amazing to me that the Apache/db
world talks about such kludges when they're clearly not necessary.

My first experience running a website (donb.photo.net) was with Apache
on Linux on an old P100 system in 1996 when few folks had personal photo
sites with 1000 photos on them getting thousands of hits a day.  I have
fond memories of those days, and Apache served me (or more properly webserved
my website) well.  This site is largely responsible for my reputation that
lets me freelance nature photography to the national media market pretty
much at will.  Thus my fondness.

But ... for database stuff the release of AOLserver as first Free Beer,
and now Free Speech software has caused me to abandon Apache and suggestions
like the above just make me cringe.

It shouldn't be that hard, folks.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] Re: [NOVICE] Re: re : PHP and persistent connections

2000-11-25 Thread Don Baccus

At 10:00 PM 11/25/00 -0800, Mitch Vincent wrote:
I've tried quite a bit to use persistent connections with PHP (for over
a year) and always the scripts that I try to use them with behave crazy...
The last time I tried there were problems all over the place with PHP,
variables getting overwritten, certain functions just totally breaking
(date() to name one) and so on.. I know I'm not being specific but my point
is that I think there are some other outstanding PHP issues that play into
this problem as the behavior that I've seen isn't directly related to
PostgreSQL but only happens when I use persistent connections.. 

I've heard rumors that PHP isn't thoroughly threadsafe, could this be a
source of your problems?




- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] OK, that's one LOCALE bug report too many...

2000-11-24 Thread Don Baccus

At 07:32 PM 11/24/00 -0500, Tom Lane wrote:
Possible compromise: let initdb accept en_US, but have it spit out a
warning message:

NOTICE: initializing database with en_US collation order.
If you're not certain that's what you want, then it's probably not what
you want.  We recommend you set LC_COLLATE to "C" and re-initdb.
For more information see appropriate place in admin guide

Thoughts?

Are you SURE you want to use en_US collation? [no]

(ask the question, default to no?)

Yes, a question in initdb is ugly, this whole thing is ugly.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: AW: [HACKERS] Please advise features in 7.1

2000-11-23 Thread Don Baccus

At 12:28 PM 11/23/00 +0100, Zeugswetter Andreas SB wrote:

 Reason: I want to know if any of these features are scheduled.
 
 1. Calculated fields in table definitions . eg.
 
Create table test (
 A Integer,
 B integer,
the_sum   As  (A+B),
 );

This is currently easily done with a procedure that takes a tabletype
parameter
with the name the_sum returning the sum of a + b.

   Create table test (
 A Integer,
 B integer
);

create function the_sum (test) returns integer as
'
   begin;
   return ($1.a + $1.b);
   end;
' language 'plpgsql';

A select * won't return the_sum

create view test2 select A, B, A+B as the_sum from test;

will, though.

See, lots of ways to do it!



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] Please advise features in 7.1

2000-11-23 Thread Don Baccus

At 06:00 PM 11/23/00 +1300, John Huttley wrote:

1. Calculated fields in table definitions . eg.

   Create table test (
A Integer,
B integer,
   the_sum   As  (A+B),
);

...

These are _extraordinarily_ useful for application development.

If anyone has a way of bolting on any of these to 7.0, I'd be keen to hear
from
you.

Create a trigger on insert/update for this case...



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



[HACKERS] OpenACS datamodel vs. current PG 7.1 sources

2000-11-22 Thread Don Baccus

After Tom's bug fix, I can now load the data model with no
problem.

Very cool, I'm pumped!



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] Table/Column Constraints

2000-11-21 Thread Don Baccus

At 12:18 AM 11/21/00 -0500, Tom Lane wrote:
Don Baccus [EMAIL PROTECTED] writes:
 If this problem is attacked, should one stop at constraints or make certain
 that other elements like views are dumped properly, too?  (or were views
 fixed for 7.1, I admit to a certain amount of "ignoring pgsql-hackers over
 the last few months")

...

Views do seem to be dumped as views by current sources.

Good...definitely a step in the right direction!



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



RE: [HACKERS] Crash during WAL recovery?

2000-11-21 Thread Don Baccus

At 02:01 PM 11/21/00 -0800, Mikheev, Vadim wrote:
 This snippet in xlog.c makes we wonder...
 
  else if (ControlFile-state == DB_IN_RECOVERY)
  {
  elog(LOG, "Data Base System was interrupted 
 being in recovery at %s\n"
   "\tThis propably means that some data 
 blocks are corrupted\n"
   "\tAnd you will have to use last 
 backup for recovery",
   str_time(ControlFile-time));
  }
 
 I thought this was going to be crash safe.

WAL doesn't protect against disk block corruption what
could be reason of crash (or elog(STOP)) during recovery
in most cases. Apart from disk corruption recovery is
(or should be -:)) crash safe.

Which is why we'll still need BAR tools later.

The WAL log can be used to recover from a crash if the database
itself isn't corrupted (disk corruption, whatever), but not
otherwise because it applies logged data to the database itself.

The WAL log doesn't include changes caused by renegade disk
controllers, etc :)

BAR tools will allow recovery via archives of WAL logs applied
to an archive of the database, to recreate the database in the
case where the existing database has been corrupted.

In Oracle parlance, "WAL" log == "REDO" log, and the BAR tool
builds "Archive" logs.

Uhhh...I think, anyway.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



RE: [HACKERS] Crash during WAL recovery?

2000-11-21 Thread Don Baccus

At 09:14 AM 11/22/00 +0800, Christopher Kings-Lynne wrote:
Is there any particular reason the spelling and punctuation in the code
snippet below is so bad?

Vadim's Russian.  This impacts his english but not his ability to implement
complex features like MVCC and WAL :)



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] query plan optimizer bug

2000-11-21 Thread Don Baccus

At 10:46 AM 11/22/00 +0800, xuyifeng wrote:
Hi,

it's obviously there is a query plan optimizer bug, if int2 type used in
fields,
the plan generator just use sequence scan, it's stupid

Have you checked this with real data after doing a VACUUM ANALYZE?



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] Crash during WAL recovery?

2000-11-21 Thread Don Baccus

At 12:29 AM 11/22/00 -0500, Tom Lane wrote:
 Is there any particular reason the spelling and punctuation in the code
 snippet below is so bad?

 Vadim's Russian.  This impacts his english but not his ability to implement
 complex features like MVCC and WAL :)

As someone who can't speak anything but English worth a damn (even
though I was raised in Spanish-speaking countries, so you'd think
I'd have acquired at least one clue), I have long since learned not
to criticize the English of non-native speakers.

I think it's certain that the original poster didn't realize Vadim is not
a native English speaker, which is why I made my comment (to clue him in).
Vadim didn't take my comment as criticism, as his follow-on post made clear
(he got the joke).  I don't know from your post if you thought I was adding
to the criticism or not, but I can say with certainty I wasn't.  In my
previous life as the founder of a company specializing in optimizing
compilers for minicomputers, I employed Dutch (who spoke and wrote English
than I or anyone here), Polish, Vietmanese and other nationals who were
excellent hackers and who all spoke better English than I spoke their 
language - or cooked their cuisine or even followed their table customs,
for that matter.

More generally, a lot of the PG documentation could use the attention
of a professional copy editor --- and I'm sad to say that the parts
contributed by native English speakers aren't necessarily any cleaner
than the parts contributed by those who are not.  If you have the
time and energy to submit corrections, please fall to!

This is very much true.  PG needs some good documentation volunteers.
I'm not denigrating the current efforts, because PG documention's pretty 
good all things considered.  But some volunteers devoted to improving
the docs could accomplish a lot.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] (download ANSI SQL benchmark?) Re: Postgres article

2000-11-21 Thread Don Baccus

At 10:19 AM 11/21/00 +, Pete Forman wrote:
Don Baccus writes:
  I also hope that the PG crew, and Great Bridge, never stoop so low
  as to ship benchmarks wired to "prove" PG's superiority.

I thought that Great Bridge's August benchmarks were rather skewed.
They only used one particular test from the AS3AP suite.  That was the
basis for their headline figure of 4-5 times the performance of the
competition.

I was however impressed by the TPC-C results.  MySQL and Interbase
were unable to complete them.  PostgreSQL showed almost identical
performance over a range of loads to Proprietary 1 (version 8.1.5, on
Linux) and Proprietary 2 (version 7.0, on NT).

Great Bridge didn't do the benchmarking, they hired a third party to
do so.  And that third party didn't, AFAIK, cherry-pick tests in order
to "prove" PG's superiority.

The report itself mentioned the testing group's surprise over MySQL's
poor showing in the simple, non-TPC-C test.  I'm sure it was tossed
in so they could answer the question "how much does it cost you to
use a transaction-based system rather than MySQL", since avoiding that
overhead is the big argument that the MySQL makes in favor of their
product.  I'm sure the hope was there that the answer would be "not all
that much", instead the answer was "gee, you're not that fast after
all".

Clearly the real target of the benchmark effort was Oracle.  However
inadequate the benchmarking effort might've been (they're all inadequate,
after all) the fact is that Great Bridge at least did run a set of
standard benchmarks.

The MySQL folk have always cherry-picked their benchmarks, long lied
about features in PG, do their benchmarking using default values
for PG's shared buffer etc WITHOUT TELLING PEOPLE while at the same
time installing MySQL with larger-than-default memory usage limits (the
group hired by GB used MySQL's default installation, but EXPLICITLY SAID
SO in the report), etc.

The GB-financed benchmarks weren't perfect, but they weren't dishonest.
The MySQL folks have done things over the years that have been out-and-out
dishonest, IMO.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



[HACKERS] building current sources

2000-11-20 Thread Don Baccus

After reading Vadim's note stating the WAL is enabled by default,
I downloaded sources from CVS to rebuild the latest version.

There are errors in ecpg's preproc.y grammar that weren't there 
in the CVS sources I built yesterday.

The two rules "createdb_opt_item" and "createdb_opt_list" aren't
declared as type string at the front of the file, so bison gets
upset.  

Two other rules, "createdb_opt_encoding" and "createdb_opt_location"
were declared as type string but never defined as rules.  Bison
doesn't like that, either.

The first clause for rule CreatedbStmt references a sixth item that
doesn't exist in the definition.

I've cleaned these up locally (though I just removed the $6 mentioned
last because I have no idea what was intended) so I can compile.  Someone
should clean up the CVS sources...



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] PG 7.1 pre-beta bug ...

2000-11-20 Thread Don Baccus

At 07:20 PM 11/20/00 -0500, Tom Lane wrote:
Don Baccus [EMAIL PROTECTED] writes:
 All went well except for a handful of occurances of the following error:
 ERROR:  SS_finalize_plan: plan shouldn't reference subplan's variable

Fixed, I believe.  Your test case now gives

regression=# select user_group_add('group', 'shortname', 'prettyname', 'f');
ERROR:  ExecAppend: Fail to add null value in not null attribute
registration_date

but that's correct AFAICT, and 7.0.2 agrees...

Yeah, I boiled down my example a bit too far for the case where the RDBMS
works, apparently :)   There's probably a trigger to fill the registration
date that I stripped out, something like that.

Thanks ... I'll not be able to get back to testing until later this week (I'm
busy with a client site, Oracle-based, boo-hoo).



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] Table/Column Constraints

2000-11-20 Thread Don Baccus

At 10:49 PM 11/20/00 -0500, Tom Lane wrote:
"Christopher Kings-Lynne" [EMAIL PROTECTED] writes:
 Just to catch up here - does this mean that pg_dump has issues with
 correctly recreating the contraints?

Well, if you examine the pg_dump output, it doesn't really try ---
you'll see no sign of any foreign-key constraint declarations in
a pg_dump script, for example, only trigger declarations.  This is
correct as far as reproducing the working database goes, but it's
bad news for making a readable/modifiable dump script.

Short story, you are both right.

Chris - the dumps reload and recreate the constraints (in other words,
the answer to your question is "no")

Tom's correct in that decyphering the dump output is an ... interesting
problem.

(Tom, I just want to make sure that Chris undertands that dump/restore
DOES restore the constraints.  The "it doesn't really try" statement
you made, if hastily read without the qualifier, would lead one to believe
that a dump/restore would lose constraints).

What Tom's saying is the internal implementation of the SQL constraints
are exposed during the dump, where it would be much better if the SQL
that constructed the constraint were output instead.  The implementation
isn't hidden from the dump, rather the declaration is hidden.

What's worse,
this representation ties us down over version updates: we cannot easily
change the internal representation of constraints, because the internal
representation is what's getting dumped.

Which follows up my statement above perfectly.  If the implementation
were hidden, and the SQL equivalent dumped, we could change the implementation
without breaking dump/restore ACROSS VERSIONS.  (I capped because WITHIN
A VERSION dump/restore works fine).

 
 Problem is that there are 5 difference types of constraints, implemented in
 5 different ways.  Do you want a unifed, central catalog of constraints, or
 just for some of them, or what?

Dunno.  Maybe a unified representation would make more sense, or maybe
it's OK to treat them separately.  The existing implementations of the
different types of constraints were done at different times, and perhaps
are different "just because" rather than for any good reason.  We need
investigation before we can come up with a reasonable proposal.

I think you hit the nail on the head when earlier you said that representation
was driven by the implementation.

Of course, one could say this is something of a PG tradition - check out
views,
which in PG 7.0 still are dumped as rules to the rule system, which no other
DB will understand.

So I can't say it's fair to pick on newer contraints like RI - they build
on a tradition of exposing the internal implementation to pg_dump and its
output, they didn't invent it.

If this problem is attacked, should one stop at constraints or make certain
that other elements like views are dumped properly, too?  (or were views
fixed for 7.1, I admit to a certain amount of "ignoring pgsql-hackers over
the last few months")



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] Table/Column Constraints

2000-11-20 Thread Don Baccus

At 12:03 AM 11/21/00 -0500, Tom Lane wrote:

Peter has remarked that the SQL spec offers a set of system views
intended to provide exactly this info.  That should be looked at;
if there's a workable standard for this stuff, we oughta follow it.

This and a BUNCH else.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL

2000-11-20 Thread Don Baccus

At 09:43 AM 11/13/00 -0600, [EMAIL PROTECTED] wrote:
I made it all the way through the article.  I'll summarize it for you:
Postgres - hooray!
MySQL - boo!
Since this is an open source database article linked off of slashdot, I
imagine they're getting pounded.

Why is all this e-mail showing up so late?

(I'm curious because there have been complaints about the mail server here,
and the article is old hat).



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] (download ANSI SQL benchmark?) Re: Postgres article

2000-11-20 Thread Don Baccus
At 10:24 AM 11/13/00 -0800, Limin Liu wrote: 
>>>>
This's great.  I have tested Postgres and MySQL with the benchmark shipped with mysql and (of course) MySQL out perform Postgres. 



So how many simultaneous read/write processes does the MySQL benchmark fire up?

Why test a benchmark provided by the mysql folk?  That's like trying the benchmark
provided by Intel for the initial Pentium 4 announcement and ignoring all the
benchmarks they didn't provide you because AMD thunderbird+DDR (AMD 760 chipset)
kicks P4 butt on many of them.

I should hope you're not so naive as to suppose that the MySQL folk would ship a
benchmark showing better performance by PG (or Oracle, or Sybase etc)?

I also hope that the PG crew, and Great Bridge, never stoop so low as to ship
benchmarks wired to "prove" PG's superiority.

They MySQL folk have been liars and cheaters for years, there's no reason to
put any faith into their benchmark efforts.

>>>>





- Don Baccus, Portland OR [EMAIL PROTECTED]>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net. 

Re: [HACKERS] RE: [COMMITTERS] pgsql/src/backend/access/transam (xact.c xlog.c)

2000-11-19 Thread Don Baccus

At 07:05 PM 11/19/00 +0100, [EMAIL PROTECTED] wrote:

Cam I ask what BAR is ?

Backup and recovery, presumably...



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] Coping with 'C' vs 'newC' function language namesh

2000-11-16 Thread Don Baccus

At 05:51 PM 11/16/00 +0200, 'Marko Kreen' wrote:
On Thu, Nov 16, 2000 at 09:32:43AM -0600, Ross J. Reedstrom wrote:
 On Thu, Nov 16, 2000 at 04:16:26PM +1100, Philip Warner wrote:
  Create Module foo_mod from library 'path-to-lib';
 
 Phil - be careful with the nomenclature. We've got another naming
collision,
 here. SQL9[29] talk about modules, which may or may not be related to what
 your suggesting here.

Do you know any url's where the SQL* standards could be looked
up?

I have a copy of the SQL92 draft (the one that's circulated among this
group in
the past) at dsl-dhogaza.pacifier.net.  Just use anonymous ftp, it's in the
pub
directory with an obvious name (sql1992.txt???)



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] RE: [COMMITTERS] pgsql/src/backend/access/transam ( xact.c xlog.c)

2000-11-16 Thread Don Baccus

At 02:13 PM 11/16/00 -0500, Bruce Momjian wrote:

 I think the default should probably be no delay, and the documentation
 on enabling this needs to be clear and obvious (i.e. hard to miss).

I just talked to Tom Lane about this.  I think a sleep(0) just before
the flush would be the best.  It would reliquish the cpu slice if
another process is ready to run.  If no other backend is running, it
probably just returns.  If there is another one, it gives it a chance to
complete.  On return from sleep(0), it can check if it still needs to
flush.  This would tend to bunch up flushers so they flush only once,
while not delaying cases where only one backend is running.

This sounds like an interesting approach, yes.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] RE: [COMMITTERS] pgsql/src/backend/access/transam ( xact.c xlog.c)

2000-11-16 Thread Don Baccus

At 09:32 AM 11/16/00 -0800, Alfred Perlstein wrote:
* Bruce Momjian [EMAIL PROTECTED] [001116 08:59] wrote:

 Ewe, so we have this 1/200 second delay for every transaction.  Seems
 bad to me.

I think as long as it becomes a tunable this isn't a bad idea at
all.  Fixing it at 1/200 isn't so great because people not wrapping
large amounts of inserts/updates with transaction blocks will
suffer.

I think the default should probably be no delay, and the documentation
on enabling this needs to be clear and obvious (i.e. hard to miss).



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL

2000-11-15 Thread Don Baccus

At 01:53 PM 11/15/00 -0500, markw wrote:

I'd rather not pollute the application's SQL with postgres-isms. Not that I
don't love postgres, but there are always critics looking for a reason to use
Oracle or (gasp) MS-SQL.

Define some global variable with the name of the database being run (currently
only Postgres) and guard the SET statement with a conditional...

In the OpenACS project we've got little functions that return query snippets
called things like "db_nextval" that return either "sequence_name.nextval"
or "nextval('sequence_name')" depending on whether the code's running
under Oracle or Postgres.  That helps us minimize differences in the source.




- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL

2000-11-15 Thread Don Baccus

At 09:27 AM 11/15/00 -0800, Tom Samplonius wrote:

  AOLServer isn't the only system that can pool database connections, so
can servlets/JSP, ColdFusion, ASP, etc.  No doubt AOLServer would be more
widely accepted if it used something other than TCL.

There are two separate modules that support Java in AOLserver: ns_tomcat
which provides an identical interface as Apache tomcat (and no real
advantages) and ns_java, which is coming out of the OpenACS project.  ns_java
exposes AOLserver's pooled, persistent database API to java.

There's also support available for Python, though there's still a lot of
work to be done to support the full AOLserver API (same's true of ns_java,
actually).

If you use ADP pages, your use of Tcl is typically restricted to snippets of
code anyway, so I've never really understood the complaints about Tcl...



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] Danish database patent

2000-11-11 Thread Don Baccus

At 06:35 PM 11/11/00 +0200, Hannu Krosing wrote:

I took only a brief look at them, so i may be in a state of
misunderstanding :)

As I understand it, the patent is about generating/composing queries
_before_ 
submitting them to backend, not about query processing in the backend.

Yes, it's a query generation technique separate from the database server,
generating and executing SQL queries on a set of tables in order to satisfy
some goal.

Has nothing to do with the RDBMS itself.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] Text concat problem

2000-11-09 Thread Don Baccus

At 05:47 PM 11/8/00 -0600, Luis =?UNKNOWN?Q?Maga=F1a?= wrote:

insert into employee(title,first_name,start_date,charge) values('Mr.
X','Smith',date(now()),'None');
insert into employee(title,first_name,start_date,charge) values('Mr.
Y','Smith',date(now()),'None');
insert into employee(title,first_name,start_date,charge) values('Mr.
Z','Smith',date(now()),'None');

so far there is no problem at all, the problem comes here:

select title || ' ' || first_name || ' ' || last_name as fullname from
employee;

   fullname   

  
 
  
 (3 rows)

Doesn't work , I'm thinking it is because of the null value in last_name.

Right.  NULL means "has no value", it's not the empty string.  The result of
concatenating with NULL is NULL.

 Have any idea or suggestion on how to workaround this situation.

It's a classic NULL issue.  1+NULL = NULL, too, for instance.  Try
something like
"default ''" in your table definition rather than use null.  Then you'll be
using
the empty string for concatenation.  'abc' || NULL = NULL.  'abc' || '' =
'abc'
which appears to be what you want.

This is standard SQL92 behavior...




- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] Question about reliability?

2000-11-09 Thread Don Baccus

At 10:43 AM 11/9/00 -0500, Tom Lane wrote:

 Would there be any potential to avoid these (possibly) unnecessary deaths?

No, at least it'll never get my vote.

Besides, it's not that difficult for an application to recover from these
prophylactic backend deaths.  My PG driver for AOLserver does so
transparently,
retrying queries that get the "sorry I've been asked to shut down because some
other backend screwed up and died - please retry your query" but returning an
error for the actual query that caused a backend to hose itself.

The code using the driver is unaware that anything has happened (other than
the thread issuing the query that hosed the backend that died in execution, of
course).



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] LIMIT in DECLARE CURSOR: request for comments

2000-10-28 Thread Don Baccus

At 12:18 PM 10/27/00 -0400, Tom Lane wrote:

Hiroshi was a little concerned about this change in behavior, and
so the first order of business is whether anyone wants to defend the
old way?  IMHO it was incontrovertibly a bug, but ...

Sure feels like a bug to me.  Having it ignored isn't what I'd expect.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



[GENERAL] Re: [HACKERS] My new job

2000-10-10 Thread Don Baccus

At 01:02 PM 10/10/00 -0400, Tom Lane wrote:

Bottom line is we're not sure what to do now.  Opinions from the 
floor, anyone?

Yeah, quit worrying and work your collective butts off on 7.1 and 7.2 :)

Seriously...the core group is obviously committed to PG, and appear to 
be folks of integrity.  We all will benefit by your working on PG full
time while being paid enough so you can eat, drink, and be merry, too.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



Re: [HACKERS] ALTER TABLE DROP COLUMN

2000-10-09 Thread Don Baccus

At 07:55 PM 10/9/00 -0300, The Hermit Hacker wrote:

 I am not convinced that a 2x penalty for DROP COLUMN is such a huge
 problem that we should give up all the normal safety features of SQL
 in order to avoid it.  Seems to me that DROP COLUMN is only a big
 issue during DB development, when you're usually working with
 relatively small amounts of test data anyway.

Actually, I could see DROP COLUMN being useful in a few other places
... recently, I spent several hours re-structuring a clients database that
had been built by someone else who didn't know what 'relational' means in
RDBMS ... or how about an application developer that decides to
restructure their schema's in a new release and provides an 'upgrade.sql'
script that is designed to do this?  

This last example is one reason DROP COLUMN would be a great help to
the OpenACS development effort.

However, upgrades (new releases) are fairly infrequent, and many users of
current versions won't bother unless they've run into toolkit bugs
(same goes for updating PG).  Those who do know that doing an upgrade
will require planning, testing on a system that's not running their
"live" website, and some amount of downtime.

So I don't think a 2x penalty is a huge problem.

That would make for a very painful upgrade process if I have to go through
the trouble of upgrading my hardware to add more space ...

For many folks, if eating 2x the size of a single table runs their system out
of disk space, clearly they should've upgraded long, long ago.  An OpenACS
site has hundreds of tables, I can't imagine running my disk space so tight
that I couldn't double the size of one of them long enough to do a DROP
COLUMN.

Obviously, some folks doing other things will have single tables that are
huge,
but after all they can always do what they do now - not drop columns.



- Don Baccus, Portland OR [EMAIL PROTECTED]
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.