Sporadic StatementInvalid: relation "sessions" does not exist

[Svoop]

Hello

Our Rails 3.2 app which relies on AR for session storage and OmniAuth spradicly raises the following exception:

A ActiveRecord::StatementInvalid occurred in channels#show:

 PG::Error: ERROR:  relation "sessions" does not exist
LINE 4:              WHERE a.attrelid = '"sessions"'::regclass
                                       ^
:             SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull
             FROM pg_attribute a LEFT JOIN pg_attrdef d
               ON a.attrelid = d.adrelid AND a.attnum = d.adnum
            WHERE a.attrelid = '"sessions"'::regclass
              AND a.attnum > 0 AND NOT a.attisdropped
            ORDER BY a.attnum

 vendor/bundle/ruby/1.9.1/gems/activerecord-3.2.2/lib/active_record/connection_adapters/postgresql_adapter.rb:1139:in `async_exec'

However, I can execute the above SELECT query without any problem in "rails db". And the exception occurs on the homepage which sees many hits, yet onle one or two exceptions per day are risen. Could this be a ruby-pg issue?

Here's the full stack trace:
http://pastie.org/private/ydzlmx8nzlkwpauagmchmw

Thanks for your hints!

[Michael Granger]

On Wednesday, March 28, 2012 11:51:24 PM UTC-7, Svoop wrote:

Hello

Our Rails 3.2 app which relies on AR for session storage and OmniAuth spradicly raises the following exception:

A ActiveRecord::StatementInvalid occurred in channels#show:

 PG::Error: ERROR:  relation "sessions" does not exist
LINE 4:              WHERE a.attrelid = '"sessions"'::regclass
                                       ^

[...]

However, I can execute the above SELECT query without any problem in "rails db". And the exception occurs on the homepage which sees many hits, yet onle one or two exceptions per day are risen. Could this be a ruby-pg issue?

This query will fail from psql if there is no 'sessions' table, and succeed using ruby-pg if there is one:

$ irb --prompt xmp ~/temp/sessions_query.rb

require 'pg'

    ==>true

query = <<-END_SQL

SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull

FROM pg_attribute a LEFT JOIN pg_attrdef d

ON a.attrelid = d.adrelid AND a.attnum = d.adnum

WHERE a.attrelid = '"sessions"'::regclass

AND a.attnum > 0 AND NOT a.attisdropped

ORDER BY a.attnum

END_SQL

    ==>"SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull\nFROM pg_attribute a LEFT JOIN pg_attrdef d\nON a.attrelid = d.adrelid AND a.attnum = d.adnum\nWHERE a.attrelid = '\"sessions\"'::regclass\nAND a.attnum > 0 AND NOT a.attisdropped\nORDER BY a.attnum\n"

c = PG.connect( dbname: 'test' )

    ==>#<PG::Connection:0x007fa7410516b8>

c.exec( "DROP TABLE IF EXISTS sessions" )

NOTICE:  table "sessions" does not exist, skipping

    ==>#<PG::Result:0x007fa74103f738>

c.exec( query )

PG::Error: ERROR:  relation "sessions" does not exist

LINE 4: WHERE a.attrelid = '"sessions"'::regclass

                           ^

        from /Users/mgranger/temp/sessions_query.rb:12:in `exec'

        from /Users/mgranger/temp/sessions_query.rb:12

        from /Users/mgranger/.rvm/rubies/ruby-1.9.3-p125/bin/irb:16:in `<main>'

c.exec( "CREATE TABLE sessions ( id serial )" )

NOTICE:  CREATE TABLE will create implicit sequence "sessions_id_seq" for serial column "sessions.id"

    ==>#<PG::Result:0x007fa741020590>

c.exec( query )

    ==>#<PG::Result:0x007fa7419bae20>

So somehow your sessions table is getting deleted, a database handle is pointed to the wrong database or schema, or something is connecting as a user that can't see the sessions table.

Hope this helps.

[Svoop]

On Thursday, March 29, 2012 6:06:43 PM UTC+2, Michael Granger wrote:

So somehow your sessions table is getting deleted, a database handle is pointed to the wrong database or schema, or something is connecting as a user that can't see the sessions table.

How is any of this the possible cause if it only happens in one out of thousands of successful requests? The app is not changed in any way, nor any other moving part, still one request fails, the next few thousand work fine, then another one fails etc.

[Harold Giménez]

If you're app is multithreaded this can happen if you share the same connection among threads. Is that the case?

-- 
Harold Giménez

--
You received this message because you are subscribed to the Google Groups "ruby-pg" group.
To view this discussion on the web visit https://groups.google.com/d/msg/ruby-pg/-/ksfsGxl8I2AJ.
To post to this group, send email to rub...@googlegroups.com.
To unsubscribe from this group, send email to ruby-pg+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/ruby-pg?hl=en.

[Svoop]

On Monday, April 2, 2012 1:59:00 AM UTC+2, Harold Giménez wrote:

If you're app is multithreaded this can happen if you share the same connection among threads. Is that the case?

Not really, it's just an ordinary Rails app served by Nginx/Passenger from one single box - which has been rebooted since the Session model was introduced.

[Michael Granger]

I wasn't hypothesizing about a cause; I can't diagnose what your particular application is doing with its database handles. My point was that the error you're seeing comes from the database server itself, so your application code or one of the frameworks on which it depends is doing something to cause the 'sessions' table not to be visible. I was offering guesses as to what the problem might be. If the PostgreSQL server responds with an error code, ruby-pg will raise it as an exception. As such, without further evidence to the contrary, it appears to me that your issue isn't an issue in ruby-pg.

That said, there are a number of things you can do from the driver to help diagnose the problem. You can enable query tracing:

conn.trace( $stderr ) # Log to STDERR

or:

conn.trace( File.open('pgtrace.log','a') )

You can increase the verbosity of error messages from the backend:

conn.set_error_verbosity( PG::PQERRORS_VERBOSE )

You can log the PID of the connection on the server to track which requests are using which connection handles:

$stderr.puts "PGPID: %s" % [ conn.backend_pid ]

You can set up a notice processor to log server notices, and then increase the verbosity of them:

c.set_notice_receiver {|pgres| logfile.puts(pgres.error_message) }
c.exec( "set client_min_messages to debug5" )

--
Michael Granger <g...@FaerieMUD.org>
Rubymage, Architect, Believer
The FaerieMUD Consortium <http://FaerieMUD.org/>

[Svoop]

Thanks for the debugging hints, I'll try to find out where exactly the error originates.

[Svoop]

I found the source of these requests: Passenger's "passenger_pre_start" directive (Nginx).

[Michael Granger]

On Wednesday, April 4, 2012 3:31:16 AM UTC-7, Svoop wrote:

I found the source of these requests: Passenger's "passenger_pre_start" directive (Nginx).

Ah, glad you were able to track it down. Was there some reference to this problem that you found, or did you just do detective work to find it? Future readers might be helped if you gave more details. 

[Lars Kanis]

Stacktrace, SQL string and error message match together, which otherwise could signalize a threading issue. The only idea I have, is that the schema search path is changed somewhere in the application, so that the next request fails.

Regards,
Lars

Am 04.04.2012 10:36 schrieb "Svoop" <sv...@delirium.ch>:

Thanks for the debugging hints, I'll try to find out where exactly the error originates.

--

You received this message because you are subscribed to the Google Groups "ruby-pg" group.

To view this discussion on the web visit https://groups.google.com/d/msg/ruby-pg/-/LZxOIlcLVPYJ.

[Svoop]

On Wednesday, April 4, 2012 4:28:11 PM UTC+2, Michael Granger wrote:

Ah, glad you were able to track it down. Was there some reference to this problem that you found, or did you just do detective work to find it? Future readers might be helped if you gave more details. 

I know, this thread is old, but I've never answered to this.

The reason for this weird error were calls to the default server by use of the IP (e.g. http://11.22.33.44). We use Nginx, so the solution was quite simple: Setup a default server (including the default_server directive) and redirect to the proper domain name URL.

I figured it out by installing the exception_notification gem and then comparing the offending requests. They were all using the IP in the URL and mainly coming from malicious crawlers.

Cheers, -sven

[Lars Kanis]

Hi Sven,

great you were able to solve the issue! And thank you for sharing your solution!

Regards,
Lars

[rajan....@kiprosh.com]

Try commenting 'ActionController::Base.session_store = :active_record_store' in your session_store.rb I solved this error by doing that

Regards

Rajan