Re: [SQL] Password Policy
Ezequias Rodrigues da Rocha wrote: Hi list, Is there any password polity that postgresql implement ? It is possible to put a set all no administrators passwords to = '123456' from times and times ? Has anyone implement a dinamic password autentication (the password changes according the date/month etc of a day ) ? Use PAM/LDAP/Kerberos to handle your passwords and you have access to all sorts of options. -- Richard Huxton Archonet Ltd ---(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] COPY FROM query.
Paul Lambert wrote: Phillip Smith wrote: "Having said that, if I switch it to a forward slash it works without error... odd, given that weenblows standard is backslash." Yes, but PostgreSQL uses a back-slash as an escape character, which needs to be used to escape itself at an application level before the O/S gets to deal with it :) OK, that makes sense, cheers for the help. Almost correct - PG is moving over to a more SQL standards-based interpretation of what should be done with backslashes in strings. The old behaviour will still be available by prefixing a string with E - E'like this'. See manuals for details. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Sum sets of records
Hi list, I would like a little help from you to get the sum value of various subsets of my table. My table is like this id | type | value 1 C 10.00 2 C 15.00 3 W5.00 4 W3.00 Where C is credit and w is witdraw. How do I sum all records with C and subtract with all records with type W ? Is it possible ? Ezequias
Re: [SQL] Sum sets of records
select sum(case when id = 'C' then value else -value end) as total from mytable >>> "Ezequias Rodrigues da Rocha" <[EMAIL PROTECTED]> 2007-02-12 16:41 >>> Hi list, I would like a little help from you to get the sum value of various subsets of my table. My table is like this id | type | value 1 C 10.00 2 C 15.00 3 W5.00 4 W3.00 Where C is credit and w is witdraw. How do I sum all records with C and subtract with all records with type W ? Is it possible ? Ezequias
RE : [SQL] Sum sets of records
Hi, "Group by" is what you're looking for.. select type,sum(value) from MyTable group by type Or for the total only Select sum(case when type='C' then value when type='W' then -value else 0 end) from MyTable hth Sylvain Barrette Architecte et analyste principal, RE/MAX Québec inc. Hi list, I would like a little help from you to get the sum value of various subsets of my table. My table is like this id | type | value 1 C 10.00 2 C 15.00 3 W5.00 4 W3.00 Where C is credit and w is witdraw. How do I sum all records with C and subtract with all records with type W ? Is it possible ? Ezequias -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.411 / Virus Database: 268.17.36/681 - Release Date: 2007-02-11 ---(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] Deadlock on transaction
Hi list, I am curious becouse I am seen my postgresql locking the transactions when i load a huge (in some case not to huge) numbers of records 70,000 records. Could someone help me with it ? -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/
Re: [SQL] [ADMIN] Deadlock on transaction
On Mon, 2007-02-12 at 11:43, Ezequias Rodrigues da Rocha wrote: > Hi list, > > I am curious becouse I am seen my postgresql locking the transactions > when i load a huge (in some case not to huge) numbers of records > 70,000 records. > > Could someone help me with it ? Are sure that what you're speaking of is a deadlock? Or is it just a stalled transaction while one one transaction waits for another to complete? A true deadlock means that two transactions are stalled waiting for the other to complete, and neither ever can complete, and they are DEADlocked. If you've just got a problem where an update is stalling all the other transactions, then give us a test case / example code of what's happening. If you've got a real deadlock that postgresql isn't catching, that's a much more serious problem. ---(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] [ADMIN] Deadlock on transaction
I mean really deadlock. Other transactions can't access the database until the main transaction is complete. A question: PostgreSQL doesn't permit multiple transactions concurrently ? Ezequias 2007/2/12, Scott Marlowe <[EMAIL PROTECTED]>: On Mon, 2007-02-12 at 11:43, Ezequias Rodrigues da Rocha wrote: > Hi list, > > I am curious becouse I am seen my postgresql locking the transactions > when i load a huge (in some case not to huge) numbers of records > 70,000 records. > > Could someone help me with it ? Are sure that what you're speaking of is a deadlock? Or is it just a stalled transaction while one one transaction waits for another to complete? A true deadlock means that two transactions are stalled waiting for the other to complete, and neither ever can complete, and they are DEADlocked. If you've just got a problem where an update is stalling all the other transactions, then give us a test case / example code of what's happening. If you've got a real deadlock that postgresql isn't catching, that's a much more serious problem. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/
Re: [SQL] [ADMIN] Deadlock on transaction
Ezequias Rodrigues da Rocha wrote: I mean really deadlock. Other transactions can't access the database until the main transaction is complete. A question: PostgreSQL doesn't permit multiple transactions concurrently ? PG has quite good concurrency behaviour. And "can't access the database" isn't anything to do with locking - clients should still be able to log in. We'll need more details: 1. How do you know this is a deadlock? You haven't shown us what's in pg_locks, but I assume you've identified the problem there. 2. What data are you loading, to what tables? 3. Are there any foreign-keys or triggers we would need to know about? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] [ADMIN] Deadlock on transaction
Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> schrieb: > I mean really deadlock. Other transactions can't access the database until the > main transaction is complete. A question: That's not true. > > PostgreSQL doesn't permit multiple transactions concurrently ? Why not? Show us a complete case. PG works fine with many users on many connections and many transactions, at the same time. > > Ezequias > > 2007/2/12, Scott Marlowe <[EMAIL PROTECTED]>: Please, no silly fullquote betlow the answer and no html. -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [ADMIN] Deadlock on transaction
2007/2/12, Richard Huxton : Ezequias Rodrigues da Rocha wrote: > I mean really deadlock. Other transactions can't access the database until > the main transaction is complete. A question: > > PostgreSQL doesn't permit multiple transactions concurrently ? PG has quite good concurrency behaviour. And "can't access the database" isn't anything to do with locking - clients should still be able to log in. We'll need more details: 1. How do you know this is a deadlock? You haven't shown us what's in pg_locks, but I assume you've identified the problem there. I knew becouse my Delphi application reports it to me. If there is anything in pg_log I could see it to you. 2. What data are you loading, to what tables? Simple data but allways with the same username (does it make any difference ?) 3. Are there any foreign-keys or triggers we would need to know about? No. Ps: When I do not use transactions the connections does not lock to other users. -- Richard Huxton Archonet Ltd -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/
Re: [SQL] [ADMIN] Deadlock on transaction
Ezequias Rodrigues da Rocha wrote: 2007/2/12, Richard Huxton : Ezequias Rodrigues da Rocha wrote: > I mean really deadlock. Other transactions can't access the database until > the main transaction is complete. A question: > > PostgreSQL doesn't permit multiple transactions concurrently ? PG has quite good concurrency behaviour. And "can't access the database" isn't anything to do with locking - clients should still be able to log in. We'll need more details: 1. How do you know this is a deadlock? You haven't shown us what's in pg_locks, but I assume you've identified the problem there. I knew becouse my Delphi application reports it to me. If there is anything in pg_log I could see it to you. You still aren't showing us the deadlock in pg_locks. How does your Delphi application decide there is a deadlock? I can see how it might know a timeout has occurred, but not how it determines what any other connections to the database might be doing. You do know the difference between just waiting for locks and having a deadlock? http://en.wikipedia.org/wiki/Deadlock 2. What data are you loading, to what tables? Simple data but allways with the same username (does it make any difference ?) 3. Are there any foreign-keys or triggers we would need to know about? No. Then you don't have a deadlock. Without multiple processes trying to take the same locks in different orders you don't get deadlock. Ps: When I do not use transactions the connections does not lock to other users. You are always using transactions. There is no out-of-transaction way to execute a statement in PostgreSQL. You can't lock either a connection or users, just rows. OK - I think you'd better give a full example. Can you show us: 1. What query or queries you execute to load the data? 2. What query or queries you think are causing "deadlock"? 3. The order they execute in and what error you get. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [ADMIN] Deadlock on transaction
On Mon, Feb 12, 2007 at 03:08:27PM -0300, Ezequias Rodrigues da Rocha wrote: > I mean really deadlock. Other transactions can't access the database until > the main transaction is complete. What you describe sounds like a blocking lock: one transaction uses a resource and another has to wait until it is not locked anymore A deadlock is a special case: two transactions both need resources A and B. transaction 1 locks A, and the next moment transaction 2 locks B. Now transaction 1 waits for 2 to release B, but at the same time 2 waits for 1 to release A. This is called a deadlock, or circulair lock. Postgresql automatically detects such a situation and rolls one of them back. The client recieves a 'restartable database error' and that is exactly true: ususally another try will work. One method of fixing deadlocks is to make sure all your code accesses the database in the same order. Alphabetically is easiest to remember... -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] [ADMIN] Deadlock on transaction
Ok you teach me. Thank you and sorry. :( I just want to know why is it occuring. I am pretty sad. Ezequias 2007/2/12, Reinoud van Leeuwen <[EMAIL PROTECTED]>: On Mon, Feb 12, 2007 at 03:08:27PM -0300, Ezequias Rodrigues da Rocha wrote: > I mean really deadlock. Other transactions can't access the database until > the main transaction is complete. What you describe sounds like a blocking lock: one transaction uses a resource and another has to wait until it is not locked anymore A deadlock is a special case: two transactions both need resources A and B. transaction 1 locks A, and the next moment transaction 2 locks B. Now transaction 1 waits for 2 to release B, but at the same time 2 waits for 1 to release A. This is called a deadlock, or circulair lock. Postgresql automatically detects such a situation and rolls one of them back. The client recieves a 'restartable database error' and that is exactly true: ususally another try will work. One method of fixing deadlocks is to make sure all your code accesses the database in the same order. Alphabetically is easiest to remember...
Re: [SQL] COPY FROM query
Hi,
For what it's worth, WindowsXP (at least) seems to currently support
forward slashes at the OS level, so this Postgres behavior isn't as odd
as it might seem. If you enclose your paths with double quote (")
marks, Windows will even accept Unix style paths for some instructions
on the command line:
dir "/temp"
Feeding Windows API calls with forward slashes seems to work with
everything I've tried so far, so using them in Postgres seems perfectly
smart, and reasonable..
Hope that helps,
Steve
At 11:03 AM 2/12/2007, [EMAIL PROTECTED] wrote:
Date: Mon, 12 Feb 2007 09:09:08 +0900
From: Paul Lambert <[EMAIL PROTECTED]>
To: Joe <[EMAIL PROTECTED]>
Cc: pgsql-sql@postgresql.org
Subject: Re: COPY FROM query.
Message-ID: <[EMAIL PROTECTED]>
Joe wrote:
> Hi Paul,
>
> On Mon, 2007-02-12 at 08:19 +0900, Paul Lambert wrote:
>> I'm attempting to copy from a table into a file using a select
query
>> inside the copy.
>>
>> The following is my command:
>>
>> COPY (SELECT DISTINCT ON (dealer_id,appraisal_id) * FROM
>> appraisals_temp) TO 'c:\autodrs_appraisal_new.txt' WITH DELIMITER
AS '^'
>> CSV HEADER;
>>
>> I get the following returned:
>>
>> WARNING: nonstandard use of escape in a string literal
>> LINE 1: ...ealer_id,appraisal_id) * FROM appraisals_temp) TO
'C:\autodr...
>> ^
>> HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
>>
>> ERROR: relative path not allowed for COPY to file
>> SQL state: 42602
>>
>>
>> (The caret character is pointing to the M in FROM)
>
> I believe that on Windows you need to use double backslashes, i.e.,
'c:\
> \autodrs_appraisal_new.txt', although the regular slash may also
work,
> i.e., 'c:/autodrs_appraisal_new.txt'.
>
> Joe
>
>
> ---(end of
broadcast)---
> TIP 5: don't forget to increase your free space map settings
>
>
If this is the case, it is strange that the first copy statement works
as that is also only using a single backslash.
Having said that, if I switch it to a forward slash it works without
error... odd, given that weenblows standard is backslash.
Thanks for the help though.
--
Paul Lambert
Database Administrator
AutoLedgers
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] [ADMIN] Deadlock on transaction
On Mon, 2007-02-12 at 12:08, Ezequias Rodrigues da Rocha wrote: > I mean really deadlock. Other transactions can't access the database > until the main transaction is complete. A question: > > PostgreSQL doesn't permit multiple transactions concurrently ? Again, that's not a deadlock. A deadlock would mean that the transaction causing the lock never returned, because it wound up waiting for another transaction that was in turn waiting on it. i.e. begin transaction a begin trasnaction b a does something that waits on b b does something that waits on a neither transaction can complete. What you have is a blocking transaction. In postgresql, very few transactions tend to block other transactions. Have you got a test case that demonstrates your problem with blocking? ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] [ADMIN] Deadlock on transaction
This is delphi. I don't intent you understand but the sql actions are quite
simple (I am reading a list of numbers).
If not dm.database1.InTransaction then
dm.database1.StartTransaction;
For i:= 0 to memo1.Lines.Count - 1 do
Begin
// Catching ID
dm.qQ1.SQL.text:= Concat('select id from base.cartao where
numero = ', memo1.lines[i]);
dm.qQ1.open;
cartaoID:= dm.qQ1.fieldByName('id').asString;
// Cathing the ticket ID
dm.qQ1.SQL.clear;
dm.qQ1.SQL.text:= Concat('select nextval(',QuotedStr('
base.ingresso_id') , ')');
dm.qQ1.open;
IngressoID:= dm.qQ1.fieldByName('nextval').asString;
// $$ Recording Tickets $$
dm.Qq1.sql.text:= Concat('Insert into base.ingresso values ('
, QuotedStr(IngressoID), ',' , EstadoID, ',' , 'now()', ',' , valor, ',' ,
valor_promotor, ',' , AssentoID, ',', CaixaID, ',' , CartaoID, ',' ,
PromocaoID, ',' , SessaoID, ',' , VendedorID, ')');
// Showmessage(dm.Qq1.sql.text);
dm.Qq1.execSQL;
// ### Recording Tickets ###
// Pegando o Id do Bilhete no PostgreSQL
dm.qQ1.SQL.clear;
dm.qQ1.SQL.text:= Concat('select nextval(',QuotedStr('
base.bilhete_id') , ')');
dm.qQ1.open;
BilheteID:= dm.qQ1.fieldByName('nextval').asString;
dm.qQ1.SQL.clear;
dm.qQ1.SQL.add(Concat('Insert into base.bilhete (id, estado,
uso_sequencia, promocao_documento, assento_id, cartao_id, ingresso_id,
promocao_id, sessao_id, vendedor_venda_id )'));
dm.qQ1.SQL.add(Concat('values(', BilheteID, ',' ,
QuotedStr(EstadoID), ',' , '0,0', ',' , QuotedStr(AssentoID), ',' ,
QuotedStr(CartaoID), ',' , QuotedStr(IngressoID) , ',' ,
QuotedStr(PromocaoID), ',' , QuotedStr(SessaoID) , ',' ,
QuotedStr(VendedorID), ')' ) );
dm.qQ1.execSQL;
end;
2007/2/12, Richard Huxton :
Ezequias Rodrigues da Rocha wrote:
> 2007/2/12, Richard Huxton :
>>
>> Ezequias Rodrigues da Rocha wrote:
>> > I mean really deadlock. Other transactions can't access the database
>> until
>> > the main transaction is complete. A question:
>> >
>> > PostgreSQL doesn't permit multiple transactions concurrently ?
>>
>> PG has quite good concurrency behaviour. And "can't access the
database"
>> isn't anything to do with locking - clients should still be able to log
>> in.
>>
>> We'll need more details:
>> 1. How do you know this is a deadlock? You haven't shown us what's in
>> pg_locks, but I assume you've identified the problem there.
>
>
> I knew becouse my Delphi application reports it to me. If there is
anything
> in pg_log I could see it to you.
You still aren't showing us the deadlock in pg_locks. How does your
Delphi application decide there is a deadlock? I can see how it might
know a timeout has occurred, but not how it determines what any other
connections to the database might be doing. You do know the difference
between just waiting for locks and having a deadlock?
http://en.wikipedia.org/wiki/Deadlock
> 2. What data are you loading, to what tables?
>
> Simple data but allways with the same username (does it make any
difference
> ?)
>
> 3. Are there any foreign-keys or triggers we would need to know about?
>
> No.
Then you don't have a deadlock. Without multiple processes trying to
take the same locks in different orders you don't get deadlock.
> Ps: When I do not use transactions the connections does not lock to
other
> users.
You are always using transactions. There is no out-of-transaction way to
execute a statement in PostgreSQL. You can't lock either a connection or
users, just rows.
OK - I think you'd better give a full example. Can you show us:
1. What query or queries you execute to load the data?
2. What query or queries you think are causing "deadlock"?
3. The order they execute in and what error you get.
--
Richard Huxton
Archonet Ltd
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/
Re: [SQL] [ADMIN] Deadlock on transaction
Ezequias Rodrigues da Rocha wrote:
This is delphi. I don't intent you understand but the sql actions are quite
simple (I am reading a list of numbers).
Well, let's see - the last Pascal I did was in 1986 I think...
If not dm.database1.InTransaction then
dm.database1.StartTransaction;
For i:= 0 to memo1.Lines.Count - 1 do
Loop through your import.
Begin
// Catching ID
dm.qQ1.SQL.text:= Concat('select id from base.cartao where
numero = ', memo1.lines[i]);
dm.qQ1.open;
cartaoID:= dm.qQ1.fieldByName('id').asString;
// Cathing the ticket ID
OK, you're fetching ticket IDs one at a time.
dm.qQ1.SQL.clear;
dm.qQ1.SQL.text:= Concat('select nextval(',QuotedStr('
base.ingresso_id') , ')');
dm.qQ1.open;
IngressoID:= dm.qQ1.fieldByName('nextval').asString;
And now you've generated a new ID from a sequence.
// $$ Recording Tickets $$
dm.Qq1.sql.text:= Concat('Insert into base.ingresso values ('
, QuotedStr(IngressoID), ',' , EstadoID, ',' , 'now()', ',' , valor, ',' ,
valor_promotor, ',' , AssentoID, ',', CaixaID, ',' , CartaoID, ',' ,
PromocaoID, ',' , SessaoID, ',' , VendedorID, ')');
// Showmessage(dm.Qq1.sql.text);
dm.Qq1.execSQL;
Insert one row into "ingresso".
// ### Recording Tickets ###
// Pegando o Id do Bilhete no PostgreSQL
dm.qQ1.SQL.clear;
dm.qQ1.SQL.text:= Concat('select nextval(',QuotedStr('
base.bilhete_id') , ')');
dm.qQ1.open;
BilheteID:= dm.qQ1.fieldByName('nextval').asString;
dm.qQ1.SQL.clear;
dm.qQ1.SQL.add(Concat('Insert into base.bilhete (id, estado,
uso_sequencia, promocao_documento, assento_id, cartao_id, ingresso_id,
promocao_id, sessao_id, vendedor_venda_id )'));
dm.qQ1.SQL.add(Concat('values(', BilheteID, ',' ,
QuotedStr(EstadoID), ',' , '0,0', ',' , QuotedStr(AssentoID), ',' ,
QuotedStr(CartaoID), ',' , QuotedStr(IngressoID) , ',' ,
QuotedStr(PromocaoID), ',' , QuotedStr(SessaoID) , ',' ,
QuotedStr(VendedorID), ')' ) );
dm.qQ1.execSQL;
Insert a row into "bilhete".
end;
I don't see anything like "dm.database1.CommitTransaction" - are you
sure you are committing after the inserts?
One other point - if Delphi doesn't offer something like it, you might
find it useful to write a function that makes it easier to build queries:
my_db_func('INSERT INTO foo (id,a,b,c) VALUES (?,?,?,?)',
'NUM,TEXT,DATE,DATE',
variable1,variable2,variable3...);
I'd be surprised if there wasn't something like that already though.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
