Обсуждение: Enforcing referential integrity against a HSTORE column

On 01/01/2016 07:47 PM, Dane Foster wrote:
> Hello,
>
> I'm moving a MySQL database to PostgreSQL and redesigning parts of it to
> take advantage of PostgreSQL's richer type system and other advance
> features. Currently I am attempting to replace a table of name/value
> pair data w/ a hstore column. But now that the data will no longer be
> flattened out in a table I need to manually handle referential integrity

And the benefit is?

>
> So given:
>
> CREATE TABLE xtra_fields(
>    xfk SERIAL PRIMARY KEY,
>    xtk INTEGER NOT NULL REFERENCES xtra_types,
>    ...
> );
>
> CREATE OR REPLACE FUNCTION foo_xtra_fk(HSTORE) RETURNS BOOLEAN AS $$
> WITH keyz AS (SELECT skeys($1)::INT AS xfk)
> SELECT
>    (SELECT COUNT(*) FROM keyz JOIN xtra_fields USING (xfk))
>    =
>    (SELECT COUNT(*) FROM keyz)
> $$LANGUAGE SQL STABLE STRICT LEAKPROOF;
>
> CREATE TABLE foo(
>    id INTEGER NOT NULL CHECK (id > 0),
>    ...
> -- Extra fields where the keys are the xtra_fields.xfk values and the
> values are the
> -- data values for the specific xfk.
>    xtra hstore CHECK (foo_xtra_fk(xtra))
> );
>
> is ​there a more efficient way of maintaining logical referential integrity?

Yes, use a table:) I guess it comes down to the first question above and
what you are trying to achieve by moving to hstore. I use hstore and it
is very handy for storing ad-hoc data, however when I want all the the
RI whistle and bells I use table structures. The work has been done for
me by folks who know a lot more about this then I and it is one less
thing for me to code/worry about.

>
>
> Thank you for your consideration,​
>
> Dane
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On Sat, 2 Jan 2016 07:30:38 -0800
Adrian Klaver <adrian.klaver@aklaver.com> wrote:

> > So given:
> >
> > CREATE TABLE xtra_fields(
> >    xfk SERIAL PRIMARY KEY,
> >    xtk INTEGER NOT NULL REFERENCES xtra_types,
> >    ...
> > );
> >
> > CREATE OR REPLACE FUNCTION foo_xtra_fk(HSTORE) RETURNS BOOLEAN AS $$
> > WITH keyz AS (SELECT skeys($1)::INT AS xfk)
> > SELECT
> >    (SELECT COUNT(*) FROM keyz JOIN xtra_fields USING (xfk))
> >    =
> >    (SELECT COUNT(*) FROM keyz)
> > $$LANGUAGE SQL STABLE STRICT LEAKPROOF;
> >
> > CREATE TABLE foo(
> >    id INTEGER NOT NULL CHECK (id > 0),
> >    ...
> > -- Extra fields where the keys are the xtra_fields.xfk values and the
> > values are the
> > -- data values for the specific xfk.
> >    xtra hstore CHECK (foo_xtra_fk(xtra))
> > );
> >
> > is ?there a more efficient way of maintaining logical referential integrity?

I second Adrian's comment on making sure that the benefit of HSTORE is
outweighing the drawback of having to write your own checks ... however,
if you decide that HSTORE is the right way to go, you may want to try
something more along the lines of this for your check:

SELECT true WHERE NOT EXISTS(SELECT 1 FROM keyz WHERE xfk NOT IN (akeys($1)));

Not tested, so it's possible that I have some typo or something; but overall
I've found that the NOT EXISTS construct can be very efficient in cases
like these.

--
Bill Moran

On 01/02/2016 08:13 AM, Dane Foster wrote:

Ccing list.
> On Sat, Jan 2, 2016 at 10:30 AM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 01/01/2016 07:47 PM, Dane Foster wrote:
>
>         Hello,
>
>         I'm moving a MySQL database to PostgreSQL and redesigning parts
>         of it to
>         take advantage of PostgreSQL's richer type system and other advance
>         features. Currently I am attempting to replace a table of name/value
>         pair data w/ a hstore column. But now that the data will no
>         longer be
>         flattened out in a table I need to manually handle referential
>         integrity
>
>
>     And the benefit is?
>     ​
>
> ​
> The benefit is supposed to be client side simplicity. The data in these
> particular tables are ultimately consumed by JavaScript as JSON on the
> front end to populate/maintain a dynamic HTML for​m. So I was attempting
> to build a model that more closely reflects how the data is used because
> the people using the data aren't SQL folks and the code that converts
> the data from table/rows to JSON is not straight forward for my audience.

In that case you may want to look at the JSON types, json and/or
jsonb(depending on Postgres version):


http://www.postgresql.org/docs/9.4/interactive/datatype-json.html

>
>
>         So given:
>
>         CREATE TABLE xtra_fields(
>             xfk SERIAL PRIMARY KEY,
>             xtk INTEGER NOT NULL REFERENCES xtra_types,
>             ...
>         );
>
>         CREATE OR REPLACE FUNCTION foo_xtra_fk(HSTORE) RETURNS BOOLEAN AS $$
>         WITH keyz AS (SELECT skeys($1)::INT AS xfk)
>         SELECT
>             (SELECT COUNT(*) FROM keyz JOIN xtra_fields USING (xfk))
>             =
>             (SELECT COUNT(*) FROM keyz)
>         $$LANGUAGE SQL STABLE STRICT LEAKPROOF;
>
>         CREATE TABLE foo(
>             id INTEGER NOT NULL CHECK (id > 0),
>             ...
>         -- Extra fields where the keys are the xtra_fields.xfk values
>         and the
>         values are the
>         -- data values for the specific xfk.
>             xtra hstore CHECK (foo_xtra_fk(xtra))
>         );
>
>         is ​there a more efficient way of maintaining logical
>         referential integrity?
>
>
>     Yes, use a table:) I guess it comes down to the first question above
>     and what you are trying to achieve by moving to hstore. I use hstore
>     and it is very handy for storing ad-hoc data, however when I want
>     all the the RI whistle and bells I use table structures. The work
>     has been done for me by folks who know a lot more about this then I
>     and it is one less thing for me to code/worry about.
>
>
> ​I agree.​ I should keep the table.
>
> ​While thinking deeply about your question it dawned on me that I can
> have it both ways. So my new solution is to create a view that looks
> like the foo table. This way I get built-in referential integrity via
> foreign keys and a programmer friendlier view.
>
> Thanks for shaking up my perspective,
>
> Dane​
>


--
Adrian Klaver
adrian.klaver@aklaver.com