Re: [HACKERS] Two-phase commit issues
On Saturday 21 May 2005 03:37, Josh Berkus wrote: 2PC is a key to supporting 3rd-party replication tools, like C-JDBC. I don't think C-JDBC requires 2PC for replication. Mixed up acronyms maybe? :) -- Jose Orlando Pereira ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] subquery returning array
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi! I have posted this as a user comment in section 9.17:
-
= ANY(array expression) doesn't work in the obvious way when the array
expression is a subselect. For example:
select * from stat3 where stat3.id = any ('{4,5,6,7}');
works (and returns the expected tuples). However,
select * from stat3 where stat3.id = any (select stat3 from
helix_request where id=11);
DOESN'T work and complains with an error: operator does not exist:
integer = integer[]. The inner select returns EXACTLY ONE value, namely
the same array as the literal in the first example.
The solution is:
select * from stat3 where (select stat3.id = any (stat3) from
helix_request where id=11);
I thank to Chris Kings-Lynne (KL) for helping me out with this over IRC.
-
KL suggested to mail this question to the hackers list. The problem with
this solution is that postgresql uses sequential scan for the proposed
solution:
Seq Scan on stat3 (cost=0.00..40018.94 rows=3321 width=32) (actual
time=0.112..75.911 rows=4 loops=1)
Filter: (subplan)
SubPlan
- Index Scan using helix_request_pkey on helix_request
(cost=0.00..6.01 rows=1 width=32) (actual time=0.007..0.008 rows=1
loops=6756)
Index Cond: (id = 11)
Total runtime: 76.040 ms
(6 rows)
If I write select * from stat3 where id in (4,5,6,7) and index scan is used:
Index Scan using stat3_pkey, stat3_pkey, stat3_pkey, stat3_pkey on
stat3 (cost=0.00..23.94 rows=4 width=32) (actual time=0.066..0.091
rows=4 loops=1)
Index Cond: ((id = 4) OR (id = 5) OR (id = 6) OR (id = 7))
Total runtime: 0.164 ms
I'm going to have MANY queries of this kind and having sequential scan
for each such query will lead to quadratic performance - unacceptable
for the amount of data I will have (millions of rows).
Is there yet another way of making WHERE field = ANY (subselect
returning an array) work? Or make postgres to use index?
OK, I know the suggestion from the manual: usually it's bad database
design searching through arrays and a separate table is better. however,
to convert {4,5,6,7} into a table would require a table with two
columns, e.g. (1,4), (1,5), (1,6), (1,7) where the first column would be
used to identify elements in the single array). this additional column
is a waste of space.
KL has mentioned a package for converting an array into a tuple. Where
can I find it? Also, what is the limit on the number of elements in the
IN (...) condition before the database resorts to sequential scan?
PS: Please reply also via e-mail as I'm not a regular subscriber of this
list.
Thanks.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.7 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iQCVAwUBQpF0U2nN2B8CwPRmAQKN8gP+JCzLiX5b48kMYmHRwTSFZWN5Jydfw0iH
MABuYj2mKCY9Dgmd4pLK8Xlxhf/tEYzd3N2lcPFYf1vIXCSpbFasRrO3hJ4WjRLr
MZ6MLXCn59Y8wtd8Iz8uug47XuYVGSreZESKA9tRfl+u8t16nPC9nEMyDNDFIRI9
ZOnBMCnQzrY=
=5qmB
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] subquery returning array
Zeljko Vrba wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi! I have posted this as a user comment in section 9.17:
-
= ANY(array expression) doesn't work in the obvious way when the array
expression is a subselect. For example:
select * from stat3 where stat3.id = any ('{4,5,6,7}');
works (and returns the expected tuples). However,
select * from stat3 where stat3.id = any (select stat3 from
helix_request where id=11);
DOESN'T work and complains with an error: operator does not exist:
integer = integer[]. The inner select returns EXACTLY ONE value, namely
the same array as the literal in the first example.
No, it doesn't - hence the error.
What it returns is a SET containing ONE TUPLE which contains ONE ARRAY.
Here the any ranges over the SET not the array, so it tries to compare
the outer integer with the inner array of integers.
The solution is:
select * from stat3 where (select stat3.id = any (stat3) from
helix_request where id=11);
I thank to Chris Kings-Lynne (KL) for helping me out with this over IRC.
-
KL suggested to mail this question to the hackers list. The problem with
this solution is that postgresql uses sequential scan for the proposed
solution:
[snip]
I'm going to have MANY queries of this kind and having sequential scan
for each such query will lead to quadratic performance - unacceptable
for the amount of data I will have (millions of rows).
Is there yet another way of making WHERE field = ANY (subselect
returning an array) work? Or make postgres to use index?
OK, I know the suggestion from the manual: usually it's bad database
design searching through arrays and a separate table is better. however,
to convert {4,5,6,7} into a table would require a table with two
columns, e.g. (1,4), (1,5), (1,6), (1,7) where the first column would be
used to identify elements in the single array). this additional column
is a waste of space.
You're trading space for time, and deliberately spoiling your design to
do so. If you're going to want to access each value separately, design
your system to reflect that fact.
Have you actually tested your example with a proper two-column table? It
might be that weaknesses in PostgreSQL force you to compromise your
design, but I wouldn't start from that point. Try a proper relational
design, with a bit of tuning and see if that will work for you.
If you really want to use arrays, perhaps see if the contrib/intarray
module can help you.
--
Richard Huxton
Archonet Ltd
---(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] INSTEAD OF trigger on VIEWs
Is there any plans to create an INSTEAD OF trigger on VIEWS? I have view which consists of a master and detail table. When a row is inserted into the view, the view needs to figure out if the master record already exsists. If the record does not exists in the master table, then insert into the master and the detail table. If the record already exists in the master, just insert into detail table. Conversely, if a delete record is performed on the view, the view needs to figure out if it only needs to delete from the detail table, or should it also delete from the master table when all the detail records are already deleted. In Oracle this is easily done using INSTEAD OF triggers. INSTEAD OF triggers can only be created for VIEWs. The purpose of it is to short-circuit the event (INSERT, UPDATE or DELETE) and perform whatever is specified in the trigger. CREATE OR REPLACE TRIGGER schema.trigger_name INSTEAD OF INSERT ON object_name BEGIN -- Perform the following instead -- END; __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] INSTEAD OF trigger on VIEWs
You can probably just create an INSTEAD rule on the view... Chris --= Tono =-- wrote: Is there any plans to create an INSTEAD OF trigger on VIEWS? I have view which consists of a master and detail table. When a row is inserted into the view, the view needs to figure out if the master record already exsists. If the record does not exists in the master table, then insert into the master and the detail table. If the record already exists in the master, just insert into detail table. Conversely, if a delete record is performed on the view, the view needs to figure out if it only needs to delete from the detail table, or should it also delete from the master table when all the detail records are already deleted. In Oracle this is easily done using INSTEAD OF triggers. INSTEAD OF triggers can only be created for VIEWs. The purpose of it is to short-circuit the event (INSERT, UPDATE or DELETE) and perform whatever is specified in the trigger. CREATE OR REPLACE TRIGGER schema.trigger_name INSTEAD OF INSERT ON object_name BEGIN -- Perform the following instead -- END; __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] INSTEAD OF trigger on VIEWs
I have a similar problem and already considered using RULEs, but I encountered the problem, that I did not find any way to execute procedures from RULEs without using SELECT, which creates always a result set being passed to the application invoking the INSERT, UPDATE or DELETE, even if the function is declared VOID. This is causing trouble when using asynchronous command processing. Christopher Kings-Lynne wrote: You can probably just create an INSTEAD rule on the view... Chris --= Tono =-- wrote: Is there any plans to create an INSTEAD OF trigger on VIEWS? I have view which consists of a master and detail table. When a row is inserted into the view, the view needs to figure out if the master record already exsists. If the record does not exists in the master table, then insert into the master and the detail table. If the record already exists in the master, just insert into detail table. Conversely, if a delete record is performed on the view, the view needs to figure out if it only needs to delete from the detail table, or should it also delete from the master table when all the detail records are already deleted. In Oracle this is easily done using INSTEAD OF triggers. INSTEAD OF triggers can only be created for VIEWs. The purpose of it is to short-circuit the event (INSERT, UPDATE or DELETE) and perform whatever is specified in the trigger. CREATE OR REPLACE TRIGGER schema.trigger_name INSTEAD OF INSERT ON object_name BEGIN -- Perform the following instead -- END; ---(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] INSTEAD OF trigger on VIEWs
I have a similar problem and already considered using RULEs, but I encountered the problem, that I did not find any way to execute procedures from RULEs without using SELECT, which creates always a result set being passed to the application invoking the INSERT, UPDATE or DELETE, even if the function is declared VOID. This is causing trouble when using asynchronous command processing. The solution then is for us to get around to implementing procedures, rather than functions, in PostgreSQL I think. Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] INSTEAD OF trigger on VIEWs
Christopher Kings-Lynne wrote: I have a similar problem and already considered using RULEs, but I encountered the problem, that I did not find any way to execute procedures from RULEs without using SELECT, which creates always a result set being passed to the application invoking the INSERT, UPDATE or DELETE, even if the function is declared VOID. This is causing trouble when using asynchronous command processing. The solution then is for us to get around to implementing procedures, rather than functions, in PostgreSQL I think. Chris Yes, I think that this would be a good way to solve the problem, but is it planned to implement procedures in near future? What about extending the SQL command set by an INVOKE command, which invokes a function and discards the result? Jan Behrens ---(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] INSTEAD OF trigger on VIEWs
I have tried using INSTEAD rules but there are some conditional logic that needs to happen inside the rule (performing counts, getting and storing the primary key of the master record etc.). AFAIK, rules only allows conditional logic to be check prior to execution of the rule and not inside the rule itself. One way to get around this is to allow calling a stored procedure inside the rule. This stored procedure should have full access of NEW.* (and OLD.* in the case of UPDATE and DELETE). This way the manual INSERT, UPDATE or DELETE on the actual tables can be performed from inside the stored procedure. --- Christopher Kings-Lynne [EMAIL PROTECTED] wrote: You can probably just create an INSTEAD rule on the view... Chris --= Tono =-- wrote: Is there any plans to create an INSTEAD OF trigger on VIEWS? I have view which consists of a master and detail table. When a row is inserted into the view, the view needs to figure out if the master record already exsists. If the record does not exists in the master table, then insert into the master and the detail table. If the record already exists in the master, just insert into detail table. Conversely, if a delete record is performed on the view, the view needs to figure out if it only needs to delete from the detail table, or should it also delete from the master table when all the detail records are already deleted. In Oracle this is easily done using INSTEAD OF triggers. INSTEAD OF triggers can only be created for VIEWs. The purpose of it is to short-circuit the event (INSERT, UPDATE or DELETE) and perform whatever is specified in the trigger. CREATE OR REPLACE TRIGGER schema.trigger_name INSTEAD OF INSERT ON object_name BEGIN -- Perform the following instead -- END; __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] inet increment w/ int8
Bruce Momjian pgman@candle.pha.pa.us writes: I modified the TODO. I think we only need an INT4. I realize INT8 would be for IPV6 but I can't imagine a network that has more than INT4 hosts (not part of the network address). Actually increment the host address isn't a well-defined concept for IPV6. The host part of the address (if you're on an Ethernet) is generally the 64 bit MAC address. -Doug ---(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] inet increment w/ int8
Douglas McNaught wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I modified the TODO. I think we only need an INT4. I realize INT8 would be for IPV6 but I can't imagine a network that has more than INT4 hosts (not part of the network address). Actually increment the host address isn't a well-defined concept for IPV6. The host part of the address (if you're on an Ethernet) is generally the 64 bit MAC address. So if the network card dies the machine has a new IPv6 address and you just update your DNS? Do you update your routing tables? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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] inet increment w/ int8
* Bruce Momjian (pgman@candle.pha.pa.us) wrote: Douglas McNaught wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I modified the TODO. I think we only need an INT4. I realize INT8 would be for IPV6 but I can't imagine a network that has more than INT4 hosts (not part of the network address). Actually increment the host address isn't a well-defined concept for IPV6. The host part of the address (if you're on an Ethernet) is generally the 64 bit MAC address. So if the network card dies the machine has a new IPv6 address and you just update your DNS? Do you update your routing tables? Generally routing isn't done to the last 48 bits (dunno where 64 bit came from, but MAC's are 48 last I checked :). DNS to that level would need to be changed though, yes.. :/ (I'm not exactly a big fan of this development, in fact, I think it's a bunch of poo, but then, I don't write the standards). Stephen signature.asc Description: Digital signature
[HACKERS] Speeding up the Postgres lexer
I was doing some profiles recently that showed that on simple statements
(like INSERT with a few column values) the basic parser (flex/bison) was
taking up a noticeable percentage of the total CPU time. We already
have done some things to speed up the lexer, like use -CF option for
large-but-fast tables. I read in the flex manual about how you can make
it faster yet if you can avoid the need for backup states. It's not
terribly well documented, but I eventually worked out how to do it, and
find that it seems to make the lexer about a third faster. Basically
what you have to do is provide alternate rules that allow the lexer to
match *some* rule on any prefix of a complete token. For instance the
rule for {real} implies that given
123.456E+
if the next character is not a digit, then the {real} rule fails, and
the lexer has to backtrack to
123.456
which it can recognize as a {decimal}. You can add a rule that does
match in this situation, throws back the two unwanted characters with
yyless(), and returns the number as the {decimal} rule would do.
The next call will carry on lexing the E as an identifier, etc.
(Note: this is going to end up being a syntax error because the SQL
grammar never allows a number to immediately precede an identifier;
but that's not the lexer's concern, and so I don't think that we should
make the extra rule throw an error immediately.)
What I'm wondering is whether this is really worth doing or not.
There are currently just two parts of the lexer rules that are affected
--- the {real} rule illustrated above, and the rules that allow quoted
strings to be split across lines as the SQL spec requires. But the
patches are still pretty ugly, and what's really annoying is that there
doesn't seem to be any way to get flex to complain if someone later
makes a change that breaks the no-backup-cases property again.
A prototype patch (completely undocumented :-() is attached. Any
thoughts about whether it's worth pressing forward with?
regards, tom lane
*** src/backend/parser/scan.l.orig Fri Mar 11 21:15:20 2005
--- src/backend/parser/scan.l Mon May 23 01:05:54 2005
***
*** 148,163
* validate the contents.
*/
xbstart [bB]{quote}
! xbstop{quote}
xbinside [^']*
xbcat {quote}{whitespace_with_newline}{quote}
/* Hexadecimal number
*/
xhstart [xX]{quote}
! xhstop{quote}
xhinside [^']*
xhcat {quote}{whitespace_with_newline}{quote}
/* National character
*/
--- 148,165
* validate the contents.
*/
xbstart [bB]{quote}
! xbstop{quote}{whitespace}*
xbinside [^']*
xbcat {quote}{whitespace_with_newline}{quote}
+ xbconfused{quote}{whitespace}*-
/* Hexadecimal number
*/
xhstart [xX]{quote}
! xhstop{quote}{whitespace}*
xhinside [^']*
xhcat {quote}{whitespace_with_newline}{quote}
+ xhconfused{quote}{whitespace}*-
/* National character
*/
***
*** 169,180
*/
quote '
xqstart {quote}
! xqstop{quote}
xqdouble {quote}{quote}
xqinside [^\\']+
xqescape [\\][^0-7]
xqoctesc [\\][0-7]{1,3}
xqcat {quote}{whitespace_with_newline}{quote}
/* $foo$ style quotes (dollar quoting)
* The quoted string starts with $foo$ where foo is an optional string
--- 171,183
*/
quote '
xqstart {quote}
! xqstop{quote}{whitespace}*
xqdouble {quote}{quote}
xqinside [^\\']+
xqescape [\\][^0-7]
xqoctesc [\\][0-7]{1,3}
xqcat {quote}{whitespace_with_newline}{quote}
+ xqconfused{quote}{whitespace}*-
/* $foo$ style quotes (dollar quoting)
* The quoted string starts with $foo$ where foo is an optional string
***
*** 185,190
--- 188,194
dolq_start[A-Za-z\200-\377_]
dolq_cont [A-Za-z\200-\377_0-9]
dolqdelim \$({dolq_start}{dolq_cont}*)?\$
+ dolqfailed\${dolq_start}{dolq_cont}*
dolqinside[^$]+
/* Double quote
***
*** 247,253
integer {digit}+
decimal (({digit}*\.{digit}+)|({digit}+\.{digit}*))
! real
((({digit}*\.{digit}+)|({digit}+\.{digit}*)|({digit}+))([Ee][-+]?{digit}+))
param \${integer}
--- 251,259
integer {digit}+
decimal (({digit}*\.{digit}+)|({digit}+\.{digit}*))
! real
Re: [HACKERS] Speeding up the Postgres lexer
This seems fine. I don't think the lexer changes enough for us to have
issues with new cases. I think adding some comments to explain why we
are doing it is enough, and perhaps a test case that can be reproduced
later for testing.
---
Tom Lane wrote:
I was doing some profiles recently that showed that on simple statements
(like INSERT with a few column values) the basic parser (flex/bison) was
taking up a noticeable percentage of the total CPU time. We already
have done some things to speed up the lexer, like use -CF option for
large-but-fast tables. I read in the flex manual about how you can make
it faster yet if you can avoid the need for backup states. It's not
terribly well documented, but I eventually worked out how to do it, and
find that it seems to make the lexer about a third faster. Basically
what you have to do is provide alternate rules that allow the lexer to
match *some* rule on any prefix of a complete token. For instance the
rule for {real} implies that given
123.456E+
if the next character is not a digit, then the {real} rule fails, and
the lexer has to backtrack to
123.456
which it can recognize as a {decimal}. You can add a rule that does
match in this situation, throws back the two unwanted characters with
yyless(), and returns the number as the {decimal} rule would do.
The next call will carry on lexing the E as an identifier, etc.
(Note: this is going to end up being a syntax error because the SQL
grammar never allows a number to immediately precede an identifier;
but that's not the lexer's concern, and so I don't think that we should
make the extra rule throw an error immediately.)
What I'm wondering is whether this is really worth doing or not.
There are currently just two parts of the lexer rules that are affected
--- the {real} rule illustrated above, and the rules that allow quoted
strings to be split across lines as the SQL spec requires. But the
patches are still pretty ugly, and what's really annoying is that there
doesn't seem to be any way to get flex to complain if someone later
makes a change that breaks the no-backup-cases property again.
A prototype patch (completely undocumented :-() is attached. Any
thoughts about whether it's worth pressing forward with?
regards, tom lane
*** src/backend/parser/scan.l.origFri Mar 11 21:15:20 2005
--- src/backend/parser/scan.l Mon May 23 01:05:54 2005
***
*** 148,163
* validate the contents.
*/
xbstart [bB]{quote}
! xbstop {quote}
xbinside[^']*
xbcat {quote}{whitespace_with_newline}{quote}
/* Hexadecimal number
*/
xhstart [xX]{quote}
! xhstop {quote}
xhinside[^']*
xhcat {quote}{whitespace_with_newline}{quote}
/* National character
*/
--- 148,165
* validate the contents.
*/
xbstart [bB]{quote}
! xbstop {quote}{whitespace}*
xbinside[^']*
xbcat {quote}{whitespace_with_newline}{quote}
+ xbconfused {quote}{whitespace}*-
/* Hexadecimal number
*/
xhstart [xX]{quote}
! xhstop {quote}{whitespace}*
xhinside[^']*
xhcat {quote}{whitespace_with_newline}{quote}
+ xhconfused {quote}{whitespace}*-
/* National character
*/
***
*** 169,180
*/
quote '
xqstart {quote}
! xqstop {quote}
xqdouble{quote}{quote}
xqinside[^\\']+
xqescape[\\][^0-7]
xqoctesc[\\][0-7]{1,3}
xqcat {quote}{whitespace_with_newline}{quote}
/* $foo$ style quotes (dollar quoting)
* The quoted string starts with $foo$ where foo is an optional string
--- 171,183
*/
quote '
xqstart {quote}
! xqstop {quote}{whitespace}*
xqdouble{quote}{quote}
xqinside[^\\']+
xqescape[\\][^0-7]
xqoctesc[\\][0-7]{1,3}
xqcat {quote}{whitespace_with_newline}{quote}
+ xqconfused {quote}{whitespace}*-
/* $foo$ style quotes (dollar quoting)
* The quoted string starts with $foo$ where foo is an optional string
***
*** 185,190
--- 188,194
dolq_start [A-Za-z\200-\377_]
dolq_cont [A-Za-z\200-\377_0-9]
dolqdelim \$({dolq_start}{dolq_cont}*)?\$
+ dolqfailed \${dolq_start}{dolq_cont}*
dolqinside [^$]+
/* Double quote
***
*** 247,253
[HACKERS] Obtaining Firing Statement clause in (pl/perlu) Trigger Function
Hello Everyone,
Apologies in advance, its time for another of my whacky 'can we do
this' emails ;)
I am trying to write a function/Trigger in pl/perl (although any
other language which allows this is perfectly fine with me :) and I need to
find the firing statement. I understand that if the trigger was fired in
a long sequence, then of course, the calling statement will be the
previous trigger.
The scenario comes from that I am trying to setup a function which
queries a remote Sybase server. I have the functionality such that this
works;
select * from test_func('where userid=2313423');
It then returns a SETOF the table in question. However, the syntax
is, to put it politely, klunky (it smells of a kludge, which, it is :).
Therefore, if I can get access to the where section of the calling
statement in the Trigger itself, i can have a syntax which reads as
follows;
select * from test_func() where userid=2324142;
The reason for needing the syntax inside the Trigger, is obviously
with large tables, grabbing all the tuples and then parsing them down
once obtained is, rather crazy. Some of the tables in question have at
least 3 million records. Flinging them all around only to get a few (or
most of the time one) seems rather crazy.
Do any of the pl/ languages support getting at the calling
trigger statement ?
I am not adverse to trying to 'fix' pl/perl, however, if the trigger
never even gets passed this information in the first place, well, things are
going to start getting messy, and probably spiral outside of my control
:)
Regards and Thanks
Stef
pgpS2GmOnfQcM.pgp
Description: PGP signature
Re: [HACKERS] Obtaining Firing Statement clause in (pl/perlu) Trigger Function
Stef, I am trying to write a function/Trigger in pl/perl (although any other language which allows this is perfectly fine with me :) and I need to find the firing statement. I understand that if the trigger was fired in a long sequence, then of course, the calling statement will be the previous trigger. Talk to David Fetter (author of DBI-Link) about this. You're also probably unnecessarily replicating his work. It's not currently possible, unfortunately. Tge real way to do this would be through RULES. However, RULEs currently don't give you a handle on query substructures like where clauses (let alone join clauses). DF and I have talked about it, but it would take some major back-end hacking to enable it. :-( -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Speeding up the Postgres lexer
On Mon, 2005-05-23 at 12:31 -0400, Tom Lane wrote: doesn't seem to be any way to get flex to complain if someone later makes a change that breaks the no-backup-cases property again. After some digging, there is a -b option will generate a file called lex.backup if any backup-states exist. The file is said to contain information that would help you remove backup-states. It seems straightforward to test for the existence of that file in the build process? Or perhaps add a special test state --enable-flextest to perform the test during the build. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Speeding up the Postgres lexer
Tom Lane wrote:
[snip - flex is slowed down by backtracking - how to fix ]
What I'm wondering is whether this is really worth doing or not.
There are currently just two parts of the lexer rules that are affected
--- the {real} rule illustrated above, and the rules that allow quoted
strings to be split across lines as the SQL spec requires. But the
patches are still pretty ugly, and what's really annoying is that there
doesn't seem to be any way to get flex to complain if someone later
makes a change that breaks the no-backup-cases property again.
I would be more concerned if there were not reasonable alternatives to
many bulk parsed inserts (COPY, prepared statement).
But I do think it's worth it, even so ... not all client interfaces
support prepared statements (notoriously PHP, although I understand KL
has sent patches to fix that) and not all inserts are suitable for COPY.
cheers
andrew
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] inet increment w/ int8
Hi, I modified the TODO. I think we only need an INT4. I realize INT8 would be for IPV6 but I can't imagine a network that has more than INT4 hosts (not part of the network address). Actually increment the host address isn't a well-defined concept for IPV6. The host part of the address (if you're on an Ethernet) is generally the 64 bit MAC address. So if the network card dies the machine has a new IPv6 address and you just update your DNS? Do you update your routing tables? There are standards defined for automatically determining the IPv6 address of a host (Stateless Address Autoconfiguration). These include a standard for Privacy Extensions for Stateless Address Autoconfiguration in IPv6 where the host-part of the IPv6 address changes over time to make it more difficult to identify a single user. The net-part of the IPv6 address can be determined by Router Advertisements. By default an IPv6 address is divided as follows: first 32 bits: ISP next 16 bits: customer next 16 bits: subnet rest (64 bits): host So an ISP gets a /32 from ARIN/RIPE/LACNIC/APNIC/AfriNIC, which assigns a /48 to a customer, which assigns a /64 to each separate network. There are ISPs that have so many customers that they got more than a /32, and if a customer needs more than 16 bits for subnets they can get a bigger block than a /48. This addressing scheme means that even a home-user is a customer and gets a /48 with 16 bits for subnetting. There are discussions going on about giving home users a /56 block instead, but I haven't heard a final decision about that yet (in the RIPE region). From http://www.tcpipguide.com/free/t_IPv6InterfaceIdentifiersandPhysicalAddressMapping.htm: The IEEE has also defined a format called the 64-bit extended unique identifier, abbreviated EUI-64. It is similar to the 48-bit MAC format, except that while the OUI remains at 24 bits, the device identifier becomes 40 bits instead of 24. This provides gives each manufacturer 65,536 times as many device addresses within its OUI. A form of this format, called modified EUI-64, has been adopted for IPv6 interface identifiers. To get the modified EUI-64 interface ID for a device, you simply take the EUI-64 address and change the 7th bit from the left (the universal/local or U/L bit) from a zero to a one. Because the 7th bit is always a one with auto-configuration, addresses with 7th bit zero are still free to be manually assigned. I hope this helps a little... Sander. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] character type value is not padded with spaces
Hackers,
The problem he found is not only existing in Japanese characters but
also in any multibyte encodings including UTF-8. For me the patch
looks good and I will commit it to 7.3, 7.4, 8.0 stables and current
if there's no objection.
--
Tatsuo Ishii
Character type value including multibyte characters is not padded
with spaces. It reproduces at 7.3.x, 7.4.x and 8.0.x.
create table t (a char(10));
insert into t values ('X'); -- X is 2byte character.
I expect that 'X ' is inserted. But 'X' is inserted.
select a, octed_length(a) from t;
a | octet_length
---+--
X | 10
If padded with spaces, octet_length(a) is 15. This problem is caused
that string length is calculated by byte length(VARSIZE) in
exprTypmod().
I attache the patch for this problem.
Regards,
--
Yoshiyuki Asaba
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] character type value is not padded with spaces
Ahemm,...
UNICODE DB:
create table t (a char(10));
set client_encoding = iso88591;
insert into t VALUES ('æøå');
select a, octet_length(a),length(a) from t;
a | octet_length | length
+--+
æøå| 13 | 3
(1 row)
This is with 8.0.2.
Just FYI.
... John
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tatsuo Ishii
Sent: Tuesday, May 24, 2005 8:52 AM
To: [EMAIL PROTECTED]
Cc: pgsql-patches@postgresql.org; pgsql-hackers@postgresql.org
Subject: Re: [PATCHES] character type value is not padded with spaces
Hackers,
The problem he found is not only existing in Japanese
characters but also in any multibyte encodings including
UTF-8. For me the patch looks good and I will commit it to
7.3, 7.4, 8.0 stables and current if there's no objection.
--
Tatsuo Ishii
Character type value including multibyte characters is not
padded with
spaces. It reproduces at 7.3.x, 7.4.x and 8.0.x.
create table t (a char(10));
insert into t values ('X'); -- X is 2byte character.
I expect that 'X ' is inserted. But 'X' is inserted.
select a, octed_length(a) from t;
a | octet_length
---+--
X | 10
If padded with spaces, octet_length(a) is 15. This problem
is caused
that string length is calculated by byte length(VARSIZE) in
exprTypmod().
I attache the patch for this problem.
Regards,
--
Yoshiyuki Asaba
[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])
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] character type value is not padded with spaces
Ahhh...
-Original Message-
From: Tatsuo Ishii [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 24, 2005 9:26 AM
To: John Hansen
Cc: [EMAIL PROTECTED]; pgsql-patches@postgresql.org;
pgsql-hackers@postgresql.org
Subject: Re: [PATCHES] character type value is not padded with spaces
I think you need to test with 5 characters, not 3.
--
Tatsuo Ishii
Ahemm,...
UNICODE DB:
create table t (a char(10));
set client_encoding = iso88591;
insert into t VALUES ('æøå');
select a, octet_length(a),length(a) from t;
a | octet_length | length
+--+
æøå| 13 | 3
(1 row)
This is with 8.0.2.
Just FYI.
... John
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tatsuo
Ishii
Sent: Tuesday, May 24, 2005 8:52 AM
To: [EMAIL PROTECTED]
Cc: pgsql-patches@postgresql.org; pgsql-hackers@postgresql.org
Subject: Re: [PATCHES] character type value is not padded with
spaces
Hackers,
The problem he found is not only existing in Japanese
characters but
also in any multibyte encodings including UTF-8. For me the patch
looks good and I will commit it to 7.3, 7.4, 8.0 stables
and current
if there's no objection.
--
Tatsuo Ishii
Character type value including multibyte characters is not
padded with
spaces. It reproduces at 7.3.x, 7.4.x and 8.0.x.
create table t (a char(10));
insert into t values ('X'); -- X is 2byte character.
I expect that 'X ' is inserted. But 'X' is inserted.
select a, octed_length(a) from t;
a | octet_length
---+--
X | 10
If padded with spaces, octet_length(a) is 15. This problem
is caused
that string length is calculated by byte length(VARSIZE) in
exprTypmod().
I attache the patch for this problem.
Regards,
--
Yoshiyuki Asaba
[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])
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] character type value is not padded with spaces
I think you need to test with 5 characters, not 3.
--
Tatsuo Ishii
Ahemm,...
UNICODE DB:
create table t (a char(10));
set client_encoding = iso88591;
insert into t VALUES ('æøå');
select a, octet_length(a),length(a) from t;
a | octet_length | length
+--+
æøå| 13 | 3
(1 row)
This is with 8.0.2.
Just FYI.
... John
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tatsuo Ishii
Sent: Tuesday, May 24, 2005 8:52 AM
To: [EMAIL PROTECTED]
Cc: pgsql-patches@postgresql.org; pgsql-hackers@postgresql.org
Subject: Re: [PATCHES] character type value is not padded with spaces
Hackers,
The problem he found is not only existing in Japanese
characters but also in any multibyte encodings including
UTF-8. For me the patch looks good and I will commit it to
7.3, 7.4, 8.0 stables and current if there's no objection.
--
Tatsuo Ishii
Character type value including multibyte characters is not
padded with
spaces. It reproduces at 7.3.x, 7.4.x and 8.0.x.
create table t (a char(10));
insert into t values ('X'); -- X is 2byte character.
I expect that 'X ' is inserted. But 'X' is inserted.
select a, octed_length(a) from t;
a | octet_length
---+--
X | 10
If padded with spaces, octet_length(a) is 15. This problem
is caused
that string length is calculated by byte length(VARSIZE) in
exprTypmod().
I attache the patch for this problem.
Regards,
--
Yoshiyuki Asaba
[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])
---(end of broadcast)---
TIP 8: explain analyze is your friend
[HACKERS] Deadlocks in 7.4.x ...
Client is running 7.4.6 right now (we're looking at moving to 7.4.8 when we can get a suitable ops window for the upgrade), and is experiencing semi-regular (10-16 times a day) deadlocks, with the following information: A little background: 1) We do not do explicit row or table locking. 2) We use serialized transactions quite often. 3) All deadlocks are fighting for the same relation: 4335271, which is the tsearch2 fulltext index on the articles table: articles_idxft1_idx. 4) I have a trigger on the articles table which is executed before insert or update. It updates the fulltext index column if any changes are made to the relevant article columns. This is the only place where changes are made to the fulltext index column. DETAIL: Process 8122 waits for AccessExclusiveLock on relation 4335271 of database 17142; blocked by process 11846. Process 11846 waits for ShareLock on transaction 2897133915; blocked by process 10042. Process 10042 waits for AccessExclusiveLock on relation 4335271 of database 17142; blocked by process 8122. -- DETAIL: Process 10042 waits for AccessExclusiveLock on relation 4335271 of database 17142; blocked by process 11846. Process 11846 waits for ShareLock on transaction 2897133915; blocked by process 10042. I haven't had much experience (okay, I've yet to use it) with tsearch2, but according to http://www.postgresql.org/docs/8.0/interactive/limitations.html, GiST does have concurrency issues The current implementation of GiST within PostgreSQL has some major limitations: GiST access is not concurrent Could this be what we are experiencing? This is a wee bit vague, but I'm not sure what direction(s) to look at for Deadlocks themselves, so pointers/ideas would be much appreciated ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] plpgsql trigger - does it auto-acquire a lock?
Second part to the other question ... the function/trigger attempts to be smart ... basically, it checks the updated record to see if there has been any changes that warrant updating the tsearch2 index, and only does it if it needs to ... Apparently, even if there are no changes, the Lock is being acquired ... kinda like what would happen if you were doing a SELECT * FROM table FOR UPDATE; inside of a transaction in order to lock the table ... but, there is nothing in the trigger that is, in fact, doing this ... Is there something special about a trigger that auto-acquires this sort of lock? If so, anywhere in the docs that mentions this that I should be reading through? Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Speeding up the Postgres lexer
What I'm wondering is whether this is really worth doing or not.
There are currently just two parts of the lexer rules that are affected
--- the {real} rule illustrated above, and the rules that allow quoted
strings to be split across lines as the SQL spec requires. But the
patches are still pretty ugly, and what's really annoying is that there
doesn't seem to be any way to get flex to complain if someone later
makes a change that breaks the no-backup-cases property again.
I was just thinking that if there's not a switch, it's prone to error again.
However, the lexer isn't touched anywhere near as much as the grammar is
right? So just put a large comment/warning/reminder at the top to
test for non-backup states.
I'm definitely in favour of a 1/3 speedup of the lexer.
Chris
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] Deadlocks in 7.4.x ...
I haven't had much experience (okay, I've yet to use it) with tsearch2, but according to http://www.postgresql.org/docs/8.0/interactive/limitations.html, GiST does have concurrency issues The current implementation of GiST within PostgreSQL has some major limitations: GiST access is not concurrent Could this be what we are experiencing? All GiST indexes allow only 1 writer at a time. Dunno if that's causing your headaches. The other thing that causes deadlocks is using foreign keys... Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Speeding up the Postgres lexer
But I do think it's worth it, even so ... not all client interfaces support prepared statements (notoriously PHP, although I understand KL has sent patches to fix that) and not all inserts are suitable for COPY. There is now pg_prepare/pg_execute/pg_query_params in PHP, however you could always have just used straight SQL PREPARE and EXECUTE commands. Chris ---(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] Speeding up the Postgres lexer
Simon Riggs [EMAIL PROTECTED] writes: On Mon, 2005-05-23 at 12:31 -0400, Tom Lane wrote: doesn't seem to be any way to get flex to complain if someone later makes a change that breaks the no-backup-cases property again. After some digging, there is a -b option will generate a file called lex.backup if any backup-states exist. The file is said to contain information that would help you remove backup-states. Right, reading that file is how I learned to fix it ... It seems straightforward to test for the existence of that file in the build process? Or perhaps add a special test state --enable-flextest to perform the test during the build. Well, the problem is that the success state isn't that the file doesn't exist or is empty or anything so easy as that. What you want is for it to say No backing up. and it seems a tad too ugly to code such a test into the makefiles. I'd not want to bet that the success message is spelled the same way in every flex version, or is locale-independent. Based on the comments so far in this thread, I'll go ahead and commit the patch, with some comments attached of course --- in particular a big head comment to run flex with -b and see that lex.backup says something to this effect. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Speeding up the Postgres lexer
Based on the comments so far in this thread, I'll go ahead and commit the patch, with some comments attached of course --- in particular a big head comment to run flex with -b and see that lex.backup says something to this effect. Add it to the release check-list. Chris ---(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] Speeding up the Postgres lexer
Christopher Kings-Lynne [EMAIL PROTECTED] writes: However, the lexer isn't touched anywhere near as much as the grammar is right? Yeah --- if you look at the CVS history, changes that affect the flex rules (and not just the text of the C-code actions) are really rare these days. If there were a lot of churn I'd think this proposal was completely impractical, but there's not much anymore. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Deadlocks in 7.4.x ...
Marc G. Fournier [EMAIL PROTECTED] writes: ... 3) All deadlocks are fighting for the same relation: 4335271, which is the tsearch2 fulltext index on the articles table: articles_idxft1_idx. DETAIL: Process 8122 waits for AccessExclusiveLock on relation 4335271 of database 17142; blocked by process 11846. Process 11846 waits for ShareLock on transaction 2897133915; blocked by process 10042. Process 10042 waits for AccessExclusiveLock on relation 4335271 of database 17142; blocked by process 8122. -- DETAIL: Process 10042 waits for AccessExclusiveLock on relation 4335271 of database 17142; blocked by process 11846. Process 11846 waits for ShareLock on transaction 2897133915; blocked by process 10042. Gist indexes require AccessExclusiveLock for any update, so the blocks on 4335271 just indicate an index update attempt. The ShareLock on transaction lines suggest a block on a row that is updated or selected FOR UPDATE. It's hard to say more without more info. Do you have any related foreign keys, for instance? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
