[HACKERS] REPEATED INSERT INTO ...
I've noticed subsequent executions of the same insert command are slow. I've searched the list archives for this matter and found several entries related, including suggestions how to speed up. The standard answer from the core team is, use COPY. Sorry, but this is from an application point of view not really an option if you're dealing with program variables. We used to have an INGRES installation around, and since I know Postgres is based on it, may be this old INGRES feature is worth to consider: Taken from the Reference Manual [REPEATED] INSERT INTO ... The key word REPEATED directs INGRES to encode the INSERT and save its execution plan when it is first executed. This encoding can account for significant performance improvements on subsequent executions of the same INSERT. What do you others think of it? Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] REPEATED INSERT INTO ...
Christoph Haller <[EMAIL PROTECTED]> writes: > Taken from the Reference Manual > [REPEATED] INSERT INTO ... > The key word REPEATED directs INGRES to encode the INSERT and save its > execution plan when it is first executed. This encoding can account for > significant performance improvements on subsequent executions of the > same INSERT. > What do you others think of it? You can do that today with PREPARE/EXECUTE; there's no need to invent specialized syntax for it. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Thread safe ecpg
Hi all, I was just wondering.The patches for making ecpg thread safe that were floating around few days back, are they going to make in any near future releases? I am badly bitten by libpq as code i my multithreaded app. is growing steadily. I find myself making stupid mistakes every now and then.Of course I don't expect any language to solve them for me but with ecpg it would be bit easier. Just thinking.. Shridhar ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] REPEATED INSERT INTO ...
On Mon, 2003-02-24 at 07:22, Christoph Haller wrote: > I've noticed subsequent executions of the same insert command are slow. > I've searched the list archives for this matter and found several > entries > related, including suggestions how to speed up. > The standard answer from the core team is, use COPY. > Sorry, but this is from an application point of view not really an > option > if you're dealing with program variables. What do you mean by "program variables"? If you're just referring to variables in the programming language the DB client is written in, I see no reason why you couldn't use COPY to input those. > The key word REPEATED directs INGRES to encode the INSERT and save its > execution plan when it is first executed. This encoding can account for > significant performance improvements on subsequent executions of the > same INSERT. As Tom mentioned, we already support PREPARE/EXECUTE for this functionality -- but I'd suspect that for most INSERT queries, the parsing/rewriting/planning stages are relatively inexpensive, and the bulk of the runtime is spent in the executor (and writing WAL records, etc). So my guess would be that you won't see that much of a performance improvement from this... Cheers, Neil -- Neil Conway <[EMAIL PROTECTED]> || PGP Key ID: DB3C29FC ---(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] ILIKE
On Sat, 22 Feb 2003, Peter Eisentraut wrote: > AFAICT, ILIKE cannot use an index. So why does ILIKE even exist, when > lower(expr) LIKE 'foo' provides a solution that can use an index and is > more standard, too? I would guess because for lower(expr) to work you need to make an index on it. Since making ilike work invisibly would require the creation of an "invisible" lower(expr) index, it would double index storage requirements without warning the user. To make ilike invisible it might be worth setting up a GUC that controls automatic ilike index creation. That way ilike could either be a seq scan all the time function, which is great for certain operations anyway, or an automatically indexed operation. #create_ilike_indexes = false # costs 2x storage on index of text, char, types I like ilike, but it's seq scan nature is a bit klunky. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Outdated example in documentation
User's Guide section 7.2, example 7-1, claims that SELECT 2 ^ 3 AS "Exp"; will be equivalent, after type resolution, to SELECT CAST(2 AS double precision) ^ CAST(3 AS double precision) AS "Exp"; (which is true) or SELECT 2.0 ^ 3.0 AS "Exp"; which is not true, since 2.0 and 3.0 are nowadays of type numeric. Rather than deleting the third branch of this claim (which would also imply deleting the subsequent Note), does anyone want to think of a new example? Related observations: The premise of the example is that the operator ^ only exists for double precision arguments. ^ is implemented using SQL function dpow, which is implemented using C function dpow. There's also a documented SQL function pow, which is implemented using C function dpow. Wouldn't it be enough to have the documented SQL function pow and the operator on top of that? There's also a documented SQL function pow for "numeric", but no operator for it. Should that be added? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] I cant find it or I'm just lazy ?
I need two answers I did not find in documentation : How can I get exact number of rows in DECLARED CURSOR ? OK, I can FETCH until NULL, but this does not fits my needs ! How can I get information is TRANSACTION already started ? (TRANSACTION LEVEL) The interface I'm using is libpq. Regards ! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] More outdated examples
This example in User's Guide section 7.2 doesn't work anymore at all: tgl=> select @ text '-4.5' as "abs"; abs - 4.5 (1 row) What really happens is this: => select @ text '-4.5' as "abs"; ERROR: Unable to identify a prefix operator '@' for type 'text' You may need to add parentheses or an explicit cast Again, delete or new example? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Possibly inconsistent type casting logic
In the old days, when every function of the form foo(bar) was a cast from bar to foo, and if foo and bar happened to be binary compatible, the system short-circuited this function call to do a "zero-effort" cast. This logic still exists, but since in general casts are controlled through pg_cast now, it seems inconsistent that the old system still exists in some places. Did we forget that or is that intentional? The consequence of this feature put in other words appears to be that "if you create a binary-compatible cast from foo to bar, a function 'bar(foo) returns bar' will magically appear to exist". Maybe that's fine, but then we should probably document it more explicitly. -- Peter Eisentraut [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] ILIKE
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Josh Berkus writes: >> 4) It's just as indexible (or not indexable) as regexp comparisons, and easier >> to understand for users from the Microsoft world than regexp. > ILIKE is not indexible at all. You are arguing from a false premise. regression=# create table foo (f1 text unique); NOTICE: CREATE TABLE / UNIQUE will create implicit index 'foo_f1_key' for table 'foo' CREATE TABLE regression=# explain select * from foo where f1 ilike '123%'; QUERY PLAN Index Scan using foo_f1_key on foo (cost=0.00..17.07 rows=5 width=32) Index Cond: ((f1 >= '123'::text) AND (f1 < '124'::text)) Filter: (f1 ~~* '123%'::text) (3 rows) ILIKE is exactly as indexable as any other pattern that does the same thing. regards, tom lane ---(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] Linking to "current" docs
On Sunday 23 February 2003 20:52, Dave Page wrote: > > -Original Message- > > From: Ian Barwick [mailto:[EMAIL PROTECTED] > > > > Would it be possible to modify the new docs to provide > > similar functionality? E.g. something like > > http://www.postgresql.org/docs/view.php?version=current&idoc=0&file=sql- > createtable.html > > Done. Thanks! :-) Ian Barwick [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] ILIKE
Tom Lane kirjutas E, 24.02.2003 kell 19:30: > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > Hey, I don't want to take your ILIKE away. But at the time it was added > > the claim was that it was for compatibility and now we learn that that was > > wrong. This _is_ a compatibility feature, just not as straightforward as you may think, i.e. some databases have LIKE which behaves like our ILIKE. > That is something to make people aware of, for example in the > > documentation. > > It already does say > > : The keyword ILIKE can be used instead of LIKE to make the match case > : insensitive according to the active locale. This is not in the SQL > : standard but is a PostgreSQL extension. > > What else would you want to say? Perhaps add (From the mail of Josh Berkus): 3) It's an easy search-and-replace operator for porting applications from SQL databases which automatically do case-insensitive comparisons using LIKE, such as MySQL and some installations of MSSQL. --- Hannu ---(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] ILIKE
Josh Berkus writes: > 4) It's just as indexible (or not indexable) as regexp comparisons, and easier > to understand for users from the Microsoft world than regexp. ILIKE is not indexible at all. Other forms of pattern comparisons are at least indexible sometimes. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] ILIKE
Peter, > > 4) It's just as indexible (or not indexable) as regexp comparisons, and > > easier to understand for users from the Microsoft world than regexp. > > ILIKE is not indexible at all. Other forms of pattern comparisons are at > least indexible sometimes. And how is ~* indexable? -- Josh Berkus Aglio Database Solutions San Francisco ---(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] Hard problem with concurrency
On Mon, 17 Feb 2003 09:51:54 +0800, "Christopher Kings-Lynne"
<[EMAIL PROTECTED]> wrote:
>Strategy three:
>
>begin;
>lock table in exclusive mode;
>update row;
>if (no rows affected) insert row;
>commit;
>
>Problem - Works, but this table needs high concurrency.
Chris,
distributing congestion might improve that. Instead of locking the
whole table just lock one row in a dummy table:
CREATE TABLE dummylock(id INT PRIMARY KEY);
INSERT INTO dummylock VALUES (0);
INSERT INTO dummylock VALUES (1);
...
INSERT INTO dummylock VALUES (999);
Create an immutable function lockhash() returning a value
between 0 and 999.
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE t SET c = 'newval' WHERE pk = 'pk';
if (no rows affected) THEN
SELECT * FROM dummylock
WHERE id = lockhash('pk') FOR UPDATE;
-- try again
UPDATE t SET c = 'newval' WHERE pk = 'pk';
if (no rows affected) THEN
INSERT INTO t ...;
END IF;
END IF;
COMMIT;
This is just an idea. Completely untested ...
Servus
Manfred
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] ILIKE
On Sun, 2003-02-23 at 23:31, Tom Lane wrote: > Josh Berkus <[EMAIL PROTECTED]> writes: > > - Some other databases support ILIKE and it makes porting easier. > > Which other ones? I checked our archives and found that when we were > discussing adding ILIKE, it was claimed that Oracle had it. But I can't > find anything on the net to verify that claim. I did find that mSQL > (not MySQL) had it, as far back as 1996. Nothing else seems to --- but > Google did provide a lot of hits on pages saying that ILIKE is a mighty > handy Postgres-ism ;-) Isn't MySQL case insensitive by default? I know the ='s operator is (was?) 'a' = 'A' -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] ILIKE
Tom Lane writes: > My feeling too. Whatever you may think of its usefulness, it's been a > documented feature since 7.1. It's a bit late to reconsider. It's never too late for new users to reconsider. It's also never too late to change your application of performance is not satisfactory. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ILIKE
Josh Berkus writes: > - Some other databases support ILIKE and it makes porting easier. Which database would that be? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] ILIKE
On Mon, 24 Feb 2003, Peter Eisentraut wrote: > Tom Lane writes: > > > My feeling too. Whatever you may think of its usefulness, it's been a > > documented feature since 7.1. It's a bit late to reconsider. > > It's never too late for new users to reconsider. It's also never too late > to change your application of performance is not satisfactory. And if performance is satisfactory? Vince. -- Fast, inexpensive internet service 56k and beyond! http://www.pop4.net/ http://www.meanstreamradio.com http://www.unknown-artists.com Internet radio: It's not file sharing, it's just radio. ---(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] ILIKE
Peter Eisentraut wrote: Tom Lane writes: My feeling too. Whatever you may think of its usefulness, it's been a documented feature since 7.1. It's a bit late to reconsider. It's never too late for new users to reconsider. It's also never too late to change your application of performance is not satisfactory. Well, ILIKE has been a feature for quite some time and the amount of negative feedback we've been receiving about upgrade problems makes me feel that _removing_ it would be detrimental. (i.e. broken applications) As an alternative to _removing_ it, would a feasible idea be to transparently alias it to something else, say a specific type of regex query or something? Regards and best wishes, Justin Clift -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] ILIKE
On Tue, 25 Feb 2003, Justin Clift wrote: > Peter Eisentraut wrote: > > Tom Lane writes: > > > >>My feeling too. Whatever you may think of its usefulness, it's been a > >>documented feature since 7.1. It's a bit late to reconsider. > > > > It's never too late for new users to reconsider. It's also never too late > > to change your application of performance is not satisfactory. > > > > Well, ILIKE has been a feature for quite some time and the amount of > negative feedback we've been receiving about upgrade problems makes me > feel that _removing_ it would be detrimental. (i.e. broken applications) > > As an alternative to _removing_ it, would a feasible idea be to > transparently alias it to something else, say a specific type of regex > query or something? Why screw with it for the sake of screwing with it? Vince. -- Fast, inexpensive internet service 56k and beyond! http://www.pop4.net/ http://www.meanstreamradio.com http://www.unknown-artists.com Internet radio: It's not file sharing, it's just radio. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ILIKE
Vince Vielhaber wrote: Why screw with it for the sake of screwing with it? Hmmm, good point. "If it aint broke" ? Regards and best wishes, Justin Clift Vince. -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ILIKE
Vince Vielhaber <[EMAIL PROTECTED]> writes: > On Tue, 25 Feb 2003, Justin Clift wrote: >> As an alternative to _removing_ it, would a feasible idea be to >> transparently alias it to something else, say a specific type of regex >> query or something? > Why screw with it for the sake of screwing with it? AFAICT, Peter isn't interested in changing the implementation, but in removing it outright (to reduce our nonstandardness, or something like that). While we've removed marginal features in the past, I think this one is sufficiently popular that there's no chance of removing it just on the strength of the argument that it's not standard. The efficiency argument seemed irrelevant --- AFAICT, ILIKE is exactly as indexable as any equivalent regex substitute, which is to say "only if the pattern's leading characters are fixed (nonalphabetic)". regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ILIKE
Vince Vielhaber writes: > > It's never too late for new users to reconsider. It's also never too late > > to change your application of performance is not satisfactory. > > And if performance is satisfactory? Hey, I don't want to take your ILIKE away. But at the time it was added the claim was that it was for compatibility and now we learn that that was wrong. That is something to make people aware of, for example in the documentation. -- Peter Eisentraut [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] ILIKE
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Hey, I don't want to take your ILIKE away. But at the time it was added > the claim was that it was for compatibility and now we learn that that was > wrong. That is something to make people aware of, for example in the > documentation. It already does say : The keyword ILIKE can be used instead of LIKE to make the match case : insensitive according to the active locale. This is not in the SQL : standard but is a PostgreSQL extension. What else would you want to say? regards, tom lane ---(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] request for sql3 compliance for the update command
Given that the direction of the spec seems to be headed towards the desired syntax, can we put this on the TODO list? Dave On Thu, 2003-02-20 at 11:49, Dave Cramer wrote: > Scott, > > I can't find page 858 in that document, is it the right one? > > also the link s/b ? > > ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf > > Dave > On Thu, 2003-02-20 at 11:20, scott.marlowe wrote: > > On Thu, 20 Feb 2003, Tom Lane wrote: > > > > > Hannu Krosing <[EMAIL PROTECTED]> writes: > > > > Are you against it just on grounds of cleanliness and ANSI compliance, > > > > or do you see more serious problems in letting it in ? > > > > > > At this point it seems there are two different things being tossed > > > about. I originally understood Dave to be asking for parens to be > > > allowed around individual target column names, which seems a useless > > > frammish to me. What Bruce has pointed out is that a syntax that lets > > > you assign multiple columns from a single rowsource would be an actual > > > improvement in functionality, or at least in convenience and efficiency. > > > (It would also be a substantial bit of work, which is why I think this > > > isn't what Dave was offering a quick patch to do...) What I'd like to > > > know right now is which interpretation Informix actually implements. > > > > > > I don't like adding nonstandard syntaxes that add no functionality --- > > > but if Informix has done what Bruce is talking about, that's a different > > > matter altogether. > > > > Tom, I was purusing the wild and wonderfully exciting new SQL > > > > (found here: > > ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf) > > > > ANSI TC NCITS H2 > > ISO/IEC JTC 1/SC 32/WG 3 > > Database > > > > document to see what it had to say, and on this subject, and it looks like > > update is going to be supporing this same style we're discussing here. > > > > Look on or around p. 858 in that doc.) -- Dave Cramer <[EMAIL PROTECTED]> Cramer Consulting ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ILIKE
Four Reasons to use ILIKE, which have nothing to do with mSQL: 1) It's faster to type than most analagous regexp comparisons, and much faster than comparing two LOWERs or two UPPERS. 2) It's a great operator for comparing two text variables or columns of small tables where you don't want to worry about escaping the many items of regexp punctuation. 3) It's an easy search-and-replace operator for porting applications from SQL databases which automatically do case-insensitive comparisons using LIKE, such as MySQL and some installations of MSSQL. 4) It's just as indexible (or not indexable) as regexp comparisons, and easier to understand for users from the Microsoft world than regexp. And, on a quick search, one of my applications uses ILIKE 21 times in the built in functions and views. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] I cant find it or I'm just lazy ?
On Mon, Feb 24, 2003 at 07:53:05PM +, Darko Prenosil wrote: > I need two answers I did not find in documentation : > > How can I get exact number of rows in DECLARED CURSOR ? > OK, I can FETCH until NULL, but this does not fits my needs ! You need to move to the end of the cursor. When you declare a cursor it doesn't run the query yet. You have to tell it to run the query before it can tell you how many rows it is. I think the command is MOVE. -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Support bacteria! They're the only culture some people have. pgp0.pgp Description: PGP signature
[HACKERS] Intel drops 64-bit Itanium
After failing to make Itanium competitive, Intel is now downplaying 64-bit CPU's. Of course, they didn't think that until Itanium failed. Here is the slashdot story: http://slashdot.org/article.pl?sid=03/02/23/2050237&mode=nested&tid=118 Seems AMD's hammer is going to be the popular 64-bit desktop CPU. -- 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] Intel drops 64-bit Itanium
> After failing to make Itanium competitive, Intel is now downplaying > 64-bit CPU's. Of course, they didn't think that until Itanium failed. > Here is the slashdot story: > http://slashdot.org/article.pl?sid=03/02/23/2050237&mode=nested&tid=118 > > Seems AMD's hammer is going to be the popular 64-bit desktop CPU. It's really unsurprising; there was /no/ likelihood of Itanium getting widely deployed on desktops when there would be an absolute dearth of desktop software. Think back: Alpha was presented in /exactly/ the same role, years ago, and the challenges it had vis-a-vis: a) Need for emulation to run legacy software that can't get recompiled; b) Need to deploy varying binaries on the substantially varying platforms; c) It's real costly to be an early adoptor of new hardware, so the hardware is expensive stuff. Certain sorts of "enterprise" software got deployed on Alpha, but you never got the ordinary stuff like MS Office and such, which meant there was no point to anyone pushing "desktop" software to Alpha. And we thereby had the result that Alpha became server-only. Why should it be the slightest bit remarkable that IA-64 is revisiting the very same marketing challenges? It has the very same set of technical challenges. It may well be that by the time it /is/ time to generally deploy IA-64, it will have become the Alpha platform. After all, Compaq sold the architecture to Intel, and Alpha already has a mature set of hardware designs as well as compilers... -- (reverse (concatenate 'string "gro.gultn@" "enworbbc")) http://www3.sympatico.ca/cbbrowne/oses.html "Everything should be built top-down, except the first time." -- Alan Perlis ---(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
