Skip site navigation (1) Skip section navigation (2)

SQL update statements are failing if submitted in sequence on same connection

From: "Panon, Paul-Andre" <Paul-AndrePanon(at)SierraSystems(dot)com>
To: "'pgsql-bugs(at)postgresql(dot)org'" <pgsql-bugs(at)postgresql(dot)org>
Subject: SQL update statements are failing if submitted in sequence on same connection
Date: 2001-05-26 01:06:48
Message-ID: 2417BD0B3A2D5A4086512AD8BDDB1D4E010A0A61@svvan2000.sierrasys.com (view raw or flat)
Thread:
Lists: pgsql-bugs
============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name               :	Paul-Andre Panon
Your email address      : Paul-AndrePanon(at)sierrasystems(dot)com


System Configuration
---------------------
  Architecture (example: Intel Pentium)         : Intel Pentium Pro

  Operating System (example: Linux 2.0.26 ELF)  :Mandrake Linux 7.2 Kernel
2.2.17-21mdksmp

  PostgreSQL version (example: PostgreSQL-7.1.1):   PostgreSQL-7.1, 7.1.1,
7.1.2

  Compiler used (example:  gcc 2.95.2)          :gcc version 2.95.3 19991030
(prerelease)

  I use rpms rebuilt from the source RPMs on the ftp site. For 7.1.2, I
modified the the 7.1.1 spec and support files to use the 7.1.2 tar files,
but I have the same problem with the stock 7.1 and 7.1.1 rpms.

Please enter a FULL description of your problem:
------------------------------------------------

SQL update statements are failing, possibly in the query planner. A copy of
the -d3 postgres log is attached. The error returned by psql when submitting
this query is
ERROR:  Relation 2699531655 does not exist
The third update statement fails if executed on the same connection as the
first two, but succeeds if it is executed on a different connection.
  
I am using a custom GUID/UUID data type extension similar to the MS SQL
Server uniqueidentifier data type. I used the varbits type in the contrib
directory as a template, as well as the FreeDCE library to generate new
UUIDs. I would be happy to contribute this code if anybody is interested.
With the new ODBC driver working towards ODBC 3.0 level support, there may
be more interest in having GUID support in PostgreSQL. Although I can't rule
out that the UUID data type may be at fault, it does work correctly under
7.0.2 . The included uniqid.tar.gz should be extracted into the contrib
directory and compiled and installed from there.

I have included a database dump of a pared down version of the database, as
well as the statements that cause the problem.
If you run the three statements in sequence on the same connection you get
the crash, but if you run the third update by itself on a new connection it
works correctly. I have massaged the database dump to include the
uniqueidentifier type definitions assuming the shared library  is placed in
/usr/lib/pgsql/modules. If you place the shared library in a different
location, you will  need to change the reference in the dump. Alternately,
you can run the uniqid.sql generated and installed by the make process
before your restore the database dump; the restore will then provide some
error messages regarding the type redefinitions but they can be ignored.

----------------------------------------------------------------------


Perhaps this may be related to another bug that Tom Lane describes as:
The direct cause of the problem is that EvalPlanQual isn't completely
initializing the estate that it sets up for re-evaluating the plan. In
particular it's not filling in es_result_relations and
es_num_result_relations, which need to be set up if the top plan node is an
Append. 
since psql refers to a vary large relation oid in its error message? It
looks like the query is crashing after the query parsing is completed. -d4
doesn't seem to provide additional useful information regarding the Query
Processing and where it is breaking. If there are any additional flags at
compile or run time that I can set to provide more useful information,
please let me know.

---------------------------------------------------------------------


 <<bugdb.load>>  <<errorsrc.sql>>  <<postgresql.log>> 

Paul-Andre Panon
Sierra Systems
1177 West Hastings Street, Suite 2500
Vancouver, BC  V6E 2K3
Main: 604.688.1371
Fax:  604.688.6482
www.SierraSystems.com




Attachment: postgresql.log
Description: application/octet-stream (86.6 KB)
Attachment: errorsrc.sql
Description: application/octet-stream (10.0 KB)
Attachment: bugdb.load
Description: application/octet-stream (48.2 KB)

pgsql-bugs by date

Next:From: Justin CliftDate: 2001-05-26 01:13:45
Subject: Re: [GENERAL] Compilation fails --with-ssl on Solaris 8
Previous:From: Tom LaneDate: 2001-05-25 22:38:26
Subject: Re: Optimizer in 7.1.1 worse thatn 7.0.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group