Is there a way to bypass sql?

hello

2010/8/9 samantha <sjatkins@mac.com>:

I have been digging into NoSQL of late.  For navigational queries it would
be great if there was a way to bypass SQL and directly pull from an
identifier for a record or arbitrary byte stream.  Does postgresql directly
support such ability?   What is the closest that you could come?

no, there are nothing similar - you cannot to bypass SQL.

Regards

Pavel Stehule

On Mon, Aug 9, 2010 at 2:39 PM, samantha <sjatkins@mac.com> wrote:

I have been digging into NoSQL of late.  For navigational queries it would
be great if there was a way to bypass SQL and directly pull from an
identifier for a record or arbitrary byte stream.  Does postgresql directly
support such ability?   What is the closest that you could come?

You can get pretty close, depending on how you define 'bypass'. For
example, it is possible to send rich data structures back and forth
between the client and the server without constructing a SQL text
string. Those structures still have to be strongly typed in the
server unless you want to stuff everything into a bytea (which btw I
think is a terrible idea for most cases). Could you describe in more
detail what you'd like to do and what (if any) inefficiencies or
restrictions SQL is imposing that you would like to bypass?

merlin

On Mon, Aug 9, 2010 at 1:39 PM, samantha <sjatkins@mac.com> wrote:

I have been digging into NoSQL of late (...)

Be wary of DBAs Running with Scissors...

http://www.pgcon.org/2010/schedule/attachments/141_PostgreSQL-and-NoSQL.pdf

On Aug 9, 2010, at 11:57 AM, Merlin Moncure wrote:

On Mon, Aug 9, 2010 at 2:39 PM, samantha <sjatkins@mac.com> wrote:

I have been digging into NoSQL of late. For navigational queries it would
be great if there was a way to bypass SQL and directly pull from an
identifier for a record or arbitrary byte stream. Does postgresql directly
support such ability? What is the closest that you could come?

You can get pretty close, depending on how you define 'bypass'. For
example, it is possible to send rich data structures back and forth
between the client and the server without constructing a SQL text
string. Those structures still have to be strongly typed in the
server unless you want to stuff everything into a bytea (which btw I
think is a terrible idea for most cases). Could you describe in more
detail what you'd like to do and what (if any) inefficiencies or
restrictions SQL is imposing that you would like to bypass?

In many OO projects the majority of the work on persistent objects is navigational and inserts with relatively few updates. Queries are usually mainly for initial working set in many such systems and little else. When retrieving an object given a persistent oid it would be better if I didn't need to go through even a prepared statement and especially it would be better if I did not need to translate column values or do subqueries to either construct my OO language object or construct my OO cache entry. One thought is that I could in many cases store the cache entry format directly in a KV store and save a bit.

There is also the interesting case of dynamic OO languages where technically the object fields do not have a defined type to start with.

- samantha

On 08/10/10 10:31 PM, Samantha Atkins wrote:

In many OO projects the majority of the work on persistent objects is
navigational and inserts with relatively few updates. Queries are
usually mainly for initial working set in many such systems and little
else. When retrieving an object given a persistent oid it would be
better if I didn't need to go through even a prepared statement and
especially it would be better if I did not need to translate column
values or do subqueries to either construct my OO language object or
construct my OO cache entry. One thought is that I could in many cases
store the cache entry format directly in a KV store and save a bit.

or just dump your data in a flatfile, in ASN.1 or XML or something.
sheesh.

On Wed, Aug 11, 2010 at 1:31 AM, Samantha Atkins <sjatkins@mac.com> wrote:

On Aug 9, 2010, at 11:57 AM, Merlin Moncure wrote:

On Mon, Aug 9, 2010 at 2:39 PM, samantha <sjatkins@mac.com> wrote:

I have been digging into NoSQL of late.  For navigational queries it would
be great if there was a way to bypass SQL and directly pull from an
identifier for a record or arbitrary byte stream.  Does postgresql directly
support such ability?   What is the closest that you could come?

You can get pretty close, depending on how you define 'bypass'.  For
example, it is possible to send rich data structures back and forth
between the client and the server without constructing a SQL text
string.  Those structures still have to be strongly typed in the
server unless you want to stuff everything into a bytea (which btw I
think is a terrible idea for most cases).  Could you describe in more
detail what you'd like to do and what (if any) inefficiencies or
restrictions SQL is imposing that you would like to bypass?

In many OO projects the majority of the work on persistent objects is navigational and inserts with relatively few updates.  Queries are usually mainly for initial working set in many such systems and little else.  When retrieving an object given a persistent oid it would be better if I didn't need to go through even a prepared statement and especially it would be better if I did not need to translate column values or do subqueries to either construct my OO language object or construct my OO cache entry.   One thought is that I could in many cases store the cache entry format directly in a KV store and save a bit.

you can certainly do that...you have a few options:
*) EAV style table design
*) XML or JSON column in the database (xml is currently better supported)
*) hstore column type

PostgreSQL also supports arrays of composite types that you can use to
define nested complex structures and stuff them into a column. This
is a hybrid approach between the classic way and the EAV style; it has
a couple of annoying downsides (updating single values is a PITA) and
is not commonly used.

In my experience these type of designs are typically pursued when the
actual data modeling and constraint checking is done in code. Using
them will highly limit the ability of the database to support
operations that are not exposed through your OO layer whereas a rich,
strongly typed schema is more flexible, and typically easier to
maintain and administrate at the cost of having to marshal your data
coming in and out of hte database. Most people on this list will
discourage EAV for general use (specific cases might be ok though).

merlin

Samantha Atkins <sjatkins@mac.com> writes:

In many OO projects the majority of the work on persistent objects is
navigational and inserts with relatively few updates. Queries are
usually mainly for initial working set in many such systems and little
else. When retrieving an object given a persistent oid it would be
better if I didn't need to go through even a prepared statement and
especially it would be better if I did not need to translate column
values or do subqueries to either construct my OO language object or
construct my OO cache entry.

I think you are drastically overestimating the overhead of a prepared
query, and drastically underestimating the work involved in implementing
or maintaining a "bypass" solution. This really isn't going to be worth
your time.

If you think you don't need SQL at all anywhere, then go with something
like BDB.

regards, tom lane

On Wednesday 11 August 2010 07.31:24 Samantha Atkins wrote:

There is also the interesting case of dynamic OO languages where
technically the object fields do not have a defined type to start
with.

I'm not sure what you want to say here. If you apply this to databases, my
answer is: if you want to use persistent objects in such languages, don't
use a relational database, since it's just the wrong tool. Storing stuff
like XML snippets or Python pickled objects or whatever in tables in a
postgres database is just like using a 40t lorry to commute to work: it's
possible, but it's kind of silly.

cheers
-- vbi

--
Protect your privacy - encrypt your email: http://fortytwo.ch/gpg/intro