Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

'Token "-" is invalid' when inserting into PostgreSQL server via Rails and pg gem

I've recently started experimenting with setting up a new local PostgreSQL server, hooked up to an existing Rails app. I have a table I am trying to insert to:

postgres=# \d+ events.t_sales_events
                                                             Table "events.t_sales_events"
   Column   |            Type             |                              Modifiers                              | Storage  | Stats target | Description 
------------+-----------------------------+---------------------------------------------------------------------+----------+--------------+-------------
 id         | integer                     | not null default nextval('events.t_sales_events_id_seq'::regclass) | plain    |              | 
 event_name | character varying(45)       | not null                                                            | extended |              | 
 actor_id   | integer                     |                                                                     | plain    |              | 
 actor_type | character varying(45)       |                                                                     | extended |              | 
 json       | jsonb                       |                                                                     | extended |              | 
 created    | timestamp without time zone | not null                                                            | plain    |              | 
 modified   | timestamp without time zone | not null                                                            | plain    |              | 
 deleted    | timestamp without time zone |                                                                     | plain    |              | 
 timestamp  | timestamp without time zone | not null                                                            | plain    |              | 
 context    | text                        |                                                                     | extended |              | 
Indexes:
    "t_sales_events_pkey" PRIMARY KEY, btree (id)

This is an example of an event that I'm trying to insert into t_sales_events:

event =
    {
        :event_name => "lead_created",
           :context => nil,
        :actor_type => "sales_user",
         :timestamp => "2013-03-18T07:13:42.000+0000",
              :json => {
                       :dc_id => "00AA000000AAaaAAaa1",
                          :name => "John Doe",
                         :title => "CEO",
                          :role => nil,
                         :phone => nil,
                       :company => "Does Does",
                         :email => "[email protected]",
                   :dc_source => nil,
                       :lead_id => nil
        },
          :actor_id => nil,
           :created => 2015-09-15 18:32:25 -0700,
          :modified => 2015-09-15 18:32:33 -0700
    }

And this is the error I get when I run SalesEvent.create(event):(0.2ms)

BEGIN
  SQL (4.0ms)  INSERT INTO "events"."t_sales_events" ("actor_id", "actor_type", "context", "created", "deleted", "event_name", "json", "modified", "timestamp") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING "id"  [["actor_id", nil], ["actor_type", "sales_user"], ["context", nil], ["created", Wed, 16 Sep 2015 01:32:25 UTC +00:00], ["deleted", nil], ["event_name", "lead_created"], ["json", "---\n:sfdc_id: 00AA000000AAaaAAaa1\n:name: John Doe\n:title: CEO\n:role: \n:phone: \n:company: Does Does\n:email: [email protected]\n:lead_id: \n"], ["modified", Wed, 16 Sep 2015 01:32:33 UTC +00:00], ["timestamp", Mon, 18 Mar 2013 07:13:42 UTC +00:00]]
PG::InvalidTextRepresentation: ERROR:  invalid input syntax for type json
DETAIL:  Token "-" is invalid.
CONTEXT:  JSON data, line 1: -...
: INSERT INTO "events"."t_sales_events" ("actor_id", "actor_type", "context", "created", "deleted", "event_name", "json", "modified", "timestamp") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING "id"
   (0.1ms)  ROLLBACK
*** ActiveRecord::StatementInvalid Exception: PG::InvalidTextRepresentation: ERROR:  invalid input syntax for type json
DETAIL:  Token "-" is invalid.
CONTEXT:  JSON data, line 1: -...
: INSERT INTO "events"."t_sales_events" ("actor_id", "actor_type", "context", "created", "deleted", "event_name", "json", "modified", "timestamp") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING "id"

It seems to me that the error arises because "---" is added to the beginning of the field labelled 'json', but I'm at a loss as to why.

EDIT: I've added the model for SalesEvent below. I did indeed have serialize :json in there. Apologies - I forgot that I'd added that in a somewhat blind attempt to try and resolve the same error.

class SalesEvent < ActiveRecord::Base

  establish_connection :warehouse_development
  self.table_name = "events.t_sales_events"

  serialize :json

  def self.get_all
    SalesEvent.all
  end

end

Removing that particular line returns the same 'Token "-" is invalid' error, albeit due to different input:

(0.1ms)  BEGIN
  SQL (4.2ms)  INSERT INTO "events"."t_sales_events" ("actor_id", "actor_type", "context", "created", "deleted", "event_name", "json", "modified", "timestamp") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING "id"  [["actor_id", nil], ["actor_type", "sales_user"], ["context", nil], ["created", Wed, 16 Sep 2015 06:09:00 UTC +00:00], ["deleted", nil], ["event_name", "lead_created"], ["json", {:sfdc_id=>"00AA000000AAaaAAaa1", :name=>"John Doe", :title=>"CEO", :role=>nil, :phone=>nil, :company=>"Does Does", :email=>"[email protected]", :dc_source=>nil, :lead_id=>nil}], ["modified", Wed, 16 Sep 2015 06:09:07 UTC +00:00], ["timestamp", Mon, 18 Mar 2013 07:13:42 UTC +00:00]]
PG::InvalidTextRepresentation: ERROR:  invalid input syntax for type json
DETAIL:  Token "-" is invalid.
CONTEXT:  JSON data, line 1: -...
: INSERT INTO "events"."t_sales_events" ("actor_id", "actor_type", "context", "created", "deleted", "event_name", "json", "modified", "timestamp") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING "id"
   (0.1ms)  ROLLBACK
*** ActiveRecord::StatementInvalid Exception: PG::InvalidTextRepresentation: ERROR:  invalid input syntax for type json
DETAIL:  Token "-" is invalid.
CONTEXT:  JSON data, line 1: -...
: INSERT INTO "events"."t_sales_events" ("actor_id", "actor_type", "context", "created", "deleted", "event_name", "json", "modified", "timestamp") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING "id"
like image 250
jayengee Avatar asked Nov 09 '22 05:11

jayengee


1 Answers

Turns out it was the incompatibility of the postgres connector in rails 3.2.17 with jsonb objects. After upgrading to 4.2.4, the error stopped cropping up.

like image 168
jayengee Avatar answered Nov 14 '22 22:11

jayengee