0

Initially I thought this was a heroku issue as, for some bizarre reason this code runs fine locally, but on further investigation I realized id was returning 0 all the time. Essentially I am trying to code an upsert that returns the id. I am working in go with the sql library. 

-- ----------------------------
--  Table structure for books
-- ----------------------------
DROP TABLE IF EXISTS "public"."books" CASCADE;
CREATE TABLE "public"."books" (
    "id" serial primary key,
    "title" varchar(255) NOT NULL COLLATE "default",
    "first" varchar(40) NOT NULL COLLATE "default",
    "last" varchar(40) NOT NULL COLLATE "default",
    "class" varchar(40) NOT NULL COLLATE "default"
)
WITH (OIDS=FALSE);
-- ----------------------------
--  Table structure for bookitems
-- ----------------------------
DROP TABLE IF EXISTS "public"."bookitem";
CREATE TABLE "public"."bookitem" (

    "id" serial primary key,
    "price" int NOT NULL,
    "condition" int NOT NULL,
    "views" int NOT NULL,
    "seller" bigint NOT NULL,
    "book" int REFERENCES books(id),
    "saletype" int NOT NULL,
    "date" bigint NOT NULL,
    "description" varchar(255) NOT NULL COLLATE "default",
    "bucket" int NOT NULL,
    "status" int NOT NULL --This is for showing what the staus of the item is (bought sold etc.)
    --FOREIGN KEY (book) REFERENCES books(id)
)
WITH (OIDS=FALSE);

Here is the code that triggers the error: 

rows, err := db.Query(`with vals as (
      select $1::VARCHAR as title, $2::VARCHAR as first, $3::VARCHAR as last, $4::VARCHAR as class
    )
    insert into books (title, first, last, class)
    select v.title, v.first, v.last, v.class
    from vals as v
    where not exists (select * from books as t where t.title = v.title and t.last = v.last and t.first = v.first and t.class = v.class)
    RETURNING id`, r.FormValue("title"),
            r.FormValue("first"),
            r.FormValue("last"),
            r.FormValue("class"))
        //defer rows.Close()
        rows.Scan(&id)

        PanicIf(err)

        _, err = db.Query("INSERT INTO bookitem (price, condition, views, seller, book, saletype,   date, description, bucket, status) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)",
            r.FormValue("price"),
            r.FormValue("condition"),
            0,
            0,
            id,
            r.FormValue("saletype"),
            0,
            r.FormValue("description"),
            0,
            0)

        PanicIf(err)

The error I get when I run it deployed to heroku is: 

pq: insert or update on table "bookitem" violates foreign key constraint"bookitem_book_fkey"

At this point any suggestions would be appreciated as I have tried twice to fix this to no avail.

P.S. Thank you to all of you who helped me realize the issue was my code and not heroku!

sasms
  • 21
  • 3
  • Not sure what language/library you're working with but are you sure you're getting an `id` out of that INSERT...SELECT? Are you perhaps trying to insert a duplicate row so that the `not exists` is finding something and the INSERT is never done? – mu is too short Aug 23 '15 at 18:10
  • You should tag the question with your language/framework/library to get users who understand it to respond. – eirikir Aug 23 '15 at 21:35
  • I am working with go lang and database sql. I have a id var that is defined above (as an int64). I'll check if it by outputting it and then running it by manually. – sasms Aug 24 '15 at 02:01
  • Ok so @muistooshort was right. My code, not heroku is broken. Any suggestions on how to fix it? Its essentially an upsert returning the id. – sasms Aug 24 '15 at 02:19

1 Answers1

0

So changing it to QueryRow solved my issues. I believe it had to due with a concurrency issue. Hopefully this is useful to someone down the road.

sasms
  • 21
  • 3