Skip to content

Cannot execute parameterized queries using certain postgres drivers

I run picodata 24.4.0-46-gb1c5a133 on Ubuntu 22.04. I start picodata with:

picodata run --data-dir i1 --listen :3301 --http-listen :8081 --pg-listen :3302

I am using a single instance setup and I have created user and table with the following queries:

CREATE USER andy WITH PASSWORD 'Passw0rd' USING md5;
CREATE TABLE "characters" (
            "id" INTEGER NOT NULL,
            "name" TEXT NOT NULL,
            "year" INTEGER,
            PRIMARY KEY ("id")
)
USING MEMTX DISTRIBUTED BY ("id")
OPTION (TIMEOUT = 3.0);
GRANT WRITE ON TABLE "characters" TO andy;
GRANT read ON TABLE "characters" TO andy;
INSERT INTO "characters" values (1, 'Dima', 1997);

I have a sample golang application and I am using lib/pq driver to connect to Picodata and I want to get my new character from DB:

package main

import (
	"database/sql"
	"log"

	_ "github.com/lib/pq"
)

type Character struct {
	id   int
	name string
	year int
}

func main() {
	connStr := "postgres://ANDY:Passw0rd@localhost:3302?sslmode=disable"
	db, err := sql.Open("postgres", connStr)
	if err != nil {
		log.Fatal(err)
	}

	id := 1
	row := db.QueryRow("SELECT * FROM \"characters\" WHERE \"id\" = $1", id)

	var char Character
	err = row.Scan(&char.id, &char.name, &char.year)
	if err != nil {
		log.Fatalf("Error during row scan: %s", err)
	}
	log.Printf("Character with id %d is %s and is %d years old\n", char.id, char.name, char.year)
}

If I run such an app, I will recieve the following error:

2024/06/06 19:43:33 Error during row scan: pq: picodata error: sbroad: Lua error (IR dispatch): LuaError(ExecutionError("sbroad: LuaError(ExecutionError(\"Type mismatch: can not convert string('1') to integer\"))"))

And I expect the following to be printed:

2024/06/06 19:48:18 Character with id 1 is Dima and is 1997 years old

The problems appears only with the parametrization of the query. If I will swap the query with the following code I will receive my character

row := db.QueryRow("SELECT * FROM \"characters\" WHERE \"id\" = 1")
Edited by Дмитрий Кольцов
To upload designs, you'll need to enable LFS and have an admin enable hashed storage. More information