This is a first pass on a simple shared memory variable system for
PostgreSQL. I would appriciate anyone interested in this functionality to
rip it apart.

It basically adds this functionality:

SetShared('name', value);
GetSharedInt('name');
SetSharedText('name);
RemovedShared('name');

I also added two extra functions that are sort of a kludge, but could be
very helpful.

AddSharedInt('name', value);
SubSharedInt('name', value);

These add or subtect the 'value' from the variable and return the result.
Wed, 25 Aug 2004 17:15:28 -0400 (EDT)
pgsql@mohawksoft.com
Contrib -- PostgreSQL shared variables
LockShared('name');

pgsql@mohawksoft.com wrote:
> This is a first pass on a simple shared memory variable system for
> PostgreSQL. I would appriciate anyone interested in this functionality to
> rip it apart.
> 
> It basically adds this functionality:
> 
> SetShared('name', value);
> GetSharedInt('name');
> SetSharedText('name);
> RemovedShared('name');
> 
> I also added two extra functions that are sort of a kludge, but could be
> very helpful.
> 
> AddSharedInt('name', value);
> SubSharedInt('name', value);
> 
> These add or subtect the 'value' from the variable and return the result.
> 
> 
> ------------------------------------------------------------------------
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match

Wed, 25 Aug 2004 15:22:30 -0600
Rick Gigger <rick@alpinenetworking.com>
Re: Contrib -- PostgreSQL shared variables
pgsql@mohawksoft.com wrote:
> This is a first pass on a simple shared memory variable system for
> PostgreSQL. I would appriciate anyone interested in this functionality to
> rip it apart.
> 
> It basically adds this functionality:
> 
> SetShared('name', value);
> GetSharedInt('name');
> SetSharedText('name);
> RemovedShared('name');
> 
> I also added two extra functions that are sort of a kludge, but could be
> very helpful.
> 
> AddSharedInt('name', value);
> SubSharedInt('name', value);
> 
> These add or subtect the 'value' from the variable and return the result.
> 
Something that I've found very useful when dealing with shared memory is 
the ability to do atomic exchange of values. With that in place, its 
possible to perform atomic operations involving several variables.

Perhaps it could be as simple as splitting your SetShared into 
SetSharedInt and SetSharedString and then have them return the old value?

Here's an example of what I mean (I use C syntax for clarity, I know the 
intended use is from SQL).

     /* Loop until something other than LOCKED is returned. When
      * that happens, we have the lock.
      */
     while(SetSharedInt('lock', LOCKED) == LOCKED)
         usleep(WAIT_PERIOD);

     Set a group of variables here.

     /* Unlock
      */
     SetSharedInt('lock', UNLOCKED);


Regards,

Thomas Hallgren


Wed, 25 Aug 2004 23:42:01 +0200
Thomas Hallgren <thhal@mailblocks.com>
Re: Contrib -- PostgreSQL shared variables
> LockShared('name');

Hmmm, I thought about that, but it is *WAY* more complicated than it
looks. What if after a "Lock" the process crashes before it can call
"Unlock?" It is this problem that inspired the "add" and "sub" calls.



>
> pgsql@mohawksoft.com wrote:
>> This is a first pass on a simple shared memory variable system for
>> PostgreSQL. I would appriciate anyone interested in this functionality
>> to
>> rip it apart.
>>
>> It basically adds this functionality:
>>
>> SetShared('name', value);
>> GetSharedInt('name');
>> SetSharedText('name);
>> RemovedShared('name');
>>
>> I also added two extra functions that are sort of a kludge, but could be
>> very helpful.
>>
>> AddSharedInt('name', value);
>> SubSharedInt('name', value);
>>
>> These add or subtect the 'value' from the variable and return the
>> result.
>>
>>
>> ------------------------------------------------------------------------
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: the planner will ignore your desire to choose an index scan if
>> your
>>       joining column's datatypes do not match
>


Wed, 25 Aug 2004 17:47:28 -0400 (EDT)
pgsql@mohawksoft.com
Re: Contrib -- PostgreSQL shared variables
This new version contains, in addition to the previous version,
SwapShared(..) which allows you to swap a value in a variable.
Wed, 25 Aug 2004 21:33:50 -0400 (EDT)
pgsql@mohawksoft.com
Re: Contrib -- PostgreSQL shared variables -with swap
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wednesday 25 August 2004 02:15 pm, pgsql@mohawksoft.com wrote:
> This is a first pass on a simple shared memory variable system for
> PostgreSQL. I would appriciate anyone interested in this functionality to
> rip it apart.
>
> It basically adds this functionality:
>
> SetShared('name', value);
> GetSharedInt('name');
> SetSharedText('name);
> RemovedShared('name');
>
> I also added two extra functions that are sort of a kludge, but could be
> very helpful.
>
> AddSharedInt('name', value);
> SubSharedInt('name', value);
>
> These add or subtect the 'value' from the variable and return the result.

I don't see how this is different from "CREATE TABLE shared_variables (name 
VARCHAR PRIMARY KEY, value VARCHAR)" and 
inserting/updating/deleting/selecting from that. Perhaps these are 
per-session shared variables? IN which case, what is the utility if sharing 
them across shared memory?

- -- 
Jonathan Gardner
jgardner@jonathangardner.net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFBL8FMqp6r/MVGlwwRAl4KAJ4oC02uGs5IjO0basUCxNPd3/CCqQCfcepL
NLDzKJPINHKK9adjKue2qlQ=
=Z8mP
-----END PGP SIGNATURE-----

Fri, 27 Aug 2004 16:18:32 -0700
Jonathan Gardner <jgardner@jonathangardner.net>
Re: Contrib -- PostgreSQL shared variables

> I don't see how this is different from "CREATE TABLE shared_variables
> (name
> VARCHAR PRIMARY KEY, value VARCHAR)" and
> inserting/updating/deleting/selecting from that. Perhaps these are
> per-session shared variables? IN which case, what is the utility if
> sharing
> them across shared memory?
>
> - --
> Jonathan Gardner

Well, the issues you don't see is this:

What if you have to update the variables [n] times a second?

You have to vacuum very frequently. If you update a variable a hundred
times a second, and vacuum only once every minute, the time it takes to
update ranges from reading one row from the database to reading 5999 dead
rows to get to the live one. Then you vacuum, then you are back to one row
again.

On top of that, all the WAL logging that has to take place for each
"transaction."

I hope no one misunderstands my motives here. I think PostgreSQL is a
GREAT project. I differ with the core team on a frequent basis. Maybe the
problem is maybe I've been corrupted by Windows programming or something.
If you develop software on Windows, the trick is knowing how to do things
not intended to be done by you in order to accomplish your task. These
small little tricks are common knowledge in the M$ campuses, but in the
outside world they are the street cred that gets you paid. I may play a
little too fast and loose with "standards."

PostgreSQL is a "GREAT" SQL database, however the "database" is no longer
the product. The product is the set of functions and infrastructure on top
of the database. The SQL database is a commodity, it is what you can do
with it that makes it interresting.

The stuff that I contribute is often met with resistence, and that may or
may not be a good thing, but over the years, things I've said they NEEDED
to do, have been done and PostgreSQL is better for it.

I remember an exchange with Tom about four or five years ago, about the
need to return a number of rows from a PostgreSQL function. I don't
remember it too well, but my memory tells me he said it wasn't possible,
or maybe it was possible but not worth it, or something like that.
PostgreSQL returns sets of rows now. It took a few years, but no one would
say, now, they didn't need to do it.

Now don't think I'm talking trash to Tom either. These guys do a lot of
hard work. I have the luxury of working on other projects and noticing the
sorts of things that are a problem or could be great features. I have
great respect for them, even if not they for me.

I think the shared variable module is another one of those things. The
cost overhead of a single variable implemented as a row is too high,
especially if you want to update it many times a second. This is a simple
module. It will work for a good number of deployments and make PostgreSQL
more viable for them. I fully expect that the module will grow into
something better, ala rserv, be folded into PostgreSQL, ala toast, or just
languish in contrib. (if it is added at all.)

I believe in Open Source, I have needed this functionality in the past, I
will probably need it in the future. If I can share it with the project,
why not?


Sat, 28 Aug 2004 00:00:58 -0400 (EDT)
pgsql@mohawksoft.com
Re: Contrib -- PostgreSQL shared variables
On Sat, 28 Aug 2004 pgsql@mohawksoft.com wrote:

>
>> I don't see how this is different from "CREATE TABLE shared_variables
>> (name
>> VARCHAR PRIMARY KEY, value VARCHAR)" and
>> inserting/updating/deleting/selecting from that. Perhaps these are
>> per-session shared variables? IN which case, what is the utility if
>> sharing
>> them across shared memory?
>>
>> - --
>> Jonathan Gardner
>
> Well, the issues you don't see is this:
>
> What if you have to update the variables [n] times a second?
>
> You have to vacuum very frequently. If you update a variable a hundred
> times a second, and vacuum only once every minute, the time it takes to
> update ranges from reading one row from the database to reading 5999 dead
> rows to get to the live one. Then you vacuum, then you are back to one row
> again.

I think the right approach is to tackle that problem instead of working 
around it with a completely new variable mechanism.

I've been playing with the idea of a quick vacuum that runs through the 
shmem buffers. The idea is that since the pages are already in memory, 
the vacuum runs very quickly. Vacuuming the hot pages frequently 
enough should avoid the problem you describe. It also saves I/O in the 
long run since dirty pages are vacuumed before they are written to 
disk, eliminating the need to read in, vacuum and write the same pages 
again later.

The problem is of course that to vacuum the heap pages, you have to make 
sure that there is no references to the dead tuples from any indexes.

The trivial case is that the table has no indexes. But I believe that 
even if the table has ONE index, it's very probable that the corresponding 
index pages of the dead tuples are also in memory, since the tuple was 
probably accessed through the index.

As the number of indexes gets bigger, the chances of all corresponding 
index pages being in memory gets smaller.

If the "quick" vacuum or opportunistic vacuum as I call it is clever 
enough to recognize that there is a dead tuple in memory, and all the 
index pages that references are in memory too, it could reliably vacuum 
just those tuples without scanning through the whole relation and without 
doing any extra I/O.

I've written some code that implements the trivial case of no indexes. I'm 
hoping to extend it to handle the indexes too if I have time. Then we'll 
see if it's any good. I've attached a patch with my current ugly 
implementation if you want to give it a try.

> On top of that, all the WAL logging that has to take place for each
> "transaction."

How is that a bad thing? You don't want to give up ACID do you?

- Heikki
Sat, 28 Aug 2004 20:26:20 +0300
Heikki Linnakangas <hlinnaka@iki.fi>
Re: Contrib -- PostgreSQL shared variables
> On Sat, 28 Aug 2004 pgsql@mohawksoft.com wrote:
>
>>
>>> I don't see how this is different from "CREATE TABLE shared_variables
>>> (name
>>> VARCHAR PRIMARY KEY, value VARCHAR)" and
>>> inserting/updating/deleting/selecting from that. Perhaps these are
>>> per-session shared variables? IN which case, what is the utility if
>>> sharing
>>> them across shared memory?
>>>
>>> - --
>>> Jonathan Gardner
>>
>> Well, the issues you don't see is this:
>>
>> What if you have to update the variables [n] times a second?
>>
>> You have to vacuum very frequently. If you update a variable a hundred
>> times a second, and vacuum only once every minute, the time it takes to
>> update ranges from reading one row from the database to reading 5999
>> dead
>> rows to get to the live one. Then you vacuum, then you are back to one
>> row
>> again.
>
> I think the right approach is to tackle that problem instead of working
> around it with a completely new variable mechanism.

You assume there is a problem with the existing mechanism that needs
fixing.  There isn't. PostgreSQL does the right thing already. The issue
is that the class of problem we are trying to solve does not fit the MVCC
methodology very well.

I've given this example a number of times:

Consider this query:

select sum(col) from table;

If this is an active system, with many inserts and updates a second, and
table has a couple million rows, there is NO way to get any meaningful
answer out of this query. It is only a snapshot, not much better than an
estimate.

The shared variables are a way of expressing, "I know this is just an
estimate, but I need to do it FAST."

A LOT of websites can benefit from this, like a quick "Number of people
online"


>
> I've been playing with the idea of a quick vacuum that runs through the
> shmem buffers. The idea is that since the pages are already in memory,
> the vacuum runs very quickly. Vacuuming the hot pages frequently
> enough should avoid the problem you describe. It also saves I/O in the
> long run since dirty pages are vacuumed before they are written to
> disk, eliminating the need to read in, vacuum and write the same pages
> again later.

That's fine, but how does it affect performance?

>
> The problem is of course that to vacuum the heap pages, you have to make
> sure that there is no references to the dead tuples from any indexes.

Exactly.

>
> The trivial case is that the table has no indexes. But I believe that
> even if the table has ONE index, it's very probable that the corresponding
> index pages of the dead tuples are also in memory, since the tuple was
> probably accessed through the index.
>
> As the number of indexes gets bigger, the chances of all corresponding
> index pages being in memory gets smaller.
>
> If the "quick" vacuum or opportunistic vacuum as I call it is clever
> enough to recognize that there is a dead tuple in memory, and all the
> index pages that references are in memory too, it could reliably vacuum
> just those tuples without scanning through the whole relation and without
> doing any extra I/O.
>
> I've written some code that implements the trivial case of no indexes. I'm
> hoping to extend it to handle the indexes too if I have time. Then we'll
> see if it's any good. I've attached a patch with my current ugly
> implementation if you want to give it a try.

This is cool, but is it actually "more" work over the long run than
vacuum? I mean, a wile ago, vacuum had to lock the tables. This
non-locking vacuum is pretty cool and takes away almost all objections
that I can think about with PostgreSQL.

>
>> On top of that, all the WAL logging that has to take place for each
>> "transaction."
>
> How is that a bad thing? You don't want to give up ACID do you?

Actually, yes, yes I do. Not for everything, but for classes of problems
that do not need it.

I've some experience with this. I have a session manager for PHP. I wrote
it because databases fail miserably when what you want to do is have
SQL-esque information that neeeds to be FAST and can go away without
worrying too much about it.

Think about a website shopping cart. The session and the things in the
cart don't matter after 10 or 15 minutes. Most of the time, it will just
be abandoned. The only time it is important is at checkout. At which point
it is transfered to an ACID compliant system.

My session manager on a modern dual PIII can handle 8000 full "get vars,
set vars" cycles a second. This is more or less impossible with a SQL
database without spending a LOT of money on clustering. Mine can do it
with a single standard x86 Linux server box.

Some information can go away without worry. Paying for protecting it with
CPU and disk I/O can be VERY expensive. PostgreSQL does what it does very
well. Sometimes what it does can get in the way of using it.

>
> - Heikki
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


Sun, 29 Aug 2004 09:24:59 -0400 (EDT)
pgsql@mohawksoft.com
Re: Contrib -- PostgreSQL shared variables
On Sun, Aug 29, 2004 at 09:24:59AM -0400, pgsql@mohawksoft.com wrote:

> Think about a website shopping cart. The session and the things in the
> cart don't matter after 10 or 15 minutes. Most of the time, it will just
> be abandoned. The only time it is important is at checkout. At which point
> it is transfered to an ACID compliant system.

Much of the rest of what you said makes sense, but this is a TERRIBLE
example.  As a user, I have been burned repeatedly by crappy,
non-persistent shopping carts built by developers who, apparently,
agree with you that, "Oh, it's just a shopping cart, no one cares if
it randomly disappears and the user has to start over from scratch."

Nonsense!  Damn it, if put 12 different items in my shopping cart and
then go to lunch, I expect my shopping cart to STILL BE THERE when I
get back!  Note that Amazon does this right, so any ecommerce site
doing it WRONG does so at severe peril of losing its customers.

> My session manager on a modern dual PIII can handle 8000 full "get vars,
> set vars" cycles a second.

I don't really see why you need or want to do this in PostgreSQL,
though.  Can't you do it in your web or application server?  (I
certainly can in AOLserver with its nsv API, for example.)

What's the advantage of instead stuffing these in-memory variables
into PostgreSQL?  That it still works correctly and simply even if you
have a farm of 10 separate web servers sitting in front of it?  That
could be useful for certain applications, but is there any more to it
than that?

I've occasionally REALLY wanted a small in-memory ACId (no "D" for
Durability) RDBMS for tracking transient data that doesn't need to go
to disk (and one of these days I will experiment with using SQLite for
that), but that's not what you're talking about, right?  The shared
variables you're talking about here are just simple scalar values, no
tables, no integrity constraints, no MVCC, none of that, right?

-- 
Andrew Piskorski <atp@piskorski.com>
http://www.piskorski.com/

Sun, 29 Aug 2004 13:48:07 -0400
Andrew Piskorski <atp@piskorski.com>
Re: Contrib -- PostgreSQL shared variables
> On Sun, Aug 29, 2004 at 09:24:59AM -0400, pgsql@mohawksoft.com wrote:
>
>> Think about a website shopping cart. The session and the things in the
>> cart don't matter after 10 or 15 minutes. Most of the time, it will just
>> be abandoned. The only time it is important is at checkout. At which
>> point
>> it is transfered to an ACID compliant system.
>
> Much of the rest of what you said makes sense, but this is a TERRIBLE
> example.  As a user, I have been burned repeatedly by crappy,
> non-persistent shopping carts built by developers who, apparently,
> agree with you that, "Oh, it's just a shopping cart, no one cares if
> it randomly disappears and the user has to start over from scratch."
>
> Nonsense!  Damn it, if put 12 different items in my shopping cart and
> then go to lunch, I expect my shopping cart to STILL BE THERE when I
> get back!  Note that Amazon does this right, so any ecommerce site
> doing it WRONG does so at severe peril of losing its customers.

Your complaint is the timing, not the process. Most web sessions go away,
less than 10% of all shopping carts ever get checked out, period. The
Msession manager has a module that will save expired sessions to a
database and reload them if the session ID ever comes back, if you like.

Listen, I don't want to ramble on about web sessions, that is a different
topic, and I only wanted an example of data that was important to use in
the database, but was not persistent enough or irreplacable enough to
warrent the full ACID overhead. Web sessions are temporary. If you have a
machine that crashes every 9 months which causes all the active sessions
to be lost, this is probably acceptable as long as there is failover. That
is totally unacceptable with regard to a database.

There are classes of undependable or transient and reproducable data in a
system which works in concert with dependable data. It is inefficient to
require the transient data to be managed under the strict guidelines of
the dependable data.

Furthermore, the more independent systems you have within a larger system,
the less reliable the larger system is.


>
>> My session manager on a modern dual PIII can handle 8000 full "get vars,
>> set vars" cycles a second.
>
> I don't really see why you need or want to do this in PostgreSQL,
> though.  Can't you do it in your web or application server?  (I
> certainly can in AOLserver with its nsv API, for example.)

Having managed many projects, I can tell you, point blank, the fewer
things you need to use the better. An extension to an interface you
already use is much better than a different interface.

Also, the data in the shared variables is related to the data in the
database. It belongs close to it.
>
> What's the advantage of instead stuffing these in-memory variables
> into PostgreSQL?  That it still works correctly and simply even if you
> have a farm of 10 separate web servers sitting in front of it?  That
> could be useful for certain applications, but is there any more to it
> than that?

Isn't "that could be useful for certain applications" enough of a reason
to add something to contrib?

>
> I've occasionally REALLY wanted a small in-memory ACId (no "D" for
> Durability) RDBMS for tracking transient data that doesn't need to go
> to disk (and one of these days I will experiment with using SQLite for
> that), but that's not what you're talking about, right?  The shared
> variables you're talking about here are just simple scalar values, no
> tables, no integrity constraints, no MVCC, none of that, right?

Right. The metaphor will be done in almost every non-trivial system. The
developer can either search for some system to do it and figure out some
way to use the data that system in PostgreSQL, or load this contrib
module, and have it at the SQL level.

Honestly, I don't see the problem.


Mon, 30 Aug 2004 09:50:54 -0400 (EDT)
pgsql@mohawksoft.com
Re: Contrib -- PostgreSQL shared variables
On Monday 30 August 2004 09:50, pgsql@mohawksoft.com wrote:
> > On Sun, Aug 29, 2004 at 09:24:59AM -0400, pgsql@mohawksoft.com wrote:
> > What's the advantage of instead stuffing these in-memory variables
> > into PostgreSQL?  That it still works correctly and simply even if you
> > have a farm of 10 separate web servers sitting in front of it?  That
> > could be useful for certain applications, but is there any more to it
> > than that?
>
> Isn't "that could be useful for certain applications" enough of a reason
> to add something to contrib?
>

That's enough of a reason to add something to *gborg*, but I can think of a 
lot of things I would rather see the postgresql major contributors 
maintaining than this.. especially when you consider that we've already 
removed things like jdbc from contrib. 

-- 
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Mon, 30 Aug 2004 21:58:32 -0400
Robert Treat <xzilla@users.sourceforge.net>
Re: Contrib -- PostgreSQL shared variables