Re: [SQL] Problems Formulating a SELECT
On Tuesday 08 Oct 2002 7:19 pm, Charles Hauser wrote: > Richard, > [snip] > Is there a method to remove duplicate results? For instance the query > below in part yields : SELECT DISTINCT ... is what you're after. I'd do it in the views so the join has less rows to compare against. > chlamy_est-> ; > contig_id > --- > 27170 > 27173 > 27173 > 27179 > 27179 > 27179 > 27179 > 27179 -- Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] PLPGSQL errors
Hi,
I'm getting an error on a function I want to execute and I can't see why.
I've tried a few different things and tweaked my sql without joy so far.
I want to log all inserts or updates on one table to another table.
Below I have my error, function , table schema and sql insert statement.
Thanks
It's a little long and I know anyone on this list has enough work of their
own but what goes around comes around :-)
error:
[postgres@central postgres]$ /usr/local/pgsql/bin/psql demo -f sysinsert.sql
psql:sysinsert.sql:16: NOTICE: Error occurred while executing PL/pgSQL
function fn_sysmessages_log
psql:sysinsert.sql:16: NOTICE: at END of toplevel PL block
psql:sysinsert.sql:16: ERROR: control reaches end of trigger procedure
without RETURN
function :
CREATE function fn_sysmessages_log() RETURNS OPAQUE AS '
BEGIN
INSERT INTO sysmessages_log
(
id,
user_id,
message_date,
message_priority,
message,
status
)
VALUES
(
NEW.id,
NEW.user_id,
NEW.message_date,
NEW.message_priority,
NEW.message,
NEW.status
);
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER tr_sysmessages_log AFTER INSERT OR UPDATE ON sysmessages FOR
EACH ROW EXECUTE PROCEDURE fn_sysmessages_log();
and my 2 tables :
create table sysmessages
(
id serial PRIMARY KEY,
user_id integer NOT NULL,
message_date date DEFAULT now() NOT NULL,
message_priority char(1) CHECK( message_priority IN (1,2,3,4,5,6,7,8,9) )
NOT NULL,
message text NOT NULL,
status char(1) CHECK( status IN ('A','P','N') ) NOT NULL
);
create table sysmessages_log
(
log_id serial PRIMARY KEY,
id integer,
user_id integer NOT NULL,
message_date date DEFAULT now() NOT NULL,
message_priority char(1) CHECK( message_priority IN (1,2,3,4,5,6,7,8,9) )
NOT NULL,
message text NOT NULL,
status char(1) CHECK( status IN ('A','P','N') ) NOT NULL
);
and the insert statement which is causing the error:
INSERT INTO sysmessages
(
user_id,
message_date,
message_priority,
message,
status
)
VALUES
(
101,
'2002-10-10',
1,
'hi',
'A'
)
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] PLPGSQL errors
On Wednesday 09 Oct 2002 11:06 am, Rudi Starcevic wrote: > > error: > [postgres@central postgres]$ /usr/local/pgsql/bin/psql demo -f > sysinsert.sql psql:sysinsert.sql:16: NOTICE: Error occurred while > executing PL/pgSQL function fn_sysmessages_log > psql:sysinsert.sql:16: NOTICE: at END of toplevel PL block > psql:sysinsert.sql:16: ERROR: control reaches end of trigger procedure > without RETURN Look carefully - you don't have a RETURN new/old/null in your function. Take a look at the online manual (Server Programming, chapter 20) or some of the samples in Roberto's PostgreSQL cookbook (http://techdocs.postgresql.org) > function : > CREATE function fn_sysmessages_log() RETURNS OPAQUE AS ' > BEGIN > INSERT INTO sysmessages_log >( >id, >user_id, >message_date, >message_priority, >message, >status >) > VALUES > ( > NEW.id, > NEW.user_id, > NEW.message_date, > NEW.message_priority, > NEW.message, > NEW.status > ); > END; > ' LANGUAGE 'plpgsql'; -- Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Complex SQL query and performance strategy
Hi, I have a complex SQL query which requires the joining of 18 tables. There are only primary key indices on the table and at the moment it runs a little slow (30s or so) and so I am trying to optimise it. The output of EXPLAIN is a little confusing and seems to vary from run to run. Does the query optimiser have trouble with larger number of table joins? Also this will be running from a web front end, and I hope to have it encapsulated all within a function. Would it be better to break it up into multiple SQL statements/functions? Or to try to really tweak the indices? Thanks for any advice adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Complex SQL query and performance strategy
Adam Witney <[EMAIL PROTECTED]> writes: > I have a complex SQL query which requires the joining of 18 tables. There > are only primary key indices on the table and at the moment it runs a little > slow (30s or so) and so I am trying to optimise it. > The output of EXPLAIN is a little confusing and seems to vary from run to > run. Does the query optimiser have trouble with larger number of table > joins? The output probably would vary, because at that number of tables it'll be using the GEQO optimizer, which is probabilistic. If you don't like that, you can raise the GEQO threshold above 18 tables, but I suspect you'll not like the amount of time the exhaustive optimizer will take. A reasonable solution is to jack up the threshold, experiment until you find a good query plan, and then restructure the query with explicit JOIN operators to limit the optimizer's search space. That will bring the planning time down out of the stratosphere. See http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/explicit-joins.html for details. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] problem with the Index
I have a problem with the index of 1 table.
I hava a table created :
CREATE TABLE "acucliart" (
"cod_pto" numeric(8,0) NOT NULL,
"cod_cli" varchar(9) NOT NULL,
"mes" numeric(2,0) NOT NULL,
"ano" numeric(4,0) NOT NULL,
"int_art" numeric(5,0) NOT NULL,
"cantidad" numeric(12,2),
"ven_siv_to" numeric(14,2),
"ven_civ_to" numeric(14,2),
"tic_siv_to" numeric(14,2),
"tic_civ_to" numeric(14,2),
"visitas" numeric(2,0),
"ult_vis" date,
"ven_cos" numeric(12,2),
"ven_ofe" numeric(12,2),
"cos_ofe" numeric(12,2),
CONSTRAINT "acucliart_pkey"
PRIMARY KEY ("cod_cli")
);
if i do this select:
explain select * from acucliart where cod_cli=1;
postgres use the index
NOTICE: QUERY PLAN:
Index Scan using cod_cli_ukey on acucliart (cost=0.00..4.82 rows=1
width=478)
and this select
explain select * from acucliart where cod_cli>1;
Postgres don't use the index:
NOTICE: QUERY PLAN:
Seq Scan on acucliart (cost=0.00..22.50 rows=333 width=478)
why?
tk
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] SELECT statement never completes.
pgsql-sql, We are porting a database from IBM DB2 to PostgreSQL. In several related scripts, there is a SELECT statement that never completes in Postgres but completes in a few seconds using DB2, for example: Table row count: SELECT count(*) FROM tableX; 112671 SELECT count(*) from tableY; 314625 This statement does not complete: SELECT id FROM tableX WHERE id NOT IN (SELECT id FROM tableY); Any suggestions? Thanks __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos & More http://faith.yahoo.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] SELECT statement never completes.
On Wed, 9 Oct 2002, John Pauley wrote: > pgsql-sql, > > We are porting a database from IBM DB2 to PostgreSQL. > In several related scripts, there is a SELECT > statement that never completes in Postgres but > completes in a few seconds using DB2, for example: > > Table row count: > SELECT count(*) FROM tableX; > 112671 > SELECT count(*) from tableY; > 314625 > > This statement does not complete: > SELECT id FROM tableX WHERE id NOT IN (SELECT id FROM > tableY); > > Any suggestions? Unfortunately IN tends to have poor performance in postgresql. Often you can get better performance out of exists, but not always. You might want to try: select id from tableX WHERE NOT EXISTS (select * from tableY where tableY.id=tableX.id); and see if it runs better. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] problem with the Index
On Wed, 9 Oct 2002, Jose Antonio Leo wrote:
> I have a problem with the index of 1 table.
>
> I hava a table created :
> CREATE TABLE "acucliart" (
>"cod_pto" numeric(8,0) NOT NULL,
>"cod_cli" varchar(9) NOT NULL,
>"mes" numeric(2,0) NOT NULL,
>"ano" numeric(4,0) NOT NULL,
>"int_art" numeric(5,0) NOT NULL,
>"cantidad" numeric(12,2),
>"ven_siv_to" numeric(14,2),
>"ven_civ_to" numeric(14,2),
>"tic_siv_to" numeric(14,2),
>"tic_civ_to" numeric(14,2),
>"visitas" numeric(2,0),
>"ult_vis" date,
>"ven_cos" numeric(12,2),
>"ven_ofe" numeric(12,2),
>"cos_ofe" numeric(12,2),
>CONSTRAINT "acucliart_pkey"
>PRIMARY KEY ("cod_cli")
> );
>
> if i do this select:
> explain select * from acucliart where cod_cli=1;
> postgres use the index
> NOTICE: QUERY PLAN:
> Index Scan using cod_cli_ukey on acucliart (cost=0.00..4.82 rows=1
> width=478)
>
> and this select
> explain select * from acucliart where cod_cli>1;
> Postgres don't use the index:
> NOTICE: QUERY PLAN:
> Seq Scan on acucliart (cost=0.00..22.50 rows=333 width=478)
>
> why?
Well, how many rows are in the table? In the first case it estimates 1
row will be returned, in the second 333. Index scans are not always faster
than sequential scans as the percentage of the table to scan becomes
larger. If you haven't analyzed recently, you probably should do so and
if you want to compare, set enable_seqscan=off and try an explain there
and see what it gives you.
Also, why are you comparing a varchar(9) column with an integer?
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] foreign key problem
7.2.3. It's a mystery as to how this happened but there is a program we use to copy rows from one version to another. For some reason the person who wrote it disabled triggers before copying rows to a new version then re-enabled the triggers. If someone made changes while the triggers were off then this could happen. At least that's my story and I'm sticking to it. So, the program has been fixed and we hope this plugs the hole that that rabbit went down. Thanks, L. On Sun, 6 Oct 2002, Josh Berkus wrote: > Laurette, > > > How is this possible? I've tried to reproduce this, but haven't been able > > to yet. This has happened to use several times. > > Oh, forgot question 2: > SELECT version()? > > -- Laurette Cisneros The Database Group (510) 420-3137 NextBus Information Systems, Inc. www.nextbus.com -- It's 10 o'clock... Do you know where your bus is? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
