Re: [GENERAL] Postgres Database size
I have been looking for such a function. Having Just upgraded to 8.2, this function is a very welcome addition to my arsenal of tools. Many thanks! - Naz. Reece Hart wrote: On Sun, 2007-03-18 at 00:40 +0530, Mageshwaran wrote: how to find the size of a particular database in postgres... The old way was to use du or similar. Recent versions (I believe >=8.1, but check the release notes to be sure) provide several useful functions for this: pg_column_size pg_database_size pg_relation_size pg_size_pretty pg_tablespace_size pg_total_relation_size For example: [EMAIL PROTECTED]> select datname,pg_size_pretty(pg_database_size(oid)) from pg_database ; datname | pg_size_pretty -+ postgres| 3905 kB csb | 113 GB template0 | 3840 kB csb-dev | 124 GB csb-dev-snapshot-2007-03-08 | 123 GB csb_02_02_2007 | 121 GB template1 | 3840 kB
Re: [GENERAL] Shell script to determine if PostgreSQL is accepting connections?
On 10:44 Sun 18 Mar , Jeff Ross wrote: > Hi, > > Is there a simple way to determine with a shell script if PostgreSQL is > ready to accept connections? > > PostgreSQL is started and controlled by daemontools. This shell script > is called by the /etc/netstart script as a part of bringing up the > first interface. Unfortunately, it will never exit the loop. > > #!/bin/sh > /bin/csh -cf '/command/svscanboot &' > until [ -S /tmp/.s.PGSQL.5432 ] ; do > /bin/echo "Waiting for PostgreSQL to start..." > /bin/sleep 1 > done > > Because this is happening at startup before I get a login prompt I'm not > sure I know how to figure out why it never exits the loop. > > The purpose of this is that I'm running pglogd, a daemon that uses a > custom log format and a fifo to stuff Apache web logs into a table. The > pglogd daemon must start after PostgreSQL and before Apache. I can do > this in /etc/rc.local, but OpenBSD's /etc/rc wants to start Apache > before rc.local is parsed. Once I know that PostgreSQL is up and > accepting connections I can then start pglogd and then it's ready to go > when Apache starts. > > As a side note, the original creator and maintainer of pglogd has EOLed > the project but it works well for me under 8.2.3. I intend to fix a few > of its problems (it does fine until the backend goes away ;-) and > re-release it. > > Thanks, > > Jeff Ross > > P.S. Apologies to the owner of the list--I inadvertently sent this to > the wrong address first. > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq variants on the following might be useful... $ psql -U postgres -t -c "select pg_postmaster_start_time()" postgres` 2007-03-18 16:51:55.239342-04 so ... start loop response=`psql -U postgres -t -c "select pg_postmaster_start_time()" postgres` or response=`psql -U postgres -t -c "select now()" postgres` if [ $? == 0 ]; then parse/evaluate response to make determination of whether db is up ( or, base decision on evaluation of $? ) break loop if db is up or other criteria are met ( taking too long, etc ) else query failed, backend not ready, continue loop fi end loop ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL 8.2.3 VACUUM Timings/Performance
On Fri, Mar 16, 2007 at 07:06:57PM -, Bruce McAlister wrote: > Okay, I'm getting a little further now. I'm about to create entries in the > pg_autovacuum system tables. However, I'm a little confused as to how I go > about finding out the OID value of the tables. The pg_autovacuum table > requires the OID of the table you want to create settings for (vacrelid). > Can anyone shed some light on how I can extract the OID of the table? Also, > what happens if you create a table without OID's, are you still able to add > it's details in the pg_autovacuum table if there is no OID associated with a > table? The easiest would seem to be to be: SELECT 'mytable'::regclass; That will get you the OID without you having to look it up yourself. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Is This A Set Based Solution?
Stefan Berglund <[EMAIL PROTECTED]> writes:
> I tried this:
> create or replace function foo(plist TEXT)
> RETURNS SETOF Show_Entries as $$
> SELECT *
> FROM Show_Entries
> WHERE Show_ID = 1250 AND Show_Number IN ($1);
> $$ LANGUAGE sql;
> When I use select * from foo('101,110,115,120'); I get no results. When
> I use select * from foo(101,110,115,120); I get the correct results.
Just for the record, the reason that didn't work is that Postgres saw it
as a comparison to a single scalar IN-list item. What you had was
effectively
WHERE Show_ID = 1250 AND Show_Number::text IN ('101,110,115,120');
which of course will fail to find any rows.
In recent releases (8.2 for sure, don't remember if 8.1 can do this
efficiently) you could instead do
create or replace function foo(plist int[])
RETURNS SETOF Show_Entries as $$
SELECT *
FROM Show_Entries
WHERE Show_ID = 1250 AND Show_Number IN ($1);
$$ LANGUAGE sql;
select * from foo(array[101,110,115,120]);
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Shell script to determine if PostgreSQL is accepting connections?
Hi, Is there a simple way to determine with a shell script if PostgreSQL is ready to accept connections? PostgreSQL is started and controlled by daemontools. This shell script is called by the /etc/netstart script as a part of bringing up the first interface. Unfortunately, it will never exit the loop. #!/bin/sh /bin/csh -cf '/command/svscanboot &' until [ -S /tmp/.s.PGSQL.5432 ] ; do /bin/echo "Waiting for PostgreSQL to start..." /bin/sleep 1 done Because this is happening at startup before I get a login prompt I'm not sure I know how to figure out why it never exits the loop. The purpose of this is that I'm running pglogd, a daemon that uses a custom log format and a fifo to stuff Apache web logs into a table. The pglogd daemon must start after PostgreSQL and before Apache. I can do this in /etc/rc.local, but OpenBSD's /etc/rc wants to start Apache before rc.local is parsed. Once I know that PostgreSQL is up and accepting connections I can then start pglogd and then it's ready to go when Apache starts. As a side note, the original creator and maintainer of pglogd has EOLed the project but it works well for me under 8.2.3. I intend to fix a few of its problems (it does fine until the backend goes away ;-) and re-release it. Thanks, Jeff Ross P.S. Apologies to the owner of the list--I inadvertently sent this to the wrong address first. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] phpPgAdmin - prior version available?
Hi all, I have been struggling with phpPgAdmin 4.1 - login failures. There does not yet seem to be a fix. Where can I find a prior version for FC6 - rpm, tar.gz etc. Thanks, Bob ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Is This A Set Based Solution?
On Thu, Mar 15, 2007 at 10:26:48AM -0700, Stefan Berglund wrote:
> that PostgreSQL would allow a substitutable parameter in the IN clause.
> However, it seems that it can't be done in this fashion without using
> dynamic SQL unless I'm missing something.
The substitutable list has to be an array, not a text value. So
if the parameter is specified as ARRAY OF INTEGER, you can call it
like:
SELECT foo(ARRAY[1,2,3,45]);
SELECT foo('{1,2,3,4,56}');
Hope this helps,
--
Martijn van Oosterhout http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to
> litigate.
signature.asc
Description: Digital signature
Re: [GENERAL] Design / Implementation problem
Naz First, there is nothing you can do about the computational load except make your code as efficient as possible. Get it right and then make it fast. But there is only so much you can do. If a "calculation" requires an integer sum and an integer difference, you inevitably have two integer operations per "calculation". For a given complex calculation, you generally have to resort to a little algebra to put the calculation into a form that requires the least number of atomic operations (think of a mean and variance, for a simple example where a brute force approach will be correct but much more expensive than one based on a little algebraic manipulation. That leaves making your application appear to be more responsive by distributing the computational load differently. This time think of some basic data structures and the work required to obtain sorted output. Considering the standard C++ STL, one could use a vector, with contents in random order, and sort that, or one could use a map and have sorted output at any time without an explicit sort operation before getting it. But think what has happened. Element access, including especially inserts or additions and reading container elements, will be very fast with the vector, faster than with a map. But all the computational load for obtaining sorted output is carried at the moment the request for such output is made. On the other hand, getting sorted output from the map will appear to be blindingly fast relative to getting the same output from the vector. Why? Because getting sorted output requires a certain number of comparisons. This can not be avoided. But with the map, these are done on insert, so your data is actually stored sorted. The price of the sort is paid in both the case of using a vector and in the case of using the map, but the user may not notice that cost in the case of a map where he would in the case of the vector (if the dataset is large). I suspect you are aware of some of this already, but I say it just to make certain of it. I say all the above so that you don't get too distracted by your calculations of the potential costs of supporting your program. You need to have an idea of how expensive it wll be, and how you might distribute the costs, but if you have millions of clients, and each operation requires a hald a dozen basic operations, there is no avoiding that. You reduce as much as possible, but you will always get to a point where it can not be reduced further. At that point, all you can do is throw more iron at the application. Now, an option to consider. Create two tables, one for points accrued and one for points used. In each case, you'll have fields to identify the customer, his transactions, etc., but the important fields will be one for the number of points (acquired or used) on a given day, and the date. It may also be prudent to have a field to represent expiry date since how long points can be stored before they have to be used is a policy decision, one that could change at any time, and ought therefore be a data item stored rather than hardcoded into your code. The points acquired table will need a field to represent the points acquired on that date that remain to be used, so that transactions using points can use the oldest points acquired by the customer before using new ones. You may well want this for auditing purposes in addition to the core functionality you're after. Now add a third which keeps track of the number of points available at a given time. Again, you'll have fields to identify the customer, the number of available points, and maybe the date (it depends on how you want to do it and what else your database needs to support). Now, update the points acquired table and the summary table in each transaction in which the customer can gain points, and update the points used table and summary table in each transaction where the customer can use points. And you can update all three in transactions where a customer can use points for part of the transaction and other resources for the rest. At least until your unit testing is done, I'd suggest an extra table or two that allows you to store information about which points a customer is using for a given transaction, just to make absolutely certain that in fact your customer is using the oldest points that he has, in a given transaction, before the ones he acquired today. Finally, you may want to create archive tables and a scheduled task that moves records dealing with expired points from the table you're always working with to the archive tables (in enough detail that you can reconstruct precisely how and when points were acquired and used by each customer), but you might need extra code to bring them back should a policy make decide that points can have a three year lifespan instead of one. I'd put all the required code to support all of this into a suite of store
Re: [GENERAL] cannot get build (initdb) to work after trying for a week
Eric wrote: > I am getting truly desperate! I am trying to build any version of > Postgres on Windows from source. First experience with Postgres > source. I have struggled for days trying to solve a problem. Would be > willing to pay for the solution!!! > > The following error occurred when issuing a vanilla initdb command on > my Windows system. > > FATAL: could not select a suitable default timezone > DETAIL: It appears that your GMT time zone uses leap seconds. > PostgreSQL does not support leap seconds. > > I built this from 8.1.8 source under msys/mingw. Also tried 8.2.2. > Everything builds fine but initdb always reports the above error and > quits. This could happen if the timezone files aren't properly installed. This should normally happen when you do "make install", but verify that they are actually present (in share/timezone). > I also had to create a typedef int ssize_t in a couple of files to get > the compile finished. Maybe this is related. Other than that, have > not touched any code. I don't think that's related, but it's a clear indicator that something in your environment is broken. Because it should build without any such changes. //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] cannot get build (initdb) to work after trying for a week
Eric wrote: > I am getting truly desperate! I am trying to build any version of > Postgres on Windows from source. First experience with Postgres > source. I have struggled for days trying to solve a problem. Would be > willing to pay for the solution!!! > > The following error occurred when issuing a vanilla initdb command on > my Windows system. > > FATAL: could not select a suitable default timezone > DETAIL: It appears that your GMT time zone uses leap seconds. > PostgreSQL does not support leap seconds. Try specifying a timezone in postgresql.conf. For initdb, I'd try specifying it in the TZ environment variable. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] cannot get build (initdb) to work after trying for a week
I am getting truly desperate! I am trying to build any version of Postgres on Windows from source. First experience with Postgres source. I have struggled for days trying to solve a problem. Would be willing to pay for the solution!!! The following error occurred when issuing a vanilla initdb command on my Windows system. FATAL: could not select a suitable default timezone DETAIL: It appears that your GMT time zone uses leap seconds. PostgreSQL does not support leap seconds. I built this from 8.1.8 source under msys/mingw. Also tried 8.2.2. Everything builds fine but initdb always reports the above error and quits. I was able to run successfully from 8.1.5 binaries on the same system. This would lead me to believe that it is a simple compiler switch somewhere that would alleviate this problem. I have tried variations of configure, including and not including mingw includes and libs. I also had to create a typedef int ssize_t in a couple of files to get the compile finished. Maybe this is related. Other than that, have not touched any code. Given that an 8.1.5 binary dist worked for me, what flags were provided to build that version? Simply configure, then make? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PgSql on Vista?
Ok thanks i've fixed. The problem is with 8.2.3 installer, i tried 8.1.8 and I had no problem. Thanks On 3/15/07, Dave Page <[EMAIL PROTECTED]> wrote: > --- Original Message --- > From: Alvaro Herrera <[EMAIL PROTECTED]> > To: Dave Page <[EMAIL PROTECTED]> > Sent: 15/03/07, 22:32:50 > Subject: Re: [GENERAL] PgSql on Vista? > > Dave Page escribió: > > Paul Lambert wrote: > > > > >After install completes you can turn it back on... if you want - > > >personally I leave it off, it's an incredibly annoying "feature". > > > > Doesn't the security center keep popping up to point out that it's > > turned off? > > You mean, like this? > > http://images.apple.com/movies/us/apple/getamac/apple-getamac-security_480x376.mov > I haven't watched yet as I'm on my pda, but would it seem a little ironic if I pointed out that the Windows distribution of PostgreSQL is built on a Macbook Pro these days? /D -- (¯`·._) XF86Config ne sa MOLTE più del diavolo. O forse serve ad evocare il diavolo stesso, non ho capito bene. (¯`·._) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Is This A Set Based Solution?
On Sat, 10 Mar 2007 08:26:32 +0300 (MSK), oleg@sai.msu.su (Oleg Bartunov) wrote: in <[EMAIL PROTECTED]> >I don't know if you could change your schema. but I'd consider your >problem as a overlapping arrays task and use contrib/intarray for that. That's a nice piece of work, Oleg, and extremely quick. I played with it and pored over the docs but it just seems to keep coming back to the fact that all of the array type manipulations are column based as opposed to row based. In fact, this from section 8.10.5 of the docs confirms it: Tip Arrays are not sets; searching for specific array elements may be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale up better to large numbers of elements. I was able to clean up the function I originally posted removing the extraneous LOOP and I'm more than happy with the performance. What's funny is that the function as it now stands is what I initially obtained by googling, but I mistakenly added the extra loop. :-) What I finally came up with is here: <[EMAIL PROTECTED]> --- Stefan Berglund ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] issue with SELECT settval(..);
thanks a lot Christian. On 3/18/07, Christian Schröder <[EMAIL PROTECTED]> wrote: Alain Roger wrote: > insert into immense.statususer (statususer_id, statususer_type) values > (SELECT nextval( 'statususer_statususer_id_seq' ),'customer'); The correct syntax would be: insert into immense.statususer (statususer_id, statususer_type) values ((SELECT nextval( 'statususer_statususer_id_seq' )),'customer'); The sub-select must be put in parentheses. However, the much simpler statement insert into immense.statususer (statususer_id, statususer_type) values (nextval( 'statususer_statususer_id_seq' ),'customer'); will do the same without a sub-select. Regards Christian -- Deriva GmbH Tel.: +49 551 489500-42 Financial IT and Consulting Fax: +49 551 489500-91 Hans-Böckler-Straße 2 http://www.deriva.de D-37079 Göttingen Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Alain Windows XP SP2 PostgreSQL 8.1.4 Apache 2.0.58 PHP 5
Re: [GENERAL] Is This A Set Based Solution?
On Fri, 16 Mar 2007 09:38:52 -0300, [EMAIL PROTECTED] (Jorge Godoy) wrote: in <[EMAIL PROTECTED]> >Tino Wildenhain <[EMAIL PROTECTED]> writes: > >> Show me a user which really clicks on 1000 or more checkboxes on a >> webpage or similar ;) >> I'd think around 20 values is plenty. > >On the other hand, show me a page with 1000 or more checkboxes to be clicked >at once and I'd show a developer / designer that needs a new career... :-) Just to allay your fears, a fairly typical scenario might have the user presented with a list of from twenty to fifty names of horse trainers depending on the size of the show. Since each trainer can have anywhere from one to thirty or forty horses in their barn you can do the math to see that the list of IDs passed from the app to the database can be anywhere from a single ID up to possibly thousands of IDs. --- Stefan Berglund ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Is This A Set Based Solution?
On Thu, 15 Mar 2007 15:46:33 -0500, [EMAIL PROTECTED] (Bruno Wolff III)
wrote:
in <[EMAIL PROTECTED]>
>On Mon, Mar 12, 2007 at 11:15:01 -0700,
> Stefan Berglund <[EMAIL PROTECTED]> wrote:
>>
>> I have an app where the user makes multiple selections from a list. I
>> can either construct a huge WHERE clause such as SELECT blah blah FROM
>> foo WHERE (ID = 53016 OR ID = 27 OR ID = 292 OR ID = 512) or I could
>> alternatively pass the string of IDs ('53016,27,292,512') to a table
>> returning function which TABLE is then JOINed with the table I wish to
>> query instead of using the unwieldy WHERE clause. The latter strikes me
>> as a far more scalable method since it eliminates having to use dynamic
>> SQL to construct the ridiculously long WHERE clause which will no doubt
>> ultimately bump up against parser length restrictions or some such.
>
>How big is huge?
>If the list of IDs is in the 1000s or higher, then it may be better to
>load the data into a temp table and ANALYSE it before running your query.
>Otherwise, for smaller lists the IN suggestion should work well in recent
>versions.
Sorry, huge was an exaggeration. I doubt it would ever approach 1000 -
more like a couple hundred. I'll look at it a little closer.
---
Stefan Berglund
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] Own messages for constraints?
Hi ! Is it possible in postgres to define own message for some constraint? Example: If i create check constraint on zip_code column , i want to get a message: "Zip code is invalid.Please,input code in format: nn-nnn" and I want to send this message to the end user. It will be much better(in my case) than "violates constraint zip_code_check" :-) . I can make this validation in trigger (and send msg to application by notice or raise),but in this case i will duplicate validation rules (in trigger and in constraint).Effect: Lower performance(i think) and rules in two places... Best regards and thanks for help, Kacper Chrapa Fantastyczna promocja w RUCHu! Rób zakupy, wysyłaj SMSy i codziennie wygrywaj bony pieniężne! W finale do wygrania Nissan NOTE. Sprawdź: http://klik.wp.pl/?adr=www.loteriaruch.pl&sid=1062 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Is This A Set Based Solution?
On Thu, 15 Mar 2007 09:47:27 -0500, [EMAIL PROTECTED] (George Weaver)
wrote:
in <[EMAIL PROTECTED]>
>
>Stefan Berglund wrote:
>
>> foo WHERE (ID = 53016 OR ID = 27 OR ID = 292 OR ID = 512) or I could
>> alternatively pass the string of IDs ('53016,27,292,512') to a table
>> returning function which TABLE is then JOINed with the table I wish to
>
>The user selections will be in some sort of list. Could you not use WHERE
>ID IN (the list)?
Coming from SQL Server where that is not allowed, it didn't occur to me
that PostgreSQL would allow a substitutable parameter in the IN clause.
However, it seems that it can't be done in this fashion without using
dynamic SQL unless I'm missing something.
I tried this:
create or replace function foo(plist TEXT)
RETURNS SETOF Show_Entries as $$
SELECT *
FROM Show_Entries
WHERE Show_ID = 1250 AND Show_Number IN ($1);
$$ LANGUAGE sql;
When I use select * from foo('101,110,115,120'); I get no results. When
I use select * from foo(101,110,115,120); I get the correct results.
At any rate, I'm happy with what I've come up with and so far
performance is excellent:
CREATE TABLE test_table (
id int not null,
tname varchar(50) not null);
INSERT INTO test_table
SELECT 1, 'Adams'
UNION SELECT 2, 'Baker'
UNION SELECT 3, 'Chrysler'
UNION SELECT 4, 'Douglas'
UNION SELECT 5, 'Everyman';
CREATE OR REPLACE FUNCTION foo (
pList TEXT) RETURNS SETOF INTEGER AS $foo$
DECLARE
v_arr text[];
BEGIN
v_arr := string_to_array($1, ',');
FOR i IN array_lower(v_arr, 1)..array_upper(v_arr, 1) LOOP
RETURN NEXT v_arr[i]::int;
END LOOP;
RETURN;
END;
$foo$ LANGUAGE plpgsql;
SELECT *
FROM
foo('5,1,3') SL INNER JOIN
test_table T ON SL=T.ID;
SELECT * FROM foo('52001,17,22,42,47') ORDER BY foo;
---
Stefan Berglund
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Design / Implementation problem
Naz Gassiep <[EMAIL PROTECTED]> writes: > that calculating the point > balance on the fly is not an unfeasibly heavy duty calculation to be done at > every page view? One alternative to calculate it everytime is calculating it once a day. If there are calculations for today, then just read the calculated value. Otherwise calculate it. If the user earns more points, make an entry at the raw table (for the expiration process) and increments today points. Do the same for points spent. -- Jorge Godoy <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Design / Implementation problem
Here it is again with more sensible wrapping: *** The Scenario *** We are running a customer loyalty program whereby customers earn points for purchasing products. Each product has a value of points that are earned by purchasing it, and a value of points required to redeem it. In order to prevent customers from stockpiling points, we want to place an expiry date on points so that unused points expire and are lost if they are not redeemed within a certain period of time. This will be calculated on a FIFO basis, I.e., the oldest points will expire first. We will assume the expiry period is 12 months. *** The Problem *** Ascertaining which points to expire is fairly conceptually simple. At any given point in time, the points expired is simply the balance on hand at the start of the period, less redemptions in that period. If the redemptions is less than the balance at open, not all points that were available on that date were used, and the difference is the expiry. This can be done periodically, say, at the start of every month. However there are a few problems with doing it periodically 1. The runs are likely to be too large to be manageable. A DB with tens of thousands of customers and many hundreds of thousands or even millions of sales in the records tables will require several queries and some application calculation to compute. If it takes 2 seconds to compute each balance of a 20,000 strong customer base, that's over 11 hours of heavy lifting in the DB, which will likely result in severely degraded performance during those hours. This problem can only get worse as time goes on, and hardware upgrade requirements just to accommodate a 12 hour window once a month is the sign of an app not designed to scale well. 2. Calculating the balance on the fly would be more effective, as it is unlikley that many customers will check their balance on a regular basis. It is likely that a small fraction of customers will check their balance in a given month, meaning that calculating it on the fly would both spread the load over time as well as reduce the total load, even if on the fly calculation results in significantly higher per-customer calculation time. 3. The app is a web app, and it would be preferable to contain business logic within the database itself or the current app codebase. Spreading application logic into an external mechanism such as cron or an external daemon would be undesirable unless there was no other way. *** A Possible Solution *** Calculating the balance on the fly can be done easily if it is done at the time the customer seeks to redeem a voucher. Expired points are only relevant at these times, and so the expired points would be calculated with an application function that did the following: 1. Get the balance as it was 12 months ago by getting total points earned less redemptions and expiries up to that date. 2. Subtract from it redemptions and expiries since then. The value obtained, if it is positive, is the value of points to expire. 3. Log the expiry entry, and then calculate the balance of points to the current date by subtracting total points redeemed and expired from total points earned. This procedure has a few practical problems, however: 1. Customers, when they view their running total, will not be aware that some of the points included in it will have expired, as the expiry will only happen when the application attempts to log a redemption. 2. A customer may attempt to redeem a product they do not have enoughh points for, and be told at the last minute that they do not have enough points, leading to acrimony. The solution is then to calculate it on only on redemptions, but also whenever the customer attempts to view their balance. This will ensure that expired points will never be shown in the current balance of available points. This, however, means the calculation may be done many times by each customer in a single catalog browsing session. *** The Question *** Is there a way to design the DB schema as well as the query in such a manner that calculating the point balance on the fly is not an unfeasibly heavy duty calculation to be done at every page view? This problem does not appear to be solved comprehensively by anyone. When I log into my credit card company web site to check my points, I get a message "please come back in an hour, and your points will be calculated" if I haven't logged in for over a week. So obviously they calculate the balance and put it in a table that acts as a "cached balance". Emirates has a different solution, they do bi-annual runs, so points expire every March and September for them. Neither of these solutions appeals, and there must be A Better Way(tm). ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Design / Implementation problem
This is possibly not a DB only problem, the solution may involve application logic as well. But PG users are the smartest bunch I know. Ass kissing aside, here are the details: *** The Scenario *** We are running a customer loyalty program whereby customers earn points for purchasing products. Each product has a value of points that are earned by purchasing it, and a value of points required to redeem it. In order to prevent customers from stockpiling points, we want to place an expiry date on points so that unused points expire and are lost if they are not redeemed within a certain period of time. This will be calculated on a FIFO basis, I.e., the oldest points will expire first. We will assume the expiry period is 12 months. *** The Problem *** Ascertaining which points to expire is fairly conceptually simple. At any given point in time, the points expired is simply the balance on hand at the start of the period, less redemptions in that period. If the redemptions is less than the balance at open, not all points that were available on that date were used, and the difference is the expiry. This can be done periodically, say, at the start of every month. However there are a few problems with doing it periodically 1. The runs are likely to be too large to be manageable. A DB with tens of thousands of customers and many hundreds of thousands or even millions of sales in the records tables will require several queries and some application calculation to compute. If it takes 2 seconds to compute each balance of a 20,000 strong customer base, that's over 11 hours of heavy lifting in the DB, which will likely result in severely degraded performance during those hours. This problem can only get worse as time goes on, and hardware upgrade requirements just to accommodate a 12 hour window once a month is the sign of an app not designed to scale well. 2. Calculating the balance on the fly would be more effective, as it is unlikley that many customers will check their balance on a regular basis. It is likely that a small fraction of customers will check their balance in a given month, meaning that calculating it on the fly would both spread the load over time as well as reduce the total load, even if on the fly calculation results in significantly higher per-customer calculation time. 3. The app is a web app, and it would be preferable to contain business logic within the database itself or the current app codebase. Spreading application logic into an external mechanism such as cron or an external daemon would be undesirable unless there was no other way. *** A Possible Solution *** Calculating the balance on the fly can be done easily if it is done at the time the customer seeks to redeem a voucher. Expired points are only relevant at these times, and so the expired points would be calculated with an application function that did the following: 1. Get the balance as it was 12 months ago by getting total points earned less redemptions and expiries up to that date. 2. Subtract from it redemptions and expiries since then. The value obtained, if it is positive, is the value of points to expire. 3. Log the expiry entry, and then calculate the balance of points to the current date by subtracting total points redeemed and expired from total points earned. This procedure has a few practical problems, however: 1. Customers, when they view their running total, will not be aware that some of the points included in it will have expired, as the expiry will only happen when the application attempts to log a redemption. 2. A customer may attempt to redeem a product they do not have enoughh points for, and be told at the last minute that they do not have enough points, leading to acrimony. The solution is then to calculate it on only on redemptions, but also whenever the customer attempts to view their balance. This will ensure that expired points will never be shown in the current balance of available points. This, however, means the calculation may be done many times by each customer in a single catalog browsing session. *** The Question *** Is there a way to design the DB schema as well as the query in such a manner that calculating the point balance on the fly is not an unfeasibly heavy duty calculation to be done at every page view? This problem does not appear to be solved comprehensively by anyone. When I log into my credit card company web site to check my points, I get a message "please come back in an hour, and your points will be calculated" if I haven't logged in for over a week. So obviously they calculate the balance and put it in a table that acts as a "cached balance". Emirates has a different solution, they do bi-annual runs, so points expire every March and September for them. Neither of these solutions appeals to me, and there must be A Better Way(tm). ---(end of broadcast)-
Re: [GENERAL] Creation of a read-only role.
Oh, sorry for the previous question - I can create a scheme-changes-disallowed role by revoking the "CREATE" permission from all the database schemas. The issue seems to be closed now, thanks. On 3/18/07, Dmitry Koterov <[EMAIL PROTECTED]> wrote: > actually - you dont need the read-only role, if this is only for slave > nodes. slony takes care about the issue and doesn't allow any writes > on slaves. Great! But what about a role which can modify the data, but cannot modify the database schema? On 3/17/07, hubert depesz lubaczewski <[EMAIL PROTECTED]> wrote: > > On 3/16/07, Dmitry Koterov <[EMAIL PROTECTED]> wrote: > > Overall, we need 3 roles: > > 1. Administrator: can do anything with a database (by default this > user is > > already exists - "postgres"). > > 2. Read-only: can only read. Runs on all slave nodes. > > actually - you dont need the read-only role, if this is only for slave > nodes. slony takes care about the issue and doesn't allow any writes > on slaves. > > depesz > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster >
Re: [GENERAL] Creation of a read-only role.
actually - you dont need the read-only role, if this is only for slave nodes. slony takes care about the issue and doesn't allow any writes on slaves. Great! But what about a role which can modify the data, but cannot modify the database schema? On 3/17/07, hubert depesz lubaczewski <[EMAIL PROTECTED]> wrote: On 3/16/07, Dmitry Koterov <[EMAIL PROTECTED]> wrote: > Overall, we need 3 roles: > 1. Administrator: can do anything with a database (by default this user is > already exists - "postgres"). > 2. Read-only: can only read. Runs on all slave nodes. actually - you dont need the read-only role, if this is only for slave nodes. slony takes care about the issue and doesn't allow any writes on slaves. depesz ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
