i guess i can't code in C... - Mailing list pgsql-novice

greetings,

i think i should not be posting this much junk, but i was afraid
people would just ask for it if i didn't, soo...  sorry.

This C code fragment is not working.  you can see from the verbose
trace output that the select that happens just prior to the attempted
insert does get logged, but the insert never does.

if i cut and paste the program's attempted insert onto a "psql" prompt,
add a ";", it works.  so my sql syntax is okay.

do i need to clear something before i do an insert after a select?

if this isn't the right output to send, lemme know.

thx in advance.  i mean it--to those of you who've experienced
oracle's support--well, i don't have to tell you.

j.

------------------------------------
code fragment:
------------------------------------
        sprintf(buffer,
                "INSERT INTO machines (hostname, os) VALUES ('%s', '%s')",
                node, sys);
fprintf(stderr,"===try to add:\n%s\n===\n",buffer);

        igot = PQsendQuery(conn_to_db,buffer);
fprintf(stderr,"igot is %d\n",igot);
        res_set = PQgetResult(conn_to_db);
  tmwh( res_set );
        while( res_set != NULL )
                {
fprintf(stderr,"res_set != NULL\n");
                res_set = PQgetResult(conn_to_db);
                PQclear(res_set);
                }

------------------------------------
program's running output:
------------------------------------
main: db_name is foot
main: node is aaaaaaaaaaaaaaaaa2
main: sys is Solaris
main: logType is messages
newHost():  db_server = xxxxx
newHost():  username = xxxxx
newHost():  password = xxxxx
newHost():  db_name = foot
db_connect():  host is xxxxx
db_connect():  pgport is 5432
db_connect():  attempting connection to database foot
tmwh: PGRES_TUPLES_OK
tmwh: PGRES_TUPLES_OK
tmwh:
SELECT m_id FROM machines WHERE hostname = 'aaaaaaaaaaaaaaaaa2';
===try to add:
INSERT INTO machines (hostname, os) VALUES ('aaaaaaaaaaaaaaaaa2', 'Solaris')
===
igot is 0
tmwh: PGRES_NONFATAL_ERROR
tmwh: PGRES_NONFATAL_ERROR

here's the relevant part of the log, with full debugging.
----------------------
DEBUG:  /opt/depot/postgresql-7.3.1/bin/postmaster child[8963]: starting with (
DEBUG:      postgres
DEBUG:      -d5
DEBUG:      -v131072
DEBUG:      -p
DEBUG:      foot
DEBUG:  )
DEBUG:  InitPostgres
DEBUG:  BackendStartup: forked pid=8963 socket=9
DEBUG:  StartTransactionCommand
LOG:  query: begin; select getdatabaseencoding(); commit
LOG:  parse tree:
{ QUERY :command 5 :source 0 :utility ? :resultRelation 0 :into <> :isPortal
false :isBinary false :hasAggs false :hasSubLinks false :rtable <> :jointree
<> :rowMarks () :targetList <> :groupClause <> :havingQual <> :distinctClause
<> :sortClause <> :limitOffset <> :limitCount <> :setOperations <>
:resultRelations ()}

LOG:  rewritten parse tree:
({ QUERY :command 5 :source 0 :utility ? :resultRelation 0 :into <> :isPortal
false :isBinary false :hasAggs false :hasSubLinks false :rtable <> :jointree
<> :rowMarks () :targetList <> :groupClause <> :havingQual <> :distinctClause
<> :sortClause <> :limitOffset <> :limitCount <> :setOperations <>
:resultRelations ()})

DEBUG:  ProcessUtility
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
LOG:  parse tree:
{ QUERY :command 1 :source 0 :utility <> :resultRelation 0 :into <> :isPortal
false :isBinary false :hasAggs false :hasSubLinks false :rtable <> :jointree {
FROMEXPR :fromlist <> :quals <>} :rowMarks () :targetList ({ TARGETENTRY
:resdom { RESDOM :resno 1 :restype 19 :restypmod -1 :resname
getdatabaseencoding :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { EXPR :typeOid 19  :opType func :oper { FUNC :funcid 1039
:funcresulttype 19 :funcretset false :funcformat 0 } :args <>}}) :groupClause
<> :havingQual <> :distinctClause <> :sortClause <> :limitOffset <>
:limitCount <> :setOperations <> :resultRelations ()}

LOG:  rewritten parse tree:
({ QUERY :command 1 :source 0 :utility <> :resultRelation 0 :into <> :isPortal
false :isBinary false :hasAggs false :hasSubLinks false :rtable <> :jointree {
FROMEXPR :fromlist <> :quals <>} :rowMarks () :targetList ({ TARGETENTRY
:resdom { RESDOM :resno 1 :restype 19 :restypmod -1 :resname
getdatabaseencoding :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { EXPR :typeOid 19  :opType func :oper { FUNC :funcid 1039
:funcresulttype 19 :funcretset false :funcformat 0 } :args <>}}) :groupClause
<> :havingQual <> :distinctClause <> :sortClause <> :limitOffset <>
:limitCount <> :setOperations <> :resultRelations ()})

LOG:  plan:
{ RESULT :startup_cost 0.00 :total_cost 0.01 :rows 1 :width 0 :qptargetlist ({
TARGETENTRY :resdom { RESDOM :resno 1 :restype 19 :restypmod -1 :resname
getdatabaseencoding :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { EXPR :typeOid 19  :opType func :oper { FUNC :funcid 1039
:funcresulttype 19 :funcretset false :funcformat 0 } :args <>}}) :qpqual <>
:lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0
:resconstantqual <>}

DEBUG:  ProcessQuery
LOG:  parse tree:
{ QUERY :command 5 :source 0 :utility ? :resultRelation 0 :into <> :isPortal
false :isBinary false :hasAggs false :hasSubLinks false :rtable <> :jointree
<> :rowMarks () :targetList <> :groupClause <> :havingQual <> :distinctClause
<> :sortClause <> :limitOffset <> :limitCount <> :setOperations <>
:resultRelations ()}

LOG:  rewritten parse tree:
({ QUERY :command 5 :source 0 :utility ? :resultRelation 0 :into <> :isPortal
false :isBinary false :hasAggs false :hasSubLinks false :rtable <> :jointree
<> :rowMarks () :targetList <> :groupClause <> :havingQual <> :distinctClause
<> :sortClause <> :limitOffset <> :limitCount <> :setOperations <>
:resultRelations ()})

DEBUG:  ProcessUtility
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
LOG:  query: SELECT m_id FROM machines WHERE hostname = 'aaaaaaaaaaaaaaaaa1';
LOG:  parse tree:
{ QUERY :command 1 :source 0 :utility <> :resultRelation 0 :into <> :isPortal
false :isBinary false :hasAggs false :hasSubLinks false :rtable ({ RTE :alias
<> :eref { ALIAS :aliasname machines :colnames ( "m_id"   "hostname"   "os" )}
:rtekind 0 :relid 33480 :inh true :inFromCl true :checkForRead true
:checkForWrite false :checkAsUser 0}) :jointree { FROMEXPR :fromlist ({
RANGETBLREF 1 }) :quals { EXPR :typeOid 16  :opType op :oper { OPER :opno 1062
:opid 0 :opresulttype 16 :opretset false } :args ({ VAR :varno 1 :varattno 2
:vartype 1043 :vartypmod 84  :varlevelsup 0 :varnoold 1 :varoattno 2} { CONST
:consttype 1043 :constlen -1 :constbyval false :constisnull false :constvalue
22 [ 0 0 0 22 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 49 ] })}}
:rowMarks () :targetList ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1 :resname m_id :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 1}}) :groupClause <> :havingQual <>
:distinctClause <> :sortClause <> :limitOffset <> :limitCount <>
:setOperations <> :resultRelations ()}

LOG:  rewritten parse tree:
({ QUERY :command 1 :source 0 :utility <> :resultRelation 0 :into <> :isPortal
false :isBinary false :hasAggs false :hasSubLinks false :rtable ({ RTE :alias
<> :eref { ALIAS :aliasname machines :colnames ( "m_id"   "hostname"   "os" )}
:rtekind 0 :relid 33480 :inh true :inFromCl true :checkForRead true
:checkForWrite false :checkAsUser 0}) :jointree { FROMEXPR :fromlist ({
RANGETBLREF 1 }) :quals { EXPR :typeOid 16  :opType op :oper { OPER :opno 1062
:opid 0 :opresulttype 16 :opretset false } :args ({ VAR :varno 1 :varattno 2
:vartype 1043 :vartypmod 84  :varlevelsup 0 :varnoold 1 :varoattno 2} { CONST
:consttype 1043 :constlen -1 :constbyval false :constisnull false :constvalue
22 [ 0 0 0 22 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 49 ] })}}
:rowMarks () :targetList ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1 :resname m_id :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 1}}) :groupClause <> :havingQual <>
:distinctClause <> :sortClause <> :limitOffset <> :limitCount <>
:setOperations <> :resultRelations ()})

LOG:  plan:
{ SEQSCAN :startup_cost 0.00 :total_cost 22.50 :rows 5 :width 4 :qptargetlist
({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname
m_id :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 1 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno 1}}) :qpqual ({ EXPR :typeOid 16  :opType op :oper { OPER :opno
1062 :opid 1070 :opresulttype 16 :opretset false } :args ({ VAR :varno 1
:varattno 2 :vartype 1043 :vartypmod 84  :varlevelsup 0 :varnoold 1 :varoattno
2} { CONST :consttype 1043 :constlen -1 :constbyval false :constisnull false
:constvalue  22 [ 0 0 0 22 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97
49 ] })}) :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm
0  :scanrelid 1 }

DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  proc_exit(0)
DEBUG:  shmem_exit(0)
DEBUG:  exit(0)
DEBUG:  reaping dead processes
DEBUG:  child process (pid 8963) exited with exit code 0
DEBUG:  StartTransactionCommand
LOG:  query: select * from machines;
LOG:  parse tree:
{ QUERY :command 1 :source 0 :utility <> :resultRelation 0 :into <> :isPortal
false :isBinary false :hasAggs false :hasSubLinks false :rtable ({ RTE :alias
<> :eref { ALIAS :aliasname machines :colnames ( "m_id"   "hostname"   "os" )}
:rtekind 0 :relid 33480 :inh true :inFromCl true :checkForRead true
:checkForWrite false :checkAsUser 0}) :jointree { FROMEXPR :fromlist ({
RANGETBLREF 1 }) :quals <>} :rowMarks () :targetList ({ TARGETENTRY :resdom {
RESDOM :resno 1 :restype 23 :restypmod -1 :resname m_id :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype
23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY
:resdom { RESDOM :resno 2 :restype 1043 :restypmod 84 :resname hostname
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 2 :vartype 1043 :vartypmod 84  :varlevelsup 0 :varnoold 1 :varoattno
2}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 1042 :restypmod 11
:resname os :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr {
VAR :varno 1 :varattno 3 :vartype 1042 :vartypmod 11  :varlevelsup 0 :varnoold
1 :varoattno 3}}) :groupClause <> :havingQual <> :distinctClause <>
:sortClause <> :limitOffset <> :limitCount <> :setOperations <>
:resultRelations ()}

LOG:  rewritten parse tree:
({ QUERY :command 1 :source 0 :utility <> :resultRelation 0 :into <> :isPortal
false :isBinary false :hasAggs false :hasSubLinks false :rtable ({ RTE :alias
<> :eref { ALIAS :aliasname machines :colnames ( "m_id"   "hostname"   "os" )}
:rtekind 0 :relid 33480 :inh true :inFromCl true :checkForRead true
:checkForWrite false :checkAsUser 0}) :jointree { FROMEXPR :fromlist ({
RANGETBLREF 1 }) :quals <>} :rowMarks () :targetList ({ TARGETENTRY :resdom {
RESDOM :resno 1 :restype 23 :restypmod -1 :resname m_id :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype
23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY
:resdom { RESDOM :resno 2 :restype 1043 :restypmod 84 :resname hostname
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 2 :vartype 1043 :vartypmod 84  :varlevelsup 0 :varnoold 1 :varoattno
2}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 1042 :restypmod 11
:resname os :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr {
VAR :varno 1 :varattno 3 :vartype 1042 :vartypmod 11  :varlevelsup 0 :varnoold
1 :varoattno 3}}) :groupClause <> :havingQual <> :distinctClause <>
:sortClause <> :limitOffset <> :limitCount <> :setOperations <>
:resultRelations ()})

LOG:  plan:
{ SEQSCAN :startup_cost 0.00 :total_cost 20.00 :rows 1000 :width 73
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod
-1 :resname m_id :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0
:varnoold 1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype
1043 :restypmod 84 :resname hostname :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 2 :vartype 1043 :vartypmod 84
:varlevelsup 0 :varnoold 1 :varoattno 2}} { TARGETENTRY :resdom { RESDOM
:resno 3 :restype 1042 :restypmod 11 :resname os :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 3 :vartype
1042 :vartypmod 11  :varlevelsup 0 :varnoold 1 :varoattno 3}}) :qpqual <>
:lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0
:scanrelid 1 }

DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand

--
Jay Scott        512-835-3553        gl@arlut.utexas.edu
Head of Sun Support, Sr. Operating Systems Specialist
Applied Research Labs, Computer Science Div.                   S224
University of Texas at Austin