[HACKERS] 7.3beta and ecpg
Hi, I didn't download the beta but compared the CVS checkouts and it appears the ecpg directory is still the one from 7.2 not the one tagged big_bison. Will this one be moved into the mainstream source? Else we would be stuck with a non-compatible parser. If I shall move it, please tell me, I'm just not doing it before talking to you guys. Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] About connectby()
On Sat, 07 Sep 2002 10:26:36 -0700
Joe Conway [EMAIL PROTECTED] wrote:
OK -- patch submitted to fix this. Once the patch is applied, this case
gives:
test=# SELECT * FROM connectby('connectby_tree', 'keyid',
'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level
int, branch text);
ERROR: infinite recursion detected
Thank you for your patch.
If you specifically limit the depth to less than where the repeated key
is hit, everything works as before:
And I also think this approach is reasonable.
test=# SELECT * FROM connectby('connectby_tree', 'keyid',
'parent_keyid', '2', 4, '~') AS t(keyid int, parent_keyid int, level
int, branch text);
keyid | parent_keyid | level | branch
---+--+---+-
2 | | 0 | 2
4 |2 | 1 | 2~4
6 |4 | 2 | 2~4~6
8 |6 | 3 | 2~4~6~8
5 |2 | 1 | 2~5
9 |5 | 2 | 2~5~9
10 |9 | 3 | 2~5~9~10
11 | 10 | 4 | 2~5~9~10~11
(8 rows)
Thanks for the feedback!
Joe
Regards,
Masaru Sugawara
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Importing data from 7.2.2 into 7.3b1 !?
Hi, Sorry to insist, may be my previus subject was miss understood ... refering to this message : http://archives.postgresql.org/pgsql-hackers/2002-09/msg00461.php But I can't import my data from 7.2.2 into 7.3b1 ... 1- Many errors during importation of the data 2- Seems to use all the memory (and swap) during the import of the data made with a classical pg_dumpall from the 7.2.2 to the 7.3b1 ... I'm used to make the same import on the same computer from the same data from 7.2.2 (other server) to 7.2.2 (this computer)... so the 7.3b1 use more memory and seems to not understand the pg_dumpall data of the 7.3b1 ? Any idea ? I'm the only one with this kind of trouble ? The beta page tell us to make dump/initdb/reload ... it's what I've done but without any result ;) Regards, -- Hervé ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count
Hello Bruce, Monday, September 9, 2002, 12:16:32 AM, you wrote: BM Joe Conway wrote: BM Any suggestion on how to show the tag mutated? Do we want to add more BM tag possibilities? Again, I don't agree with PQcmdStatus() returning a pseudo-keyword, since I would expect a SQL command executed. I prefer Tom's suggestion of returning the same kind of command executed, or the last command as of Proposal #1. I don't think we should add tuple counts from different commands, i.e. adding UPDATE and DELETE counts just yeilds a totally meaningless number. I don't know about that. The number of rows affected is indeed this number. It's just that they were not all affected in the same way. BM Yes, that is true. The problem is that a DELETE returning a value of 10 BM may have deleted only one row and updated another 9 rows. In such BM cases, returning 1 is better. Of course, if there are multiple deletes BM then perhaps the total is better, but then again, there is no way to BM flag this so we have to do one or the other consistently. BM BM The real problem which you outline is that suppose the delete does _no_ BM deletes but only inserts. In my plan, we would return zero while in BM yours you would return the rows updated. You have a good point here, Bruce. And for avoiding it, maybe Tom's suggestion is the best. Unless the new API as of Proposal #3 is introduced. BM In my view, if you return a delete tag, you better only count deletes. Yes, this is Tom's Proposal and it makes more sense when you imagine a case situation. Proposal #1 tried to be more compatible with the behavior of multiple commands execution but that would lead us to bad situations like Bruce exposes here. BM Also, your total affected isn't going to work well with INSERT because BM we could return a non-1 for rows affected and still return an OID, which BM would be quite confusing. I did the total only matching tags because it BM does mesh with the INSERT behavior. Even if this is 100% true, I'm afraid the only way to cover all specific situations is the new API. Let's remember it's easy to implement, and could server to both multiple commands execution *and* this rules situation. I don't think there is any need/desire to add additional API routines to handle multiple return values. Agreed. BM Yep. OK, this counts two points against the new API :) - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count from complex commands [return] issue
Hello Bruce, Monday, September 9, 2002, 12:21:11 AM, you wrote: BM Steve Howe wrote: Hello Bruce, But this *is* the total number of rows affected. There is no current (defined) behavior of rows affected by the same kind of command issued, although I agree it makes some sense. BM Yes, that is a good point, i.e. rows effected. However, see my previous BM email on how this doesn't play with with INSERT. I agree with your point. In fact, since everybody until now seems to agree that the last command behavior isn't consistent, I think Tom's suggestion is the best. BM We don't like to add complexity if we can help it. I understand. If we can reach an agreement on another way, that's ok for me... We still have to hear the other developers about this, but for a while, my votes go to Proposal's #2 (by Tom) and Proposal #3 if enough people consider it important. - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proposal: Solving the Return proper effected tuple
Bruce Momjian wrote: Joe Conway wrote: This is basically Tom's proposal, but substituting MUTATED for the original command tag name acknowledges that the original command was not executed unchanged. It also serves as a warning that the affected tuple count is from one or more substitute operations, not the original command. Any suggestion on how to show the tag mutated? Do we want to add more tag possibilities? The suggestion was made based on what I think is the desired behavior, but I must admit I have no idea how it would be implemented at this point. It may turn out to be more pain than it's worth. I don't know about that. The number of rows affected is indeed this number. It's just that they were not all affected in the same way. Yes, that is true. The problem is that a DELETE returning a value of 10 may have deleted only one row and updated another 9 rows. In such cases, returning 1 is better. Of course, if there are multiple deletes then perhaps the total is better, but then again, there is no way to flag this so we have to do one or the other consistently. The real problem which you outline is that suppose the delete does _no_ deletes but only inserts. In my plan, we would return zero while in yours you would return the rows updated. In my view, if you return a delete tag, you better only count deletes. Also, your total affected isn't going to work well with INSERT because we could return a non-1 for rows affected and still return an OID, which would be quite confusing. I did the total only matching tags because it does mesh with the INSERT behavior. Sure, but that's why I am in favor of changing the tag. If you did: DELETE FROM fooview WHERE name LIKE 'Joe%'; and got: MUTATED 507324 3 it would mean that 3 tuples in total were affected by all of the substitute operations, only of of them being an INSERT, and the Oid of the lone INSERT was 507324. If instead I got: DELETE 0 I'd be back to having no useful information. Did any rows in fooview match the criteria LIKE 'Joe%'? Did any data in my database get altered? Can't tell from this. Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal: Solving the Return proper effected tuple
Steve Howe wrote: We still have to hear the other developers about this, but for a while, my votes go to Proposal's #2 (by Tom) and Proposal #3 if enough people consider it important. I think Tom and Hirosh were the people most involved in the previous discussion. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count
Joe Conway wrote: Sure, but that's why I am in favor of changing the tag. If you did: DELETE FROM fooview WHERE name LIKE 'Joe%'; and got: MUTATED 507324 3 it would mean that 3 tuples in total were affected by all of the substitute operations, only of of them being an INSERT, and the Oid of the lone INSERT was 507324. If instead I got: DELETE 0 I'd be back to having no useful information. Did any rows in fooview match the criteria LIKE 'Joe%'? Did any data in my database get altered? Can't tell from this. OK. Do any people have INSTEAD rules where there are not commands matching the original query tag? Can anyone think of such a case being created? The only one I can think of is UPDATE implemented as separate INSERT and DELETE commands. -- 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: [HACKERS] Proposal: Solving the Return proper effected tuple count
Hello Bruce, Monday, September 9, 2002, 12:22:26 AM, you wrote: BM Steve Howe wrote: JC return OID if sum of all replacement INSERTs in the rule inserted JConly one row, else zero I don't agree with this one since it would lead us to a meaningless information... what would be the number retrieved ? Not an OID, nor nothing. BM I don't understand this objection. I misunderstood Joe's statement into thinking we wanted to sum the OIDs for all INSERT commands applied :) Please ignore this. But now that I read it again, I would prefer having at least one OID for the last inserted row. With this info, I would be able to refresh my client dataset to reflect the new inserted rows. I see returning 0 if multiple INSERT commands issued is as weird as returning some OID if multiple INSERT commands issued. But the second options is usable, while the first one is useless... So I would prefer retrieving the last inserted OID. - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: Solving the Return proper effected tuple
Steve Howe wrote: Hello Bruce, Monday, September 9, 2002, 12:22:26 AM, you wrote: BM Steve Howe wrote: JC return OID if sum of all replacement INSERTs in the rule inserted JConly one row, else zero I don't agree with this one since it would lead us to a meaningless information... what would be the number retrieved ? Not an OID, nor nothing. BM I don't understand this objection. I misunderstood Joe's statement into thinking we wanted to sum the OIDs for all INSERT commands applied :) Please ignore this. But now that I read it again, I would prefer having at least one OID for the last inserted row. With this info, I would be able to refresh my client dataset to reflect the new inserted rows. I see returning 0 if multiple INSERT commands issued is as weird as returning some OID if multiple INSERT commands issued. But the second options is usable, while the first one is useless... So I would prefer retrieving the last inserted OID. We would return 0 for oid and an insert count, just like INSERT INTO ... SELECT. How is that weird? -- 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: [HACKERS] Proposal: Solving the Return proper effected tuple count
Hello Bruce, Monday, September 9, 2002, 12:36:38 AM, you wrote: BM Joe Conway wrote: Sure, but that's why I am in favor of changing the tag. If you did: DELETE FROM fooview WHERE name LIKE 'Joe%'; and got: MUTATED 507324 3 it would mean that 3 tuples in total were affected by all of the substitute operations, only of of them being an INSERT, and the Oid of the lone INSERT was 507324. If instead I got: DELETE 0 I'd be back to having no useful information. Did any rows in fooview match the criteria LIKE 'Joe%'? Did any data in my database get altered? Can't tell from this. BM OK. Do any people have INSTEAD rules where there are not commands BM matching the original query tag? Can anyone think of such a case being BM created? I can think a thousand cases. For instance, one could create an update rule that would delete rows referenced on a second table (to avoid orphan rows). OR a user could make an insert rule that empties a table with DELETE so that only one row can always be assumed in that table... the possibilities are infinite. BM The only one I can think of is UPDATE implemented as separate INSERT and BM DELETE commands. I'm afraid the great imagination of PostgreSQL users has come to all kind of uses and misuses for such a powerful feature :) - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Proposal: Solving the Return proper effected tuple
Bruce Momjian wrote: OK. Do any people have INSTEAD rules where there are not commands matching the original query tag? Can anyone think of such a case being created? The only one I can think of is UPDATE implemented as separate INSERT and DELETE commands. I could see an UPDATE implemented as an UPDATE and an INSERT. You would UPDATE the original row to mark it as dead (e.g. change END_DATE from NULL to CURRENT_DATE), and INSERT a new row to represent the new state. This is pretty common in business systems where you need complete transaction history, and never update in place over critical data. Similarly, a DELETE might be implemented as an UPDATE for the same reason (mark it dead, but keep the data). In fact, the view itself might screen out the dead rows using the field which was UPDATED. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count
Hello Bruce, Monday, September 9, 2002, 12:39:20 AM, you wrote: BM I don't understand this objection. I misunderstood Joe's statement into thinking we wanted to sum the OIDs for all INSERT commands applied :) Please ignore this. But now that I read it again, I would prefer having at least one OID for the last inserted row. With this info, I would be able to refresh my client dataset to reflect the new inserted rows. I see returning 0 if multiple INSERT commands issued is as weird as returning some OID if multiple INSERT commands issued. But the second options is usable, while the first one is useless... So I would prefer retrieving the last inserted OID. BM We would return 0 for oid and an insert count, just like INSERT INTO ... BM SELECT. How is that weird? It's not weird, or as weird as the other proposal which is retrieving the last inserted OID number. If we can return some information for the client, why not doing it ? :-) - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count
Steve Howe wrote: BM We would return 0 for oid and an insert count, just like INSERT INTO ... BM SELECT. How is that weird? It's not weird, or as weird as the other proposal which is retrieving the last inserted OID number. If we can return some information for the client, why not doing it ? :-) Well, we don't return an OID from a random row when we do INSERT INTO ... SELECT (and no one has complained about it) so I can't see why we would return an OID there. -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal: Solving the Return proper effected tuple
On Sun, 8 Sep 2002, Steve Howe wrote: Here are the proposals for solutioning the Return proper effected tuple count from complex commands [return] issue as seen on TODO. Any comments ?... This is obviously open to voting and discussion. As it seems we're voting, I think Tom's scheme is about as good as we'll do for the current API. I actually think that a better API is a good idea, but it's an API change and we're in beta, so not for 7.3. I'm not 100% sure which of the PQcmdTuples behaviors makes sense (actually I'm pretty sure neither do, but since the general complaint is knowing whether something happened or not, sum gets around the last statement doing 0 rows and running into the same type of problem). Proposal #2 (author: Tom lane): - Tom Lane's proposal, as posted on http://candle.pha.pa.us/mhonarc/todo.detail/return/msg00012.html, consists basically on the following: PQcmdStatus() == Should always return the same command type original submitted by the client. PQcmdTuples() == If no INSTEAD rule, return same output as for original command, ignoring other commands in the rule.If there is INSTEAD rules, use result of last command in the rewritten series, use result of last command of same type as original command or sum up the results of all the rewritten commands. (I particularly prefer the sum). PQoidValue() == If the original command was not INSERT, return 0. otherwise, if one INSERT, return it's original PQoidValue(). If more then one INSERT command applied, use last or other possibilities (please refer to the thread for details). Please refer to the original post to refer to the original message. I would like to point out that it was the most consistent proposal pointed out until now on the previous discussions (Bruce M. agrees with this one). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Script to compute random page cost
Because we have seen many complains about sequential vs index scans, I
wrote a script which computes the value for your OS/hardware
combination.
Under BSD/OS on one SCSI disk, I get a random_page_cost around 60. Our
current postgresql.conf default is 4.
What do other people get for this value?
Keep in mind if we increase this value, we will get a more sequential
scans vs. index scans.
One flaw in this test is that it randomly reads blocks from different
files rather than randomly reading from the same file. Do people have a
suggestion on how to correct this? Does it matter?
--
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
#!/bin/bash
trap rm -f /tmp/$$ 0 1 2 3 15
BLCKSZ=8192
if [ $RANDOM = $RANDOM ]
thenecho Your shell does not support \$RANDOM. Try using bash. 12
exit 1
fi
# XXX We assume 0 = random = 32767
echo Collecting sizing information ...
TEMPLATE1=`du -s $PGDATA/base/1 | awk '{print $1}'`
FULL=`du -s $PGDATA/base | awk '{print $1}'`
if [ $FULL -lt `expr $TEMPLATE1 \* 4` ]
thenecho Your installation should have at least four times the data stored in
template1 to yield meaningful results 12
exit 1
fi
find $PGDATA/base -type f -exec ls -ld {} \; |
awk '$5 % '$BLCKSZ' == 0 {print $5 / '$BLCKSZ', $9}' |
grep -v '^0 ' /tmp/$$
TOTAL=`awk 'BEGIN {sum=0}
{sum += $1}
END {print sum}' /tmp/$$`
echo Running random access timing test ...
START=`date '+%s'`
PAGES=1000
while [ $PAGES -ne 0 ]
do
BIGRAND=`expr $RANDOM \* 32768 + $RANDOM`
OFFSET=`awk 'BEGIN{printf %d\n, ('$BIGRAND' / 2^30) * '$TOTAL'}'`
RESULT=`awk ' BEGIN {offset = 0}
offset + $1 '$OFFSET' \
{print $2, '$OFFSET' - offset ; exit}
{offset += $1}' /tmp/$$`
FILE=`echo $RESULT | awk '{print $1}'`
OFFSET=`echo $RESULT | awk '{print $2}'`
dd bs=$BLCKSZ seek=$OFFSET count=1 if=$FILE of=/dev/null /dev/null
21
PAGES=`expr $PAGES - 1`
done
STOP=`date '+%s'`
RANDTIME=`expr $STOP - $START`
echo Running sequential access timing test ...
START=`date '+%s'`
# We run the random test 10 times more because it is quicker and
# we need it to run for a while to get accurate results.
PAGES=1
while [ $PAGES -ne 0 ]
do
BIGRAND=`expr $RANDOM \* 32768 + $RANDOM`
OFFSET=`awk 'BEGIN{printf %d\n, ('$BIGRAND' / 2^30) * '$TOTAL'}'`
RESULT=`awk ' BEGIN {offset = 0}
offset + $1 '$OFFSET' \
{print $2, $1; exit}
{offset += $1}' /tmp/$$`
FILE=`echo $RESULT | awk '{print $1}'`
FILEPAGES=`echo $RESULT | awk '{print $2}'`
if [ $FILEPAGES -gt $PAGES ]
thenFILEPAGES=$PAGES
fi
dd bs=$BLCKSZ count=$FILEPAGES if=$FILE of=/dev/null /dev/null 21
PAGES=`expr $PAGES - $FILEPAGES`
done
STOP=`date '+%s'`
SEQTIME=`expr $STOP - $START`
echo
awk 'BEGIN {printf random_page_cost = %f\n, ('$RANDTIME' / '$SEQTIME') *
10}'
---(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] Schemas not available for pl/pgsql %TYPE....
Tom Lane wrote:
Sean Chittenden [EMAIL PROTECTED] writes:
::sigh:: Is it me or does it look like all
of pl/pgsql is schema un-aware (ie, all of the declarations). -sc
Yeah. The group of routines parse_word, parse_dblword, etc that are
called by the lexer certainly all need work. There are some
definitional issues to think about, too --- plpgsql presently relies on
the number of names to give it some idea of what to look for, and those
rules are probably all toast now. Please come up with a sketch of what
you think the behavior should be before you start hacking code.
Attached is a diff -c format proposal to fix this. I've also attached a short
test script. Seems to work OK and passes all regression tests.
Here's a breakdown of how I understand plpgsql's Special word rules -- I
think it illustrates the behavior reasonably well. New functions added by this
patch are plpgsql_parse_tripwordtype and plpgsql_parse_dblwordrowtype:
Identifiers (represents)parsing function
identifierplpgsql_parse_word
tg_argv
T_LABEL (label)
T_VARIABLE (variable)
T_RECORD(record)
T_ROW (row)
identifier.identifier plpgsql_parse_dblword
T_LABEL
T_VARIABLE (label.variable)
T_RECORD(label.record)
T_ROW (label.row)
T_RECORD
T_VARIABLE (record.variable)
T_ROW
T_VARIABLE (row.variable)
identifier.identifier.identifier plpgsql_parse_tripword
T_LABEL
T_RECORD
T_VARIABLE (label.record.variable)
T_ROW
T_VARIABLE (label.row.variable)
identifier%TYPE plpgsql_parse_wordtype
T_VARIABLE
T_DTYPE (variable%TYPE)
T_DTYPE (typname%TYPE)
identifier.identifier%TYPE plpgsql_parse_dblwordtype
T_LABEL
T_VARIABLE
T_DTYPE (label.variable%TYPE)
T_DTYPE (relname.attname%TYPE)
new
identifier.identifier.identifier%TYPE plpgsql_parse_tripwordtype
T_DTYPE (nspname.relname.attname%TYPE)
identifier%ROWTYPE plpgsql_parse_wordrowtype
T_DTYPE (relname%ROWTYPE)
new
identifier.identifier%ROWTYPE plpgsql_parse_dblwordrowtype
T_DTYPE (nspname.relname%ROWTYPE)
Parameters - parallels the above
$#plpgsql_parse_word
$#.identifier plpgsql_parse_dblword
$#.identifier.identifier plpgsql_parse_tripword
$#%TYPE plpgsql_parse_wordtype
$#.identifier%TYPE plpgsql_parse_dblwordtype
$#.identifier.identifier%TYPE plpgsql_parse_tripwordtype
$#%ROWTYPE plpgsql_parse_wordrowtype
$#.identifier%ROWTYPE plpgsql_parse_dblwordrowtype
Comments?
Thanks,
Joe
Index: src/pl/plpgsql/src/pl_comp.c
===
RCS file: /opt/src/cvs/pgsql-server/src/pl/plpgsql/src/pl_comp.c,v
retrieving revision 1.51
diff -c -r1.51 pl_comp.c
*** src/pl/plpgsql/src/pl_comp.c4 Sep 2002 20:31:47 - 1.51
--- src/pl/plpgsql/src/pl_comp.c9 Sep 2002 04:22:24 -
***
*** 1092,1097
--- 1092,1217
return T_DTYPE;
}
+ /* --
+ * plpgsql_parse_tripwordtype Same lookup for word.word.word%TYPE
+ * --
+ */
+ #define TYPE_JUNK_LEN 5
+
+ int
+ plpgsql_parse_tripwordtype(char *word)
+ {
+ Oid classOid;
+ HeapTuple classtup;
+ Form_pg_class classStruct;
+ HeapTuple attrtup;
+ Form_pg_attribute attrStruct;
+ HeapTuple typetup;
+ Form_pg_type typeStruct;
+
Re: [HACKERS] Script to compute random page cost
OK, turns out that the loop for sequential scan ran fewer times and was
skewing the numbers. I have a new version at:
ftp://candle.pha.pa.us/pub/postgresql/randcost
I get _much_ lower numbers now for random_page_cost.
---
Bruce Momjian wrote:
Because we have seen many complains about sequential vs index scans, I
wrote a script which computes the value for your OS/hardware
combination.
Under BSD/OS on one SCSI disk, I get a random_page_cost around 60. Our
current postgresql.conf default is 4.
What do other people get for this value?
Keep in mind if we increase this value, we will get a more sequential
scans vs. index scans.
One flaw in this test is that it randomly reads blocks from different
files rather than randomly reading from the same file. Do people have a
suggestion on how to correct this? Does it matter?
--
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
#!/bin/bash
trap rm -f /tmp/$$ 0 1 2 3 15
BLCKSZ=8192
if [ $RANDOM = $RANDOM ]
then echo Your shell does not support \$RANDOM. Try using bash. 12
exit 1
fi
# XXX We assume 0 = random = 32767
echo Collecting sizing information ...
TEMPLATE1=`du -s $PGDATA/base/1 | awk '{print $1}'`
FULL=`du -s $PGDATA/base | awk '{print $1}'`
if [ $FULL -lt `expr $TEMPLATE1 \* 4` ]
then echo Your installation should have at least four times the data stored in
template1 to yield meaningful results 12
exit 1
fi
find $PGDATA/base -type f -exec ls -ld {} \; |
awk '$5 % '$BLCKSZ' == 0 {print $5 / '$BLCKSZ', $9}' |
grep -v '^0 ' /tmp/$$
TOTAL=`awk 'BEGIN {sum=0}
{sum += $1}
END {print sum}' /tmp/$$`
echo Running random access timing test ...
START=`date '+%s'`
PAGES=1000
while [ $PAGES -ne 0 ]
do
BIGRAND=`expr $RANDOM \* 32768 + $RANDOM`
OFFSET=`awk 'BEGIN{printf %d\n, ('$BIGRAND' / 2^30) * '$TOTAL'}'`
RESULT=`awk ' BEGIN {offset = 0}
offset + $1 '$OFFSET' \
{print $2, '$OFFSET' - offset ; exit}
{offset += $1}' /tmp/$$`
FILE=`echo $RESULT | awk '{print $1}'`
OFFSET=`echo $RESULT | awk '{print $2}'`
dd bs=$BLCKSZ seek=$OFFSET count=1 if=$FILE of=/dev/null /dev/null
21
PAGES=`expr $PAGES - 1`
done
STOP=`date '+%s'`
RANDTIME=`expr $STOP - $START`
echo Running sequential access timing test ...
START=`date '+%s'`
# We run the random test 10 times more because it is quicker and
# we need it to run for a while to get accurate results.
PAGES=1
while [ $PAGES -ne 0 ]
do
BIGRAND=`expr $RANDOM \* 32768 + $RANDOM`
OFFSET=`awk 'BEGIN{printf %d\n, ('$BIGRAND' / 2^30) * '$TOTAL'}'`
RESULT=`awk ' BEGIN {offset = 0}
offset + $1 '$OFFSET' \
{print $2, $1; exit}
{offset += $1}' /tmp/$$`
FILE=`echo $RESULT | awk '{print $1}'`
FILEPAGES=`echo $RESULT | awk '{print $2}'`
if [ $FILEPAGES -gt $PAGES ]
thenFILEPAGES=$PAGES
fi
dd bs=$BLCKSZ count=$FILEPAGES if=$FILE of=/dev/null /dev/null 21
PAGES=`expr $PAGES - $FILEPAGES`
done
STOP=`date '+%s'`
SEQTIME=`expr $STOP - $START`
echo
awk 'BEGIN{printf random_page_cost = %f\n, ('$RANDTIME' / '$SEQTIME') *
10}'
---(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
--
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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] --with-maxbackends
Matthew T. O'Connor wrote: On Saturday 07 September 2002 12:52 pm, Bruce Momjian wrote: Peter Eisentraut wrote: Didn't we want to remove that option? I didn't know it was still in there. I see no reason for it. How about --enable-depend, that's not still needed is it? Or is that something other than the new dependancy system? That relates to C file dependencies, not pg_depend. -- 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
[HACKERS] Proposal: Solving the Return proper effected tuple count from complex commands [return] issue
Hello all, Here are the proposals for solutioning the Return proper effected tuple count from complex commands [return] issue as seen on TODO. Any comments ?... This is obviously open to voting and discussion. -- Best regards, Steve Howe mailto:[EMAIL PROTECTED] - Introduction These are three proposals to give a solution on the issue: * Return proper effected tuple count from complex commands [return] ... as seen on TODO http://developer.postgresql.org/todo.php as of 09 Sep 2002. Affect Versions: PostgreSQL v7.2X PostgreSQL pre 7.2 versions has inconsistent behavior as stated below. References -- The main thread discussion is listed in (1): http://momjian.postgresql.org/cgi-bin/pgtodo?return Some previous discussion started on (2): http://archives.postgresql.org/pgsql-general/2002-05/msg00096.php The topic was revisited by Steve Howe in the thread (3): http://archives.postgresql.org/pgsql-hackers/2002-09/msg00429.php Problem Description: PQcmdStatus(), PQcmdTuples() and PQoidValue() do not work properly on rules, most notably updating views. An additional layer of problems can arise if user issues multiple commands per rule, as of what should be the output of those functions in that situation. Specially problematic is PQcmdTuples(), which will return 0, confusing client applications into thinking nothing was updated and even breaking some applications. The pre-version 7.2 behavior is not acceptable as stated by Tom Lane on the threads above. An urgent fix is demanded to allow applications using rules to work properly and allow clients to retrieve proper execution information. Proposal #1 (author: Steve Howe): - As stated in the threads above (from the [References] topic), we have 3 tags to worry about, returned by the following functions: PQcmdStatus() - command status string PQcmdTuples() - number of rows updated PQoidValue() - last inserted OID My proposal consists basically on having the same behavior of when multiple commands per execution string are executed currently (except for PQcmdTuples()) : PQcmdStatus() == Should return the last executed command or the same as the original command (I prefer the second way, but the first is more intuitive on a multiple execution case, as I'll explain below). PQcmdTuples() == should return the sum of modified rows of all commands executed by the rule (DELETE / INSERT / UPDATE). PQoidValue() == should return the value for the last INSERT executed command in the rule (if any). Using this scheme, any SELECT commands executed would not count on PQcmdTuples(), what makes plain sense. The other commands would give a similar response to what we already have when we issue multiple commands per execution string. I would like to quote an issued pointed by Tom Lane, from one of the messages on the thread above: I'm also concerned about having an understandable definition for the OID returned for an INSERT query --- if there are additional INSERTs triggered by rules, does that mean you don't get to see the OID assigned to the single row you tried to insert? In this case, the user has to be aware that if he issued multiple commands, he will get the result for only the last one. This is is the same behavior of multiple commands when you execute: db# insert into MyTable values(1 ,1); insert into MyTable values(2 ,2); INSERT 93345 1 INSERT 93346 1 Of course this could lead to have a PQcmdStatus() return value greater then the number of rows viewable by the rule, but I think that's perfectly understandable if there are multiple commands involved and the client application programmer should be aware of that. PQoidStatus() will return the OID only for the last command, so (again) the proposed behavior is compatible on what already happens when you issue multiple commands. So if the user issues some insert commands but The proposed behavior would be the same for DO and DO INSTEAD rules unless someone points out some flaw. Proposal #2 (author: Tom lane): - Tom Lane's proposal, as posted on http://candle.pha.pa.us/mhonarc/todo.detail/return/msg00012.html, consists basically on the following: PQcmdStatus() == Should always return the same command type original submitted by the client. PQcmdTuples() == If no INSTEAD rule, return same output as for original command, ignoring other commands in the rule.If there is INSTEAD rules, use result of last command in the rewritten series, use result of last command of same type as original command or sum up the results of all the rewritten commands.
Re: [HACKERS] Proposal: Solving the Return proper effected tuple count
I liked option #2. I don't think the _last_ query in a rule should have any special handling. So, to summarize #2, we have: if no INSTEAD, return value of original command if INSTEAD, return tag of original command return sum of all affected rows with the same tag return OID if all INSERTs in the rule insert only one row, else zero This INSERT behavior seems consistent with INSERTs inserting multiple rows via INSERT INTO ... SELECT: test= create table x (y int); inseCREATE TABLE test= insert into x select 1; INSERT 507324 1 ^^ test= insert into x select 1 union select 2; INSERT 0 2 ^ I don't think we should add tuple counts from different commands, i.e. adding UPDATE and DELETE counts just yeilds a totally meaningless number. I don't think there is any need/desire to add additional API routines to handle multiple return values. Can I get some votes on this? We have one user very determined to get a fix, and the TODO.detail file has another user who really wants a fix. --- Proposal #2 (author: Tom lane): - Tom Lane's proposal, as posted on http://candle.pha.pa.us/mhonarc/todo.detail/return/msg00012.html, consists basically on the following: PQcmdStatus() == Should always return the same command type original submitted by the client. PQcmdTuples() == If no INSTEAD rule, return same output as for original command, ignoring other commands in the rule.If there is INSTEAD rules, use result of last command in the rewritten series, use result of last command of same type as original command or sum up the results of all the rewritten commands. (I particularly prefer the sum). PQoidValue() == If the original command was not INSERT, return 0. otherwise, if one INSERT, return it's original PQoidValue(). If more then one INSERT command applied, use last or other possibilities (please refer to the thread for details). -- 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
[HACKERS] Australian Open Source Awards
Hi Guys, You might be interested in the results of the Australian Open Source Awards: http://www.smh.com.au/articles/2002/09/06/1031115931961.html Justin Clift and I both rated mentions - Justin for the Postgres websites and myself for BSD Users Group WA. One good things is that both Postgres and BUGWA got a mention on Slashdot and the Sydney Morning Herald with is neat. Cheers, Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proposal: Solving the Return proper effected tuple
Bruce Momjian wrote: I liked option #2. I don't think the _last_ query in a rule should have any special handling. So, to summarize #2, we have: if no INSTEAD, return value of original command if INSTEAD, return tag of original command return sum of all affected rows with the same tag return OID if all INSERTs in the rule insert only one row, else zero How about: if no INSTEAD, return value of original command if INSTEAD, return tag MUTATED return sum of sum of tuple counts of all replacement commands return OID if sum of all replacement INSERTs in the rule inserted only one row, else zero This is basically Tom's proposal, but substituting MUTATED for the original command tag name acknowledges that the original command was not executed unchanged. It also serves as a warning that the affected tuple count is from one or more substitute operations, not the original command. I don't think we should add tuple counts from different commands, i.e. adding UPDATE and DELETE counts just yeilds a totally meaningless number. I don't know about that. The number of rows affected is indeed this number. It's just that they were not all affected in the same way. I don't think there is any need/desire to add additional API routines to handle multiple return values. Agreed. Can I get some votes on this? We have one user very determined to get a fix, and the TODO.detail file has another user who really wants a fix. +1 for the version above ;-) Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Australian Open Source Awards
God, I wish ppl would at least get information correct :( Justin Clift (for the postgreSQL documentation website) the website they point to *isn't* techdocs, but www, which Justin has had nothing to do with ;( On Mon, 9 Sep 2002, Christopher Kings-Lynne wrote: Hi Guys, You might be interested in the results of the Australian Open Source Awards: http://www.smh.com.au/articles/2002/09/06/1031115931961.html Justin Clift and I both rated mentions - Justin for the Postgres websites and myself for BSD Users Group WA. One good things is that both Postgres and BUGWA got a mention on Slashdot and the Sydney Morning Herald with is neat. Cheers, Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [GENERAL] Australian Open Source Awards
Well annoyingly enough they have me down as 'founding pandaemonium' whereas it should be co-founded pandaemonium :( Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Marc G. Fournier Sent: Monday, 9 September 2002 11:01 AM To: Christopher Kings-Lynne Cc: Hackers; [EMAIL PROTECTED] Subject: Re: [HACKERS] [GENERAL] Australian Open Source Awards God, I wish ppl would at least get information correct :( Justin Clift (for the postgreSQL documentation website) the website they point to *isn't* techdocs, but www, which Justin has had nothing to do with ;( On Mon, 9 Sep 2002, Christopher Kings-Lynne wrote: Hi Guys, You might be interested in the results of the Australian Open Source Awards: http://www.smh.com.au/articles/2002/09/06/1031115931961.html Justin Clift and I both rated mentions - Justin for the Postgres websites and myself for BSD Users Group WA. One good things is that both Postgres and BUGWA got a mention on Slashdot and the Sydney Morning Herald with is neat. Cheers, Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(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] Proposal: Solving the Return proper effected tuple count from complex commands [return] issue
Hello Bruce, Sunday, September 8, 2002, 10:52:45 PM, you wrote: BM I liked option #2. I don't think the _last_ query in a rule should have BM any special handling. BM So, to summarize #2, we have: BM if no INSTEAD, BM return value of original command The problem is, this would lead us to the same behavior of Proposal #1 (returning the value for the last command executed), which you didn't like... BM if INSTEAD, BM return tag of original command BM return sum of all affected rows with the same tag BM return OID if all INSERTs in the rule insert only one row, else zero BM This INSERT behavior seems consistent with INSERTs inserting multiple BM rows via INSERT INTO ... SELECT: BM test= create table x (y int); BM inseCREATE TABLE BM test= insert into x select 1; BM INSERT 507324 1 BM^^ BM test= insert into x select 1 union select 2; BM INSERT 0 2 BM^ BM I don't think we should add tuple counts from different commands, i.e. BM adding UPDATE and DELETE counts just yeilds a totally meaningless BM number. But this *is* the total number of rows affected. There is no current (defined) behavior of rows affected by the same kind of command issued, although I agree it makes some sense. BM I don't think there is any need/desire to add additional API routines to BM handle multiple return values. I'm ok with that if we can reach an agreement on how the existing API should work. But as I stated, a new API would be a no-discussion way to solve this, and preferably extending some of the other proposals. BM Can I get some votes on this? We have one user very determined to get a BM fix, and the TODO.detail file has another user who really wants a fix. *Please* let's do it :) Thanks. - Best regards, Steve Howe mailto:[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] Proposal: Solving the Return proper effected tuple count
Joe Conway wrote: Bruce Momjian wrote: I liked option #2. I don't think the _last_ query in a rule should have any special handling. So, to summarize #2, we have: if no INSTEAD, return value of original command if INSTEAD, return tag of original command return sum of all affected rows with the same tag return OID if all INSERTs in the rule insert only one row, else zero How about: if no INSTEAD, return value of original command if INSTEAD, return tag MUTATED return sum of sum of tuple counts of all replacement commands return OID if sum of all replacement INSERTs in the rule inserted only one row, else zero This is basically Tom's proposal, but substituting MUTATED for the original command tag name acknowledges that the original command was not executed unchanged. It also serves as a warning that the affected tuple count is from one or more substitute operations, not the original command. Any suggestion on how to show the tag mutated? Do we want to add more tag possibilities? I don't think we should add tuple counts from different commands, i.e. adding UPDATE and DELETE counts just yeilds a totally meaningless number. I don't know about that. The number of rows affected is indeed this number. It's just that they were not all affected in the same way. Yes, that is true. The problem is that a DELETE returning a value of 10 may have deleted only one row and updated another 9 rows. In such cases, returning 1 is better. Of course, if there are multiple deletes then perhaps the total is better, but then again, there is no way to flag this so we have to do one or the other consistently. The real problem which you outline is that suppose the delete does _no_ deletes but only inserts. In my plan, we would return zero while in yours you would return the rows updated. In my view, if you return a delete tag, you better only count deletes. Also, your total affected isn't going to work well with INSERT because we could return a non-1 for rows affected and still return an OID, which would be quite confusing. I did the total only matching tags because it does mesh with the INSERT behavior. I don't think there is any need/desire to add additional API routines to handle multiple return values. Agreed. Yep. Can I get some votes on this? We have one user very determined to get a fix, and the TODO.detail file has another user who really wants a fix. +1 for the version above ;-) OK, we are getting closer. -- 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: [HACKERS] Proposal: Solving the Return proper effected tuple count
Hello Joe, Sunday, September 8, 2002, 11:54:45 PM, you wrote: JC Bruce Momjian wrote: I liked option #2. I don't think the _last_ query in a rule should have any special handling. So, to summarize #2, we have: if no INSTEAD, return value of original command if INSTEAD, return tag of original command return sum of all affected rows with the same tag return OID if all INSERTs in the rule insert only one row, else zero JC How about: JC if no INSTEAD, JC return value of original command JC if INSTEAD, JC return tag MUTATED I see PQcmdStatus() returning a SQL command and not a pseudo-keyword, so I don't agree with this tag. JC return sum of sum of tuple counts of all replacement commands Agreed. JC return OID if sum of all replacement INSERTs in the rule inserted JConly one row, else zero I don't agree with this one since it would lead us to a meaningless information... what would be the number retrieved ? Not an OID, nor nothing. JC I don't know about that. The number of rows affected is indeed this JC number. It's just that they were not all affected in the same way. Agreed too... JC +1 for the version above ;-) Which ? Yours or Tom's ? :) - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal: Solving the Return proper effected tuple
Steve Howe wrote: Hello Bruce, Sunday, September 8, 2002, 10:52:45 PM, you wrote: BM I liked option #2. I don't think the _last_ query in a rule should have BM any special handling. BM So, to summarize #2, we have: BM if no INSTEAD, BM return value of original command The problem is, this would lead us to the same behavior of Proposal #1 (returning the value for the last command executed), which you didn't like... I don't like treating the last command as special when there is more than one command. Of course, if there is only no INSTEAD, the main statement is the only one we care about returning information for. BM if INSTEAD, BM return tag of original command BM return sum of all affected rows with the same tag BM return OID if all INSERTs in the rule insert only one row, else zero BM This INSERT behavior seems consistent with INSERTs inserting multiple BM rows via INSERT INTO ... SELECT: BM test= create table x (y int); BM inseCREATE TABLE BM test= insert into x select 1; BM INSERT 507324 1 BM^^ BM test= insert into x select 1 union select 2; BM INSERT 0 2 BM^ BM I don't think we should add tuple counts from different commands, i.e. BM adding UPDATE and DELETE counts just yeilds a totally meaningless BM number. But this *is* the total number of rows affected. There is no current (defined) behavior of rows affected by the same kind of command issued, although I agree it makes some sense. Yes, that is a good point, i.e. rows effected. However, see my previous email on how this doesn't play with with INSERT. BM I don't think there is any need/desire to add additional API routines to BM handle multiple return values. I'm ok with that if we can reach an agreement on how the existing API should work. But as I stated, a new API would be a no-discussion way to solve this, and preferably extending some of the other proposals. We don't like to add complexity if we can help it. -- 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 4: Don't 'kill -9' the postmaster
