Thread: debugging query to put message in pg logfile?

[postgresql-7.4RC2, python-2.3.3, PyGreSQL-3.4, SuSE x86 Linux 8.2]

I've started putting debugging queries like:
  select "opwin.py: committing step signoff"

in my app, just to have an entry in the postgres logfile.  These are
especially helpful in tracking down what piece of code did a 'commit',
since there's nothing to distinguish one from another in the log. 

Is there some cheaper (or more appropriate) sql statement that will show
up in the postgres log?  I thought I remembered a "message" sql statement
or something like that.

-- George Young

-- 
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)

george young <gry@ll.mit.edu> writes:
> I've started putting debugging queries like:
>    select "opwin.py: committing step signoff"
> in my app, just to have an entry in the postgres logfile.

> Is there some cheaper (or more appropriate) sql statement that will show
> up in the postgres log?

You could just send SQL comments:
-- opwin.py: committing step signoff

One advantage of this is that you can merge the comments with actual
commands, thus not incurring even a network round-trip time for them.
If you do send it separately, it will act like an empty query string.

People tend not to think of this because psql strips -- comments before
sending commands.  But I believe all the lower-level libraries will pass
them through.  (If you need to pass loggable comments through psql, I
think the /* ... */ form will work.)
        regards, tom lane

On Thu, 04 Mar 2004 16:35:01 -0500
Tom Lane <tgl@sss.pgh.pa.us> threw this fish to the penguins:

> george young <gry@ll.mit.edu> writes:
> > I've started putting debugging queries like:
> >    select "opwin.py: committing step signoff"
> > in my app, just to have an entry in the postgres logfile.
> 
> > Is there some cheaper (or more appropriate) sql statement that will show
> > up in the postgres log?
> 
> You could just send SQL comments:
> 
>     -- opwin.py: committing step signoff
> 
> One advantage of this is that you can merge the comments with actual
> commands, thus not incurring even a network round-trip time for them.
> If you do send it separately, it will act like an empty query string.
> 
> People tend not to think of this because psql strips -- comments before
> sending commands.  But I believe all the lower-level libraries will pass
> them through.  (If you need to pass loggable comments through psql, I
> think the /* ... */ form will work.)

Alas no:

Python 2.3.3 (#1, Jan  3 2004, 07:17:11) 
[GCC 3.3.2] on linux2
>>> import pgdb
>>> db=pgdb.connect(host='ivy:5433',database='pigtest')
>>> c=db.cursor()
>>> cur.execute('-- the rain')
Traceback (most recent call last): File "<stdin>", line 1, in ? File "/usr/local/lib/python2.3/site-packages/pgdb.py",
line189, in execute   self.executemany(operation, (params,)) File "/usr/local/lib/python2.3/site-packages/pgdb.py",
line210, in executemany   raise OperationalError, "internal error in '%s'" % sql
 
pgdb.OperationalError: internal error in '-- the rain'

Likewise for /* comments */. :-(

I'll continue this on the pygresql mailing list; and I guess stick to
"select 'comment text'" for now...

Thanks,
  -- George Young

-- 
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)