Visibility of Data Changes

16.3. Visibility of Data Changes

PostgreSQL data changes visibility rule: during a query execution, data changes made by the query itself (via SQL-function, SPI-function, triggers) are invisible to the query scan. For example, in query

INSERT INTO a SELECT * FROM a;

tuples inserted are invisible for SELECT scan. In effect, this duplicates the database table within itself (subject to unique index rules, of course) without recursing.

But keep in mind this notice about visibility in the SPI documentation:

Changes made by query Q are visible by queries that are started after query Q, no matter whether they are started inside Q (during the execution of Q) or after Q is done.

This is true for triggers as well so, though a tuple being inserted (tg_trigtuple) is not visible to queries in a BEFORE trigger, this tuple (just inserted) is visible to queries in an AFTER trigger, and to queries in BEFORE/AFTER triggers fired after this!

Home
Online Resources
General
Beginner Tutorials
MySQL
PostgreSQL
Oracle
mSQL
Microsoft SQL
Contact Us
Random quote of the moment:
The co-locator cannot verify the frame-relay gateway to the ISDN server.
 
http://www.sql.org/
 
Proudly designed and hosted by OmegaSphere, providers of all your Web Hosting, SSL Certificate, and Domain Name needs!