Thomas Hallgren <[EMAIL PROTECTED]> writes:
> 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 main
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 the
"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
(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
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
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
Tom Lane wrote:
> Bruce Momjian 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
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 n
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 indee
"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 i
Bruce Momjian 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
retroactively cause se
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.
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 trigge
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 flat
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 writes:
> > > -- Set escape environment for poss
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...
http://candle.pha.pa.us/mhonarc/patches/msg3.h
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 t
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 ...
> > >
> >
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: Do
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
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
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 ne
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$#
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 loop:
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
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 LOO
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$# RETUR
Martijn van Oosterhout 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 easier to all
Martijn van Oosterhout 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 logtape in adva
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
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
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 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 exp
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 =
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 "my
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 reverse-
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 writt
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 expe
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 di
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 re
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:
>
>
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
Subject:
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 no
On Tue, 2006-05-16 at 11:53 -0400, Andrew Dunstan wrote:
> 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
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 happen
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 ta
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 f
On 5/16/06, Martijn van Oosterhout 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
EnterpriseDB and WITH [RECUR
>
> 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 spe
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! Mai
Martijn van Oosterhout 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.
regards, tom lane
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 wei
> >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 assum
> >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 assume
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
___
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 f
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). An
On 5/16/06, Martijn van Oosterhout 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 schema1.mytable and schema
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 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
over
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
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 generat
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 the
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 th
> > 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 r
> > 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 think
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';
67 matches
Mail list logo
