[SQL] check source of trigger
Hello, I have a question about trigger. I have tables with the following structure: create table A ( e_codeA char(5) default '' not null, n_codeA varchar(20) default '' not null, constraint A_pkey primary key ( e_codeA ) ); create table B ( e_codeB char(5) default '' not null, e_codeA char(5) default '' not null constraint e_codeA_ref references A( e_codeA ) on delete cascade on update cascade, n_codeB varchar(20) default '' not null, constraint B_pkey primary key ( e_tranB, e_codeA ) ); I have trigger and procedure on table B to capture any change and insert into table logB: create trigger trigger_b before insert or update or delete on B for each row execute procedure log_change(); When I update e_codeA in table A, the constrain trigger will update e_codeA in B. My trigger, trigger_b, also was trigged and procedure will record change into table logB too. How to write a code in my db procedure to check whether the procedure was called by normal SQL or was called by cascade trigger. Regards, wit ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] query concat
On Friday 20 September 2002 16:46, Ricardo Javier Aranibar León wrote: > Hi List, Hello > > The Table "result"(I like this information) > numtti | numorden | tt | usuario | estado | > --+---++-+-+ > TTI0206| ORD0244, ORD0245..| 100029 | joroza | CERRADO | > TTI0207| ORD0261, ORD0264 | 100051 | pdorado | REVISION| > TTI0208| ORD0242, ORD0243 | 56729 | joroza | CERRADO | > Normally I would say use a "create view as select ... .". but I am not sure if this will display your needs. Do you really need the data like "ORD0244, ORD0245..." ? Then you should use an array or set datatype. They are normally not nice to handel. regards -andreas -- Andreas Schmitz - Phone +49 201 8501 318 Cityweb-Technik-Service-Gesellschaft mbH Friedrichstr. 12 - Fax +49 201 8501 104 45128 Essen - email [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] Getting acces to MVCC version number
Jean-Luc Lachance <[EMAIL PROTECTED]> writes: > How about making available the MVCC last version number just like oid is > available. This would simplify a lot of table design. You know, having > to add a field "updated::timestamp" to detect when a record was updated > while viewing it (a la pgaccess). > That way, if the version number do not match, one would know that the > reccord was updated since last retrieved. > What do think? I think it's already there: see xmin and cmin. Depending on your needs, testing xmin might be enough (you'd only need to pay attention to cmin if you wanted to notice changes within your own transaction). 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] Performance w/ multiple WHERE clauses
Aaron, > # SET enable_seqscan to FALSE ; > forced the use of an Index and sped things up greatly. > > I am not sure why it made the switch. The load on the server seems to > affect the performance, but I am seeing it more on the production server > with 100 million rows as opposed to the development server with only > about 6 million. I need to buy more drives and develop on a larger data > set. What version are you using? I'd have 3 suggestions: 1) ANALYZE, ANALYZE, ANALYZE. Then check if the row estimates made by EXPLAIN seem accurate. 2) Modify your postgresql.conf file to raise the cost of seq_scans for parser estimates. 3) Test this all again when 7.3 comes out, as parser estimate improves all the time. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] check source of trigger
On Friday 20 Sep 2002 9:25 am, wit wrote: > I have trigger and procedure on table B to capture any change and insert > into table logB: > create trigger trigger_b before insert or update or delete on B for > each row execute procedure log_change(); > > When I update e_codeA in table A, the constrain trigger will update e_codeA > in B. My trigger, trigger_b, also was trigged and procedure will record > change into table logB too. > How to write a code in my db procedure to check whether the procedure was > called by normal SQL or was called by cascade trigger. There are a number of "special" variables defined if you are a trigger procedure (not just OLD and NEW) - is that what you were after (Programmers manual, ch 23.9) - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL]
On Thursday 19 Sep 2002 9:30 pm, Ricardo Javier Aranibar León wrote: > Hi list, Hi Ricardo > I need your colaboration,I like a table or view with this information > from 2 tables "ticket" and "orden_respuesta". > > numtti | numorden | tt | usuario | estado | > ---+---++-+-+ > TTI0206| ORD0246, ORD0245..| 100029 | joroza | CERRADO | > > -++-+--+ > TTI0206 | 100029 | joroza | CERRADO | > > -+- > ORD0246 | TTI0206 > ORD0245 | TTI0206 Do a search on aggregate functions and "concat" in the mailing list archives, also see the Postgresql Cookbook on techdocs.postgresql.org, I think there might be something there for you. That's assuming you don't care about order of course. - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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. I will search in the applications... Very thank´s. Alexandre ---(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] Performance w/ multiple WHERE clauses
Thanks,
Changing '0/19/01' to '0/19/01'::date gave me a subjective 50% speedup.
A ran a bunch of queries w/ explain and I noticed that some
combinations did not use the indexes and went right to seq scan. All of
the where clause args are indexed.
# SET enable_seqscan to FALSE ;
forced the use of an Index and sped things up greatly.
I am not sure why it made the switch. The load on the server seems to
affect the performance, but I am seeing it more on the production server
with 100 million rows as opposed to the development server with only
about 6 million. I need to buy more drives and develop on a larger data
set.
Thanks for the help,
-Aaron Held
Chris Ruprecht wrote:
> Aaron,
>
> On Wed September 18 2002 17:17, Aaron Held wrote:
>
>>I am running into a serious performance issue with some basic queries.
>>
>>If I run something like
>>
>> select * from "Calls" WHERE
>>( ("CallType" = 'LONG DIST' ))
>>
>>The search takes about 15 seconds
>>
>>if I run
>>select * from "Calls" WHERE
>>( ( "DateOfCall"='06/19/02') )
>>AND ( ( "CallType" = 'LONG DIST' ))
>> [DateOfCall is a DateTime field]
>
>
> try ... "DateOfCall" = '2002-06-19'::date ...
>
> Best regards,
> Chris
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] query concat
Hi List, First, Thanks for your colaboration Richard Huxton "Do a search on aggregate functions and "concat" in the mailing list archives, also see the Postgresql Cookbook on techdocs.postgresql.org, I think there might be something there for you." I have been written this mail because I din't find the solution for my problem. Please, I need your colaboration,I like a table or view with this information from 2 tables "ticket" and "orden_respuesta". If you see the table "orden_respuesta", I have many numorden's for one numtti. and in the table "ticket" I have a tti with information about user. The Table "result"(I like this information) numtti | numorden | tt | usuario | estado | --+---++-+-+ TTI0206| ORD0244, ORD0245..| 100029 | joroza | CERRADO | TTI0207| ORD0261, ORD0264 | 100051 | pdorado | REVISION| TTI0208| ORD0242, ORD0243 | 56729 | joroza | CERRADO | (I have this tables as information) Table "ticket" tti | tt | usuario | estado | -++-+--+ TTI0206 | 100029 | joroza | CERRADO | TTI0207 | 100051 | pdorado | REVISION | TTI0208 | 100049 | joroza | CERRADO | Table "orden_respuesta" numorden | numtti +- ORD0246 | TTI0206 ORD0245 | TTI0206 ORD0244 | TTI0206 ORD0264 | TTI0207 ORD0261 | TTI0207 ORD0243 | TTI0208 ORD0242 | TTI0208 Regards, Ricardo P.D: Sorry for my grammar I'm from Bolivia, but I understand Enghish _ MSN Fotos: la forma más fácil de compartir e imprimir fotos. http://photos.msn.es/support/worldwide.aspx ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] query concat
On Fri, 20 Sep 2002, [iso-8859-1] Ricardo Javier Aranibar León wrote: > Hi List, > First, Thanks for your colaboration Richard Huxton "Do a search on aggregate > functions and "concat" in the mailing list archives, > also see the Postgresql Cookbook on techdocs.postgresql.org, I think there > might be something there for you." > > I have been written this mail because I din't find the solution for my > problem. For example, an aggregate like: http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=139 (available in the aggregates section of the cookbook - note there's a bug and you need to double the quotes around the ', ') Then you can use a left join and the comma aggregate, something like: select ticket.numtti, ticket.tt, ticket.usuario, ticket.estado, comma(order_respuesta.numorden) from ticket left outer join orden_respuesta on (tti=numtti) group by ticket.numtti, ticket.tt, ticket.usuario, ticket.estado; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Monitoring a Query
Is there any way to monitor a long running query?
I have stats turned on and I can see my queries, but is there any better
measure of the progress?
Thanks,
-Aaron Held
select current_query from pg_stat_activity;
current_query
in transaction
FETCH ALL FROM PgSQL_470AEE94
in transaction
select * from "Calls" WHERE "DurationOfCall" = 2.5 AND "DateOfCall" =
'7/01/02' AND ("GroupCode" = 'MIAMI' OR "GroupCode" = 'Salt Lake');
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] help w/ constructing a SELECT
Charles, > > 3. All contigs where all clones have read = 'x' SELECT * FROM contigs WHERE NOT EXISTS ( SELECT contig_id FROM clones WHERE clones.contig_id = contigs.contig_id AND read <> 'x'); i.e. "Select all contigs not having any clone whose read is something other than 'x' " got it? -Josh Berkus ---(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] [GENERAL] Monitoring a Query
Aaron Held wrote: > Is there any way to monitor a long running query? > > I have stats turned on and I can see my queries, but is there any better > measure of the progress? Oh, sorry, you want to know how far the query has progressed. Gee, I don't think there is any easy way to do that. Sorry. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] [GENERAL] Monitoring a Query
There is pgmonitor:
http://gborg.postgresql.org/project/pgmonitor
---
Aaron Held wrote:
> Is there any way to monitor a long running query?
>
> I have stats turned on and I can see my queries, but is there any better
> measure of the progress?
>
> Thanks,
> -Aaron Held
>
> select current_query from pg_stat_activity;
> current_query
>
>
>
>
>
> in transaction
> FETCH ALL FROM PgSQL_470AEE94
> in transaction
> select * from "Calls" WHERE "DurationOfCall" = 2.5 AND "DateOfCall" =
> '7/01/02' AND ("GroupCode" = 'MIAMI' OR "GroupCode" = 'Salt Lake');
>
>
>
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(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] Getting acces to MVCC version number
Hi all developpers, This is just a idea. How about making available the MVCC last version number just like oid is available. This would simplify a lot of table design. You know, having to add a field "updated::timestamp" to detect when a record was updated while viewing it (a la pgaccess). That way, if the version number do not match, one would know that the reccord was updated since last retrieved. What do think? JLL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] Monitoring a Query
Uh, no, not yet. There is a non-X version of tcl but I don't think
pgaccess will work under that.
---
[EMAIL PROTECTED] wrote:
>
> I just downloaded and installed pgmonitor on my dev. machine after seeing
> your post, and it looks nifty. Only problem is I really want to avoid
> running X on the database server to conserve the RAM it uses, and this
> appears to require X. Any terminal applications to monitor database
> activity, perhaps loosely analagous to mtop for MySQL?
> (http://mtop.sf.net/)
>
> Wes Sheldahl
>
>
>
>
> Bruce Momjian <[EMAIL PROTECTED]>@postgresql.org on 09/20/2002
> 12:18:06 PM
>
> Sent by:[EMAIL PROTECTED]
>
>
> To:Aaron Held <[EMAIL PROTECTED]>
> cc:[EMAIL PROTECTED], [EMAIL PROTECTED]
> Subject:Re: [GENERAL] Monitoring a Query
>
>
>
> There is pgmonitor:
>
> http://gborg.postgresql.org/project/pgmonitor
>
> ---
>
> Aaron Held wrote:
> > Is there any way to monitor a long running query?
> >
> > I have stats turned on and I can see my queries, but is there any better
> > measure of the progress?
> >
> > Thanks,
> > -Aaron Held
> >
> > select current_query from pg_stat_activity;
> > current_query
> >
> >
> >
> >
> >
> > in transaction
> > FETCH ALL FROM PgSQL_470AEE94
> > in transaction
> > select * from "Calls" WHERE "DurationOfCall" = 2.5 AND "DateOfCall" =
> > '7/01/02' AND ("GroupCode" = 'MIAMI' OR "GroupCode" = 'Salt Lake');
> >
> >
> >
> >
> >
> > ---(end of broadcast)---
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
> >
>
> --
> Bruce Momjian| http://candle.pha.pa.us
> [EMAIL PROTECTED] | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup.| Newtown Square, Pennsylvania
> 19073
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
>
>
>
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] Monitoring a Query
There are some good views and functions you can use to get at the SQL
query being executed
try turning on the stats collector and running
select * from pg_stat_activity;
(See http://www.postgresql.org/idocs/index.php?monitoring-stats.html )
You can also see the procID.
From Python I can use this info to get a lot of details about the
running query, CPU and memory use.
But I can't tell how far along it actually is.
-Aaron
[EMAIL PROTECTED] wrote:
> I just downloaded and installed pgmonitor on my dev. machine after seeing
> your post, and it looks nifty. Only problem is I really want to avoid
> running X on the database server to conserve the RAM it uses, and this
> appears to require X. Any terminal applications to monitor database
> activity, perhaps loosely analagous to mtop for MySQL?
> (http://mtop.sf.net/)
>
> Wes Sheldahl
>
>
>
>
> Bruce Momjian <[EMAIL PROTECTED]>@postgresql.org on 09/20/2002
> 12:18:06 PM
>
> Sent by:[EMAIL PROTECTED]
>
>
> To:Aaron Held <[EMAIL PROTECTED]>
> cc:[EMAIL PROTECTED], [EMAIL PROTECTED]
> Subject:Re: [GENERAL] Monitoring a Query
>
>
>
> There is pgmonitor:
>
> http://gborg.postgresql.org/project/pgmonitor
>
> ---
>
> Aaron Held wrote:
>
>>Is there any way to monitor a long running query?
>>
>>I have stats turned on and I can see my queries, but is there any better
>>measure of the progress?
>>
>>Thanks,
>>-Aaron Held
>>
>>select current_query from pg_stat_activity;
>>current_query
>>
>>
>>
>>
>>
>> in transaction
>>FETCH ALL FROM PgSQL_470AEE94
>> in transaction
>>select * from "Calls" WHERE "DurationOfCall" = 2.5 AND "DateOfCall" =
>>'7/01/02' AND ("GroupCode" = 'MIAMI' OR "GroupCode" = 'Salt Lake');
>>
>>
>>
>>
>>
>>---(end of broadcast)---
>>TIP 2: you can get off all lists at once with the unregister command
>>(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>>
>
>
> --
> Bruce Momjian| http://candle.pha.pa.us
> [EMAIL PROTECTED] | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup.| Newtown Square, Pennsylvania
> 19073
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
>
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Appending to an array[] feild...[ ltree ]
Hi ,
We are undergoing a data consolidation process wherein we are making a common
repository of business profiles from various sources.
I require to store label paths like 1.1.1 , 1.1.2,1.1.3 etc in a feild
and i use ltree[] for fast searching.
The problem is in the ltree[] feild in need to store uniq paths and need
want to know if some utility functions exists.
For example if {1.1.1,1.1.2,1.1.3} is contained in a ltree[] record and i encounter
a path say 1.1.4 i need to update it to {1.1.1,1.1.2,1.1.3,1.1.4} in other words
i need to insert to the ltree[] feild. Does there exists any generic function
(or ltree[] specific function) to add an item in the array? (first question)
My another question is is there any way to matain uniqueness in a ltree[] feild
for example , suppose i now encounter {1.1.1} again i do not want to
update the record to {1.1.1,1.1.2,1.1.3,1.1.4,1.1.1} to want it to remain the same
ie,{1.1.1,1.1.2,1.1.3,1.1.4} becoz 1.1.1 is already present in the [] , does there
exists any function to probe an ltree[] feild for existance ?
Regards
Mallah.
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] timestamp parse error
Hello! i'm using PostgreSQL 7.2.1 and got strange parse errors.. could somebody tell me what's wrong with this timestamp query example? PostgreSQL said: ERROR: parser: parse error at or near "date" Your query: select timestamp(date '1998-02-24', time '23:07') example is from PostgreSQL help and certainly worked in previous versions of pgsql.. but in 7.2.1 it does not. had anything changed and not been updated in pgsql manuals or is it a bug? thanx for any help Tomas Lehuta ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] timestamp parse error
On Fri, 20 Sep 2002, Tomas Lehuta wrote: > Hello! > > i'm using PostgreSQL 7.2.1 and got strange parse errors.. > could somebody tell me what's wrong with this timestamp query example? > > PostgreSQL said: ERROR: parser: parse error at or near "date" > Your query: > > select timestamp(date '1998-02-24', time '23:07') > > example is from PostgreSQL help and certainly worked in previous versions of > pgsql.. but in 7.2.1 it does not. had anything changed and not been updated > in pgsql manuals or is it a bug? Presumably it's a manual example that didn't get changed. Timestamp(...) is now a specifier for the type with a given precision. You can use "timestamp"(date '1998-02-24', time '23:07') or datetime math (probably something like date '1998-02-24' + time '23:07' and possibly a cast) ---(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] [GENERAL] timestamp parse error
"Tomas Lehuta" <[EMAIL PROTECTED]> writes: > could somebody tell me what's wrong with this timestamp query example? > select timestamp(date '1998-02-24', time '23:07') > PostgreSQL said: ERROR: parser: parse error at or near "date" > example is from PostgreSQL help >From where exactly? I don't see any such example in current sources. Although you could make this work by double-quoting the name "timestamp" (which is a reserved word now, per SQL spec), I'd recommend sidestepping the problem by using the equivalent + operator instead: regression=# select "timestamp"(date '1998-02-24', time '23:07'); timestamp - 1998-02-24 23:07:00 (1 row) regression=# select date '1998-02-24' + time '23:07'; ?column? - 1998-02-24 23:07:00 (1 row) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
