Обсуждение: strange corruption?

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

On Thu, Dec 27, 2012 at 7:27 AM, John Fabiani <johnf@jfcomputer.com> 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.

There are many reasons this may or may not be working.  Can you create
a self-contained test case that reproduces this issue?

<div class="moz-cite-prefix">On 28/12/12 03:27, John Fabiani wrote:<br /></div><blockquote
cite="mid:50DC5AD0.3040507@jfcomputer.com"type="cite">Hi, <br /> I have the following statement in a function. <br
/><br/>     UPDATE orderseq <br />     SET orderseq_number = (orderseq_number + 1) <br />     WHERE
(orderseq_name='InvcNumber');<br /><br /> All it does is update a single record by incrementing a value (int). <br
/><br/> 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. <br /><br /> Johnf <br /><br /><br
/></blockquote><fontsize="-1">It might he<font size="-1">lp if you give the table definition</font></font>.<br /><br />
Definitelyimportant: is the exact version of PostgreSQL used, and the operating system.<br /><br /><br /> Cheers,<br />
Gavin 

<div class="moz-cite-prefix">On 28/12/12 05:44, John Fabiani wrote:<br /></div><blockquote
cite="mid:50DC7AF9.3070802@jfcomputer.com"type="cite"><div class="moz-cite-prefix">On 12/27/2012 08:21 AM, Gavin Flower
wrote:<br/></div><blockquote cite="mid:50DC758D.9010309@archidevsys.co.nz" type="cite"><div class="moz-cite-prefix">On
28/12/1203:27, John Fabiani wrote:<br /></div><blockquote cite="mid:50DC5AD0.3040507@jfcomputer.com" type="cite">Hi,
<br/> I have the following statement in a function. <br /><br />     UPDATE orderseq <br />     SET orderseq_number =
(orderseq_number+ 1) <br />     WHERE (orderseq_name='InvcNumber'); <br /><br /> All it does is update a single record
byincrementing a value (int). <br /><br /> But it never completes.  This has to be some sort of bug.  Anyone have a
thoughtwhat would cause this to occur.  To my knowledge it was working and does work in other databases. <br /><br />
Johnf<br /><br /><br /></blockquote><font size="-1">It might he<font size="-1">lp if you give the table
definition</font></font>.<br/><br /> Definitely important: is the exact version of PostgreSQL used, and the operating
system.<br/><br /><br /> Cheers,<br /> Gavin </blockquote> 9.1.6 updated 12.22.2012, openSUSE 12.1 64 bit Linux<br
/><br/> CREATE TABLE orderseq<br /> (<br />   orderseq_id integer NOT NULL DEFAULT
nextval(('orderseq_orderseq_id_seq'::text)::regclass),<br/>   orderseq_name text,<br />   orderseq_number integer,<br
/>  orderseq_table text,<br />   orderseq_numcol text,<br />   CONSTRAINT orderseq_pkey PRIMARY KEY (orderseq_id )<br
/>)<br /> WITH (<br />   OIDS=FALSE<br /> );<br /> ALTER TABLE orderseq<br />   OWNER TO admin;<br /> GRANT ALL ON
TABLEorderseq TO admin;<br /> GRANT ALL ON TABLE orderseq TO xtrole;<br /> COMMENT ON TABLE orderseq<br />   IS
'Configurationinformation for common numbering sequences';<br /><br /><br /> Johnf<br /></blockquote><font
size="-1"><br/> I had a vague idea what the problem might be<font size="-1">, but yo<font size="-1">u</font>r ta<font
size="-1">bledefi<font size="-1">ni</font>tion proved I was wrong!  :-)</font></font><br /><br /><br /> This won't sole
yourproblem, but I was won<font size="-1">dering why you don't use a simpler definition like:<br /></font></font><br
/><tt><fontsize="-1"><font size="-1">CREATE TABLE orderseq<br /> (<br />   orderseq_id       S<font
size="-1">E</font>R<fontsize="-1">IAL </font></font></font></tt><font size="-1"><font size="-1"><tt><font
size="-1"><fontsize="-1"><font size="-1">PRIMARY KEY</font></font></font></tt><tt>,</tt><tt><br /></tt><tt>  
orderseq_name    text,</tt><tt><br /></tt><tt>   orderseq_number   integer,</tt><tt><br /></tt><tt>   orderseq_table   
text,</tt><tt><br/></tt><tt>   orderseq_numcol   text</tt><tt><br /></tt><tt> );</tt></font></font><br /><font
size="-1"><fontsize="-1"><br /><font size="-1"><font size="-1">S<font size="-1">E</font>R<font size="-1">IAL
automaticallyattaches the table's own sequence and do<font size="-1">es a </font></font></font></font>DEFAULT
nextval<br/><br /><font size="-1">P</font>RIMARY KEY implies NOT NULL & UNIQUE<br /><br /><font
size="-1">OIDS</font></font></font><fontsize="-1"><font size="-1"><font size="-1">=FALSE is the
default</font></font></font><br/><br /> 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.<br /><br /><br /> Cheers,<br />
Gavin<br/> 

Hi,
Maybe you have triggers on the target table.

27.12.2012, 18:28, "John Fabiani" <johnf@jfcomputer.com>:
> 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
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql