Re: Pre-set Hint bits/VACUUM FREEZE on data load..?

On 24.03.2011 23:08, Stephen Frost wrote:
> In a discussion which came up at PgEast, I questioned if it'd be
> possible to set the 'all visible' hint bit and give the tuples the
> frozen XID when loading data into a table which was created in the
> same transaction.
>
> The idea being that no other transactions could see the table (in any
> important way anyway.. couldn't SELECT from it, for example) since it
> was created in the same transaction that the data was loaded. This
> would avoid having to rewrite the table to set the hint bits and to
> set the tuples as frozen after the data load.

The problem is that you still need to track which queries within the
transaction can see the tuples. For example:

BEGIN;
CREATE TABLE foo ...
INSERT INTO foo VALUES (1);
DECLARE foocur CURSOR FOR SELECT * FROM foo;
FETCH foocur;
INSERT INTO foo VALUES (2);
FETCH foocur;

The cursor was opened before the 2nd tuple was inserted, so it should
not be returned by the cursor.

There's also corner cases like triggers that query the same table, and
self-joins.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com