PostgreSQL not returning a complete response when there is an error

Question

I'm getting an issue that happens when I run a query that results in an error.

For example, if I run the following query

SELECT * FROM users WHERE account_id=1;

The table users does not have an account_id column.

The correct error message returns:

 00000000  45 00 00 00 6e 53 45 52  52 4f 52 00 56 45 52 52  |E...nSERROR.VERR|
 00000010  4f 52 00 43 34 32 37 30  33 00 4d 63 6f 6c 75 6d  |OR.C42703.Mcolum|
 00000020  6e 20 22 61 63 63 6f 75  6e 74 5f 69 64 22 20 64  |n "account_id" d|
 00000030  6f 65 73 20 6e 6f 74 20  65 78 69 73 74 00 50 32  |oes not exist.P2|
 00000040  37 00 46 70 61 72 73 65  5f 72 65 6c 61 74 69 6f  |7.Fparse_relatio|
 00000050  6e 2e 63 00 4c 33 32 39  33 00 52 65 72 72 6f 72  |n.c.L3293.Rerror|
 00000060  4d 69 73 73 69 6e 67 43  6f 6c 75 6d 6e 00 00 5a  |MissingColumn..Z|
 00000070  00 00 00 05 49                                    |....I|

Note the ending of the response.

But every now and then I will get the following response from PostgreSQL.

 00000000  45 00 00 00 6e 53 45 52  52 4f 52 00 56 45 52 52  |E...nSERROR.VERR|
 00000010  4f 52 00 43 34 32 37 30  33 00 4d 63 6f 6c 75 6d  |OR.C42703.Mcolum|
 00000020  6e 20 22 61 63 63 6f 75  6e 74 5f 69 64 22 20 64  |n "account_id" d|
 00000030  6f 65 73 20 6e 6f 74 20  65 78 69 73 74 00 50 32  |oes not exist.P2|
 00000040  37 00 46 70 61 72 73 65  5f 72 65 6c 61 74 69 6f  |7.Fparse_relatio|
 00000050  6e 2e 63 00 4c 33 32 39  33 00 52 65 72 72 6f 72  |n.c.L3293.Rerror|
 00000060  4d 69 73 73 69 6e 67 43  6f 6c 75 6d 6e 00 00     |MissingColumn..|

And the client then fails to parse the response because it's missing the ending.

Is this something I'm doing wrong, or is this possibly a bug in PostgreSQL?

I'm receiving the response from PostgreSQL in Golang's net.Conn via Read()

Thoughts?

The script to create the table:

 DROP TABLE IF EXISTS ready.public.users;

 CREATE TABLE ready.public.users
 (
   user_id             UUID             NOT NULL,
   email               VARCHAR(325)     NOT NULL,
   password            VARCHAR(2000)    NULL,
   first_name          VARCHAR(200)     NULL,
   last_name           VARCHAR(200)     NULL,
   global_permissions  BIGINT DEFAULT 1 NOT NULL,
   is_system           BOOLEAN          NOT NULL DEFAULT false,
   is_device_only      BOOLEAN          NOT NULL DEFAULT false,
   date_created        TIMESTAMP        NOT NULL,
   date_updated        TIMESTAMP        NULL,
   date_last_logged_in TIMESTAMP        NULL,
   date_last_seen      TIMESTAMP        NULL
 );

The code I have in my golang library.

 rConn.Write(buf.Bytes())

 data := make([]byte, 2048)
 n, err := rConn.Read(data)
 data = data[:n]
 if err != nil {
    if err != io.EOF {
        panic(err)
    } else {
        log.Printf("received err: %v", err)
    }
 }

The value of buf.Bytes() is:

 00000000  50 00 00 00 2e 00 53 45  4c 45 43 54 20 2a 20 46  |P.....SELECT * F|
 00000010  52 4f 4d 20 75 73 65 72  73 20 57 48 45 52 45 20  |ROM users WHERE |
 00000020  61 63 63 6f 75 6e 74 5f  69 64 3d 31 00 00 00 42  |account_id=1...B|
 00000030  00 00 00 0c 00 00 00 00  00 00 00 00 44 00 00 00  |............D...|
 00000040  06 50 00 45 00 00 00 09  00 00 00 01 f6 53 00 00  |.P.E.........S..|
 00000050  00 04                                             |..|

Changing my code to read the response from postgresql to this still results in the same response from the server.

 rBuf := &bytes.Buffer{}
 for {
    data := make([]byte, 256)
    n, err := rConn.Read(data)
    if err != nil {
        panic(err)
    }
    rBuf.Write(data[:n])
    if n < 256 {
        break
    }
 }
 go log.Printf("received:\n%v", hex.Dump(rBuf.Bytes()))

Did you import _ "github.com/lib/pq" in addition to database/sql? — jrefior
– jrefior, Commented Jun 23, 2018 at 19:32
You assume that you can read the whole response with single read (and that it is not bigger than 2048 bytes). This might not be the case. Isn't there response length in the start of the response packet? Or shouldn't the rConn.Read() return indicator that whole response is read (ie io.EOF or some other special error)? — ain
– ain, Commented Jun 23, 2018 at 19:32
You only make a single read! It might take several reads to get the whole stream. So after first read you should determine the packet length and then read until you have whole stream read into buffer. — ain
– ain, Commented Jun 23, 2018 at 19:39

jrefior · Accepted Answer · 2018-06-24 16:03:15Z

2

Is it hitting the panic and printing an error message? Is it breaking out of the loop before it's done reading?

In the "changed code" section, you are panicking even on io.EOF and you aren't doing anything with the read bytes in that case. Try removing the panic, and using the read bytes before error handling (as you were in the "before" snippet), and loop until you get a read error. I would at least want to find out/test:

buf := new(bytes.Buffer)
for {
    data := make([]byte, 2048)
    n, err := rConn.Read(data)
    if n > 0 {
        buf.Write(data[:n])
    }
    if err != nil {
        log.Printf("Error reading from connection: %s\n", err)
        break
    }
}
log.Printf("Received: %s\n", buf.String())

edited Jun 24, 2018 at 16:03

answered Jun 24, 2018 at 15:20

jrefior

4,4612 gold badges21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL not returning a complete response when there is an error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related