On May 16, 2006, at 14:17 , Tom Lane wrote:
[1] Mostly. There's the infamous continued-string-literal
construct...
Are you referring to this?
est=# SELECT 'foo''bar'; -- invalid
ERROR: syntax error at or near 'bar' at character 17
LINE 1: SELECT 'foo''bar';
Given that any time that happens we end up caring much less about
CPU
usage and much more about disk IO, for any of these cases that use
non-random access, compressing the data before sending it to disk
would
potentially be a sizeable win.
Note however that what the code thinks is a
Personally, I believe it would be worth it - but only to a few. And
these most of these few are likely using Oracle. So, no gain unless
you can convince them to switch back... :-)
We do know that the benefit for commercial databases that use raw and
file system storage is that raw
On Tue, May 16, 2006 at 09:15:13AM +0530, Gurjeet Singh wrote:
I don't think Cristiano is asking for the schema_name in the
EXPLAIN o/p. The request is for the table ALIASes to be shown in the
o/p, which makes more sense than schema_name+table_name, since the
same table can be used in the
I think Jonah is referring to the the 'START WITH ... CONNECT BY'
clause feature from Oracle. Am I right Jonah?
For such queries, Oracle introduces a pseudocolumn LEVEL, that
holds the value of the indentation level of the current rusultant row.
In Oracle, the LEVEL column returns 0 for
Hello
I test using index on foreign key. I found situation, when planner choose
worse plan.
create table f1(pk serial primary key);
create table f2(fk integer references f1(pk));
insert into f1 select a from generate_series(1,1) a;
insert into f2 select (random()*)::int+1 from
On Tue, May 16, 2006 at 03:18:19PM +0530, Gurjeet Singh wrote:
LEVEL might not be a part of the standard, but it is very handy
when dealing with hierarchical queries.
The chapter 1 (http://www.oreilly.com/catalog/sqlpr/chapter/ch01.pdf)
of book 'SQL Pocket Guide' elaborates more on it.
On Tue, May 16, 2006 at 11:52:05AM +0200, Pavel Stehule wrote:
Hello
I test using index on foreign key. I found situation, when planner choose
worse plan.
Can we seen an EXPLAIN ANALYZE output to see where the miscalculation
lies. Is it underestimating the cost of the index scan, or
Can we seen an EXPLAIN ANALYZE output to see where the miscalculation
lies. Is it underestimating the cost of the index scan, or
overestimating the cost of the hash join.
postgres= explain analyze select count(*) from f1 join f2 on pk=fk;
On 5/16/06, Martijn van Oosterhout kleptog@svana.org wrote:
On Tue, May 16, 2006 at 09:15:13AM +0530, Gurjeet Singh wrote:
As has been pointed out, aliases ave always been displayed. The OPs
problem was that he was using schema.tablename everywhere and explain
didn't distinguish between
On Tue, May 16, 2006 at 04:36:36PM +0530, Gurjeet Singh wrote:
If a big JOIN query takes N minutes on MS SQL Server, and I wish
to see the plan why it is taking that long, one would expect me to
enable 'Show Execution Plan' in the SQL Query Analyzer (similar to
PG's EXPLAIN ANALYZE). And
On Tue, May 16, 2006 at 12:54:58PM +0200, Pavel Stehule wrote:
Can we seen an EXPLAIN ANALYZE output to see where the miscalculation
lies. Is it underestimating the cost of the index scan, or
overestimating the cost of the hash join.
postgres= explain analyze select count(*) from f1 join f2
These are all minor abberations though, on the whole the estimates are
pretty good. Perhaps you need to tweak the values of random_page_cost
and similar variables.
Thank You, It's general problem or only mine? I have 100% standard current
PC.
Pavel
These are all minor abberations though, on the whole the estimates
are
pretty good. Perhaps you need to tweak the values of random_page_cost
and similar variables.
Thank You, It's general problem or only mine? I have 100%
standard current PC.
The default random_page_cost assumes some
These are all minor abberations though, on the whole the estimates
are
pretty good. Perhaps you need to tweak the values of random_page_cost
and similar variables.
Thank You, It's general problem or only mine? I have 100%
standard current PC.
The default random_page_cost assumes some
Michael Glaesemann [EMAIL PROTECTED] writes:
On May 16, 2006, at 14:17 , Tom Lane wrote:
[1] Mostly. There's the infamous continued-string-literal
construct...
Are you referring to this?
^
test=# SELECT 'foo'
test-# 'bar'; -- valid
Yeah. It seems weird that a
Martijn van Oosterhout kleptog@svana.org writes:
I'm assuming that what's actually being implemented is the SQL standard
method with the Oracle alternative being another way of specifying the
same thing?
What's being implemented should be the standard. Full stop.
Hi to all!I am a student in Computer Science, and in Databases' Technologycourse I must to writea report on physical structures of DBMS's. I choosed PostgreSQL; Ilooked the sources, andI have understood the block structure. And relations? And databases?How are structured?Thanks!!!
Yahoo!
Compressed-filesystem extension (like e2compr, and I think either
Fat or NTFS) can do that.
Windows (NT/2000/XP) can compress individual directories and files under
NTFS; new files in a compressed directory are compressed by default.
So if the 'spill-to-disk' all happened in its own
On 5/16/06, Martijn van Oosterhout kleptog@svana.org wrote:
I'm assuming that what's actually being implemented is the SQL standard
method with the Oracle alternative being another way of specifying the
same thing?
I'm working on both versions of hierarchical queries; CONNECT BY for
On 5/16/06, winlinchu [EMAIL PROTECTED] wrote:
I must to write a report on physical structures of DBMS's.
I choosed PostgreSQL; I looked the sources, and I have
understood the block structure. And relations? And databases?
How are structured?
Is there a question in there?
If you're looking
Gurjeet Singh wrote:
I don't think Cristiano is asking for the schema_name in the
EXPLAIN o/p.
In fact I'm requesting for schema_name in EXPLAIN o/p.
The request is for the table ALIASes to be shown in the
o/p, which makes more sense than schema_name+table_name, since the
same table
Bort, Paul wrote:
Compressed-filesystem extension (like e2compr, and I think either
Fat or NTFS) can do that.
Windows (NT/2000/XP) can compress individual directories and files under
NTFS; new files in a compressed directory are compressed by default.
So if the 'spill-to-disk' all
Rod Taylor wrote:
I habitually turn off all compression on my Windows boxes, because it's
a performance hit in my experience. Disk is cheap ...
Disk storage is cheap. Disk bandwidth or throughput is very expensive.
Sure, but in my experience using Windows File System compression is
hackers - any opinions?
The biggest downside would be that a pgpass file would be version
specific for this feature. The badness of this is somewhat mitigated by
the ability we now have to specify an alternative pgpassfile location.
cheers
andrew
Original Message
Uh, why wouldn't we allow localhost to match the default unix domain
socket name, as well as an empty hostname? If you specify a non-default
location, you then have to specify the full path.
---
Andrew Dunstan wrote:
Doug,
But what if the user calls the access function, sees the data, then
issues a ROLLBACK? The audit record would be rolled back as well (as
Tom pointed out earlier).
You could use dblink to log to a separate audit database, I suppose.
Or just write to some other non-transational
On Tue, May 16, 2006 at 09:24:38AM +0200, Zeugswetter Andreas DCP SD wrote:
Given that any time that happens we end up caring much less about
CPU
usage and much more about disk IO, for any of these cases that use
non-random access, compressing the data before sending it to disk
would
On Tue, May 16, 2006 at 12:27:42PM -0400, Andrew Dunstan wrote:
Rod Taylor wrote:
I habitually turn off all compression on my Windows boxes, because it's
a performance hit in my experience. Disk is cheap ...
Disk storage is cheap. Disk bandwidth or throughput is very expensive.
Hey,
On Tue, May 16, 2006 at 04:36:36PM +0530, Gurjeet Singh wrote:
I don't think adding a schema_name to EXPLAIN's output, is really
a good idea!! The ALIAS feature of the SQL language caters to this
very need of assigning unambiguous names to tables.
That's assuming that the query was written
I agree... VERBOSE option can be made parameterised to include
additional information in the EXPLAIN's output.
I also agree that adding the schema name wouldn't add any
overhead, and I support Tom's suggestion: 'Possibly a reasonable
compromise would be for EXPLAIN to act like rule
Cristiano Duarte wrote:
SQL table aliases doesn't help locating the real place where the table is.
If I have a table named test at the schema place and I do:
EXPLAIN SELECT * FROM place.test mytest
I will get:
Seq Scan on test mytest
With this output I know that mytest is an alias
Joshua D. Drake wrote:
Hello,
I was dinking around wand came across something that may (or may not be
useful).
What if single line statements that were seperated by ; within psql were
implicitly within a transaction?
E.g;
postgres=# select * from foo; update foo set bar = 'baz';
On Tue, May 16, 2006 at 12:31:07PM -0500, Jim C. Nasby wrote:
In any case, my curiousity is aroused, so I'm currently benchmarking
pgbench on both a compressed and uncompressed $PGDATA/base. I'll also do
some benchmarks with pg_tmp compressed.
Results: http://jim.nasby.net/bench.log
As
On Tue, May 16, 2006 at 12:31:07PM -0500, Jim C. Nasby wrote:
Does anyone have time to hack some kind of compression into the on-disk
sort code just to get some benchmark numbers? Unfortunately, doing so is
beyond my meager C abilitiy...
I had a look at this. At first glance it doesn't seem
On Tue, May 16, 2006 at 11:46:15PM +0200, Martijn van Oosterhout wrote:
On Tue, May 16, 2006 at 12:31:07PM -0500, Jim C. Nasby wrote:
Does anyone have time to hack some kind of compression into the on-disk
sort code just to get some benchmark numbers? Unfortunately, doing so is
beyond my
On Tue, May 16, 2006 at 04:50:22PM -0500, Jim C. Nasby wrote:
I had a look at this. At first glance it doesn't seem too hard, except
the whole logtape process kinda gets in the way. If it wern't for the
mark/restore it'd be trivial. Might take a stab at it some time, if I
can think of a
Martijn van Oosterhout kleptog@svana.org writes:
Not seek, mark/restore. As the code describes, sometimes you go back a
tuple. The primary reason I think is for the final pass, a merge sort
might read the tuples multiple times, so it needs to support it there.
However it'd be possible to tell
Martijn van Oosterhout kleptog@svana.org writes:
It might be easier to switch to giving each tape it's own file...
I don't think it would make much difference. OTOH, if this turns out to
be a win, the tuplestore could have the same optimisation.
Would giving each tape its own file make it
Hellow PostgreSQL hackers,
Quick question. Why does the 'i = i + 1' syntax work in this PL/pgSQL
function?
try=# CREATE OR REPLACE FUNCTION inc_by_two(
try(#upfrom int,
try(#upto int
try(# ) RETURNS SETOF INT AS $$
try$# BEGIN
try$# FOR i IN upfrom..upto LOOP
try$#
David Wheeler wrote:
Hellow PostgreSQL hackers,
Quick question. Why does the 'i = i + 1' syntax work in this PL/pgSQL
function?
try=# CREATE OR REPLACE FUNCTION inc_by_two(
try(#upfrom int,
try(#upto int
try(# ) RETURNS SETOF INT AS $$
try$# BEGIN
try$# FOR i IN upfrom..upto
David Wheeler wrote:
Hellow PostgreSQL hackers,
Quick question. Why does the 'i = i + 1' syntax work in this PL/pgSQL
function?
try=# CREATE OR REPLACE FUNCTION inc_by_two(
try(#upfrom int,
try(#upto int
try(# ) RETURNS SETOF INT AS $$
try$# BEGIN
try$# FOR i IN
On May 16, 2006, at 16:30, Andrew Dunstan wrote:
It ought to be illegal to modify the loop control variable anyway,
IMNSHO - it certainly is in Ada, the distant antecedent of pl/pgsql.
I agree, but I must say that it's incredibly useful to be able to
increment by two as I go through a
Mark Dilger wrote:
David Wheeler wrote:
Hellow PostgreSQL hackers,
Quick question. Why does the 'i = i + 1' syntax work in this PL/pgSQL
function?
try=# CREATE OR REPLACE FUNCTION inc_by_two(
try(#upfrom int,
try(#upto int
try(# ) RETURNS SETOF INT AS $$
try$# BEGIN
try$# FOR i
On May 16, 2006, at 16:42, Mark Dilger wrote:
So I don't know why it works for you. I wrote the following, and
it also
increments the variable:
CREATE OR REPLACE FUNCTION weird () RETURNS SETOF INT AS $$
DECLARE
i integer;
BEGIN
i := 0;
return next i;
i = i + 1;
return
On May 16, 2006, at 16:53, Mark Dilger wrote:
Sorry, I meant to say that it should only be a no-op according to
the language
specification, as I understand it. The fact that it works suggests
to me that
the implementation of PL/pgsql has been modified (or broken?).
Whether the
change is
David Wheeler wrote:
On May 16, 2006, at 16:53, Mark Dilger wrote:
Sorry, I meant to say that it should only be a no-op according to the
language
specification, as I understand it. The fact that it works suggests
to me that
the implementation of PL/pgsql has been modified (or broken?).
On May 16, 2006, at 17:02, Mark Dilger wrote:
It seems this has been answered before, by Tom Lane:
http://archives.postgresql.org/pgsql-novice/2006-04/msg00138.php
Ah, cool, then it *is* known.
Thanks,
David
---(end of broadcast)---
TIP 2:
On Tue, May 16, 2006 at 12:31:07PM -0500, Jim C. Nasby wrote:
On Tue, May 16, 2006 at 12:27:42PM -0400, Andrew Dunstan wrote:
Rod Taylor wrote:
I habitually turn off all compression on my Windows boxes, because it's
a performance hit in my experience. Disk is cheap ...
Disk storage is
On 5/16/06, David Wheeler [EMAIL PROTECTED] wrote:
On May 16, 2006, at 16:30, Andrew Dunstan wrote:
It ought to be illegal to modify the loop control variable anyway,
IMNSHO - it certainly is in Ada, the distant antecedent of pl/pgsql.
I agree, but I must say that it's incredibly useful to
On May 16, 2006, at 17:51, Jaime Casanova wrote:
i have posted a patch to add a BY clause to the for statement (integer
version), with the BY clause you can specify an increment value...
it's in the unapplied patches list waiting for review...
I have seen no reply to my suggestion below, so I assume it is the way
people want to go for 7.3, 7.4, and 8.0.
---
Bruce Momjian wrote:
Tom Lane wrote:
Bruce Momjian pgman@candle.pha.pa.us writes:
-- Set escape
Andrew Dunstan [EMAIL PROTECTED] writes:
It ought to be illegal to modify the loop control variable anyway,
IMNSHO - it certainly is in Ada, the distant antecedent of pl/pgsql.
Distant ancestors aren't particularly relevant here. What plpgsql tries
to be is a ripoff^H^H^H^H^H^Hsincere
Do we have any plans of introducing 'AUTONOMOUS TRANSACTION' like feature?
Again, it might not be a part of the standard but it is very
helpful in situations like these!!! You can run a trigger with an
autonomous transaction attached to it, which guarantees that the work
done by
On May 16, 2006, at 19:52, Tom Lane wrote:
Distant ancestors aren't particularly relevant here. What plpgsql
tries
to be is a ripoff^H^H^H^H^H^Hsincere flattery of Oracle's PL/SQL. If
modifying the loop variable is disallowed in PL/SQL, I'm all for
disallowing it in plpgsql, otherwise not.
Bruce Momjian pgman@candle.pha.pa.us writes:
I have seen no reply to my suggestion below, so I assume it is the way
people want to go for 7.3, 7.4, and 8.0.
I'm not particularly for it, if that's what you meant, and certainly not
for hacking up old branches that way. For one thing, you can't
Gurjeet Singh [EMAIL PROTECTED] writes:
Do we have any plans of introducing 'AUTONOMOUS TRANSACTION' like
feature?
No.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore
Andrew Piskorski [EMAIL PROTECTED] writes:
The main tricks seem to be: One, EXTREMELY lightweight compression
schemes - basically table lookups designed to be as cpu friendly as
posible. Two, keep the data compressed in RAM as well so that you can
also cache more of the data, and indeed
David Wheeler [EMAIL PROTECTED] writes:
Even if PL/SQL disallows it, why would you not allow it in PL/pgSQL?
Um ... design sanity and consistency, maybe? Not that I think PL/SQL
is any paragon of those virtues, but surely we'll invent any feature
we feel like whether it's sane or not is not a
Tom Lane wrote:
Bruce Momjian pgman@candle.pha.pa.us writes:
I have seen no reply to my suggestion below, so I assume it is the way
people want to go for 7.3, 7.4, and 8.0.
I'm not particularly for it, if that's what you meant, and certainly not
for hacking up old branches that way. For
On May 16, 2006, at 20:51, Tom Lane wrote:
Um ... design sanity and consistency, maybe? Not that I think PL/SQL
is any paragon of those virtues, but surely we'll invent any feature
we feel like whether it's sane or not is not a recipe for a
maintainable language.
Yes, sanity is important, I
Greg Stark [EMAIL PROTECTED] writes:
Andrew Piskorski [EMAIL PROTECTED] writes:
A corrolary of that is forget compression schemes like gzip - it
reduces data size nicely but is far too slow on the cpu to be
particularly useful in improving overall throughput rates.
There are some very fast
(No, I'm not particularly in favor of the BY feature mentioned upthread,
either.)
regards, tom lane
mmm... and why is that? i mean, many languages have some way to
increment the for variable by different values... call it STEP, BY or
even i+=number
--
regards,
Jaime Casanova [EMAIL PROTECTED] writes:
(No, I'm not particularly in favor of the BY feature mentioned upthread,
either.)
mmm... and why is that?
Essentially because it's not in the upstream language. Oracle could
come out with the same feature next week, only they use STEP or some
other
Some users of PL/Java make use of a non-default connection from within a Trigger in order to
do this. In essence, they load the client JDBC package into the backend to let the backend
as such become a client. The second connection is then maintained for the lifetime of the
first. Perhaps not
65 matches
Mail list logo
