Reading pg_dumped file

ERROR: unknown function: nextval()
COPY 87
COPY 10
ERROR: result is ambiguous (error=r371 was not found, pending RPCs=1)
message type 0x5a arrived from server while idle
message type 0x5a arrived from server while idle

The above is logging from reading a schema dumpfile created from a postgres source using pg_dump. The first 2 tables give no problems, just a few rows.
The third table has about 200K rows and only 57100 are read before the ‘ambiguous’ error kicks in.
Many many more similar lines are dumped eventually ending with:
message type 0x5a arrived from server while idle
message type 0x5a arrived from server while idle
message type 0x47 arrived from server while idle
connection not open
connection to server was lost

I was reading using psql from a remote host.

is this known behaviour?
How can I fix this?

cockroach$ cockroach version
Build Tag: v1.0.2
Build Time: 2017/06/15 12:56:53
Distribution: CCL
Platform: darwin amd64
Go Version: go1.8.3
C Compiler: 4.2.1 Compatible Clang 3.8.0 (tags/RELEASE_380/final)
Build SHA-1: 9e3606bd2863ce7a460fd0c842414673d62f0533
Build Type: release

Thanks for the report. What method were you using to import the dump?

There are a few issues here.

  1. CockroachDB doesn’t support sequences yet. nextval() is a sequence function, probably indicating that one of your tables uses a sequence datatype. https://github.com/cockroachdb/cockroach/issues/5811 tracks sequence support.
  2. The ambiguous result error indicates that the server could not be sure whether a transaction was committed, and should probably not be occurring in this case. It might indicate a bug on our side.
  3. The message type 0x5a arrived from server while idle messages indicates that the client and server’s pgwire state machines were thrown out of sync. Depending on what method you were using to run the import, it might indicate a bug in our server or our client.

Can you reproduce this problem easily? Can you share your dump file, or if that’s sensitive, could you share the table schema?

Thanks,
Jordan

Thanks for your reply Jordan,

the sequences stuff is not the problem, I guess I can change that to something like SERIAL when the data is in.
Yes, I can reproduce this at will.
I run this from a linux vm using
psql "postgresql://root@my.url:26257/zabbix?sslcert=certs/client.root.crt&sslkey=certs/client.root.key&sslmode=verify-full&sslrootcert=certs/ca.crt" <zabbix.pgdump

If I should do this smarter, please, enlighten me.
I have no problem sharing this dump, it is 4,4G

accessing a 6 node cdb on osX (well, all running on same mac mini with 16GB mem and cdb using --cache=2G)

How did you create the dump exactly? Any special flags to pg_dump?

Can you upload the dump somewhere, maybe Dropbox or similar?

The dump is in dropbox
please give me a signal when you got it, so I can remove it since my quota is not that high …

The dump is created from postgres 9.6.2 using pg_dump -n dbname dbname >dumpfile.

Okay, feel free to delete it now. I’ll take a look, thank you for the trouble!

Hi Jordan,
I am running the same dump on v1.0.3 now and it looks like there has been a positive change.
COPY 87
COPY 10
COPY 205462
COPY 0
COPY 0
COPY 1477
the dump is not complete yet but there is a good progress. Now reading history, one of the larger tables:
history

CREATE TABLE history (
itemid INT NOT NULL,
clock INT NOT NULL DEFAULT 0:::INT,
value DECIMAL(20,4) NOT NULL DEFAULT 0.0000:::DECIMAL,
ns INT NOT NULL DEFAULT 0:::INT,
FAMILY “primary” (itemid, clock, value, ns, rowid)
)

User Grants
root ALL
1.0 GiB
Size
21
Ranges

There were more errors in reading the dump, I think mostly due to missing features in the db.
like:
ERROR: syntax error at or near "bpchar"
CREATE TABLE users (
userid bigint NOT NULL,
alias character varying(100) DEFAULT ‘’::character varying NOT NULL,
name character varying(100) DEFAULT ‘’::character varying NOT NULL,
surname character varying(100) DEFAULT ‘’::character varying NOT NULL,
passwd character(32) DEFAULT ‘’::bpchar NOT NULL,

history just completed with: COPY 15221715
some bigger tables to follow.

and dang … crashed again.
COPY 15221715
COPY 1996861
COPY 1556966
COPY 1952657
COPY 30015777
COPY 2
COPY 86
COPY 20
COPY 154
COPY 343
COPY 226
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 50
ERROR: expected 4 values, got 1
invalid command .
invalid command .
invalid command .
invalid command .
invalid command .
message type 0x5a arrived from server while idle
message type 0x5a arrived from server while idle
message type 0x5a arrived from server while idle

message type 0x5a arrived from server while idle
invalid command \N
invalid command \N

ending with:
message type 0x5a arrived from server while idle
connection not open
connection to server was lost

the crash happened during processing of table images:
images

CREATE TABLE images (
imageid INT NOT NULL,
imagetype INT NOT NULL DEFAULT 0:::INT,
name STRING(64) NOT NULL DEFAULT ‘0’:::STRING,
image BYTES NOT NULL DEFAULT b’x’,
CONSTRAINT images_pkey PRIMARY KEY (imageid ASC),
FAMILY “primary” (imageid, imagetype, name, image)
)

(the table following ids, 0 rows imported in images)

Hi Jordan,
did you find some nice clues here? It would be nice to be able to import existing data …

Thanks,
Ronald.

Hi Ronald,

Sorry for the delay. I did investigate what was going on for you and found several further problems, but nothing obvious that would lead to an easy fix.

  1. Besides sequences/nextval(), we also don’t support the bpchar type, which is used in your schema. The users table therefore will never get imported. I think you might be able to get around this by changing bpchar to string everywhere, but I’m not sure if that’ll result in adverse effects for your database.
  2. There’s a bunch of statements at the top of the file that we don’t support either, mostly to do with role management.

I was unable to reproduce the message type 0x5a arrived from server while idle issues you observed, nor the ambiguous result error issue.

I think the trouble here is that this kind of automated ingest comes in the form of ordinary SQL statements, which are vulnerable as always to ambiguous result errors as detailed here: https://www.cockroachlabs.com/docs/stable/transactions.html#error-handling

It’s not clear to me what the best way to get around this problem is, since we’re just executing a giant chunk of SQL statements. In the future, it might be better to import via our upcoming distributed CSV import feature, but that’s not available yet. See https://github.com/cockroachdb/cockroach/pull/16995 for information about that upcoming feature.

Hi thanks Jordan,

so you were able to import the images table?
For me the sequences and missing datatype support are no problem, I can work around that.
So I wil do some more testing to try to import the images table…:thinking: