[SQL] Stripping white-space in SELECT statments
Hello, I've encountered a strange behavior in postgres 7.2.1 regarding how psql handles strings ending with space characters. If I want to search for records where the first column (artnrgrpmtrln_1) begins with '201901 ', our system that uses the database creates the following SQL statement: select artnrgrpmtrln_1 from sr where (artnrgrpmtrln_1>='201901 ' and artnrgrpmtrln_1<='201901 ÿ' The execution of this statement gives the following resultset, which I didn't expect. What I wanted was the first 8 records only. artnrgrpmtrln_1 -- 201901 00R18000 0035C2 201901 00R18005 0035C3 201901 00R18707 007593 201901 00R28541 0030D6 201901 00R40055 0030D8 201901 00R40277T 0030D7 201901 00S00406 0030D9 201901 00SA0200 003662 201901-D00R18000 0035C2 201901-D00R18005 0035C3 201901-D00R18702 008439 201901-D00R18707 007593 201901-D00R28541 0030D6 201901-D00R40055 0030D8 201901-D00R40277T 0030D7 201901-D00S00406 0030D9 201901-D00SA0200 003662 201901JW00R18000 0035C2 201901JW00R18005 0035C3 201901JW00R18707 007593 The Table description is below. Table "sr" Column | Type | Modifiers -+---+--- artnrgrpmtrln_1 | character(24) | mangd_2 | character(8) | enhet_3 | character(1) | text_4 | character(15) | start_5 | character(3) | lageruppd_6 | character(1) | materialnr_7| character(8) | opfoljd_8 | character(3) | lgst_9 | character(3) | Indexes: sr_materialnr_7 Unique keys: sr_artnrgrpmtrln_1 This behaviour seems to have changed since postgreSQL v. 7.2, since it works there. The reason that we don't use 'LIKE 201901 %' is that it don't use the index sr_artnrgrpmtrln_1 when doing the lookup. Is there anyone who can explain this behaviour? Could it be that the parser strips of the whitespaces in '201901 '? Best Regards, Tobbe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Stripping white-space in SELECT statments
> If I want to search for records where the first column (artnrgrpmtrln_1) > begins with > '201901 ', our system that uses the database creates the following SQL > statement: > > select artnrgrpmtrln_1 from sr where (artnrgrpmtrln_1>='201901 ' and > artnrgrpmtrln_1<='201901 ÿ' what does "our system" mean? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Stripping white-space in SELECT statments
By "our system" I mean the software that uses the database. It is a quit old software written in C that has been ported a couple of times to different *NIX platforms using different DBMS's. It uses in-house developed functions that, depending on parameters, creates a variety of SQL statements that in the end gets executed by PQexec (from libpq i guess). > -Ursprungligt meddelande- > Från: dima [mailto:[EMAIL PROTECTED]] > Skickat: den 19 september 2002 13:08 > Till: [EMAIL PROTECTED] > Kopia: [EMAIL PROTECTED] > Ämne: Re: [SQL] Stripping white-space in SELECT statments > > > > If I want to search for records where the first column (artnrgrpmtrln_1) > > begins with > > '201901 ', our system that uses the database creates the following SQL > > statement: > > > > select artnrgrpmtrln_1 from sr where (artnrgrpmtrln_1>='201901 ' and > > artnrgrpmtrln_1<='201901 ÿ' > what does "our system" mean? > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] How To Get Bytea Data Instead Of Its Oid
Greetings! I must have missed lesson 1 of postgresql! create table test (c1 text,c2 bytea); Then, I probably have successfullyi nserted several records into test using C++Builder. Now I am trying to retrieve back the binary data in c2 I just inserted. However, the SQL statement issued by both C++Builder and psql: SELECT * FROM test LIMIT 1 returns, I guess, the OID of c2 instead of the binary data to which the OID points. What is the correct SQL syntax for C++Builder for bytea? Regards, -- ___ Get your free email from http://www.graffiti.net Powered by Outblaze ---(end of broadcast)--- TIP 3: 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] Index usage on date feild , differences in '>' and '>='
On Thu, 19 Sep 2002, Rajesh Kumar Mallah. wrote: > Thanks very much for the response. > set enable_seqscan=off; Definitely helps. > and for wide date ranges it usees indexes. > > > But with default value of enable_sequence changing date range seems to have effect. > can you explain me a bit more or point me to right documents for understanding > the languae of "EXPLAIN". > > EXPLAIN > tradein_clients=> explain select list_id from eyp_rfi a where generated between >'2002-09-11' and '2002-09-19' ; > NOTICE: QUERY PLAN: > > Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=12924 width=4) > > EXPLAIN > tradein_clients=> explain select list_id from eyp_rfi a where generated between >'2002-09-12' and '2002-09-19' ; > NOTICE: QUERY PLAN: > > Index Scan using eyp_rfi_date on eyp_rfi a (cost=0.00..17369.05 rows=12220 width=4) > > EXPLAIN Note the cost and row estimates for the two queries and for the first query with and without enable_seqscan=off; My guess is that if you do an explain with it off, you'll get a cost number >17923.81 which is why it's picking the seq scan, becaust it's guessing that it'll be faster. The row count seems off by a factor of 2 from the numbers below, have you analyzed recently and how many rows are in the table as a whole? Also, I'm guessing that your data is probably well ordered in relation to those dates as opposed to truly random which may throw off the estimates as well, what version are you using? set enable_seqscan=off is kinda dangerous since sometimes the seq scan is actually faster. > the distribution of values are as follows: > > select generated ,count(generated) from eyp_rfi a where generated between >'2002-09-10' and '2002-09-19' group by generated; > > > generated | count > +--- > 2002-09-10 | 442 > 2002-09-11 | 1060 > 2002-09-12 | 641 > 2002-09-13 | 607 > 2002-09-14 | 1320 > 2002-09-15 | 521 > 2002-09-16 | 1474 > 2002-09-17 | 940 > 2002-09-18 | 1005 > 2002-09-19 | 178 > (10 rows) > > Last Question , Shud i do "enable_seqscan=off" in Application Level? I use Perl-DBI ---(end of broadcast)--- TIP 3: 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] Stripping white-space in SELECT statments
=?iso-8859-1?Q?Thorbj=F6rn_Eriksson?= <[EMAIL PROTECTED]> writes: > I've encountered a strange behavior in postgres 7.2.1 regarding how psql > handles strings ending with space characters. Perhaps you are running in a non-C locale? A lot of locales have sorting rules that are pretty weird about whitespace. > The reason that we don't use 'LIKE 201901 %' is that it don't use > the index This suggests strongly that you are in a non-C locale. Your external software seems to be emulating the standard LIKE-to-index optimization; which as you are now discovering, does not work with non-C sorting rules (so the system doesn't try to apply it). regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Stripping white-space in SELECT statments
On Thu, 19 Sep 2002, [iso-8859-1] Thorbjörn Eriksson wrote: > Hello, > > I've encountered a strange behavior in postgres 7.2.1 regarding how psql > handles strings ending with space characters. > What locale did you initdb with? Some locales on some systems have behavior like that. To test, you could try the unix sort command on data like: 201901 Z 201901-D 201901 D in the same locale and see what order you get (specifically where does that first line go - on my machine it goes to the end unless I explicitly choose a locale like "C") ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Table Copy.
> As on insert to table one I should get the same insert on table two. > As on delete to table one I should get the same delete on table two. > As on update to table one I should get the same update on table two. > > Can someone provide the examples i can study ? Look up 'CREATE TRIGGER' and 'CREATE RULE' in the postgres documentation. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Effective usage without unique key -- suggestion
Bhuvan A wrote: > Hi, > > I am using postgresql 7.2.1 on redhat linux 7.3 > > I have a table in very high production database which is circulating > several thousands of records per day ie.. count does not exceed several > thousands. Here for some technical reason i dont have unique key on this > table, but it should contain unique records. I know that without using > unique index it can be achieved in any of the following methods. > > Method 1 > > * check for the record. > * if exists update, else insert > > Method 2 > > * delete the record (trigger on before insert) > * insert the record > > So Can you please suggest the best among the above 2 methods? > > BTW, internals of the above 2 methods would be different. For example in > method 2, frequent deletion of records calls for vacuuming the database. > FYI, i does VACUUM ANALYZE every day. So Can you please suggest the best > among the above 2 methods which well suits me and to use postgres more > effectively? > Not really - internally update does pretty much the same thing as delete+insert - so, either way, you won't be able to get away from vacuum'ing it... I *think* the second method should be (a little) more effective... I hope, it helps... Dima ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Query Freeze
"alexandre :: aldeia digital" <[EMAIL PROTECTED]> writes: > I have 3 applications in windows and they > starts 3 postgres backends. > The 1st app. call the 2nd and this call the 3rd. > In the same place of the 3rd backend, the query freeze. > If I kill the second backend(or app.), the query is released... I think your second backend is holding a lock that the third one needs. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] help w/ constructing a SELECT
Greetings, Having a problem with a query. I would like to construct a query which will return a list of all contigs which are comprised of clones whose 'read' = 'x' (read can be either 'x' or 'y'). Details: A contig may be comprised of more than 1 clone, so in TABLE clone_contig, there may be multiple entries for a given contig as in: chlamy_est=> select * from clone_contig; clone_id | contig_id --+--- 9811 | 1 82214 | 1 127472 | 1 82213 | 1 112644 | 1 9810 | 1 81641 | 2 This SELECT returns contigs comprised of clones whose reads are either 'x' or 'y'. Somehow I need an intersect or NOT comparrison??? SELECT contig.assembly,contig.ace,contig.ver FROM clone JOIN clone_contig USING (clone_id) JOIN contig USING (contig_id) WHERE clone.read = 'x' ; Tables: CREATE TABLE clone_contig( clone_id INTEGER REFERENCES clone(clone_id) ON DELETE CASCADE, contig_id INTEGER REFERENCES contig(contig_id) ON DELETE CASCADE, UNIQUE(clone_id,contig_id) ); CREATE TABLE clone ( clone_id SERIAL PRIMARY KEY, project INTEGER REFERENCES library(project) NOT NULL, snip read CHAR(1) NOT NULL, snip UNIQUE (project,plate,row,col,read,ver) ); CREATE TABLE contig ( contig_id SERIAL PRIMARY KEY, assembly DATE NOT NULL, ace INTEGER NOT NULL, ver INTEGER NOT NULL, length INTEGER NOT NULL, seq TEXT NOT NULL, UNIQUE (assembly,ace,ver) ); CREATE TABLE clone_contig( clone_id INTEGER REFERENCES clone(clone_id) ON DELETE CASCADE, contig_id INTEGER REFERENCES contig(contig_id) ON DELETE CASCADE, UNIQUE(clone_id,contig_id) ); regards, Charles ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Stripping white-space in SELECT statments
--On jeudi 19 septembre 2002 13:20 +0200 Thorbjörn Eriksson <[EMAIL PROTECTED]> wrote: > Hello, > > I've encountered a strange behavior in postgres 7.2.1 regarding how psql > handles strings ending with space characters. > > If I want to search for records where the first column (artnrgrpmtrln_1) > begins with > '201901 ', our system that uses the database creates the following SQL > statement: > > select artnrgrpmtrln_1 from sr where (artnrgrpmtrln_1>='201901 ' and > artnrgrpmtrln_1<='201901 ÿ' and why not select artnrgrpmtrln_1 from sr where substr(artnrgrpmtrln_1, 0, 9) = '201901 ' -- Mathieu Arnold ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Table Copy.
HI! I'm new to postgres. I need to have a table as a copy of another one. Example: CREATE TABLE one ( fileda INTEGER, filedb INTEGER, filedc INTEGER ); CREATE TABLE two ( fileda INTEGER, filedb INTEGER, filedc INTEGER ); As on insert to table one I should get the same insert on table two. As on delete to table one I should get the same delete on table two. As on update to table one I should get the same update on table two. Can someone provide the examples i can study ? Thanks in advance. Alex ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How To Get Bytea Data Instead Of Its Oid
"CN LIOU" <[EMAIL PROTECTED]> writes:
> SELECT * FROM test LIMIT 1
> returns, I guess, the OID of c2 instead of the binary data to which the OID points.
I don't think so...
regression=# create table test (c1 text,c2 bytea);
CREATE TABLE
regression=# insert into test values ('some text', 'some binary data \\000\\001');
INSERT 284058 1
regression=# select * from test;
c1 |c2
---+---
some text | some binary data \000\001
(1 row)
I see no OID here.
Perhaps you should show us an example of what you are doing, rather than
your interpretation of what's going wrong.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Stripping white-space in SELECT statments
On Thursday 19 September 2002 13:41, Thorbjörn Eriksson wrote: > By "our system" I mean the software that uses the database. It is a quit > old software written in C that has been ported a couple of times to > different *NIX platforms using different DBMS's. It uses in-house developed > functions that, depending on parameters, creates a variety of SQL > statements that in the end gets executed by PQexec (from libpq i guess). [snip] > > > artnrgrpmtrln_1<='201901 ÿ' This looks like to me that your C software doesn't '\0' terminate its strings proparly and some random byte gets in the query. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Table Copy.
Alex wrote: > HI! > > I'm new to postgres. I need to have a table as a copy of another one. > > Example: > > CREATE TABLE one ( > fileda INTEGER, > filedb INTEGER, > filedc INTEGER ); > > CREATE TABLE two ( > fileda INTEGER, > filedb INTEGER, > filedc INTEGER ); > > As on insert to table one I should get the same insert on table two. > As on delete to table one I should get the same delete on table two. > As on update to table one I should get the same update on table two. > > Can someone provide the examples i can study ? You could do it with RULEs. Here is an example how you would do the DELETE. CREATE RULE copy_one_to_two AS ON DELETE TO one DO DELETE FROM two WHERE two.fileda=OLD.fileda AND two.filedb=OLD.filedb AND two.filedc=OLD.filedc; You should change the where clause if you have a primary key on that table. I am presuming fileda/filedb/filedc are unique in combination... Read the section of the docs about RULEs for INSERT and UPDATE examples. Regards, Michael Paesold ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] problem with query
Hi list, I need your help, I need a table or view with this information that show down,I have two tables "ticket" and "orden_respuesta" and I like this table. Note, that some tti for example (TTI0208) of ticket it isn't in the table orden_respuesta, if somone can help me i will thank full tti | numorden | tt | usuario | estado | tipo -+++-+--+-- TTI0206 | ORD0246, ORD0245 ..| 100029 | joroza | CERRADO | ESPECIFI TTI0207 | ORD0264, ORD0261 | 100051 | joroza | REVISION | ESPECIFI TTI0208 | (** nothing * )| 100049 | joroza | REVISION | IP TTI0209 | ORD0240| 56729 | joroza | CERRADO | FACTURA hb=# select tti, tt, usuario, estado, tipo from ticket; tti | tt | usuario | estado |tipo -++-+--+- TTI0206 | 100029 | joroza | CERRADO | ESPECIFICO TTI0207 | 100051 | joroza | REVISION | ESPECIFICO TTI0208 | 100049 | joroza | REVISION | IP TTI0209 | 56729 | joroza | CERRADO | FACTURACION hb=# select * from orden_respuesta ; numorden | numtti -+- ORD0246 | TTI0206 ORD0245 | TTI0206 ORD0244 | TTI0206 ORD0243 | TTI0206 ORD0242 | TTI0206 ORD0264 | TTI0207 ORD0261 | TTI0207 ORD0240 | TTI0209 Regards, Ricardo _ Charle con sus amigos online usando MSN Messenger: http://messenger.msn.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] problem with query
Hi list, First sorry if this message arrive in your box mail twice, I need your colaboration,I like a table or view with this information from 2 tables "ticket" and "orden_respuesta". Note, that the tti (TTI0208) doesn't have links to "numorden" in the table orden_respuesta, if someone can help me i will thankfull numtti | numorden | tt | usuario | estado | tipo -+---++-+--+- TTI0206 | ORD0246, ORD0245..| 100029 | joroza | CERRADO | ESPECIFI TTI0207 | ORD0264, ORD0261..| 100051 | joroza | REVISION | ESPECIFI TTI0208 | (***void***) | 100049 | joroza | REVISION | IP TTI0209 | ORD0240 | 56729 | joroza | CERRADO | FACTURA // table ticket// // hb=# select tti, tt, usuario, estado, tipo from ticket; tti | tt | usuario | estado |tipo -++-+--+- TTI0206 | 100029 | joroza | CERRADO | ESPECIFICO TTI0207 | 100051 | joroza | REVISION | ESPECIFICO TTI0208 | 100049 | joroza | REVISION | IP TTI0209 | 56729 | joroza | CERRADO | FACTURACION /// table orden_respuesta// /// hb=# select * from orden_respuesta ; numorden | numtti -+- ORD0246 | TTI0206 ORD0245 | TTI0206 ORD0244 | TTI0206 ORD0243 | TTI0206 ORD0242 | TTI0206 ORD0264 | TTI0207 ORD0261 | TTI0207 ORD0240 | TTI0209 regards, Ricardo _ Charle con sus amigos online usando MSN Messenger: http://messenger.msn.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL]
Hi list, I need your colaboration,I like a table or view with this information from 2 tables "ticket" and "orden_respuesta". Note, that the tti (TTI0208) doesn't have links or relations to "numorden" in the table orden_respuesta, if someone can help me i will thankfull numtti | numorden | tt | usuario | estado | ---+---++-+-+ TTI0206| ORD0246, ORD0245..| 100029 | joroza | CERRADO | TTI0207| ORD0264, ORD0261..| 100051 | joroza | REVISION| TTI0208| (***void***) | 100049 | joroza | REVISION| TTI0209| ORD0240 | 56729 | joroza | CERRADO | // table ticket// // hb=# select tti, tt, usuario, estado, tipo from ticket; tti | tt | usuario | estado | -++-+--+ TTI0206 | 100029 | joroza | CERRADO | TTI0207 | 100051 | joroza | REVISION | TTI0208 | 100049 | joroza | REVISION | TTI0209 | 56729 | joroza | CERRADO | /// table orden_respuesta// /// hb=# select * from orden_respuesta ; numorden | numtti -+- ORD0246 | TTI0206 ORD0245 | TTI0206 ORD0244 | TTI0206 ORD0243 | TTI0206 ORD0242 | TTI0206 ORD0264 | TTI0207 ORD0261 | TTI0207 ORD0240 | TTI0209 regards, Ricardo _ Únase al mayor servicio mundial de correo electrónico: http://www.hotmail.com/es ---(end of broadcast)--- TIP 3: 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
[SQL] using rownum as index-counter of nested table or varray
Hi,
In Oracle 8.1.7, I have a problem using rownum as an index-counter of nested
table(or varray). I got a "ORA-06532: Subscript outside of limit" for the
following code, although rownum is in the correct range.
I saw an example to solve this problem, but I haven't got any succesfully
result.
For this example put a clause at where statement was enough. The clause is
"where rownum < varray.count", but It didn´t work it for me.
The nested table is defined as a type.
V_PERIOD_PLUS_LT TYPE_PERIOD_PLUS_LT_OBJECT :=
TYPE_PERIOD_PLUS_LT_OBJECT('','',0,0,0);
V_PERIOD_PLUS_LT_TABLE TYPE_PERIOD_LT_TABLE := TYPE_PERIOD_LT_TABLE();
Then I fill this table:
for i in 1..10 loop
V_PERIOD_PLUS_LT := TYPE_PERIOD_PLUS_LT_OBJECT('compania', 'producto',
2002, 9, 16);
V_PERIOD_PLUS_LT_TABLE.EXTEND;
V_PERIOD_PLUS_LT_TABLE(I) := V_PERIOD_PLUS_LT;
end loop;
Here I open the cursor using a rownum to access the table created before.
open p_cursor for
select V_PERIOD_PLUS_LT_TABLE(rownum).kyear year
from product_master
where rownum < 10;
Is it really working at Oracle 8.17?
Thanks
_
Send and receive Hotmail on your mobile device: http://mobile.msn.com
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Table Copy.
what about CREATE TABLE one ( fileda INTEGER, filedb INTEGER, filedc INTEGER ); CREATE VIEW two AS SELECT * FROM one; ? Dima PostgreSQL Server wrote: > HI! > > I'm new to postgres. I need to have a table as a copy of another one. > > Example: > > CREATE TABLE one ( > fileda INTEGER, > filedb INTEGER, > filedc INTEGER ); > > CREATE TABLE two ( > fileda INTEGER, > filedb INTEGER, > filedc INTEGER ); > > As on insert to table one I should get the same insert on table two. > As on delete to table one I should get the same delete on table two. > As on update to table one I should get the same update on table two. > > Can someone provide the examples i can study ? > > Thanks in advance. > > Alex > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] help w/ constructing a SELECT
Charles, > Having a problem with a query. > I would like to construct a query which will return a list of all > contigs which are comprised of clones whose 'read' = 'x' (read can be > either 'x' or 'y'). It appears that you haven't formulated clearly what you want to get. I can see 3 possibilities: 1. All contigs with one or more clones whose read = 'x' and those clones. 2. All contigs with one or more clones whose read = 'x' and all of those contig's clones 3. All contigs where all clones have read = 'x' Which do you want? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Query Freeze
Hi, (sorry for the big mail) I have 3 applications in windows and they starts 3 postgres backends. The 1st app. call the 2nd and this call the 3rd. In the same place of the 3rd backend, the query freeze. If I kill the second backend(or app.), the query is released... Any suggestions? Thank's Alexandre *** This is my ps when the query is freeze: postgres 19055 0.0 0.9 336188 4932 pts/2 S21:16 0:00 /usr/bin/postmaster -i -d 2 postgres 19057 0.0 0.9 337180 4900 pts/2 S21:16 0:00 postgres: stats buffer process postgres 19059 0.0 0.9 336236 4960 pts/2 S21:16 0:00 postgres: stats collector process postgres 19070 0.0 1.4 337808 7320 pts/2 S21:16 0:00 postgres: postgres mg_pg 192.168.1.156 idle in transaction postgres 19071 0.5 2.0 338932 10372 pts/2 S21:16 0:00 postgres: postgres mg_pg 192.168.1.156 idle in transaction postgres 19098 0.0 1.3 337472 6768 pts/2 S21:16 0:00 postgres: postgres mg_pg 192.168.1.156 SELECT waiting *** This is my gdb (attach 19098; bt) : #0 0x4018b6ff in semop () from /lib/libc.so.6 #1 0x08104fec in IpcSemaphoreLock (semId=2457600, sem=2, interruptOK=1 '\001') at ipc.c:422 #2 0x08108f3f in ProcSleep (lockMethodTable=0x81ee500, lockmode=5, lock=0x53e28f14, holder=0x53e2bf2c) at proc.c:717 #3 0x081081ff in WaitOnLock (lockmethod=1, lockmode=5, lock=0x53e28f14, holder=0x53e2bf2c) at lock.c:910 #4 0x08107fbb in LockAcquire (lockmethod=1, locktag=0xbfffebf0, xid=20642, lockmode=5, dontWait=0 '\000') at lock.c:698 #5 0x08107885 in XactLockTableWait (xid=20636) at lmgr.c:344 #6 0x08074370 in heap_mark4update (relation=0x5476fc18, tuple=0xbfffec90, buffer=0xbfffec84) at heapam.c:1684 #7 0x080c265c in ExecutePlan (estate=0x825c1a8, plan=0x825c108, operation=CMD_SELECT, numberTuples=0, direction=ForwardScanDirection, destfunc=0x825cc10) at execMain.c:1052 #8 0x080c1bc7 in ExecutorRun (queryDesc=0x825c190, estate=0x825c1a8, feature=3, count=0) at execMain.c:233 #9 0x0810e6be in ProcessQuery (parsetree=0x8247cb0, plan=0x825c108, dest=Remote, completionTag=0xbfffed70 "") at pquery.c:259 #10 0x0810cf60 in pg_exec_query_string ( query_string=0x8247268 "BEGIN; SELECT EmpCod, L001Codi, L029Codi, L029Tama, L029Qatu, L029Empe, L029Emps, L029ATec, L029QRes FROM LOJ0291 WHERE EmpCod = '1' AND L029Codi = '6308' AND L001Codi = '1' AND L029Tama = '2' "..., dest=Remote, parse_context=0x821dfd8) at postgres.c:811 #11 0x0810df3e in PostgresMain (argc=5, argv=0xbfffefa0, username=0x8205819 "postgres") at postgres.c:1926 #12 0x080f557e in DoBackend (port=0x82056e8) at postmaster.c:2243 #13 0x080f4ebf in BackendStartup (port=0x82056e8) at postmaster.c:1874 #14 0x080f4142 in ServerLoop () at postmaster.c:995 #15 0x080f3c5b in PostmasterMain (argc=4, argv=0x81ee178) at postmaster.c:771 #16 0x080d36b5 in main (argc=4, argv=0xb924) at main.c:206 #17 0x400d20af in __libc_start_main () from /lib/libc.so.6 ---(end of broadcast)--- TIP 3: 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
[SQL] Two Permance Questions
Hi! Q1. Is subquery better or join? For subquery: SELECT t1.c1,(SELECT t2.c2 FROM t2 WHERE t2.c1 = t1.c1) FROM t1 I wonder it will loop n times if t1 returns n rows. If this is the case, is it better to use join like this: SELECT t1.c1,t2.c2 FROM t1,t2 WHERE t2.c1 = t1.c1 Q2. If the query is not optimize like this: SELECT t1.c1,t2.c2 FROM t1,t2,t1,t2,t2 WHERE t2.c1=t1.c1 AND t1.c1=t2.c1 AND t1.c1=t2.c1 and the size of this clause can soar up to several kbytes, then can this query cause performance problem? Regards, -- ___ Get your free email from http://www.graffiti.net Powered by Outblaze ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
