L. Berger wrote:
> On May 23, 8:47 am, "EMS Database Management Solutions
> (SQLManager.net)" <sqlmana...@gmail.com> wrote:
>> We, here at EMS Database Management Solutions, are pleased to announce
>> SQL Manager 2007 for PostgreSQL - the new major version of the
>> powerful PostgreSQL administration and development tool!
>>
>> You can download SQL Manager 2007 for PostgreSQL at:http://www.sqlmanager.net/products/p...nager/download
>>
>> You can purchase SQL Manager 2007 for PostgreSQL at:http://www.sqlmanager.net/products/p...ql/manager/buy
>>
>> What's new in SQL Manager 2007 for PostgreSQL?
>
> <snip>
>
>
> Thanks for this, but is there any plan to launch something like this
> for use on Linux admin servers? Something that I could install on a
> server, and perhaps work with a web interface? I would love some
> recommendations.

Maybe I'm a bit old fashioned, but I would never ever consider
graphical frontends for a server. I mean, ssh -L and
local GUI client work very well here. Even web based stuff
seems dangerous (if not just for educational purposes)

Regards
Tino

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

> P.S. it's not the "the cloud" anymore, it's "the tubes".

It was always tubes. The cloud was just a convenient simplification for the
technically declined ;-)

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice



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

http://archives.postgresql.org/

On Sun, 2007-06-03 at 22:54 +0200, Alexander Staubo wrote:
> I agree with you and I don't; as it stands now, it's too hard to
> implement validation in the database alone, for the reasons I stated
> earlier. But I would love for it to be possible, so that I can be sure
> that not even plain SQL can screw up the data.

You're blurring the line between an RDBMS and an application.
Applications errors and database errors do not have a one-to-one
mapping, although they do usually overlap.

There are times when one database error maps onto several possible
user-level errors; and when many database errors map onto the same
user-level error; and when one database error does not cause any
user-level error; and when something that is a user-level error might
not have a matching constraint in the database at all. Trying to equate
the two concepts is a bad idea.

The application has much more information about the user and the context
of the error that the database shouldn't have. For instance, the
language that the user speaks might affect the error message. Or, there
may be two possible user interface actions that result in the same
constaint violation. For instance if you have a two-column unique
constraint, perhaps there is one interface to change one column and one
another. But you might want to return a different error to the user that
makes sense in the context of which value they tried to change.

A database error doesn't even always need to be propogated back to the
user. If so, there would be no need for SAVEPOINTs and nobody would use
ISOLATION LEVEL SERIALIZABLE (not directly related to constraints, but
can cause an error just the same).

Some user errors don't have a corresponding database constriant at all.
For instance, how about a "re-type your password here" field? That
should cause an error if it doesn't match the "password" field, but the
database would have no matching constraint.

Regards,
Jeff Davis


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

On 6/4/07, Jeff Davis <pgsql@j-davis.com> wrote:
> On Sun, 2007-06-03 at 22:54 +0200, Alexander Staubo wrote:
> > I agree with you and I don't; as it stands now, it's too hard to
> > implement validation in the database alone, for the reasons I stated
> > earlier. But I would love for it to be possible, so that I can be sure
> > that not even plain SQL can screw up the data.
>
> You're blurring the line between an RDBMS and an application.
> Applications errors and database errors do not have a one-to-one
> mapping, although they do usually overlap.

True, and when they overlap you tend to want to describe the
validation errors in one place, not two -- either the database or the
app, not both. Relational databases have traditionally argued that
these rules should be in the former, so that there's one layer through
which every single change has to go.

> There are times when one database error maps onto several possible
> user-level errors; and when many database errors map onto the same
> user-level error; and when one database error does not cause any
> user-level error; and when something that is a user-level error might
> not have a matching constraint in the database at all. Trying to equate
> the two concepts is a bad idea.

I agree. In my experience, however, the best kind of data model is the
one that is immediately mappable to user-level concepts -- to human
concepts. A "user" relation has attributes like "name", "birth_date",
etc. If you manage to keep the model flat and friendly enough, you can
map the attributes to forms and translate attribute-level errors
directly to form error messages.

In the cases where a user-level attribute is represented by a set of
columns, or a referenced relation, or similar, you provide simple
shims that translate between them. For example, you probably want to
store date-time attributes as a single "timestamp with timezone"
column, but offer two fields to the user, one for the date and for the
time. With Rails this kind of shim is simple:

class User < ActiveRecord::Base
...
validates_each :human_birth_date do |record, user, value|
record.errors.add(attr, "Bad date") unless MyDateParser.valid?(value)
end

def human_birth_date
birth_datetime.strftime("%Y-%m-d")
end

def human_birth_date=(date)
year, month, day = MyDateParser.parse(date)
birth_datetime = Time.local(year, month, day, birth_datetime.hour,
birth_datetime.minute)
end
end

With a well-designed, normalized schema, mapping relations and their
attributes to user input is very easy. I would argue that if mapping
is a problem, your schema is probably to blame.

> The application has much more information about the user and the context
> of the error that the database shouldn't have. For instance, the
> language that the user speaks might affect the error message.

Localization is easily accomplished by piping the error message through gettext.

> Some user errors don't have a corresponding database constriant at all.
> For instance, how about a "re-type your password here" field? That
> should cause an error if it doesn't match the "password" field, but the
> database would have no matching constraint.

That's a user-interface detail, and not a data model detail; a
re-typed password has no database counterpart. I am speaking purely
about invariant constraints on the data itself.

Alexander.

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

On Mon, 2007-06-04 at 00:51 +0200, Alexander Staubo wrote:
> True, and when they overlap you tend to want to describe the
> validation errors in one place, not two -- either the database or the
> app, not both. Relational databases have traditionally argued that
> these rules should be in the former, so that there's one layer through
> which every single change has to go.
>

I disagree here. You often _do_ want to describe some types of errors
twice. You check the same thing in different ways at different points in
the code, and that isolates errors and assures developers that certain
assumptions are safe.

In the database world, it's particularly important to use declarative
constraints. If developer A inserts bad data and developer B uses that
bad data, it could compound the problem and yet remain invisible until
the problem is almost impossible to debug. Constraints assure the
developers that they are starting with some known state.

Applications should check for inconsistencies when it makes sense, as
well. Every important API that I can think of checks the input, and
reports some kind of useful error when the assumptions are violated.
Every system call has all sorts of useful error codes. For example,
read() can return EBADF. Nobody thinks "Hey, I'll send a random integer
for the file descriptor", but I'd be willing to bet that the error
condition has been reached by accident before, and probably saved
someone a lot of time versus just filling the target buffer with random
bytes and returning success.

I would argue it's more important in a database, because the error
conditions can persist for a longer period of time and cause more damage
in the process, but the idea is the same.

> I agree. In my experience, however, the best kind of data model is the
> one that is immediately mappable to user-level concepts -- to human
> concepts. A "user" relation has attributes like "name", "birth_date",
> etc. If you manage to keep the model flat and friendly enough, you can
> map the attributes to forms and translate attribute-level errors
> directly to form error messages.
>

I think you're oversimplifying. What you say works when user input is a
separate, contained, isolated chunk of data. In that case, any error is
only related to the self-consistency of the input, and can easily be
mapped back to a user-level error.

However, it breaks down when you have constraints on the
interrelationships between pieces of data. These interrelationships are
what can be broken from multiple points in the application code, and
there is no way to map backwards from the constraint violation to a
specific user error. Hence, the application must translate.

Try to imagine some of the complexities in a scheduling system, and what
kind of constraints that might involve. Then, think about how some of
the same constraints might be broken in very different ways. Time
conflicts could come about either by participants overscheduling
themselves, or by the event itself shifting in time such that some
participants are overscheduled. Perhaps someone tries to sign up for an
event that's already full, or perhaps the venue moves to a new location
with a lower capacity. I can't think of any way to map backwards from
the constraint violation to the user level error.

You could probably imagine similar problems with an inventory system.

> > The application has much more information about the user and the context
> > of the error that the database shouldn't have. For instance, the
> > language that the user speaks might affect the error message.
>
> Localization is easily accomplished by piping the error message through gettext.
>

And what about the two-column unique index that can be violated from two
different aspects of the UI? You only get one database error, but you
really should present two different errors to the user.

Any time that a constraint can be violated through two completely
different paths, your one-to-one constraint-to-application-error breaks
down.

The root of the issue is that the database knows that an inconsistent
state has been reached, it does not know _how_, nor should it. The how
might change as new code is added or perhaps as new bugs are introduced.
Constraints in an RDBMS should be declarative which is very important
(you don't need to prove that a column is always in a unique state, you
look, and it says it is unique). You can add procedural code to an
RDBMS, but you can end up making your database your application that
way.

User-level errors are heavily dependent on _how_ the error occurred. The
database doesn't know this, so the constraint violation message
shouldn't presume to know how it happened.

I'll use the analogy to read() again: who passes EBADF back to the user
directly? Does that mean it's a bad API? No, it just means it had no
idea why you passed it a bad file descriptor, but it knows it's bad, and
it tells the caller. Similarly with exceptions in java/ruby/python: you
shouldn't pass those exceptions back to the user.

Regards,
Jeff Davis


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

http://www.postgresql.org/docs/faq

Alexander Staubo schrieb:
> On 6/1/07, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
>> These are all different solutions to different problems, so it's not
>> surprising that they look different. This was the reason I asked,
>> "What is the problem you are trying to solve?"
>
> You mean aside from the obvious one, scalability?
>
> The databases is becoming a bottleneck for a lot of so-called "Web
> 2.0" apps which use a shared-nothing architecture (such as Rails,
> Django or PHP) in conjunction with a database. Lots of ad-hoc database
> queries that come not just from web hits but also from somewhat
> awkwardly fitting an object model onto a relational database.

....

> the single server, but I would hope that there would, at some point,
> appear a solution that could enable a database to scale horizontally
> with minimal impact on the application. In light of this need, I think
> we could be more productive by rephrasing the question "how/when we
> can implement multimaster replication?" as "how/when can we implement
> horizontal scaling?".
>
> As it stands today, horizontally partitioning a database into multiple
> separate "shards" is incredibly invasive on the application
> architecture, and typically relies on brittle and non-obvious hacks
> such as configuring sequence generators with staggered starting
> numbers, omitting referential integrity constraints, sacrificing
> transactional semantics, and moving query aggregation into the app
> level. On top of this, dumb caches such as Memcached are typically

Did you have a look at BizgresMPP?

Especially for your shared-nothing approach it seems to be a better
solution then just replicating everything.

Regards
Tino

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On 6/4/07, Tino Wildenhain <tino@wildenhain.de> wrote:
> Did you have a look at BizgresMPP?
>
> Especially for your shared-nothing approach it seems to be a better
> solution then just replicating everything.

I had completely forgotten about that one. Bizgres.org seems down at
the moment, but looking at their whitepaper, the architecture looks
similar to that of pgpool-II; from what I can see, you connect through
a proxy that transparently partitions data across multiple PostgreSQL
database instances, and then queries them in parallel and merges the
query results. Looks promising.

Alexander.

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

On Fri, 2007-06-01 at 22:13 -0500, Ron Johnson wrote:
> On 06/01/07 19:29, Jeff Davis wrote:
> [snip]
> > You shouldn't use a volatile function in a check constraint. Use a
> > trigger instead, but even that is unlikely to work for enforcing
> > constraints correctly.
> >
> > In general, for partitioning, you have to make some sacrifices. It's
> > very challenging (and/or expensive) to ensure uniqueness across
> > partitions.
>
> Are partitioned databases the same as federated databases?
>

I think that usually people refer to a table that is split to be
partitioned (whether across servers or within a single server). I think
federated databases are where various parts of the database are split
across servers, but tables may be intact.

That's my own understanding of the terminology.

Regards,
Jeff Davis


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Sun, Jun 03, 2007 at 07:47:04PM +0200, Alexander Staubo wrote:

> For example, part of the point of having validations declared on the
> model is so that you can raise user-friendly errors (and pipe them
> through gettext for localization) such as "Your password must be at
> least 4 characters long and contain only letters and digits".

If anyone is interested, we have sort of re-implemented gettext in SQL:

http://cvs.savannah.gnu.org/viewvc/g...l/?root=gnumed

(see the gmI18n-*.sql stuff)

In essence it enables you to write queries like so

select pk, name, _(name) as l10n_name from states

which will give you a localized name for states.name in l10n_name.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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