Re: [SQL] INSERT/UPDATEs cycles and lack of phantom locking

[Florian Weimer]

* Florian Weimer:

> In addition, it occurred to me that I get the INSERT failure only if
> there is a suitable PRIMARY KEY/UNIQUE constraint on the table.  I
> haven't got that in all cases, so I need that advisory locking anyway,

It seems that LOCK TABLE ... IN EXCLUSIVE MODE does exactly what I
need: it locks out itself (and write access), but not read access to
the table.  And deadlocks are detected as well.  Yay!

-- 
Florian Weimer<[EMAIL PROTECTED]>
BFK edv-consulting GmbH   http://www.bfk.de/
Durlacher Allee 47tel: +49-721-96201-1
D-76131 Karlsruhe fax: +49-721-96201-99

---(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] Storing encrypted data?

[John Tregea]

Hi Aaron,

I removed them because when I bring the data back into my GUI, the 
returns are treated as a record break by the software and I cannot 
decrypt them or display the rest of the record that is not encrypted.


Regards

John T

Aaron Bono wrote:
On 7/19/06, *John Tregea* <[EMAIL PROTECTED] 
> wrote:


Hi Aaron,

I found that your suggestion worked well. For some reason the IDE
I use
(Revolution) put a return character every 73rd character when it
did the
base64encode, but I strip those out and there no further problems. I
don't even have to put them back later to decode it.


 
I usually leave the return characters where they are.


I am curious, why did you decide to remove the return characters?

==
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
== 


---(end of broadcast)---
TIP 6: explain analyze is your friend

[SQL] Error when trying to use a FOR loop

[Kevin Nikiforuk]

Sorry if this is in the archives, but I've done a search and couldn't find 
anything relevant. I'm running HP's precompiled version of 8.1.3.1 as part of 
their Internet Express offering, and I can't seem to run a for loop. Here's 
what I'm seeing:

xp512-0715-0716=# FOR LV in 1..10 LOOP

xp512-0715-0716-# select * from ldevrg;

ERROR: syntax error at or near "FOR" at character 1

LINE 1: FOR LV in 1..10 LOOP

^

I'm still pretty new to postgres, but based on the documentation I'm not 
picking up what I'm doing wrong. 

Help much appreciated.

Thanks,

Kevin


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[SQL] simple problem???

[ivan marchesini]

Dear users
It's a simple problem I think (and I hope  :-)

I have a table like this:
A|B|C
1|2|20
1|3|10
1|4|21
2|3|12
2|4|22
3|4|23

where the first two column are a combination without repetition (with
k=2) of the numbers 1,2,3,4
for each pair I have a value in the column C.

I would want a select that can extract these records:

1|3|10
2|3|12
3|4|23


i.e. the select must look into the A field first and: 
-select all records where A=1, 
-find, into this selection, the record where there's the minimum value
of the field C 
-print all the fields for this record.

then

-select all records where A=2
-find, into this selection, the record where there's the minimum value
of the field C 
-print all the fields of this record.

and so on...

using 
SELECT a,MIN(c) FROM table GROUP BY a

is a partial solution because I can't see the value of B
and I obtain:

1|10
2|12
3|23

How can I do for plotting also the value of B???

Thank you very much

Ivan





-- 
Ivan Marchesini
Department of Civil and Environmental Engineering
University of Perugia
Via G. Duranti 93/a 
06125
Perugia (Italy)
e-mail: [EMAIL PROTECTED]
[EMAIL PROTECTED]
tel: +39(0)755853760
fax: +39(0)755853756
jabber: [EMAIL PROTECTED]




---(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] Help with privilages please

[Hilary Forbes]

Dear All
We are running pg v 7.4.1 and importantly the database has been converted
from earlier versions of pg (6.5 I seem to recall).
I have an existing table suppliers and I have created a new user
'hilary'
REVOKE ALL on TABLE suppliers FROM hilary;
now login as hilary
SELECT * from suppliers;
and I get all the records!!!
If I create a **new** table though and then do the above, the permissions
work I get a polite message telling me "no go".  This
sounds to me like a problem with earlier compatibility.  Is there a
way I can overcome this.  A simple dump/restore does not solve the
problem.
Many thanks
Hilary

Hilary Forbes
DMR Limited (UK registration 01134804) 
A DMR Information and Technology Group company
(www.dmr.co.uk)

Direct tel 01689 889950 Fax 01689 860330 
DMR is a UK registered trade mark of DMR Limited
**

Re: [SQL] Help with privilages please

[Stephan Szabo]

On Thu, 20 Jul 2006, Hilary Forbes wrote:

> Dear All
>
> We are running pg v 7.4.1 and importantly the database has been
> converted from earlier versions of pg (6.5 I seem to recall).
>
> I have an existing table suppliers and I have created a new user 'hilary'
>
> REVOKE ALL on TABLE suppliers FROM hilary;
>
> now login as hilary
> SELECT * from suppliers;
> and I get all the records!!!

This probably means that "public" also has rights on suppliers (and thus,
the user still has access through the public permissions). You can
probably get around this by revoking the public rights and granting rights
explicitly to the users that should have rights.


---(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] Error when trying to use a FOR loop

[Stephan Szabo]

On Thu, 20 Jul 2006, Kevin Nikiforuk wrote:

> Sorry if this is in the archives, but I've done a search and couldn't
> find anything relevant. I'm running HP's precompiled version of 8.1.3.1
> as part of their Internet Express offering, and I can't seem to run a
> for loop. Here's what I'm seeing:
>
> xp512-0715-0716=# FOR LV in 1..10 LOOP
>
> xp512-0715-0716-# select * from ldevrg;
>
> ERROR: syntax error at or near "FOR" at character 1
>
> LINE 1: FOR LV in 1..10 LOOP

I think the problem is that the FOR is a pl/pgsql construct and isn't
allowed in straight sql contexts but only inside a function.



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Error when trying to use a FOR loop

[Richard Broersma Jr]

> Sorry if this is in the archives, but I've done a search and couldn't find 
> anything relevant.
> I'm running HP's precompiled version of 8.1.3.1 as part of their Internet 
> Express offering, and
> I can't seem to run a for loop. Here's what I'm seeing:
> xp512-0715-0716=# FOR LV in 1..10 LOOP
> xp512-0715-0716-# select * from ldevrg;
> ERROR: syntax error at or near "FOR" at character 1
> LINE 1: FOR LV in 1..10 LOOP

The following is give examples of control structures.  However,  you need to be 
sure that your
version of postgresql has a procedural language installed.  If not, you will 
need to install it.

http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS

Use can use createlang to add different languages to postgresql.
http://www.postgresql.org/docs/8.1/interactive/sql-createlanguage.html

The following link gives a shot list of languages that you can use.  There are 
others however:
http://www.postgresql.org/docs/8.1/interactive/server-programming.html

see Server-side Procedural Languages from:
http://www.postgresql.org/download/

Hope this help.

Regards,
Richard Broersma Jr.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Help with privilages please

[Richard Broersma Jr]

REVOKE ALL on TABLE suppliers FROM hilary;
now login as hilary
SELECT * from suppliers;
and I get all the records!!!
If I create a **new** table though and then do the above, the permissionswork I 
get a polite
message telling me "no go".  Thissounds to me like a problem with earlier 
compatibility.  Is there
away I can overcome this.  A simple dump/restore does not solve theproblem.


You might also have to revoke all from public:

Regards,
Richard Broersma Jr.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Error when trying to use a FOR loop

[George Weaver]

- Original Message From: "Kevin Nikiforuk"




xp512-0715-0716=# FOR LV in 1..10 LOOP




It appears that you are trying to use the For .. Loop structure from the 
psql command line.  This structure is not plain SQL - its meant to be used 
within PL/pgSQL.  Have you tried incorporating it into a PL/pgSQL function?


See 
http://www.postgresql.org/docs/8.1/static/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS


Regards,
George 




---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] simple problem???

[Richard Broersma Jr]

> A|B|C
> 1|2|20
> 1|3|10
> 1|4|21
> 2|3|12
> 2|4|22
> 3|4|23
> 1|3|10
> 2|3|12
> 3|4|23
> -select all records where A=1, 
> -find, into this selection, the record where there's the minimum value
> of the field C 
> -print all the fields for this record.

maybe this will work;

select a,b,c
from table as T1
join (select a, min(c) as minc from table group by a) as T2
on (t1.a = t2.a) and (t1.c = t2.c)
;

Regards,

Richard Broersma Jr.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Help with privilages please

[Tom Lane]

Hilary Forbes <[EMAIL PROTECTED]> writes:
> I have an existing table suppliers and I have created a new user
> 'hilary'
> REVOKE ALL on TABLE suppliers FROM hilary;
> now login as hilary
> SELECT * from suppliers;
> and I get all the records!!!

Most likely there's been a grant of (at least) select privilege to PUBLIC.
You'll need to revoke that if you don't want every user to have that
privilege implicitly.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend