I have noticed that all SELECT queries alone in a transaction also
update pg_stat_database (I know it's just a view that calls
pg_stat_get_db_xact_commit()).

Does that mean that SELECTs by themselves require a disk write? If so,
is that a synchronous disk write? Is it due to something else entirely
(like autovacuum or the statistics collector)?

Also, is there a way to retrieve the current transaction id?

Thanks,
Jeff Davis


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Jeff Davis wrote:
> I have noticed that all SELECT queries alone in a transaction also
> update pg_stat_database (I know it's just a view that calls
> pg_stat_get_db_xact_commit()).

The stuff in the stats collector is not stored on disk. Or, rather, it
is, but not in the same way as regular tables; in particular, it's not
fsynced.

> Also, is there a way to retrieve the current transaction id?

Some people like to write a C function calling GetTopTransactionId() to
get it. If you want it for a quick experiment, you can create a temp
table and examine pg_class.xmin for that table.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster