Bughunters,
I am testing the 8.2beta1 server with a pg_dump/restore from a debian
production sarge-PG74 instance.
When the dump is restored to a test PG-8.1.3 my application works
nicely (have not upgraded the production server though).
When the same dump is restored to a PG-8.2beta1 some queries succeed
and others fail. The server *restarts* as can be seen from the server
logfile below.
The database is accessed through iBatis version 1.3.1 --> jakarta DBCP 1.2.1
OS: SuSe 8.0
JDBC driver used: postgresql-8.2dev-503.jdbc3.jar' downloaded from
'jdbc.postgresql.org'.
JVM: java full version "1.5.0-b64"
PG-8.2beta1 built from source with this configure command:
'$ ./configure --prefix=3D/srv/pg-8.2.0.beta1 --with-zlib' (from config.lo=
g)
Version from version():
------------------------------
Welcome to psql 8.2beta1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
XXXX=3D> select version();
LOG: statement: BEGIN
LOG: statement: select version();
version
-------------------------------------------------------------------------
PostgreSQL 8.2beta1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
(1 row)
Locales:
-----------
Locale used in initdb command: LATIN1
Locale used in createdb command: LATIN1
Locale used in production db: LATIN1
Table description:
-----------------------
Table "booking.booking"
Column | Type |
Modifiers
--------------------+--------------------------+--------------------------=
-----------------------------------------
id | integer | not null default
nextval(('booking.booking_seq'::text)::regclass)
customer_id | integer | not null
type | integer | not null
fromtime | timestamp with time zone | not null
totime | timestamp with time zone | not null
contact | character varying(255) |
comment | character varying(2000) |
driver | character varying(255) |
last_updated | timestamp with time zone | not null
sms_last_sent | timestamp with time zone |
location | character varying(255) |
sms_sent | boolean | not null
deleted | boolean | not null
regular | boolean | not null
express_booking_id | integer |
Indexes:
"booking_pkey" PRIMARY KEY, btree (id)
"booking_customer_id_idx" btree (customer_id)
"booking_express_booking_id_idx" btree (express_booking_id)
"booking_fromtime_idx" btree (fromtime)
"booking_totime_idx" btree (totime)
Foreign-key constraints:
"fk_booking_customer_id" FOREIGN KEY (customer_id) REFERENCES customer=
(id)
"fk_booking_pickup_type" FOREIGN KEY ("type") REFERENCES enum_value(id)
Triggers:
tr_after_update AFTER UPDATE ON booking.booking FOR EACH ROW
EXECUTE PROCEDURE tr_maintain_pickup_status()
Query as logged on client:
---------------------------------
DEBUG 2006-10-10 13:31:56,481 PreparedStatement - {pstm-101012}
PreparedStatement: select distinct id, customer_id,
express_booking_id, type, fromtime, totime, contact, comment, driver,
sms_sent, regular, deleted, last_updated, sms_last_sent, location
from ( select public.customer.name, booking.booking.id as id,
customer_id, express_booking_id, type, fromtime, totime, contact,
comment, driver, sms_sent, regular, deleted, last_updated,
sms_last_sent, location from booking.booking, public.customer
WHERE booking.booking.customer_id =3D public.customer.id
AND fromtime >=3D ? AND totime
<=3D ? AND customer.zip < '5000'
UNION select public.customer.name,
booking.booking.id as id, customer_id, express_booking_id, type,
fromtime, totime, contact, comment, driver, sms_sent, regular,
deleted, last_updated, sms_last_sent, location from
booking.booking, public.customer WHERE
booking.booking.customer_id =3D public.customer.id AND
fromtime >=3D ? AND totime <=3D ?
AND customer.zip >=3D '5000'
) as subselect
DEBUG 2006-10-10 13:31:56,481 PreparedStatement - {pstm-101012}
Parameters: [2006-10-10 00:00:00.0, 2006-10-10 23:59:59.0, 2006-10-10
00:00:00.0, 2006-10-10 23:59:59.0]
DEBUG 2006-10-10 13:31:56,482 PreparedStatement - {pstm-101012}
Types: [java.sql.Timestamp, java.sql.Timestamp, java.sql.Timestamp,
java.sql.Timestamp]
Query as logged on server:
-----------------------------------
This log snip is from the working 8.1.3 instance beacuse the 8.2beta1
does not log the query before crash.
<snip>
LOG: statement: PREPARE <unnamed> AS SET SESSION CHARACTERISTICS AS
TRANSACTION ISOLATION LEVEL SERIALIZABLE
LOG: statement: <BIND>
LOG: statement: EXECUTE <unnamed> [PREPARE: SET SESSION
CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE]
LOG: statement: <BIND>
LOG: statement: EXECUTE <unnamed> [PREPARE: BEGIN]
LOG: statement: PREPARE <unnamed> AS
select distinct id, customer_id, express_booking_id,
type, fromtime, totime, contact, comment, driver, sms_sent, regular,
deleted, last_updated, sms_last_sent, location
from
(
select public.customer.name, booking.booking.id as id,
customer_id, express_booking_id, type, fromtime, totime, contact,
comment, driver, sms_sent, regular, deleted, last_updated,
sms_last_sent, location
from booking.booking, public.customer
WHERE
booking.booking.customer_id =3D public.customer.id
AND
fromtime >=3D $1
AND
totime <=3D $2
AND
customer.zip < '5000'
UNION
select public.customer.name, booking.booking.id as id,
customer_id, express_booking_id, type, fromtime, totime, contact,
comment, driver, sms_sent, regular, deleted, last_updated,
sms_last_sent, location
from booking.booking, public.customer
WHERE
booking.booking.customer_id =3D public.customer.id
AND
fromtime >=3D $3
AND
totime <=3D $4
AND
customer.zip >=3D '5000'
) as subselect
LOG: statement: <BIND>
LOG: statement: EXECUTE <unnamed> [PREPARE:
select distinct id, customer_id, express_booking_id,
type, fromtime, totime, contact, comment, driver, sms_sent, regular,
deleted, last_updated, sms_last_sent, location
from
(
select public.customer.name, booking.booking.id as id,
customer_id, express_booking_id, type, fromtime, totime, contact,
comment, driver, sms_sent, regular, deleted, last_updated,
sms_last_sent, location
from booking.booking, public.customer
WHERE
booking.booking.customer_id =3D public.customer.id
AND
fromtime >=3D $1
AND
totime <=3D $2
AND
customer.zip < '5000'
UNION
select public.customer.name, booking.booking.id as id,
customer_id, express_booking_id, type, fromtime, totime, contact,
comment, driver, sms_sent, regular, deleted, last_updated,
sms_last_sent, location
from booking.booking, public.customer
WHERE
booking.booking.customer_id =3D public.customer.id
AND
fromtime >=3D $3
AND
totime <=3D $4
AND
customer.zip >=3D '5000'
) as subselect
]
LOG: statement: <BIND>
LOG: statement: EXECUTE <unnamed> [PREPARE: ROLLBACK]
</snip>
It seems the querys is sent twice, must look into that later.
Same query in psql:
--------------------------
The query is successfull in psql and returns 63 rows
select distinct id, customer_id, express_booking_id, type,
fromtime, totime, contact, comment, driver, sms_sent, regular,
deleted, last_updated, sms_last_sent, location
from
(
select public.customer.name, booking.booking.id as id,
customer_id, express_booking_id, type, fromtime, totime, contact,
comment, driver, sms_sent, regular, deleted, last_updated,
sms_last_sent, location
from booking.booking, public.customer
WHERE
booking.booking.customer_id =3D public.customer.id
AND
fromtime >=3D '2006-10-10 00:00:00'
AND
totime <=3D '2006-10-10 23:59:59'
AND
customer.zip < '5000'
UNION
select public.customer.name, booking.booking.id as id,
customer_id, express_booking_id, type, fromtime, totime, contact,
comment, driver, sms_sent, regular, deleted, last_updated,
sms_last_sent, location
from booking.booking, public.customer
WHERE
booking.booking.customer_id =3D public.customer.id
AND
fromtime >=3D '2006-10-10 00:00:00'
AND
totime <=3D '2006-10-10 23:59:59'
AND
customer.zip >=3D '5000'
) as subselect
The error in the client log is:
--------------------------------------
Caused by: java.io.EOFException
at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:258)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryEx=
ecutorImpl.java:1164)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorI=
mpl.java:190)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdb=
c2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(Ab=
stractJdbc2Statement.java:354)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(Abstra=
ctJdbc2Statement.java:258)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuer=
y(DelegatingPreparedStatement.java:92)
at sun.reflect.GeneratedMethodAccessor71.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMetho=
dAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at com.ibatis.db.jdbc.logging.PreparedStatementLogProxy.invoke(Unk=
nown
Source)
at $Proxy1.executeQuery(Unknown Source)
... 56 more
The error in server log is:
--------------------------------
LOG: execute <unnamed>: SET SESSION CHARACTERISTICS AS TRANSACTION
ISOLATION LEVEL SERIALIZABLE
LOG: execute S_1: BEGIN
LOG: server process (PID 23573) was terminated by signal 11
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll
back the current transaction and exit, because another server process
exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database
and repeat your command.
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted at 2006-10-10 13:42:34 CEST
LOG: checkpoint record is at 0/5240B8CC
LOG: redo record is at 0/5240B8CC; undo record is at 0/0; shutdown TRUE
LOG: next transaction ID: 0/143146; next OID: 6644019
LOG: next MultiXactId: 1; next MultiXactOffset: 0
LOG: database system was not properly shut down; automatic recovery
in progress
LOG: connection received: host=3D::ffff:127.0.0.1 port=3D33904
FATAL: the database system is starting up
LOG: redo starts at 0/5240B914
LOG: record with zero length at 0/5240F9FC
LOG: redo done at 0/5240F9D4
LOG: database system is ready
LOG: transaction ID wrap limit is 2147484172, limited by database "postgr=
es"
The 8.2beta1 server does not log the query before it crashes
Feel free to ask more information if this bugreport is useful to you.
Thank you for a great database !
Regards
Per Jensen
--=20
--
D=E5rlige volt er noget skidt !