[GENERAL] Optimising SELECT on a table with one million rows

Hi, I'm fairly new with Postgresql, so I am not sure if the performance problems I'm having are due to poorly constructed queries/indices, or if I bumped into more fundamental problems requiring a design of my database structure. That's why I'm requesting your help. Here's the situation: I have

Re: [GENERAL] Optimising SELECT on a table with one million rows

Hi, > If you have no index on comments.comment_author, then a seqscan will be > required for your join between comments and users. Similarly, if you > have no index on comments.comment_story, then any query against comments > that uses that column as part of a predicate will require a seqscan o

[GENERAL] Cannot declare record members NOT NULL

Hi, I am not sure if this qualifies as a bug report or a feature request, but I don't see any way to tell Postgresql that the members of a record cannot be NULL. This causes all kinds of problems when this record is used to declare the return type of a function. Suppose I had the following table

Re: [GENERAL] Cannot declare record members NOT NULL

> Why do you create an extra type for that? > Just have your method return "movies" Hi, Thanks for the answer. The simple example obfuscates the fact that in reality the table has a few extra columns that are omitted from get_movies_t. Therefore, I cannot return "movies". However, your answer

Re: [GENERAL] Cannot declare record members NOT NULL

Hi again, > However, your answer did give me an idea: instead of declaring > "get_movies_t" as a record, I declare it as dummy table, and return > that (see code at the end). > This works, though it is *very* ugly. Any other ideas? My apologies, but it turns out that this solution doesn't work

Re: [GENERAL] Cannot declare record members NOT NULL

> CREATE FUNCTION get_movies () > RETURNS SETOF get_movies_t > LANGUAGE sql STABLE > AS > $$ > SELECT movie_id, movie_name FROM movies > WHERE movie_id NOT NULL AND movie_name NOT NULL; > $$ Hey, Thanks for the suggestion. Unfortunately, it still doesn't work. Here is what Postgresql is tellin

Re: [GENERAL] Cannot declare record members NOT NULL

> > I haven't tried it with a view yet - so this may or may not work. But try > giving it a shot by declaring a view > > create view vmovies as > select movie_id,movie_text from movies > > and let your function return setof vmovies > > Maybe that works - I think it should. Hey, Thanks for th

Re: [GENERAL] Cannot declare record members NOT NULL

> I don't know if this will work, but here is another idea: > > SELECT movie_id, movie_name > FROM get_movies() AS ( int4 NOT NULL, text NOT NULL ); > Hi, Nope. That's not even valid syntax... But thanks for effort, anyway! Cheers, C.S. ___

Re: [GENERAL] Cannot declare record members NOT NULL

Hi Tom, > In any case, it appears to me that your gripe has little to do with > whether there's actually any enforcement of the not-null condition, > and much to do with whether some unspecified client-side software > thinks the query result column is guaranteed not null. Most likely > you're goi

Re: [GENERAL] Cannot declare record members NOT NULL

Hi, > I presume you mean that the server is saying the column can be NULL, > not that it is actually NULL, since: Yeah, that is the correct semantics: "it can be NULL". It does make a world of difference on the client side, because an "int4 never NULL" is a different type from "int4 possibly NU

Re: [GENERAL] Cannot declare record members NOT NULL

> Interesting. What language are you using? OCaml. The type-safety comes from the PG'OCaml bindings, which basically check if the types in the database are consistent with the types in the program. It's very neat technology, but unfortunately sometimes it seems too advanced for its own good, si

Re: [GENERAL] Cannot declare record members NOT NULL

> Unfortunately for you, they are not different types. If the OCaml > binding thinks they are, it's the binding's problem; especially since > the binding seems to be using a completely lame method of trying to tell > the difference. Hi, In OCaml and in other languages with strong type systems, "

Re: [GENERAL] Cannot declare record members NOT NULL

Hi, > The thing behind the RETURNS in a function is always a data type, > regardless if it is one that has been explicitly declared with > CREATE TYPE or implicitly by CREATE TABLE. > > There are no NOT NULL conditions for data types. > > NOT NULL only exists for table columns. Thanks for the i

[GENERAL] PL/pgSQL and SETOF

Hi, I am having trouble getting a really simple PL/pgSQL function to work. I am beginning to wonder if there is not a bug somewhere, or if Postgresql's type system is not broken. Anyway, suppose I have the following table and type defined: CREATE TABLE items (

Re: [GENERAL] PL/pgSQL and SETOF

> I would guess you're calling it like: > > SELECT get_items2(); > > whereas, you should call set returning functions like: > > SELECT * FROM get_items2(); Hi, Yeah, that was the problem -- thanks! But still on that subject: is my version of get_items2 the simplest that is possible in PL/

[GENERAL] Common table expressions and threaded comments

Greetings, I need to implement the classical problem of threaded comments in a Postgresql database. I know that the upcoming 8.4 release includes Common Table Expressions, so I have to ask: is this feature the mother-of-all-solutions to the threaded comments problem, or is it still worth investi