Radoslaw Zielinski wrote:
> radek=# \d kandydaci
> Table "public.kandydaci"
> Column | Type | Modifiers
> ---+--+---
>id_rekordu| bigint | not null
>id_osoby | integer | n
Greetings,
The video of the August 11, 2009 SFPUG talk, featuring David Fetter's
presentation on windowing and common table expressions, is now up:
http://thebuild.com/blog/2009/08/13/sfpug-windowing-and-common-table-expressions/
--
-- Christophe Pettus
x...@thebuild.com
--
Sent via p
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Christophe Pettus
>On Aug 11, 2009, at 12:19 AM, Jan Verheyden wrote:
>> I was looking in what way it's possible to alert via mail when some
>> conditions are true in a d
On Fri, Aug 14, 2009 at 12:33 AM, Sam Mason wrote:
> On Fri, Aug 14, 2009 at 12:03:37AM +0100, Greg Stark wrote:
>> On Thu, Aug 13, 2009 at 11:20 PM, Sam Mason wrote:
>> > There would be no way of creating a row 1.6TB in size in one go
>
> I was thinking of a single update per column.
Oh, my
On Thu, Aug 13, 2009 at 11:53:49PM +0100, Greg Stark wrote:
> On Thu, Aug 13, 2009 at 11:44 PM, Daniel Verite
> wrote:
> >> In other discussions about similar issues I've said that the expression:
> >>
> >>ROW(NULL,NULL) IS DISTINCT FROM NULL
> >>
> >> should evaluate to FALSE. I still think
On Fri, Aug 14, 2009 at 12:03:37AM +0100, Greg Stark wrote:
> On Thu, Aug 13, 2009 at 11:20 PM, Sam Mason wrote:
> > Is it worth having a note about having enough memory floating around
> > for those limits to actually be hit in practice? There would be no
> > way of creating a row 1.6TB in size i
On Thu, Aug 13, 2009 at 11:20 PM, Sam Mason wrote:
> On Thu, Aug 13, 2009 at 03:31:39PM -0400, Greg Smith wrote:
>> If your client app is coded correctly to handle large packets of data, it
>> should work up to the size limits documented at
>> http://www.postgresql.org/about/ , so you probably havi
>
> ERROR: operator does not exist: integer = integer[]
> HINT: No operator matches the given name and argument type(s).
> You might need to add explicit type casts.
>
Sounds like you are trying to return directly the query.
You must do a loop with that query inside (cursor) and
use ne
On Thu, Aug 13, 2009 at 11:44 PM, Daniel Verite wrote:
>> In other discussions about similar issues I've said that the expression:
>>
>> ROW(NULL,NULL) IS DISTINCT FROM NULL
>>
>> should evaluate to FALSE. I still think this is correct and generally
>> useful behavior.
>
> I see no reason to dis
Sam Mason wrote:
> Hum, there seem to be lots of different things happening here--lets try
> and untangle them a bit. I would say that the following returns a null
> value of type row (actually a pair of integers):
>
> SELECT b
> FROM (SELECT 1) a
> LEFT JOIN (SELECT 1,2) b(b1,b2
Hi. I'm trying to write a plperl function that returns a list of ids
that I want to use in a subquery.
The function call would look like:
select * from mlist( 168.4, 55.2, 0.1);
and would return a list of integers. I've written this function,
and it returns the right list of integers, but
On Thu, Aug 13, 2009 at 03:31:39PM -0400, Greg Smith wrote:
> If your client app is coded correctly to handle large packets of data, it
> should work up to the size limits documented at
> http://www.postgresql.org/about/ , so you probably having nothing to worry
> about here.
Is it worth having
On Thu, 13 Aug 2009, Alan McKay wrote:
Make sure your database accepts packages over 5 MB in size. A MySQL
database for example accepts packages up to 1 MB by default. In this
case, the value for max_allowed_packet must be increased.
packages->packet for this to make sense; basically they're s
Alan McKay wrote:
> Hey folks,
>
> I'm installing OTRS/ITSM (and yes, sending the same question to their
> list) and it gives me this warning. I cannot find an equivalent
> config parameter in Postgres.
>
> Make sure your database accepts packages over 5 MB in size. A MySQL
> database for exampl
Hey folks,
I'm installing OTRS/ITSM (and yes, sending the same question to their
list) and it gives me this warning. I cannot find an equivalent
config parameter in Postgres.
Make sure your database accepts packages over 5 MB in size. A MySQL
database for example accepts packages up to 1 MB by d
Philip Rhoades wrote:
People,
It would be nice to be able to use PostgreSQL as the storage for Firefox
Bookmarks - anyone know if this would be possible? how it could be done?
Most likely it could be done, if you wanted to.
You will need to know how to write a firefox plugin, and how to
pr
>
> Why don't you make it simple and just use row_number() from 8.4... It can be
> simplified as:
>
> select row_number() over(), i, p from prueba limit 5;
>
I know the use of WF. What surprised me, is the difference between
both versions in the same query. I'm trying to understand why happens
and
a...@archie.netg.se writes:
> I am sitting on version 7.4.x and am going to upgrade to version 8.3.x.
>> From all I can read I should have no problem with actual format of the
> pgdump file (for actual dumping and restoring purposes) but I am having
> problems with encoding (which I was fairly s
On Thu, Aug 13, 2009 at 04:02:14PM +0300, mito wrote:
> I have table like this:
> CREATE TABLE messages {
> recepients varchar[],
> };
>
> Want to select by content co array type field recepients:
> SELECT * FROM messages where 'john' ANY (recepients);
not sure if it went missing in the ema
Hello,
I am sitting on version 7.4.x and am going to upgrade to version 8.3.x.
From all I can read I should have no problem with actual format of the
pgdump file (for actual dumping and restoring purposes) but I am having
problems with encoding (which I was fairly sure I would). I have search
I have table like this:
CREATE TABLE messages {
recepients varchar[],
};
Want to select by content co array type field recepients:
SELECT * FROM messages where 'john' ANY (recepients);
If i want to create index on recepients field, is it enough to
CREATE INDEX messages_recepients_index
I am working on cleaning up a bloated database. I have been
reindexing etc. There appear to be a good number of never to almost
never used indexes. I am looking in pg_stat_user_indexes which yields
some questions.
Assuming that the reset stats on server is not turned on how old are
sta
Grzegorz Jaśkiewicz [2009-08-13 14:23]:
> On Thu, Aug 13, 2009 at 12:36 PM, Radoslaw Zielinski
> wrote:
[...]
>> "kandydaci_fk_id_rekordu" FOREIGN KEY (id_rekordu) REFERENCES
>> rekordy(id) ON DELETE CASCADE
[...]
> since you do LEFT JOIN, indeed you can get r.id to be null.
There is a for
On Thu, Aug 13, 2009 at 05:20:22PM +0200, Durumdara wrote:
> I must get informations about the tables.
> For example:
> Tnnn:
> 1. [Field Name, Type, Size, NotNull]
> 2. [Field Name, Type, Size, NotNull]
I'd recommend either using the standard defined information_schema[1] or
playing around with r
On Fri, 14 Aug 2009, Philip Rhoades wrote:
It would be nice to be able to use PostgreSQL as the storage for Firefox
Bookmarks - anyone know if this would be possible? how it could be done?
Firefox uses SQLite to hold its bookmarks. It's certainly possible to
hack the code to use an alternate
On Fri, 2009-08-14 at 02:36 +1000, Philip Rhoades wrote:
> Devrim,
>
>
> On 2009-08-14 00:55, Devrim GÜNDÜZ wrote:
> > On Fri, 2009-08-14 at 00:20 +1000, Philip Rhoades wrote:
> >>
> >> It would be nice to be able to use PostgreSQL as the storage for
> >> Firefox Bookmarks - anyone know if this
On Thu, Aug 13, 2009 at 12:31:29PM -0400, Dan Halbert wrote:
> Perhaps I should have mentioned that initially.
In retrospect everything is easy!
> SELECT ARRAY[1,2,1+2]::INT[];
> works fine.
I'd not put a cast into that one. I can't see any performance reason
why it's bad, I think it's mainly
Richard Huxton writes:
> Scara Maccai wrote:
>> http://explain-analyze.info/query_plans/3817-query-plan-2525
> Ah, good - that's useful.
Hmmm ... why is the inner Nested Loop estimated to produce 30120 rows,
when the cartesian product of its inputs would only be 285 * 14 = 3990
rows? What PG ve
Devrim,
On 2009-08-14 00:55, Devrim GÜNDÜZ wrote:
On Fri, 2009-08-14 at 00:20 +1000, Philip Rhoades wrote:
It would be nice to be able to use PostgreSQL as the storage for
Firefox Bookmarks - anyone know if this would be possible? how it
could be done?
Uh, I think it would consume more sys
From "Sam Mason" :
>The nicer syntax to distinguish things is to use:
>
> TYPENAME 'literal'
Thanks! That is very helpful. I saw that syntax in one example I found on the
web, and incorrectly thought it was an alternate way of writing the function
call.
The point of all this was to figure out
>
> This just looks like PG missing a feature. plpgsql has much less user
> and developer time spent on it, so I'd expect to find more strangeness
> in darker corners like this.
>
this rule should be simply removed. It's not problem. The people long
time believe so row cannot be null ever. I don't
Scara Maccai wrote:
set enable_mergejoin=off;
set enable_hashjoin=off
http://explain-analyze.info/query_plans/3817-query-plan-2525
Ah, good - that's useful.
As you can see, the 2 root partition roots (teststscell73 and teststscell13)
take
teststscell73: 3.90 * 30120 loops = 117468 cost
te
On Thu, Aug 13, 2009 at 08:30:07AM -0700, Scott Bailey wrote:
> >On Wed, Aug 12, 2009 at 08:45:58PM -0700, Scott Bailey wrote:
> >>CREATE OR REPLACE FUNCTION unnest(anyarray)
> >> RETURNS SETOF anyelement AS
> >>$BODY$
> >>SELECT $1[i] FROM
> >>generate_series(array_lower($1,1),
> >>
I am trying to make sense of geometric literal syntax in and out of array
syntax. I cannot figure out a general rule: sometimes single quotes work,
sometimes double quotes work, and inside and outside of array literals the
rules are different an seemingly inconsistent.
Examples of all the we
On Thu, Aug 13, 2009 at 11:02:37AM -0400, Dan Halbert wrote:
> I am trying to make sense of geometric literal syntax in and out of
> array syntax. I cannot figure out a general rule: sometimes single
> quotes work, sometimes double quotes work, and inside and outside of
> array literals the rules a
On Wed, Aug 12, 2009 at 08:45:58PM -0700, Scott Bailey wrote:
CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement AS
$BODY$
SELECT $1[i] FROM
generate_series(array_lower($1,1),
array_upper($1,1)) i;
$BODY$
LANGUAGE 'sql' IMMUTABLE STRICT
I'd recommen
> What version are you using? Also,
> please post the table
> definitions (preferably in pg_dump -s format)
Table definition at the end of the msg.
Postgresql 8.4beta1
> I'm not sure I agree with your assessment of the problem.
This is why I think that's the problem:
This is an explain of the q
On Wed, Aug 12, 2009 at 10:57:54PM +0200, Daniel Verite wrote:
> It seems to me that there is something special with rows: in tables, the
> values of columns may be null or not, but at the level of the row, there is
> no information that would say: this row itself as an object is null.
Hum, there
Hi!
I must get informations about the tables.
For example:
Tnnn:
1. [Field Name, Type, Size, NotNull]
2. [Field Name, Type, Size, NotNull]
...
The test table is this:
CREATE TABLE testfields
(
fbigint bigint NOT NULL,
fbool boolean,
fchar character(100),
fcharv character varying(100),
I am trying to make sense of geometric literal syntax in and out of array
syntax. I cannot figure out a general rule: sometimes single quotes work,
sometimes double quotes work, and inside and outside of array literals the
rules are different an seemingly inconsistent.
Examples of all the weird
Scara Maccai wrote:
> > Huh, clearly not the same query (you're using the partition directly
> > in the first query) ... Doing two changes at once is not helping
> > your case.
>
> Sorry, I don't understand... of course I used the partition directly
> in the first query... it's the difference bet
On Fri, 2009-08-14 at 00:20 +1000, Philip Rhoades wrote:
>
>
> It would be nice to be able to use PostgreSQL as the storage for
> Firefox Bookmarks - anyone know if this would be possible? how it
> could be done?
Uh, I think it would consume more system resources.
--
Devrim GÜNDÜZ, RHCE
Comma
Philip Rhoades wrote:
People,
It would be nice to be able to use PostgreSQL as the storage for Firefox
Bookmarks - anyone know if this would be possible? how it could be done?
Not sure about doing it with PostgreSQL but there is the Firefox Weave
Service which might do what I think you're af
> > -> Index Scan using
> teststscell13_pkey on teststscell13 data1 (cost=0.0..3.9
> rows=1 width=16) (actual time=0.006..0.006 rows=0
> loops=285)
> >
> > doesn't make any sense: that table will never have any
> data.
> > I'd like to have a way to tell that to Postgresql...
>
> It's one inde
Scara Maccai wrote:
I'm still looking into it, but it seems the difference in the 2 plans is due to the fact
that when using partitions, the planner adds the time it would take to index-scan the
empty "root" table.
But that table will never contain any data...
Is there any chance to have the p
People,
It would be nice to be able to use PostgreSQL as the storage for Firefox
Bookmarks - anyone know if this would be possible? how it could be done?
Thanks,
Phil.
--
Philip Rhoades
GPO Box 3411
Sydney NSW 2001
Australia
E-mail: p...@pricom.com.au
--
Sent via pgsql-general mailing
> Huh, clearly not the same query (you're using the partition
> directly in
> the first query) ... Doing two changes at once is not
> helping your
> case.
Sorry, I don't understand... of course I used the partition directly in the
first query... it's the difference between the two... what I don'
Sim Zacks wrote:
> According to the documentation, you can pass multiple parameters into an
> aggregate function, but it only stores one value.
>
>
> What I am trying to do is sum a quantity field, but it also has units
> that need to be converted.
Have you seen Martijn van Oosterhout's tagged t
Scara Maccai wrote:
> explain analyze
> select nome1,
> thv3tralacc,
> dltbfpgpdch
> FROM cell_bsc_60_0610 as cell_bsc
> left outer join teststscell73_0610_1 as data on
> data.ne_id=cell_bsc.nome1
> left outer join teststscell13_0610_1 as data
> That could work in some cases, however in our case it would
> not produce
> desirable results.
Well I don't think you got Alban's suggestion right...
What he was trying to say was:
- use a regular (not aggregated) function to convert all measures to mm
- use the normal SUM() to sum those value
> It's probably easiest to decide on an internal unit to use in your
> aggregate and only convert it to the desired unit once you're done
> summing them. I'd probably convert all measurements to mm in the
> function and summarise those.
That could work in some cases, however in our case it would no
On Thu, Aug 13, 2009 at 12:36 PM, Radoslaw Zielinski wrote:
> Hello,
>
> I have reported this yesterday via WWW as bug 4979, but I can't see it
> in the -bugs archive. Has it been lost or are the bug reports being
> moderated...?
>
> Anyway. Pg 8.4.0 from yum.postgresql.org running on 64bit RHEL
On Thu, Aug 13, 2009 at 13:21, PG Subscriber wrote:
> I'm on Windows XP SP 2, trying to run PostgreSQL 8.3.4.
>
> Running postgres.exe gives the error:
>
> "Execution of PostgreSQL by a user with administrative permissions is
> not permitted.
> The server must be started under an unprivileged user
On 13 Aug 2009, at 12:51, Sim Zacks wrote:
What I am trying to do is sum a quantity field, but it also has units
that need to be converted.
4 meter
400 mm
100 cm
I want to sum it all, my function decides to use meter (based on the
requirements) and should return 4.00104 (or something like
PG Subscriber wrote:
I'm on Windows XP SP 2, trying to run PostgreSQL 8.3.4.
Running postgres.exe gives the error:
"Execution of PostgreSQL by a user with administrative permissions is
not permitted.
The server must be started under an unprivileged user ID to prevent
possible system security co
Hello,
I have reported this yesterday via WWW as bug 4979, but I can't see it
in the -bugs archive. Has it been lost or are the bug reports being
moderated...?
Anyway. Pg 8.4.0 from yum.postgresql.org running on 64bit RHEL 5.3.
radek=# \d kandydaci
Table "public.kandydaci"
I'm on Windows XP SP 2, trying to run PostgreSQL 8.3.4.
Running postgres.exe gives the error:
"Execution of PostgreSQL by a user with administrative permissions is
not permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromises. See the docu
> Is there a better way?
I think you could use a User Data Type.
Then pass that as parameter to your aggregate function.
That is: you would pass
(4, 'meter')
(400, 'mm')
(100, 'cm')
to your aggregate function.
Each one is a user datatype:
CREATE TYPE mytype AS (
v double precisio
I'm still looking into it, but it seems the difference in the 2 plans is due to
the fact that when using partitions, the planner adds the time it would take to
index-scan the empty "root" table.
But that table will never contain any data...
Is there any chance to have the partitioning mechanism
According to the documentation, you can pass multiple parameters into an
aggregate function, but it only stores one value.
What I am trying to do is sum a quantity field, but it also has units
that need to be converted.
My function should take 2 values, the quantity and the unit, determine
which
On Wed, Aug 12, 2009 at 08:45:58PM -0700, Scott Bailey wrote:
> CREATE OR REPLACE FUNCTION unnest(anyarray)
> RETURNS SETOF anyelement AS
> $BODY$
> SELECT $1[i] FROM
> generate_series(array_lower($1,1),
> array_upper($1,1)) i;
> $BODY$
> LANGUAGE 'sql' IMMUTABLE STRICT
Scara Maccai wrote:
Thank you for your reply. This makes partitions unusable for me...
hope someone explains why this happens... this still looks like a bug
to me... BTW the problem arises when adding the second "left outer
join": when using only 1 partitioned table (that is, only 1 "left
outer j
Thank you for your reply.
This makes partitions unusable for me... hope someone explains why this
happens... this still looks like a bug to me...
BTW the problem arises when adding the second "left outer join": when using
only 1 partitioned table (that is, only 1 "left outer join") the 2 plans ar
On Wed, 12 Aug 2009, sam mulube wrote:
is my interpreting of buffers_clean = 0 correct?
Yes.
If so, why would the bgwriter not be writing out any buffers?
The purpose of the cleaner is to prepare buffers that we expect will be
needed for allocations in the near future. Let's do a littl
Scara Maccai wrote:
same query, but using postgresql's partition pruning ("2"):
explain analyze
select nome1,
thv3tralacc,
dltbfpgpdch
FROM cell_bsc_60_0610 as cell_bsc
left outer join teststscell73 as data on
data.ne_id=cell_bsc.nome1
left outer join teststscel
Anyone??? This looks like a bug to me... or is there an explanation?
--- Mer 12/8/09, Scara Maccai ha scritto:
> Da: Scara Maccai
> Oggetto: [GENERAL] totally different plan when using partitions
> A: "pgsql-general"
> Data: Mercoledì 12 agosto 2009, 13:05
>
> query using partitions explicitl
66 matches
Mail list logo
