Quantcast

postgresql/postgis views and primary keys

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

postgresql/postgis views and primary keys

Geoff Clitheroe-2
Hi,

has the issue with postgresql views not having primary keys been solved yet?  I see the tantalisingly named 'Primary key metadata table' in the datastore config.  Is this the solution and if so is there any documentation on how to set it up?

Thanks,
Geoff


------------------------------------------------------------------------------


_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgresql/postgis views and primary keys

Geoff Clitheroe-2


On Thu, May 20, 2010 at 1:03 PM, Geoff Clitheroe <[hidden email]> wrote:
Hi,

has the issue with postgresql views not having primary keys been solved yet?  I see the tantalisingly named 'Primary key metadata table' in the datastore config.  Is this the solution and if so is there any documentation on how to set it up?

Thanks,
Geoff


And to seemingly answer my own question:

It looks like the metadata table is being handled by geotools-2.6.1:./library/jdbc/src/main/java/org/geotools/jdbc/MetadataTablePrimaryKeyFinder.java

Which says:

**
 * Looks up primary key information in a metadata table provided by the user
 * <p>
 * The table schema will contain:
 * <ul>
 * <li>table_schema (varchar): schema name</li>
 * <li>table_name (varchar): table name</li>
 * <li>pk_column (varchar): column name</li>
 * <li>pk_column_idx (integer): column index if pk is multicolumn (nullable)</li>
 * <li>pk_policy (varchar): pk assignment policy: "assigned", "sequence", "autogenerated"</li>
 * <li>pk_sequence (varchar): full name of the sequence to be used to generate the next value, if
 * any</li>
 * </ul>
 *
 * By default the table is named 'gt_pk_metadata_table'
 *
 * @author Andrea Aime - OpenGeo
 *
 */


So I made a table:

CREATE TABLE gt_pk_metadata_table (table_schema varchar(255), table_name varchar(255), pk_column varchar(255), pk_column_idx integer, pk_policy varchar(255), pk_sequence varchar(255));

add some data

insert into gt_pk_metadata_table(table_schema, table_name, pk_column, pk_column_idx, pk_policy, pk_sequence) VALUES ('qrt', 'eventhistory', 'eventid', null, 'sequence', 'qrt.event_eventid_seq');

Where eventhistory is my view and eventid is a PK from an underlying table that is included in the view.

Then in the datastore config I defined the 'Primary key metadata table' as 'public.gt_pk_metadata_table' and now in the GeoRSS output for the eventhistory layer the eventid column is being used as the primary key and the linking works.

At this stage I don't know if it breaks anything else.  Also, I'm just reading - no attempts to update data via the view or anything like that.

Cheers,
Geoff


------------------------------------------------------------------------------


_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgresql/postgis views and primary keys

Rahkonen Jukka (Tike)
Hi,

You have discovered it right. It will work also with updating. We are using metadatatable with Oracle and WFS-T both with   updatable views and tables. For us the most valuable thing is the possibility to select which sequence is used for generating new fids.

-Jukka Rahkonen-

Geoff Clitheroe wrote:

>> Hi,
>>
>> has the issue with postgresql views not having primary keys been solved
>> yet?  I see the tantalisingly named 'Primary key metadata table' in the
>> datastore config.  Is this the solution and if so is there any documentation
>> on how to set it up?
>>
>> Thanks,
>> Geoff
>>
>>
> And to seemingly answer my own question:

> It looks like the metadata table is being handled by
> geotools-2.6.1:./library/jdbc/src/main/java/org/geotools/jdbc/MetadataTablePrimaryKeyFinder.java

> Which says:

> **
 * Looks up primary key information in a metadata table provided by the user
 * <p>
 * The table schema will contain:
 * <ul>
 * <li>table_schema (varchar): schema name</li>
 * <li>table_name (varchar): table name</li>
 * <li>pk_column (varchar): column name</li>
 * <li>pk_column_idx (integer): column index if pk is multicolumn
(nullable)</li>
 * <li>pk_policy (varchar): pk assignment policy: "assigned", "sequence",
"autogenerated"</li>
 * <li>pk_sequence (varchar): full name of the sequence to be used to
generate the next value, if
 * any</li>
 * </ul>
 *
 * By default the table is named 'gt_pk_metadata_table'
 *
 * @author Andrea Aime - OpenGeo
 *
 */


> So I made a table:

> CREATE TABLE gt_pk_metadata_table (table_schema varchar(255), table_name
> varchar(255), pk_column varchar(255), pk_column_idx integer, pk_policy
> varchar(255), pk_sequence varchar(255));

> add some data

> insert into gt_pk_metadata_table(table_schema, table_name, pk_column,
> pk_column_idx, pk_policy, pk_sequence) VALUES ('qrt', 'eventhistory',
> 'eventid', null, 'sequence', 'qrt.event_eventid_seq');

> Where eventhistory is my view and eventid is a PK from an underlying table
> that is included in the view.

> Then in the datastore config I defined the 'Primary key metadata table' as
> 'public.gt_pk_metadata_table' and now in the GeoRSS output for the
> eventhistory layer the eventid column is being used as the primary key and
> the linking works.

> At this stage I don't know if it breaks anything else.  Also, I'm just
> reading - no attempts to update data via the view or anything like that.

> Cheers,
> Geoff


------------------------------------------------------------------------------

_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgresql/postgis views and primary keys

george-silva
This is great. I was talking about dekstop GIS softwares, but it's nice to know that there is a way to deal with that in GeoServer.

Congrats Geoff. I'll add this to my repertoire of knowledge :P

George

On Thu, May 20, 2010 at 1:31 AM, Rahkonen Jukka <[hidden email]> wrote:
Hi,

You have discovered it right. It will work also with updating. We are using metadatatable with Oracle and WFS-T both with   updatable views and tables. For us the most valuable thing is the possibility to select which sequence is used for generating new fids.

-Jukka Rahkonen-

Geoff Clitheroe wrote:

>> Hi,
>>
>> has the issue with postgresql views not having primary keys been solved
>> yet?  I see the tantalisingly named 'Primary key metadata table' in the
>> datastore config.  Is this the solution and if so is there any documentation
>> on how to set it up?
>>
>> Thanks,
>> Geoff
>>
>>
> And to seemingly answer my own question:

> It looks like the metadata table is being handled by
> geotools-2.6.1:./library/jdbc/src/main/java/org/geotools/jdbc/MetadataTablePrimaryKeyFinder.java

> Which says:

> **
 * Looks up primary key information in a metadata table provided by the user
 * <p>
 * The table schema will contain:
 * <ul>
 * <li>table_schema (varchar): schema name</li>
 * <li>table_name (varchar): table name</li>
 * <li>pk_column (varchar): column name</li>
 * <li>pk_column_idx (integer): column index if pk is multicolumn
(nullable)</li>
 * <li>pk_policy (varchar): pk assignment policy: "assigned", "sequence",
"autogenerated"</li>
 * <li>pk_sequence (varchar): full name of the sequence to be used to
generate the next value, if
 * any</li>
 * </ul>
 *
 * By default the table is named 'gt_pk_metadata_table'
 *
 * @author Andrea Aime - OpenGeo
 *
 */


> So I made a table:

> CREATE TABLE gt_pk_metadata_table (table_schema varchar(255), table_name
> varchar(255), pk_column varchar(255), pk_column_idx integer, pk_policy
> varchar(255), pk_sequence varchar(255));

> add some data

> insert into gt_pk_metadata_table(table_schema, table_name, pk_column,
> pk_column_idx, pk_policy, pk_sequence) VALUES ('qrt', 'eventhistory',
> 'eventid', null, 'sequence', 'qrt.event_eventid_seq');

> Where eventhistory is my view and eventid is a PK from an underlying table
> that is included in the view.

> Then in the datastore config I defined the 'Primary key metadata table' as
> 'public.gt_pk_metadata_table' and now in the GeoRSS output for the
> eventhistory layer the eventid column is being used as the primary key and
> the linking works.

> At this stage I don't know if it breaks anything else.  Also, I'm just
> reading - no attempts to update data via the view or anything like that.

> Cheers,
> Geoff


------------------------------------------------------------------------------

_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users



--
George R. C. Silva

Desenvolvimento em GIS
http://blog.geoprocessamento.net

------------------------------------------------------------------------------


_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgresql/postgis views and primary keys

babsip
In reply to this post by Geoff Clitheroe-2
Hi Geoff,

hope you don't mind me unearthing this thread, but I'm curious how you managed the thing with the PK in the view. I'm quite stuck with a PostGIS-view without a PK and don't want to create a table instead of the view. I'm accessing the view from a complex feature (actually non-feature type that is accessed by a complex feature via Feature Chaining)

I seem to have trouble with "pk_column" : you said you filled it with a PK from an underlying table that is included in the view. Is the PK  defined as a CONSTRAINT PRIMARY KEY? Did you mean the PK itself is included in the view? Or just the table? If the PK itself is included - how did you manage to include it? I tried to create a view containing a PK of one of the tables like this, e.g.:

first one of the tables of the view:
--------------------------------
CREATE TABLE map_species_ps
(
  id_species character varying(20) NOT NULL,
  id_ps character varying(20) NOT NULL,
  CONSTRAINT map_species_ps_pkey PRIMARY KEY (id_species, id_ps)
)
WITH (
  OIDS=FALSE
);

omitting creation of second table "species"

the view:
---------
CREATE OR REPLACE VIEW species_in_ps_PK AS
 SELECT map_species_ps.id_species, map_species_ps.id_ps, species.title, species.href, map_species_ps.map_species_ps_pkey
   FROM species
   JOIN map_species_ps ON species.idstr::text = map_species_ps.id_species::text;

error:
-----
ERROR:  Column map_species_ps.map_species_ps_pkey does not exist
LINE 2: ...ap_species_ps.id_ps, species.title, species.href, map_specie...
                                                                              ^

I then created the view without the PK. Then I created gt_pk_metadata_table like this:

CREATE TABLE gt_pk_metadata_table (
   table_schema VARCHAR(32) NOT NULL,
   table_name VARCHAR(32) NOT NULL,
   pk_column VARCHAR(32) NOT NULL,
   pk_column_idx INTEGER,
   pk_policy VARCHAR(32),
   pk_sequence VARCHAR(64),
   unique (table_schema, table_name, pk_column),
   check (pk_policy in ('sequence', 'assigned', 'autoincrement'))
)

and inserted my view:

INSERT INTO gt_pk_metadata_table(table_schema, table_name, pk_column, pk_column_idx, pk_policy)
       VALUES ('public', 'species_in_ps', 'map_species_ps.map_species_ps_pkey', null, 'assigned');

I'm not sure, though, whether the values are correct? I'm quite at a loss with how to populate "pk_policy" and "pk_sequence". Do you have a clue which one I should use?

Now GeoServer reports an error when starting up:

WARN [geotools.jdbc] - Unknown column map_species_ps.map_species_ps_pkey in table species_in_ps
WARN [geotools.jdbc] - No primary key or unique index found for species_in_ps.

I have described the problem in more detail in this thread:
http://old.nabble.com/Error-duplicate-mappingName-in-2.0.2-%28but-works-fine-in-2.0.1%29-td29570368.html

Thank you very much in advance for your help!

Barbara

Geoff Clitheroe-2 wrote
On Thu, May 20, 2010 at 1:03 PM, Geoff Clitheroe <g.clitheroe@gmail.com>wrote:

And to seemingly answer my own question:

It looks like the metadata table is being handled by
geotools-2.6.1:./library/jdbc/src/main/java/org/geotools/jdbc/MetadataTablePrimaryKeyFinder.java

Which says:

**
 * Looks up primary key information in a metadata table provided by the user
 * <p>
 * The table schema will contain:
 * <ul>
 * <li>table_schema (varchar): schema name</li>
 * <li>table_name (varchar): table name</li>
 * <li>pk_column (varchar): column name</li>
 * <li>pk_column_idx (integer): column index if pk is multicolumn
(nullable)</li>
 * <li>pk_policy (varchar): pk assignment policy: "assigned", "sequence",
"autogenerated"</li>
 * <li>pk_sequence (varchar): full name of the sequence to be used to
generate the next value, if
 * any</li>
 * </ul>
 *
 * By default the table is named 'gt_pk_metadata_table'
 *
 * @author Andrea Aime - OpenGeo
 *
 */


So I made a table:

CREATE TABLE gt_pk_metadata_table (table_schema varchar(255), table_name
varchar(255), pk_column varchar(255), pk_column_idx integer, pk_policy
varchar(255), pk_sequence varchar(255));

add some data

insert into gt_pk_metadata_table(table_schema, table_name, pk_column,
pk_column_idx, pk_policy, pk_sequence) VALUES ('qrt', 'eventhistory',
'eventid', null, 'sequence', 'qrt.event_eventid_seq');

Where eventhistory is my view and eventid is a PK from an underlying table
that is included in the view.

Then in the datastore config I defined the 'Primary key metadata table' as
'public.gt_pk_metadata_table' and now in the GeoRSS output for the
eventhistory layer the eventid column is being used as the primary key and
the linking works.

At this stage I don't know if it breaks anything else.  Also, I'm just
reading - no attempts to update data via the view or anything like that.

Cheers,
Geoff

------------------------------------------------------------------------------


_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgresql/postgis views and primary keys

Rahkonen Jukka (Tike)
Hi,

Metadata record must suit your your data source and in your case it is the view.  Information needed:

- schema of the view
- name of the view
- column in the view that should be used as primary key
- in case of multi-column primary keys, index of first primary key part, and then again the column and index for the next part
- pk_policy and pk_sequence are needed only with WFS-T  so you can left them null

Primary key columns will be eaten out from the output.  If you need to get the information of the primary key columns into output you can select it twise into the view

create view view_name as (select primary_key PK, primary_key to_be_published, second_column, ....)

-Jukka Rahkonen-



-----Alkuperäinen viesti-----
Lähettäjä: babsip [mailto:[hidden email]]
Lähetetty: pe 3.9.2010 17:08
Vastaanottaja: [hidden email]
Aihe: Re: [Geoserver-users] postgresql/postgis views and primary keys
 

Hi Geoff,

hope you don't mind me unearthing this thread, but I'm curious how you
managed the thing with the PK in the view. I'm quite stuck with a
PostGIS-view without a PK and don't want to create a table instead of the
view. I'm accessing the view from a complex feature (actually non-feature
type that is accessed by a complex feature via Feature Chaining)

I seem to have trouble with "pk_column" : you said you filled it with a PK
from an underlying table that is included in the view. Is the PK  defined as
a CONSTRAINT PRIMARY KEY? Did you mean the PK itself is included in the
view? Or just the table? If the PK itself is included - how did you manage
to include it? I tried to create a view containing a PK of one of the tables
like this, e.g.:

first one of the tables of the view:
--------------------------------
CREATE TABLE map_species_ps
(
  id_species character varying(20) NOT NULL,
  id_ps character varying(20) NOT NULL,
  CONSTRAINT map_species_ps_pkey PRIMARY KEY (id_species, id_ps)
)
WITH (
  OIDS=FALSE
);

omitting creation of second table "species"

the view:
---------
CREATE OR REPLACE VIEW species_in_ps_PK AS
 SELECT map_species_ps.id_species, map_species_ps.id_ps, species.title,
species.href, map_species_ps.map_species_ps_pkey
   FROM species
   JOIN map_species_ps ON species.idstr::text =
map_species_ps.id_species::text;

error:
-----
ERROR:  Column map_species_ps.map_species_ps_pkey does not exist
LINE 2: ...ap_species_ps.id_ps, species.title, species.href, map_specie...
                                                                             
^

I then created the view without the PK. Then I created gt_pk_metadata_table
like this:

CREATE TABLE gt_pk_metadata_table (
   table_schema VARCHAR(32) NOT NULL,
   table_name VARCHAR(32) NOT NULL,
   pk_column VARCHAR(32) NOT NULL,
   pk_column_idx INTEGER,
   pk_policy VARCHAR(32),
   pk_sequence VARCHAR(64),
   unique (table_schema, table_name, pk_column),
   check (pk_policy in ('sequence', 'assigned', 'autoincrement'))
)

and inserted my view:

INSERT INTO gt_pk_metadata_table(table_schema, table_name, pk_column,
pk_column_idx, pk_policy)
       VALUES ('public', 'species_in_ps',
'map_species_ps.map_species_ps_pkey', null, 'assigned');

I'm not sure, though, whether the values are correct? I'm quite at a loss
with how to populate "pk_policy" and "pk_sequence". Do you have a clue which
one I should use?

Now GeoServer reports an error when starting up:

WARN [geotools.jdbc] - Unknown column map_species_ps.map_species_ps_pkey in
table species_in_ps
WARN [geotools.jdbc] - No primary key or unique index found for
species_in_ps.

I have described the problem in more detail in this thread:
http://old.nabble.com/Error-duplicate-mappingName-in-2.0.2-%28but-works-fine-in-2.0.1%29-td29570368.html
http://old.nabble.com/Error-duplicate-mappingName-in-2.0.2-%28but-works-fine-in-2.0.1%29-td29570368.html 

Thank you very much in advance for your help!

Barbara


Geoff Clitheroe-2 wrote:

>
> On Thu, May 20, 2010 at 1:03 PM, Geoff Clitheroe
> <[hidden email]>wrote:
>
> And to seemingly answer my own question:
>
> It looks like the metadata table is being handled by
> geotools-2.6.1:./library/jdbc/src/main/java/org/geotools/jdbc/MetadataTablePrimaryKeyFinder.java
>
> Which says:
>
> **
>  * Looks up primary key information in a metadata table provided by the
> user
>  * <p>
>  * The table schema will contain:
>  * <ul>
>  * <li>table_schema (varchar): schema name</li>
>  * <li>table_name (varchar): table name</li>
>  * <li>pk_column (varchar): column name</li>
>  * <li>pk_column_idx (integer): column index if pk is multicolumn
> (nullable)</li>
>  * <li>pk_policy (varchar): pk assignment policy: "assigned", "sequence",
> "autogenerated"</li>
>  * <li>pk_sequence (varchar): full name of the sequence to be used to
> generate the next value, if
>  * any</li>
>  * </ul>
>  *
>  * By default the table is named 'gt_pk_metadata_table'
>  *
>  * @author Andrea Aime - OpenGeo
>  *
>  */
>
>
> So I made a table:
>
> CREATE TABLE gt_pk_metadata_table (table_schema varchar(255), table_name
> varchar(255), pk_column varchar(255), pk_column_idx integer, pk_policy
> varchar(255), pk_sequence varchar(255));
>
> add some data
>
> insert into gt_pk_metadata_table(table_schema, table_name, pk_column,
> pk_column_idx, pk_policy, pk_sequence) VALUES ('qrt', 'eventhistory',
> 'eventid', null, 'sequence', 'qrt.event_eventid_seq');
>
> Where eventhistory is my view and eventid is a PK from an underlying table
> that is included in the view.
>
> Then in the datastore config I defined the 'Primary key metadata table' as
> 'public.gt_pk_metadata_table' and now in the GeoRSS output for the
> eventhistory layer the eventid column is being used as the primary key and
> the linking works.
>
> At this stage I don't know if it breaks anything else.  Also, I'm just
> reading - no attempts to update data via the view or anything like that.
>
> Cheers,
> Geoff
>
> ------------------------------------------------------------------------------
>
>
> _______________________________________________
> Geoserver-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>
>

--
View this message in context: http://old.nabble.com/postgresql-postgis-views-and-primary-keys-tp28616028p29614681.html
Sent from the GeoServer - User mailing list archive at Nabble.com.


------------------------------------------------------------------------------
This SF.net Dev2Dev email is sponsored by:

Show off your parallel programming skills.
Enter the Intel(R) Threading Challenge 2010.
http://p.sf.net/sfu/intel-thread-sfd
_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users


------------------------------------------------------------------------------
This SF.net Dev2Dev email is sponsored by:

Show off your parallel programming skills.
Enter the Intel(R) Threading Challenge 2010.
http://p.sf.net/sfu/intel-thread-sfd
_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: postgresql/postgis views and primary keys

babsip
Hi Jukka,

thanks a lot for this information, it was exactly what I needed! It works now!

I think I confused Primary Key Constraint (which wrongly inserted into gt_pk_metadata_table) with the column that should SERVE as primary key.

Thanks!
Barbara

Rahkonen Jukka wrote
Hi,

Metadata record must suit your your data source and in your case it is the view.  Information needed:

- schema of the view
- name of the view
- column in the view that should be used as primary key
- in case of multi-column primary keys, index of first primary key part, and then again the column and index for the next part
- pk_policy and pk_sequence are needed only with WFS-T  so you can left them null

Primary key columns will be eaten out from the output.  If you need to get the information of the primary key columns into output you can select it twise into the view

create view view_name as (select primary_key PK, primary_key to_be_published, second_column, ....)

-Jukka Rahkonen-
Loading...