Thread: Ah! pgsql query leads to evil

Ah! pgsql query leads to evil

From
Jamie Lawrence
Date:
OK, my baseline tables are a states table, from commonly available
sources, and cities, from the Census. \d's are:

cal=# \d states
                                       Table "public.states"
   Column   |            Type             |                       Modifiers
------------+-----------------------------+--------------------------------------------------------
 id         | integer                     | not null default nextval('public.states_id_seq'::text)
 name       | text                        |
 abbr       | text                        |
 createdate | timestamp without time zone | default now()
 moddate    | timestamp without time zone |
Indexes: states_pkey primary key btree (id)
Triggers: timestamp_tr

cal=# \d cities
                                       Table "public.cities"
   Column   |            Type             |                       Modifiers
------------+-----------------------------+--------------------------------------------------------
 id         | integer                     | not null default nextval('public.cities_id_seq'::text)
 name       | text                        |
 createdate | timestamp without time zone | default now()
 moddate    | timestamp without time zone |
 states_id  | integer                     |
Indexes: cities_pkey primary key btree (id)
Foreign Key constraints: valid_state FOREIGN KEY (states_id) REFERENCES states(id) ON UPDATE NO ACTION ON DELETE NO
ACTION
Triggers: timestamp_tr

(Timestamps are an ease of use thing - we can purge data if someone
screws up easily. Otherwise, 'id' is defined as SERIAL PRIMARY KEY,
states_id is INT REFERECES states)

I enter a query like so, and get the following errors:

cal=# select * from states, cities;
server sent binary data ("B" message) without prior row description ("T" message)
server sent binary data ("B" message) without prior row description ("T" message)
server sent data ("D" message) without prior row description ("T" message)
server sent binary data ("B" message) without prior row description ("T" message)
server sent data ("D" message) without prior row description ("T" message)
server sent data ("D" message) without prior row description ("T" message)
server sent data ("D" message) without prior row description ("T" message)
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> \.
unexpected response from server; first received character was "a"
lost synchronization with server, resetting connection
Asynchronous NOTIFY '2003-11-26 18:15:20.548647' from backend with pid 30 received.
Asynchronous NOTIFY 'n' from backend with pid 1969386866 received.
Asynchronous NOTIFY '2003-11-26 18:15:20.548647' from backend with pid 30 received.
Asynchronous NOTIFY 't' from backend with pid 1836020325 received.
Asynchronous NOTIFY '' from backend with pid 1986359929 received.


I know that's a pathological query, and I performed it only because
Software Gone Wrong did it, and I was looking to see what happened.
The result just seems really, really wrong, and I'm wondering if anyone has
seen something like that before.

This is PG 7.3.4, on Debian/stable on a DEC box (compiled here,
but we didn't do anything strange to it).

-j

--
Jamie Lawrence                                        jal@jal.org
Prediction is very difficult, especially of the future.
   - Niels Bohr



Re: Ah! _psql_ query leads to evil

From
Jamie Lawrence
Date:
Sorry for the misleading subject. typing pgsql when I mean psql
is an unfortunate muscle memory. Happens to me in the shell, too.

Also, sorry to reply to my own post.

-j


--
Jamie Lawrence                                        jal@jal.org
I'll see your senator, and I'll raise you two judges.



Re: Ah! pgsql query leads to evil

From
Tom Lane
Date:
Jamie Lawrence <postgres@jal.org> writes:
> cal=# select * from states, cities;
> server sent binary data ("B" message) without prior row description ("T" message)

Before 7.4, the client-side libpq library tended to go nuts like this
if the received query result overran available memory.

            regards, tom lane