<br><div class="gmail_extra"><br><br><div class="gmail_quote">On Wed, Nov 14, 2012 at 11:36 AM, Alejandro Imass <span dir="ltr"><<a href="mailto:alejandro.imass@gmail.com" target="_blank">alejandro.imass@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div class="im">On Wed, Nov 14, 2012 at 7:45 AM, Fernan Aguero <span dir="ltr"><<a href="mailto:fernan.aguero@gmail.com" target="_blank">fernan.aguero@gmail.com</a>></span> wrote:<br>
</div><div class="gmail_extra"><div class="gmail_quote"><div class="im">
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex">Hi,<br>
<br>
I'm having this issue with my catalyst app where the session table is<br>
not fully qualified in the generated SQL statement:<br></blockquote></div><div><br>[...]<br> </div><div class="im"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex">
The sessions table lives in my PostgreSQL database in a separate<br>
schema 'webapp'.<br>
<br>
So I would expect the statement to be:<br>
DELETE FROM webapp.sessions WHERE ( id = ? )<br>
<br>
I also have the table name fully qualified in the corresponding DBIC<br>
schema class (GUS/Webapp/Sessions.pm):<br>
<br></blockquote></div><div><br>[...]<br><br>DBIC works well with schemas.<br></div></div></div></blockquote><div><br></div><div>I know. The app works so far OK with all schemas in the DB. The only one giving problems is the sessions table.</div>
<div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div class="gmail_extra"><div class="gmail_quote">
<div>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? <br></div></div></div></blockquote><div><br></div><div>Of course, I can reproduce succesfully what DBIC is trying to do on the Pg terminal. E.g. for the following error:</div>
<div><br></div><div>[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'] </div>
<div><br></div><div>I can fix the query and run it (using the same userid/credentials of the catalyst app) without issues:</div><div><br></div><div><div>tcsnp3=> DELETE FROM webapp.sessions WHERE ( id = 'session:c7d07ff1ec06ebf2c6fc37c3cb8f36b117053384' );</div>
<div>DELETE 1</div></div><div><br></div><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div class="gmail_extra">
<div class="gmail_quote"><div>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.<br>
</div></div></div></blockquote><div><br></div><div>The owner of all schemas is 'dba'.</div><div><br></div><div>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:</div>
<div><a href="http://www.postgresql.org/docs/8.4/static/sql-grant.html">http://www.postgresql.org/docs/8.4/static/sql-grant.html</a><br></div><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
<div class="gmail_extra"><div class="gmail_quote"><div>Also, if you are creating your Result classes with loader have you tried "{loader_options=>{db_schema=>'foo'}}<br></div></div></div></blockquote><div>
<br></div><div>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. </div>
<div><br></div><div>__PACKAGE__->table("schemaname.tablename");</div><div> <br></div><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
<div class="gmail_extra"><div class="gmail_quote"><div>Best,<span class=""><font color="#888888"><br><br>-- <br>Alejandro Imass</font></span></div></div></div></blockquote></div><div class="gmail_extra"><br></div><div class="gmail_extra">
Thanks Alejandro for the quick response. I still think that this is exposing a bug in Session::Store::DBIC or <span style="background-color:rgb(238,238,238);color:rgb(0,0,0)">Session</span><span class="" style="color:rgb(0,0,0)">::</span><span style="background-color:rgb(238,238,238);color:rgb(0,0,0)">Store</span><span class="" style="color:rgb(0,0,0)">::</span><span style="background-color:rgb(238,238,238);color:rgb(0,0,0)">Delegate. They should use whatever qualified name is given in the DBIC class ... but I'm not sure where to go next ...</span></div>
<br clear="all"><div><br></div>-- <br>fernan<br>
</div>