Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1ToQTg-0007YG-7c for pgsql-sql@arkaria.postgresql.org; Fri, 28 Dec 2012 03:19:20 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1ToQTf-0007vs-KL for pgsql-sql@arkaria.postgresql.org; Fri, 28 Dec 2012 03:19:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1ToJwX-0007WE-8B for pgsql-sql@postgresql.org; Thu, 27 Dec 2012 20:20:41 +0000 Received: from mbx.knossos.net.nz ([202.160.48.10]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1ToJwO-0004Jk-GA for pgsql-sql@postgresql.org; Thu, 27 Dec 2012 20:20:40 +0000 Received: from [10.1.1.3] (60-234-150-59.bitstream.orcon.net.nz [60.234.150.59]) (authenticated bits=0) by mbx.knossos.net.nz (8.14.4/8.14.4) with ESMTP id qBRKKOfg030890 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT); Fri, 28 Dec 2012 09:20:24 +1300 Message-ID: <50DCAD89.9030103@archidevsys.co.nz> Date: Fri, 28 Dec 2012 09:20:25 +1300 From: Gavin Flower Organization: ArchiDevSys User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/17.0 Thunderbird/17.0 MIME-Version: 1.0 To: johnf@jfcomputer.com CC: "'PostgreSQL (SQL)'" Subject: Re: strange corruption? References: <50DC5AD0.3040507@jfcomputer.com> <50DC758D.9010309@archidevsys.co.nz> <50DC7AF9.3070802@jfcomputer.com> In-Reply-To: <50DC7AF9.3070802@jfcomputer.com> Content-Type: multipart/alternative; boundary="------------090103000608020307090702" X-Pg-Spam-Score: -1.6 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------090103000608020307090702 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 28/12/12 05:44, John Fabiani wrote: > On 12/27/2012 08:21 AM, Gavin Flower wrote: >> On 28/12/12 03:27, John Fabiani wrote: >>> Hi, >>> I have the following statement in a function. >>> >>> UPDATE orderseq >>> SET orderseq_number = (orderseq_number + 1) >>> WHERE (orderseq_name='InvcNumber'); >>> >>> All it does is update a single record by incrementing a value (int). >>> >>> But it never completes. This has to be some sort of bug. Anyone >>> have a thought what would cause this to occur. To my knowledge it >>> was working and does work in other databases. >>> >>> Johnf >>> >>> >> It might help if you give the table definition. >> >> Definitely important: is the exact version of PostgreSQL used, and >> the operating system. >> >> >> Cheers, >> Gavin > 9.1.6 updated 12.22.2012, openSUSE 12.1 64 bit Linux > > CREATE TABLE orderseq > ( > orderseq_id integer NOT NULL DEFAULT > nextval(('orderseq_orderseq_id_seq'::text)::regclass), > orderseq_name text, > orderseq_number integer, > orderseq_table text, > orderseq_numcol text, > CONSTRAINT orderseq_pkey PRIMARY KEY (orderseq_id ) > ) > WITH ( > OIDS=FALSE > ); > ALTER TABLE orderseq > OWNER TO admin; > GRANT ALL ON TABLE orderseq TO admin; > GRANT ALL ON TABLE orderseq TO xtrole; > COMMENT ON TABLE orderseq > IS 'Configuration information for common numbering sequences'; > > > Johnf I had a vague idea what the problem might be, but your table definition proved I was wrong! :-) This won't sole your problem, but I was wondering why you don't use a simpler definition like: CREATE TABLE orderseq ( orderseq_id SERIAL PRIMARY KEY, orderseq_name text, orderseq_number integer, orderseq_table text, orderseq_numcol text ); SERIAL automatically attaches the table's own sequence and does a DEFAULT nextval PRIMARY KEY implies NOT NULL & UNIQUE OIDS=FALSE is the default My personal preference is just to use the name 'id' for the tables own primary key, and only prepend the table name when it is foreign key - makes them stand out more. Cheers, Gavin --------------090103000608020307090702 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 28/12/12 05:44, John Fabiani wrote:
On 12/27/2012 08:21 AM, Gavin Flower wrote:
On 28/12/12 03:27, John Fabiani wrote:
Hi,
I have the following statement in a function.

    UPDATE orderseq
    SET orderseq_number = (orderseq_number + 1)
    WHERE (orderseq_name='InvcNumber');

All it does is update a single record by incrementing a value (int).

But it never completes.  This has to be some sort of bug.  Anyone have a thought what would cause this to occur.  To my knowledge it was working and does work in other databases.

Johnf


It might help if you give the table definition.

Definitely important: is the exact version of PostgreSQL used, and the operating system.


Cheers,
Gavin
9.1.6 updated 12.22.2012, openSUSE 12.1 64 bit Linux

CREATE TABLE orderseq
(
  orderseq_id integer NOT NULL DEFAULT nextval(('orderseq_orderseq_id_seq'::text)::regclass),
  orderseq_name text,
  orderseq_number integer,
  orderseq_table text,
  orderseq_numcol text,
  CONSTRAINT orderseq_pkey PRIMARY KEY (orderseq_id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE orderseq
  OWNER TO admin;
GRANT ALL ON TABLE orderseq TO admin;
GRANT ALL ON TABLE orderseq TO xtrole;
COMMENT ON TABLE orderseq
  IS 'Configuration information for common numbering sequences';


Johnf

I had a vague idea what the problem might be, but your table definition proved I was wrong!  :-)


This won't sole your problem, but I was wondering why you don't use a simpler definition like:

CREATE TABLE orderseq
(
  orderseq_id       SERIAL PRIMARY KEY,
   orderseq_name     text,
   orderseq_number   integer,
   orderseq_table    text,
   orderseq_numcol   text
 );

SERIAL automatically attaches the table's own sequence and does a DEFAULT nextval

PRIMARY KEY implies NOT NULL & UNIQUE

OIDS=FALSE is the default

My personal preference is just to use the name 'id' for the tables own primary key, and only prepend the table name when it is foreign key - makes them stand out more.


Cheers,
Gavin
--------------090103000608020307090702--