Re: PostgreSQL design question

Tom Lane wrote:

Jeffrey Tenny <jeffrey.tenny@comcast.net> writes: 

To determine the cache coherency of my java memory cache of database 
table content,
I maintain an "update table" for all tables to be cached.
Whenever a transaction updates a table that is cached, it must also 
update the 'update table'.   

Hm.  I do not actually see what it buys you to store n_rows in the
update table; seems like the update time is the only thing that is
useful.  (With concurrent inserts under MVCC, n_rows is in the eye
of the beholder anyway, so if your caching code *is* relying on it
then it is wrong.) 

Since many of my tables are append-only, the logic is like this:

I start the transaction and query n_row for append-only tables.  If I have fewer rows in my cache,
I know I need to read the rows inserted after n_rows (basically those records whose primary key is > n_rows). 
I do this based on primary key and not update time, since I don't
want to store update times in the append-only table rows in order to know which were added 'since' a given time.

If someone updates the table that's all well and good, I'm in SERIALIZABLE state anyway,
so nothing about my view of the database will see any updates since I queried the update table.

Or am I still missing the point?

However, getting rid of n_rows doesn't eliminate the fundamental problem
that rows of the update table are serialization bottlenecks.

I wasn't trying to get rid of n_rows, but yes, the table represents a bottleneck.

Recognizing that update_time is all you can usefully propagate anyway,
consider this design:

* Get rid of primary key --- there are allowed to be multiple rows in
the update table for any given data table.

* Updating transactions insert (not update) a row identifying the table
they changed and giving their update_time.

* Caching transactions look at the max update_time for each table they
are interested in.  This is cheap to do efficiently with the right query
and index --- see past discussions about optimizing max() operations.
(Hint: you do *not* write max().)

Yup, been there, done that.
And I can logically max() n_rows (unless I see the light as to why that's worthless, I'm still missing that).

* Periodically, a maintenance process deletes all but the newest row for
each table, and then does a VACUUM of the update table.

This avoids serializing on any individual row of the update table.
The maintenance process doesn't block anything either (it's okay if
it happens to leave more than one valid row for a particular data
table on any given run).

Couldn't I take it a step further and simply delete all existing rows for the table (as the transaction sees it)
and insert a new row, every time I update the update-table?  Or is that going to fragment the database somehow?

However, this idea has a serious problem: it does not quite work because
transactions may not commit in the same order they start.  Consider this
scenario:

Transaction A starts

A inserts some data
		Transaction B starts
		B inserts some data
		B inserts its update_time
		B commits
						C inspects max(update_time)
						C updates its cache

A inserts its update_time

A commits
						C inspects max(update_time)
						C updates its cache?? 

Unless C is two transactions, the update table is inspected only once at the start of each transaction,
then updated at the end of the transaction if necessary.

Since I'm using SERIALIZABLE transaction isolation level, I should see B's commit, but not A's.
It sounds like you're considering the READ COMMITTED state of things, which is very interesting
because I'd like to be there for performance reasons, but right now I'm using SERIALIZABLE.

If update_time is the transaction's start time (now()), then C will in
fact fail to update its cache for A's updates, because it will still see
max(update_time) as being B's value.  A's update_time is smaller than B's. 

As long as C has a transaction coherent snapshot of all tables as they existed when C first inspected
the update table, which I believe (falsely?) is guaranteed by SERIALIZABLE transaction semantics,
A's updates don't matter.  My database view, and my memory cache view for C will all reflect a consistent data set.
(That's trickier than it sounds, since I share data in my memory cache across threads, and had to implement my
own memory cache transaction system to provide the right semantics).

I'm updating the row with  CURRENT_TIMESTAMP(6) in all cases.

But perhaps you've illustrated a bug even with my assumptions about SERIALIZABLE transactions.
C  could set the timestamp of the row to a time that postdates A's even though C commits first,
depending on each transactions delays between update and commit time.

But if this happens with serializable semantics, A would be rolled back because of a single row update conflict.

Using the idea of multiple rows per table, then A would have a row with an earlier timestamp than C's even though
it commits first.  I think that's ok.  Any transaction that postdates one or both of A and C
will be looking for the max update time.

We can narrow the window for this failure by using current_timestamp
(wall clock time) taken as close as possible before the transaction
commits --- but I don't think we can eliminate the window entirely. 

Let me know if I've missed your point entirely or if I've explained why it isn't a problem.

I think the truly glaring problem is that if ever move to a READ COMMITTED isolation level,
what the update table says and what subsequent queries on the tables say will be potentially two different things.
I can handle that with my append-only tables, but for update/delete enabled tables I'd have to essentially lock the tables or rows
in suitable lock hierarchy order.

BTW, I think your original design is vulnerable to this same problem.
You may or may not be saved from it by the fact that you are serializing
on update-table rows; it would depend on the details of your coding.

I thought about using a sequence generator instead of now() to generate
the update_time values, but that doesn't seem to help.  Does anyone
see a way to make this 100% reliable?
 

I've avoided using any notifications from the server (I'm not sure JDBC 
even supports it, haven't tried),
since I'm unsure such mechanisms are reliable.   

LISTEN/NOTIFY would be more reliable than this, mainly because it
ensures that notifications are not delivered until after the sending
transaction commits (and therefore you cannot miss seeing its updates).
However, I'm not sure JDBC supports it either :-(.  Anyone know?
 

Furthermore, app servers in some hostile customer network environments
have idle network connections dropped after some number of hours. If
that happens, the app server will miss messages and have to
potentially completely reinitialize its cache.   

[ shrug... ]  Use dummy keepalive transactions to prevent the connection
from looking idle for long periods.  In any case, I can't believe that
infrequent cache reloads are a fatal objection. 

Depends on the deployment topology.  If the app servers have cached a gigabyte of data
in Singapore and the database server is in Kansas, the cache becomes very important.
The Singapore folks would be pissed if their first application transaction of the morning took 30 minutes to execute
while loading lots of data from Kansas.

Since I'm not currently supporting any distributed database deployments the cache is important.
Still, a cache invalidation doesn't mean I need to re-read everything, especially for those append-only tables.

I haven't even looked at the database replication or 2 phase commit capabilities for PostgreSQL.  I'm trying to write
code that is fairly least-common-denominator with respect to database capability.  There are some requirements
of the databases I use though.  MVCC is crucial to app performance given my use of complex serializable transactions,
MySql would just be stalled all the time since it doesn't have MVCC.

I'm also curious if anybody has developed a sequence generator for 
PostgreSQL that doesn't permit
gaps in the sequence based on failed transactions.   

It's easy to see that any such thing *must* be a serialization
bottleneck: you can't assign the next sequence ID till you see if
the guy who took the previous one commits.  Otherwise you have gaps.

You can imagine mechanisms that will reissue the IDs taken by failed
transactions to the next requestors.  However, this just means that you
have transient instead of permanent gaps in the ID sequence (since
later IDs might be used by xacts that commit before those that
successfully use reissued IDs).  Most of the scenarios I can think of
for wanting non-gapping ID sequences will fail just as miserably with
transient gaps as with permanent ones.

Most of my ID use would be fine to have temporary gaps.  I have only one entity that absolutely has to have monotonically increasing
primary keys and strongly prefers them to be adjacent integer values (the latter more for performance than absolute necessity).

In fact, if your criterion for "no gaps" is "no one who looks at the
current set of committed values ever sees a gap", then you really don't
have any choice: you have to ensure that the xact that took ID n
commits before the one that took ID n+1 commits; it's not enough they
commit, the commits have to occur in the right order.  So you'd have to
have serialization at commit time even if you avoided serializing at the
ID-issuance point.

I would counsel taking a very hard look at why you think you need
non-gapping IDs, rather than engaging in wishful thinking about finding
an efficient way to have them.

Rewording the my above reply, most of my non-gap requirements are only in the long term to avoid
performance problems.  Where I really lost sleep and decided  I had to go with my own sequence allocator
was the nightmare scenario of my app end-user doing some thing really stupid in a script that caused
an endless loop of failed transactions beyond my control.  Then the sequence generator gaps would be prohobitive.
Depending on the damage done to the database, that could require one of those nasty trips customer site to
repair the database, not to mention server downtime (anathema to my customers).

Better slow and steady than fast and unreliable, but there are limits to how slow... 
(Fast, cheap, good, pick two.  Sigh).

Thanks,

Dave