Re: [GENERAL] 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.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Ubuntu 15.04 Installing Oracle_fdw
Hi, I compiled oracle_fdw.so on Ubuntu 15.04 Server 64bits and I'm getting the "No such file or directory" error when I try to create the oracle_fdw extension. After doing make and make install on Oracle fdw source oracle_fdw.so is built and installed correctly (oracle_fdw.so is copied to my $libdir directory those related files are copied to the extension directory: ls -lah /usr/share/postgresql/9.4/extension -rw-r--r-- 1 root root 231 ene 2 12:19 oracle_fdw--1.0--1.1.sql -rw-r--r-- 1 root root 1003 ene 2 12:19 oracle_fdw--1.1.sql -rw-r--r-- 1 root root 133 ene 2 12:19 oracle_fdw.control But, when I try to create the extension I get this: psql (9.4.4, server 9.4.2) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. postgres=# create extension oracle_fdw; ERROR: could not open extension control file "/usr/share/postgresql/9.4/extension/oracle_fdw.control": No such file or directory Any hint? -- Leonardo M. Ramé http://leonardorame.blogspot.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Enforcing referential integrity against a HSTORE column
On Sat, 2 Jan 2016 07:30:38 -0800 Adrian Klaver 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Enforcing referential integrity against a HSTORE column
On Sat, Jan 2, 2016 at 10:59 AM, Bill Moran wrote: > On Sat, 2 Jan 2016 07:30:38 -0800 > Adrian Klaver 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))); > I also agree w/ Adrian's comments and to that end I will be keeping the original tables and building a view that does what I need on top of them. But I really like your SQL solution. It is quite elegant and I suspect it would run faster than mine for a large enough data set. So I'll be filing it away in memory for use at some future point in time. > > 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 > Thanks, Dane
Re: [GENERAL] Ubuntu 15.04 Installing Oracle_fdw
El 02/01/16 a las 12:51, Leonardo M. Ramé escribió: Hi, I compiled oracle_fdw.so on Ubuntu 15.04 Server 64bits and I'm getting the "No such file or directory" error when I try to create the oracle_fdw extension. After doing make and make install on Oracle fdw source oracle_fdw.so is built and installed correctly (oracle_fdw.so is copied to my $libdir directory those related files are copied to the extension directory: ls -lah /usr/share/postgresql/9.4/extension -rw-r--r-- 1 root root 231 ene 2 12:19 oracle_fdw--1.0--1.1.sql -rw-r--r-- 1 root root 1003 ene 2 12:19 oracle_fdw--1.1.sql -rw-r--r-- 1 root root 133 ene 2 12:19 oracle_fdw.control But, when I try to create the extension I get this: psql (9.4.4, server 9.4.2) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. postgres=# create extension oracle_fdw; ERROR: could not open extension control file "/usr/share/postgresql/9.4/extension/oracle_fdw.control": No such file or directory Any hint? hehe, sorry, I was connected to a remote host using psql. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Enforcing referential integrity against a HSTORE column
On 01/02/2016 08:13 AM, Dane Foster wrote: Ccing list. On Sat, Jan 2, 2016 at 10:30 AM, Adrian Klaver mailto:adrian.kla...@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 form. 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.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Enforcing referential integrity against a HSTORE column
On Sat, Jan 2, 2016 at 12:19 PM, Adrian Klaver wrote: > On 01/02/2016 08:13 AM, Dane Foster wrote: > > Ccing list. > >> On Sat, Jan 2, 2016 at 10:30 AM, Adrian Klaver >> mailto:adrian.kla...@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 form. 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 > > That's exactly what I did. The json_object_agg function sealed the deal. > -- > Adrian Klaver > adrian.kla...@aklaver.com > Dane
Re: [GENERAL] Ubuntu 15.04 Installing Oracle_fdw
On 01/02/2016 08:22 AM, Leonardo M. Ramé wrote: El 02/01/16 a las 12:51, Leonardo M. Ramé escribió: Hi, I compiled oracle_fdw.so on Ubuntu 15.04 Server 64bits and I'm getting the "No such file or directory" error when I try to create the oracle_fdw extension. After doing make and make install on Oracle fdw source oracle_fdw.so is built and installed correctly (oracle_fdw.so is copied to my $libdir directory those related files are copied to the extension directory: ls -lah /usr/share/postgresql/9.4/extension -rw-r--r-- 1 root root 231 ene 2 12:19 oracle_fdw--1.0--1.1.sql -rw-r--r-- 1 root root 1003 ene 2 12:19 oracle_fdw--1.1.sql -rw-r--r-- 1 root root 133 ene 2 12:19 oracle_fdw.control But, when I try to create the extension I get this: psql (9.4.4, server 9.4.2) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. postgres=# create extension oracle_fdw; ERROR: could not open extension control file "/usr/share/postgresql/9.4/extension/oracle_fdw.control": No such file or directory Any hint? hehe, sorry, I was connected to a remote host using psql. So that would explain this: psql (9.4.4, server 9.4.2) I take it then that connecting locally solved the problem? -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How do I implement a .XSD in Postgres?
ERR ORR schrieb: Hi everybody, I need to import some DB schemas which are defined in .XSD (a XML derivative) format. I've googled about this but have found no satisfactory answer. Perhaps I just don't know what exactly to ask Google. Perhaps you are talking about the xsd definition of strongly typed datasets? https://msdn.microsoft.com/en-us/library/esbykkzb(v=vs.110).aspx https://msdn.microsoft.com/en-us/library/84sxtbxh(v=vs.110).aspx Those can be used to create a Dataset in .Net which is something like an in-memory representation of a database but are not intended to be the source of a database schema. https://msdn.microsoft.com/en-us/library/zb0sdh0b(v=vs.110).aspx https://msdn.microsoft.com/en-us/library/3b4194wc(v=vs.110).aspx So please: - What tool can I use in order to import .XSD schema definitions into Postgresql 9.4.5? - If there is no tool, can you please direct me to a document at least hinting at how to import a .XSD schema into a DB? If my guess was correct, there might be tools out there but as I stated this is not what these files are usually made for. http://xsd2db.sourceforge.net/ Thanks and happy new year to all. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
