3 ms·

Please also have a look at this presentation the original article linked to: http://pgeu-plv8.herokuapp.com http://pgeu-plv8.herokuapp.com Ever since we began

by pilif 15y ago

Please also have a look at this presentation the original article linked to: http://pgeu-plv8.herokuapp.com http://pgeu-plv8.herokuapp.com Ever since we began storing document data as JSON instead of serialized PHP arrays (yes. PHP - I can't port this behemoth to a more convenient language), I was thinking that maybe Postgres would eventually get some way of peeking inside these blobs. Now that pl/v8 exists, we can. Combined with the right functional indexes, this can make our life much easier and help greatly to either reduce joins or data amounts transmitted between database and application server - both of which are concerning me at particular places - nothing real bad yet, but potentially. Thanks a lot for posting this article. You might have just made my day.

underwater 15y ago
PHP's serialization is pretty straightforward, you don't even have to worry about escape characters. You could write encoding/decoding functions in a couple of dozen lines.
- pilif 15y ago
  I probably could, but in this case there were other issues which lead me to move to JSON. Speed being one of them json_*code is about twice as fast as un/serialize. I know igbinary exists to solve that, but it would make debugging something using psql even worse. Also, there is no formal specification for the output of serialize() and there is some talk about changing the format sometime in the future. While they could have unserialize() understand both formats and serialize only write the new one, it would mean that I have to play catch-up and fix my code as the format changes. As I would probably never really have time to do it, this would crate a fixed dependency on an old version of PHP which a) would preclude me from using new language features and b) would get me into trouble as distros update and the last version which writes the current format falls out of support. Then there are also some encoding issues which are too complicated to explain here but basically force you to store the output of serialize() in a bytea column which again sucks for quick psql-based inspection and for PDO access which converts bytea fields into streams. See http://pilif.github.com/2011/09/serialize-mistake/ http://pilif.github.com/2011/09/serialize-mistake/ if you are really interested in how I f'ed up. No. JSON only had advantages for me and now I get one more huge additional advantage without having to write even a single line of C code. On a related note: do have a look at the sourcecode of unserialize() - it was generated by some parser generator and is completely unreadable on casual glance, forcing me to dive even deeper into PHPs build system were I to rebuild unserialize() as a Postgres extension, which I don't want to do anyways for all the other reasons. PS: no, looking at the output of serialize() and kinda sorta inferring the format and then building a quick parser is a recipe for disaster. Think escaping. Think arbitrary data types, think nesting. Are you sure you can hack together a parser which is guaranteed to be correct in all cases?

by pilif 15y ago

underwater 15y ago

PHP's serialization is pretty straightforward, you don't even have to worry about escape characters. You could write encoding/decoding functions in a couple of dozen lines.

pilif 15y ago
I probably could, but in this case there were other issues which lead me to move to JSON. Speed being one of them json_*code is about twice as fast as un/serialize. I know igbinary exists to solve that, but it would make debugging something using psql even worse. Also, there is no formal specification for the output of serialize() and there is some talk about changing the format sometime in the future. While they could have unserialize() understand both formats and serialize only write the new one, it would mean that I have to play catch-up and fix my code as the format changes. As I would probably never really have time to do it, this would crate a fixed dependency on an old version of PHP which a) would preclude me from using new language features and b) would get me into trouble as distros update and the last version which writes the current format falls out of support. Then there are also some encoding issues which are too complicated to explain here but basically force you to store the output of serialize() in a bytea column which again sucks for quick psql-based inspection and for PDO access which converts bytea fields into streams. See http://pilif.github.com/2011/09/serialize-mistake/ http://pilif.github.com/2011/09/serialize-mistake/ if you are really interested in how I f'ed up. No. JSON only had advantages for me and now I get one more huge additional advantage without having to write even a single line of C code. On a related note: do have a look at the sourcecode of unserialize() - it was generated by some parser generator and is completely unreadable on casual glance, forcing me to dive even deeper into PHPs build system were I to rebuild unserialize() as a Postgres extension, which I don't want to do anyways for all the other reasons. PS: no, looking at the output of serialize() and kinda sorta inferring the format and then building a quick parser is a recipe for disaster. Think escaping. Think arbitrary data types, think nesting. Are you sure you can hack together a parser which is guaranteed to be correct in all cases?