Re: [GENERAL] Duplicate Row Removal

[Dean Gibson (DB Administrator)]

CREATE TABLE new_name AS SELECT DISTINCT * FROM old_name;

DROP TABLE old_name;

ALTER TABLE new_name RENAME TO old_name;

On 2005-11-04 17:15, Peter Atkins wrote:

All,

I have a duplicate row problem and to make matters worse some tables don't have a PK or any unique identifier. 


Anyone have any thoughts on how to remove dups?



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

Re: [GENERAL] Duplicate Row Removal

[Tom Lane]

"Peter Atkins" <[EMAIL PROTECTED]> writes:
> I was hoping to have a system oid for each row but it looks like that's
> not the case. Anyone have any thoughts on how to remove dups?

ctid always works ...

regards, tom lane

---(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

[GENERAL] Duplicate Row Removal

[Peter Atkins]

All,

I have a duplicate row problem and to make matters worse some tables
don't have a PK or any unique identifier. See below:

uid | cleanval | timestamp  | received
-+---++--
  38 | 5 | 1125081799 |1
  38 |14 | 1122683252 |0
  38 | 5 | 1125081799 |1
  38 |14 | 1122683252 |0


I was hoping to have a system oid for each row but it looks like that's
not the case. Anyone have any thoughts on how to remove dups? I have
about 40 tables of various sizes.

Cheers,
Peter



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

   http://archives.postgresql.org

Re: [GENERAL] Changing ids conflicting with serial values?

[Marc Boucher]

At 11:49 04/11/2005 -0500, Alex Turner wrote:
> I think he meant
>
> create sequence test_seq;
> select setval('test_seq',(select max(primary_key_id) from my_table));
>
> not max value of a serial type.

What I understand, and from what I know by using mysql, is that mysql
auto-adjust the max value of a serial.
Something like :
 - a table with 5000 elements with ids from 1-5000.
 - if you update the id in one of the rows and set it to 65000, mysql
   updates the serial current value.
 - even if you delete, or change the id back to its previous value, the
   current value will still be 65000.
 - a new inserted row will have the id 65001.

Now assuming the id's maximum value is 65535, and you set one of the rows to
this value, mysql will be unable to find a "nextval" greater than 65535. New
inserts will fail.
I don't know if it is still the case with recent versions of mysql, but that's
what I discovered while testing a web application.

Just to say that even mysql has its problems when a user plays with serial PK.


-- 
Marc

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

Re: [GENERAL] Replicating databases

[Christopher Browne]

>> But if someone decided to "fork" their own *new* project, perhaps
>> starting based on one of the releases, that would an entirely
>> interesting idea.
>
> Wouldn't async multimaster make use of most all of what slony-I
> currently has? ISTM that it would make life a lot easier to use one
> combined project rather than two...

When you combine projects, you require the participants to participate
in the union of the complexity of the projects.  The project can't
generate releases unless they all coordinate a release, and if their
interests differ, that can be tough to do...  There are OSes we could
name where increasing sets of participants are having that very
effect...

If projects remain largely independent, they can limit themselves to
their respective individual sets of complexities.  That's precisely
why the PostgreSQL project is trying to push as many of the "contrib"
things out to outside projects as possible.

There's a famous saying about "sufficient to the day is the evil
thereof;" we might substitute "project" for "day" in that ;-).
-- 
(reverse (concatenate 'string "moc.liamg" "@" "enworbbc"))
http://linuxfinances.info/info/wp.html
"Whenever you  find that you  are on the  side of the majority,  it is
time to reform." -- Mark Twain

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

Re: [GENERAL] pl/pgsql list as parameter.

[David Gagnon]

Hi,

Here is an example

Regards
/David

CREATE OR REPLACE FUNCTION 
usp_Inventaire_Transaction_Statut_Changer(int[], varchar, int, date) 
RETURNS INTEGER AS '

DECLARE
requestIds ALIAS FOR $1;
companyId ALIAS FOR $2;
targetStatus ALIAS FOR $3;
transactionDate ALIAS FOR $4;
transactionDate_ timestamp;
inventoryTransaction IR%ROWTYPE;
temp RECORD;
itemIds varchar[]:= ''{}'';
BEGIN




IF ( transactionDate IS NOT NULL) THEN
-- Si la date de requˆte est vide ou si c est la date du jour
IF ( date_trunc(''day'', transactionDate) = CURRENT_DATE )
THEN
transactionDate_ := CURRENT_TIMESTAMP;
ELSE
transactionDate_ := transactionDate;
END IF;


Assa
Assad Jarrahian wrote:


Hi,
I have a couple questions, I am tryingto write a function that takes 
as input a list (size being dynamic) of primaryIDKeys, along with a 
userdefined type and returns a set of rows containing those keys. 
Furthermore the rows are exactly (columns) like the table that 
contains the keys, but has an extra field.((so I define my own tupe)


CREATE FUNCTION somefunc(,my_predef_type ) 
RETURNS SETOF tp_lm_object  AS '
DECLARE

..

How do you take in a list of int? And how would one loop through that?


Much thanks in advance.

-assad




---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] pl/pgsql list as parameter.

[Tom Lane]

Assad Jarrahian <[EMAIL PROTECTED]> writes:
> How do you take in a list of int?

Use an array.

regards, tom lane

---(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: [GENERAL] Save prepared plan...

[Jim C. Nasby]

On Thu, Nov 03, 2005 at 03:22:34PM -0600, Cristian Prieto wrote:
> Save globally for all session or between sessions.
> 
> Well, I have a set of queries which runs several times. My clients connect
> and execute it. But each time the client connects and disconnects, that
> means that I loose the execution plan. I would like the keep the execution
> plan between sessions that would improve the performance of the execution of
> my application.

If you're connecting and disconnecting frequently you probably want to
be using pgpool. Even if you're not, it would (mostly) satisfy what
you're trying to do here.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

Re: [GENERAL] Replicating databases

[Jim C. Nasby]

On Thu, Nov 03, 2005 at 10:29:56PM -0500, Christopher Browne wrote:
> > On Wed, Nov 02, 2005 at 03:44:26PM -0800, Marc Munro wrote:
> >> experts there may suggest a better solution.  I have seen talk of
> >> disabling the standard slony triggers to allow this sort of thing but
> >> whether that is more or less nasty is questionable.
> >
> > FWIW, I don't think that's the question; it's more like whether it'd
> > be merely horribly nasty or likely to break in unexpected and really
> > painful ways. ;-)  But the discussion around that surely should move
> > to the Slony list.
> 
> It seems to me that lots of the "stuff" in Slony-I could be reapplied
> to _try_ to create an asynchronous multimaster replication system.
> 
> A *major* addition would need to be some form of "conflicts queue."
> 
> That's the sort of thing they have in the analagous "O-word"
> replication system.
> 
> What's a non-starter is to try to reshape the Slony-I project into
> "async multimaster."  That would get considerable push-back :-).
> 
> But if someone decided to "fork" their own *new* project, perhaps
> starting based on one of the releases, that would an entirely
> interesting idea.

Wouldn't async multimaster make use of most all of what slony-I
currently has? ISTM that it would make life a lot easier to use one
combined project rather than two...
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

   http://archives.postgresql.org

Re: [GENERAL] Image File System Question

[Jan Peterson]

We have a similar functional requirement to Vishal's, but with an
added twist.  Currently, we are utilizing the pg_largeobject table to
store context data delivered through our java application.  We are
finding, however, that very large pg_largeobject tables degrade our
performance in other areas (primarily VACUUM) due to I/O limitations
of our hardware (an appliance-like device).  We've been experimenting
with customized functions that provide similar access mechanisms as
are available with large object support.  Here are some examples of
what we've been experimenting with:

CREATE OR REPLACE FUNCTION fileread(varchar, varchar, int8, int8)
  RETURNS bytea AS
$BODY$
my ($type, $hash, $offset, $length) = @_;
my $file = $_SHARED{filename}->($type, $hash);
my $data;
my $fh;

# some argument validation code removed here

if (! open($fh, "< $file")) {
elog(ERROR, "unable to open $file: $!");
}
binmode $fh;

if ((defined($offset)) and ($offset > 0)) {
if (! sysseek($fh, $offset, 0)) {
elog(ERROR, "can't seek to pos $offset in $file: $!");
}
}

my $numread = sysread($fh, $data, $length);
if (! defined($numread)) {
elog(ERROR, "unable to read $file: $!");
}

close($fh);
return undef if $numread == 0;

# here is where it gets ugly due to the way we have to munge
# the data coming back from perl -> postgres

$data =~ s/([\0\\\'])/sprintf("%03o",ord($1))/ge;
return($data);
$BODY$
  LANGUAGE 'plperlu' VOLATILE;

There is also a function that sets up the %_SHARED hash to hold some
utility functions (filename is one of them, whose job it is to convert
the hash ... a.k.a. filename ... to a full path containing
sub-directories).  A similar function filewrite() is also used to
handling paging the data into the filesystem.

Problems we've encountered with this mechanism are: the evil
substitution required to quote the bytea value being returned from the
function (because we potentially have binary data including null
characters, single quotes, and backslashes), the lack of persistance
of %_SHARED (fixed in 8.1, I think, but we're using 8.0.2 + some local
patches), and the limitations of the underlying filesystem (many of
our objects are < 4k in size, but the linux ext3 filesystem we're
using has no support for storing multiple fragments or tail fragments
in a single data block, so the minimum file size on the filesystem is
4k, which kills us).

We're looking now at re-writing this stuff in C and storing the tail
fragment < 4k of the data stream directly in a bytea column in our
main table.  Still no idea if this will really solve our VACUUM
problems, but it's the best thing we've been able to come up with so
far.  Another drawback, of course, is the lack of transactional
security of this externally stored data (what if we have a statement
that does a select deletefile('type', 'hash'); and then it needs to
roll back?  answer: we're hosed).  I'd be happy to hear any
suggestions for solutions to the above problems.

-jan-
--
Jan L. Peterson
<[EMAIL PROTECTED]>

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

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

[GENERAL] I just can't get it:record_in, record_out(I know i try to do something postgres is not developed for but there are many 'buts')

[Peter Filipov]

This is what i can't get right:


CREATE type tst_tst as (a integer, b text);

CREATE OR REPLACE FUNCTION tst_store(a anyelement) RETURNS text AS $$
BEGIN
RETURN textin(record_out(a));
END
$$ LANGUAGE PLPGSQL;


create or replace function tst_load1(a text) returns tst_tst as $$
declare
b tst_tst;
c text;
begin
c := record_in(textout(a), 'tst_tst'::regtype::oid, 0);
c := 'select row'  || c || '::tst_tst';
raise notice 'boza:%', c;
execute c into b;
return b;
end
$$ language plpgsql;

Somebody help please

--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Division

[Tino Wildenhain]

Am Freitag, den 04.11.2005, 12:13 -0500 schrieb Robert Fitzpatrick:
> I am having a problem gettig a percent via division. Below is the first
> part of my trigger function where pct returns 0.00, instead of the
> expected 0.50. If I try without dimensions to the numeric variable, I
> just get 0. What is the correct way to accomplish the percent?
> 
> CREATE OR REPLACE FUNCTION "public"."issue" (varchar) RETURNS numeric
> AS'
> DECLARE
>   repar text[];
>   pct numeric(3,2);
>   noreps integer;
> 
> BEGIN
>   repar := string_to_array($1,''-'');
>   noreps := array_upper(repar,1);
>   pct := 1/noreps;

you have noreps integer, 1 is integer too so division almost
every time will result in 0. (Unless noreps is 0)
Then 0 will be casted to numeric(3,2) which is 0.00.

>   RETURN pct;
> END;
> 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
> 
> bachman=# select issue('AA-BB');
>  issue
> ---
>   0.00
> (1 row)

Btw, apart from the integer problem you are facing, what is
the whole point of that function (when it finally 'works')? 

Regards
Tino


---(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

[GENERAL] Missing seconds in a date (timestamp)

[Eliézer Madeira de Campos]

Hello everyone:
 
I'm working to upgrade a database from PostgreSQL 
7.3 to 8.0 version.The database serves a complex J2EE application, running 
Jboss in multiple servers. All servers run Linux RedHat.I found a problem 
yesterday.Whenever the application inserts a date prior to 1914-01-01, the 
actual date written in the database is different. For instance:'1900-01-01' 
is stored as '1899-12-31 23:59:32'. The column datatype is timestamp without 
timezone.
 
If I run the command directly against the 
database (psql) the date is stored correctly. The weird thing is that we tried 
to debug the JDBC driver and it seems to be sending the correct information to 
the database.
 
Any ideas?
 
Eliézer Madeira de Camposeliezer (at) 
diuno.com.br
 

Re: [GENERAL] BLOB and OID

[vishal saberwal]

yes, there is one, but i dont know how would you modify the PostgreSQL code.

You can implement a GUID datatype and can use it as indexing which guarantees uniqueness and can be stored in double format.

thanks,
vishOn 11/4/05, Lolke B. Dijkstra <[EMAIL PROTECTED]> wrote:
Hi,OID being a 4 byte int seems limited to indexing for binary objects.More precisely I use BLOB to store images in the database and link theseobjects to another table using the OID as FK.If I run out of OID there will be no way to index new images. Of course
when not automatically creating OID for all objects in the database youwill not easily run out of index values, but still..Is there an alternative way of indexing BLOB? Or can I extend the rangeof OID?
Lolke---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Division

[Robert Ftizpatrick]

On Fri, 2005-11-04 at 18:39 +0100, Tino Wildenhain wrote:
> > CREATE OR REPLACE FUNCTION "public"."issue" (varchar) RETURNS numeric
> > AS'
> > DECLARE
> >   repar text[];
> >   pct numeric(3,2);
> >   noreps integer;
> > 
> > BEGIN
> >   repar := string_to_array($1,''-'');
> >   noreps := array_upper(repar,1);
> >   pct := 1/noreps;
> 
> you have noreps integer, 1 is integer too so division almost
> every time will result in 0. (Unless noreps is 0)
> Then 0 will be casted to numeric(3,2) which is 0.00.
> 
> >   RETURN pct;
> > END;
> > 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
> > 
> > bachman=# select issue('AA-BB');
> >  issue
> > ---
> >   0.00
> > (1 row)
> 
> Btw, apart from the integer problem you are facing, what is
> the whole point of that function (when it finally 'works')? 
> 

Thanks, of course, can't see for the confusion :( 

It is part of a trigger that breaks apart an incoming CSV field by
dashes into an array and inserts the percent of the transaction that
belongs to each element of the array. So, if there are 2 elements, each
get 50%, if 4, then they will get 25%, so on.

Thanks again...

--
Robert


---(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: [GENERAL] Image File System Question

[vishal saberwal]

thanks for all your prompt reponses but i am still not clear with the way to solve the problem.I am sorry if i wasn't clear with my problem the first time.

My problem isn't file system and the way i lay it out. 
What i need to know is a way to stream out external resource files via  stored procedure calls.

Steps: 
(1) User calls stored procedure ReadImage(ImageID).
(2) Stored procedure reads the Image table to find the location (field) for the given ID.
(3) Now this stored procedure ("HOW TO?") opens the file.
(4) Then it "Streams" it out to the User (as if it was a BLOB).
(5) For User its just like a lo_export but he has no clue if its stored in database or a seperate resource file system.

Resource file system can be accessed only through the database, where
we will check for permissions if we need to (though i know i can
implement permissions in the file systems as well).

What is the best way to do Steps (3) and (4) above?

thanks,
vish
(vishal saberwal)
On 11/4/05, Douglas McNaught <[EMAIL PROTECTED]> wrote:
vishal saberwal <[EMAIL PROTECTED]> writes:> HFS is the filesystem richard was tlking about. I am not familiar with> this file system but i guessed it was HFS+ that he was talking about.
> yes, i understand all modern filesystems are Hirearchical file systems.Oh, OK--reading your original message, you just want to store the datafiles in a hierarchical arrangement of some sort.-Doug

[GENERAL] pl/pgsql list as parameter.

[Assad Jarrahian]

Hi,
  I have a couple questions, I am tryingto write a function that
takes as input a list (size being dynamic) of primaryIDKeys, along with
a userdefined type and returns a set of rows containing those keys.
Furthermore the rows are exactly (columns) like the table that contains
the keys, but has an extra field.((so I define my own tupe)

CREATE FUNCTION somefunc(,my_predef_type ) RETURNS SETOF tp_lm_object  AS 'DECLARE
..

How do you take in a list of int? And how would one loop through that?


 Much thanks in advance.

-assad

Re: [GENERAL] Image File System Question

[Csaba Nagy]

See my comments below.

On Fri, 2005-11-04 at 15:24, vishal saberwal wrote:
> thanks for your response,
> 
> We are kind of jailing (may be hiding would be a better term)
> resources behind the database/Stored procedures and GUI needs to have
> a feel as if the data is comming from database.
> Its a requirement for the project that any communication of resources
> and data be done through stored procedures/function calls.
> What other options do you suggest.

Hmm... this sounds like an application server is what you need. The app
server is the interface to the user, and should get the data from the
file system or data base or whatever else. Now some data base vendors
would argue that you can do all what you need with the DB and put all
code into it, but I guess that's just not true (there are more efficient
ways to store some data than a data base, and a web server will
definitely serve you files faster than any DB), and an efficient setup
will need a separate application server in front of the data base. For
one thing, it is a lot easier to cluster/scale the app server than the
data base, and you can use it to integrate multiple data sources. IMHO,
using the data base as an app server is just a bad choice.

> Did try finding something about HFS for ext3/xfs but in vain. The
> information i found was the conversion between these file systems.
> 
> There are not just the Users but different devices and applications as
> clients that would talk to the database and may not require the web
> servers.

If you're coding your clients, then you can code them to access any
given API, including HTTP, or even some proprietary API/protocol you
design. HTTP is not made only for humans, and I guess is as easy to
write code which accesses data through HTTP as it would writing code
accessing any of the postgres client APIs. Or maybe it's not that easy,
but not a lot harder.

[snip]

HTH,
Csaba.



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

   http://archives.postgresql.org

Re: [GENERAL] Division

[Peter Eisentraut]

Robert Fitzpatrick wrote:
> I am having a problem gettig a percent via division. Below is the
> first part of my trigger function where pct returns 0.00, instead of
> the expected 0.50. If I try without dimensions to the numeric
> variable, I just get 0. What is the correct way to accomplish the
> percent?

You are dividing an integer by an integer, and if the first integer is 1 
then the result is often 0.  You need to use numeric for your noreps 
variable.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(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: [GENERAL] Changing ids conflicting with serial values?

[Jerry Sievers]

Steven Brown <[EMAIL PROTECTED]> writes:

> I'm granting access to insert/update/delete rows of a table to people, 
> but I don't want all future inserts to fail if they decided to change an 
> id (which they obviously shouldn't, but they /can/).  It makes for a 
> fragile system.

create rule no_pkey_update
as on update to foo where new.id != old.id
do instead nothing;

Simple minded solution with negative aspect that it will silently skip
ANY update trying to change pkey... other changes to record also
discarded.

> Should I just be using some sort of trigger to block them from modifying 
> the id, or is there another way to handle it?  I.e., how do people 
> normally handle that?  It's a migration thing - MySQL prevented this 
> situation due to the way it handles auto_increment (it will never assign 
> you an id that already exists).

Bit more complex but still easy is trigger to just always set new.id
to old.id thereby insuring that it can't be changed.

create function no_pkey_update()
returns trigger
as '
begin
new.id = old.id;
return new;
end'
language plpgsql;

HTH


-- 
---
Jerry Sievers   305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobilehttp://www.JerrySievers.com/

---(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

[GENERAL] Division

[Robert Fitzpatrick]

I am having a problem gettig a percent via division. Below is the first
part of my trigger function where pct returns 0.00, instead of the
expected 0.50. If I try without dimensions to the numeric variable, I
just get 0. What is the correct way to accomplish the percent?

CREATE OR REPLACE FUNCTION "public"."issue" (varchar) RETURNS numeric
AS'
DECLARE
  repar text[];
  pct numeric(3,2);
  noreps integer;

BEGIN
  repar := string_to_array($1,''-'');
  noreps := array_upper(repar,1);
  pct := 1/noreps;
  RETURN pct;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

bachman=# select issue('AA-BB');
 issue
---
  0.00
(1 row)



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

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

Re: [GENERAL] Changing ids conflicting with serial values?

[Alex Turner]

I think he meant

create sequence test_seq;
select setval('test_seq',(select max(primary_key_id) from my_table));

not max value of a serial type.

Alex

On 11/3/05, Marc Boucher <[EMAIL PROTECTED]> wrote:
> On Wed, 02 Nov 2005 19:29:10 -0800, you wrote:
>
> >It's a migration thing - MySQL prevented this
> >situation due to the way it handles auto_increment (it will never assign
> >you an id that already exists).
>
> AFAIK, in mysql, if you modify a serial by setting it to the max value for
> this type, mysql will fail all new inserts.
>
>
> ---(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
>

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Bug in Role support?

[Tom Lane]

"Florian G. Pflug" <[EMAIL PROTECTED]> writes:
> grant admin to fgp ;

> The last statement fails with "role "admin" is a member of role "fgp" - 
> but I believe it is not.

> Did I do something wrong, or is this really a bug?

Looks like a bug to me too.  I think it's coming from the fact that
is_member_of_role() thinks superusers are members of every role
ex officio ... which is true for permission checking purposes but
we don't want that rule applied here.  Will fix.

regards, tom lane

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

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

[GENERAL] Bug in Role support?

[Florian G. Pflug]

Hi

I wanted to try out the new role support in 8.1. My goal is to create
two roles, dev and admin, and make all other users member of those two
roles. The users shall then issue either "set role dev" (if the want
to to development work), or "set role admin" (in the rare case where
they need superuser access).

I did the following:

create role dev with nosuperuser nocreaterole createdb noinherit nologin ;
create role admin with superuser noinherit nologin ;
create role fgp with nosuperuser nocreaterole nocreatedb noinherit login ;
grant dev to fgp ;
grant admin to fgp ;

The last statement fails with "role "admin" is a member of role "fgp" - 
but I believe it is not.


pg_auth_members looks like this:
 roleid | member | grantor | admin_option
++-+--
  16391 |  16393 |  10 | f

16391 is dev, 16392 is admin, 16393 is fgp

So, "admin" isn't even mentioned in pg_auth_members..

Did I do something wrong, or is this really a bug?

greetings, Florian Pflug


smime.p7s
Description: S/MIME Cryptographic Signature

Re: [GENERAL] Oracle 10g Express - any danger for Postgres?

[Bob]

On 11/1/05, Tom Lane <[EMAIL PROTECTED]> wrote:
> Jan Wieck <[EMAIL PROTECTED]> writes:
> > On 10/31/2005 1:14 PM, Chris Browne wrote:
> >> The fact that it appears "a joke" to people wanting to deploy big
> >> databases doesn't prevent it from taking a painful bite out of, oh,
> >> say, certain vendors that forgot to own their own transactional
> >> storage engine...
>
> > It's not a joke. It fits exactly the "small web application" needs. Who
> > will want to pay for a commercial MySQL license when they can run Oracle
> > for free?
>
> People who can't figure out how to configure Postgres are not likely to
> get far with Oracle ;-).  Unless Oracle has made some *huge* strides in
> ease of installation/administration with 10g, I see this making
> practically no dent in MySQL.  Or PG for that matter.  All they're
> really likely to accomplish is to cannibalize some of their own low-end
> sales.
>
>   regards, tom lane
>
> ---(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
>
Well to be fair, Oracle 10g Express is easy to install and admin.
Basically you don't have to do any admin work and installing is as
hard as clicking next 3 or 4 times.
To me the only really nice thing Oracle has at this time is called
HTML DB that provides a semi easy development tool that hooks into
Oracle very easily. No need to write glue code such as connections and
state as the dev tool provides all this.

With that being said those of us who know better will not take that
over Postgresql, but it will buy Oracle more market share that is for
sure.

Bob

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] tablename.columnname notation for columnnames of joined tables

[dgroth]

Hello,

I know it is possible in other dbs like sqlite.

Let's say I have two tables which I want to join owners (name,age) and
dogs (name,owner,age).

select * from owners as a join dogs as b on a.name = b.owner

How could I convince postgres to prefix the returned  columnnames with
the tablename like owners.name, dogs.name etc.

I need this for a python and tcl interface and if only name is
retrieved as columnname, then the last value is assigned to the
hash/dictionary entry "age". So the owner gets the age of his dog :(
and even its name :(( . Hoe you get the point

I know that I can use fully qualified columnames instead of "select *"
but this is very annoying if you have many columns and you nest your
statements.

Is there a solution ?

regards, Detlef


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

[GENERAL] BLOB and OID

[Lolke B. Dijkstra]

Hi,

OID being a 4 byte int seems limited to indexing for binary objects. 
More precisely I use BLOB to store images in the database and link these 
objects to another table using the OID as FK.
If I run out of OID there will be no way to index new images. Of course 
when not automatically creating OID for all objects in the database you 
will not easily run out of index values, but still..
Is there an alternative way of indexing BLOB? Or can I extend the range 
of OID?


Lolke

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Image File System Question

[Douglas McNaught]

vishal saberwal <[EMAIL PROTECTED]> writes:

> HFS is the filesystem richard was tlking about. I am not familiar with
> this file system but i guessed it was HFS+ that he was talking about.
> yes, i understand all modern filesystems are Hirearchical file systems.

Oh, OK--reading your original message, you just want to store the data
files in a hierarchical arrangement of some sort.

-Doug

---(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: [GENERAL] Image File System Question

[Richard Huxton]

vishal saberwal wrote:
HFS is the filesystem richard was tlking about. I am not familiar with 
this file system but i guessed it was HFS+ that he was talking about.

yes, i understand all modern filesystems are Hirearchical file systems.


Ah, you said (H)ierarchical (F)ile (S)ystem in the original message 
(note the capitals). Since I'm a long-term Macintosh owner, I took it as 
a reference to the Apple-Mac filesystem. In fact you just meant a 
filesystem (since they are all hierarchical as Doug said).


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] how to emit line number in a function?

[Bricklen Anderson]

Jerry Sievers wrote:
> Bricklen Anderson <[EMAIL PROTECTED]> writes:
> 
> 
>>I couldn't find any useful references in the docs or archives for emitting the
>>line number of a plpgsql function (in a RAISE statement). I'd like to use it 
>>for
>>debugging some complex functions.
>>Does anyone have any tips on where to look, or an example of this?
> 
> 
> Have a look at the m4 macro processor
> 
> changequote({,})dnl
> define({func_body},{$$begin
>   raise exception 'I barfed on line #__line__';
> end$$})dnl
> 
> create function some_func()
> returns whatever
> as func_body
> language plpgsql;
> 
> This can be useful sometimes... but may ADD to your debugging
> headaches if not used artfully!
> 
> HTH
> 
I'll look into that, thanks for the suggestion.


Cheers,

Bricklen

-- 
___

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
___

---(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: [GENERAL] Image File System Question

[vishal saberwal]

HFS is the filesystem richard was tlking about. I am not familiar with
this file system but i guessed it was HFS+ that he was talking about.
yes, i understand all modern filesystems are Hirearchical file systems.

thanks,
vishOn 11/4/05, Douglas McNaught <[EMAIL PROTECTED]> wrote:
vishal saberwal <[EMAIL PROTECTED]> writes:> Did try finding something about HFS for ext3/xfs but in vain. The> information i found was the conversion between these file systems.
What exactly do you mean by HFS?  All modern filesystems arehierarchical.-Doug

Re: [GENERAL] Image File System Question

[Douglas McNaught]

vishal saberwal <[EMAIL PROTECTED]> writes:

> Did try finding something about HFS for ext3/xfs but in vain. The
> information i found was the conversion between these file systems.

What exactly do you mean by HFS?  All modern filesystems are
hierarchical.

-Doug

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Image File System Question

[vishal saberwal]

thanks for your response,

We are kind of jailing (may be hiding would be a better term) resources
behind the database/Stored procedures and GUI needs to have a feel as
if the data is comming from database.
Its a requirement for the project that any communication of resources and data be done through stored procedures/function calls.
What other options do you suggest.

Did try finding something about HFS for ext3/xfs but in vain. The
information i found was the conversion between these file systems.

There are not just the Users but different devices and applications as
clients that would talk to the database and may not require the web
servers.

I would appreciate if someone can share if they have done sommething similar or if someone can point me to the right resource.

pgfoundry does talk about something with Npgsql but thats a different
layer. Some devices and applications may not go through that layer.

Thanks,
vishOn 11/4/05, Richard Huxton  wrote:
vishal saberwal wrote:> hi,>> My server is Postgres 8.0.1 on fedora core2.> My clients are remote and interface with my server using .NET GUI.>> We are trying to store many images/icons/audio/video clips in our system.
> We expect many of these. The way we are doing it is using Hierarchical> File System.HFS is an old Apple Macintosh filesystem - an unlikely choice. You'llprobably find it's ext3.> I understand we need to limit the size of these directories by
> controlling number of resources in each directory.Less important nowadays than it used to be, but usually done via hashingthe filename. So, file 123456.gif is stored in 1/2/3/123456.gif> The way i want to let anone access these resources is only through
> stored procedures rather than direct downloading.Why?What are the benefits of this system?> I am trying to create stored procedure/function API for the same. These> functions will check for the permissions for the user and/or the file,
> check the location from the schema and then would "stream" it out for> the GUI to use.Why not just set up a webserver and get it to authenticate to your database?> The question is:
> (1) How do i use the database stored procedures/functions as a tunnel> for just streaming the data rather than storing it in database?> That is, a function that given a Image ID for example, will read the
> location from the table and then just go to the location and stream out> the bits.> (2) Am i right in saying that it can't solely be done iwth plpgsql but> would need somem c/c++ api.
Any of the "untrusted" languages (which of course includes "C"). Fileaccess will be different in each of course - pick whichever you are mostfamiliar with. The key differences between a "trusted" and "untrusted"
version of a language are:1. Untrusted languages can access the rest of the system2. Functions in untrusted languages can only be added by a superuser.> (3) Has anyone here done something like this and can share with me how
> he/she implemented this.>> I did do my homework of googling for something like this but may be my> search skills were not strong enough to find some substantial> information/HOW TOs or examples.
Well, there is the "procedural languages" section of the manuals. Itmight also be worth checking on pgfoundry to see if there is anythinguseful there.--   Richard Huxton   Archonet Ltd

Re: [GENERAL] how to emit line number in a function?

[Jerry Sievers]

Bricklen Anderson <[EMAIL PROTECTED]> writes:

> I couldn't find any useful references in the docs or archives for emitting the
> line number of a plpgsql function (in a RAISE statement). I'd like to use it 
> for
> debugging some complex functions.
> Does anyone have any tips on where to look, or an example of this?

Have a look at the m4 macro processor

changequote({,})dnl
define({func_body},{$$begin
raise exception 'I barfed on line #__line__';
end$$})dnl

create function some_func()
returns whatever
as func_body
language plpgsql;

This can be useful sometimes... but may ADD to your debugging
headaches if not used artfully!

HTH

-- 
---
Jerry Sievers   305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobilehttp://www.JerrySievers.com/

---(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

[GENERAL] Using native win32 psql.exe using alternative cygwin terminal

[Richard van den Berg]

I just installed postgresql 8.1-rc1 on a Windows 2003 machine. Since I
like psql a lot, and hate the cmd.exe terminal, I use puttycyg instead.

When I tried using psql.exe in a puttycyg terminal, after entering my
password for the database, the screen just "hangs". When I do the same
from a cygwin cmd.exe terminal, everything works. Another detail: the
password using cmd.exe is hidden, using puttycyg it is shown.

The exact same behaviour is shown when using the OpenSSH daemon (under
cygwin) and logging in to it from remote (putty client or OpenSSH client).

It looks like there is a problem with the interactive mode. When I run:

psql -d postgres -c 'select * from pg_tables limit 1;'

the output is shown just fine. I tried different TERM settings, but they
made no difference.

Is there something I can do to make this combination work? I'd gladly do
more tests..

Sincerely,

-- 
Richard van den Berg, CISSP
---
Trust Factory B.V. | www.dna-portal.net
Bazarstraat 44a|  www.trust-factory.com
2518AK The Hague   |  Phone: +31 70 3620684
The Netherlands|  Fax  : +31 70 3603009
---

---(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

[GENERAL] records to text representation

[Peter Filipov]

Is there any way to covert an instance of a composite type to text,
to store it in a text column, and then to recreate the original object?
For example:

create type test as (a integer, b text);
create tble tstore (s text);

insert into tstore values((someproc((row(1, 'tst'))::test))::text);
select (someproc(s))::test from tstore;

BTW, I succeeded to create an array of composite types althought I think
i met somewhere in the docs that these are still not supported.
I did it by fiddling with the system catalogs.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] SQL injection

[Benjamin Smith]

Prepared statements are the way to go. 

I developed my own prepared statements methodology (I called it "SafeQuery") 
some time back before discovering that others had done it. It's so nice, 
since I've not worried about SQL injection for YEARS. 

Sample of my API: 

$_REQUEST['username'], 
 'password'=>$_REQUEST['password']); 
if (!$res=$MDB->SafeQuery($sql, $todb)) 
 return Error("Database query failure"); 
?> 

SafeQuery checks: 
1) That the variables in the query (in brackets) and in the input array 
all 
match up. 
2) Runs pg_escape_string on all elements in $todb; 
3) Copy/Pastes strings from the array into the query. 
4) Runs query against DB, returns results from pg_exec(); 

-Ben 

On Tuesday 01 November 2005 05:27, Kevin Murphy wrote:
> Can some knowledgeable person set the record straight on SQL injection, 
> please?  I thought that the simple answer was to use prepared statements 
> with bind variables (except when you are letting the user specify whole 
> chunks of SQL, ugh), but there are many people posting who either don't 
> know about prepared statements or know something I don't.
> 
> Thanks,
> Kevin Murphy
> 
> P.S.  I don't use PHP, but google informs me that PHP definitely has 
> prepared statement options: PEAR::DB, PDO in 5.X+, etc.
> 
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
> 

-- 
"The best way to predict the future is to invent it."
- XEROX PARC slogan, circa 1978

---(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: [GENERAL] Image File System Question

[Richard Huxton]

vishal saberwal wrote:

hi,

My server is Postgres 8.0.1 on fedora core2.
My clients are remote and interface with my server using .NET GUI.

We are trying to store many images/icons/audio/video clips in our system.
We expect many of these. The way we are doing it is using Hierarchical 
File System.


HFS is an old Apple Macintosh filesystem - an unlikely choice. You'll 
probably find it's ext3.


I understand we need to limit the size of these directories by 
controlling number of resources in each directory.


Less important nowadays than it used to be, but usually done via hashing 
the filename. So, file 123456.gif is stored in 1/2/3/123456.gif


The way i want to let anone access these resources is only through 
stored procedures rather than direct downloading.


Why?
What are the benefits of this system?

I am trying to create stored procedure/function API for the same. These 
functions will check for the permissions for the user and/or the file,
check the location from the schema and then would "stream" it out for 
the GUI to use.


Why not just set up a webserver and get it to authenticate to your database?


The question is:
(1) How do i use the database stored procedures/functions as a tunnel 
for just streaming the data rather than storing it in database?
That is, a function that given a Image ID for example, will read the 
location from the table and then just go to the location and stream out 
the bits.
(2) Am i right in saying that it can't solely be done iwth plpgsql but 
would need somem c/c++ api.


Any of the "untrusted" languages (which of course includes "C"). File 
access will be different in each of course - pick whichever you are most 
familiar with. The key differences between a "trusted" and "untrusted" 
version of a language are:

1. Untrusted languages can access the rest of the system
2. Functions in untrusted languages can only be added by a superuser.

(3) Has anyone here done something like this and can share with me how 
he/she implemented this.


I did do my homework of googling for something like this but may be my 
search skills were not strong enough to find some substantial 
information/HOW TOs or examples.


Well, there is the "procedural languages" section of the manuals. It 
might also be worth checking on pgfoundry to see if there is anything 
useful there.


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Looping through arrays

[Joe Conway]

Robert Fitzpatrick wrote:

I have a field with 'AA-BB-CC-DD' and I want to pull those four values
into an array and then loop through the array inserting records into a
table for each element. Can you someone point me to an example of this
in pl/pgsql?



Something like this?

create table testfoo (id int, arrstr text);
create table testfoo_det (id int, elem text);
insert into testfoo values (1, 'AA-BB-CC-DD');
insert into testfoo values (2, 'EE-FF-GG-HH');

create or replace function testfoo_func(int) returns void as $$
declare
  arrinptext[];
begin
  select into arrinp string_to_array(arrstr,'-')
  from testfoo where id = $1;

  for i in array_lower(arrinp, 1)..array_upper(arrinp, 1) loop
execute 'insert into testfoo_det
values (' || $1 || ', ''' || arrinp[i] || ''')';
  end loop;

  return;

end;
$$ language plpgsql;

regression=# select testfoo_func(id) from testfoo;
 testfoo_func
--


(2 rows)

regression=# select * from testfoo_det;
 id | elem
+--
  1 | AA
  1 | BB
  1 | CC
  1 | DD
  2 | EE
  2 | FF
  2 | GG
  2 | HH
(8 rows)

HTH,

Joe

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

  http://archives.postgresql.org