FAQ

On Wed, Nov 14, 2012 at 11:36 AM, Alejandro Imass wrote:

On Wed, Nov 14, 2012 at 7:45 AM, Fernan Aguero wrote:

Hi,

I'm having this issue with my catalyst app where the session table is
not fully qualified in the generated SQL statement:
[...]

The sessions table lives in my PostgreSQL database in a separate
schema 'webapp'.

So I would expect the statement to be:
DELETE FROM webapp.sessions WHERE ( id = ? )

I also have the table name fully qualified in the corresponding DBIC
schema class (GUS/Webapp/Sessions.pm):
[...]

DBIC works well with schemas.

I know. The app works so far OK with all schemas in the DB. The only one
giving problems is the sessions table.



The first obvious question is permissions. Have you tried manually
connecting as the app user using pgsql and then selecting the table using
the fq name?

Of course, I can reproduce succesfully what DBIC is trying to do on the Pg
terminal. E.g. for the following error:


[error] Scheduler: Error executing /cron/remove_sessions:
DBIx::Class::ResultSet::delete(): DBI Exception: DBD::Pg::st execute
failed: ERROR: relation "sessions" does not exist [for Statement "DELETE
FROM sessions WHERE ( id = ? )" with ParamValues:
1='session:c7d07ff1ec06ebf2c6fc37c3cb8f36b117053384']


I can fix the query and run it (using the same userid/credentials of the
catalyst app) without issues:


tcsnp3=> DELETE FROM webapp.sessions WHERE ( id =
'session:c7d07ff1ec06ebf2c6fc37c3cb8f36b117053384' );
DELETE 1




Pg requires grants on an object by object basis not like MySQL where you
can do something like grant all on *.db. You have to grant one by one to
the Catalyst DB user to the objects in the particular schema, unless of
course the user is the owner of the schema and in that case you don't even
need to fq the object names.

The owner of all schemas is 'dba'.


The catalyst user has 'arwdxt' privileges on the webapp schema (this schema
contains the sessions and users tables). Essentially it has all privileges
except TRUNCATE:
http://www.postgresql.org/docs/8.4/static/sql-grant.html


Also, if you are creating your Result classes with loader have you tried
"{loader_options=>{db_schema=>'foo'}}

I am creating the Result classes in this way for larger schemas. However,
for this particular schema that isolates the catalyst-specific tables, I've
manually created the classes (there are only two tables), and I've checked
that it follows the same convention as those created by loader, e.g.


__PACKAGE__->table("schemaname.tablename");




Best,
--
Alejandro Imass

Thanks Alejandro for the quick response. I still think that this is
exposing a bug in Session::Store::DBIC or Session::Store::Delegate. They
should use whatever qualified name is given in the DBIC class ... but I'm
not sure where to go next ...




--

fernan
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/catalyst/attachments/20121114/7adb40b7/attachment.htm

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 3 of 10 | next ›
Discussion Overview
groupcatalyst @
categoriescatalyst, perl
postedNov 14, '12 at 12:45p
activeNov 15, '12 at 2:08p
posts10
users3
websitecatalystframework.org
irc#catalyst

People

Translate

site design / logo © 2022 Grokbase