Re: [SQL] Very strange 'now' behaviour in nested triggers.
> On Sat, Jul 26, 2003 at 03:14:16PM +0100, Richard Huxton wrote: >> On Saturday 26 July 2003 14:39, Denis Zaitsev wrote: > >> Solution: make the default now() or CURRENT_TIMESTAMP and all will be as >> you >> expect. >> >> PS - I think this is mentioned in the manuals somewhere, but it's not >> surprising you missed it. Interesting example. > > As I remember, namely 'now' is mentioned in the manuals, as the best > approach to keep the same value thru the whole transaction. That is > why I used it here. For now I've tested that now() does the thing. > Why? I remember that now() is changing thru the transaction, just > showing the current time... No- 'now',now() and CURRENT_TIMESTAMP all stay fixed during a transaction. The one that changes is timeofday() I think. See the "Functions and Operators" section for details. - Richard ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Very strange 'now' behaviour in nested triggers.
On Sun, Jul 27, 2003 at 08:47:16AM +0100, Richard Huxton wrote: > > No- 'now',now() and CURRENT_TIMESTAMP all stay fixed during a transaction. > The one that changes is timeofday() I think. See the "Functions and > Operators" section for details. Yes, indeed... Documentation describes this. And I haven't found anything about 'now' that I used to say... I don't know why (my memory leak?) :) Thanks. BTW, this text is at the bottom of the "Date/Time Functions and Operators" section (functions-datetime.html): SELECT CURRENT_TIMESTAMP; SELECT now(); SELECT TIMESTAMP 'now'; Note: You do not want to use the third form when specifying a DEFAULT clause while creating a table. The system will convert now to a timestamp as soon as the constant is parsed, so that when the default value is needed, the time of the table creation would be used! ... It's nearly what you have written about. But I want to note phrase. Should it be fixed there? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Very strange 'now' behaviour in nested triggers.
Denis Zaitsev <[EMAIL PROTECTED]> writes: > On Sat, Jul 26, 2003 at 10:31:44AM -0400, Tom Lane wrote: >> That's a dangerous way to define the default --- 'now' is taken as a >> literal of type timestamp, which means it will be reduced to a timestamp >> constant as soon as a statement that requires the default is planned. > Aaa... So, the INSERT inside a trigger will use the 'now' for the > time this trigger is compiled (i.e. called first time)? Do I > understand right? And the only outer trigger uses the right 'now' as > its value goes from the top-level INSERT... Right. I put up a proposal in pgsql-hackers to change this behavior: http://archives.postgresql.org/pgsql-hackers/2003-07/msg00818.php If we made that change then the "wrong" way of defining the default would fail in an obvious fashion --- the 'now' would get reduced to a particular time immediately at CREATE TABLE. Doubtless this would annoy some people, but the "right" way of defining the default isn't really any harder, and it would save folks from getting burnt in corner cases, like you were. Any comments? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Very strange 'now' behaviour in nested triggers.
On Sun, Jul 27, 2003 at 11:49:10AM -0400, Tom Lane wrote: > > I put up a proposal in pgsql-hackers to change this behavior: > http://archives.postgresql.org/pgsql-hackers/2003-07/msg00818.php > If we made that change then the "wrong" way of defining the default > would fail in an obvious fashion --- the 'now' would get reduced to a > particular time immediately at CREATE TABLE. Doubtless this would annoy > some people, but the "right" way of defining the default isn't really > any harder, and it would save folks from getting burnt in corner cases, > like you were. > > Any comments? I think that is a preferred behavior. -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + -°*'. (Explosive Tagline) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Can a table have a reference to itself?
Hi, I'd like to have a table in which one column has an integrity reference to another column within the same table, and for updates to the primary column to be cascaded. The former aspect seems to work OK, but the latter does not. For example: > create table foo (a int primary key, b int constraint chk_a references foo(a) match full on update cascade); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo' NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE Inserting values into foo works as expected (and hoped); setting b to be equal to a or to an existing value of a is fine: > insert into foo values (1,1); INSERT 141920621 1 > insert into foo values (2,2); INSERT 141920622 1 > insert into foo values (3,2); INSERT 141920623 1 but setting b to be a value not yet present in a fails: > insert into foo values (4,5); ERROR: chk_a referential integrity violation - key referenced from foo not found in foo So far so good, but what I'd like to do is to be able to change a value of a, and have this cascaded to b; however this gives an integrity violation error: > update foo set a = 5 where a = 2; ERROR: chk_a referential integrity violation - key referenced from foo not found in foo Is what I'm trying to do possible? In practice, the table I wish to create is a list of data sets; a is the id of each data set, and b indicates whether or not a data set is 'original' (in which case b should equal a) or derived from an existing data set (in which case b should equal the id of the data set from from which it was derived i.e. an existing value of a from elsewhere in the table). (I'm currently using postgres 7.2.1 on Solaris 5.7) Cheers, Oliver -- Oliver Duke-Williams School of Geography, University of Leeds ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Problem using Subselect results
I want to use the result of a subselect as condition of another one. CREATE VIEW my_view AS SELECT b,c (SELECT a, b FROM table2 WHERE b=1) my_ab, (SELECT c FROM table3, my_ab WHERE table3.a=my_ab.a) my_c; does return "relation my_ab unknown". it is not just a problem of execution order - if i turn it the other way round it's still the same. Am I just trying to do something really stupid? And what for is the (necessary) AS statement for subselects, if it's not possible to access their results by that name? As I need the result of a subselect in several other subselects it's not possible to transform them into a cascade of sub, subsub, subsubsub selects. Any ideas? TIA, Oliver - This mail sent through IMP: http://horde.org/imp/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] PostgreSQL or pl/psSQL equivalent to MS SQL Server's xp_cmdshell?
Title: PostgreSQL or pl/psSQL equivalent to MS SQL Server's xp_cmdshell? Does anyone know of the PostgreSQL or pl/psSQL equivalent to MS SQL Server's xp_cmdshell? This is the command that allows you issue command-line statements from within SQL, e.g., you would do xp_cmdshell 'dir c:\' if you wanted to see the contents of the c: drive. TM ** The information transmitted herewith is sensitive information intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.
Re: [SQL] [OT] SUMMERY Frontend recommendations
SUMMERY Question: > Hey postgresql gurus, > > I was just wondering, what kind of frontend do you recommend in 'bills > (windows...)' environment? > Is it better to use a tool like Qt from trolltech or is it better to use > something like Omnis or Access??? > > What are you opinions ANSWERS: It all depends what you need to do. From using Qt for graphical frontend. I have found the Sql bits to be extremly slow. So the plan is currently to stop using them and use the pqxx drivers. Change is not easy however! (Thats under Linux...) If you need a quick gui try pgaccess and the like. If you want to writea full gui pick you favoute language and there should be some postgres drivers (if all else fails there is always ODBC) somthing like perl or python may be nice and platform independant <> I've always felt there's nowt wrong with Access as a way of building forms/reports etc. ---(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] Function index qeustion
Questions: 1) If you have an index on a cacheable function, does PostgreSQL use the index instead of calculating the results? 2) How does PostgreSQL know when to recompute the function? Jon On Fri, 25 Jul 2003, Elielson Fontanezi wrote: > Thanks a lot! > > The complete solution is here! > > 1st. The function wich substitute the trunc() function > > CREATE OR REPLACE FUNCTION func_cod_secretaria(INTEGER) RETURNS INTEGER AS ' >DECLARE > v_nr_proponente ALIAS FOR $1; >BEGIN > return TRUNC(v_nr_proponente/10,0)*10; >END; > ' LANGUAGE 'plpgsql' WITH isCachable; > > 2nd. The index on that function > > CREATE INDEX bt_proposta_f01 > ON proposta USING BTREE (func_cod_secretaria(nr_proponente)); > > 3rd. The anlysis of both queries: the old and new one. > > a) the old query: > > --- > DEBUG: query: select >pa.nr_projeto, >pa.dc_denom_projeto, >pa.nr_proponente, >pa.dc_coordenador, >op.dc_proponente >from proposta pa >inner join orgao_proponente op >on (trunc(pa.nr_proponente/10,0)*10 = op.nr_proponente) >where pa.in_situacao_proposta <> 'E' ORDER BY 1 DESC; > > DEBUG: QUERY STATISTICS > ! system usage stats: > ! 104.665005 elapsed 10.09 user 0.42 system sec > ! [10.10 user 0.42 sys total] > ! 0/0 [0/0] filesystem blocks in/out > ! 141/50 [352/180] page faults/reclaims, 0 [0] swaps > ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent > ! 0/0 [0/0] voluntary/involuntary context switches > ! postgres usage stats: > ! Shared blocks: 7408 read, 0 written, buffer hit rate > = 13.23 > % > ! Local blocks: 0 read, 0 written, buffer hit rate > = 0.00% > ! Direct blocks: 0 read, 0 written > --- > > > b) the new query > > > --- > DEBUG: query: select >pa.nr_projeto, >pa.dc_denom_projeto, >pa.nr_proponente, >pa.dc_coordenador, >op.dc_proponente >from proposta pa >inner join orgao_proponente op >on (op.nr_proponente = func_cod_secretaria(pa.nr_proponente)) >where pa.in_situacao_proposta <> 'E'; > DEBUG: query: SELECT TRUNC( $1 /10,0)*10 > DEBUG: QUERY STATISTICS > ! system usage stats: > ! 0.130885 elapsed 0.02 user 0.01 system sec > ! [0.02 user 0.02 sys total] > ! 0/0 [0/0] filesystem blocks in/out > ! 168/68 [369/172] page faults/reclaims, 0 [0] swaps > ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent > ! 0/0 [0/0] voluntary/involuntary context switches > ! postgres usage stats: > ! Shared blocks:142 read, 1 written, buffer hit rate > = 88.10 > % > ! Local blocks: 0 read, 0 written, buffer hit rate > = 0.00% > ! Direct blocks: 0 read, 0 written > --- > > -Mensagem original- > De: Stephan Szabo [mailto:[EMAIL PROTECTED] > Enviada em: sexta-feira, 25 de julho de 2003 16:47 > Para: Elielson Fontanezi > Cc: pgsql-general; pgsql-sql > Assunto: Re: [GENERAL] ERROR: DefineIndex: index function must be marked > iscachable > > > > On Fri, 25 Jul 2003, Elielson Fontanezi wrote: > > > Who can help me on that? > > > > First of all, my envoronment is: > > Linux netlab142.prodam 2.4.8-26mdk #1 Sun Sep 23 17:06:39 CEST > 2001 > > i686 unknown > > pg_ctl (PostgreSQL) 7.2. > > You should definately move to the highest 7.2 release (7.2.4 I think) > which shouldn't require a restore (although you should back up first in > any case). I think there were some reasonably important fixes between > 7.2.1 and 7.2.4. > > > CREATE OR REPLACE FUNCTION func_cod_secretaria(INTEGER) RETURNS INTEGER AS > ' > >DECLARE > > v_nr_proponente ALIAS FOR $1; > >BEGIN > > return TRUNC(v_nr_proponente/10,0)*10 > >END; > > ' LANGUAGE 'plpgsql' > Add WITH (iscachable) after LANGUAGE 'plpgsql' to make the function usable > in the functional index unless I'm misremembering the old syntax. > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Can a table have a reference to itself?
On Wed, 23 Jul 2003, Oliver Duke-Williams wrote: > I'd like to have a table in which one column has an integrity reference > to another column within the same table, and for updates to the primary > column to be cascaded. The former aspect seems to work OK, but the > latter does not. I think you need to upgrade to 7.3 or better and try it there. I think that's when those bugs were fixed. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Using a compound primary key
Hi all,
I have a table containing tariff information. It has a primary key (and therefore
unique index) of (tariff_type varchar(5),prefix varchar(12)) where tariff_type is a
set of rates assigned to dialling prefixes.
e.g.
tariff_type prefix rate
abc 44 $x
abc 441 $y
abc 61 $z
def 44 $a
def 441 $b
def 61 $c
and so on.
For a known tariff_type, I need to find the rate that has the longest matching
dialling prefix. In the data above, if I made a phone call to +4412345678 using
tariff_type abc, then I would want to retrieve the record abc,441,$y and not the
record abc,44,$x. I do this currently by dividing up the phone number and using this
query:
select * from tariff
where tariff_type = 'UIA'
and prefix in ('44','441','4412','44123','441234','4412345','44123456')
order by prefix desc limit 1;
The query doesn't use the primary key index as I might expect:
Limit (cost=98.88..98.88 rows=1 width=31)
-> Sort (cost=98.88..98.89 rows=7 width=31)
Sort Key: prefix
-> Seq Scan on tariff (cost=0.00..98.78 rows=7 width=31)
Filter: ((tariff_type = 'UIA'::character varying) AND ((prefix =
'44'::character varying) OR (prefix = '441'::character varying) OR (prefix =
'4412'::character varying) OR (prefix = '44123'::character varying) OR (prefix =
'441234'::character varying) OR (prefix = '4412345'::character varying) OR (prefix =
'44123456'::character varying)))
If I specify both parts of the key then it will, of course, use the index and cost
very little:
select * from tariff
where tariff_type = 'UIA' and prefix = '441'
order by prefix desc limit 1;
QUERY PLAN
--
Limit (cost=5.23..5.23 rows=1 width=31)
-> Sort (cost=5.23..5.23 rows=1 width=31)
Sort Key: prefix
-> Index Scan using tariff_ix2 on tariff (cost=0.00..5.22 rows=1 width=31)
Index Cond: ((tariff_type = 'UIA'::character varying) AND (prefix =
'441'::character varying))
I have used this exact scenario using an Informix database and the query planner is
able to use the index:
QUERY:
--
select *
from tariff where tariff_type = 'SIL18'
and (prefix = '44' or prefix = '441' or prefix = '4412'
or prefix = '44123' or prefix = '441234' or prefix = '4412345'
or prefix = '44123456' ) order by prefix desc
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) informix.tariff: INDEX PATH
(1) Index Keys: tariff_type prefix (Key-First)
Lower Index Filter: informix.old_tariff.tariff_type = 'SIL18'
Key-First Filters: (((informix.old_tariff.prefix = '44' OR informix.ol
d_tariff.prefix = '441' ) OR informix.old_tariff.prefix = '4412' ) OR informix.old_
tariff.prefix = '44123' ) OR informix.old_tariff.prefix = '441234' ) OR informix.ol
d_tariff.prefix = '4412345' ) OR informix.old_tariff.prefix = '44123456' ) )
Is there a way I can achieve the same result using PostgreSQL 7.3.2? I can add another
index just on prefix and get a performance increase but it's still not as
cost-efficient as using the primary index. Would it be more cost effective to do a
bunch of individual queries for each length of prefix until I find one that matches?
The average length of a prefix would probably be around 3 digits and I would need to
start at 8 digits and work back to cover all possibilities.
Thanks for any advice,
David Witham
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] Can a table have a reference to itself?
Oliver Duke-Williams <[EMAIL PROTECTED]> writes: > So far so good, but what I'd like to do is to be able to change a value > of a, and have this cascaded to b; however this gives an integrity > violation error: >> update foo set a = 5 where a = 2; > ERROR: chk_a referential integrity violation - key referenced from foo > not found in foo Seems to work in 7.3.4 and CVS tip: regression=# update foo set a = 5 where a = 2; UPDATE 1 regression=# select * from foo; a | b ---+--- 1 | 1 3 | 5 5 | 5 (3 rows) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Using a compound primary key
"David Witham" <[EMAIL PROTECTED]> writes:
> select * from tariff
> where tariff_type = 'UIA'
> and prefix in ('44','441','4412','44123','441234','4412345','44123456')
> order by prefix desc limit 1;
> The query doesn't use the primary key index as I might expect:
Flip the index column order --- this should do what you want if prefix
is the first column in the index. (Improving that is on the wish-list,
but it seems to require a wholesale rethink of the way the planner
searches for indexes matching OR-clauses.) You may also need to phrase
the ORDER BY as "prefix desc, tariff_type desc" to make it perfectly
clear to the planner that you don't need a separate sort step ... not
quite sure whether that will be needed or not.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
