[SQL] UTF-8 Problem ?
Hi Listers, I want to insert some german specific characters (umlaut characters) into a table, but I am getting the following Error message: postgres=# EXECUTE stmt (1, 1 , 1 , 'Grün') ; ERROR: invalid UTF-8 byte sequence detected near byte 0xfc Or postgres=# EXECUTE stmt (1, 1 , 1 , 'MAßßtab') ; ERROR: invalid UTF-8 byte sequence detected near byte 0xdf Here are my object/statement definitions : A) PREPARE stmt( int, int, int, varchar) as insert INTO part values ($1,$2,$3,$4); B) postgres=# \d+ part Table "public.part" Column | Type | Modifiers | Description ++---+- id1| integer| not null | id2| integer| not null | id3| integer| not null | filler | character varying(200) | | C) postgres=# l\l List of databases Name| Owner | Encoding +---+--- db1| user1 | SQL_ASCII postgres | pg| UTF8 template0 | pg| UTF8 template1 | pg| UTF8 How to solve my problem ? Best Regards. Milen ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] UTF-8 Problem ?
On Thu, Jun 15, 2006 at 01:01:56PM +0200, Milen Kulev wrote: > postgres=# EXECUTE stmt (1, 1 , 1 , 'Grün') ; > ERROR: invalid UTF-8 byte sequence detected near byte 0xfc > > Or > > postgres=# EXECUTE stmt (1, 1 , 1 , 'MAßßtab') ; > ERROR: invalid UTF-8 byte sequence detected near byte 0xdf Sounds like your client is sending something other than UTF-8. Is it? A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] UTF-8 Problem ?
On Jun 15 01:01, Milen Kulev wrote: > I want to insert some german specific characters (umlaut characters) > into a table, but I am getting the following > Error message: > postgres=# EXECUTE stmt (1, 1 , 1 , 'Grün') ; > ERROR: invalid UTF-8 byte sequence detected near byte 0xfc > ... > postgres=# l\l >List of databases > Name| Owner | Encoding > +---+--- > db1| user1 | SQL_ASCII > postgres | pg| UTF8 > template0 | pg| UTF8 > template1 | pg| UTF8 Did you set your client_encoding properly too? (Also, assuming that your terminal supports the related client encoding.) Regards. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] UTF-8 Problem ?
Hi Thomas, What actually the compile option --enable-recode is doing ? I haven't compiled PG with this option for sure (perhaps is the option On by defalt ?), but oyu advice hepled me: postgres=# \encoding UTF8 postgres=# \encoding UTF8 postgres=# SET client_encoding = 'LATIN1'; SET postgres=# \encoding LATIN1 postgres=# PREPARE stmt( int, int, int, varchar) as insert INTO part values ($1,$2,$3,$4); PREPARE postgres=# EXECUTE stmt (1, 1 , 1 , 'MAßßtab') ; INSERT 0 0 postgres=# EXECUTE stmt (1, 1 , 1 , 'MAßßtab') ; INSERT 0 0 postgres=# EXECUTE stmt (1, 1 , 1 , 'Grün') ; INSERT 0 0 postgres=# postgres=# SELECT filler from part where filler like 'MA%' or filler like 'Gr%' ; filler - MAßßtab MAßßtab Grün (3 rows) Regards. Milen -Original Message- From: Thomas Beutin [mailto:[EMAIL PROTECTED] Sent: Thursday, June 15, 2006 2:45 PM To: pgsql-sql@postgresql.org Cc: Milen Kulev Subject: Re: [SQL] UTF-8 Problem ? Hi Milen, Milen Kulev wrote: > Hi Listers, > I want to insert some german specific characters (umlaut characters) > into a table, but I am getting the following > Error message: > postgres=# EXECUTE stmt (1, 1 , 1 , 'Grün') ; > ERROR: invalid UTF-8 byte sequence detected near byte 0xfc > > Or > > postgres=# EXECUTE stmt (1, 1 , 1 , 'MAßßtab') ; > ERROR: invalid UTF-8 byte sequence detected near byte 0xdf > > Here are my object/statement definitions : > > A) PREPARE stmt( int, int, int, varchar) as insert INTO part values > ($1,$2,$3,$4); > > B) > postgres=# \d+ part > Table "public.part" > Column | Type | Modifiers | Description > ++---+- > id1| integer| not null | > id2| integer| not null | > id3| integer| not null | > filler | character varying(200) | | > > C) > > postgres=# l\l >List of databases > Name| Owner | Encoding > +---+--- > db1| user1 | SQL_ASCII > postgres | pg| UTF8 > template0 | pg| UTF8 > template1 | pg| UTF8 > > > How to solve my problem ? You should insert only correct utf8 strings or set the client encoding correctly: SET client_encoding = 'LATIN1'; or SET client_encoding = 'LATIN9'; IIRC postgresql must be compiled with --enable-recode to support this. Regards, -tb ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] UTF-8 Problem ?
Hi Milen, Milen Kulev wrote: > Hi Listers, > I want to insert some german specific characters (umlaut characters) into a > table, but I am getting the following > Error message: > postgres=# EXECUTE stmt (1, 1 , 1 , 'Grün') ; > ERROR: invalid UTF-8 byte sequence detected near byte 0xfc > > Or > > postgres=# EXECUTE stmt (1, 1 , 1 , 'MAßßtab') ; > ERROR: invalid UTF-8 byte sequence detected near byte 0xdf > > Here are my object/statement definitions : > > A) PREPARE stmt( int, int, int, varchar) as insert INTO part values > ($1,$2,$3,$4); > > B) > postgres=# \d+ part > Table "public.part" > Column | Type | Modifiers | Description > ++---+- > id1| integer| not null | > id2| integer| not null | > id3| integer| not null | > filler | character varying(200) | | > > C) > > postgres=# l\l >List of databases > Name| Owner | Encoding > +---+--- > db1| user1 | SQL_ASCII > postgres | pg| UTF8 > template0 | pg| UTF8 > template1 | pg| UTF8 > > > How to solve my problem ? You should insert only correct utf8 strings or set the client encoding correctly: SET client_encoding = 'LATIN1'; or SET client_encoding = 'LATIN9'; IIRC postgresql must be compiled with --enable-recode to support this. Regards, -tb ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] UTF-8 Problem ?
Hi Milen, Milen Kulev wrote: > What actually the compile option --enable-recode is doing ? IIRC it enables the support for string recoding, but this might not be correct anymore ... > I haven't compiled PG with this option for sure (perhaps is the option > On by defalt ?), but oyu advice hepled me: [...] You're welcome :) Regards, -tb > -Original Message- > From: Thomas Beutin [mailto:[EMAIL PROTECTED] > Sent: Thursday, June 15, 2006 2:45 PM > To: pgsql-sql@postgresql.org > Cc: Milen Kulev > Subject: Re: [SQL] UTF-8 Problem ? > > > Hi Milen, > > Milen Kulev wrote: >> Hi Listers, >> I want to insert some german specific characters (umlaut characters) >> into a table, but I am getting the following >> Error message: >> postgres=# EXECUTE stmt (1, 1 , 1 , 'Grün') ; >> ERROR: invalid UTF-8 byte sequence detected near byte 0xfc >> >> Or >> >> postgres=# EXECUTE stmt (1, 1 , 1 , 'MAßßtab') ; >> ERROR: invalid UTF-8 byte sequence detected near byte 0xdf >> >> Here are my object/statement definitions : >> >> A) PREPARE stmt( int, int, int, varchar) as insert INTO part values >> ($1,$2,$3,$4); >> >> B) >> postgres=# \d+ part >> Table "public.part" >> Column | Type | Modifiers | Description >> ++---+- >> id1| integer| not null | >> id2| integer| not null | >> id3| integer| not null | >> filler | character varying(200) | | >> >> C) >> >> postgres=# l\l >>List of databases >> Name| Owner | Encoding >> +---+--- >> db1| user1 | SQL_ASCII >> postgres | pg| UTF8 >> template0 | pg| UTF8 >> template1 | pg| UTF8 >> >> >> How to solve my problem ? > > You should insert only correct utf8 strings or set the client encoding > correctly: > SET client_encoding = 'LATIN1'; > or > SET client_encoding = 'LATIN9'; > > IIRC postgresql must be compiled with --enable-recode to support this. > > Regards, > -tb > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] UTF-8 Problem ?
Thomas Beutin <[EMAIL PROTECTED]> writes: > Milen Kulev wrote: >> What actually the compile option --enable-recode is doing ? > IIRC it enables the support for string recoding, but this might not be > correct anymore ... --enable-recode has been gone for a long time (a quick look shows it was last present in 7.3), and even then it didn't have anything to do with support for multibyte encodings like UTF8. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] listen_addresses = '*' ok, specific address(es) no
I'm running PostgreSQL 8.1.3. In my postgresql.conf, the following works: listen_addresses = '*' but the following does not: listen_addresses = '192.168.1.33' I get an error: WARNING: could not create listen socket for "192.168.1.33" FATAL: could not create any TCP/IP sockets I'm running Mac OS X 10.4.6 on PPC, if that makes a difference. For now, listen_addresses = '*' works for me, but I was curious why I couldn't use the more restrictive listen_addresses. Geoffrey -- Geoffrey S. Knauth | http://knauth.org/gsk ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] listen_addresses = '*' ok, specific address(es) no
On Thu, Jun 15, 2006 at 10:05:26AM -0400, Geoffrey Knauth wrote: > I get an error: >WARNING: could not create listen socket for "192.168.1.33" >FATAL: could not create any TCP/IP sockets > > I'm running Mac OS X 10.4.6 on PPC, if that makes a difference. Well, do you actually have an interface with that address? A -- Andrew Sullivan | [EMAIL PROTECTED] "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] listen_addresses = '*' ok, specific address(es) no
Geoffrey Knauth <[EMAIL PROTECTED]> writes: > I'm running PostgreSQL 8.1.3. In my postgresql.conf, the following > works: > listen_addresses = '*' > but the following does not: > listen_addresses = '192.168.1.33' > I get an error: > WARNING: could not create listen socket for "192.168.1.33" > FATAL: could not create any TCP/IP sockets There should be more info than that --- AFAICS all the failure paths in that code emit LOG messages. Perhaps you have log_min_messages set too high to allow the info to come out? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] sessions and prepared statements
in PHP for example, where there are multiple sessions and which you get is random:how do you know if the session you're in has prepared a particular statement?and/or how do you get a list of prepared statements?last, is there any after login trigger that one could use to prepare statements the session would need? or is this a dumb idea?thankschester __Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [SQL] UTF-8 Problem ?
Hello, Db-encoding LATIN1 works fine for me with german, scandic, other umlauted or accented and even cyrillic characters. BR, Aarni On Thursday 15 June 2006 14:01, Milen Kulev wrote: > Hi Listers, > I want to insert some german specific characters (umlaut characters) into a > table, but I am getting the following Error message: > postgres=# EXECUTE stmt (1, 1 , 1 , 'Grün') ; > ERROR: invalid UTF-8 byte sequence detected near byte 0xfc > > Or > > postgres=# EXECUTE stmt (1, 1 , 1 , 'MAßßtab') ; > ERROR: invalid UTF-8 byte sequence detected near byte 0xdf > > Here are my object/statement definitions : > > A) PREPARE stmt( int, int, int, varchar) as insert INTO part values > ($1,$2,$3,$4); > > B) > postgres=# \d+ part > Table "public.part" > Column | Type | Modifiers | Description > ++---+- > id1| integer| not null | > id2| integer| not null | > id3| integer| not null | > filler | character varying(200) | | > > C) > > postgres=# l\l >List of databases > Name| Owner | Encoding > +---+--- > db1| user1 | SQL_ASCII > postgres | pg| UTF8 > template0 | pg| UTF8 > template1 | pg| UTF8 > > > How to solve my problem ? > > Best Regards. Milen > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster -- Aarni Ruuhimäki Megative Tmi Pääsintie 26 45100 Kouvola Finland +358-5-3755035 +358-50-4910037 www.kymi.com | cfm.kymi.com -- This is a bugfree broadcast to you from **Kmail** on **Fedora Core** linux system -- Linux is like a wigwam - no windows, no gates and a free apache inside. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] SQL Technique Question
i frequently join certain tables together in various tables. view the following link for an example: http://www.rafb.net/paste/results/mBvzn950.html is it a good practice to leave this included in the queries, as is, or should i factor it out somehow? if i should factor it, how do i do so? tia... __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] SQL Technique Question
On Thu, Jun 15, 2006 at 01:59:22PM -0700, [EMAIL PROTECTED] wrote: > > is it a good practice to leave this included in the > queries, as is, or should i factor it out somehow? if > i should factor it, how do i do so? If what you're saying is that these additional criteria are redundant, then it's up to you: what do you want to optimise for? If you're protecting against future errors, then the additional criteria might help. If you're protecting against having to write your code to produce a more efficient query, you should weigh the cost and benefit (which benefit includes "easier to debug queries"). There is a probably non-zero cost to the extra joins. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL Technique Question
On Thu, 2006-06-15 at 13:59 -0700, [EMAIL PROTECTED] wrote: > i frequently join certain tables together in various > tables. > is it a good practice to leave this included in the > queries, as is, or should i factor it out somehow? if > i should factor it, how do i do so? Future proofing selects queries is difficult because they never throw errors. They just give different results. What you really want is an ASSERTION to disallow bad entries from being created in the first place but PostgreSQL doesn't do those yet. Yes, you can do it with triggers but those are annoying to create by the hundreds for development only purposes. I would tend to add all of the columns to select be selected out and have assertions in the code. This way you can detect incorrect values in development and disable those safety's for production. select t_inspect_result.inspect_result_pass , t_inspect.serial_number_id , t_serial_number.serial_number_id ... assert(t_inspect.serial_number_id = t_serial_number.serial_number_id); -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL Technique Question
On Jun 16, 2006, at 5:59 , <[EMAIL PROTECTED]> wrote: i frequently join certain tables together in various tables. view the following link for an example: http://www.rafb.net/paste/results/mBvzn950.html is it a good practice to leave this included in the queries, as is, or should i factor it out somehow? if i should factor it, how do i do so? I'm not quite sure what you're getting at in your message (in particular, what is the "this" in "leave this included in the queries"?), and it looks like I have a completely different interpretation of what you're asking, looking at the responses you've already received from Andrew and Rod. I think you're asking about encapsulation and how to efficiently use code, rather than copying and pasting the basic query and then modifying a small portion of it. Based on that interpretation, here's what I'd do. (If I'm wrong, well, then, oh well.) I'd create a view that contains the common code. CREATE VIEW t_inspect_join_view AS SELECT t_inspect_result.inspect_result_pass , t_inspect_result.inspect_result_timestamp , t_product.product_number , t_inspect_result.inspect_result_id FROM t_inspect_result, t_inspect, t_inspect_area, t_serial_number, t_link_contract_number_job_number, t_job_number, t_product WHERE t_inspect_result.inspect_id = t_inspect.inspect_id AND t_inspect.serial_number_id = t_serial_number.serial_number_id AND t_serial_number.link_contract_number_job_number_id = t_link_contract_number_job_number.link_contract_number_job_number_id AND t_link_contract_number_job_number.job_number_id = t_job_number.job_number_id AND t_inspect.inspect_area_id = t_inspect_area.inspect_area_id AND t_product.product_id = t_job_number.product_id; or in a style I find a bit clearer: CREATE VIEW t_inspect_join_view AS SELECT t_inspect_result.inspect_result_pass , t_inspect_result.inspect_result_timestamp , t_product.product_number , t_inspect_result.inspect_result_id FROM t_inspect_result JOIN t_inspect USING (inspect_id) JOIN t_serial_number USING (serial_number_id) JOIN t_link_contract_number_job_number USING (link_contract_number_job_number_id) JOIN t_inspect_area USING (inspect_area_id) JOIN t_job_number USING (job_number_id) JOIN t_product USING (product_id); One of the reasons I like this style is that it makes it easy to see that all of the tables in the FROM clause have join conditions (which is usually what you want). For example, it looks like you probably want the AND t_inspect.inspect_area_id = t_inspect_area.inspect_area_id part of the WHERE clause in your "repeating code" section, so I've added it to the view. With the JOIN conditions (how tables are joined together) now part of the FROM clause, the WHERE clause can be used to list just restrictions restrictions (limiting what rows are returned). I find this much clearer to write and read, as I've got clear separation between these two things. While underneath it all the server might consider everything part of the WHERE clause, sytactically I find this style helpful. I've also added some more columns to the SELECT target list, as you'll want to have them exposed for the extra WHERE clause restrictions. Once part of the view, only columns listed in the SELECT target list will be accessible outside of the view. You may have other restrictions that you want to apply in different cases, so you may want to add more columns to the target list. So your original query, using this view, would look like: SELECT inspect_result_pass FROM t_inspect_join_view WHERE product_number = '7214118000' AND inspect_result_timestamp > '2006-01-01' AND inspect_result_timestamp < '2006-06-13' AND inspect_result_id IN ... Hope this helps. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] SQL Technique Question
> > On Jun 16, 2006, at 5:59 , > <[EMAIL PROTECTED]> wrote: > > > i frequently join certain tables together in > various > > tables. > > > > view the following link for an example: > > > > http://www.rafb.net/paste/results/mBvzn950.html > > > > is it a good practice to leave this included in > the > > queries, as is, or should i factor it out somehow? > if > > i should factor it, how do i do so? > > I'm not quite sure what you're getting at in your > message (in > particular, what is the "this" in "leave this > included in the > queries"?), and it looks like I have a completely > different > interpretation of what you're asking, looking at the > responses you've > already received from Andrew and Rod. I think you're > asking about > encapsulation and how to efficiently use code, > rather than copying > and pasting the basic query and then modifying a > small portion of it. > Based on that interpretation, here's what I'd do. > (If I'm wrong, > well, then, oh well.) > > I'd create a view that contains the common code. > > CREATE VIEW t_inspect_join_view AS > > SELECT t_inspect_result.inspect_result_pass > , t_inspect_result.inspect_result_timestamp > , t_product.product_number > , t_inspect_result.inspect_result_id > FROM t_inspect_result, t_inspect, t_inspect_area, > t_serial_number, > t_link_contract_number_job_number, > t_job_number, t_product > WHERE t_inspect_result.inspect_id = > t_inspect.inspect_id > AND t_inspect.serial_number_id = > t_serial_number.serial_number_id > AND > t_serial_number.link_contract_number_job_number_id = > > t_link_contract_number_job_number.link_contract_number_job_number_id > AND > t_link_contract_number_job_number.job_number_id = > t_job_number.job_number_id > AND t_inspect.inspect_area_id = > t_inspect_area.inspect_area_id > AND t_product.product_id = > t_job_number.product_id; > > or in a style I find a bit clearer: > > CREATE VIEW t_inspect_join_view AS > > SELECT t_inspect_result.inspect_result_pass > , t_inspect_result.inspect_result_timestamp > , t_product.product_number > , t_inspect_result.inspect_result_id > FROM t_inspect_result > JOIN t_inspect USING (inspect_id) > JOIN t_serial_number USING (serial_number_id) > JOIN t_link_contract_number_job_number > USING (link_contract_number_job_number_id) > JOIN t_inspect_area USING (inspect_area_id) > JOIN t_job_number USING (job_number_id) > JOIN t_product USING (product_id); > > One of the reasons I like this style is that it > makes it easy to see > that all of the tables in the FROM clause have join > conditions (which > is usually what you want). For example, it looks > like you probably > want the > >AND t_inspect.inspect_area_id = > t_inspect_area.inspect_area_id > > part of the WHERE clause in your "repeating code" > section, so I've > added it to the view. With the JOIN conditions (how > tables are joined > together) now part of the FROM clause, the WHERE > clause can be used > to list just restrictions restrictions (limiting > what rows are > returned). I find this much clearer to write and > read, as I've got > clear separation between these two things. While > underneath it all > the server might consider everything part of the > WHERE clause, > sytactically I find this style helpful. > > I've also added some more columns to the SELECT > target list, as > you'll want to have them exposed for the extra WHERE > clause > restrictions. Once part of the view, only columns > listed in the > SELECT target list will be accessible outside of the > view. You may > have other restrictions that you want to apply in > different cases, so > you may want to add more columns to the target list. > > So your original query, using this view, would look > like: > > SELECT inspect_result_pass > FROM t_inspect_join_view > WHERE product_number = '7214118000' >AND inspect_result_timestamp > '2006-01-01' >AND inspect_result_timestamp < '2006-06-13' >AND inspect_result_id IN ... > > Hope this helps. > > Michael Glaesemann > grzm seespotcode net Michael, this is the answer to my question. i have to read up on the other answers to see if i can learn something to incorporate into my programming. i've just had a bear of a time trying to keep everything straight... okay, i want to know what inspects are associated with p/n 123, s/n 1... or what is the p/n associated with p/n 456? i had to troll through all my relations to get at results. i need to become mor familiar with views. if i use views, will i substantially impair performance? thanks for the answer... the end result sure sure looks clean. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner wi
Re: [SQL] SQL Technique Question
> On Thu, Jun 15, 2006 at 01:59:22PM -0700, > [EMAIL PROTECTED] wrote: > > > > is it a good practice to leave this included in > the > > queries, as is, or should i factor it out somehow? > if > > i should factor it, how do i do so? > > If what you're saying is that these additional > criteria are > redundant, then it's up to you: what do you want to > optimise for? If > you're protecting against future errors, then the > additional > criteria might help. If you're protecting against > having to write > your code to produce a more efficient query, you > should weigh the > cost and benefit (which benefit includes "easier to > debug queries"). > There is a probably non-zero cost to the extra > joins. Andrew and Rod, my apologies for not being more clear in my question. all the code is required to get from t_inspect_result data back to t_product information. however, many of the joins are used over and over and over - making for a complex query to view and try and to debug - not to mention forcing a long trail of chasing linked data to get from t_inspect_result_id back to the linked t_product data. Thanks for taking the time to address the question - and i will try and be more clear going forward. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] SQL Technique Question
[Please quote responsibly. There was no need to quote my entire message back to the list.] On Jun 16, 2006, at 7:15 , <[EMAIL PROTECTED]> wrote: i need to become mor familiar with views. if i use views, will i substantially impair performance? Short answer: maybe, maybe not Long answer: benchmark and compare. EXPLAIN ANALYZE is your friend. For example, compare the EXPLAIN ANALYZE output using the view and using the whole, explicit query. You'll learn a lot that will only help you write better queries. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Repetitive code
Hi,
This is prompted by the previous thread on "SQL Technique Question". I
have the following query, extracted from a PHP script, where $dt is a
date provided to the script.
SELECT created, topic_id, 0, 0, 0, 0 FROM topic
WHERE created >= $dt AND page_type IN (1, 2)
UNION
SELECT updated, topic_id, 1, 0, 0, 0 FROM topic
WHERE date_trunc('day', updated) != created
AND updated >= $dt AND page_type IN (1, 2)
UNION
SELECT e.created, subject_id, 0, 1, entry_id, subject_type
FROM entry e, topic
WHERE subject_id = topic_id AND e.created >= $dt
AND page_type IN (1, 2)
UNION
SELECT e.created, actor_id, 0, 1, entry_id, actor_type
FROM entry e, topic
WHERE actor_id = topic_id AND e.created >= $dt
AND page_type IN (1, 2)
UNION
SELECT e.updated, subject_id, 1, 1, entry_id, subject_type
FROM entry e, topic
WHERE date_trunc('day', e.updated) != e.created
AND subject_id = topic_id AND e.updated >= $dt
AND page_type IN (1, 2)
UNION
SELECT e.updated, actor_id, 1, 1, entry_id, actor_type
FROM entry e, topic
WHERE date_trunc('day', e.updated) != e.created
AND actor_id = topic_id AND e.updated >= $dt
AND page_type IN (1, 2)
UNION
SELECT e.created, e.topic_id, 0, 1, entry_id, rel_type
FROM topic_entry e, topic t
WHERE e.topic_id = t.topic_id AND e.created >= $dt
AND page_type IN (1, 2)
UNION
SELECT e.updated, e.topic_id, 1, 1, entry_id, rel_type
FROM topic_entry e, topic t
WHERE e.topic_id = t.topic_id
AND date_trunc('day', e.updated) != e.created
AND e.updated >= $dt AND page_type IN (1, 2);
As you can see, there's quite a bit of repetitive code, so the previous
thread got me to thinking about simplifying it, perhaps through a view,
perhaps through the use of CASE statements, particularly since I'm about
to add at least one other table to the mix.
As background, each table has a 'created' date column and an 'updated'
timestamp column and the purpose of the various selects is to find the
rows that were created or updated since the given $dt date. The third
expression in each select list is an indicator of NEW (0) or CHANGED
(1). The fourth item is a code for row type (topic=0, entry=1, but a
new code is coming).
I've been trying to figure out if simplifying into a view (one or more)
is indeed possible. One factoring out that I can see is the "topics of
interest" restriction (i.e., the join of each secondary table back to
topic to get only topics whose page_types are 1 or 2). Another
redundancy is the "date_trunc('day', updated) != created" which is there
to avoid selecting "changed" records when they're actually new.
However, although creating these views may simplify the subqueries it
doesn't seem there is a way to avoid the eight-way UNION, or is there?
TIA
Joe
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Repetitive code
Each of your queries has the filter xxx >= $dt where the xxx is the first column in each select. You could simplify the query by turning the unioned selects into a sub-query and then putting the $dt filter in the outer query.
I don't know if this will cause performance problems though. If PostgreSQL completes the inner query before filtering by your $dt you may be better off leaving the $dt filters where they are.I know Oracle has materialized views. Does PostgreSQL also have materialized views? If so, you could get great performance from your views AND simplify your SQL.
-Aaron BonoOn 6/15/06, Joe <[EMAIL PROTECTED]> wrote:
Hi,This is prompted by the previous thread on "SQL Technique Question". Ihave the following query, extracted from a PHP script, where $dt is adate provided to the script. SELECT created, topic_id, 0, 0, 0, 0 FROM topic
WHERE created >= $dt AND page_type IN (1, 2) UNION SELECT updated, topic_id, 1, 0, 0, 0 FROM topic WHERE date_trunc('day', updated) != created AND updated >= $dt AND page_type IN (1, 2)
UNION SELECT e.created, subject_id, 0, 1, entry_id, subject_type FROM entry e, topic WHERE subject_id = topic_id AND e.created >= $dt AND page_type IN (1, 2) UNION SELECT
e.created, actor_id, 0, 1, entry_id, actor_type FROM entry e, topic WHERE actor_id = topic_id AND e.created >= $dt AND page_type IN (1, 2) UNION SELECT e.updated, subject_id, 1, 1, entry_id, subject_type
FROM entry e, topic WHERE date_trunc('day', e.updated) != e.created AND subject_id = topic_id AND e.updated >= $dt AND page_type IN (1, 2) UNION SELECT e.updated, actor_id, 1, 1, entry_id, actor_type
FROM entry e, topic WHERE date_trunc('day', e.updated) != e.created AND actor_id = topic_id AND e.updated >= $dt AND page_type IN (1, 2) UNION SELECT e.created, e.topic_id
, 0, 1, entry_id, rel_type FROM topic_entry e, topic t WHERE e.topic_id = t.topic_id AND e.created >= $dt AND page_type IN (1, 2) UNION SELECT e.updated, e.topic_id, 1, 1, entry_id, rel_type
FROM topic_entry e, topic t WHERE e.topic_id = t.topic_id AND date_trunc('day', e.updated) != e.created AND e.updated >= $dt AND page_type IN (1, 2);As you can see, there's quite a bit of repetitive code, so the previous
thread got me to thinking about simplifying it, perhaps through a view,perhaps through the use of CASE statements, particularly since I'm aboutto add at least one other table to the mix.As background, each table has a 'created' date column and an 'updated'
timestamp column and the purpose of the various selects is to find therows that were created or updated since the given $dt date. The third_expression_ in each select list is an indicator of NEW (0) or CHANGED
(1). The fourth item is a code for row type (topic=0, entry=1, but anew code is coming).I've been trying to figure out if simplifying into a view (one or more)is indeed possible. One factoring out that I can see is the "topics of
interest" restriction (i.e., the join of each secondary table back totopic to get only topics whose page_types are 1 or 2). Anotherredundancy is the "date_trunc('day', updated) != created" which is there
to avoid selecting "changed" records when they're actually new.However, although creating these views may simplify the subqueries itdoesn't seem there is a way to avoid the eight-way UNION, or is there?
TIAJoe
Re: [SQL] listen_addresses = '*' ok, specific address(es) no
Tom, I omitted the LOG and HINT lines before. LOG: could not bind IPv4 socket: Can't assign requested address HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for "192.168.1.33" FATAL: could not create any TCP/IP sockets This works fine if I use '*' instead of '192.168.1.33'. Andrew Sullivan wrote: Well, do you actually have an interface with that address? I think I do, in that the machine's wireless interface is set up with a 192.168.1.x/24 address and 1.33 is on the same subnet. Or maybe I'm misunderstanding. I thought the purpose of listen_addresses was to allowing incoming connections only from listed addresses. Geoff On Jun 15, 2006, at 10:40, Tom Lane wrote: Geoffrey Knauth <[EMAIL PROTECTED]> writes: I'm running PostgreSQL 8.1.3. In my postgresql.conf, the following works: listen_addresses = '*' but the following does not: listen_addresses = '192.168.1.33' I get an error: WARNING: could not create listen socket for "192.168.1.33" FATAL: could not create any TCP/IP sockets There should be more info than that --- AFAICS all the failure paths in that code emit LOG messages. Perhaps you have log_min_messages set too high to allow the info to come out? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] listen_addresses = '*' ok, specific address(es) no
Geoffrey Knauth <[EMAIL PROTECTED]> writes: > Andrew Sullivan wrote: >> Well, do you actually have an interface with that address? > I think I do, in that the machine's wireless interface is set up with > a 192.168.1.x/24 address and 1.33 is on the same subnet. Or maybe > I'm misunderstanding. I thought the purpose of listen_addresses was > to allowing incoming connections only from listed addresses. You're misunderstanding then. What listen_addresses can bind to is IP addresses of *your own machine*. For example, if you bind to only 127.0.0.1 then only local loopback connections will work. Binding to just one external IP address is only interesting if your machine has more than one such address; then it prevents connections that're coming in through one of the other addresses. The right way to limit incoming connections to only come *from* particular IP addresses is to use pg_hba.conf. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] listen_addresses = '*' ok, specific address(es) no (.... and a thread hi-jack!)
Hi Geoff, Listen_addresses means what local interface to listen to connections - ie, if you have 2 network interfaces (cards) in the machine that go to 2 different networks - such as one to the internet and one to your LAN, you could tell Postgres to only listen on the LAN interface for connections so it won't accept connections from anything on the internet. What you're after would be better done by a firewall (ipchains / iptables) I've just installed Postgres 8.1 on RedHat 7.1 and I'm getting the error: "2006-06-16 14:49:00 NZST @ []LOG: could not create IPv6 socket: Address family not supported by protocol" RedHat 7.1 does not support IPv6, but I don't need it - how can I disable it? I've set my listen_addresses to: listen_addresses = '172.23.0.1' Yes, I do have a local address 172.23.0.1 as per output from ifconfig: [EMAIL PROTECTED] pgsql]$ ifconfig eth0 eth0 Link encap:Ethernet HWaddr 00:20:ED:38:EB:F4 inet addr:172.23.0.1 Bcast:172.23.255.255 Mask:255.255.0.0 UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:2548578 errors:0 dropped:0 overruns:0 frame:0 TX packets:2479774 errors:0 dropped:0 overruns:1 carrier:0 collisions:0 txqueuelen:100 Interrupt:18 Base address:0xe000 Memory:e0998000-e0998c40 Cheers, -p -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Geoffrey Knauth Sent: Friday, 16 June 2006 12:06 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] listen_addresses = '*' ok, specific address(es) no Tom, I omitted the LOG and HINT lines before. LOG: could not bind IPv4 socket: Can't assign requested address HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for "192.168.1.33" FATAL: could not create any TCP/IP sockets This works fine if I use '*' instead of '192.168.1.33'. Andrew Sullivan wrote: > Well, do you actually have an interface with that address? I think I do, in that the machine's wireless interface is set up with a 192.168.1.x/24 address and 1.33 is on the same subnet. Or maybe I'm misunderstanding. I thought the purpose of listen_addresses was to allowing incoming connections only from listed addresses. Geoff On Jun 15, 2006, at 10:40, Tom Lane wrote: > Geoffrey Knauth <[EMAIL PROTECTED]> writes: >> I'm running PostgreSQL 8.1.3. In my postgresql.conf, the following >> works: >> listen_addresses = '*' > >> but the following does not: >> listen_addresses = '192.168.1.33' > >> I get an error: >> WARNING: could not create listen socket for "192.168.1.33" >> FATAL: could not create any TCP/IP sockets > > There should be more info than that --- AFAICS all the failure > paths in > that code emit LOG messages. Perhaps you have log_min_messages set > too > high to allow the info to come out? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] listen_addresses = '*' ok, specific address(es) no
quote: "The right way to limit incoming connections to only come *from* particular IP addresses is to use pg_hba.conf." Apologies Geoff - that would be the easier way rather than ipchains / iptables. -p -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Friday, 16 June 2006 12:47 PM To: Geoffrey Knauth Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] listen_addresses = '*' ok, specific address(es) no Geoffrey Knauth <[EMAIL PROTECTED]> writes: > Andrew Sullivan wrote: >> Well, do you actually have an interface with that address? > I think I do, in that the machine's wireless interface is set up with > a 192.168.1.x/24 address and 1.33 is on the same subnet. Or maybe > I'm misunderstanding. I thought the purpose of listen_addresses was > to allowing incoming connections only from listed addresses. You're misunderstanding then. What listen_addresses can bind to is IP addresses of *your own machine*. For example, if you bind to only 127.0.0.1 then only local loopback connections will work. Binding to just one external IP address is only interesting if your machine has more than one such address; then it prevents connections that're coming in through one of the other addresses. The right way to limit incoming connections to only come *from* particular IP addresses is to use pg_hba.conf. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] listen_addresses = '*' ok, specific address(es) no (.... and a thread hi-jack!)
"Phillip Smith" <[EMAIL PROTECTED]> writes: > > I've just installed Postgres 8.1 on RedHat 7.1 Uh ... *why*? I wouldn't use RH 7.1 today any more than I'd use PG 7.1 ... > and I'm getting the error: > "2006-06-16 14:49:00 NZST @ []LOG: could not create IPv6 socket: Address > family not supported by protocol" It seems you've got libc code that supports IPv6 even though your kernel does not (else PG would not have been told that an IPv6 address was a possible translation of "localhost"). PG copes with this but will issue LOG messages complaining about it. If you don't like the warnings, fix your system so it's all on the same page about whether IPv6 is supported. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] listen_addresses = '*' ok, specific address(es) no (.... and a thread hi-jack!)
Dang - Our NZ operations are a lot smaller than ours. They only have the one Linux server and it's primary role is to run the D3 gear I was talking about the other day - same in Australia!! I am trying to get the number crunchers to fork out the $$$ for RH ES4 and a nice new server. Thanks Tom, I'll put up with the errors for now until I can get them a new server. -p -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Friday, 16 June 2006 1:04 PM To: [EMAIL PROTECTED] Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] listen_addresses = '*' ok, specific address(es) no ( and a thread hi-jack!) "Phillip Smith" <[EMAIL PROTECTED]> writes: > > I've just installed Postgres 8.1 on RedHat 7.1 Uh ... *why*? I wouldn't use RH 7.1 today any more than I'd use PG 7.1 ... > and I'm getting the error: > "2006-06-16 14:49:00 NZST @ []LOG: could not create IPv6 socket: Address > family not supported by protocol" It seems you've got libc code that supports IPv6 even though your kernel does not (else PG would not have been told that an IPv6 address was a possible translation of "localhost"). PG copes with this but will issue LOG messages complaining about it. If you don't like the warnings, fix your system so it's all on the same page about whether IPv6 is supported. regards, tom lane ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] concurrency problem
Dear group, Let me explain my issue. We use Database - postgresql-8.1 JDBC Driver - postgresql-8.1-407.jdbc3.jar Java - jdk1.5 The default transaction isolation level is - Read Committed
Auto Commit is false In our application we used a single connection object. We open the connection in the MDI form and close it only when the MDI closes , simply when the application closes. I give a insert statment like
INSERT INTO rcp_patient_visit_monitor ( entry_no, patient_id, visit_date, is_newpatient, visit_type, is_medical, is_review, is_labtest, is_scan, is_scopy, is_xray, weight, height)
VALUES ((SELECT coalesce(max(entry_no)+1, 1) FROM rcp_patient_visit_monitor),?,current_timestamp,?,?,?,?,?,?,?,?,?,?) The point to note here is the select statement which gets the max entry_no and adds one to it and save the new value. entry_no is the primary key of the above table.
Now i run the same program (different instance) from two systems, save the form simultaneously, only one entry is saved, in the other system the error says - duplicate key violates. If i use the transaction level - Serializable - again one entry is saved. Only on closing this application (closing the connection) the application running in other system is getting saved.
If i lock the table and create a transaction - by sending the commands con.createStatement().executeUpdate("begin"); con.createStatement().executeUpdate("lock table rcp_patient_visit_monitor");
int rows = psSave.executeUpdate(); con.createStatement().executeUpdate("commit"); The form in one system is saved, in another system an error says - ' Deadlock detected .'
When i test the above said commands in dbvisualizer from two different systems , it works, but here it does not. why. how to solve this concurrency problem.Thanks in advance,--
Sathish Kumar.SSpireTEK
Re: [SQL] concurrency problem
sathish kumar shanmugavelu wrote: INSERT INTO rcp_patient_visit_monitor ( entry_no, patient_id, visit_date, is_newpatient, visit_type, is_medical, is_review, is_labtest, is_scan, is_scopy, is_xray, weight, height) VALUES ((SELECT coalesce(max(entry_no)+1, 1) FROM rcp_patient_visit_monitor),?,current_timestamp,?,?,?,?,?,?,?,?,?,?) The point to note here is the select statement which gets the max entry_no and adds one to it and save the new value. entry_no is the primary key of the above table. Now i run the same program (different instance) from two systems, save the form simultaneously, only one entry is saved, in the other system the error says - duplicate key violates. BEGIN; LOCK TABLE ... INSERT ... COMMIT; You'll need to handle possible errors where one client fails to get a lock and times out. It won't happen often, but you do need to consider the option. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
