Re: Question about SQL statement error

On Wed, 25 Aug 2004, [ISO-8859-1] Mário Gamito wrote:

> Hi,
>
> I'm a newbie at postgreSQL, although i have years of experience with MySQL.
>
> My question is (and i tried to found the answer):
> why this gives an error ?
>
> SELECT url_negado INTO OUTFILE '/tmp/urls_negados.txt' FROM urls_negados;
>
> Any help would be appreciated.
>
> Sorry for the basic answer :(
>
> Warm Regards,
> Mário Gamito
>

Check out COPY and \copy. COPY is server-side, thus it's executed on
the host the server runs on, with the permissions of the user the server
is running as. \copy is a psql command, thus client-side. It's executed
on the client host and with the permissions of the user running psql (you).
If you are writing your own client application, you can use
COPY ... FROM STDIN and COPY ... TO STDOUT (which is what psql does).

http://www.postgresql.org/docs/7.4/interactive/sql-copy.html
http://www.postgresql.org/docs/7.4/interactive/app-psql.html

.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo(at)ESI(dot)it
>From pgsql-general-owner(at)postgresql(dot)org Wed Aug 25 22:51:37 2004
X-Original-To: pgsql-general-postgresql(dot)org(at)localhost(dot)postgresql(dot)org
Received: from localhost (unknown [200.46.204.144])
by svr1.postgresql.org (Postfix) with ESMTP id 8BE195E37D0
for <pgsql-general-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>; Wed, 25 Aug 2004 22:51:36 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
with ESMTP id 36014-01
for <pgsql-general-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>;
Thu, 26 Aug 2004 01:51:31 +0000 (GMT)
Received: from sccrmhc11.comcast.net (sccrmhc11.comcast.net [204.127.202.55])
by svr1.postgresql.org (Postfix) with ESMTP id 7978A5E3632
for <pgsql-general(at)postgresql(dot)org>; Wed, 25 Aug 2004 22:51:28 -0300 (ADT)
Received: from kenny.ugholf.net ([67.161.200.254])
by comcast.net (sccrmhc11) with ESMTP
id <2004082601513301100c3tmhe>; Thu, 26 Aug 2004 01:51:33 +0000
Received: from jandr.org (rjo-200-170-42-140.dial-up.vento.com.br [200.170.42.140] (may be forged))
(authenticated bits=0)
by kenny.ugholf.net (8.12.10/8.12.10) with ESMTP id i7Q1pNtv029795
(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO)
for <pgsql-general(at)postgresql(dot)org>; Wed, 25 Aug 2004 19:51:29 -0600
Message-ID: <412D431F(dot)2050908(at)jandr(dot)org>
Date: Wed, 25 Aug 2004 22:55:43 -0300
From: Jon Lapham <lapham(at)jandr(dot)org>
Reply-To: lapham(at)jandr(dot)org
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040510
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-general(at)postgresql(dot)org
Subject: EXPLAIN ANALYZE total runtime != walltime
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.6 tagged_above=0.0 required=5.0
tests=RCVD_IN_NJABL_DUL, RCVD_IN_SORBS_DUL
X-Spam-Level: *
X-Archive-Number: 200408/1335
X-Sequence-Number: 64890

I have been using the EXPLAIN ANALYZE command to debug some performance
bottlenecks in my database. In doing so, I have found an oddity (to me
anyway). The "19ms" total runtime reported below actually takes 25
seconds on my computer (no other CPU intensive processes running). Is
this normal for EXPLAIN ANALYZE to report a total runtime so vastly
different from wall clock time?

During the "explain ANALYZE delete from msgid;" the CPU is pegged at
100% for the postmaster process, and the HD light only flashes
intermittently, so I do not think it is HD I/O.

I tossed in a "EXPLAIN ANALYZE VERBOSE" at the end of this email, in
case that helps anyone.

Thanks for any help!
-Jon

translator=> SELECT version();
version

---------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 7.4.2 on i386-redhat-linux-gnu, compiled by GCC
i386-redhat-linux-gcc (GCC) 3.3.3 20040216 (Red Hat Linux 3.3.3-2.1)
(1 row)

translator=> VACUUM ANALYZE ;
WARNING: skipping "pg_shadow" --- only table or database owner can
vacuum it
WARNING: skipping "pg_database" --- only table or database owner can
vacuum it
WARNING: skipping "pg_group" --- only table or database owner can vacuum it
VACUUM
translator=> BEGIN ;
BEGIN
translator=> explain ANALYZE delete from msgid;
QUERY PLAN

-----------------------------------------------------------------------------------------------------
Seq Scan on msgid (cost=0.00..23.81 rows=981 width=6) (actual
time=0.029..10.151 rows=981 loops=1)
Total runtime: 19.755 ms
(2 rows)
translator=> \d msgid
Table "public.msgid"
Column | Type | Modifiers

-----------+---------------+-------------------------------------------------------
id | integer | not null default
nextval('public.msgid_id_seq'::text)
projectid | integer | not null
msgid | text | not null
msgidmd5 | character(32) | not null
Indexes:
"msgid_pkey" primary key, btree (id)
Foreign-key constraints:
"$1" FOREIGN KEY (projectid) REFERENCES projects(id) ON DELETE CASCADE
translator=> rollback;
ROLLBACK
translator=> explain ANALYZE VERBOSE delete from msgid;
QUERY PLAN

----------------------------------------------------------------------------------------------------
{SEQSCAN
:startup_cost 0.00
:total_cost 23.81
:plan_rows 981
:plan_width 6
:targetlist (
{TARGETENTRY
:resdom
{RESDOM
:resno 1
:restype 27
:restypmod -1
:resname ctid
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk true
}
:expr
{VAR
:varno 1
:varattno -1
:vartype 27
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno -1
}
}
)

:qual <>
:lefttree <>
:righttree <>
:initPlan <>
:extParam ()

:allParam ()

:nParamExec 0
:scanrelid 1
}

Seq Scan on msgid (cost=0.00..23.81 rows=981 width=6) (actual
time=0.031..6.444 rows=981 loops=1)
Total runtime: 35.760 ms
(46 rows)

--
-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
Jon Lapham <lapham(at)jandr(dot)org> Rio de Janeiro, Brasil
Personal: http://www.jandr.org/
***-*--*----*-------*------------*--------------------*---------------