| Subject: | plugin fails to DELETE rows from storage when table is in a separate schema (PostgreSQL) |
The issue is described to some extend in this thread:
http://www.mail-archive.com/catalyst@lists.scsys.co.uk/msg13542.html
Essentially I'm relying on Session, Session::State::Cookie and Session::Store::DBIC to manage session data in my app.
My app is a frontend to a complex database schema, deployed in PostgreSQL. To isolate the operation that are strictly related to the catalyst webapp (sessions,
users), I created a separate schema in the database (called 'webapp').
With this layout:
1. All queries to the session storage succeed.
2. The DELETE queries fail.
3. This is not an issue of PostgreSQL privileges
Configuration:
[fernan@optima] cat tcsnp_local.yml
Model::GUS:
schema_class: GUS
connect_info:
- dbi:Pg:dbname=tcsnp
- fernan
- //PASS//
- AutoCommit: 1
session:
dbic_class: GUS::WebappSession
cookie_name: tcsnp_cookie
cookie_expires: 0
DBIC class for the webapp.sessions table (attached)
Some observed behaviors:
Success (SQL statement shows fully qualified table names):
using DBIC_TRACE=1
[info] tcsnp powered by Catalyst 5.90018
HTTP::Server::PSGI: Accepting connections at http://0:3006/
BEGIN WORK
SELECT me.id, me.session_data, me.expires FROM webapp.sessions me WHERE (
me.id = ? ): 'session:eff3bb1616f1ee3341477c4d1056c51c7ed826a6'
COMMIT
[info] *** Request 1 (0.167/s) [14993] [Wed Nov 14 18:22:13 2012] ***
[debug] Path is "genes"
[debug] "GET" request for "genes" from "127.0.0.1"
[debug] Rendering template "genes/search.tt"
[debug] Applying HTML page layout wrappers to genes/search.tt
[debug] Found sessionid "eff3bb1616f1ee3341477c4d1056c51c7ed826a6" in cookie
[debug] Response Code: 200; Content-Type: text/html; charset=utf-8;
Content-Length: 25221
[info] Request took 0.331861s (3.013/s)
Failure (notice how now the sessions table is not fully qualified with the schema name anymore):
[debug] Scheduler: Executing /cron/remove_sessions
[error] Scheduler: Error executing /cron/remove_sessions: DBI Exception: DBD::Pg::st execute failed: ERROR: relation "sessions" does not exist [for Statement
"DELETE FROM sessions WHERE ( expires < ? )" with ParamValues: 1='1352929241'] at /home/fernan/perl5/lib/perl5/DBIx/Class/Schema.pm line 1101.
DBIx::Class::Schema::throw_exception('GUS=HASH(0xbfc2518)', 'DBI Exception: DBD::Pg::st execute failed: ERROR: relation "...') called at
/home/fernan/perl5/lib/perl5/DBIx/Class/Storage.pm line 112
DBIx::Class::Storage::throw_exception('DBIx::Class::Storage::DBI::Pg=HASH(0xbfc2a58)', 'DBI Exception: DBD::Pg::st execute failed: ERROR: relation
"...') called at /home/fernan/perl5/lib/perl5/DBIx/Class/Storage/DBI.pm line 1427
DBIx::Class::Storage::DBI::__ANON__('DBD::Pg::st execute failed: ERROR: relation "sessions" does ...', 'DBI::st=HASH(0xe4f8f78)', undef) called at
/home/fernan/perl5/lib/perl5/DBIx/Class/Storage/DBI.pm line 1756
DBIx::Class::Storage::DBI::_dbh_execute('DBIx::Class::Storage::DBI::Pg=HASH(0xbfc2a58)', 'DBI::db=HASH(0xde9d9d8)', 'DELETE FROM sessions WHERE ( expires
< ? )', 'ARRAY(0xedfbad0)', 'ARRAY(0xd9a0398)') called at /home/fernan/perl5/lib/perl5/DBIx/Class/Storage/DBI.pm line 799
DBIx::Class::Storage::DBI::__ANON__() called at /home/fernan/perl5/lib/perl5/DBIx/Class/Storage/BlockRunner.pm line 137
DBIx::Class::Storage::BlockRunner::__ANON__() called at /usr/share/perl5/Try/Tiny.pm line 71
eval {...} called at /usr/share/perl5/Try/Tiny.pm line 67
Try::Tiny::try('CODE(0xedcee30)', 'Try::Tiny::Catch=REF(0xde8b5c8)') called at /home/fernan/perl5/lib/perl5/DBIx/Class/Storage/BlockRunner.pm line 141
DBIx::Class::Storage::BlockRunner::__ANON__() called at /home/fernan/perl5/lib/perl5/Context/Preserve.pm line 42
Context::Preserve::preserve_context('CODE(0xedb78c8)', 'replace', 'CODE(0xedc58a8)') called at
/home/fernan/perl5/lib/perl5/DBIx/Class/Storage/BlockRunner.pm line 219
DBIx::Class::Storage::BlockRunner::_run('DBIx::Class::Storage::BlockRunner=HASH(0xe4f9b10)') called at
/home/fernan/perl5/lib/perl5/DBIx/Class/Storage/BlockRunner.pm line 110
DBIx::Class::Storage::BlockRunner::run('DBIx::Class::Storage::BlockRunner=HASH(0xe4f9b10)') called at
/home/fernan/perl5/lib/perl5/DBIx/Class/Storage/DBI.pm line 802
DBIx::Class::Storage::DBI::dbh_do(undef, undef, 'DELETE FROM sessions WHERE ( expires < ? )', 'ARRAY(0xedfbad0)', 'ARRAY(0xd9a0398)') called at
/home/fernan/perl5/lib/perl5/DBIx/Class/Storage/DBI.pm line 1721
DBIx::Class::Storage::DBI::_execute('DBIx::Class::Storage::DBI::Pg=HASH(0xbfc2a58)', 'delete', 'DBIx::Class::ResultSource::Table=HASH(0xd260188)',
'HASH(0xde9e770)') called at /home/fernan/perl5/lib/perl5/DBIx/Class/Storage/DBI.pm line 2243
DBIx::Class::Storage::DBI::delete('DBIx::Class::Storage::DBI::Pg=HASH(0xbfc2a58)', 'DBIx::Class::ResultSource::Table=HASH(0xd260188)', 'HASH(0xde9e770)')
called at /home/fernan/perl5/lib/perl5/DBIx/Class/ResultSet.pm line 1804
DBIx::Class::ResultSet::_rs_update_delete('DBIx::Class::ResultSet=HASH(0x8b76950)', 'delete') called at
/home/fernan/perl5/lib/perl5/DBIx/Class/ResultSet.pm line 2000
DBIx::Class::ResultSet::delete('DBIx::Class::ResultSet=HASH(0x8b76950)') called at /home/fernan/perl5/lib/perl5/Catalyst/Plugin/Session/Store/DBIC.pm
line 221
Catalyst::Plugin::Session::Store::DBIC::delete_expired_sessions('tcsnp=HASH(0xde9d978)') called at
/home/fernan/subversion/tcsnp2/development/tcsnp/script/../lib/tcsnp/Controller/cron.pm line 20
tcsnp::Controller::cron::remove_sessions('tcsnp::Controller::cron=HASH(0xd99e5e8)', 'tcsnp=HASH(0xde9d978)') called at
/home/fernan/perl5/lib/perl5/Catalyst/Action.pm line 65
Catalyst::Action::execute('Catalyst::Action=HASH(0xdbdb660)', 'tcsnp::Controller::cron=HASH(0xd99e5e8)', 'tcsnp=HASH(0xde9d978)') called at
/home/fernan/perl5/lib/perl5/Catalyst.pm line 1682
eval {...} called at /home/fernan/perl5/lib/perl5/Catalyst.pm line 1682
Catalyst::execute('tcsnp=HASH(0xde9d978)', 'tcsnp::Controller::cron', 'Catalyst::Action=HASH(0xdbdb660)') called at
/home/fernan/perl5/lib/perl5/Catalyst/Plugin/StackTrace.pm line 94
Catalyst::Plugin::StackTrace::execute('tcsnp=HASH(0xde9d978)', 'tcsnp::Controller::cron', 'Catalyst::Action=HASH(0xdbdb660)') called at
/home/fernan/perl5/lib/perl5/Catalyst/Action.pm line 60
Catalyst::Action::dispatch('Catalyst::Action=HASH(0xdbdb660)', 'tcsnp=HASH(0xde9d978)') called at /home/fernan/perl5/lib/perl5/Catalyst/Dispatcher.pm
line 257
Catalyst::Dispatcher::_do_forward('Catalyst::Dispatcher=HASH(0x3553038)', 'forward', 'tcsnp=HASH(0xde9d978)', '/cron/remove_sessions') called at
/home/fernan/perl5/lib/perl5/Catalyst/Dispatcher.pm line 237
Catalyst::Dispatcher::forward('Catalyst::Dispatcher=HASH(0x3553038)', 'tcsnp=HASH(0xde9d978)', '/cron/remove_sessions') called at
/home/fernan/perl5/lib/perl5/Catalyst.pm line 372
Catalyst::forward('tcsnp=HASH(0xde9d978)', '/cron/remove_sessions') called at /home/fernan/perl5/lib/perl5/Catalyst/Plugin/Scheduler.pm line 134
eval {...} called at /home/fernan/perl5/lib/perl5/Catalyst/Plugin/Scheduler.pm line 121
Catalyst::Plugin::Scheduler::dispatch('tcsnp=HASH(0xde9d978)') called at /home/fernan/perl5/lib/perl5/Catalyst/Plugin/Static/Simple.pm line 77
Class::MOP::Class:::around('CODE(0x3d062f0)', 'tcsnp=HASH(0xde9d978)') called at /home/fernan/perl5/lib/perl5/x86_64-linux-gnu-thread-
multi/Class/MOP/Method/Wrapped.pm line 162
Class::MOP::Method::Wrapped::__ANON__('tcsnp=HASH(0xde9d978)') called at /home/fernan/perl5/lib/perl5/x86_64-linux-gnu-thread-
multi/Class/MOP/Method/Wrapped.pm line 91
tcsnp::dispatch('tcsnp=HASH(0xde9d978)') called at /home/fernan/perl5/lib/perl5/Catalyst.pm line 1978
Catalyst::__ANON__() called at /usr/share/perl5/Try/Tiny.pm line 76
eval {...} called at /usr/share/perl5/Try/Tiny.pm line 67
Try::Tiny::try('CODE(0xe50d918)', 'Try::Tiny::Catch=REF(0xde903b0)') called at /home/fernan/perl5/lib/perl5/Catalyst.pm line 1984
Catalyst::handle_request('tcsnp', 'env', 'HASH(0xc79e9d0)', 'response_cb', 'CODE(0x75824d0)') called at /home/fernan/perl5/lib/perl5/Catalyst/Engine.pm
line 688
Catalyst::Engine::__ANON__('CODE(0x75824d0)') called at /home/fernan/perl5/lib/perl5/Plack/Util.pm line 302
Plack::Util::__ANON__('CODE(0xbe6fff0)') called at /home/fernan/perl5/lib/perl5/HTTP/Server/PSGI.pm line 181
HTTP::Server::PSGI::handle_connection('HTTP::Server::PSGI=HASH(0xdcaa250)', 'HASH(0xc79e9d0)', 'IO::Socket::INET=GLOB(0xd9a1648)', 'CODE(0xdc9dea0)')
called at /home/fernan/perl5/lib/perl5/HTTP/Server/PSGI.pm line 129
HTTP::Server::PSGI::accept_loop('HTTP::Server::PSGI=HASH(0xdcaa250)', 'CODE(0xdca9ed8)') called at /home/fernan/perl5/lib/perl5/HTTP/Server/PSGI.pm line
55
HTTP::Server::PSGI::run('HTTP::Server::PSGI=HASH(0xdcaa250)', 'CODE(0xdca9ed8)') called at /home/fernan/perl5/lib/perl5/Plack/Handler/HTTP/Server/PSGI.pm
line 14
Plack::Handler::HTTP::Server::PSGI::run('Plack::Handler::Standalone=HASH(0x2594b08)', 'CODE(0xdca9ed8)', 'HASH(0x25963a8)') called at
/home/fernan/perl5/lib/perl5/Catalyst/Engine.pm line 670
Catalyst::Engine::run('Catalyst::Engine=HASH(0x41410f8)', 'tcsnp', 'CODE(0xdca9ed8)', 3006, undef, 'HASH(0x25963a8)',
'Plack::Handler::Standalone=HASH(0x2594b08)') called at /home/fernan/perl5/lib/perl5/Catalyst.pm line 2443
Catalyst::run('tcsnp', 3006, undef, 'HASH(0x25963a8)', 'Plack::Handler::Standalone=HASH(0x2594b08)') called at
/home/fernan/perl5/lib/perl5/Catalyst/ScriptRole.pm line 91
Catalyst::ScriptRole::_run_application('Catalyst::Script::Server=HASH(0x2594c28)') called at /home/fernan/perl5/lib/perl5/Catalyst/Script/Server.pm line
242
Catalyst::Script::Server::run('Catalyst::Script::Server=HASH(0x2594c28)') called at /home/fernan/perl5/lib/perl5/Catalyst/ScriptRunner.pm line 50
Catalyst::ScriptRunner::run('Catalyst::ScriptRunner', 'tcsnp', 'Server') called at ./script/tcsnp_server.pl line 8
--
fernan
| Subject: | WebappSession.pm |
use utf8;
package GUS::Result::WebappSession;
# Created by DBIx::Class::Schema::Loader
# DO NOT MODIFY THE FIRST PART OF THIS FILE
=head1 NAME
GUS::Result::WebappSession
=cut
use strict;
use warnings;
use base 'DBIx::Class::Core';
=head1 COMPONENTS LOADED
=over 4
=item * L<DBIx::Class::InflateColumn::DateTime>
=item * L<DBIx::Class::TimeStamp>
=back
=cut
__PACKAGE__->load_components("InflateColumn::DateTime", "TimeStamp");
=head1 TABLE: C<webapp.sessions>
=cut
__PACKAGE__->table("webapp.sessions");
=head1 ACCESSORS
=head2 id
data_type: 'char'
is_nullable: 0
size: 72
=head2 session_data
data_type: 'text'
is_nullable: 1
=head2 expires
data_type: 'integer'
is_nullable: 1
=cut
__PACKAGE__->add_columns(
"id",
{ data_type => "char", is_nullable => 0, size => 72 },
"session_data",
{ data_type => "text", is_nullable => 1 },
"expires",
{ data_type => "integer", is_nullable => 1 },
);
=head1 PRIMARY KEY
=over 4
=item * L</id>
=back
=cut
__PACKAGE__->set_primary_key("id");
# Created by DBIx::Class::Schema::Loader v0.07033 @ 2012-11-14 16:41:47
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:h+AFAzt8UFPDpxCrgkkFgA
# You can replace this text with custom code or comments, and it will be preserved on regeneration
1;