Re^4: Need DBM file that holds data up to 50,000 bytes

[erix]

Postgres will return too much by default on big tables

What do you mean with "return too much"? That sounds serious.

And I think you give not enough credit for the freedom of the software. Oracle has a great database but it's ridiculously expensive to run even a single instance, and Mysql and BerkeleyDB are pawns in Oracle hands. In my opinion that is a *very* good reason not to use them (I kicked them out when Oracle took them over).

With regard to cdb: its main annoyance is that it is for databases that do not change (this is by design: it's after all named cdb: "constant database"). Perhaps it is fits the OPs requirements but it is often a pain (kicked that out, too ;-))

SQLite is nice but pretty simple (and was and is inspired by PostgreSQL, its author told us at PGCon - see here, the talk by Richard Hipp).

Yeah, I agree: Too many factors to think about :)

And IMHO PostgreSQL does not suck. :)

[Tux]

I subscribe to most of your comments.

I value freedom of software more than you can imagine. I supply patches for fixes or new features for many OpenSource projects I use in daily life. Today I enhanced Claws-Mail. Awaiting further discussion two of their main committers liked the patch and even want to change the default to the new behavior (still being able to use the old behavior).

Maybe that is also what I like so much in our beloved Perl community. CPAN motivates users to give valuable feedback. Either by patches, bug reports or comments.

I find myself using MySQL with more and more reluction over the years. The problem is that none of my annoyences are gone with new releases.

I have to use Oracle and Unify because of our customers, but I keep wondering how Oracle can get away with bad NULL/Empty-string handling in varchar. By now "backward compatibility" is no valid excuse anymore.

I have close to no grunts with Postgres. What I remember is that select * from foo; where foo has millions of records, wil build the complete list as result set before it returns the first record. That may take long. Now that my postgres servers are fast and have lots of RAM, it is no real concern to me, but I hate having to use LIMIT in a loop to get smaller sets. Things might have changed since then and I am quite happy with postgres 9.3.5

Unify is great for BLOB handling. it is completely transparent and has no limits, unlike that sucking 4k limit in Oracle.

[BrowserUk]

What I remember is that select * from foo; where foo has millions of records, wil build the complete list as result set before it returns the first record. That may take long.

Isn't that true of every RDBMS?

And isn't it unavoidable (without the use of LIMIT) when using DBI? (Cos if there is a way around it, I'd love to see it.)

[erix]

Well, for instance Oracle has HINTs. HINTS are a possibility to talk to the SQL server "between the lines" as it were. Inside comments (that are formatted a certain way) you can (for instance) HINT a preference for faster response times (as opposed to greater general throughput).

A quick google yields for instance this page (look for hint FIRST_ROWS). That page also shows the syntax of HINTing use.

Or see: Oracle 12 docs

(The PostgreSQL devs have always rejected the whole HINTing business as a shortcut that would prevent better, more-thorough solutions in the planner/executor later. (Nevertheless, I often think they would be handy...))

[Tux]

Not really. Some databases support record streaming. When an index can be used or a full table scan is done, records can be given on demand:

my $sth = $dbh->prepare ("select * from big_table order by key");
$sth->execute; # Will find the execution plan
while (my $row = $sth->fetch) { # first row returned immediate
    # do something
    }

Some databases however will build up a complete result set (of all records) before returning the first.