Title
#users-public
e

Elina Kuldkepp

08/30/2022, 11:50 AM
Hello, thank you. I have a weird problem. For some reason I can not anymore add new rows to my questDB database via Postgresql wire protocol. Does anyone else have similar issue?
Pei

Pei

08/30/2022, 12:02 PM
Hi Elina, could you provide more context, for example, did you see any errors in the log? Which version are you currently using? Have you been using Postgres for ingestion all along and it suddenly stopped, or are you also ingesting via another interface at the same time and only PGwire doesm’t work, etc.?
e

Elina Kuldkepp

08/30/2022, 12:16 PM
This is my docker compose
questdb:
  image: questdb/questdb:6.1.1
  restart: unless-stopped
  volumes:
    - "/var/lib/questdb:/root/.questdb"
  ports:
    - '8812:8812'
    - '9009:9009'
  networks:
    - default
  environment:
    - QDB_PG_USER=${QUESTDB_USER}
    - QDB_PG_PASSWORD=${QUESTDB_PASS}
  logging:
    driver: "json-file"
    options: 
      tag: "{{.ImageName}}|{{.Name}}|{{.ImageFullID}}|{{.FullID}}"
I created table, and tried to add rows like this:
CREATE TABLE rawdata(device INT, tag INT, modality INT, ts TIMESTAMP, value DOUBLE, desc STRING, created TIMESTAMP)  timestamp(ts) PARTITION BY DAY;

INSERT INTO rawdata (device, tag, modality, ts, value, desc, created) VALUES (3, 3, 2, '2021-01-10', 8040, 'des99c', '2021-01-10');


select * from rawdata;
creation and select are working. Insert gives either 0 1 or 0 0
dev=> INSERT INTO rawdata (device, tag, modality, ts, value, desc) VALUES (3, 3, 2, '2021-01-10', 8040, 'des99c');
INSERT 0 1
container logs are here:
2022-08-30T12:06:23.863088Z I pg-server connected [ip="secret:D", fd=90]
2022-08-30T12:06:23.867262Z I i.q.c.p.PGConnectionContext protocol [major=3, minor=0]
2022-08-30T12:06:23.867286Z I i.q.c.p.PGConnectionContext property [name=user, value=dev]
2022-08-30T12:06:23.867288Z I i.q.c.p.PGConnectionContext property [name=dev, value=database]
2022-08-30T12:06:23.867290Z I i.q.c.p.PGConnectionContext property [name=database, value=dev]
2022-08-30T12:06:23.867291Z I i.q.c.p.PGConnectionContext property [name=dev, value=application_name]
2022-08-30T12:06:23.867293Z I i.q.c.p.PGConnectionContext property [name=application_name, value=psql]
2022-08-30T12:06:23.867294Z I i.q.c.p.PGConnectionContext property [name=psql, value=client_encoding]
2022-08-30T12:06:23.867296Z I i.q.c.p.PGConnectionContext property [name=client_encoding, value=UTF8]
2022-08-30T12:06:23.867297Z I i.q.c.p.PGConnectionContext property [name=UTF8, value=]
2022-08-30T12:06:23.869284Z I pg-server scheduling disconnect [fd=90, reason=15]
2022-08-30T12:06:23.869348Z I pg-server disconnected [ip="secret:D", fd=90, src=queue]
2022-08-30T12:06:25.244714Z I pg-server connected [ip="secret:D", fd=90]
2022-08-30T12:06:25.244962Z I i.q.c.p.PGConnectionContext protocol [major=3, minor=0]
2022-08-30T12:06:25.244993Z I i.q.c.p.PGConnectionContext property [name=user, value=dev]
2022-08-30T12:06:25.244997Z I i.q.c.p.PGConnectionContext property [name=dev, value=database]
2022-08-30T12:06:25.244999Z I i.q.c.p.PGConnectionContext property [name=database, value=dev]
2022-08-30T12:06:25.245001Z I i.q.c.p.PGConnectionContext property [name=dev, value=application_name]
2022-08-30T12:06:25.245003Z I i.q.c.p.PGConnectionContext property [name=application_name, value=psql]
2022-08-30T12:06:25.245005Z I i.q.c.p.PGConnectionContext property [name=psql, value=client_encoding]
2022-08-30T12:06:25.245006Z I i.q.c.p.PGConnectionContext property [name=client_encoding, value=UTF8]
2022-08-30T12:06:25.245008Z I i.q.c.p.PGConnectionContext property [name=UTF8, value=]
2022-08-30T12:06:28.078307Z I i.q.c.p.PGConnectionContext parse [fd=90, q=INSERT INTO rawdata (device, tag, modality, ts, value, desc) VALUES (3, 3, 2, '2021-01-10', 8040, 'des99c');]
2022-08-30T12:06:28.086791Z I i.q.c.p.WriterPool >> [table=`rawdata`, thread=12]
2022-08-30T12:06:28.086832Z I i.q.c.TableWriter switched to o3 [table=rawdata]
2022-08-30T12:06:32.481358Z I http-server disconnected [ip="secret:D", fd=38, src=idle]
It used to work for me some weeks ago in another server but now it does not work on old and today's new server.
12:17 PM
Note: everything does work over the API / UI interface
Jaromir Hamala

Jaromir Hamala

08/30/2022, 12:18 PM
hi Elina, how did you figure out it does not work? is your program calling SELECT immediately after the INSERT?
Andrey Pechkurov

Andrey Pechkurov

08/30/2022, 12:18 PM
6.1.1 is a very old version. I'd suggest switching to 6.5.1. Tons of bugs were fixed since 6.1.1
e

Elina Kuldkepp

08/30/2022, 12:18 PM
psql is invoked like this:
psql -h localhost -p 8812 -U dev
12:19 PM
Yes, after insert I ran
select * from rawdata;
Jaromir Hamala

Jaromir Hamala

08/30/2022, 12:25 PM
indeed, I would try the latest version if you can. there have been tons of improvements & fixes sine 6.1 times.
12:33 PM
Elina: can you upgrade QuestDB and populate your entire database from scratch or would you prefer to just upgrade while keeping your current data?
e

Elina Kuldkepp

08/31/2022, 7:50 AM
Thanks, seems that version update worked 👍
Pei

Pei

08/31/2022, 10:28 AM
Glad to know! Thank you for reporting back!