[Postgres-xl-general] INSERT w/ incorrect # of rows

Aaron Jackson ajackson at revionics.com
Mon Nov 24 12:09:54 PST 2014


I'm not quite sure how to describe the problem I've encountered, but I'd like to see if anyone else has seen anything like this.  I have a table defined as follows:

CREATE TABLE trash._tmpProdnodes (NodeId int) DISTRIBUTE BY REPLICATION;

I only do this once and subsequent runs simply truncate the table.  A little later in my function, I do the following ...

INSERT INTO trash._tmpProdnodes (NodeId) (
    SELECT DISTINCT(SomeNodeId) as NodeId
      FROM TableA
     INNER JOIN TableB ON TableA.SomeNodeId = TableB.AncestorNodeId
     INNER JOIN TableC ON ...
     INNER JOIN TableD ON ...
     WHERE TableA.UserId = _pUserId
);

Immediately after this, I have the procedure raise info about the number of values in trash._tmpProdnodes as follows:

RAISE INFO 'items in tmpProdnodes: %', (select count(*) from _tmpProdnodes);
RAISE INFO 'items in query: %', (... query from above ...);

It outputs "items in tmpProdnodes: 0" and "items in query: 20" respectively.  I wondered if it was possible that I was looking at data from a table that was lingering on the session, so I queried the value in a separate window.  Sure enough tmpProdnodes has 20 rows in it.  But within the scope of the executing function the number of rows is zero.  Any thoughts?  Other inserts seem to have worked just fine.

Aaron
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/pipermail/postgres-xl-general-postgres-xl.org/attachments/20141124/b6141a0b/attachment.htm>


More information about the postgres-xl-general mailing list