Re: [HACKERS] Design notes for EquivalenceClasses
On Thu, 2007-01-18 at 11:53 +1100, Gavin Sherry wrote: the major rule in the executor: do what ever the plan tells you to do. I thought the rule was: do what the plan tells you to do, as efficiently as possible. Turning an explicit step into a no-op seems like great execution to me. In the case you mention, the HashJoin node already looks inside its Hash node child. It seems possible to have the Sort node check at ExecInit time that it is sitting on a HashJoin node, so that at Exec time it can ask the HashJoin node whether the Hash node has spilled to disk or not. If not, it can just pass the rows through as a no-op. We could formalise a last words API so that parent nodes sometimes calls child nodes before they execute them, or maybe just leave it somewhat dirty as the H/HJ communication is now. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] ideas for auto-processing patches
[EMAIL PROTECTED] wrote: One thing: the patch server will have to run over HTTPS - that way we can know that it is who it says it is. Right, I'm not sure if the computer I'm proofing it on is the best place for it so I didn't bother with the HTTPS, but should be trivial to have it. Yes, this was more by way of a don't forget this note. The implementation can be happily oblivious of it, other than setting https in the proxy for the SOAP::Lite dispatcher. From a buildfarm point of view, we would add such SOAP params into the config file. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] What is the motivation of include directive and
Takayuki Tsunakawa wrote: From: Andrew Dunstan [EMAIL PROTECTED] Meeting FHS requirements is no bad thing, though. And the ability to include a common configuration set in multiple instances is surely useful to a number of people. After all, you aren't forced to use these facilities - I typically don't. Thank you, Andrew-san. What I want to know is a more concreet thing. How useful are those facilities to what kind of users in what cases? Is there a reason why users in the real world positively use those facilities? If you want to find out about usage patterns this is probably not the best place to survey users - the hackers are not very representative of users in general. I have a sneaking suspicion that include directives are not used a whole lot, but alternative file locations are much more widely used. My off-the-cuff advice about your tool would be to process any include directives when reading the config file, but initially just write out new settings to the top level file (at the end, so they don't get overridden by the includes). cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Temparary disable constraint
Maybe you are right as technically but this behaver causes a lot of maintance problem on a database that alot of view and functions that depends on a table or a type. I think objects has a property if that object is enable or not. We hope this problem can be resolved at 8.4 release. Best Regards Adnan DURSUN ASRIN Bilisim Ltd. - Original Message - From: Bruce Momjian [EMAIL PROTECTED] To: Adnan DURSUN [EMAIL PROTECTED] Cc: pgsql-hackers@postgresql.org Sent: Wednesday, January 17, 2007 5:49 PM Subject: Re: [HACKERS] Temparary disable constraint Adnan DURSUN wrote: Hi, Maybe added more further things to TODO list. Enabled / disabled other objects like view/funtion. imagine a lot of views that referances a table and i wanna drop a column on this table that used by these views. Postgres doesnt allow this. First i must drop these views then drop the column on that table and then recreate these views. Can this be resolved (like oracle does) ? Not easily, because the view are bound to the object id of the tables involved. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Scanner/Parser question - what does _P imply?
I can't find an authoritative answer to this question. Many of the keywords listed in keywords.c are defined with symbolic names that end in '_P' (underscore P). What differentiates those keywords from the other keywords? What does the 'P' stand for? Are those PostgreSQL-specific keywords (i.e. keywords not defined by the SQL standard)? Thanks. -- Korry -- Korry Douglas[EMAIL PROTECTED] EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Scanner/Parser question - what does _P imply?
[EMAIL PROTECTED] writes: Many of the keywords listed in keywords.c are defined with symbolic names that end in '_P' (underscore P). What differentiates those keywords from the other keywords? What does the 'P' stand for? P = Parser. The reason for the _P is just to avoid conflicts with other definitions of the macro name, either in our own code or various platforms' header files. We haven't been totally consistent about it, but roughly speaking we've stuck _P on when it was either known or seemed likely that there might be a conflict. Some years ago there was discussion of consistently P-ifying *all* those macros, but it didn't get done; I think Thomas or somebody objected that it would make gram.y needlessly harder to read. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Temparary disable constraint
Bruce Momjian wrote: Adnan DURSUN wrote: Hi, Maybe added more further things to TODO list. Enabled / disabled other objects like view/funtion. imagine a lot of views that referances a table and i wanna drop a column on this table that used by these views. Postgres doesnt allow this. First i must drop these views then drop the column on that table and then recreate these views. Can this be resolved (like oracle does) ? Not easily, because the view are bound to the object id of the tables involved. The trick would be I think to bind them to individual columns, so if view V doesn't mention column C then dropping C has no effect on it. That's a lot more dependencies to track of course. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Scanner/Parser question - what does _P imply?
P = Parser. The reason for the _P is just to avoid conflicts with other definitions of the macro name, either in our own code or various platforms' header files. We haven't been totally consistent about it, but roughly speaking we've stuck _P on when it was either known or seemed likely that there might be a conflict. Some years ago there was discussion of consistently P-ifying *all* those macros, but it didn't get done; I think Thomas or somebody objected that it would make gram.y needlessly harder to read. Ahhh... now it's clear. Thanks. -- Korry
Re: [HACKERS] Design notes for EquivalenceClasses
Note that a bitmap scan or multi-pass indexscan (OR clause scan) has NIL pathkeys since we can say nothing about the overall order of its result. It's seems to me that multi-pass indexscan was removed after introducing bitmapscan. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Design notes for EquivalenceClasses
Teodor Sigaev [EMAIL PROTECTED] writes: Note that a bitmap scan or multi-pass indexscan (OR clause scan) has NIL pathkeys since we can say nothing about the overall order of its result. It's seems to me that multi-pass indexscan was removed after introducing bitmapscan. Yeah, but it might come back someday, so I didn't feel a need to change that sentence... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [COMMITTERS] pgsql: Fix failure due to accessing an
Tatsuo Ishii [EMAIL PROTECTED] writes: One of our engineer claimed that double free bug itself is a vulnerability, thus 8.2.1 release should be called as security release. [ shrug... ] AFAICS the crashing bugs we fixed in 8.2.1 can't be exploited for anything beyond crashing the backend, and only by an attacker who can issue arbitrary SQL commands. There are plenty of other ways to cause momentary DOS if you can do that, so it doesn't strike me as a big security vulnerability. But if you want to call it one, you can. regards, tom lane ---(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: [HACKERS] Design notes for EquivalenceClasses
Note that a bitmap scan or multi-pass indexscan (OR clause scan) has NIL pathkeys since we can say nothing about the overall order of its result. Yeah, but it might come back someday, so I didn't feel a need to change that sentence... Hmm. Our OR patch makes the same possibility by using Append node - without reintroducing multi-pass indexscan. Moreover, it allows to sort OR clauses to match sort order. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [GENERAL] Corrupt database? 8.1/FreeBSD6.0
Tom Lane wrote: I wrote: ... but I suddenly fear that we've missed a fundamental point about pg_clog truncation. And WAL wraparound for that matter. To wit, a sufficiently long-lived temp table could contain old XIDs, and there's no way for anyone except the owning backend to clean them out, or even guarantee that they're marked committed. After further thought I believe this is OK as of 8.2, because a temp table's relfrozenxid is tracked independently of any other's. (This problem puts a stake through the heart of the recently-discussed idea that a temp table might be able to get along without a globally visible pg_class entry, however.) But it seems that we need a band-aid for 8.1 and earlier. The simplest fix I can think of is for vacuum not to attempt to advance the datvacuumxid/datfrozenxid fields if it skipped over any temp tables of other backends. That's a bit nasty, since in a database making heavy use of temp tables, you might do a whole lot of vacuums without ever meeting that condition. Anyone have a better idea? That seems nasty. Can we examine the xmin of the pg_class entry for temp tables instead? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] Corrupt database? 8.1/FreeBSD6.0
Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: But it seems that we need a band-aid for 8.1 and earlier. The simplest fix I can think of is for vacuum not to attempt to advance the datvacuumxid/datfrozenxid fields if it skipped over any temp tables of other backends. That's a bit nasty, since in a database making heavy use of temp tables, you might do a whole lot of vacuums without ever meeting that condition. Anyone have a better idea? That seems nasty. Can we examine the xmin of the pg_class entry for temp tables instead? No, because any sort of schema update on the temp table would rewrite its pg_class row with a newer version. You couldn't assume that the pg_class row is older than what's in the table. Consider this perfectly reasonable scenario: CREATE TEMP TABLE foo ... COPY foo FROM ... CREATE INDEX ...- must set relhasindex regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Temparary disable constraint
- Original Message - From: Richard Huxton dev@archonet.com To: Bruce Momjian [EMAIL PROTECTED] Cc: Adnan DURSUN [EMAIL PROTECTED]; pgsql-hackers@postgresql.org Sent: Thursday, January 18, 2007 5:57 PM Subject: Re: [HACKERS] Temparary disable constraint Not easily, because the view are bound to the object id of the tables involved. The trick would be I think to bind them to individual columns, so if view V doesn't mention column C then dropping C has no effect on it. That's a lot more dependencies to track of course. Is that not possible that all objects have a column that says whether object state is ok or not. When any session wants to try to execute any DML on object, then the Postgres checks that state column. If object state is not ok then Postgres raises an exception. Best Regards Adnan DURSUN ASRIN Bilisim Ltd. ---(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: [HACKERS] [COMMITTERS] pgsql: Fix failure due to accessing an
Ok, understood. -- Tatsuo Ishii SRA OSS, Inc. Japan Tatsuo Ishii [EMAIL PROTECTED] writes: One of our engineer claimed that double free bug itself is a vulnerability, thus 8.2.1 release should be called as security release. [ shrug... ] AFAICS the crashing bugs we fixed in 8.2.1 can't be exploited for anything beyond crashing the backend, and only by an attacker who can issue arbitrary SQL commands. There are plenty of other ways to cause momentary DOS if you can do that, so it doesn't strike me as a big security vulnerability. But if you want to call it one, you can. regards, tom lane ---(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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] Windows buildfarm failures
Alvaro Herrera wrote: Stefan Kaltenbrunner wrote: yeah - looks like it's the autovacuum change - snake is now passing the numeric-test but still fails the stats one ... Interesting -- both yak and snake are failing in a very similar way. I'll investigate it tomorrow if no one beats me to it. All our Windows buildfarm machines are failing. AFAICT, the first failure was on Yak, http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=yakdt=2007-01-16%2021:55:20 and the last successful run just before that seems to come from Snake, http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=snakedt=2007-01-16%2014:30:00 The only changes that went in in that period are the patch that enabled autovacuum by default, an information_schema fix and a TODO file change. The only that could cause this problem seems to be the autovacuum enable bit. The failures are all exactly alike: *** ./expected/stats.outThu Jan 18 08:48:12 2007 --- ./results/stats.out Thu Jan 18 09:02:53 2007 *** *** 51,57 WHERE st.relname='tenk2' AND cl.relname='tenk2'; ?column? | ?column? | ?column? | ?column? --+--+--+-- ! t| t| t| t (1 row) SELECT st.heap_blks_read + st.heap_blks_hit = pr.heap_blks + cl.relpages, --- 51,57 WHERE st.relname='tenk2' AND cl.relname='tenk2'; ?column? | ?column? | ?column? | ?column? --+--+--+-- ! f| f| f| f (1 row) SELECT st.heap_blks_read + st.heap_blks_hit = pr.heap_blks + cl.relpages, *** *** 60,66 WHERE st.relname='tenk2' AND cl.relname='tenk2'; ?column? | ?column? --+-- ! t| t (1 row) -- End of Stats Test --- 60,66 WHERE st.relname='tenk2' AND cl.relname='tenk2'; ?column? | ?column? --+-- ! f| f (1 row) -- End of Stats Test The full failing queries are these: -- check effects SELECT st.seq_scan = pr.seq_scan + 1, st.seq_tup_read = pr.seq_tup_read + cl.reltuples, st.idx_scan = pr.idx_scan + 1, st.idx_tup_fetch = pr.idx_tup_fetch + 1 FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr WHERE st.relname='tenk2' AND cl.relname='tenk2'; ?column? | ?column? | ?column? | ?column? --+--+--+-- t| t| t| t (1 row) SELECT st.heap_blks_read + st.heap_blks_hit = pr.heap_blks + cl.relpages, st.idx_blks_read + st.idx_blks_hit = pr.idx_blks + 1 FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr WHERE st.relname='tenk2' AND cl.relname='tenk2'; ?column? | ?column? --+-- t| t (1 row) The six booleans are false on Windows. What could be the reason for this change? The only thing that occurs to me is that autovacuum is firing just when running that test, it processes that table and increments the counters before the final SQL is run. Now, if some Windows-enabled person could step forward so that we can suggest some tests to run, that would be great. Perhaps the solution to the problem is to relax the conditions a little, so that two scans are accepted on that table instead of only one; but it would be good to confirm whether the stat system is really working and it's really still counting stuff as it's supposed to do. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Windows buildfarm failures
Alvaro Herrera [EMAIL PROTECTED] writes: Now, if some Windows-enabled person could step forward so that we can suggest some tests to run, that would be great. Perhaps the solution to the problem is to relax the conditions a little, so that two scans are accepted on that table instead of only one; but it would be good to confirm whether the stat system is really working and it's really still counting stuff as it's supposed to do. No, you misread it: the check is for at least one new event, not exactly one. We've been seeing this intermittently for a long time, but it sure seems that autovac has raised the probability greatly. That's pretty odd. If it's a timing thing, why are all and only the Windows machines affected? Could it be that autovac is sucking all the spare cycles and keeping the stats collector from running? (Does autovac use vacuum_cost_delay by default? It probably should if not.) I noticed today on my own machine several strange pauses while running the serial regression tests --- the machine didn't seem to be hitting the disk nor sucking lots of CPU, it just sat there for several seconds and then picked up again. I wonder if that's related. It sure seems it must be due to autovac being on now. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Windows buildfarm failures
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Now, if some Windows-enabled person could step forward so that we can suggest some tests to run, that would be great. Perhaps the solution to the problem is to relax the conditions a little, so that two scans are accepted on that table instead of only one; but it would be good to confirm whether the stat system is really working and it's really still counting stuff as it's supposed to do. No, you misread it: the check is for at least one new event, not exactly one. Doh :-( We've been seeing this intermittently for a long time, but it sure seems that autovac has raised the probability greatly. That's pretty odd. If it's a timing thing, why are all and only the Windows machines affected? Could it be that autovac is sucking all the spare cycles and keeping the stats collector from running? Hmm, that could explain it, but it's strange that only Windows machines are affected. Maybe it's a scheduler issue, and the Unix machines are able to let pgstat do some work but Windows are not. (Does autovac use vacuum_cost_delay by default? It probably should if not.) The default autovacuum_vacuum_cost_delay is -1, which means use the system default, which in turn is 0. So it's off by default. I noticed today on my own machine several strange pauses while running the serial regression tests --- the machine didn't seem to be hitting the disk nor sucking lots of CPU, it just sat there for several seconds and then picked up again. I wonder if that's related. It sure seems it must be due to autovac being on now. Hmm, strange; I ran the tests several times today testing Magnus changes, and I didn't notice any pause. It was mostly the parallel tests though; I'll try serial. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] pg_trigger.tgargs needs detoast
Hello,
This patch fixes a bug of case of extraction of pg_trigger.tgargs.
There was a problem when we used a long argument in defining trigger,
possibly resulting in a server crash.
Example:
We defined a CREATE TRIGGER such as follows and registered trigger.
In this case, the argument value which we received in the trigger
procedure was not right.
CREATE TRIGGER trigger_test BEFORE INSERT OR UPDATE ON sample FOR EACH
ROW EXECUTE PROCEDURE sample_trig('XXX...(more than 1823 characters)');
The trigger procedure which receives the argument:
Datum sample_trig(PG_FUNCTION_ARGS)
{
TriggerData* trigdata = (TriggerData*)fcinfo-context;
char** args = trigdata-tg_trigger-tgargs;
int nargs = trigdata-tg_trigger-tgnargs;
int i;
for (i = 0; i nargs; i++) {
elog(LOG, %s, args[i]);
}
...
}
Result:
Before: LOG: (the character that is not right, for example '%')
After : LOG: XXX...(more than 1823 characters)
Regards,
---
Kenji Kawamura
NTT Open Source Center, Japan
Index: src/backend/commands/tablecmds.c
===
--- src/backend/commands/tablecmds.c(HEAD)
+++ src/backend/commands/tablecmds.c(modified)
@@ -1800,8 +1800,7 @@
* line; so does trigger.c ...
*/
tgnargs = pg_trigger-tgnargs;
- val = (bytea *)
- DatumGetPointer(fastgetattr(tuple,
+ val = DatumGetByteaP(fastgetattr(tuple,
Anum_pg_trigger_tgargs,
tgrel-rd_att, isnull));
if (isnull || tgnargs RI_FIRST_ATTNAME_ARGNO ||
Index: src/backend/commands/trigger.c
===
--- src/backend/commands/trigger.c (HEAD)
+++ src/backend/commands/trigger.c (modified)
@@ -906,8 +906,7 @@
char *p;
int i;
- val = (bytea *)
- DatumGetPointer(fastgetattr(htup,
+ val = DatumGetByteaP(fastgetattr(htup,
Anum_pg_trigger_tgargs,
tgrel-rd_att, isnull));
if (isnull)
Index: src/backend/utils/adt/ruleutils.c
===
--- src/backend/utils/adt/ruleutils.c (HEAD)
+++ src/backend/utils/adt/ruleutils.c (modified)
@@ -521,8 +521,7 @@
char *p;
int i;
- val = (bytea *)
- DatumGetPointer(fastgetattr(ht_trig,
+ val = DatumGetByteaP(fastgetattr(ht_trig,
Anum_pg_trigger_tgargs,
tgrel-rd_att, isnull));
if (isnull)
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] PostgreSQL win32 fragmentation issue
I have a production database that is running on Win32 NTFS with PostgreSQL 8.2.1. The drive that the data is stored on is dedicated to PostgreSQL data files (transaction log stored on a separate disk) The data partition is currently 44% fragmented, which I believe is resulting in performance degradation. I don't have any benchmarks though. Joshua D. Drake wrote: Hello, I ran a simple test... I ran defrag on my drives. Then I initialized pgbench with a -s 1000. 11% fragmentation. I dropped the bench database, and my fragmentation is 1%. I know this isn't *our* fault :) but I am curious if there is anything we can do about the way postgresql writes files to help limit fragmentation. Essentially, this makes win32 impossible in a 24x7 environment (jokes aside about Win32 in general) because we *have* to defrag on Windows and Windows won't defrag open files (thus anything PostgreSQL is using). Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] pg_trigger.tgargs needs detoast
Hello,
This patch fixes a bug of case of extraction of pg_trigger.tgargs.
There was a problem when we used a long argument in defining trigger,
possibly resulting in a server crash.
Example:
We defined a CREATE TRIGGER such as follows and registered trigger.
In this case, the argument value which we received in the trigger
procedure was not right.
CREATE TRIGGER trigger_test BEFORE INSERT OR UPDATE ON sample FOR EACH
ROW EXECUTE PROCEDURE sample_trig('XXX...(more than 1823 characters)');
The trigger procedure which receives the argument:
Datum sample_trig(PG_FUNCTION_ARGS)
{
TriggerData* trigdata = (TriggerData*)fcinfo-context;
char** args = trigdata-tg_trigger-tgargs;
int nargs = trigdata-tg_trigger-tgnargs;
int i;
for (i = 0; i nargs; i++) {
elog(LOG, %s, args[i]);
}
...
}
Result:
Before: LOG: (the character that is not right, for example '%')
After : LOG: XXX...(more than 1823 characters)
Regards,
---
Kenji Kawamura
NTT Open Source Center, Japan
Index: src/backend/commands/tablecmds.c
===
--- src/backend/commands/tablecmds.c(HEAD)
+++ src/backend/commands/tablecmds.c(modified)
@@ -1800,8 +1800,7 @@
* line; so does trigger.c ...
*/
tgnargs = pg_trigger-tgnargs;
- val = (bytea *)
- DatumGetPointer(fastgetattr(tuple,
+ val = DatumGetByteaP(fastgetattr(tuple,
Anum_pg_trigger_tgargs,
tgrel-rd_att, isnull));
if (isnull || tgnargs RI_FIRST_ATTNAME_ARGNO ||
Index: src/backend/commands/trigger.c
===
--- src/backend/commands/trigger.c (HEAD)
+++ src/backend/commands/trigger.c (modified)
@@ -906,8 +906,7 @@
char *p;
int i;
- val = (bytea *)
- DatumGetPointer(fastgetattr(htup,
+ val = DatumGetByteaP(fastgetattr(htup,
Anum_pg_trigger_tgargs,
tgrel-rd_att, isnull));
if (isnull)
Index: src/backend/utils/adt/ruleutils.c
===
--- src/backend/utils/adt/ruleutils.c (HEAD)
+++ src/backend/utils/adt/ruleutils.c (modified)
@@ -521,8 +521,7 @@
char *p;
int i;
- val = (bytea *)
- DatumGetPointer(fastgetattr(ht_trig,
+ val = DatumGetByteaP(fastgetattr(ht_trig,
Anum_pg_trigger_tgargs,
tgrel-rd_att, isnull));
if (isnull)
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
[HACKERS] Fix for bug in plpython bool type conversion
Hi!
The attached patch fixes a bug in plpython.
This bug was found while creating sql from trigger functions
written in plpython and later running the generated sql.
The problem was that boolean was was silently converted to
integer, which is ok for python but fails when the created
sql is used.
The patch uses the Py_RETURN_xxx macros shown at
http://docs.python.org/api/boolObjects.html .
It would be nice if someone could test and comment
on the patch.
Cheers
Guido
--- postgresql-8.2.1.orig/src/pl/plpython/plpython.c2006-11-21
22:51:05.0 +0100
+++ postgresql-8.2.1/src/pl/plpython/plpython.c 2007-01-17 18:06:58.185497734
+0100
@@ -1580,8 +1580,8 @@
PLyBool_FromString(const char *src)
{
if (src[0] == 't')
- return PyInt_FromLong(1);
- return PyInt_FromLong(0);
+ Py_RETURN_TRUE;
+ Py_RETURN_FALSE;
}
static PyObject *
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[HACKERS] Planning aggregates which require sorted or distinct input
Recenly, I've been researching and putting together a proposal for window functions. I have not finished this but when I do, I will post it. A nice list of examples can be found here[1]. Rather than spend a lot of time talking about the problems window functions present to the planner and executor, I'd like to bring up the topic of an existing piece of SQL which is well understood and presents a similar problem. Consider the following query: select saledate, count(distinct prodid), count(distinct sellerid), sum(price) from sales group by 1; The point here is that aggregates usually just receive the input that the lower level of the plan generates. When qualified with distinct, however, that changes. Notice that the count on prodid and sellerid receive unique input while the sum on price does not. We do not create seperate plan nodes to achieve this. Rather, it is a property of the aggregation code inside the executor[2]. It seems to me that by creating actual plan nodes for this distinct step we can improve the range of options for executing these types of queries. For example, consider a more complex query than the above that groups over a join using a join key of saledate, prodid (and that the planner implements with a merge join). This means that the sort order is preserved and count(distinct prodid) will receive sorted input. As far as I can tell, however, the executor doesn't know this and but the planner does. That is, the sort step inside the aggregate code is redundant. Another way it could be improved is if we ever introduce a 'hash distinct' execution node. This has been discussed before. My interest here is not so much to do with distinct aggregates but, rather, window functions. Window functions have this same problem as the input to the functions is generally sorted by different keys. So, hypothetically, lets say we wanted to create a plan for the above query which had an explicit Sort - Unique 'branch' for each of the distinct aggregates. This is actually difficult to represent with the existing plan constructs, as it turns out. Currently, the plan looks something like: GroupAggregate ^ | Sort Op ^ | Scan What we want to do is have a kind of 'sub plan' for each aggregate. In effect, the plan might start looking like a directed graph. Here is part of the plan as a directed graph. GroupAggregate /-^---... | | | | ^ | | Unique | ^ | | Sort Sort (saledate)(saledate,prodid) ^ ^ | | -- Fan Out ... ^ | Scan This idea was presented by Brian Hagenbuch at Greenplum. He calls it a 'Fan Out' plan. It is trivial to rejoin the data because all data input to the aggregates is sorted by the same primary key. Also, we could/would optimize this to perform a single sort for those columns which require the same sort order (saledate and sum(price) in the example query). An extra step would be required for (some) window functions because they wont necessarily preserve order. That's not important now. An alternative approach would be a 'pipeline' design. This would fit into the existing plan structure better. In this approach, each aggregate would be a distinct step in the plan. Finalize (like Result) ^ | Agg (sum(price)) ^ | Sort (saledate) ^ | Agg (count(sellerid)) ^ | Sort (saleid, sellerid)/Unique ^ | Agg (count(prodid)) ^ | Sort (saleid, prodid)/Unique ^ | Scan Now, this would actually work as follows: the input would be received from the Scan node. We sort by the input by the key saleid, prodid and produce a unique result. It is input to the aggregate and we produce a result for a distinct grouping expression. We then pass the output of the aggregate for this grouping expression up the tree along with a pointer to the scan data. We do not discard the
