Tom Lane wrote:
Nis Jorgensen [EMAIL PROTECTED] writes:
Try removing the DISTINCT ON from your view - that should make things
clearer to you. When t.approved is true, the row is joined to all rows
of the datum table satisfying the criteria. The sort order you specify
does not guarantee a
Hello all,
I think I found a little but annoying bug in views when ordering is
involved. First, my version of Postgres:
PostgreSQL 8.1.3 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC)
3.4.4 [FreeBSD] 20050518
Please try the following:
CREATE TABLE datum (
projekt_id INTEGER NOT
Tom Lane wrote:
=?ISO-8859-1?Q?Sebastian_B=F6ck?= [EMAIL PROTECTED] writes:
I get unpredictibale results selecting from a view depending on
index-usage.
It's not actually *using* the indexes, although presence of the indexes
does seem to be needed to trigger the bug:
regression=# explain
Tom Lane wrote:
=?ISO-8859-1?Q?Sebastian_B=F6ck?= [EMAIL PROTECTED] writes:
I patched my 8.1 installation with the following changes:
http://www-new.commandprompt.com/projects/public/pgsql/changeset/23461
The error described above doesn't exist any more, but it's still
buggy.
Yup,
Hello,
I get unpredictibale results selecting from a view depending on
index-usage.
Please see the attached script for details.
Is it a bug or some weird feature?
Any help appreciated to get predictibale results
Sebastian
CREATE TABLE test1 (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
Scott Marlowe wrote:
On Mon, 2005-11-14 at 11:07, Sebastian Böck wrote:
Hello,
I get unpredictibale results selecting from a view depending on
index-usage.
PostgreSQL uses a cost based planner. So, it tends to not use the plan
you might expect, especially in toy test cases with small
Jaime Casanova wrote:
On 11/14/05, Sebastian Böck [EMAIL PROTECTED] wrote:
Hello,
I get unpredictibale results selecting from a view depending on
index-usage.
Please see the attached script for details.
Is it a bug or some weird feature?
Any help appreciated to get predictibale results
Oleg wrote:
Dear All,
is it possible to temporary deactivate a constraint in PostgreSQL?
There is a constraint that does not allow me to write some data (see
e-mail below). But after all datasets are written the constraint is
valid. So I was wondering wether it is possible to deactivate a
Hello all,
why is the last definition of a view not working, although the
documentation says all three are equal?
Testcase:
CREATE SCHEMA one;
CREATE SCHEMA two;
CREATE TABLE one.one (
id SERIAL PRIMARY KEY
);
CREATE TABLE two.two (
id SERIAL PRIMARY KEY
);
CREATE TABLE
Richard Huxton wrote:
Sebastian Böck wrote:
Hello all,
why is the last definition of a view not working, although the
documentation says all three are equal?
CREATE OR REPLACE VIEW not_working AS
SELECT one.*
FROM one.one, two.two
JOIN join1 ON join1.id = one.id;
I think
Tom Lane wrote:
=?ISO-8859-1?Q?Sebastian_B=F6ck?= [EMAIL PROTECTED] writes:
why is the last definition of a view not working, although the
documentation says all three are equal?
The documentation says no such thing...
So I misinterpreted the following:
Dawid Kuroczko wrote:
On 5/24/05, Sebastian Böck [EMAIL PROTECTED] wrote:
/* 3rd way of separating updates
con: unnecessary updates on tables
pro: view gets evaluated only 1 time
Not adressing the problem of unnecessary updates, but the view
gets only evaluated one time.
*/
CREATE
itself now, only want to get rid of all the unnecessary evaluations of
the view.
So I tried to collapse the rules into on rule as shown in the example
below:
On Wed, May 25, 2005 at 10:40:38AM +0200, Sebastian Böck wrote:
CREATE OR REPLACE RULE upd AS ON UPDATE TO view_test
DO INSTEAD
Dawid Kuroczko wrote:
Control question, I didn't check it, but would it be enough to change from:
UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
to:
UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id AND text1 NEW.text1?
... I may be wrong. :)
Yes, thats more elegant then my
Ragnar Hafstað wrote:
On Wed, 2005-05-25 at 13:09 +0200, Sebastian Böck wrote:
Dawid Kuroczko wrote:
Be wary of the NULL values though. :) Either don't use them, add
something like 'AND (text1 NEW.text1 OR text1 IS NULL OR NEW.text1
IS NULL)' or something more complicated. :)
Thanks
Hi all,
maybe it's a very silly question, but why does Postgres perform an
update on the table even if no data changes?
I recognized this recently doing a rewrite of my rules because they
took to long. I had many conditional rules I collapsed to one
unconditional rule, so that the views get
Martijn van Oosterhout wrote:
I'm sure I'm not the only one, but, what are you talking about? RULEs
are not really obvious so it would help if you could post an example of
what you mean...
Have a nice day,
Hi, I'm not really talking about rules.
I'm talking about updates on *real* tables,
Jaime Casanova wrote:
On 5/24/05, Sebastian Böck [EMAIL PROTECTED] wrote:
Martijn van Oosterhout wrote:
I'm sure I'm not the only one, but, what are you talking about? RULEs
are not really obvious so it would help if you could post an example of
what you mean...
I attach some sample SQL
Sorry, missed the SQL to test.
Sebastian
/* tables */
CREATE TABLE test (
id INTEGER PRIMARY KEY,
test TEXT NOT NULL
);
CREATE TABLE join1 (
id INTEGER PRIMARY KEY,
text1 TEXT NOT NULL
);
CREATE TABLE join2 (
id INTEGER PRIMARY KEY,
text2 TEXT
Oleg Bartunov wrote:
On Thu, 28 Apr 2005, [ISO-8859-1] Sebastian B?ck wrote:
Hello,
i wanted to define and GIST-index on a table with a timestamp-column
containing 'infinity' values, and it throws the following error:
ERROR: cannot subtract infinite timestamps
Is this a known limitation?
I don't
Hello,
i wanted to define and GIST-index on a table with a timestamp-column
containing 'infinity' values, and it throws the following error:
ERROR: cannot subtract infinite timestamps
Is this a known limitation?
How can i avoid or circumvent this?
Thanks in advance
Sebastian
Stephane Bortzmeyer wrote:
On Wed, Apr 27, 2005 at 04:50:23PM +0200,
Sebastian Böck [EMAIL PROTECTED] wrote
a message of 48 lines which said:
CREATE UNIQUE INDEX na ON x (name, address) WHERE address IS NULL;
No, because it prevents two tuples with the same value of name.
Ahh, sorry! Ment
Ron Mayer wrote:
Did anyone get multi-column GIST indexes working using both
the gist_btree and postgis modules?
Not quite stable yet.
It seems to work fine for me on small test cases (shown
at the bottom), but seems to crash my database for large
ones. Any advice is welcome - including pointers
Hello all,
i have a problem with index usage and joins.
Attached is some SQL demonstrating my problem;
Why is the index only used in the 2nd query?
Can anybody explain me how to avoid/fix this.
Thanks in advance
Sebastian
CREATE TABLE users (
login NAME NOT NULL PRIMARY KEY,
datum
Richard Huxton wrote:
Sebastian Böck wrote:
Richard Huxton wrote:
Can you post the output from your explain analyse calls too? The
statistics aren't going to be the same on different machines.
Sure, here it is.
Thanks. (PS - remember to cc the list too).
[output of EXPLAIN ANALYZE]
OK - so what
Richard Huxton wrote:
Sebastian Böck wrote:
Richard Huxton wrote:
Sebastian Böck wrote:
Richard Huxton wrote:
Can you post the output from your explain analyse calls too? The
statistics aren't going to be the same on different machines.
Sure, here it is.
Thanks. (PS - remember to cc the list
Tom Lane wrote:
=?ISO-8859-1?Q?Sebastian_B=F6ck?= [EMAIL PROTECTED] writes:
infinite recursion detected in rules for relation ...
If you need a patch immediately, here it is.
*** src/backend/rewrite/rewriteHandler.c.orig Sat Nov 6 12:46:35 2004
--- src/backend/rewrite/rewriteHandler.c Sat Nov 20
Hello all,
i have a problem migrating my application from version
7.4 to 8.0. Everything is fine in 7.4 but with 8.0 i get
the following error:
infinite recursion detected in rules for relation ...
I've been able to narrow the problem down to the
attached SQL.
I don't know wether its a bug or a
Tom Lane wrote:
=?ISO-8859-1?Q?Sebastian_B=F6ck?= [EMAIL PROTECTED] writes:
I investigated a little bit further and can be more precisely
about the whole thing. This (wrong) behaviour only occurs, if
the view has an order by clause.
The bug is triggered by the combination of an inherited UPDATE
Tom Lane wrote:
=?ISO-8859-1?Q?Sebastian_B=F6ck?= [EMAIL PROTECTED] writes:
Is this a known limitation with views, rules and
inherited tables i haven't heard of? Or is it a bug?
When you haven't shown us any details, it's impossible to tell.
Let's see the actual table, view, and rule definitions.
Hello all,
i have a view defined as a simple select of a table.
This table is inherited by a couple of others.
All entries belong to the child-tables.
I also have an unconditional update rule on the view.
If i do an update to the view, the update is rewritten
to update the father-table.
And now a
Andreas wrote:
Seastian Böck wrote:
for primary keys there is a simple (and at least working for me)
solution as long as you can use the SERIAL type for your primary
key.
[...]
Now the id column gets merged and you should have the desired
behaviour.
If you want objects.id to get referenced by
32 matches
Mail list logo