Hi Mathew,

On 7/4/2011 12:12 PM, Matthew Woodcraft wrote:
Because that information could be exploited by "smarter"
incarnations of the planner?

I.e., their philosophy being to let the planner do the thinking
and give it whatever information might be *helpful* to facilitate
its coming to a "better plan" and then just invest (development)
effort in *making* that "smarter planner"?

Presumably, the planner wouldn't be *obligated* to do as those
"non-hint hints" would otherwise suggest?

--don

Don Y wrote:
[about query hints]

Correct.

[about bad execution plans]

Usually, that's how it is. Unless you Do It Right and test with
a realistic set of data beforehand.

Yes. A query hint is part of the query and refers to certain
parts of it.

Right. That's what I am talking about.

That sounds like a really bad idea.

If the database user that performs SQL on behalf of the end user
has superuser privileges, that constitutes an unnecessary risk.
Security holes or bugs in your software can cause much more damage
that way.

Of course.

Then you need to learn about WAL and point-in-time recovery.

Yours,
Laurenz Albe

Hi Laurenz,

On 7/4/2011 11:58 PM, Laurenz Albe wrote:

So, unless you have accurate, prior knowledge of the dataset's
actual complexion, you stand a chance of shooting yourself in the
foot *if* you hint (based on assumptions you make that may not
turn out to be true)

No choice. There is no "organic" DBA involved. So, even if I
let autovacuum handle that aspect, there will always be other
aspects that have to be "coded" (and "Just Work")

OK. I can't guarantee that so it is something I will have to
be aware of.

<grin> I'll add it to the List of Stuff I Must Learn.

Thanks!
--don

Don Y wrote:
You can automatize administrative tasks, that's a good thing,
but that does not necessitate that the end user gets served by a
superuser account. Those things should be done separately.

You won't get away without some sort of human DBA.
You must at least ascertain that backups complete successfully
and space doesn't run out. And you need somebody who is able to
restore a backup. So at least when things go wrong, you need a DBA.

Yours,
Laurenz Albe

Hi Laurenz,

On 7/6/2011 12:00 AM, Laurenz Albe wrote:
The intend is to deploy in *devices* -- not "servers". For a
(crude) parallel, imagine the "contacts", etc. in your cell phone
were being managed by PostgreSQL. I.e., a set of relations
defining people, their phone numbers, log of recent calls, etc.

[recall, I am using a cell phone solely as an analogy as the
usage patterns are similar -- largely "fixed"]

The sorts of relations are all predefined. A (cell phone) "user"
could opt to add a name to his "contact list". Or, delete one.
Or "edit" an existing contact. Or, purge the log of the least
recent calls. etc.

He might even want to add "another" contact list (named "business
contacts"), etc.

But, he's not creating any arbitrary relations. And, the queries
he'll run are predefined -- with "fill in the blank" parameters, etc.

I.e., if PostgreSQL can't keep the relations intact, then it has
fundamental bugs (in which case, why would *anyone* be using it?).

If PostgreSQL isn't "ready for prime time", in that regard, I
have taken pains to define all my relations in such a way that I
could fall back to cruder tools (e.g., dbm-style where I do most
of the "work") and, even, a sparse VM implementation (linking
relations with actual pointers).

I have a trial, "server based" application running "unattended"
that has performed well so far (despite abuses to the server
itself). And, one of the two projects in the works currently
takes that a step further (more traffic, users, etc.). So,
I've got "an out" for the other project if I discover PG can't
"keep a clean house" without someone babysitting it... :<

<shrug> We'll see. AFAIK, it hasn't been tried on this large
a scale previously (?) As I said, the things I'm expecting from
the DBMS are probably different than what most users/DBA's would
expect.

--don

Don Y wrote:
This is pretty vague, but it sounds like nothing you need a
superuser account for.
Keep administrative stuff (VACUUM, backups) separate from the rest.

Yup, that's exactly where the user can use SQL injection to break
into your database.

You misunderstood me. PostgreSQL will keep its stuff consistent.
If somebody breaks into your database with a superuser account,
he or she can very consistently read and change everything in the
database, access the file system, and theoretically do anything with
your machine that the OS user has permissions to do.

I don't want to play Kassandra here, but most of the people who
want to use a software for something else than the intended use
become quite unhappy in the end.

Yours,
Laurenz Albe

Hi Laurenz,

On 7/7/2011 12:05 AM, Laurenz Albe wrote:
I'm not claiming to need a "privileged" account. Rather, there is
an agent (a piece of software) that sits between the "user" and
the DBMS that interacts with the DB on behalf of the user and
reports back to the user.

So, it can run a query -- and then manually vacuum, etc.

Resources are scarce/fixed so you can't just arbitrarily do
something and let the pieces fall where they may. Instead,
you have to interact with the DB in ways that are more
"economical" (in space/time/power/etc.).

That's why there's an agent in the middle to "sanitize" all
interactions with the DBMS. The interface to the DBMS is
never "raw".

Then there won't be any problems. :>

Understood. But, the same vulnerabilities exist in any PostgreSQL
instance. Less so, here, because the device isn't sitting on a
network, "exposed".

I don't see that what we are doing is in any way different from
what a PostgreSQL user could expect from a DB. The differences
are that our "users" are applications -- not "organic beings".
And, that there is typically only a single "organic" user
associated with each PG instance.