Re: [Catalyst] Session::Store::DBIC and session table (postgres)
On Wed, Nov 14, 2012 at 5:56 PM, Francisco Obispo fobi...@isc.org wrote:
Use:
$c-model('GUS::WebappSession') as the name.
So, it was a good suggestion after all ... it didn't work at first, and I
think this led me to (maybe) a possible fix
This doesn't work:
sub remove_sessions : Private {
my ( $self, $c ) = @_;
$c-delete_expired_sessions;
}
And this doesn't either:
sub remove_sessions : Private {
my ( $self, $c ) = @_;
my $model = $c-model('GUS::WebappSession');
my $rs = $model-search( { expires = { '' , time() } } )-delete;
}
But this workaround did work:
sub remove_sessions : Private {
my ( $self, $c ) = @_;
my $model = $c-model('GUS::WebappSession');
my $rs = $model-search( { expires = { '' , time() } } );
while (my $session = $rs-next() ) {
$session-delete;
}
}
So, apparently the problem is calling delete() right on the resultset
object. For some reason that eludes me, DBIC is not fully qualifying the
table in this case.
Whereas when I call delete() on the DBIC::Class::Row? object it works.
Now looking back I guess I should have started all this fuss on the DBIC
mailing list :)
Folks, apologies for the cross-post, but I'm now moving the discussion to
its natural home.
For those interested here's the link to the complete thread:
http://www.mail-archive.com/catalyst@lists.scsys.co.uk/msg13542.html
And here's the link to the RT ticket
https://rt.cpan.org/Ticket/Display.html?id=81179
--
fernan
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
Re: [Dbix-class] Re: [Catalyst] Session::Store::DBIC and session table (postgres)
On Thu, Nov 15, 2012 at 10:38 AM, Peter Rabbitson rabbit+d...@rabbit.uswrote: On Thu, Nov 15, 2012 at 10:31:21AM -0300, Fernan Aguero wrote: So, apparently the problem is calling delete() right on the resultset object. For some reason that eludes me, DBIC is not fully qualifying the table in this case. This is a known issue, RT#80015. Should be fixed shortly (hopefully before next weekend). In the meantime use $rs-delete_all, which in essence is your workaround, just a little more efficient. Great! Will patch my code (and Session::Store::DBIC) with your suggestion until then. Thanks! -- fernan ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Session::Store::DBIC and session table (postgres)
On Wed, Nov 14, 2012 at 7:45 AM, Fernan Aguero fernan.agu...@gmail.comwrote:
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.
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?
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.
Also, if you are creating your Result classes with loader have you tried
{loader_options={db_schema='foo'}}
Best,
--
Alejandro Imass
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Session::Store::DBIC and session table (postgres)
On Wed, Nov 14, 2012 at 11:36 AM, Alejandro Imass alejandro.im...@gmail.com
wrote:
On Wed, Nov 14, 2012 at 7:45 AM, Fernan Aguero fernan.agu...@gmail.comwrote:
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
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Session::Store::DBIC and session table (postgres)
On Wed, Nov 14, 2012 at 10:07 AM, Fernan Aguero fernan.agu...@gmail.comwrote: [...] 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 ... Could be. I've used schemas with Pg and DBIC with Cat but usually just to a single one being the app user the same owner, so the are no fq names in my Result files. Maybe as you say it's specific to the Session Store. Good luck. maybe look at source and contact the module's author directly? -- Alejandro Imass ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Session::Store::DBIC and session table (postgres)
Did you use the dbicdump script (or myapp_create.pl model) command to create the schema files? I would suggest you look to see how the 'sessions' table was created and use that as the name for the relation. regards On Nov 14, 2012, at 7:07 AM, Fernan Aguero fernan.agu...@gmail.com wrote: 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 Francisco Obispo Director of Applications and Services - ISC email: fobi...@isc.org Phone: +1 650 423 1374 || INOC-DBA *3557* NOC PGP KeyID = B38DB1BE ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Session::Store::DBIC and session table (postgres)
On Wed, Nov 14, 2012 at 3:09 PM, Francisco Obispo fobi...@isc.org wrote: Did you use the dbicdump script (or myapp_create.pl model) command to create the schema files? I would suggest you look to see how the 'sessions' table was created and use that as the name for the relation. regards I did not use the dbicdump script originally (these were tables from an old app). I generated all schema classes using perl -MDBIx::Class::Schema::Loader=dump_to_dir:lib/ -Mschemaname -e1 where schemaname was a DBIx::Class::Schema::Loader class specifying connection and loader options (e.g. schema). However, I've now tried the newer dbicdump script, and still get the same error: [debug] Scheduler: Executing /cron/remove_sessions [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 ( expires ? ) with ParamValues: 1='1352925249'] at /home/fernan/perl5/lib/perl5/Catalyst/Plugin/Session/Store/DBIC.pm The new classes are slightly different: lib/GUS/Webapp/Sessions.pm (old) lib/GUS/Result/WebappSession.pm (new) but still they both call __PACKAGE__-table(webapp.sessions); and function with no issues for creating new sessions in the Pg storage, updating the session_data, etc. They're only failing at the DELETE statement, and from the error it is clear that they're failing to fully qualify the table name. I'm now CCing FLORA to see if he's aware of this behaviour and/or can help see if it falls into the Session::Store::DBIC realm ... Thanks again for all responses! -- fernan ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Session::Store::DBIC and session table (postgres)
Use:
$c-model('GUS::WebappSession') as the name.
or in the config:
Plugin::Session
dbic_class GUS::WebappSession
id_field id
storage_field session_data
expires_field expires
need_commit 0
expires 3600
verify_address 1
/Plugin::Session
francisco
On Nov 14, 2012, at 12:51 PM, Fernan Aguero fernan.agu...@gmail.com wrote:
The new classes are slightly different:
lib/GUS/Webapp/Sessions.pm (old)
lib/GUS/Result/WebappSession.pm (new)
Francisco Obispo
Director of Applications and Services - ISC
email: fobi...@isc.org
Phone: +1 650 423 1374 || INOC-DBA *3557* NOC
PGP KeyID = B38DB1BE
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
