Is there a One True Postgres interface? Is Ruby-postgresmaintained?

[John Carter]

Having started this huge thread on PostgresSQL, I found...

....after much swearing at the complexities of setting up users and
passwords and permissions in PostgresSQL.... (I don't care, there will
only ever be one user, why am I battling this stuff?! Bugger this for
a bowl of cherries!)

..and now am yet another happy SQLite user.

Soo so so much simpler.


John Carter Phone : (64)(3) 358 6639
Tait Electronics Fax : (64)(3) 359 4632
PO Box 1645 Christchurch Email : (e-mail address removed)
New Zealand

 

[Mohit Sindhwani]

Having started this huge thread on PostgresSQL, I found...

....after much swearing at the complexities of setting up users and
passwords and permissions in PostgresSQL.... (I don't care, there will
only ever be one user, why am I battling this stuff?! Bugger this for
a bowl of cherries!)

..and now am yet another happy SQLite user.

Soo so so much simpler.

John,

I quite like SQLite actually.. though I'm tempted to go to Postgre
mostly for GIS functionality...

Cheers,
Mohit.
5/24/2008 | 12:28 AM.

 

[Todd Benson]

Having started this huge thread on PostgresSQL, I found...

....after much swearing at the complexities of setting up users and
passwords and permissions in PostgresSQL.... (I don't care, there will
only ever be one user, why am I battling this stuff?! Bugger this for
a bowl of cherries!)

..and now am yet another happy SQLite user.

Soo so so much simpler.

If the shoe fits. I would use SQLite more if it had proper relational
algebra under its hood.

Todd

 

[Will Parsons]

If the shoe fits. I would use SQLite more if it had proper relational
algebra under its hood.

Would you mind explaining what you mean my that?

 

[Jeff Davis]

Does anyone know which of the Postgres JDBC driver or libpq is most
widely used today and most reliable?

Libpq is widely used. I'm not sure what exactly you mean by reliable,
but libpq is solid code.

I don't have any experience with the JDBC driver.

Regards,
Jeff Davis

 

[Jeff Davis]

Having started this huge thread on PostgresSQL, I found...

....after much swearing at the complexities of setting up users and
passwords and permissions in PostgresSQL.... (I don't care, there will
only ever be one user, why am I battling this stuff?! Bugger this for
a bowl of cherries!)

..and now am yet another happy SQLite user.

Soo so so much simpler.

I'm glad it worked out. If you have some time, would you mind doing a
quick writeup of what you tried, what you expected, and what you got, so
that if there is unneeded complexity we can fix it for other users?
Generally PostgreSQL installs with fairly liberal permissions for local
access, so the single-user case is usually easy.

It would also be interesting to know what you think of the embedded
version of FirebirdSQL.

I'm not trying to direct you away from SQLite, but it would be helpful
to know at what point you were stuck.

Regards,
Jeff Davis

Regards,
Jeff Davis

 

[Jeff Davis]

Would you mind explaining what you mean my that?

One example is that SQLite uses a very different typing system. In an
RDBMS (for SQL and every other definition of "relational" that I'm aware
of), every attribute has an associated type, and type checking is done
at the time the expression (SQL statement) is compiled.

In SQLite, you can store values of different types in the same attribute
in different tuples. This leaves us with the following undesirable
options:

1. Define every operator for every type.
2. Raise type exceptions at runtime when an operator is not defined for
the types of the input data.

If we choose #1, we end up with a language more like PHP, where values
are cast implicitly until it finds some way to return a result. This
completely circumvents type constraints, and it's easy to end up getting
a garbage result, and passing that garbage along through a series of
other operators until the source of the problem is thoroughly obscured.

If we choose #2, we introduce runtime errors that depend on the actual
data we have stored in the database. We can test the application and get
no errors for anyone, and then when we put it into production someone
may put some data of some type that is not supported by the operators
we're using for our queries. That may cause errors in many queries
throughout the system.

Both of these options are really just mechanisms to allow errors to
propagate further before they are caught. I think it's much better to
catch errors early, personally.

I have nothing against SQLite, in fact I think it's pretty good in some
ways, but I don't consider it to be a relational database.

Regards,
Jeff Davis

 

[Todd Benson]

Would you mind explaining what you mean my that?

No solid constraints for data integrity. Like foreign key
constraints, for example. Somebody mentioned on this list in a
different thread, though, that this will change in the future. Here's
a contrived example without caps...

sqlite3 --version
=> 3.4.0
sqlite3

create table food (
ingredient varchar not null primary key
);

create table measurements (
measurement varchar not null primary key
);

create table preparation (
measurement not null references measurements (measurement),
ingredient not null references food (ingredient),
primary key (measurement, ingredient)
);

insert into food values ("garlic");
insert into measurements ("clove");
insert into preparation (measurement, ingredient) values ("garlic", "clove");
insert into preparation (measurement, ingredient) values ("clove", "garlic");
select * from preparation;

=> garlic |clove (yes, the result has that space after garlic)
=> clove|garlic

Sort of a bandaid on logic, especially considering that the above code
complies with the SQL92 standard. Hey, but it's small, fast, and
works for some things.

Sorry for the bad vibe, but people need to be aware that they are not
working with an RDBMS.

Todd

 

[Todd Benson]

No solid constraints for data integrity. Like foreign key
constraints, for example. Somebody mentioned on this list in a
different thread, though, that this will change in the future. Here's
a contrived example without caps...

sqlite3 --version
=> 3.4.0
sqlite3

create table food (
ingredient varchar not null primary key
);

create table measurements (
measurement varchar not null primary key
);

create table preparation (
measurement not null references measurements (measurement),
ingredient not null references food (ingredient),
primary key (measurement, ingredient)
);

insert into food values ("garlic");
insert into measurements ("clove");

Oops, there was supposed to be a values after measurements there. The
point still stands, though.

 

[Todd Benson]

create table preparation (
measurement not null references measurements (measurement),
ingredient not null references food (ingredient),
primary key (measurement, ingredient)
);

Even worse, I missed the required type with measurement and ingredient
here with no error! which means I wouldn't be able to migrate this to
another database.

Todd

 

[Bill Kelly]

No solid constraints for data integrity. Like foreign key
constraints, for example. Somebody mentioned on this list in a
different thread, though, that this will change in the future. Here's
a contrived example without caps...

sqlite3 --version
=> 3.4.0
sqlite3

create table food (
ingredient varchar not null primary key
);

create table measurements (
measurement varchar not null primary key
);

create table preparation (
measurement not null references measurements (measurement),
ingredient not null references food (ingredient),
primary key (measurement, ingredient)
);

insert into food values ("garlic");
insert into measurements ("clove");
insert into preparation (measurement, ingredient) values ("garlic", "clove");
insert into preparation (measurement, ingredient) values ("clove", "garlic");
select * from preparation;

=> garlic |clove (yes, the result has that space after garlic)
=> clove|garlic

What actually happened there? Is it that SQLite added the space
on purpose because it noticed "garlic" didn't satisfy the
"references measurements (measurement)" clause?

Or is the space just some artifact of its output formatting that
doesn't relate to the data?


Puzzled,

Bill

 

[Todd Benson]

What actually happened there? Is it that SQLite added the space
on purpose because it noticed "garlic" didn't satisfy the
"references measurements (measurement)" clause?

I don't think so, because, even though I could repeat it, I could add
another row, and then it would output correctly.

Or is the space just some artifact of its output formatting that
doesn't relate to the data?

Maybe that. I'll play around a little. I seem to remember SQLite
documentation claiming the software would comply with SQL standards up
to a point, but to play well with others decided to silently ignore
certain things that it wasn't capable of.

Todd

 

[John Carter]

If the shoe fits. I would use SQLite more if it had proper relational
algebra under its hood.

Yip, I was speaking, half tongue-in-cheek hence the smiley ) in the
subject line. There are many very valid reasons for using
postgressql...

But unless one or more of those reasons are biting you hard, the
simplicity of sqllite is a major win.

Starting with sqllite and moving to postgres sql as and when and if you
need to is not a bad strategy either.


John Carter Phone : (64)(3) 358 6639
Tait Electronics Fax : (64)(3) 359 4632
PO Box 1645 Christchurch Email : (e-mail address removed)
New Zealand

 

[Joel VanderWerf]

But unless one or more of those reasons are biting you hard, the
simplicity of sqllite is a major win.

On the subject of sqlite (and tangentially ruby), and with all you smart
folks on the list, a question:

Does anyone know how to keep sqlite from going to disk so much, or is
that a necessary part of the locking mechanism? Running with strace, the
write() is immediately followed by fsync(). It keeps my disk very busy
for what should be cheap insert operations to the end of a table with no
indexes. Cpu usage is low too (and I'm using SQLite3:atabase#prepare
from the sqlite3 gem, to keep query overhead low).

There is :memory: for an in-memory db, but then if you want multiple
processes to access it, I guess you need to write a dedicated server
process(?).

Not only is this slow, but also it could be a killer on our embedded
flash-based devices that can't be rewritten forever.

Thanks for any advice...

 

[Phillip Gawlowski]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Joel VanderWerf wrote:
| John Carter wrote:
|> But unless one or more of those reasons are biting you hard, the
|> simplicity of sqllite is a major win.
|
| On the subject of sqlite (and tangentially ruby), and with all you smart
| folks on the list, a question:
|
| Does anyone know how to keep sqlite from going to disk so much, or is
| that a necessary part of the locking mechanism? Running with strace, the
| write() is immediately followed by fsync(). It keeps my disk very busy
| for what should be cheap insert operations to the end of a table with no
| indexes. Cpu usage is low too (and I'm using SQLite3:atabase#prepare
| from the sqlite3 gem, to keep query overhead low).

Two suggestions:
1) Use indexes, as that will improve your performance, and it might just
help with making sure SQLite actually appends directly, instead of
seeking a position first. And the performance improvement even with
small DBs is remarkable: milliseconds instead of seconds..).
2) Are you using transactions? If you don't use transactions, every
insert or update is written to disk immediately, requiring a sync to
disk (we don't want inconsistent data). However, with a transaction,
every operation gets committed to the DB in one go (which is faster,
allows rollbacks in case an operation fails, and should require less
fsync() calls).

- --
Phillip Gawlowski
Twitter: twitter.com/cynicalryan
Blog: http://justarubyist.blogspot.com

Terminate input by end-of-file or marker, not by count.
~ - The Elements of Programming Style (Kernighan & Plaugher)
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.8 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkg6HHEACgkQbtAgaoJTgL8ZVACeNM4+TOOBRISib7rlqRoHefNI
OFIAnRFavTkTEaIGZKw3It0V95i37yV+
=h05V
-----END PGP SIGNATURE-----

 

[Petite Abeille]

Does anyone know how to keep sqlite from going to disk so much, or
is that a necessary part of the locking mechanism? Running with
strace, the write() is immediately followed by fsync(). It keeps my
disk very busy for what should be cheap insert operations to the end
of a table with no indexes

Take a look at PRAGMA synchronous = FULL | NORMAL | OFF

http://www.sqlite.org/pragma.html

Make sure to read on "Atomic Commit In SQLite" before changing
anything though:

http://www.sqlite.org/atomiccommit.html

 

[Joel VanderWerf]

Take a look at PRAGMA synchronous = FULL | NORMAL | OFF

Thanks to you and Philip for the good suggestions... will digest them
for a while...