Re: [GENERAL] Time problem again?
Title: Nachricht No, not really perhaps you can do a ORDER BY (oid || yourtimefield). So you have the RecordOrder in the way the records where inserted. Daniel I need to sort some data based on a Time field and the times can cross both midnight and noon. As far as I can tell, there is no way to solve this without also supplying a date or am I missing something?Regards,BTJ
Re: [GENERAL] Time problem again?
Well, I don't.. But normal timespan is about 6-7 hours +- (so one can assume max timespan = 12 hours really...) BTJ On Mon, 2003-09-29 at 13:40, Richard Huxton wrote: On Monday 29 September 2003 12:26, Bjrn T Johansen wrote: The problem is that I don't have such a period. I can have a select containing these data: 2350 0110 0330 which then should be sorted like that. And I can also have the following: 1030 1145 1240 (also sorted as shown...) the only thing I know for sure, is that the interval between the first record and the last, is always less than 24 hours... And how do you know that the first example shouldn't have been 0110 0330 2350
Re: [GENERAL] Time problem again?
Title: Nachricht Yes, I could do that... I was just hoping to catch the odd times when records aren't inserted in order BTJ On Mon, 2003-09-29 at 13:44, Daniel Schuchardt wrote: No, not really perhaps you can do a ORDER BY (oid || yourtimefield). So you have the RecordOrder in the way the records where inserted. Daniel I need to sort some data based on a Time field and the times can cross both midnight and noon. As far as I can tell, there is no way to solve this without also supplying a date or am I missing something? Regards, BTJ
[GENERAL] Where are user-defined types stored/viewed
After I execute a command like CREATE TYPE employee_wage_journal_sum AS (supplier_pk integer, employee_pk integer, hourly_dollars double precision, annual_dollars double precision); where does this definition get stored, and what query can I run to get teh definition back as output? I don't see the new type show up anywhere in pgAdminII. The new type DOES show up in the result when I run select * from pg_type order by typname but I see only the name itself (and a lot of *id columns that probably reference something relevant), but I don't know how to get the actual definition back so that I can use an existing type definition as the basis for modification when application design changes are required. ~Berend Tober ---(end of broadcast)--- TIP 3: 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] Time problem again?
On Monday 29 September 2003 12:47, Bjørn T Johansen wrote: Well, I don't.. But normal timespan is about 6-7 hours +- (so one can assume max timespan = 12 hours really...) Well, if you don't know what order you want, how can you tell PG to show them in that order? I think you might want to log a full timestamp by the sound of it. I'm not sure your information is well defined. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] CASE tools
FYI I have found 2 CASE tools that support PostgreSQL CASE Studio 2 http://www.casestudio.com/enu/products.aspx DDS-Lite http://www.dds-lite.com/ However, they are both for Windows. Can you point me to others? Hopefully multi-platform ones. Another one is at http://www.danny.cz/datadesigner.en.html. It has not so many features and is still in development. Dan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] downloading latest source from cvs
Relaxin wrote: But how do you tell it that I only want what's tagged as WIN32_DEV? Thanks Bruce Momjian [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I think you need -rWIN32_DEV ok, I did as you suggested and I got only and everything that was tagged as WIN32_DEV, but I still have the following errors when compiling using the bcc32.mak file: try to compile the libpq library with bcc32.mak under bcb5, but have some problems when linked. The problems are about : 1._pqGethostbyname 2._pqStrerror can't referenced. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Edward
Hi!, First i'm a beginner working with php. And i've a problem working with that and postgresql of course, i cann't connect both. I've been working with debian sid, and like usually the webserver is apache; the error is something like thist: error cannot locale host:localhost..., but i try to do this: # psql -h localhost -U xxx -p 5432 -d test and i make connection. In test.php, i change localhost by 127.0.0.1, and nothing, the same error, and phpinfo() work. So is somebody help me. thanks! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] numeric rounding
hey guys..need your help on this.. i have a plpgsql function where in i compute numeric values for my php scripts.. my problem is my function just won't round some numbers properly.. what i want it to do is like this. example: 721.875 = 721.88721.865 = 721.87721.765 = 721.77721.775 = 721.78 here's my function which returns numeric(12,2): CREATE OR REPLACE FUNCTION fn_adjust_accum_dep(int4,int4,numeric(12,2)) RETURNS numeric(12,2) AS ' DECLARE fxamid ALIAS FOR $1;-- life is in monthslife ALIAS FOR $2;acqamt ALIAS FOR $3;depmonth int4;depyear int4;depdate date;lastdepdate date;dyear int4;dmon int4;manth int4;manthlife int4;depexpense numeric(12,2);salvagevalue float4;BEGIN SELECT EXTRACT(month FROM fxam_acquisition_date), EXTRACT(year FROM fxam_acquisition_date), fxam_dep_date, fxam_salvage_value/100 INTO depmonth, depyear, lastdepdate, salvagevalue FROM fixed_asset_master WHERE fxam_id = fxamid;-- for Month of December IF (depmonth = 12) THEN--Next yeardepyear := depyear + 1;--January the following yeardepmonth := 1;ELSEdepmonth := depmonth + 1;END IF;-- first depreciation date of property based on acquisition datedepdate := depmonth || ''/1/'' || depyear; -- RAISE NOTICE ''depdate = %'', depdate; -- get number of month and years from first depreciation date to last depreciation dateSELECT EXTRACT(month FROM AGE(lastdepdate,depdate::DATE)), EXTRACT(year FROM AGE(lastdepdate,depdate::DATE)) INTO dmon,dyear;-- RAISE NOTICE ''dmon=%,dyear=%,depdate=%'',dmon,dyear,depdate;-- Number of months to depreciatemanth := (dyear * 12) + dmon;-- Number of months of estimated lifemanthlife := life; -- Number of months to depreciate is greater than number of months of estimated life-- Only happens when property is encoded late and life is already consumed during first depreciation..IF ( dyear = 0 AND manth manthlife ) THEN-- Monthly depreciation expense Multiplied by number of month since Acquisition date depexpense := (acqamt - (acqamt * salvagevalue)) + 0.0001; RAISE NOTICE ''manth = % manthlife = %, depexpense=%'',manth, manthlife,depexpense;ELSE-- Monthly depreciation expense Multiplied by number of month since Acquisition date-- depexpense := ((acqamt - (acqamt * salvagevalue)) / life) * manth;depexpense := (((acqamt - (acqamt * salvagevalue)) / life) * manth) + 0.0001; -- sample data :--depexpense := (((2750.00 - (2750.00 * 0.1)) / 24 ) * 7) + 0.0001-- returns 721.87-- should return 721.88 -- RAISE NOTICE ''manth = % manthlife = %, depexpense=%'',manth, manthlife,depexpense; END IF; RETURN depexpense; END;'LANGUAGE 'plpgsql'; This function is up and running but my boss is such a great debugger.. ;) You can see I already added a value of 0.0001 to the computation but I also get the same results.. :( I know that numeric data type automatically rounds off values but how come it's not returning the right values??? Marie Gezeala M. Bacuño II IS DepartmentMuramoto Audio-Visual Phils., Inc.MEPZ1, Lapu-Lapu City, Cebu, Philippines 6015 The person with the ultimate cachinnation possesses, thereby, the optimal cachinnation. Do you Yahoo!? The New Yahoo! Shopping - with improved product search
Re: [GENERAL] Modification Dates
Hi again Got it, quite obvious too. The trigger has to be called BEFORE the UPDATE, not AFTER. (Hmmm, very obvious even.) CREATE TRIGGER _modified BEFORE UPDATE ON any_table FOR EACH ROW EXECUTE PROCEDURE touch(); That does the trick! Greets, -sven ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Rewriting pg_upgrade
[EMAIL PROTECTED] (Bruce Momjian) writes: Jim C. Nasby wrote: FWIW, FreeBSD just removed it (in the 5.x versions). Of course you can still easily install it from ports. Interesting. Why would they remove it? Because it's a REALLY BIG ball of mud to include as a core dependancy? Don't get me wrong; I have several Emacs buffers presently open to Perl programs; it's a _useful_ ball of mud. But big ball of mud it certainly is, and it seems unremarkable that there would be some reluctance to be dependent on it. There's a LOT of stuff going on with Perl (Parrot + Perl6), and for the FreeBSD folk to be reluctant to contract to all that change seems unsurprising. -- output = reverse(ofni.smrytrebil @ enworbbc) http://dev6.int.libertyrms.com/ Christopher Browne (416) 646 3304 x124 (land) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Modification Dates
Hi Nigel I suspect you're misunderstanding something about triggers, an on update trigger setting a such a field to the current timestamp shouldn't be causing a second update. You're actually doing an update statement within the trigger I presume? That's not the way, just set NEW.modified to the value you want, eg. the current timestamp. I'm doing it this way but I've read somewhere that this causes a second UPDATE. If that's not the case, the better! However, I seem to be missing something else. All my tables contain a column... modified TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() The following function exists... CREATE FUNCTION touch() RETURNS OPAQUE AS 'BEGIN new.modified = NOW(); RETURN ne w; END;' LANGUAGE 'plpgsql'; And all tables have the following trigger defined... CREATE TRIGGER _modified AFTER UPDATE ON any_table FOR EACH ROW EXECUTE PROCEDURE touch(); All this returned no errors. I do get a notice though: NOTICE: CreateTrigger: changing return type of function touch() from OPAQUE to TRIGGER I had the impression that now the modified-column should be set to the NOW() whenever an UPDATE is made on the row. That's not the case, the value remains unchanged. What's wrong with this? Your help is greatly apprechiated! -sven ---(end of broadcast)--- TIP 3: 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] downloading latest source from cvs
Relaxin wrote: Relaxin wrote: But how do you tell it that I only want what's tagged as WIN32_DEV? Thanks Bruce Momjian [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I think you need -rWIN32_DEV ok, I did as you suggested and I got only and everything that was tagged as WIN32_DEV, but I still have the following errors when compiling using the bcc32.mak file: try to compile the libpq library with bcc32.mak under bcb5, but have some problems when linked. The problems are about : 1._pqGethostbyname 2._pqStrerror can't referenced. Oh, WIN32_DEV is just for server Win32 development. Beta3 or CVS snapshot will give you a working win32 client end. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: 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] PostgreSQL SSL communication with SecureTcpClient (Ssl
Kris Jurka [EMAIL PROTECTED] writes: On Mon, 29 Sep 2003, Angel Todorov wrote: : An error occurs while communicating with the remote host. --- Org.Mentalis.Security.Ssl.Shared.SslException: The server hello message uses a protocol that was not recognized. Do you have any idea what can be the reason? Thanks in advance. You are aware that the connection does not start as a SSL connection, but begins as a normal one and then switches over. Specifically, you need to eat the initial S or N response byte from the server before firing up the SSL startup handshake. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Result set granularity..
Short answer, no there's no trick to doing this in postgres like rownum. You would have to either add a column with sequential or random keys and then select on it, or else pull down all the rows and only process the ones you want. Rownum is handy though, I wonder how easy it would be to add it to postgres. Rasmus Aveskogh [EMAIL PROTECTED] writes: SELECT column FROM table WHERE mod(rownum, 5) = 0; Uhm. Does that actually work? I thought rownum only incremented for every row actually returned. So that this would return one row and then stop returning rows. I would have thought you would have to use a subquery to get this to work like: SELECT * FROM (SELECT column, rownum AS n FROM table) WHERE mod(n,5)=0 The query above would give me every fifth row of the original result set and would save me from sending the data over my database connection and do the lowering of the granularity in the application. Also, in Oracle there's actually a SAMPLE keyword that you can put on a select to tell oracle that you only need a sample. It's way more efficient than using rownum because it skips whole blocks. Ie, the equivalent of above would be 'SAMPLE 20 PERCENT' or something like that, and it would read a whole block, then skip 4 whole blocks. However, for statistical purposes both of these techniques have downsides. Consider the case where you're looking for the standard deviation of some timing information and there's a spike every five minutes. The correlation between the sampling and the event could create spurious results. You could completely fail to see the events, or thing they are much worse than they are. Or even that they're worse at some times of day when in fact they're consistent. Really what you want is to assign statistically random numbers, probably floating point numbers, to each record, then read all records where those numbers are in some range. Even that might not really be kosher for serious statistics. -- greg ---(end of broadcast)--- TIP 3: 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] Seeking insight from heavy lifters
A manager friend of mine sent me the following concern. He's preparing to shift to Postgresql from a proprietary DB and 4gl system: --- To that end, I've also started studying up on Postgresql. It seems to have all the necessary features for a transaction heavy DB. The recent release is 7.3. Of course, the proof will be in the pudding. We average 2.5 million transactions per day or 800 per second. Unfortunately, we would have no way of testing that until we committed to getting the business logic moved over and had something to test it with. This is a bit of a catch 22 situation. Just wished I knew of someone locally who was running Postgresql in such a heavy environment. I'd love to find out how it performs for them. --- While I have a lot of experience with PG, it's not really been in a heavy processing environment. Could I get some input to send him from anyone out in the field using Postgres in a similar environment. If PG isn't the best option here, what is? Thanks very much for your input! John ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] downloading latest source from cvs
On Mon, Sep 29, 2003 at 10:57:20AM -0400, Bruce Momjian wrote: Relaxin wrote: try to compile the libpq library with bcc32.mak under bcb5, but have some problems when linked. Oh, WIN32_DEV is just for server Win32 development. Beta3 or CVS snapshot will give you a working win32 client end. You can use cvs update -r HEAD to get the latest tree for testing, outside WIN32_DEV. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) God is real, unless declared as int ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] numeric rounding
On Mon, Sep 29, 2003 at 12:47:05AM -0700, Gezeala 'Eyah' Bacuño II wrote: my problem is my function just won't round some numbers properly.. what i want it to do is like this. example: 721.875 = 721.88 a= select round(721.875, 2); round 721.88 (1 row) -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) La primera ley de las demostraciones en vivo es: no trate de usar el sistema. Escriba un guión que no toque nada para no causar daños. (Jakob Nielsen) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Edward
On Monday 29 September 2003 14:41, [EMAIL PROTECTED] wrote: Hi!, First i'm a beginner working with php. And i've a problem working with that and postgresql of course, i cann't connect both. I've been working with debian sid, and like usually the webserver is apache; the error is something like thist: error cannot locale host:localhost..., but i try to do this: # psql -h localhost -U xxx -p 5432 -d test and i make connection. In test.php, i change localhost by 127.0.0.1, and nothing, the same error, and phpinfo() work. Perhaps choose a more informative subject next time :-) This is almost certainly a typing error in your PHP connection string. It should be something like: $conn = pg_connect(host=localhost dbname=test user=test password=tpass); If your connect string is of that type, please give the *exact* error message you receive. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Access - can't close Form
Hi, i use Access as a frontend via ODBC to an Linux-based postgresql. I have a problem in a form where I use a lot of subforms. In some cases ( i couldn't detect when - but I really try to) the form and Access hangs. The only way to close the form is to close Access via cross (upper right corner). If I try then to view once again the form I get the following error message: Sie konnen diese Aktion momentan nicht ausfuhren means : You counldn't do this now. Has anybody an idea - why this is hanging or where i can start to debug. -Elmar ---(end of broadcast)--- TIP 3: 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] Seeking insight from heavy lifters
What are the transfering from, exactly? --- John Wells wrote: A manager friend of mine sent me the following concern. He's preparing to shift to Postgresql from a proprietary DB and 4gl system: --- To that end, I've also started studying up on Postgresql. It seems to have all the necessary features for a transaction heavy DB. The recent release is 7.3. Of course, the proof will be in the pudding. We average 2.5 million transactions per day or 800 per second. Unfortunately, we would have no way of testing that until we committed to getting the business logic moved over and had something to test it with. This is a bit of a catch 22 situation. Just wished I knew of someone locally who was running Postgresql in such a heavy environment. I'd love to find out how it performs for them. --- While I have a lot of experience with PG, it's not really been in a heavy processing environment. Could I get some input to send him from anyone out in the field using Postgres in a similar environment. If PG isn't the best option here, what is? Thanks very much for your input! John ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Where are user-defined types stored/viewed
[EMAIL PROTECTED] writes: After I execute a command like CREATE TYPE employee_wage_journal_sum AS (supplier_pk integer, employee_pk integer, hourly_dollars double precision, annual_dollars double precision); I don't see the new type show up anywhere in pgAdminII. Probably not. Stand-alone composite types (like the above) are a new feature in 7.3, and pgAdminII likely doesn't know about them. You might try pgAdmin III, which just got out of beta I believe. but I see only the name itself (and a lot of *id columns that probably reference something relevant), but I don't know how to get the actual definition back so that I can use an existing type definition as the basis for modification when application design changes are required. There's always pg_dump -s to extract such stuff. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Modification Dates
Sven Schwyn [EMAIL PROTECTED] writes: CREATE TRIGGER _modified AFTER UPDATE ON any_table FOR EACH ROW EXECUTE PROCEDURE touch(); You want BEFORE UPDATE here. AFTER UPDATE happens, well, after the update. :) -Doug ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Where is libpq++ on redhat 9?
Title: Where is libpq++ on redhat 9? Hello, I've installed redhat 9 on my box and postgresql (with it's -devel package) and found that it didn't contain libpq++. I tried a search on redhat and rpmfind with no luck (rpmfind found only suse packages). I know that I can download from gborg.org and compile it, but I'd prefeer the rpm packages. Does anyone knows where is it?
Re: [GENERAL] Seeking insight from heavy lifters
Bruce, A system called Progress, which is a 4GL/database combo I'm not really that familiar with. They're aware that Postgresql doesn't have a 4gl per se, but I think they view the savings they'll gain by switching to an open solution as well worth it. Thanks, John Bruce Momjian said: What are the transfering from, exactly? --- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] numeric rounding
On Mon, Sep 29, 2003 at 12:47:05AM -0700, Gezeala 'Eyah' BacuXo II wrote: You can see I already added a value of 0.0001 to the computation but I also get the same results.. :( Why 0.0001? If you want to round to the second digit by cutting the number you have to add 0.005. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(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
[GENERAL] Access - can't close Form
Hi, i use Access as a frontend via ODBC to an Linux-based postgresql. I have a problem in a form where I use a lot of subforms. In some cases ( i couldn't detect when - but I really try to) the form and Access hangs. The only way to close the form is to close Access via cross (upper right corner). If I try then to view once again the form I get the following error message: Sie konnen diese Aktion momentan nicht ausfuhren means : You counldn't do this now. Has anybody an idea - why this is hanging or where i can start to debug. -Elmar ---(end of broadcast)--- TIP 3: 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] Access - can't close Form
Hi, i use Access as a frontend via ODBC to an Linux-based postgresql. I have a problem in a form where I use a lot of subforms. In some cases ( i couldn't detect when - but I really try to) the form and Access hangs. The only way to close the form is to close Access via cross (upper right corner). If I try then to view once again the form I get the following error message: Sie konnen diese Aktion momentan nicht ausfuhren means : You counldn't do this now. Has anybody an idea - why this is hanging or where i can start to debug. -Elmar ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Seeking insight from heavy lifters
A manager friend of mine sent me the following concern. He's preparing to shift to Postgresql from a proprietary DB and 4gl system: --- To that end, I've also started studying up on Postgresql. It seems to have all the necessary features for a transaction heavy DB. The recent release is 7.3. Of course, the proof will be in the pudding. We average 2.5 million transactions per day or 800 per second. Unfortunately, we would have no way of testing that until we committed to getting the business logic moved over and had something to test it with. This is a bit of a catch 22 situation. Just wished I knew of someone locally who was running Postgresql in such a heavy environment. I'd love to find out how it performs for them. --- While I have a lot of experience with PG, it's not really been in a heavy processing environment. Could I get some input to send him from anyone out in the field using Postgres in a similar environment. If PG isn't the best option here, what is? Thanks very much for your input! John ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Conditional row grained + FK dependency oriented lazy replication
Hi, first off, i broadcast my case to as many people as possible, maybe off topic for many, but maybe as well of interest for some. Also i speak sort of DBMirror terminology ( a great and simple tool). The problem is as follows: Tables must be replicated to remote sites (1 master, many slaves). The tables are classified as 1) Non replicated (nothing is specified for them, and eventually nothing is done) 2) Replicated to all slaves 3) Conditionally replicated, so that a row is replicated only if it matches some criteria (usually a comparison of a column value against a remote site ID) 4) Implicitly replicated. This is the hard case. Nothing is explicitly declared for those tables, but the necessity of replicating them comes from the FK constraints that tables in case 3) point to. The problem with those tables is that we cannot know in advance if any future record in some table of case 3) will point to a record currently getting inserted into the db, and also there is a need for accounting info for those tables, since we want to know whether they got inserted at a remote site or not. Imagine we have a table parts with part numbers,descriptions,manuals,etc Now we have lets say 1 master db in the computer center, and many slaves for each retailer shop we have. Each shop maintains an inventory of items,supplies,etc... Each item has a FK to a part. (instance of a part). Now we dont want the huge ammount of parts to be replicated to each remote site, but only those parts, whose partno *will* be referenced by an item for a specific remote site. (That is we want to lazilly simulate the actions of a replication trigger as if we knew in advance that this part will be referenced by an item in the remote site). All i currently am aware off is DBMirror (i took a look at erserv, which didnt seem that spectacular), and i'd like to know if people have faced or solved this problem. In asynchronous situations where bandwidth cost is of primary concern (e.g.dial-up through satellite), i think that replicating useless data is a big loss. Any comments? Thanx. -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: achill ( at ) matrix ( dot ) gatewaynet ( dot ) com mantzios ( at ) softlab ( dot ) ece ( dot ) ntua ( dot ) gr ---(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
Re: [GENERAL] [SQL] Result set granularity..
On Sat, 27 Sep 2003, Rasmus Aveskogh wrote:
Hi,
Since I went from Oracle to PostgreSQL I've been missing the invisable
column 'rownum'. I often used it to lower the granularity of my data.
For example, say I have a large table containing some sort of statistical
data and want to plot a graph using it.
If the graph is 600 pixels wide I might as well lower the granularity of
my incoming data to 600 measure points before plotting.
In Oracle I used to do this by using the modulus operator on the rownum
column as a restriction.
SELECT column FROM table WHERE mod(rownum, 5) = 0;
The query above would give me every fifth row of the original result set
and would save me from sending the data over my database connection and do
the lowering of the granularity in the application.
I have two questions, one dependent on the answer on the other one..
1) Is it possible to achieve this any other _easy_ way?
Perhaps it would be feasible to write a wrapper using a counter which
makes the SELECT and then return every fifth row to the SELECT calling the
wrapper. But then I assume the data still has to travel one step which
puts on some overhead.
The standard trick in Postgresql is to create a temporary sequence and
select that as well:
create temp sequence aaa;
postgres=# select *, nextval('aaa') from accounts;
to get a row number. The sequence creation and use is pretty fast, and
I've used it before.
2) Would it be possible to add one more limit argument to the non-standard
set of LIMIT and OFFET - a 'SCALE n' or 'GRANUL n' argument which would
return every n row of the initial result set. I think that would be
gladly accepted for folks working with statistical data.
Using the same trick, you can get every 5th row like this:
select * from (select *, nextval('aaa') as row from accounts) as a where a.row%5=4;
---(end of broadcast)---
TIP 8: explain analyze is your friend
