Re: [HACKERS] btree_gist, gint4_union
Itai Zukerman wrote: In contrib/btree_gist/ I see: CREATE FUNCTION gint4_union(bytea, internal) RETURNS int4 AS 'MODULE_PATHNAME' LANGUAGE 'C'; but gint4_union does this: INT4KEY *out = palloc(sizeof(INT4KEY)); [...] PG_RETURN_POINTER(out); Is the int4 return type declared above a bug? Must be 'internal'. It's not a bug :) because this function is GiST interface member and itsn't any nessesarity to call it by hand. -- Teodor Sigaev [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] GIST_LEAF vs. leaf_key; gist?entryinit
gistPageAddItem doesn't used. Look at mail archive, it was a discussion about keep thes or not. Shortly: gistPageAddItem suppose to recompress entry, but we can't find any reason to do it. One more - gistPageAddItem works only with single-key indexes. Itai Zukerman wrote: In the examples I've seen, in the consistent method we have: if (GIST_LEAF(entry)) [...] but in compress we have: if (entry-leafkey) [...] I can see what the latter's doing, but I'm not sure what GIST_LEAF does, or why you'd want to use it. Also, I noticed this in src/backend/access/gist: static void gistcentryinit(GISTSTATE *giststate, int nkey, GISTENTRY *e, Datum k, Relation r, Page pg, OffsetNumber o, int b, bool l, bool isNull); and later (in gistPageAddItem): gistcentryinit(giststate, 0, tmpcentry, dentry-key, r, page, offsetNumber, dentry-bytes, FALSE); Isn't the call missing the isNull parameter? -- Teodor Sigaev [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] GiST: Bad newtup On Exit From gistSplit() ?
Send me 'x' file and I'll try to help you.
Itai Zukerman wrote:
(gdb) p (*newtup)[0]
$147 = {t_tid = {ip_blkid = {bi_hi = 0, bi_lo = 34}, ip_posid = 1}, t_info = 136}
(gdb) p (*newtup)[1]
$148 = {t_tid = {ip_blkid = {bi_hi = 65510, bi_lo = 65535}, ip_posid = 65535}, t_info
= 24575}
(gdb) p nlen
$149 = 2
(*newtup)[1] doesn't look right, correct?
Sorry, my fault, that should've been *(newtup[1]), which indeed looks
fine.
I'm still not having any luck tracking down my SEGV. If anyone is
interested in debugging or even just reproducing this, I'd be happy to
post or E-mail you my code. It looks like shortly after calling
gistSplit() there's maybe a double-free() somewhere? Here's a typical
session; the NOTICEs are generated by my picksplit function:
$ gdb ./postgres
(gdb) run -D /var/lib/postgres/data test x
[... output as many tuples are inserted, and finally ...]
NOTICE: g_sig_picksplit: --
NOTICE: g_sig_picksplit: left ( 9)
*.*.***..*..****.***.**.*..*.*.*..**..**.*...***..*.*.*.***..*.**..**.*..*.**.*..**.***.*...*.**.**...*..*****..*.***.*..*.*..****.*..*..**.*..*.*.***.*.*.**.**.**..*...*..*.*.*.*...*..*.*...*..**..*..******.*.*.*..*...**...***.*.*.*..*.*..***.*..*.**..**...*.**..**..**.***...*..*..**.*.*.*.*..***...*.***.****..**.***.*..***.*.*...*..**.*...*...*...*...*.*.**..*.
...
NOTICE: g_sig_picksplit: right ( 50) .**...*....***.**..*.*...***.**.***.*..**.*.***..
...
Program received signal SIGSEGV, Segmentation fault.
0x4028e2ff in mallopt () from /lib/libc.so.6
(gdb) bt
#0 0x4028e2ff in mallopt () from /lib/libc.so.6
#1 0x4028d2ca in free () from /lib/libc.so.6
#2 0x081d05e5 in AllocSetDelete (context=0x82ae098) at aset.c:460
#3 0x081d0f03 in MemoryContextDelete (context=0x82ae098) at mcxt.c:188
#4 0x081d0f36 in MemoryContextDeleteChildren (context=0x82ae010) at mcxt.c:207
#5 0x081d0e8f in MemoryContextDelete (context=0x82ae010) at mcxt.c:161
#6 0x08094070 in AtCommit_Memory () at xact.c:685
#7 0x080943e6 in CommitTransaction () at xact.c:1033
#8 0x080946b6 in CommitTransactionCommand (forceCommit=0 '\0') at xact.c:1304
#9 0x08160966 in finish_xact_command (forceCommit=0 '\0') at postgres.c:978
#10 0x081607ef in pg_exec_query_string (query_string=0x82e4570, dest=Debug,
parse_context=0x82adf88) at postgres.c:897
#11 0x081619f0 in PostgresMain (argc=4, argv=0x82960c0, username=0x82966c0 postgres)
at postgres.c:2013
#12 0x08110625 in main (argc=4, argv=0xbc14) at main.c:235
--
Teodor Sigaev
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Yet another open-source benchmark
OSDL has just come out with a set of open-source database benchmarks: http://www.osdl.org/projects/performance/ The bad news: This tool kit works with SAP DB open source database versions 7.3.0.23 or 7.3.0.25. (In fact, they seem to think they are testing kernel performance, not database performance, which strikes me as rather bizarre. But anyway.) That may be a terminology thing; the main SAP-DB process is called the kernel, and it's more than likely that the SAP-DB Kernel is the sense in which the term is being used. When they translate things from German, sometimes wordings change :-). -- output = reverse(moc.enworbbc@ enworbbc) http://www.ntlug.org/~cbbrowne/linuxxian.html Rules of the Evil Overlord #41. Once my power is secure, I will destroy all those pesky time-travel devices. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Yet another open-source benchmark
On the results page they list kernels like linux-2.4.18-1tier or linux-2.4.19-rc2 or redhat-stock-2.4.7-10cmp. This sounds really like linux-kernel-versions. Am Montag, 3. März 2003 13:41 schrieb [EMAIL PROTECTED]: OSDL has just come out with a set of open-source database benchmarks: http://www.osdl.org/projects/performance/ The bad news: This tool kit works with SAP DB open source database versions 7.3.0.23 or 7.3.0.25. (In fact, they seem to think they are testing kernel performance, not database performance, which strikes me as rather bizarre. But anyway.) That may be a terminology thing; the main SAP-DB process is called the kernel, and it's more than likely that the SAP-DB Kernel is the sense in which the term is being used. When they translate things from German, sometimes wordings change :-). -- output = reverse(moc.enworbbc@ enworbbc) http://www.ntlug.org/~cbbrowne/linuxxian.html Rules of the Evil Overlord #41. Once my power is secure, I will destroy all those pesky time-travel devices. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Dr. Eckhardt + Partner GmbH http://www.epgmbh.de ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Testing ... ignore ...
---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] problem importing languages in CVS tip
Are we supposed to report bugs with the CVS tip of PostgreSQL, or are we to assume that the developers are well aware of problems there and are already working on them? After my most recent CVS update I find that I cannot run createlang either to import the plpgsql nor the plpython languages - both attempts give the error message: ERROR: index_cost_estimator: invalid amcostestimate regproc createlang: external error at the client and ERROR: index_cost_estimator: invalid amcostestimate regproc on the server end. Hmm. Now it is giving me that error even when I attempt a simple `psql -l' which makes me wonder if I have done something horribly wrong. Does anyone else see this? -- Brandon Craig Rhodes http://www.rhodesmill.org/brandon Georgia Tech[EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] problem importing languages in CVS tip
Tom Lane [EMAIL PROTECTED] writes: [I do not see that error] here. Try a full recompile. (Unless you configure with --enable-depend, a make distclean is a smart move during *any* update from CVS.) An initdb followed by rebuilding my tables fixed the problem. -- Brandon Craig Rhodes http://www.rhodesmill.org/brandon Georgia Tech[EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] XML ouput for psql
[EMAIL PROTECTED] writes: I don't agree with this: XML and XHTML are two different things. No one claimed anything to the contrary. We could certainly upgrade the HTML portion, but I am pretty sure that the XML standard calls for this format: columnnamedata here/columnname The XML standard does not call for any table format. But a number of table formats have been established within the XML framework. Some of them are formatting-oriented (e.g., the HTML model, or CALS which is used in DocBook) and some of them are processing-oriented (e.g., SQL/XML). Which do we need? And which do we need from psql in particular (keeping in mind that psql is primarily for interactive use and shell-scripting)? In any case, it should most likely be a standard table model and not a hand-crafted one. (If, for whatever reason, we go the processing-oriented route, then I claim that there should not be a different output with and without \x mode.) -- Peter Eisentraut [EMAIL PROTECTED] ---(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: [HACKERS] Yet another open-source benchmark
On Mon, 3 Mar 2003, Tommi Maekitalo wrote: On the results page they list kernels like linux-2.4.18-1tier or linux-2.4.19-rc2 or redhat-stock-2.4.7-10cmp. This sounds really like linux-kernel-versions. Am Montag, 3. März 2003 13:41 schrieb [EMAIL PROTECTED]: OSDL has just come out with a set of open-source database benchmarks: http://www.osdl.org/projects/performance/ The bad news: This tool kit works with SAP DB open source database versions 7.3.0.23 or 7.3.0.25. (In fact, they seem to think they are testing kernel performance, not database performance, which strikes me as rather bizarre. But anyway.) That may be a terminology thing; the main SAP-DB process is called the kernel, and it's more than likely that the SAP-DB Kernel is the sense in which the term is being used. When they translate things from German, sometimes wordings change :-). -- output = reverse(moc.enworbbc@ enworbbc) http://www.ntlug.org/~cbbrowne/linuxxian.html Rules of the Evil Overlord #41. Once my power is secure, I will destroy all those pesky time-travel devices. http://www.eviloverlord.com/ I think they are testing how tuning the linux kernel impacts the database running on top, at least that's the feeling I got from the site. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] bug? rules fail to cascade after NOT IN
We have verified this problem under both 7.3.2 and the CVS tip. The attached example is far simpler than the actual code in our application, but may nevertheless benefit from some explanation. We have several tables with two ON INSERT rules: [TABLE policy_accounts] | | ON INSERT V RULE executor_active - [TABLE accounts_on_hold] | V [TABLE policy_accounts_active] | | ON INSERT V RULE executor_hamlet - [TABLE policy_hamlet_atoms] | V [TABLE account_instances] The accounts_on_hold lists user accounts for which no changes or updates should currently be made; so the the executor_active rule copies new rows from policy_accounts to policy_accounts_active only for accounts which are not listed in accounts_on_hold. Our system manages computer accounts across a university campus. Since a given account might exist on several machines, we have given the name `account instance' to the idea of an account on a particular system - so if the account `tgl' were given access to both a Linux interactive machine and a web email server, we would say that two `instances' of the account currently existed. The policy_hamlet_atoms table lists, for each policy, which systems the policy grants access to. So when a new row appears in policy_accounts_active, linking an account to a new policy, the executor_hamlet rule examines the policy_hamlet_atoms table and creates any new account_instances that are necessary. (Thus hamlet decides which account instances are to be, or not to be.) Our problem is that if we add a NOT IN clause to the executor_active rule to prevent duplicate inserts into the policy_accounts_active table, then the executor_hamlet rule will not fire for rows inserted by the executor_active rule. While we are beginning to learn how to read parse trees, we are not yet proficient enough to see what is going on here. Two test cases are attached. The `broken' test case has the NOT IN clause, and incorrectly does *not* create an account_instances row when executor_active does an INSERT to policy_accounts_active. The test code then goes on to delete and re-insert the row by hand, showing that executor_hamlet *will* fire if the INSERT to policy_accounts_active is not done through executor_active. The `working' test case omits the AND (account, policy) NOT IN (SELECT account, policy FROM policy_accounts_active) condition from the end of executor_active, which magically makes the executor_hamlet rule start firing as it should. Any help or correction of our techniques will be appreciated! test-broken.sql Description: Binary data test-working.sql Description: Binary data -- Brandon Craig Rhodes http://www.rhodesmill.org/brandon Georgia Tech[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Yet another open-source benchmark
On Mon, 2003-03-03 at 07:41, [EMAIL PROTECTED] wrote: (In fact, they seem to think they are testing kernel performance, not database performance, which strikes me as rather bizarre. But anyway.) That may be a terminology thing; the main SAP-DB process is called the kernel, and it's more than likely that the SAP-DB Kernel is the sense in which the term is being used. Actually, I believe the reason the benchmark was developed was to provide a workload for optimizing high-end Linux kernel performance (with the inference being that SAP-DB is pretty close to Oracle, Oracle performance is important for enterprise deployment of Linux, and therefore optimizing the kernel's handling of SAP-DB running TPC benchmarks will tend to improve the kernel's performance running Oracle/DB2/etc.) So when they mean kernel, I think they really mean kernel. That's not to say that the benchmark wouldn't be useful for doing other stuff, like pure database benchmarks (as long as its a valid implementation of TPC-C (or TPC-H, etc.), it should be fine...) A research group at the university I attend (www.queensu.ca) expressed some interested in a TPC-C implementation for PostgreSQL, so I was planning to port the OSDL TPC-C implementation to PostgreSQL. Unfortunately, I got sidetracked for a couple reasons: (1) lack of time (2) increasing awareness of just how boring writing benchmark apps is :-) (3) distaste for ODBC. While I'd like to get some time to do the port in the future, that shouldn't stop anyone else from doing so in the mean time :-) Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
FW: [HACKERS] [PATCHES] XML ouput for psql
-Original Message- From: Merlin Moncure Sent: Monday, March 03, 2003 3:47 PM To: 'Peter Eisentraut' Subject: RE: [HACKERS] [PATCHES] XML ouput for psql My 0.2$: keep the xml formatting rules as simple as possible and rely on xslt to do the document markup (going out) and schemas/xslt to do the validation (going in). This would allow flexibility for any type of application. Without that stuff just do very basic columndata/column. There are many excellent free tools for xml manipulation which imho are the best thing about xml. Xml datasets that are not nested tend to get verbose :( Merlin -Original Message- From: Peter Eisentraut [mailto:[EMAIL PROTECTED] Sent: Monday, March 03, 2003 12:55 PM To: [EMAIL PROTECTED] Cc: PostgreSQL Development Subject: Re: [HACKERS] [PATCHES] XML ouput for psql [EMAIL PROTECTED] writes: I don't agree with this: XML and XHTML are two different things. No one claimed anything to the contrary. We could certainly upgrade the HTML portion, but I am pretty sure that the XML standard calls for this format: columnnamedata here/columnname The XML standard does not call for any table format. But a number of table formats have been established within the XML framework. Some of them are formatting-oriented (e.g., the HTML model, or CALS which is used in DocBook) and some of them are processing-oriented (e.g., SQL/XML). Which do we need? And which do we need from psql in particular (keeping in mind that psql is primarily for interactive use and shell-scripting)? In any case, it should most likely be a standard table model and not a hand-crafted one. (If, for whatever reason, we go the processing-oriented route, then I claim that there should not be a different output with and without \x mode.) -- Peter Eisentraut [EMAIL PROTECTED] ---(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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Yet another open-source benchmark
On Mon, 2003-03-03 at 12:29, Neil Conway wrote: On Mon, 2003-03-03 at 07:41, [EMAIL PROTECTED] wrote: (In fact, they seem to think they are testing kernel performance, not database performance, which strikes me as rather bizarre. But anyway.) That may be a terminology thing; the main SAP-DB process is called the kernel, and it's more than likely that the SAP-DB Kernel is the sense in which the term is being used. Actually, I believe the reason the benchmark was developed was to provide a workload for optimizing high-end Linux kernel performance (with the inference being that SAP-DB is pretty close to Oracle, Oracle performance is important for enterprise deployment of Linux, and therefore optimizing the kernel's handling of SAP-DB running TPC benchmarks will tend to improve the kernel's performance running Oracle/DB2/etc.) So when they mean kernel, I think they really mean kernel. Yeah, Neil more-or-less hit it on the nose. The SAP DB folks do refer to their processes as kernel processes, but our focus is on the Linux kernel and helping Linux gain more ground for the enterprise. That's not to say that the benchmark wouldn't be useful for doing other stuff, like pure database benchmarks (as long as its a valid implementation of TPC-C (or TPC-H, etc.), it should be fine...) A research group at the university I attend (www.queensu.ca) expressed some interested in a TPC-C implementation for PostgreSQL, so I was planning to port the OSDL TPC-C implementation to PostgreSQL. Unfortunately, I got sidetracked for a couple reasons: (1) lack of time (2) increasing awareness of just how boring writing benchmark apps is :-) (3) distaste for ODBC. While I'd like to get some time to do the port in the future, that shouldn't stop anyone else from doing so in the mean time :-) And we're prepared to aid any effort. :) Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Mark Wong - - [EMAIL PROTECTED] Open Source Development Lab Inc - A non-profit corporation 15275 SW Koll Parkway - Suite H - Beaverton OR, 97006 (503)-626-2455 x 32 (office) (503)-626-2436 (fax) http://www.osdl.org/archive/markw/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [PATCHES] XML ouput for psql
[EMAIL PROTECTED] writes: I don't agree with this: XML and XHTML are two different things. No one claimed anything to the contrary. We could certainly upgrade the HTML portion, but I am pretty sure that the XML standard calls for this format: columnnamedata here/columnname The XML standard does not call for any table format. But a number of table formats have been established within the XML framework. Some of them are formatting-oriented (e.g., the HTML model, or CALS which is used in DocBook) and some of them are processing-oriented (e.g., SQL/XML). Which do we need? And which do we need from psql in particular (keeping in mind that psql is primarily for interactive use and shell-scripting)? In any case, it should most likely be a standard table model and not a hand-crafted one. I would expect XML output to be based on whatever the tree of data contained. If the tree is to be rewritten, then this would mean having some sort of transformation engine in PostgreSQL that you would have to program. If I want a CALS table, then I'll push CALS table data into the database. If I'm storing a GnuCash chart of accounts in PostgreSQL, I am ludicrously uninterested in seeing it rewritten for some sort of physical layout. Spit out the tags that are stored in the database, not some rewriting of it. -- (reverse (concatenate 'string moc.enworbbc@ enworbbc)) http://cbbrowne.com/info/linuxdistributions.html (1) Sigs are preceded by the sigdashes line, ie \n-- \n (dash-dash-space). (2) Sigs contain at least the name and address of the sender in the first line. (3) Sigs are at most four lines and at most eighty characters per line. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [PATCHES] ALTER SEQUENCE
Hey, with this new ALTER SEQUENCE patch, how about this for an idea:
I submitted a patch to always generate non-colliding index and sequence
names. Seemed like an excellent idea. However, 7.3 dumps tables like this:
CREATE TABLE blah
a SERIAL
);
SELECT SETVAL('blah_a_seq', 10);
Sort of thing...
How about we add a new form to ALTER SEQUENCE sequence ...?
ALTER SEQUENCE ON blah(a) CURRVAL 10 (or whatever the syntax is)
or even
ALTER SERIAL ON blah(a)...
Which would allow us to dump tables in an environment where you do now know
exactly what the generated name will be...
Chris
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] problem importing languages in CVS tip
Hi Brandon, Are we supposed to report bugs with the CVS tip of PostgreSQL, or are we to assume that the developers are well aware of problems there and are already working on them? You're supposed to report them! If we were aware of the problems, we'd fix them ;) Cheers, Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] bug? rules fail to cascade after NOT IN
Brandon Craig Rhodes [EMAIL PROTECTED] writes: The `working' test case omits the AND (account, policy) NOT IN (SELECT account, policy FROM policy_accounts_active) condition from the end of executor_active, which magically makes the executor_hamlet rule start firing as it should. I don't think this is a bug. The executor_hamlet rule fires after the executor_active rule does; therefore the (account, policy) pair *has already been inserted into policy_accounts_active*, and will be found when executor_hamlet re-executes the select to look for it. My advice to you is to use triggers, not rules, for pushing data from one table to another; especially when you need logic this complex to decide what to do. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] bug? rules fail to cascade after NOT IN
Tom Lane [EMAIL PROTECTED] writes: Brandon Craig Rhodes [EMAIL PROTECTED] writes: The `working' test case omits the AND (account, policy) NOT IN (SELECT account, policy FROM policy_accounts_active) condition from the end of executor_active, which magically makes the executor_hamlet rule start firing as it should. I don't think this is a bug. The executor_hamlet rule fires after the executor_active rule does; therefore the (account, policy) pair *has already been inserted into policy_accounts_active*, and will be found when executor_hamlet re-executes the select to look for it. This was for me neither intuitive nor clear from the documentation; I had assumed that the NEW relation of a rule always contained the same rows that were inserted, updated, or deleted by the query that wound up invoking the rule. If I understand your assertion, NEW does *not* in fact refer strictly to the rows that (in this case) were INSERTed by the upstream query; rather, NEW refers to a re-invocation - a copy or re-execution - of the query which produced the NEW rows themselves. So if the query which produced the NEW rows has side effects which affect its own outcome when it is re-evaluated, then the rule will not be working on the same rows at all? This is also troubling because it may mean that rules are less efficient than I had imagined. Having assumed that rules following (say) an INSERT used for NEW exactly the same set of rows that were inserted, then PostgreSQL could get away with only executing that query once and using the resulting rows for every rule fired by the INSERT. But if the original relation is entirely re-executed with the possibility of a different outcome then it is not clear that several rules could all share the results of the original query, vastly reducing the efficiency of several rules cascaded from one another. Forgive me for complaining about something that has probably been a fundamental part of the design since the first Postgres, but in my misunderstanding I had been looking forward to a cascade of rules cleanly and efficiently sharing the same set of, say, one thousand INSERTed rows as they propagated its results through our tables. My advice to you is to use triggers, not rules, for pushing data from one table to another; especially when you need logic this complex to decide what to do. We had hoped, by casading about a dozen rules through tables of this sort, to allow PostgreSQL to optimize our entire dataflow as a whole rather than making dozens or hundreds or little trigger invocations when sets of accounts are created or deleted. Thanks so much for spending some time helping us to think straight, :-) -- Brandon Craig Rhodes http://www.rhodesmill.org/brandon Georgia Tech[EMAIL PROTECTED] ---(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: [HACKERS] bug? rules fail to cascade after NOT IN
Brandon Craig Rhodes [EMAIL PROTECTED] writes: If I understand your assertion, NEW does *not* in fact refer strictly to the rows that (in this case) were INSERTed by the upstream query; rather, NEW refers to a re-invocation - a copy or re-execution - of the query which produced the NEW rows themselves. That's about the size of it. A rule is a macro, and so is NEW (or OLD). While rule-as-macro works beautifully for views, I've never been entirely satisfied with it for updating queries. Hardly anyone is able to wrap their minds around the behavior, and all too often the only workable solution is to use triggers instead --- which, as you say, could be a performance loss when many rows have to be processed. Even if it's not a performance loss, one comes away wondering whether the rule system is really doing the most useful thing. It would take a rather fundamental redesign of the rule system to do differently, though. Are you volunteering? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] bug? rules fail to cascade after NOT IN
Tom Lane [EMAIL PROTECTED] writes: While rule-as-macro works beautifully for views, I've never been entirely satisfied with it for updating queries. ... It would take a rather fundamental redesign of the rule system to do differently, though. Are you volunteering? From what I have seen of the planner, its plans look like a funnel - operator results are repeatedly combined until the result narrows to exactly the rows specified by the user. But if NEW and OLD are to truly become the tuples that were inserted, updated, or deleted, then plans might being to look like trees that, after combining into a trunk, start branching out again to form roots - because the resulting rows will not only be, say, inserted into a table, but might be shared between several rules that will then have their own operations to perform upon the result. And yes, I am volunteering. :-) -- Brandon Craig Rhodes http://www.rhodesmill.org/brandon Georgia Tech[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Postgresql performace question
The real question is, the data collection is in real-time, but can you have a maintenance window (6 hours a week?) to do things like REINDEX? The database has to have the last 24 hours of data online and be acessable 24 hours a day 7 days a week 365 days a year basicly no downtime. My application is weather related system, which must be avaliable at any moment. Will the data purge be spread over time or come in large lumps? The database that is online would only contain the last 24 hours of data. Once 24 hours of data has been collected the data would begin to be purged say purging of 1 hours worth of data every hour. But if it's 20 packets with parts being inserted into 50 tables (now 1000 inserts / second) things could become a little trickier -- but still easily doable. No there will be only one table with each packet having its own record. Each packet contains a few header fields that I would like to be able to search on. The rest of the data is purely binary data that goes along with the header. I no intertest in searching through it only being able to retrive it based on its header. One important way of retriving data would be based on time saying that every packet has a timestamp, I would like to select a range of records out of the database. There maybe cases were I want to stream the data back out just the way as it was recorded. Can you use a parallel loader or does it need to be loaded sequentially? I am not exactly sure what you mean by parallel loader but the data would be loaded sequentially into the database just as I receive it from my external device. As mentioned above I may want to be able to sequentially read records from the database to be able to recreate the data stream that I received and recored. I would need to be able to read the records back fast enough to replay the at the same rate at which I received them. Thanks Again Mark On Sunday 02 March 2003 06:35 pm, Rod Taylor wrote: On Sun, 2003-03-02 at 18:52, Mark Jones wrote: Hello I am working on a project that acquires real-time data from an external device that I need to store and be able to search through and retrieve quickly. My application receives packets of data ranging in size from 300 to 5000 bytes every 50 milliseconds for the minimum duration of 24 hours before the data is purged or archived off disk. There are several fields in the data that I like to be able to search on to retrieve the data at later time. By using a SQL database such as Postgresql or Mysql it seams that it would make this task much easier. My questions are, is a SQL database such as Postgresql able to handle this kind of activity saving a record of 5000 bytes at rate of 20 times a second, also how well will it perform at searching through a database which contains nearly two million records at a size of about 8 - 9 gigabytes of data, assuming that I have adequate computing hardware. I am trying to determine if a SQL database would work well for this or if I need to write my own custom database for this project. If anyone has any experience in doing anything similar with Postgresql I would love to know about your findings. Not such a big load depending on the complexity of the queries being performed. From the sounds of it, they're pretty simple. The real question is, the data collection is in real-time, but can you have a maintenance window (6 hours a week?) to do things like REINDEX? Can you use a parallel loader or does it need to be loaded sequentially? Will the data purge be spread over time or come in large lumps? Simple queries on 2 million records happen in low double digit milliseconds on relatively low end hardware with a decent index -- but complex queries can take minutes on 1/1th the dataset. 20 inserts / second shouldn't be difficult to achieve even on the lowest end hardware -- my laptop can do that -- giving each packet it's own transaction. But if it's 20 packets with parts being inserted into 50 tables (now 1000 inserts / second) things could become a little trickier -- but still easily doable. The most important question is the maintenance window. -- Mark Jones http://www.hackerjones.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Strange crash with pg_dump
Hi everyone. Environment: OS: UW 713 PG: 7.3.2 I've been banging my head for a few weeks on this one and could'nt find any answer: pg_dump is crashing with SIGSEGV before it connects to database. I've re-compiled with --enable-debug and --enable-cassert and even that did'nt help. this is gdb output not showing much: Script started on Mon Mar 3 13:26:51 2003 ~ 13:26:51: gdb /usr/local/pgsql/bin/pg_dump /archives/dbbackup/core.28913 GNU gdb 5.3 Copyright 2002 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type show copying to see the conditions. There is absolutely no warranty for GDB. Type show warranty for details. This GDB was configured as i686-unknown-sysv5UnixWare7.1.3... Core was generated by `pg_dump forums_ahe'. Program terminated with signal 11, Segmentation fault. Reading symbols from /usr/local/pgsql/lib/libpq.so.3...done. Loaded symbols for /usr/local/pgsql/lib/libpq.so.3 Reading symbols from /usr/lib/libz.so...done. Loaded symbols for /usr/lib/libz.so Reading symbols from /usr/local/lib/libreadline.so.3...done. Loaded symbols for /usr/local/lib/libreadline.so.3 Reading symbols from /usr/lib/libresolv.so.2...done. Loaded symbols for /usr/lib/libresolv.so.2 Reading symbols from /usr/lib/libsocket.so.2...done. Loaded symbols for /usr/lib/libsocket.so.2 Reading symbols from /usr/lib/libnsl.so.1...done. Loaded symbols for /usr/lib/libnsl.so.1 Reading symbols from /usr/lib/libdl.so.1... warning: Lowest section in /usr/lib/libdl.so.1 is .hash at 00a0 done. Loaded symbols for /usr/lib/libdl.so.1 Reading symbols from /usr/lib/libm.so.1...done. Loaded symbols for /usr/lib/libm.so.1 Reading symbols from /usr/lib/libc.so.1...done. Loaded symbols for /usr/lib/libc.so.1 Reading symbols from /usr/lib/ns.so.1...done. Loaded symbols for /usr/lib/ns.so.1 #0 0xbffa3dd0 in flushsmall () from /usr/lib/libc.so.1 (gdb) bt #0 0xbffa3dd0 in flushsmall () from /usr/lib/libc.so.1 #1 0x0030 in ?? () (gdb) ~ 13:27:44: exit script done on Mon Mar 3 13:27:48 2003 If I vacuum the databse it starts to work again until the script doing backup runs then pg_dump crashes again. This is a test system so databases are not even accessed! They are pulled from the real server running 7.2.3 with pg_dumpall (the 7.3 one) -h server -c|psql template1 It doesn't seem to be a database issue because it used to crash on another one. While I'm at it : what is the procedure to remove template1 and replace it by template0 after a reload? It doesn't seem right to me that pg_dumpall replace template1 but maybe I'm wrong on this one. Thanks for your tim and help -- Olivier PRENANT Tel:+33-5-61-50-97-00 (Work) Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
