Re: Postgres

If someone's taking a survey, in addition to some of the stuff Chris
pointed out below (like data integrity checks), I'd also love to see
the native network data types (i.e. CIDR, INET, MACADDR), which
PostgreSQL offers, supported in GCSQL. At least MySQL 5.6.3+ finally
supports inet6_aton/_ntoa, which use varbinary, but a native datatype
for storage of addresses would be much better. Storing addresses in a
database seems like a pretty mainstream use case, and there's still no
elegant way to do it in MySQL.

I'm sure Google had good reasons for choosing MySQL over PostgreSQL to
port for their product, but, if there had been a vote, count me twice
for the latter.

Cheers and thanks for listening to feedback,
JJC



On Sun, Jul 15, 2012 at 1:40 AM, Chris Hubick <chris@hubick.com> wrote:
> On Tuesday, January 31, 2012 10:26:50 AM UTC-7, Joe Faith wrote:
>>
>> was there a particular bit of functionality that you'd like to see that
>> postgres currently supports, or was it just a general preference?
>
>
>
> Ok, Google Cloud SQL service, exciting! Must port my project from PostgreSQL
> to MySQL so I can use it! ...
>
>
> Wait, what do you mean it wants backticks instead of apostrophes for
> escaping just certain things??
>
> *OK, whatever, fix all the table name escaping*.
>
>
> CREATE TABLE mytable (
> mycolumn type NOT NULL REFERENCES othertable(othercolumn) ON DELETE
> CASCADE
> );
>
> Why aren't any of the foreign keys I defined listed?
>
> Wait, what do you mean I have to index all the columns first??
>
> Eww...
>
> CREATE TABLE mytable (
> mycolumn type NOT NULL,
> INDEX (mycolumn),
> FOREIGN KEY (mycolumn) REFERENCES othertable(othercolumn) ON DELETE
> CASCADE
> );
>
> So, now, every foreign key column needs 3 lines instead of 1? Ugly!
>
> It's a good thing I don't have gargantuan datasets, given all the indexes
> all my foreign keys are gonna require!
>
>
> Wait, what do you mean I can't have DEFERRABLE foreign keys??
>
> But the "access control" table for my stuff references (ON DELETE RESTRICT)
> the "user" table to specify who can use the stuff, so when you delete a
> user, that will eventually cascade it's way down the content tree to delete
> all their stuff and the access controls giving them permission to see it,
> but without the access control restriction being deferrable, isn't it gonna
> freak out before it even gets that far? Ehh, does that mean I have to write
> a bunch of ugly trigger code to manually delete all a user's stuff before
> the user record? Eww!
>
>
> Wait, what do you mean cascaded foreign key actions don't activate
> triggers??
>
> But doesn't that mean that, err, how the heck am I supposed to...is this
> even ACID compliant?!?
>
> Ok, whatever, I can figure out how to (somehow) delete all the stuff later,
> let's just concentrate on getting it in there properly first! ...
>
>
> Wait, what do you mean the CHECK clause is parsed but ignored by all storage
> engines??
>
> How the heck do I validate that one of either the binary_value (clob) or the
> text_value (varchar) columns were sent?? What about checks validating
> numeric ranges??
>
> AHH! OK, maybe I just need to relax regarding the data integrity for now.
> Gah, I can't believe I just said that when talking about my database! I
> guess it's the "MySQL way"?
>
>
> Ok, so, my basic tables are there, let's port one of the INSERT triggers
> that does the calculation of default column values when not specified!
>
>
> Wait, shouldn't my trigger SET a default value, why is it still freaking out
> that there is none specified?? Who knows what's going on there, I will
> ignore that for now...
>
>
> Wait, what do you mean I can only have *one* trigger for a given event on a
> table??
>
> So, the triggers that serve totally different logical purposes need to have
> all their code combined and mixed into *one*? AHH!
>
>
> Wait, what do you mean I can't create a single trigger for both INSERT and
> UPDATE??
>
> Please tell me I don't have to duplicate all that calculation code into two
> triggers?!?
>
> Oh, I can call a shared procedure? That's cool.
>
>
> Wait, what do you mean there is no %ROWTYPE??
>
> But how do I pass the 'NEW' record from a trigger into a shared procedure so
> that it can SET calculated values on the NEW row then??
>
> I can't!?! AHH! Gah, OK, this isn't a deal breaker, I just have to
> copy/paste *all* the code for *all* the triggers, and as long as I remember
> to duplicate any fixes, maybe it won't be that bad?
>
>
> Wait, but, without %ROWTYPE, how are the cursors gonna work??
>
> I have to DECLARE a variable for *every* column I need?!?! AHH! That's a
> mountain of declarations! How the hell am I supposed to keep all the data
> types straight??
>
>
> Wait, what do you mean I can't UPDATE WHERE CURRENT OF mycursor??
>
> I have to manually restrict the update? But even just this one primary key
> is a union of three columns, which means that, because there is no %ROWTYPE,
> every time I want to update I need to DECLARE yet three more variables, on
> top of the gazillion I already added for the FETCH?!?
>
>
> Wait, what do you mean if the SELECT inside the cursor LOOP returns null it
> causes the loop to abort??
>
> And what the heck is a HANDLER? My declared handler is in scope even inside
> CALL'd procedures too?? But I have tons of select statements spread through
> a hundred functions and procedures... and how does this work when I have
> nested cursor loops four deep? How can I possibly manage... AHH!
>
>
> Ok, so, my whole database is essentially a 6 level deep tree structure, with
> a table corresponding to each depth containing child nodes with pointers to
> their parent rows in the table "above". Each table has a column with a
> floating point number (0 < n < 1) specifying the child row's order with
> respect to it's peers under their parent node. Each table calls a trigger
> after new child rows are inserted, which updates all peer nodes in that
> table to redistribute their ordering numbers evenly under their parent.
>
> Wait, what do you mean I can't update a table from within it's trigger
> because it's already being used by the statement which invoked that
> trigger??
>
>
> I GIVE UP!! :(
>
>
> Does that answer the question? ;)
>
> --
> Chris Hubick
> mailto:chris@hubick.com
> http://chris.hubick.com/
>