array parser?

[booo]

Are there any plans to parse arrays? We get arrays from our database and would like to work with arrays in nodejs too.

[brianc]

Absolutely. If you could gist an example or better yet submit a failing test & I'll get to work on it.

[booo]

SELECT ARRAY['key', 'value', 'key key', 'value value'];

http://pastebin.com/et9AhFBX

[brianc]

thanks. I've implemented a test - wondering what you would expect to be logged when you logged that row in your example? Something like this:

var query = client.query("SELECT ARRAY['key', 'value', 'key key']");
query.on('row', function(row) {
  assert.equal(row.array[0], 'key');
  assert.equal(row.array[1], 'value');
  assert.equal(row.array[2], 'key key');
})

??

[booo]

Yes, I would expect exactly this.

[kaiko]

I've used this function (copied idea from perl DBI::Pg library):

function parseArray (arrStr) {
  if (!arrStr) return null;
  if (arrStr[0] !== '{' || arrStr[arrStr.length-1] !== '}')
    throw "Not postgresql array! (" + arrStr + ")";

  var x = arrStr.substring(1, arrStr.length - 1);
  x = x.match(/(NULL|[^,]+|"((?:.|\n|\r)*?)(?!\\)"|\{((?:.|\n|\r)*?(?!\\)\})(,|$))/mg);
  if (x === null) throw "Not postgre array";
  return x.map(function (el) {
    if (el === 'NULL') return null;
    if (el[0] === '{') return arguments.callee(el);
    if (el[0] === '"')
       return el.substring(1, el.length - 1).replace('\\"', '"');
    return el;
  });
}

[brianc]

I've pushed an initial pass at this. I've exposed a place on the Query object where you can extend and write custom type parsers (as postgres will allow you to define custom types)

Check out lib/query.js for an example, but basically you can do the following:

Query.registerParser(typeOid, parseFunction);

Where typeOid is the OID of the type from pg_type table and parseFunction is the function which takes a single parameter which is the text value of the column.

Kaiko - I've used your convert function for starters, but it does not convert the following properly:

SELECT ARRAY["one", "two", "t , h { r } e e"]

I'm holding off on doing another npm version until I have an opportunity to regression test changes against 9.x versions of PostgreSQL and make the converting more robust.

[kaiko]

This works better (still only for 1-dimension arrays):

var parseStringArray = function (val) {

if (!val || val[0] !== '{' || val[val.length-1] !== '}')
    throw Error("Not postgresql array! (" + arrStr + ")");

// S will make split and set's new inel state
// C doesn't make split but changes inel state
var S  = function (set_inel, ch) { return function () { inel = set_inel; return (typeof ch !== 'undefined') ? ch : String.fromCharCode(27); } }
var C  = function (set_inel, ch) { return function () { inel = set_inel; return (typeof ch !== 'undefined') ? ch : ''; } }
var no = function (match) { return match; } // do nothing
var X  = function () { throw new Error('Not PostgreSQL array'); }; // can't happen
var c  = ','; // just shorter writing

// matrix what found vs current state = what to do next
var actions = { //  0     1     2   // inel
     '","': [ X     ,     X,   S(2)   ] // certainly separator
    , ',"': [ S(2)  ,  S(2),   S(0,c) ] // can be at end of last item (2) or start new item (1->2)
    ,'",' : [ C(2,c),     X,   S(1)   ] // can be start new item (2) or end item with starting new item (2->1)
    ,'"'  : [ C(2)  ,     X,   C(0)   ] // can be start first item (2) or finish last item (2)
    ,','  : [ S(1)  ,  S(1),   no     ] // can be item separator (1->1) or just be in item (2)
};


var prevpos = 0, inel = 0; // "in element": not quoted element: 1, quoted element: 2
var x = val.substring(1, val.length - 1)
    // let' remove real string quotes from expression and replace these with something what there can't be in real expression
    .replace(/\\+"/g, function (match) { return match.length % 2 ? match : (match.substring(0, match.length - 2) + "\u0003") })

    // now the main thing: find separators and parse then depending current state
    .replace(/","|,"|",|"|,/g, function (match, pos, str) {
        // var l = '  ' + pos + ' ' + match +  '\tPREV '  + prevpos + '\tPOS ' + pos + '\tinel ' + inel;

        var pinel = inel;
        var R = actions[match][inel].apply(this, arguments);

        // special case NULL (without quotes), we add special char and separator
        if (pinel < 2 && str.substring(prevpos, pos).toUpperCase() === 'NULL')
            R = String.fromCharCode(1) + String.fromCharCode(27);

        // console.log(l + '=>' + inel + '\t' + str.substring(prevpos, pos));
        prevpos = pos + match.length;
        return R;
    })
    .replace(/.\u0008/g, '')
    .replace(/\u0003/g, '"')
    .split(  String.fromCharCode(27))
    .map(function (e) { return e[e.length-1] === String.fromCharCode(1) ? null : e; });
return x;
};

[brianc]

cool - want me to add it to the repo & test it out? You're also welcome to fork & do a pull request as long as the tests still pass.

[booo]

Alexander is working on a binary parser. I think it's the best way to do it. Check out https://github.com/AlexanderS/node-postgres Perhaps this could be integrated somehow? Perhaps we have to add support for other data types and we should write some tests :)

[brianc]

yeah he sent a pull request, but it has no tests written. Once test are written for the binary stuff, I'd be more than happy to pull.

[dvv]

Hi! What's the status of the subj? TIA

[brianc]

The status of the array parser?

The array parser implementation can be "swapped out" by the above mentioned method. I've not received any pull requests in regards to a better implementation. If you have one (with tests) I'd be happy to merge it in.

Or of Alexander's changes?

Alexander's changes are still lacking any test coverage unfortunately. :(

[dvv]

I see. I just yesterday discovered this project and am way immature to offer. So far just pulling the info and exploring the minefield ;)

[booo]

We will work on tests in the next 4 weeks...

[dvv]

TIA. BTW, how at all arrays are useful comparing to normalized out data? I'm a noob to postgres -- what pros/cons do you see?

[merlinm]

Hi, I am one of the co-authors of libpqtypes (http://libpqtypes.esilo.com/) and have been really been playing with incorporating similar concepts into a node.js postgresql driver. Not so much the printf/scanf stuff, but:

*) binary protocol message handling
postgres has binary protocol modes for data. this is much faster for datetime types, geo types, arrays, and composites. I would like to have postgres complex types to be expressed directly as json variables without text processing. The binary formats are pretty well documented in libpqtypes which can be cribbed from

*) arrays and composites in both directions
we could send say, an array of ints to a postgres function directly from a json int array without building the postgres text, optionally validating it as it goes out the door

*) composite type discovery
to properly read out composite types, you have to query the server's type based on name and get various attribute data (especially the oid) back.

*) full type implementation
I'd like to see much broader for types than currently exists in the driver, for example the geometric types.

interested? if done properly it should really nail the array issue. Iooked at the various node.js pg drivers and this one seems the best suited to do this type of thing. maybe we can put our heads together and get something done. not sure if its better to tackle the native driver, c++, or both :(.

[merlinm]

@dvv:

TIA. BTW, how at all arrays are useful comparing to normalized out data? I'm a noob to postgres -- what pros/cons do you see?

arrays (and composite types) in postgres are useful in the following scenarios:
*) de-normalization -- sometimes it really is just more efficient to pack ints together in an array. you swap 20 byte tuple header for 4 byte length. otoh, you can't write single array element, you have to read in/write out the whole array as a block

*) function arguments: postgresql functions can't take a set of records as an input argument. you can however sent in a block of records. consider the following code block:

create table foo(a int, b text);
create function add_foos(foo[]) returns void as $$ insert into foo select (unnest($1)).*; $$ language sql;
select add_foos('{"(1,abc)","(2,def)"}'::foo[]);

the argument to add_foos can be parameterized and sent to the server as a single argument from the client. this can be used to do 'nosql' type approaches to send very complex data (composites in postgres can nest) back and forth from the client.

[booo]

We are still working on binary support. See our fork https://github.com/AlexanderS/node-postgres for more information. Feel free to contribute!

[brianc]

I'm interested in adding binary support for both the plain javascript and native bindings. I want to include the work on the AlexandereS branch; however, no test coverage of the new functionality is a no-go.

@merlinm I think the libpqtypes library looks great. Looking at libpqtypes I'm not sure just including it and calling it's functions would work properly as node-postgres native bindings yield to the node (libev) event loop and do no select/poll of their own when executing async commands from libpq. The node-postgres client also does not and will never support synchronous query/command execution (sync command execution would block the event loop in node..big no-no). I would be happy to work with you on including the advanced type stuff into node-postgres...the work you've done looks really valuable

As long as a pull request has test coverage I'm happy to help and will include anything that makes node-postgres better for the community!

[merlinm]

OK, took a look at the fork and have some thoughts (it's a great start btw):
*) Doing binary parsing in .js could turn out to be a major bottleneck (need to test to confirm). In particular, I am suspicious doing bitwise parsing based around pow() is going to yield good results. Unfortunately some quick googling didn't yield much in terms of options for converting binary strings to .js native types...the right way forward might be to bite the bullet and write some C/C++. This means doing all the byteswapping, etc in C, where it is trivially done. A really fancy version that handles arrays would be cool, but just doing (especially) int, float, datetime etc will get you pretty far.

*) It's probably good to separate the container type parsers (arrays/composites) from the POD parsers (int, float, etc). When you are pulling out container format it turns out you can always break it down to simpler types.

*) Aside from the above, I saw a fair amount of optimization potential. For example, when iterating result rows, you don't have to look up conversion functions by oid over and over...just do it once.

*) Is it worth doing anything besides the fully native protocol? anybody got some benchmark results handy showing libpq vs native?

[brianc]

1. I would kind of like to keep the pure javascript version of the driver independent of any C bindings what so ever. That way in the case of someone being on a development box or not having access to a compiler (more common in windows environments) they aren't out of luck. If performance is a concern the libpq bindings should be used at which point we can use as much C as we want. I do agree the parsing of types should be done in C for the native driver, I only reused the javascript type conversion as a temporary measure.

2. sounds good - I'm not very experienced with composite types & defer to your judgement

3. agree with you. Are you talking about this: https://github.com/brianc/node-postgres/blob/master/lib/native.js#L150 ?

4. The pure javascript was implemented first and allows those without compiler or libpq installed to still use postgres from node. I'm not opposed to adding more advanced features to the native bindings which don't exist in the pure javascript version. I have some benchmarks I run from time to time. The libpq bindings pretty much across the board are 2x as fast. You can see some benchmarks here: https://github.com/brianc/node-postgres/tree/master/benchmark

[merlinm]

@brianc I am suspicious the libpqtypes library will be of much use directly. First of all, it heavily wraps libpq, so it can't support the native driver. It's a also bit of overkill for what is needed here. Maybe some of the type conversion code can be cribbed into a support library that is shared by the native and libpq versions. On top of that, there is a non zero chance that the binary protocol mode is a total non starter for the v8 engine -- just too hard to say right now. All I'm after is to duplicate some of the functionality here -- to fluff arrays and composites into and out of javascript variables primarily.

[brianc]

Cool man. Was thinking similar things. I think doing the type coercion in the C for the native bindings would be a good start, along with support for more complex types. I am kinda in the dark about how complex types work and their best uses so some integration tests for complex types would be great too. I'm not really sure how fast/slow javascript works with binary manipulation, but the newest versions of node have some more work put into buffers around this issue:

https://github.com/joyent/node/blob/master/lib/buffer.js#L506

It's possible this will be moved into their C/C++ layer at some point making binary manipulation perhaps very fast after all.

[merlinm]

Composite types are just a box you can put other types in. create type foo as (a int, b text); Also in postgres all tables are themselves composite types, and composite types can nest, and can be made into arrays. libpqtypes made two innovations to deal with them in C:

1. you 'discover' composite types you are interested in calling PQregisterTypes (http://libpqtypes.esilo.com/man3/PQregisterTypes.html) which essentially takes a client provided list of type names and grabs all the oids for the type from the server. This is typically done right after connecting and i'm assuming we have to do similar -- unlike arrays, with composites you need to know the structure going in if you want to do any validation (composites changing from under you in the database is a big concern). This may seem like a lot of work but it's worth it.

2. libpqtypes when getting data can pop a result out of the result. so if your field happens to be, say an int[], you do some magic and get a result with one column and N rows. If your field is a composite, you get a result with one row and N fields. If your field is an array of composites, you get a result with N fields (one for each composite field) and N rows (one for each array element). Of course, this can nest.

I'm imagining that node-postgres will do all this under the hood -- as much as possible, the library will take care of it for you. Since it looks like supporting both the native and libpq protocol modes is baked in (2x performance can't be discarded trivially), hopefully we can keep most of the main logic shared.

[troyk]

Just want to chime in how awesome this feature would be for both performance and ease of use when dealing with one to many relationships.

For example, with the following schema:

create table contacts (
  id serial, 
  name text
);
create table contacts_phones (
  id serial, 
  contact_id int references contacts(id), 
  number text
);

Because tables in postgres are composite types, we can:

select contacts.*,
       (select array(select contacts_phones from contacts_phones where contacts_phones.contact_id = contacts.id))
       from contacts

Which is much better than having to query contacts, iterate, and then query contacts_phones (or do a join, which still requires iteration to reduce contacts to a single record and phones to an array).

But what I like most about being able to use a single query is now this is very friendly for emitter style queries and reduces async clutter big time.

plv8 supports this now, so it might be a good reference to see how they are doing it.

[merlinm]

if you're into that type of querying (which I 100% agree with), you need to check out the new json support for postgres. The existence of that is one of the reasons I haven't moved forward with a client side solution. the new json stuff in in-core for 9.2 but can be loaded as an extension for 9.1:

http://people.planetpostgresql.org/andrew/index.php?/archives/255-JSON-for-PG-9.2-...-and-now-for-9.1!.html

CREATE EXTENSION json;

create table contacts(contact_id serial, name text);
create table contacts_phones(contact_id int, phone text);
insert into contacts (name) values ('merlin');
insert into contacts (name) values ('troy');
insert into contacts_phones values (1, '123-456-7890');
insert into contacts_phones values (2, '234-567-8901');

select row_to_json(row(contacts,
       (select array(select contacts_phones from contacts_phones where contacts_phones.contact_id = contacts.contact_id))))
       from contacts;

postgres=# select row_to_json(row(contacts,
       (select array(select contacts_phones from contacts_phones where contacts_phones.contact_id = contacts.contact_id))))
       from contacts;
                                      row_to_json                                       
----------------------------------------------------------------------------------------
 {"f1":{"contact_id":1,"name":"merlin"},"f2":[{"contact_id":1,"phone":"123-456-7890"}]}
 {"f1":{"contact_id":2,"name":"troy"},"f2":[{"contact_id":2,"phone":"234-567-8901"}]}


postgres=# select array_to_json(array(select row(contacts,
       (select array(select contacts_phones from contacts_phones where contacts_phones.contact_id = contacts.contact_id)))
       from contacts));
                                                                                 array_to_json                                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"f1":{"contact_id":1,"name":"merlin"},"f2":[{"contact_id":1,"phone":"123-456-7890"}]},{"f1":{"contact_id":2,"name":"troy"},"f2":[{"contact_id":2,"phone":"234-567-8901"}]}]
(1 row)

[troyk]

@merlinm Agreed the row_to_json stuff is very cool, I actually got a tip from andrew and backported to 9.1 with support for getting the proper column names (instead of 'f1', 'f2', etc...).

The only issue I ran into with this approach is you loose some typing, such as dates and a faint whisper in the back of my mind that said "if this was client side, you wouldn't need to call JSON.parse" ;)

[merlinm]

yeah -- good point, and thanks for the tip on the f1/f2 issue. regarding json.parse, so far my usage for node is mostly on data services that i'm pulling from the database and immediately piping to the client so that node isn't handling the json in any way. in other words i'm keeping the node layer incredibly thin as it's just bouncing json up (although data transmission to the database is more complex obviously).

i like node, but I'm not comfortable with it enough to put a big logic investment there. that's partially due to lack of experience on my part and partially due to risk of having the platform being forcibly replaced with enterprise java for the application i'm writing, so I have to stay light. anyways, postgres + node + jquery is turning out to be an incredibly productive stack, and I've had zero issues with the node-postgres module.

[brianc]

All the issues covered here are addressed in either closed pull requests or more current & specific issues.