On Apr 2, 2008, at 9:02 PM, Alex Solovey wrote:
The reduced database example has the same problem in EXPLAIN
ANALYZE as production one, here:
Seq Scan on bar (cost=0.00..393.07 rows=1 width=4) (actual
time=0.098..3.561 rows=24 loops=1)
Hang on... You prefer sequential scans because
Alban Hertroys wrote:
On Apr 2, 2008, at 9:02 PM, Alex Solovey wrote:
The reduced database example has the same problem in EXPLAIN ANALYZE
as production one, here:
Seq Scan on bar (cost=0.00..393.07 rows=1 width=4) (actual
time=0.098..3.561 rows=24 loops=1)
Hang on... You prefer
Tom Lane wrote:
This is what I am wondering. Whether it is done this way due to
expecation/standard, or as an implementation side effect. In the
latter case it is fixable.
I don't see how this could break a standard.
Actually, I think it does, because we went to great lengths to cause
create or replace function sum_elements(anyarray)
returns anyelement as $$
select sum($1[i])
from generate_series(array_lower($1,1),
array_upper($1,1)) g(i);
$$ language sql immutable;
Thank you! Anyway what I was really asking was a $ (or whatever
On Wednesday 2. April 2008, Tomasz Ostrowski wrote:
Go for it. Even 64 (I like round numbers) would not be too much.
Geek test: Do you find the above statement odd?
Yes: 0, No: +10.
(Sorry for being massively off-topic :-))
--
Leif Biberg Kristensen | Registered Linux User #338009
I'm reviewing some function I wrote to add stable, immutable where
needed and I'd like to take the chance to add further cheap
optimisation if it helps.
There are many places where I know a function or a statement will
return just one row?
Is it helpful to add LIMIT 1?
eg.
select a, b from
We are annotating nodes on a hierarchical structure
where NULL implied an
I don't mean to be rude, but yuck. Why provide a
record for data that isn't there?
No offence taken - I'm trying to improve an old (partially inherited)
system, hence the original post.
I have no idea if this
On Thu, Apr 3, 2008 at 9:18 AM, Leif B. Kristensen [EMAIL PROTECTED] wrote:
On Wednesday 2. April 2008, Tomasz Ostrowski wrote:
Go for it. Even 64 (I like round numbers) would not be too much.
Geek test: Do you find the above statement odd?
Sadly, no.
--
Dave Page
EnterpriseDB UK Ltd:
hi, now i meet a question about the complex field. Firstly i create a complex
type (the sql is CREATE TYPE test11 as (area numeric, address character
varying(30)))and then i create a table ,set a field named tt whose type is
test11. Now i want insert the data which select from another talble
Ivan Sergio Borgonovo [EMAIL PROTECTED] writes:
I'm reviewing some function I wrote to add stable, immutable where
needed and I'd like to take the chance to add further cheap
optimisation if it helps.
There are many places where I know a function or a statement will
return just one row?
Gregory Stark [EMAIL PROTECTED] writes:
You could fix that more cleanly with ALTER FUNCTION myfunction ROWS 1 but
only if that's always true, not just for myfunction(3,5).
Perhaps the function shouldn't be declared SETOF in the first place?
regards, tom lane
--
Sent
On Thu, 03 Apr 2008 10:33:56 -0400
Tom Lane [EMAIL PROTECTED] wrote:
Gregory Stark [EMAIL PROTECTED] writes:
You could fix that more cleanly with ALTER FUNCTION myfunction
ROWS 1 but only if that's always true, not just for
myfunction(3,5).
Perhaps the function shouldn't be declared
Hi,
I've come across a strange deadlock that I need your help with. There
are two copies of the same Perl daemon running on a 2 cpu box. The
program is pretty simple (because I wrote it :)) so I can trace its
pathway fairly well: in it, there's a single LOCK table foo occurring
part way
Pavan Deolasee [EMAIL PROTECTED] writes:
Please see the attached patch. One change I made is to hold the SHARE lock
on the page while ANALYZE is reading tuples from it. I thought it would
be a right thing to do instead of repeatedly acquiring/releasing the lock.
I've applied a
Given this type query:
UPDATE bw_pool
SET user_id=?
WHERE bw_id=
(SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL)
RETURNING bw_id
The idea is to single-threadedly get at the next available empty slot,
no matter how many such queries run in
On Thu, Apr 3, 2008 at 10:29 AM, rihad [EMAIL PROTECTED] wrote:
Hi,
I've come across a strange deadlock that I need your help with. There are
two copies of the same Perl daemon running on a 2 cpu box. The program is
pretty simple (because I wrote it :)) so I can trace its pathway fairly
On Thu, Apr 3, 2008 at 10:02 PM, Tom Lane [EMAIL PROTECTED] wrote:
I've applied a modified/extended form of this patch for 8.3.2.
Thanks. I had another concern about VACUUM not reporting DEAD line
pointers (please see up thread). Any comments on that ?
Thanks,
Pavan
--
Pavan Deolasee
Hi All
I hope this isn't a FAQ, but does anyone have any suggestions as to
how to make a query that selects using:
where in(comma delimited list)
secure from an sql injection point of view?
I have grid of tiles I'm using to reference geographical points.
These tiles are identical to the tiling
On Thu, Apr 3, 2008 at 10:44 AM, rihad [EMAIL PROTECTED] wrote:
Given this type query:
UPDATE bw_pool
SET user_id=?
WHERE bw_id=
(SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL)
RETURNING bw_id
The idea is to single-threadedly get at
Tom Lane wrote:
Andreas [EMAIL PROTECTED] writes:
make[3]: *** No rule exists for Target �utf8_and_euc_jis_2004.o�,
needed to create �libutf8_and_euc_jis_2004.so.0.0�. END.
There are two or three reports like this in the archives. It appears to
be related to using an old version of
rihad wrote:
Hi,
I've come across a strange deadlock that I need your help with. There
are two copies of the same Perl daemon running on a 2 cpu box. The
program is pretty simple (because I wrote it :)) so I can trace its
pathway fairly well: in it, there's a single LOCK table foo occurring
Pavan Deolasee [EMAIL PROTECTED] writes:
Thanks. I had another concern about VACUUM not reporting DEAD line
pointers (please see up thread). Any comments on that ?
If you want to work on that, go ahead, but I wanted it separate because
I didn't think it merited back-patching. It's strictly
I hope this isn't a FAQ, but does anyone have any
suggestions as to
how to make a query that selects using:
where in(comma delimited list)
secure from an sql injection point of view?
As the length of the comma delimited list is highly
variable I don't
think I can use a prepared query
On Thu, Apr 3, 2008 at 11:50 AM, William Temperley
[EMAIL PROTECTED] wrote:
This works very well, however I'm currently directly concatenating a sql
query:
select st_collect(the_geom) from tiles where tilename in
(comma delimited list))
Which leaves my application vulnerable to sql
On Thu, Apr 3, 2008 at 9:50 AM, William Temperley
[EMAIL PROTECTED] wrote:
Hi All
I hope this isn't a FAQ, but does anyone have any suggestions as to
how to make a query that selects using:
where in(comma delimited list)
secure from an sql injection point of view?
I have an idea, but I
Scott Marlowe wrote:
On Thu, Apr 3, 2008 at 10:44 AM, rihad [EMAIL PROTECTED] wrote:
Given this type query:
UPDATE bw_pool
SET user_id=?
WHERE bw_id=
(SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL)
RETURNING bw_id
The idea is to
On Apr 3, 2008, at 9:50 AM, William Temperley wrote:
Hi All
I hope this isn't a FAQ, but does anyone have any suggestions as to
how to make a query that selects using:
where in(comma delimited list)
secure from an sql injection point of view?
I have grid of tiles I'm using to reference
On Thu, Apr 3, 2008 at 11:32 AM, rihad [EMAIL PROTECTED] wrote:
Scott Marlowe wrote:
On Thu, Apr 3, 2008 at 10:44 AM, rihad [EMAIL PROTECTED] wrote:
Given this type query:
UPDATE bw_pool
SET user_id=?
WHERE bw_id=
(SELECT MIN(bw_id) FROM
rihad wrote:
Given this type query:
UPDATE bw_pool
SET user_id=?
WHERE bw_id=
(SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL)
RETURNING bw_id
Can you use a SERIALIZABLE transaction and avoid the explicit lock?
If I'm not mistaken,
On Thu, Apr 3, 2008 at 11:42 AM, Scott Marlowe [EMAIL PROTECTED] wrote:
On Thu, Apr 3, 2008 at 11:32 AM, rihad [EMAIL PROTECTED] wrote:
Scott Marlowe wrote:
On Thu, Apr 3, 2008 at 10:44 AM, rihad [EMAIL PROTECTED] wrote:
Given this type query:
UPDATE bw_pool
On Thu, Apr 03, 2008 at 09:44:55PM +0500, rihad wrote:
Given this type query:
UPDATE bw_pool
SET user_id=?
WHERE bw_id=
(SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL)
RETURNING bw_id
The idea is to single-threadedly get at the next
William Temperley wrote:
Hi All
I hope this isn't a FAQ, but does anyone have any suggestions as to
how to make a query that selects using:
where in(comma delimited list)
secure from an sql injection point of view?
I have grid of tiles I'm using to reference geographical points.
These tiles
Scott Marlowe wrote:
On Thu, Apr 3, 2008 at 11:45 AM, Craig Ringer
[EMAIL PROTECTED] wrote:
rihad wrote:
Given this type query:
UPDATE bw_pool
SET user_id=?
WHERE bw_id=
(SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL)
rihad wrote:
Hi,
I've come across a strange deadlock that I need your help with. There
are two copies of the same Perl daemon running on a 2 cpu box. The
program is pretty simple (because I wrote it :)) so I can trace its
pathway fairly well: in it, there's a single LOCK table foo occurring
rihad wrote:
Given this type query:
UPDATE bw_pool
SET user_id=?
WHERE bw_id=
(SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL)
RETURNING bw_id
Can you use a SERIALIZABLE transaction and avoid the explicit lock?
Not really. Since LOCKing
On Thu, Apr 3, 2008 at 11:45 AM, Craig Ringer
[EMAIL PROTECTED] wrote:
rihad wrote:
Given this type query:
UPDATE bw_pool
SET user_id=?
WHERE bw_id=
(SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL)
RETURNING bw_id
Can
On Thu, Apr 3, 2008 at 12:58 PM, Craig Ringer
[EMAIL PROTECTED] wrote:
Scott Marlowe wrote:
Sure, but you have to trap that all the time. The solution using a
cycling sequence keeps you from ever seeing that (unless you managed
to check out all 9,999 other values while still getting the
Scott Marlowe wrote:
Sure, but you have to trap that all the time. The solution using a
cycling sequence keeps you from ever seeing that (unless you managed
to check out all 9,999 other values while still getting the current
one. No locking needed, dozens of updaters running concurrently and
Scott Marlowe wrote:
Sure, but you have to trap that all the time. The solution using a
cycling sequence keeps you from ever seeing that (unless you managed
to check out all 9,999 other values while still getting the current
one. No locking needed, dozens of updaters running
Scott Marlowe wrote:
The serial based approach sounds a fair bit better.
Er, I meant sequence.
Add prepared select statements and you'd get get pretty fast
performance.
Yep, and if DB round trips are a problem it can always be wrapped up in
a stored procedure. I'd be tempted to do
Alvaro Herrera schrieb:
Tom Lane wrote:
Andreas writes:
make[3]: *** No rule exists for Target �utf8_and_euc_jis_2004.o�,
needed to create �libutf8_and_euc_jis_2004.so.0.0�. END.
There are two or three reports like this in the archives. It appears to
be related to using an
Hello everyone.
There were a number of people asking about ERD tools here a while ago,
so I decided to publish one that I've put together.
It's called Autograph, and you can find it on the pg foundry:
http://pgfoundry.org/projects/autograph/
Autograph is really just an XSL stylesheet, used in
Colin Fox wrote:
Hello everyone.
There were a number of people asking about ERD tools here a while ago,
so I decided to publish one that I've put together.
It's called Autograph, and you can find it on the pg foundry:
http://pgfoundry.org/projects/autograph/
Cool! Nice to have a new option
Colin Fox wrote:
There were a number of people asking about ERD tools here a while ago,
so I decided to publish one that I've put together.
It's called Autograph, and you can find it on the pg foundry:
http://pgfoundry.org/projects/autograph/
Looks handy. I'd like to give it a try.
Have a select statement with a where clause using datestamp with
timezone column compared to a date
Select * from sometable where DateStampColumn '2008-03-31'
this returns records that are equal 2008-03-31
but when the query includes casting to date
Select * from sometable where
Justin escribió:
Have a select statement with a where clause using datestamp with
timezone column compared to a date
Select * from sometable where DateStampColumn '2008-03-31'
this returns records that are equal 2008-03-31
but when the query includes casting to date
Select * from sometable
Colin Wetherbee wrote:
Colin Fox wrote:
Autograph is really just an XSL stylesheet, used in combination with
graphviz, xsltproc, convert and the downloadXml.py program from Scott
Kirkwood's xmltoddl package.
I'm familiar with graphviz and xsltproc, but what is convert? Is this
Imagemagick's
Colin Wetherbee wrote:
Colin Wetherbee wrote:
Colin Fox wrote:
Autograph is really just an XSL stylesheet, used in combination with
graphviz, xsltproc, convert and the downloadXml.py program from Scott
Kirkwood's xmltoddl package.
I'm familiar with graphviz and xsltproc, but what is
brian wrote:
Colin Fox wrote:
Hello everyone.
There were a number of people asking about ERD tools here a while ago,
so I decided to publish one that I've put together.
It's called Autograph, and you can find it on the pg foundry:
http://pgfoundry.org/projects/autograph/
Cool! Nice to
Colin Wetherbee wrote:
Colin Wetherbee wrote:
Colin Fox wrote:
Autograph is really just an XSL stylesheet, used in combination with
graphviz, xsltproc, convert and the downloadXml.py program from Scott
Kirkwood's xmltoddl package.
I'm familiar with graphviz and xsltproc, but what is
Justin [EMAIL PROTECTED] writes:
Have a select statement with a where clause using datestamp with
timezone column compared to a date
Select * from sometable where DateStampColumn '2008-03-31'
this returns records that are equal 2008-03-31
There's no such thing as timestamp compared to
Colin Fox wrote:
Colin Wetherbee wrote:
Colin Wetherbee wrote:
Colin Fox wrote:
Autograph is really just an XSL stylesheet, used in combination with
graphviz, xsltproc, convert and the downloadXml.py program from Scott
Kirkwood's xmltoddl package.
I'm familiar with graphviz and xsltproc, but
Leif B. Kristensen wrote:
On Wednesday 2. April 2008, Tomasz Ostrowski wrote:
Go for it. Even 64 (I like round numbers) would not be too much.
Geek test: Do you find the above statement odd?
Yes: 0, No: +10.
(Sorry for being massively off-topic :-))
I had the same thought. ;)
Colin
--
I'm transferring a database from 7.4.7 to 7.4.13 using 8.1.11
utilities and pg_dump leaves out the without oids clause in the
create table commands. I've confirmed the original tables are created
without oids by using pgadmin3 v1.8.2.
How do I get the table created without oids?
--
Chris
--
Chris Velevitch [EMAIL PROTECTED] writes:
I'm transferring a database from 7.4.7 to 7.4.13 using 8.1.11
utilities
Why in the world would you try that, rather than using 7.4's pg_dump?
At no time has pg_dump version N claimed to produce output that
was loadable into server versions N.
and
Colin Fox wrote:
brian wrote:
From the example page:
I also wanted to make sure that there were no dead crows -- having
the crows-feet right side up makes the diagram a lot more readable in
my humble opinion.
Um ... those arrows look backwards to me :-(
Actually, crows feet are supposed to
mytable.id and foo.id,
instead of a link between mytable.foo_id and foo.id.
I don't think I'll use the detailed view very often, though; I just
wanted to see what it did. :)
Here's the sample detailed output from one of my databases:
http://colinwetherbee.com/data/js-20080403.png
Thanks again
Chris Velevitch wrote:
I'm transferring a database from 7.4.7 to 7.4.13 using 8.1.11
utilities and pg_dump leaves out the without oids clause in the
create table commands. I've confirmed the original tables are created
without oids by using pgadmin3 v1.8.2.
How do I get the table created
On Fri, Apr 4, 2008 at 1:45 PM, Colin Wetherbee [EMAIL PROTECTED] wrote:
If you're moving from 7.4.7 to 7.4.13, why don't you use the 7.4 utilities?
The 8.1 utilities assume 8.1 defaults, which can be quite different from 7.4
defaults and, IIRC, are responsible for your OID issue.
I've been
On Fri, Apr 4, 2008 at 2:50 PM, Chris Velevitch
[EMAIL PROTECTED] wrote:
In the mean time, which download has only the 7.4 utils and can that
be installed without removing my 8.1 utils.
I'm using Centos 5.
Chris
--
Chris Velevitch
Manager - Sydney Flash Platform Developers Group
m: 0415
Hi,
I have a SELECT statement that is a bit involved in terms of
calculations, so I wanted to set up some column aliases and refer to
them further down the select list:
--cut here---start-
SELECT table1.col1 - table2.col1 AS diff1,
table1.col2 +
On Thu, Apr 3, 2008 at 10:39 PM, Tom Lane [EMAIL PROTECTED] wrote:
Pavan Deolasee [EMAIL PROTECTED] writes:
Thanks. I had another concern about VACUUM not reporting DEAD line
pointers (please see up thread). Any comments on that ?
If you want to work on that, go ahead
Ok. I would do
Seb wrote:
--cut here---start-
SELECT table1.col1 - table2.col1 AS diff1,
table1.col2 + table2.col2 AS sum1,
sum1 - diff1
FROM table1 INNER JOIN table2 ON (table1.id = table2.id)
--cut here---end---
On Fri, 04 Apr 2008 00:35:01 -0400,
Colin Wetherbee [EMAIL PROTECTED] wrote:
[...]
SELECT foo - bar AS baz FROM ( SELECT a.a + b.a AS foo, a.b + b.b AS
bar FROM a JOIN b ON a.id = b.id ) AS subtable;
Although, I'm not really sure that's The Right Way to do it.
Thanks Colin, yes, I thought
On Wed, 2 Apr 2008, mark wrote:
with no clients connected to the database when I try to shutdown the
database [to apply new settings], it says database cant be shutdown..
for a long time both in smart and normal mode... then i had to go to
immediate mode to shut down.. but then when i start
Chris Velevitch [EMAIL PROTECTED] writes:
On Fri, Apr 4, 2008 at 2:50 PM, Chris Velevitch
[EMAIL PROTECTED] wrote:
In the mean time, which download has only the 7.4 utils and can that
be installed without removing my 8.1 utils.
I'm using Centos 5.
Um ... Red Hat, who are about as
Pavan Deolasee [EMAIL PROTECTED] writes:
On Thu, Apr 3, 2008 at 10:39 PM, Tom Lane [EMAIL PROTECTED] wrote:
I didn't think it merited back-patching. It's strictly cosmetic in
terms of being about what VACUUM VERBOSE prints, no?
Umm.. Whatever we decide on the fix, I think we should backpatch
On Fri, Apr 4, 2008 at 4:33 PM, Tom Lane [EMAIL PROTECTED] wrote:
Chris Velevitch [EMAIL PROTECTED] writes:
On Fri, Apr 4, 2008 at 2:50 PM, Chris Velevitch
[EMAIL PROTECTED] wrote:
In the mean time, which download has only the 7.4 utils and can that
be installed without removing my
On Fri, Apr 4, 2008 at 11:10 AM, Tom Lane [EMAIL PROTECTED] wrote:
The
policy of this project is that we only put nontrivial bug fixes into
back branches, and I don't think this item qualifies ...
Got it. I will submit a patch for HEAD.
Thanks,
Pavan
--
Pavan Deolasee
EnterpriseDB
69 matches
Mail list logo
