Thread: Error in insert statement
I need help understanding what I'm doing wrong with an insert statement.
I'm running 8.3.7 on Windows and the DB is complaining about the select
statement in my insert statement.
I'm trying to execute
DROP TABLE IF EXISTS "tblTempSpecs";
CREATE TEMP TABLE "tblTempSpecs" AS SELECT "tblSpecs"."CartridgeTypeID",
"tblSpecs"."ColorID", "tblTestTypes"."TestTypeID", "tblZones"."ZoneID",
"tblSpecs"."PaperID",
"tblSpecs"."AttributeID", "tblSpecs"."Spec"
FROM "tblSpecs", "tblZones", "tblTestTypes"
WHERE "tblSpecs"."ZoneID" IS NULL AND "tblTestTypes"."TestTypeID" IN
(SELECT DISTINCT "TestTypeID" FROM "tblCartridgePQSetIntervals" WHERE
"CartridgeTypeID" = 74);
INSERT INTO "tblSpecs" ("CartridgeTypeID", "ColorID", "TestTypeID",
"ZoneID", "PaperID", "AttributeID", "Spec") VALUES SELECT * FROM
"tblTempSpecs";
To insert data into
CREATE TABLE "tblSpecs"
(
"SpecID" integer NOT NULL DEFAULT
nextval('"sequence_SpecID"'::regclass),
"CartridgeTypeID" integer NOT NULL,
"ColorID" integer NOT NULL,
"TestTypeID" integer,
"ZoneID" integer,
"PaperID" integer,
"PrintCopyID" integer,
"AttributeID" integer NOT NULL,
"Spec" numeric NOT NULL,
CONSTRAINT "tblSpecs_pkey" PRIMARY KEY ("SpecID"),
CONSTRAINT "tblSpecs_AttributeID_fkey" FOREIGN KEY ("AttributeID")
REFERENCES "tblAttributes" ("AttributeID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "tblSpecs_CartridgeTypeID_fkey" FOREIGN KEY
("CartridgeTypeID")
REFERENCES "tblCartridgeTypes" ("CartridgeTypeID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "tblSpecs_ColorID_fkey" FOREIGN KEY ("ColorID")
REFERENCES "tblColors" ("ColorID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "tblSpecs_PaperID_fkey" FOREIGN KEY ("PaperID")
REFERENCES "tblPaperTypes" ("PaperID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "tblSpecs_TestTypeID_fkey" FOREIGN KEY ("TestTypeID")
REFERENCES "tblTestTypes" ("TestTypeID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "tblSpecs_ZoneID_fkey" FOREIGN KEY ("ZoneID")
REFERENCES "tblZones" ("ZoneID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
What am I doing wrong?
Mike
Try dropping the word "VALUES".
eg.
INSERT INTO "tblSpecs" ("CartridgeTypeID", "ColorID",
"TestTypeID","ZoneID", "PaperID", "AttributeID", "Spec")
SELECT "CartridgeTypeID", "ColorID", "TestTypeID","ZoneID", "PaperID",
"AttributeID","Spec" from "tblTempSpecs";
On Thu, Jul 16, 2009 at 12:40 PM, Relyea, Mike<Mike.Relyea@xerox.com> wrote:
> I need help understanding what I'm doing wrong with an insert statement.
> I'm running 8.3.7 on Windows and the DB is complaining about the select
> statement in my insert statement.
>
> I'm trying to execute
>
> DROP TABLE IF EXISTS "tblTempSpecs";
> CREATE TEMP TABLE "tblTempSpecs" AS SELECT "tblSpecs"."CartridgeTypeID",
> "tblSpecs"."ColorID", "tblTestTypes"."TestTypeID", "tblZones"."ZoneID",
> "tblSpecs"."PaperID",
> "tblSpecs"."AttributeID", "tblSpecs"."Spec"
> FROM "tblSpecs", "tblZones", "tblTestTypes"
> WHERE "tblSpecs"."ZoneID" IS NULL AND "tblTestTypes"."TestTypeID" IN
> (SELECT DISTINCT "TestTypeID" FROM "tblCartridgePQSetIntervals" WHERE
> "CartridgeTypeID" = 74);
>
> INSERT INTO "tblSpecs" ("CartridgeTypeID", "ColorID", "TestTypeID",
> "ZoneID", "PaperID", "AttributeID", "Spec") VALUES SELECT * FROM
> "tblTempSpecs";
>
> To insert data into
>
> CREATE TABLE "tblSpecs"
> (
> "SpecID" integer NOT NULL DEFAULT
> nextval('"sequence_SpecID"'::regclass),
> "CartridgeTypeID" integer NOT NULL,
> "ColorID" integer NOT NULL,
> "TestTypeID" integer,
> "ZoneID" integer,
> "PaperID" integer,
> "PrintCopyID" integer,
> "AttributeID" integer NOT NULL,
> "Spec" numeric NOT NULL,
> CONSTRAINT "tblSpecs_pkey" PRIMARY KEY ("SpecID"),
> CONSTRAINT "tblSpecs_AttributeID_fkey" FOREIGN KEY ("AttributeID")
> REFERENCES "tblAttributes" ("AttributeID") MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT "tblSpecs_CartridgeTypeID_fkey" FOREIGN KEY
> ("CartridgeTypeID")
> REFERENCES "tblCartridgeTypes" ("CartridgeTypeID") MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT "tblSpecs_ColorID_fkey" FOREIGN KEY ("ColorID")
> REFERENCES "tblColors" ("ColorID") MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT "tblSpecs_PaperID_fkey" FOREIGN KEY ("PaperID")
> REFERENCES "tblPaperTypes" ("PaperID") MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT "tblSpecs_TestTypeID_fkey" FOREIGN KEY ("TestTypeID")
> REFERENCES "tblTestTypes" ("TestTypeID") MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT "tblSpecs_ZoneID_fkey" FOREIGN KEY ("ZoneID")
> REFERENCES "tblZones" ("ZoneID") MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION
> )
> WITH (
> OIDS=FALSE
> );
>
> What am I doing wrong?
>
> Mike
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Relyea, Mike wrote on 16.07.2009 21:40:
> I need help understanding what I'm doing wrong with an insert statement.
> I'm running 8.3.7 on Windows and the DB is complaining about the select
> statement in my insert statement.
When using a SELECT for an INSERT the values part is not needed in fact its
incorrect syntax.
You need to run:
INSERT INTO "tblSpecs"
("CartridgeTypeID", "ColorID", "TestTypeID",
"ZoneID", "PaperID", "AttributeID", "Spec")
SELECT * FROM "tblTempSpecs";
But: using a "SELECT *" here is calling for trouble. You are relying on an
implicit an not guaranteed order and number of columns.
Listing all the needed columns in the SELECT statement is much more robust.
Btw: you should create your tables without using double quotes, thus you can get
rid of them when doing normal DML.
Out of curiosity: why do you prefix the table with "tbl"? Don't you know it's a
table? Sounds like a strange naming scheme to me.
Thomas
> From: bricklen [mailto:bricklen@gmail.com]
> Sent: Thursday, July 16, 2009 4:05 PM
> To: Relyea, Mike
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Error in insert statement
>
> Try dropping the word "VALUES".
>
> eg.
> INSERT INTO "tblSpecs" ("CartridgeTypeID", "ColorID",
> "TestTypeID","ZoneID", "PaperID", "AttributeID", "Spec")
> SELECT "CartridgeTypeID", "ColorID", "TestTypeID","ZoneID",
> "PaperID", "AttributeID","Spec" from "tblTempSpecs";
>
I knew there had to be some bone-headed mistake I was making. That
worked of course. Thanks.
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of
> Thomas Kellerer
> Sent: Thursday, July 16, 2009 4:05 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Error in insert statement
>
> Relyea, Mike wrote on 16.07.2009 21:40:
> > I need help understanding what I'm doing wrong with an
> insert statement.
> > I'm running 8.3.7 on Windows and the DB is complaining about the
> > select statement in my insert statement.
>
> When using a SELECT for an INSERT the values part is not
> needed in fact its incorrect syntax.
>
> You need to run:
>
> INSERT INTO "tblSpecs"
> ("CartridgeTypeID", "ColorID", "TestTypeID", "ZoneID",
> "PaperID", "AttributeID", "Spec") SELECT * FROM "tblTempSpecs";
Got it. Thanks.
> But: using a "SELECT *" here is calling for trouble. You are
> relying on an implicit an not guaranteed order and number of columns.
> Listing all the needed columns in the SELECT statement is
> much more robust.
Point taken. Since this is just to load the table initially to do my
development work no harm done. If it were a production environment I'll
make sure to list all of the columns specifically.
>
> Btw: you should create your tables without using double
> quotes, thus you can get rid of them when doing normal DML.
I would if I could. I'm interfacing with a database I did not create.
Instead of having some needing quotes and some not, I find it easier to
just quote everything.
> Out of curiosity: why do you prefix the table with "tbl"?
> Don't you know it's a table? Sounds like a strange naming
> scheme to me.
It's an old habit that I picked up from MS Access. There were times
that when going through some of the wizards in Access it wasn't clear if
the choice you were selecting was a table or a query. So I learned
there to prefix all of my tables with "tbl" and all of my queries with
"qry". Old habits die hard.
Mike
I'm running postgres 8.1.8 on Debian and I think memory leak occur when
disconnect database.
1. environment setting
1.1 postgresql version:
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 8.1.8 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
20061115 (prerelease) (Debian 4.1.1-21)
(1 row)
1.2 kernel version:
Linux PMS 2.6.18-4-686 #2 SMP Fri Aug 1 22:47:38 JST 2008 i686 GNU/Linux
2. test example:
int OpenDataBase(void)
{
int i_ret = 0;
EXEC SQL CONNECT TO test_db;
if(sqlca.sqlcode == 0){
i_ret = 1;
}
return i_ret;
}
sint8 CloseDataBase(void)
{
sint8 i_ret = !0;
EXEC SQL disconnect all;
if(sqlca.sqlcode == 0){
i_ret = 0;
}
return i_ret;
}
int main()
{
OpenDataBase();
CloseDataBase();
}
when I use valgrind to check memory information, the memory leak is in view.
would anyone give me a suggestion. I'm looking forward your help.
best wishes.
--
Winsea.
8.1.8 is pretty old.
Also you'll have better luck getting help if you actually include the output
from Valgrind.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of tanjunhua
Sent: Friday, July 17, 2009 8:12 AM
To: Postgres General Postgres General
Subject: [GENERAL] memory leak occur when disconnect database
I'm running postgres 8.1.8 on Debian and I think memory leak occur when
disconnect database.
1. environment setting
1.1 postgresql version:
version
----------------------------------------------------------------------------
-----------------------------------
PostgreSQL 8.1.8 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
20061115 (prerelease) (Debian 4.1.1-21)
(1 row)
1.2 kernel version:
Linux PMS 2.6.18-4-686 #2 SMP Fri Aug 1 22:47:38 JST 2008 i686 GNU/Linux
2. test example:
int OpenDataBase(void)
{
int i_ret = 0;
EXEC SQL CONNECT TO test_db;
if(sqlca.sqlcode == 0){
i_ret = 1;
}
return i_ret;
}
sint8 CloseDataBase(void)
{
sint8 i_ret = !0;
EXEC SQL disconnect all;
if(sqlca.sqlcode == 0){
i_ret = 0;
}
return i_ret;
}
int main()
{
OpenDataBase();
CloseDataBase();
}
when I use valgrind to check memory information, the memory leak is in view.
would anyone give me a suggestion. I'm looking forward your help.
best wishes.
--
Winsea.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Your test case doesn't build, but I've attached a trivially tweaked one
that does.
Valgrind's report (valgrind --leak-check=full ./test) on my Ubuntu 9.04
machine with Pg 8.3.7 is:
==23382== 156 (36 direct, 120 indirect) bytes in 1 blocks are definitely
lost in loss record 1 of 4
==23382== at 0x4026FDE: malloc (vg_replace_malloc.c:207)
==23382== by 0x4211548: nss_parse_service_list (nsswitch.c:547)
==23382== by 0x4211E25: __nss_database_lookup (nsswitch.c:134)
==23382== by 0x4B61F5B: ???
==23382== by 0x4B6400C: ???
==23382== by 0x41B7A51: getpwuid_r@@GLIBC_2.1.2 (getXXbyYY_r.c:253)
==23382== by 0x42A87DD: (within /usr/lib/libpq.so.5.1)
==23382== by 0x4292955: (within /usr/lib/libpq.so.5.1)
==23382== by 0x429749E: (within /usr/lib/libpq.so.5.1)
==23382== by 0x4297528: (within /usr/lib/libpq.so.5.1)
==23382== by 0x4297E24: PQsetdbLogin (in /usr/lib/libpq.so.5.1)
==23382== by 0x4053563: ECPGconnect (in /usr/lib/libecpg.so.6.0)
==23382==
==23382== LEAK SUMMARY:
==23382== definitely lost: 36 bytes in 1 blocks.
==23382== indirectly lost: 120 bytes in 10 blocks.
==23382== possibly lost: 0 bytes in 0 blocks.
==23382== still reachable: 220 bytes in 1 blocks.
==23382== suppressed: 0 bytes in 0 blocks.
If you're seeing the same output, then the issue you're running into is
libnss caching NSS services list ( /etc/services, plus LDAP/NIS services
etc) when it's first used. This memory is "leaked" in the sense that
it's not free()d when the program exits, but that doesn't matter _at_
_all_. When the program exits, the OS cleans up its allocations anyway,
so the free() would only be wasting CPU doing work that's about to be
thrown away and slowing down the program's exit in the process. It'd
also open up all sorts of exciting issues if another atexit hook tried
to use NSS...
This "leak" should be added to your valgrind suppressions file and
ignored. You can re-run valgrind with:
valgrind --leak-check=full --gen-suppressions=all ./test
to generate a suppressions file, but you'll usually want to edit it to
make it a bit less specific. For example, this suppressions entry should
do the trick:
{
libnss_service_cache
Memcheck:Leak
fun:malloc
fun:nss_parse_service_list
fun:__nss_database_lookup
}
If I re-run valgrind with the suppressions entry (in the file
"ecpg_suppressions")
valgrind --leak-check=full --suppressions=ecpg_suppressions ./test
I get no reported leaks.
Valgrind is a great tool, but you must learn how to identify false
positives and tell the difference between a leak that matters (say 1kb
allocated and not freed in a loop that runs once per second) and a leak
that doesn't.
--
Craig Ringer
Attachment
Sorry for the reply-to-self, but I thought I'd take ecpg out of the
equation:
#include <sys/types.h>
#include <pwd.h>
int main()
{
struct passwd p;
struct passwd * r;
char buf[500];
getpwuid_r(1000, &p, &buf[0], 500, &r);
}
... produces the same leak report.
Since you didn't include information like the actual errors from
valgrind, I can't be sure that the report you are getting is the same as
the one I am getting. It could be that in your older version there _is_
something wrong. Perhaps you could post the output of running
valgrind --leak-check=full ./testprogram
just to be sure?
--
Craig Ringer
Because of the three-day break, my response is late. > Valgrind is a great tool, but you must learn how to identify false > positives and tell the difference between a leak that matters (say 1kb > allocated and not freed in a loop that runs once per second) and a leak > that doesn't. I get the memory leak scenario not only from Valgrind, but also from the output of top command. At first I think the memory leak occur when I disconnect database by Valgrind, then I write a test sample that just connect and disconnect database in a infinite loop to check it. after 1 hour performation, 7M memory is leaked.
Because of the three-day break, my response is late. > 8.1.8 is pretty old. > Also you'll have better luck getting help if you actually include the > output > from Valgrind. the output from Valgrind is not stored. from now on, I will do it again and get the result from Valgrind. PS: the memory leak scenario is not only got from Valgrind, but also from the period output of top command.
On Tue, 2009-07-21 at 13:53 +0900, tanjunhua wrote: > I get the memory leak scenario not only from Valgrind, but also from the > output of top command. > At first I think the memory leak occur when I disconnect database by > Valgrind, then I write a test sample that just connect and disconnect > database in a infinite loop to check it. after 1 hour performation, 7M > memory is leaked. OK, that's interesting. Please provide full, buildable source code for your standalone test program (or confirm it also occurs with the sources I posted) and the output of: valgrind --leak-check=full ./testprogram -- Craig Ringer
>> Also you'll have better luck getting help if you actually include the >> output >> from Valgrind. The appendix include source code, execute program and output from top command and Valgrind. the top_result_before_1_hour is the information got from top command before executing test program. after 1 hour performance, I store the top information into top_result_after_1_hour. the valgrind_output is the information from Valgrind: valgrind --leak-check=full show-reachable=yes ./test > valgrind_output 2>&1 because the mail server will reject execute program. then I compress it with ciper code(111111) and the execute program is compressed also. I hope it will bring better luck from you. look forward to your response again.
Attachment
> Please provide full, buildable source code for your standalone test > program (or confirm it also occurs with the sources I posted) and the > output of: > > valgrind --leak-check=full ./testprogram The appendix include source code, execute program and output from top command and Valgrind. the top_result_before_1_hour is the information got from top command before executing test program. after 1 hour performance, I store the top information into top_result_after_1_hour. the valgrind_output is the information from Valgrind: valgrind --leak-check=full show-reachable=yes ./test > valgrind_output 2>&1 Best wishes.