[ckan-dev] Use view instead of table for datastore resource

Mon Apr 4 14:38:51 UTC 2016

Ok, I'm going to ask in another way with this issue, as I've found out some
more.

On Mon, Apr 4, 2016 at 1:00 PM, Michael Nielsen <mic.niel84 at gmail.com>
wrote:

> t fails with the line:
>
> Error - <class 'ckan.logic.NotFound'>:
>
> On Mon, Apr 4, 2016 at 12:59 PM, Michael Nielsen <mic.niel84 at gmail.com>
> wrote:
>
>> By creating another view between the actual table and the view with same
>> name as resource_id things work WHEN I'm logged in on the CKAN web GUI.
>> When logging out, things fails again. Wow, seems very complicated to use
>> a view as resource for the datastore instead of a table.
>>
>> On Mon, Apr 4, 2016 at 12:34 PM, Michael Nielsen <mic.niel84 at gmail.com>
>> wrote:
>>
>>> Investigating further, it seems as if my view gets assigned an alias
>>> "test", which all the other real tables doesn't get assigned.
>>> It's coming from a view called "_table_metadata" which gets the alias
>>> from a table called pg_class (and it's column "relname".
>>>
>>> On Mon, Apr 4, 2016 at 12:21 PM, Michael Nielsen <mic.niel84 at gmail.com>
>>> wrote:
>>>
>>>> Just to clarify, when using DROP TABLE resource_id and then manually
>>>> creating a table it still works.
>>>> So it's the fact that it's a view and not a table which seems to cast
>>>> an error.
>>>> But somehow I guess the datastore shouldn't are if it's a table or a
>>>> view as they have somewhat same behaviour?
>>>>
>>>> On Mon, Apr 4, 2016 at 12:05 PM, Michael Nielsen <mic.niel84 at gmail.com>
>>>> wrote:
>>>>
>>>>> I would like to use a view instead of a table as a DataStore resource.
>>>>>
>>>>> I'm creating my DataStore only resource via the API datastore_create:
>>>>>
>>>>>     {
>>>>>     "force": true,
>>>>>     "resource": {
>>>>>         "package_id": "3d2101e5-0166-46ba-b112-8b9da5882a42",
>>>>>         "name": "Test"
>>>>>         }
>>>>>     }
>>>>>
>>>>> The DataStore table is created successfully with 2 empty columns "_id"
>>>>> and "_full_text".
>>>>>
>>>>> I'm then trying to use:
>>>>>
>>>>>     DROP TABLE "resource_id";
>>>>>
>>>>> And create a similar view:
>>>>>
>>>>>     CREATE VIEW "resource_id" AS (
>>>>>         SELECT row_number() OVER (ORDER BY id) AS _id,
>>>>>         to_tsvector('') AS _full_text,
>>>>>         column1, column2
>>>>>         FROM schema-test.my-table
>>>>>     );
>>>>>
>>>>> When querying the resource in PSQL everything seems to match other
>>>>> normal DataStore resources. But CKAN fails to show the resource. I'm
>>>>> getting an internal error. The log shows:
>>>>>
>>>>>     ...SELECT * FROM "test" LIMIT 1'], 'orig': ['relation "test" does
>>>>> not exist\\nLINE 1: SELECT * FROM "test" LIMIT 1\\n
>>>>>  ^\\n']}, 'query': ['Invalid query']}...
>>>>>
>>>>> So it seems as if there is created some sort of alias to the table,
>>>>> which gets deleted when I'm running DROP TABLE.
>>>>> Can I recreate that correctly or is the error some where else?
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.okfn.org/pipermail/ckan-dev/attachments/20160404/ede765a0/attachment-0003.html>