Re: [SQL] debugging query to put message in pg logfile?
On Thu, 04 Mar 2004 16:35:01 -0500
Tom Lane <[EMAIL PROTECTED]> threw this fish to the penguins:
> george young <[EMAIL PROTECTED]> 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 "", line 1, in ?
File "/usr/local/lib/python2.3/site-packages/pgdb.py", line 189, in execute
self.executemany(operation, (params,))
File "/usr/local/lib/python2.3/site-packages/pgdb.py", line 210, 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)
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] query optimization
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 04 March 2004 9:56 am, Charles Hauser wrote: > All, > > I have the following query which is running quite slow on our server > and was hoping someone would have suggestions how I might improve it. > Might want to try emailing the performance list as well, after you take Tom's advice. They tend to get more excited about performance issues. - -- Jonathan Gardner [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFASKq6qp6r/MVGlwwRAjeiAKCJpD/imnxP71f1GU8zpUVrWvd+qACeNcOF 9KvCM7HSp7jr9Ep9Xrs050M= =Ph6a -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Triggers
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 26 February 2004 2:18 am, Philip J. Boonzaaier wrote: > The technical reference gives an example of a trigger on a table - > employee Just to test this, I have created the following table, > CREATE TABLE employee > (name VARCHAR(30), > age int4, > state VARCHAR(2), > manager VARCHAR(3), > adult VARCHAR(3)); > > The I created a simple Function, as follows : > > CREATE FUNCTION trig_insert_update_check_emp() RETURNS opaque AS ' > BEGIN > IF new.age > 20 THEN > new.adult = ''yes''; > ELSE > new.adult = ''no''; > END IF; > END; > ' LANGUAGE 'plpgsql'; Couple of comments: 1) Your trigger functions needs to return something. In this case, you would "RETURN NEW". 2) I believe trigger functions need to "RETURNS TRIGGER", not "RETURNS opaque", but I could be wrong. Give that a try. Question: 1) What version of PostgreSQL are you running? - -- Jonathan Gardner [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFASKulqp6r/MVGlwwRAgpLAKCycwL/i+/mb6bW1W4QjHKBO9e0xQCgl42q pmohSw7PZiuIWgOQXxtgvI0= =4iDJ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] VIEW on lookup table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday 27 February 2004 2:18 pm, JJ Gabor wrote: > Hello all, > > I have a lookup table consisting of 100,000+ rows. > > 99% of the lookup values resolve to 'Unknown'. > > Building the lookup table takes a long time. > > I would like to remove the 'Unknown' entries from the > table and provide a VIEW to emulate them. > > The VIEW would need to provide all 100,000+ rows by > using the reduced lookup data and generating the > remaining values on the fly. > > The lookup table structure: > > CREATE TABLE lookup_data ( > > id1 INTEGER, > id2 INTEGER, > name TEXT, > > PRIMARY KEY (id1, id2) > ); > > id1 is an INTEGER; from 0 through to 50,000+ > id2 is an INTEGER; either 9 or 16. > > Example data: > > INSERT INTO lookup_data (id1, id2, name) VALUES (1, 9, 'a'); > INSERT INTO lookup_data (id1, id2, name) VALUES (1, 16, 'b'); > INSERT INTO lookup_data (id1, id2, name) VALUES (2, 9, 'c'); > INSERT INTO lookup_data (id1, id2, name) VALUES (2, 16, 'd'); > INSERT INTO lookup_data (id1, id2, name) VALUES (3, 9, 'e'); > INSERT INTO lookup_data (id1, id2, name) VALUES (3, 16, 'f'); > INSERT INTO lookup_data (id1, id2, name) VALUES (4, 9, 'g'); > INSERT INTO lookup_data (id1, id2, name) VALUES (4, 16, 'h'); > INSERT INTO lookup_data (id1, id2, name) VALUES (8, 9, 'i'); > INSERT INTO lookup_data (id1, id2, name) VALUES (8, 16, 'j'); > INSERT INTO lookup_data (id1, id2, name) VALUES (10, 9, 'k'); > INSERT INTO lookup_data (id1, id2, name) VALUES (10, 16, 'l'); > .. > > In the example data, entries where id1 is 5,6,7,9 are 'Unknown'; > > The VIEW would return: > > id1, id2, name > 1, 9, 'a' > 1, 16, 'b' > 2, 9, 'c' > 2, 16, 'd' > 3, 9, 'e' > 3, 16, 'f' > 4, 9, 'g' > 4, 16, 'h' > 5, 9, 'Unknown' > 5, 16, 'Unknown' > 6, 9, 'Unknown' > 6, 16, 'Unknown' > 7, 9, 'Unknown' > 7, 16, 'Unknown' > 8, 9, 'i' > 8, 16, 'j' > 9, 9, 'Unknown' > 9, 16, 'Unknown' > 10, 9, 'k' > 10, 16, 'l' > > I am using Postgres 7.2.1, which prevents me using a > function to return a result set. > > Can I achieve this in pure SQL? Yes. If you create a table with all of the values, 1 to 100,000+, and then join that with lookup_data, using a "left outer join", and then use a case statement for the value -- when NULL, 'Unknown', then it should work. I would look at bending the requirements a bit before I do this. Why do you want the string "Unknown" and not NULL? What is this table going to be used for? Also, just because you can't write a function in the database to do this doesn't mean you can't write a function in perl or python outside of the database to do it. Also, seriously consider upgrading to 7.4.1. 7.2 is ancient and really shouldn't be used anymore. - -- Jonathan Gardner [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFASK0wqp6r/MVGlwwRAub2AKCUcqvFvkD1KjXLEeg8osybgw5kqwCgiq8W YiJY3ZYsAXNfjjBTCF0vGKE= =5EIl -END PGP SIGNATURE- ---(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
[SQL] Read bytea column from table and convert into base64.....
Hi, I have a bytea column which I want to convert into base64. At the moment I am reading the bytea colum into my java app as a byte[]. I am then calling another static method to convert the byte[] into base64. I want postgreSQl to handle the conversion so my app can read in the base64 string. So for example: select (base64)byteaColumn from tableName where artistID = x many thanks in advance ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
