[BUGS] Bug in optimizer

[Timo Nentwig]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi!
Your bug report form on the web doesn't work.
This is very slow:
SELECT urls.id FROM urls WHERE
(
urls.id <> ALL (SELECT html.urlid FROM html)
);
...while this is quite fast:
SELECT urls.id FROM urls WHERE
(
NOT (EXISTS (SELECT html.urlid FROM tml WHERE
(
html.urlid = urls.id
)))
);
Regards
Timo
- --
http://nentwig.biz/ (J2EE)
http://nitwit.de/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.2 (GNU/Linux)
iD8DBQFAlm53cmRm71Um+e0RAkJuAKChd+6zoFesZfBY/cGRsSVagnJeswCeMD5s
++Es8hVsFlUpkIIsRfrBp4Y=
=STbS
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[BUGS] Killing the backends

[Helvio Hild]

SELECT int8in(8);
 OVER psql do:
llamados=> SELECT int8in(8);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset:
Failed.
!>



In  psql superuser an other terminal:
NOTICE:  Message from PostgreSQL backend:
The Postmaster has informed me that some other backend
died abnormally and possibly corrupted shared
memory.
I have rolled back the current
transaction and am
going to terminate your database
system connection and exit.
Please reconnect to the
database system and repeat your query.


In other Terminal
BEFORE
:~$ pidof postmaster
15489 15488 15474 338 337 335
AFTER
 :~$ pidof postmaster
 338 337 335



PC:
i386->Celeron300
Linux->Debian->Kernel 2.2.18
Postgresql->7.2.1-2woody2 -> PostgreSQL 7.2.1 on i686-pc-linux-gnu,
compiled by GCC 2.95.4
BYE

---
[This E-mail scanned for viruses by Declude Virus]


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [BUGS] [GENERAL] cache lookup of relation 165058647 failed

[Sean Chittenden]

[Renames thread from "The Pepsi Challenge" to "The PostgreSQL 
Challenge"]

But, I am suspecting that it's a race condition with the new 
background
writer code.
Why?  Have you demonstrated that the failure does not occur in 7.4?
What other operations have been added to HEAD that would allow for 
successful operation of sequential use or testing of temp tables?

More importantly, yes, I can confirm that this behavior doesn't exist 
in REL7_4 as of 40min ago.  I've been running the exact same tests 
repeatedly with nearly identical configs (as close as they can be given 
the config changes) on REL7_4 as I have HEAD and only HEAD is giving me 
problems.

psql:test-end2.sql:3: ERROR:  cache lookup failed for relation 398033
CONTEXT:  SQL query "SELECT  TRUE FROM pg_catalog.pg_class c LEFT JOIN
pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname =
'tmptbl'::TEXT AND c.relkind = 'r'::TEXT AND
pg_catalog.pg_table_is_visible(c.oid)"
I think that pg_table_is_visible() will inspect the catalogs using
SnapshotNow semantics, while the above query will feed it with OIDs 
that
were valid under a start-of-query snapshot.  So I'd expect failures in
any recent PG version, if tables are being dynamically created/deleted
by concurrent transactions.
There is no concurrency in the test I gave: it's all sequential.
0 | -- connect
1 | BEGIN;
2 | SELECT setuid_wrapper();
3 | -- Do other things;
4 | COMMIT;
5 | -- disconnect
6 | -- connect again
7 | BEGIN;
8 | SELECT setuid_wrapper();  -- This fails some % of the time
If I add step 5.5 that is a sleep, step 8 will work, without fail.  In 
7.4, I have no problems, however.  That said, the bgwriter code is the 
only think that I can think of that would muck with caching.  -sc

PS  Other comments temp schema permission patch?
--
Sean Chittenden
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster