Rounding issue with PG numeric columns and TCL triggers

[adroege]

Hello,

We are having a weird rounding issue through UniDac when we've got a TCL trigger function attached to a PostgreSQL table with a field defined as numeric (with no precision). The following is the SQL I used to set up a test:

Code: Select all

CREATE TABLE t1( id integer, f1 numeric, f2 numeric(22,4) );

CREATE OR REPLACE FUNCTION bogus_function()
  RETURNS trigger AS
$BODY$BEGIN
  return NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' STABLE STRICT
  COST 100;

CREATE TRIGGER bogustrigger
  BEFORE UPDATE OR DELETE
  ON t1
  FOR EACH ROW
  EXECUTE PROCEDURE bogus_function();  

CREATE OR REPLACE FUNCTION bogus_function2()
  RETURNS trigger AS
$BODY$
  return [array get NEW]
$BODY$
  LANGUAGE 'pltcl' VOLATILE SECURITY DEFINER
  COST 100;

CREATE TRIGGER bogustrigger2
  BEFORE UPDATE OR DELETE
  ON t1
  FOR EACH ROW
  EXECUTE PROCEDURE bogus_function2();

Using this through a small project in Delphi using a TUniTable and a grid, if I edit a record in this table and enter 1.45 into f1 and 1.45 into f2, f1 rounds to 1 and f2 saves 1.45 properly. If I disable the TCL trigger (but leave the plpgsql trigger enabled) the issue goes away. If I make these edits through PGAdmin with both triggers enabled, I don't have the rounding issue. Both fields properly save 1.45.

Following are the properties that I've changed from default for the TUniTable

Code: Select all

    
Options.DefaultValues := True;
Options.UpdateAllFields := False;
Options.StrictUpdate := False;
LockMode:= lmNone;
RefreshOptions:= [ roAfterUpdate ];

I set up the DBMonitor and ran the SQLs that it logged straight through PGAdmin and they seemed to work fine.

We are using PostgreSQL 8.3.5, UniDac 2.7 and Delphi 7.

Any ideas what may be going on here?

Thanks

[Plash]

You can set UnpreparedExecute=True in SpecificOptions of TUniTable. In this case the query is executed like in PgAdmin, and the value is not rounded.

[adroege]

That seems to have fixed this particular issue, however I have some questions:

1) What exactly does UnpreparedExecute do? What am I giving up by setting this to True?

2) Why is this only an issue with TCL triggers and not with plpgsql triggers? I would like to know what to look out for in case there may be issues in other areas of our code

Thanks

[Plash]

1. When you set UnpreparedExecute to True, real parameters are not used. Parameter values are embedded into SQL text. In this mode the query is executed directly without preparation.

2. I don't know why the problem with TCL trigger occurs. It is not related to PgDAC. Maybe you should ask you question on a forum related to PostgreSQL server.