Strange behavior in protocol level

[syagrius]

Hello,

I think I found a bug when I try to execute a query with:

• - an UPDATE
  - a SELECT (to call a function)
  - and an INSERT with a returning value

Something like this:

Code: Select all

INSERT INTO table1....;
SELECT a_sample_function();
UPDATE table2... RETURNING afield;

In this situation pgDac execute the request several times.
And I have checked this with a TCPDUMP or Wireshark.
I've got the same issue with postgresql 8.x, 9.x under Windows and Linux. Under Delphi7 and Delphi2010.
(Of course the query is working with other library or pgAdmin).

How to reproduce:
(Dont be afraid you can download a sample project below)
Your can create two tables and a dummy function like this:

Code: Select all

CREATE OR REPLACE FUNCTION sms_inccredit(laciacliid integer, lcredit integer)
  RETURNS void AS
$BODY$
BEGIN
    -- no code needed
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;

CREATE TABLE smscheck
(
  smsid integer NOT NULL,
  aciacliid integer,
  creditcount integer DEFAULT 1,
  CONSTRAINT pk_smscheck PRIMARY KEY (smsid)
 );

CREATE TABLE smsaccount
(
  aciacliid integer NOT NULL,
  credit integer DEFAULT 0,
  CONSTRAINT pk_smsaccount PRIMARY KEY (aciacliid)
);

Drop a TPgQuery and try to execute:

Code: Select all

INSERT INTO smscheck(smsid,creditcount) VALUES(1,1);
SELECT sms_inccredit(123,1);
UPDATE smsaccount SET credit=credit-1 WHERE aciacliid=123 RETURNING credit;

PgDac will raise an exception:
duplicate key value violates unique constraint pk_smscheck.

If you sniff port 5432 you will see that pgDac send the request twice.
And an exception is raised because of the primary key.

Here is the dump.

Code: Select all

Packet #1 (pgDac send the request)
0x0000   51 49 4E 53 45 52 54 20-49 4E 54 4F 20 73 6D 73   QINSERT INTO sms
0x0010   63 68 65 63 6B 28 73 6D-73 69 64 2C 63 72 65 64   check(smsid,cred
0x0020   69 74 63 6F 75 6E 74 29-20 56 41 4C 55 45 53 28   itcount) VALUES(
0x0030   31 2C 31 29 3B 0D 0A 53-45 4C 45 43 54 20 73 6D   1,1);..SELECT sm
0x0040   73 5F 69 6E 63 63 72 65-64 69 74 28 31 32 33 2C   s_inccredit(123,
0x0050   31 29 3B 0D 0A 55 50 44-41 54 45 20 73 6D 73 61   1);..UPDATE smsa
0x0060   63 63 6F 75 6E 74 20 53-45 54 20 63 72 65 64 69   ccount SET credi
0x0070   74 3D 63 72 65 64 69 74-2D 31 20 57 48 45 52 45   t=credit-1 WHERE
0x0080   20 61 63 69 61 63 6C 69-69 64 3D 31 32 33 20 52    aciacliid=123 R
0x0090   45 54 55 52 4E 49 4E 47-20 63 72 65 64 69 74 3B   ETURNING credit;
0x00A0   00                                                .

Packet #2 (Postgresql)
0x0000   43 49 4E 53 45 52 54 20-30 20 31 00 50 62 6C 61   CINSERT 0 1.Pbla
0x0010   6E 6B 00 54 00 01 73 6D-73 5F 69 6E 63 63 72 65   nk.T..sms_inccre
0x0020   64 69 74 00 00 00 08 E6-00 04 FF FF FF FF 44 80   dit....æ..ÿÿÿÿD€
0x0030   00 00 00 04 43 53 45 4C-45 43 54 00 50 62 6C 61   ....CSELECT.Pbla
0x0040   6E 6B 00 54 00 01 63 72-65 64 69 74 00 00 00 00   nk.T..credit....
0x0050   17 00 04 FF FF FF FF 44-80 00 00 00 05 39 43 55   ...ÿÿÿÿD€....9CU
0x0060   50 44 41 54 45 20 31 00-5A                        PDATE 1.Z

Packet #3 (pgDac send the request again)
0x0000   51 49 4E 53 45 52 54 20-49 4E 54 4F 20 73 6D 73   QINSERT INTO sms
0x0010   63 68 65 63 6B 28 73 6D-73 69 64 2C 63 72 65 64   check(smsid,cred
0x0020   69 74 63 6F 75 6E 74 29-20 56 41 4C 55 45 53 28   itcount) VALUES(
0x0030   31 2C 31 29 3B 0D 0A 53-45 4C 45 43 54 20 73 6D   1,1);..SELECT sm
0x0040   73 5F 69 6E 63 63 72 65-64 69 74 28 31 32 33 2C   s_inccredit(123,
0x0050   31 29 3B 0D 0A 55 50 44-41 54 45 20 73 6D 73 61   1);..UPDATE smsa
0x0060   63 63 6F 75 6E 74 20 53-45 54 20 63 72 65 64 69   ccount SET credi
0x0070   74 3D 63 72 65 64 69 74-2D 31 20 57 48 45 52 45   t=credit-1 WHERE
0x0080   20 61 63 69 61 63 6C 69-69 64 3D 31 32 33 20 52    aciacliid=123 R
0x0090   45 54 55 52 4E 49 4E 47-20 63 72 65 64 69 74 3B   ETURNING credit;
0x00A0   00                                                .

Packet #4 (answer)
0x0000   45 45 52 52 4F 52 3A 20-20 64 75 70 6C 69 63 61   EERROR:  duplica
0x0010   74 65 20 6B 65 79 20 76-61 6C 75 65 20 76 69 6F   te key value vio
0x0020   6C 61 74 65 73 20 75 6E-69 71 75 65 20 63 6F 6E   lates unique con
0x0030   73 74 72 61 69 6E 74 20-22 70 6B 5F 73 6D 73 63   straint "pk_smsc
0x0040   68 65 63 6B 22 0A 00                              heck"..

Packet #5
0x0000   5A                                                Z

If you remove the RETURNING clause from the UPDATE: everything is working.
If you remove the line containing the SELECT statement: everything is working.

I wrote a sample project to reproduce this issue:
http://demo.ovh.com/download/d9dcf360471deae0a036c888b6aa8356/bugproc.zip

Sorry for my English or clumsiness
Best Regards,
Syagrius

[AlexP]

Hello,

Thank you for the information.
We have reproduced the problem.
We will notify you as soon as we have any results.

[syagrius]

Hello,

Thank you for the information.
We have reproduced the problem.
We will notify you as soon as we have any results.

Hello AlexP
Do you have any suggestions about this issue?

I still cannot make an insert+update+function call in the same transaction.

[AlexP]

Hello,

We are still working on this issue.

As a temporary solution, you can call queries one by one:

Code: Select all

  PgQuery1.SQL.Text := 'INSERT INTO smscheck(smsid,creditcount) VALUES(1,1);';
  PgQuery1.ExecSQL;
  PgQuery1.SQL.Text := 'SELECT sms_inccredit(123,1);';
  PgQuery1.ExecSQL;
  PgQuery1.SQL.Text := 'UPDATE smsaccount SET credit=credit-1 WHERE aciacliid=123 RETURNING credit;';
  PgQuery1.ExecSQL;