Jonathan,

> 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.

> 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 you're reading too much into the objections.  One of the things that 
keeps PostgreSQL fast, accessable, and open to new OSS developers is a high 
barrier of entry to new code proposed for the core.   A certain minimalism is 
called for if we don't want to end up like a Microsoft product, bloated, 
self-conflicted, and utterly impenetrable even to its own experts.   Often 
it's Tom and Peter expressing that minimalism but others would have to were 
they to go away (gods forfend).

This means that, for a project to get into the core, any proposer will have to 
spend a lot of time convincing the people on this list of its worthiness, 
both through lobbying and through code.  Thus the very first piece of 
criticism you'll see is "Why do we need it?".   But you can overcome these 
sorts of objections -- others have, as you yourself pointed out.  

Unfortunately, /contrib is no better than core in this way as our code gets 
larger and mainenance gets harder.  I believe our eventual goal is/should be 
to *remove* contrib entirely, replacing it with a build environment of 
pluggable components which Peter is working on.   This will have the 
beneficial effect of erasing the difficult-to-cross gap which currently 
exists between external (pgFoundry, GBorg, Sourceforge) projects and contrib.   
Go Peter!

Also, keep in mind that Tom just completed a 300-hour marathon of reviewing 
features for 8.0.    I can't imagine that he'll take a positive additude 
toward new features of any kind for about a month.

> 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.

Question:  How will these "system variables" behave regarding transactions?   
If I update a system variable and roll back the transaction, does it change 
back?   Do changes in a running transaction remain invisible until COMMIT?  
Excuse me if you've already answered these; I've not caught up on my -hackers 
backlog since I broke my foot.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Sat, 28 Aug 2004 13:01:02 -0700
Josh Berkus <josh@agliodbs.com>
Re: Contrib -- PostgreSQL shared variables 

>> 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.
>
> Question:  How will these "system variables" behave regarding
> transactions?
> If I update a system variable and roll back the transaction, does it
> change
> back?   Do changes in a running transaction remain invisible until COMMIT?
> Excuse me if you've already answered these; I've not caught up on my
> -hackers
> backlog since I broke my foot.

Actually that are not involved with transactions in any way.

There classes of problems in which proper ACID implementation is not
nessisary. In fact, there are subclasses within a larger system that need
not have the overhead imposed by transactions.

Think about this:

In one part of the system, you update a table:

updated summary_table set sum = sum + $newvalue ;


In another part of the system you perform this:

select sum from summary_table;

You did this to keep from having to do:

select sum(col) from real_table;

Which, as everyone knows, can be millions of records, and a full table
scan would be too expensive.


In an active system there is *no* way that the results of these
transactions can be anything but an estimate or a snap shot. By the time
the results have been reported, the results surely would have changed.

In order to get this inaccurate information, you had to incure many dleted
rows in the summary table for each update or the cost of a full table
scan.

Usng the variable metaphor, the overall cost of doing this is almost nil.
With care, the results can be just as accurate with respect to time.

Like I said, it is *NOT* a general solution, but it *is* a solution a lot
of developers using databases have to implement *somewhere*, why not in
the database in which the data is stored?


Sun, 29 Aug 2004 09:06:03 -0400 (EDT)
pgsql@mohawksoft.com
Re: Contrib -- PostgreSQL shared variables

Josh Berkus <josh@agliodbs.com> writes:

> Question:  How will these "system variables" behave regarding transactions?   
> If I update a system variable and roll back the transaction, does it change 
> back?   Do changes in a running transaction remain invisible until COMMIT?  
> Excuse me if you've already answered these; I've not caught up on my -hackers 
> backlog since I broke my foot.

I think this is precisely the objection. He wants something that allows him to
break out of transaction semantics entirely. Other backends would see the
value he sets before he commits. If he rolls back his changes would remain.

I expect to see substantial resistance to incorporating such a feature. One
argument will be that he should simply use a separate system for such data.
For example, memcached would be much faster and more scalable than
communicating via postgres.

The only disadvantage to using something like memcached would be that you
couldn't transparently use values from there inside your queries. You would
have to fetch them and then pass them as parameters to postgres.

-- 
greg


29 Aug 2004 10:42:55 -0400
Greg Stark <gsstark@mit.edu>
Re: Contrib -- PostgreSQL shared variables


Greg Stark wrote:

>I expect to see substantial resistance to incorporating such a feature. One
>argument will be that he should simply use a separate system for such data.
>For example, memcached would be much faster and more scalable than
>communicating via postgres.
>  
>

Postgres should do what postgres is good at IMNSHO. Small is beautiful.

>The only disadvantage to using something like memcached would be that you
>couldn't transparently use values from there inside your queries. You would
>have to fetch them and then pass them as parameters to postgres.
>  
>

Could you not overcome that using server-side functions, in, say, C or 
plperlu? In fact I suspect you could implement this whole scheme using 
memcached (which looks quite cool and exactly made for this) plus a very 
small amount of plperlu glue.

cheers

andrew



Sun, 29 Aug 2004 12:05:55 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: Contrib -- PostgreSQL shared variables
Mohawksoft,

> Actually that are not involved with transactions in any way.
>
> There classes of problems in which proper ACID implementation is not
> nessisary. In fact, there are subclasses within a larger system that need
> not have the overhead imposed by transactions.

Hmmm, wait, are you implementing this, or is Jonathan?  

As a database developer who would use this or a similar feature, I do not 
agree that the lack of transactionality is a benefit.   Quite the contrary.   
Imagine this scenario, once we have error-trapping in place for 8.1 (pardon 
the syntax if it's off the proposal, I think you'll see the point anyway):

BEGIN
	SAVEPOINT before_udpate;
		UPDATE jehosaphat SET slivy = 60 WHERE status = 4;
		SET SHARED jehosaphat_last_update = now();
		IF transaction_error > 0 THEN 
			ROLLBACK TO before_update;
		ELSE
			COMMIT;
		END IF;
...

Now assume that the reason I'm using a shared variable is that "jehosaphat" 
needs to be updated every 500ms, and thus we don't want to be storing it in a 
table.   In that case, if shared variables are not transactional, the above 
would be deceptive; the system would think that jeshosaphat had been updated 
when it had not.   

Ah, you say, but you could work around that if you coded correctly.   Maybe 
so, although I find the idea that transactions don't apply to some objects 
very liable to cause confusion.   Also consider this case:

CREATE FUNCTION update_jeshosaphat_last () RETURNS TRIGGER AS '
BEGIN
	SET SHARED jehosaphat_last_update = now();
END; ' language plpgsql;

CREATE TRIGGER tg_jehosaphat_update
BEFORE UPDATE OR DELETE ON jehosaphat 
FOR EACH STATEMENT EXECUTE PROCEDURE update_jehosaphat_last();

NOW imagine if your update gets rolled back; the shared variable will be 
misreporting the last update and there isn't any coding around it.   

My personal perspective is that shared variables should be transactional, just 
like everything else in PostgreSQL except sequences.    Just because they are 
transactional does not mean they have to be saved on disk; that's where most 
of your "dead tuple overhead" comes in.   If you could place the variables in 
"reserved" shared memory and never sync, then needing to have several copies 
which split and collapse to remain current with the existing transactions 
should not be a major performance cost.	Of course, it will make the code a 
lot harder, but that's one of the reasons why nobody has done it before now.

Now, if you're telling me that it's too difficult for you to implement 
transactions at this time, and that you want to push this patch without 
transactions for them to be added sometime later, then we can have it out on 
that basis.   But you're going to have a hard time convincing me or several 
other people that ignoring transactions is somehow a benefit.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Sun, 29 Aug 2004 11:23:53 -0700
Josh Berkus <josh@agliodbs.com>
Re: Contrib -- PostgreSQL shared variables
> Mohawksoft,
>
>> Actually that are not involved with transactions in any way.
>>
>> There classes of problems in which proper ACID implementation is not
>> nessisary. In fact, there are subclasses within a larger system that
>> need
>> not have the overhead imposed by transactions.
>
> Hmmm, wait, are you implementing this, or is Jonathan?
>
> As a database developer who would use this or a similar feature, I do not
> agree that the lack of transactionality is a benefit.   Quite the
> contrary.
> Imagine this scenario, once we have error-trapping in place for 8.1
> (pardon
> the syntax if it's off the proposal, I think you'll see the point anyway):
[snip -- example]

Your example makes sense if you think that you'll only be using this in
the SQL environment.

One of the most common SQL database applications is as the backend of some
higher level system, like a web site. In these systems, ususlly done in
Java, PHP, or heaven help us, .NET, you have multiple front end machine
communicating with a SQL database backend.

These systems use the database as the main data server. The overall
scalability of their system is based on the scalability of the database.
At some point, you'll max out the DB and be stuck. The shared variables
module is designed to allow you to scale better by allowing you to cheapy
maintain data.

So, rather than call this:

select sum(field) from mytable;

or

update mytable_summary set field = field + n;

You can do this:

select addshared('mytable', n);

Now, in Java or PHP, you'd do something like this:

somefunc()
{
    if(sql.exec(...))
    {
        // Worked
        sql.exec("select addshared('mytable', n)");
    }
}

OK, now you're going to say, why not use a different system for the
caching? As I said in a different post, the more systems you glue
together, the more complicated the bigger system gets. The more
complicated it gets, the less reliable it gets. Besides, this is data
you're gonna use with the data in PostgreSQL, so why not provide an
extension for it?



Mon, 30 Aug 2004 10:25:09 -0400 (EDT)
pgsql@mohawksoft.com
Re: Contrib -- PostgreSQL shared variables
Mohawksoft,

> One of the most common SQL database applications is as the backend of some
> higher level system, like a web site. In these systems, ususlly done in
> Java, PHP, or heaven help us, .NET, you have multiple front end machine
> communicating with a SQL database backend.

Leaving aside that Java, .NET and even PHP5 have much more sophisticated ways 
of sharing variables ...

> These systems use the database as the main data server. The overall
> scalability of their system is based on the scalability of the database.
> At some point, you'll max out the DB and be stuck. The shared variables
> module is designed to allow you to scale better by allowing you to cheapy
> maintain data.

I really don't follow how this makes a lack of transaction-awareness for these 
global variables an advantage.     If you're saying that it's not possible to 
make shared variables which are both cheap and transaction-aware, then say so 
and be clear about it.    

Please understand that I am not opposed to the idea behind the shared variable 
proposal.   I just think there are some details to be worked out.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Mon, 30 Aug 2004 13:11:48 -0700
Josh Berkus <josh@agliodbs.com>
Re: Contrib -- PostgreSQL shared variables
> Mohawksoft,
>
>> One of the most common SQL database applications is as the backend of
>> some
>> higher level system, like a web site. In these systems, ususlly done in
>> Java, PHP, or heaven help us, .NET, you have multiple front end machine
>> communicating with a SQL database backend.
>
> Leaving aside that Java, .NET and even PHP5 have much more sophisticated
> ways
> of sharing variables ...

Yes, and hows does a developer share between PHP, Java, and .NET? Yes, in
a homogenous system that may work, but not across multiple systems, and
that still doesn't address the data being 'in' the database as opposed to
some other system.

>
>> These systems use the database as the main data server. The overall
>> scalability of their system is based on the scalability of the database.
>> At some point, you'll max out the DB and be stuck. The shared variables
>> module is designed to allow you to scale better by allowing you to
>> cheapy
>> maintain data.
>
> I really don't follow how this makes a lack of transaction-awareness for
> these
> global variables an advantage.     If you're saying that it's not possible
> to
> make shared variables which are both cheap and transaction-aware, then say
> so
> and be clear about it.
>
> Please understand that I am not opposed to the idea behind the shared
> variable
> proposal.   I just think there are some details to be worked out.

You won't understand it from what you quoted because the reasoning has
been snipped, and I've posted the example so many times my fingers are
sore.

Some data does not need to be transactional. Data that does not need to be
transactional does not need to be transactional. If overhead can be
reduced or eliminated by eliminating transactional procedures for data
which does not need it, then the database scales better. Isn't that
completely obvious?

If your system is bonking out, every last bit of efficiency is important.
(And this is no small efficiency!) It is these sorts of tricks that can
make the difference between a well working site, and one that looks like
it has been slashdotted.



Mon, 30 Aug 2004 21:40:47 -0400 (EDT)
pgsql@mohawksoft.com
Re: Contrib -- PostgreSQL shared variables