basic debugging question

**Tino Wildenhain** · Nov 23 '05, 02:41 AM

Re: basic debugging question

Am Di, den 26.10.2004 schrieb Scott Frankel um 21:39:[color=blue]
> I'm attempting to debug a script that should perform a simple INSERT of
> values,
> but for some reason doesn't. The insert appears to occur without
> error, printing
> "INSERT 18015 1 upon completion." Nonetheless, no data values appear
> to be
> added to the table when queried in psql.
>
> Questions:
>
> - What does the status msg, "INSERT 18015 1," refer to?
>
> - What is this output called? (So I can search the documentation for
> it.)
>
> - Is there something clever I can access -- besides this list ;) -- so
> I can
> peek inside INSERT 18015 1 to see what pgres is thinking about?
>
> Note that when I perform the INSERT by hand in psql, the row of data is
> entered
> without incident.[/color]

Ok, the script inserts, no error but the values dont appear.
You use the same SQL in psql to insert, you get the status
msg and the data appears?

Either your script ignores fail messages at all or
it is successfull with the insert but fails somehow
afterwards or just forget to commit() the transaction.
Closing the database connection or errors in subsequent
statements in the same transaction cause a rollback -
efectively whiping out all changes.

If the latter there would be no point in reading the
notices since you would get the very same message
but end up with no tuples in the table.

See for example in psql:

BEGIN work;
INSERT INTO ... (your insert you try);
-> you see the message INSERT 232354 1 upon completion
ROLLBACK;
look at your table - the inserted data isnt there anymore.

HTH
Tino

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

**Oliver Elphick** · Nov 23 '05, 02:41 AM

Re: basic debugging question

On Tue, 2004-10-26 at 12:39 -0700, Scott Frankel wrote:[color=blue]
> I'm attempting to debug a script that should perform a simple INSERT of
> values,
> but for some reason doesn't. The insert appears to occur without
> error, printing
> "INSERT 18015 1 upon completion." Nonetheless, no data values appear
> to be
> added to the table when queried in psql.
>
> Questions:
>
> - What does the status msg, "INSERT 18015 1," refer to?[/color]

It means one row was added to some table and the row's oid is 18015.

....[color=blue]
> - Is there something clever I can access -- besides this list ;) -- so
> I can
> peek inside INSERT 18015 1 to see what pgres is thinking about?[/color]

Try

SELECT * FROM <tablename> WHERE oid = 18015;

If that returns nothing, the row must have been added to some other
table, which would imply the existence of another table with a
compatible structure.

--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
=============== =============== ==========
"Whosoever therefore shall be ashamed of me and of my
words in this adulterous and sinful generation; of him
also shall the Son of man be ashamed, when he cometh
in the glory of his Father with the holy angels."
Mark 8:38

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

**Martijn van Oosterhout** · Nov 23 '05, 02:41 AM

Re: basic debugging question

On Tue, Oct 26, 2004 at 12:39:46PM -0700, Scott Frankel wrote:[color=blue]
>
> I'm attempting to debug a script that should perform a simple INSERT of
> values,
> but for some reason doesn't. The insert appears to occur without
> error, printing
> "INSERT 18015 1 upon completion." Nonetheless, no data values appear
> to be
> added to the table when queried in psql.[/color]

Wild stab: did you do it within a transaction and not commit. Note that
some database interfaces automatically start a transaction. Look for
"autocommit " or "commit".
[color=blue]
> Questions:
>
> - What does the status msg, "INSERT 18015 1," refer to?[/color]

The first is the OID of the row, the second is the number of rows
inserted.
[color=blue]
> - What is this output called? (So I can search the documentation for
> it.)[/color]

No idea, it's generated by psql. In a database interface you can get
the values directly.

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana. org> http://svana.org/kleptog/[color=blue]
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.[/color]

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBfrGpY5T wig3Ge+YRAmxeAJ 9TD50Rv3/7biuwY5hgfRjzZn 4FFgCfdjhL
8AYR1QaqlpxN5Je z2SJUrLo=
=Rf6G
-----END PGP SIGNATURE-----

**Scott Frankel** · Nov 23 '05, 02:41 AM

Re: basic debugging question

I should have *myself* committed.

Thanks for the suggestions (and OID tip)! It turned out that my script
was
not committing the transaction, so the insert was getting rolled-back.

Thanks
Scott

On Oct 26, 2004, at 12:39 PM, Scott Frankel wrote:
[color=blue]
>
> I'm attempting to debug a script that should perform a simple INSERT
> of values,
> but for some reason doesn't. The insert appears to occur without
> error, printing
> "INSERT 18015 1 upon completion." Nonetheless, no data values appear
> to be
> added to the table when queried in psql.
>
> Questions:
>
> - What does the status msg, "INSERT 18015 1," refer to?
>
> - What is this output called? (So I can search the documentation for
> it.)
>
> - Is there something clever I can access -- besides this list ;) -- so
> I can
> peek inside INSERT 18015 1 to see what pgres is thinking about?
>
> Note that when I perform the INSERT by hand in psql, the row of data
> is entered
> without incident.
>
> Thanks in advance!
> Scott
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)
>[/color]

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match