date:20040112

"Jim Wilson" <[EMAIL PROTECTED]> writes:
> Yes, I think you are correct on that.  I was misreading column reference for
> column name.  Would it be difficult to patch my local copy to either permit
> this or strip off the characters from the qualifier portion in the parser?

[shrug...]  You could probably hack the grammar to throw away a
qualifier there, but wouldn't it be easier to fix your incorrect SQL?

AFAICS, SQL92 does not allow a qualified name there, and SQL99 does but
appears to assign it some completely other semantics than what you're
expecting --- looks like an object method call of some kind... so you
are in for trouble in the long run if you don't fix your code.

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

Re: [GENERAL] Vacuum Error

Kragen Sitaker <[EMAIL PROTECTED]> writes:
> On Mon, Jan 12, 2004 at 06:20:23PM -0500, Tom Lane wrote:
>> No; an OID collision would have occurred when you tried to create a
>> table.  If two tables are present in pg_class then they have different
>> OIDs, and shouldn't have any conflicts in pg_statistic.

> How would that OID collision manifest?  Do you think the error message
> might look similar?

Similar, but referring to pg_class_oid_index.

regards, tom lane

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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] sql insert function

Chris Ochs wrote:
My program starts a transaction, does about 20 inserts, then commits.  When
I replace once of the inserts with a function that does the insert, when I
do the commit I get this message:
WARNING:  there is no transaction in progress

The inserts all commit fine.  Do functions used through DBD::Pg do something
like turn on autocommit after a function is called?
Is your function calling 'commit' itself?  If so, it could be committing 
before your SQL statement issues the 'commit', thus attempting to commit 
a transaction which doesn't exist any more.

DBD::Pg defaults to "AutoCommit" behaviour, unless you explicitly turn 
it off:

   my $dbh = DBI->connect (
  "DBI:Pg:dbname=database", "user" , "password",
  {AutoCommit => 0}
   );
HTH
Alex Satrapa
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Drawbacks of using BYTEA for PK?

2004-01-12 Thread D. Dante Lorenso

they can try to look up information on other customers by doing:

http://domain.com/application/load_record.html?customer_id=12346
http://domain.com/application/load_record.html?customer_id=12344
...basically walking the sequence. Sure, you will protect against this

to happen. NOW, if you use a GUID:

Security != obscurity.

While using GUIDs may make it harder to get hacked, it in no way actually
increases security. Real security comes from secure code, period.

Well, uh, you're both wrong.
On the one hand if your GUIDs are just an MD5 of a sequence then they're
just as guessable as the sequence.

Its not a question of right or wrong. Its the method. One thing I see here is
a failing to use several security methods at different layerswhy are you not using HTTPS (i.e. authentication)?
What about using a crytographic cookies to identify your session and link that
to you userid (after authorization)?

Ok, my point is not one of security as much as the obscurity. I have the
security aspect already covered whereby I only select the customer
record from
the database where the logged in account has access to the record. So, if
you are not the admin or the actual customer, the select will return a code
indicating that you do not have permission to view the given record.

Maybe a better example of my problem is with records throughout the system
like invoices, customer data, etc... If any of these items use a sequence
and that sequence is global to the table in the database and the number is
exposed externally, then it is possible to infer the success of the company
underneath, is it not?
For instance, if I generate sequential numbers for invoice ids and the
customer
sees #123 as an invoice number one month and sees #128 the next month,
it might
imply that there are only 4 customers getting invoiced each month.

Another example ... let's say customers can create 'Widgets' in their
account.
There might be a page that lists all their 'widgets'. If you click on the
widget, you can edit it. A link to do this might look as follows:

http://.../account/widget_list.html
http://.../account/widget_edit.html?widget_id=12345
Well, if the widget_id is a sequence (global to the widget table), then
by creating
one widget, customer would get widget id (WIDG_1) and another widget
(WIDG_2),
the customer could see that the widget_id increased by only an amount of

N = WIDG_2 - WIDG_1

and would therefore provide the assumption that the number of customers
creating
widgets in total does not exceed N. I don't see this as much of a
problem about
'security' in the respect of who can access the data as much as who can make
conclusions about the company beind the data.

See what I mean? What do you propose as the best solution for this?
Not expose
the sequences to the user and use user-enumerated ids? Then a trigger
on the
table would assign ids like:

SELECT (MAX(widget_id)+1) INTO NEW.widget_id
WHERE cust_id = NEW.cust_id;
But I think after several hundred customer records, this trigger would start
getting slow. I don't know really, any ideas?
Dante

---(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: [GENERAL] Parse error help needed...

Now my function is created ... Thanks guys .. 

Now getting error while trying to execute it 

postgres=# select loadme();
WARNING:  Error occurred while executing PL/pgSQL function loadme
WARNING:  line 7 at SQL statement
ERROR:  SPI_prepare() failed on "commit"

Thx again
Deep

PS: here is fn definition

create function loadme() returns text as '
Declare
   s_out text ;
Begin
  For i in 1..1 loop
insert into test values (i,''Test'');
  end loop;
 commit;
returns s_out;
End;
' language 'plpgsql';




-Original Message-
From: Larry Rosenman [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 12, 2004 5:29 PM
To: Thapliyal, Deepak; 'PostgreSQL General'
Subject: Re: [GENERAL] Parse error help needed...




--On Monday, January 12, 2004 17:24:14 -0800 "Thapliyal, Deepak" 
<[EMAIL PROTECTED]> wrote:

> Made the change and used returns in both places now .. Gives me error
>
> postgres=# \i a.sql
> psql:a.sql:10: ERROR:  language "plpgsql" does not exist
>
createlang plpgsql


> Any help is appreciated
>
> Thx
> Deep
>
> create function loadme() returns text as '
> Declare
>s_out text ;
> Begin
>   For i in 1..1 loop
> insert into test values (i,''Test'');
>   end loop;
> returns s_out;
> End;
> ' language 'plpgsql';
>
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Alex Satrapa
> Sent: Monday, January 12, 2004 4:57 PM
> To: PostgreSQL General
> Subject: Re: [GENERAL] Parse error help needed...
>
>
> Thapliyal, Deepak wrote:
>> create function loadme() return text as '
>
> try "RETURNS" instead of "RETURN"
>
> [the guys writing the function parser might want to consider reporting 
> what the parser was expecting at this point]
>
>> Declare
>>s_out text ;
>> Begin
>>   For i in 1..1 loop
>> insert into test values (i,''Test'');
>>   end loop;
>> return s_out;
>> End;
>> ' language 'plpgsql';
>
> The rest looks fine (works for me - yes I tested it this time)
>
> Alex Satrapa
>
>
> ---(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
>
> ---(end of 
> broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>



-- 
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED]
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

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

   http://archives.postgresql.org

Re: [GENERAL] Column qualifier issue

2004-01-12 Thread Jim Wilson

Tom Lane <[EMAIL PROTECTED]> said:

> "Jim Wilson" <[EMAIL PROTECTED]> writes:
> > As far as I can tell, there isn't a way to get postgresql to accept column
> > qualifiers (e.g. tablenames).  A 'parse error at or near "."' gets returned.
> 
> You're either very confused or using a *very* old version of Postgres.
> 
> What version is it, what query are you issuing exactly, and what exactly
> is the error message?  (Cut-and-paste from a psql session would be the
> best way to answer the last two ... or if the query is being issued from
> another application, you could turn on query logging and send in an
> excerpt from the postmaster log.)
> 
>   regards, tom lane
> 

This is version 7.2.3, and the same error was produced on 7.4.x (using the
phpadmin demo site).  Looking at the logs I don't see anything that is
addressing this.  This syntax isn't required by sql92,  but it is allowed (at
least that is my reading).

Following is a log from a psql session that demonstrates the problem.

Best,

Jim Wilson

bash$ ./bin/psql testdb
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help on internal slash commands
   \g or terminate with semicolon to execute query
   \q to quit

testdb=# select * from test;
 cola |colb
--+
1 | Record One
2 | 2nd Record
(2 rows)

kelcodb=# \d test
Table "test"
 Column | Type  | Modifiers
+---+---
 cola   | bigint| not null
 colb   | character varying(10) | not null

testdb=# update test set test.colb = '2nd Record' where test.cola = 2;
ERROR:  parser: parse error at or near "."
testdb=# update test set colb = '2nd Record' where cola = 2;
UPDATE 1

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

Re: [GENERAL] Column qualifier issue

2004-01-12 Thread Jim Wilson

Stephan Szabo <[EMAIL PROTECTED]> said:

> On Mon, 12 Jan 2004, Tom Lane wrote:
> 
> > "Jim Wilson" <[EMAIL PROTECTED]> writes:
> > > As far as I can tell, there isn't a way to get postgresql to accept column
> > > qualifiers (e.g. tablenames).  A 'parse error at or near "."' gets returned.
> >
> > You're either very confused or using a *very* old version of Postgres.
> >
> > What version is it, what query are you issuing exactly, and what exactly
> > is the error message?  (Cut-and-paste from a psql session would be the
> > best way to answer the last two ... or if the query is being issued from
> > another application, you could turn on query logging and send in an
> > excerpt from the postmaster log.)
> 
> My guess from the rest of his message is something like:
> 
> sszabo=# insert into a (a.x) values ('3');
> ERROR:  syntax error at or near "." at character 17
> 
> (from my 7.5 machine).
> 
> However, AFAICS in sql92 the above isn't valid, because a.x doesn't meet
> the syntactic requirements for elements in the column name list.

Yes, I think you are correct on that.  I was misreading column reference for
column name.  Would it be difficult to patch my local copy to either permit
this or strip off the characters from the qualifier portion in the parser?

Best,

Jim



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

Re: [GENERAL] Parse error help needed...

2004-01-12 Thread Larry Rosenman



--On Monday, January 12, 2004 17:24:14 -0800 "Thapliyal, Deepak" 
<[EMAIL PROTECTED]> wrote:

Made the change and used returns in both places now .. Gives me error

postgres=# \i a.sql
psql:a.sql:10: ERROR:  language "plpgsql" does not exist
createlang plpgsql


Any help is appreciated

Thx
Deep
create function loadme() returns text as '
Declare
   s_out text ;
Begin
  For i in 1..1 loop
insert into test values (i,''Test'');
  end loop;
returns s_out;
End;
' language 'plpgsql';


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Alex Satrapa
Sent: Monday, January 12, 2004 4:57 PM
To: PostgreSQL General
Subject: Re: [GENERAL] Parse error help needed...
Thapliyal, Deepak wrote:
create function loadme() return text as '
try "RETURNS" instead of "RETURN"

[the guys writing the function parser might want to consider reporting
what the parser was expecting at this point]
Declare
   s_out text ;
Begin
  For i in 1..1 loop
insert into test values (i,''Test'');
  end loop;
return s_out;
End;
' language 'plpgsql';
The rest looks fine (works for me - yes I tested it this time)

Alex Satrapa

---(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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
   http://archives.postgresql.org



--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED]
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749


pgp0.pgp
Description: PGP signature

Re: [GENERAL] Parse error help needed...

Made the change and used returns in both places now .. Gives me error 

postgres=# \i a.sql
psql:a.sql:10: ERROR:  language "plpgsql" does not exist

Any help is appreciated

Thx
Deep

create function loadme() returns text as '
Declare
   s_out text ;
Begin
  For i in 1..1 loop
insert into test values (i,''Test'');
  end loop;
returns s_out;
End;
' language 'plpgsql';



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Alex Satrapa
Sent: Monday, January 12, 2004 4:57 PM
To: PostgreSQL General
Subject: Re: [GENERAL] Parse error help needed...


Thapliyal, Deepak wrote:
> create function loadme() return text as '

try "RETURNS" instead of "RETURN"

[the guys writing the function parser might want to consider reporting 
what the parser was expecting at this point]

> Declare
>s_out text ;
> Begin
>   For i in 1..1 loop
> insert into test values (i,''Test'');
>   end loop;
> return s_out;
> End;
> ' language 'plpgsql';

The rest looks fine (works for me - yes I tested it this time)

Alex Satrapa


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

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

   http://archives.postgresql.org

Re: [GENERAL] sql insert function

2004-01-12 Thread Chris Ochs

I am seeing another strange thing when using a function that does an insert
instead of doing the insert directly.  This is using cached connections with
apache/mod_perl.

My program starts a transaction, does about 20 inserts, then commits.  When
I replace once of the inserts with a function that does the insert, when I
do the commit I get this message:

WARNING:  there is no transaction in progress

The inserts all commit fine.  Do functions used through DBD::Pg do something
like turn on autocommit after a function is called?



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

Re: [GENERAL] Parse error help needed...

Thapliyal, Deepak wrote:
create function loadme() return text as '
try "RETURNS" instead of "RETURN"

[the guys writing the function parser might want to consider reporting 
what the parser was expecting at this point]

Declare
   s_out text ;
Begin
  For i in 1..1 loop
insert into test values (i,''Test'');
  end loop;
return s_out;
End;
' language 'plpgsql';
The rest looks fine (works for me - yes I tested it this time)

Alex Satrapa

---(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: [GENERAL] Parse error help needed...

Thx to doug ... Old error is gone .. But I am now getting new error

postgres=# \i a.sql
psql:a.sql:10: ERROR:  language "plpgsql" does not exist

But I verified that I setup plpgsql

[EMAIL PROTECTED] createlang -d test -l
Procedural languages
  Name   | Trusted? 
-+--
 plpgsql | t
(1 row)

Can u pls advice if u know whats going on.. Am I missing somin obvious here.
.. 


Thx in advance
Deep



-Original Message-
From: Doug McNaught,,, [mailto:[EMAIL PROTECTED] On Behalf Of Doug
McNaught
Sent: Monday, January 12, 2004 4:44 PM
To: Thapliyal, Deepak
Cc: '[EMAIL PROTECTED]'
Subject: Re: [GENERAL] Parse error help needed...


"Thapliyal, Deepak" <[EMAIL PROTECTED]> writes:

> I am getting this error
>
> postgres=# \i a.sql
> psql:a.sql:10: ERROR:  parser: parse error at or near "return" at 
> character 26

> create function loadme() return text as '
   ^^

I think you want "returns" here.

-Doug

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

Re: [GENERAL] Parse error help needed...

2004-01-12 Thread Doug McNaught

"Thapliyal, Deepak" <[EMAIL PROTECTED]> writes:

> I am getting this error 
>
> postgres=# \i a.sql
> psql:a.sql:10: ERROR:  parser: parse error at or near "return" at character
> 26

> create function loadme() return text as '
   ^^

I think you want "returns" here.

-Doug

---(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: [GENERAL] sql insert function

2004-01-12 Thread Chris Ochs

Hmmm since the function already knows the type, the quotes aren't needed.
If you use them it just inserts a literal $1 and $3.


- Original Message - 
From: "Alex Satrapa" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, January 12, 2004 4:33 PM
Subject: Re: [GENERAL] sql insert function


> Chris Ochs wrote:
> > Never mind, I forgot to quote the quote's...
>
> Heh... and here I was thinking you were trying to build a function ;)
>
> And I made the same mistake as you... guess I should proofread instead
> of copy-pasting ;)
>
> Alex Satrapa
>
>
> ---(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
>


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

[GENERAL] Parse error help needed...

I am getting this error 

postgres=# \i a.sql
psql:a.sql:10: ERROR:  parser: parse error at or near "return" at character
26


--
a.sql
--
create function loadme() return text as '
Declare
   s_out text ;
Begin
  For i in 1..1 loop
insert into test values (i,''Test'');
  end loop;
return s_out;
End;
' language 'plpgsql';

Any help is appreciated 

Thx
Deep

PS: I already loaded pl/pgsql in my database
[EMAIL PROTECTED] createlang -d test -l
Procedural languages
  Name   | Trusted? 
-+--
 plpgsql | t
(1 row)


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Chris Ochs
Sent: Monday, January 12, 2004 4:30 PM
To: [EMAIL PROTECTED]
Subject: Re: [GENERAL] sql insert function


Never mind, I forgot to quote the quote's...

Chris

> The documentation doesn't have any examples of using an sql language 
> function to do an insert, andI am at loss as to I am doing wrong here. 
> The error I get trying to create the function is:  ERROR:  syntax 
> error at or near "$1" at character 148
>
> CREATE FUNCTION taxship(varchar,integer,varchar,float,float) returns
integer
> AS '
> insert into taxship(s_oid,order_id,mer_id,tax,shipping) values 
> ('$1',$2,'$3',$4,$5); SELECT 1;
> ' LANGUAGE SQL;
>
>
> ---(end of 
> broadcast)---
> TIP 7: don't forget to increase your free space map settings
>


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

---(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: [GENERAL] sql insert function

2004-01-12 Thread Richard Welty

On Mon, 12 Jan 2004 16:21:17 -0800 Chris Ochs <[EMAIL PROTECTED]> wrote:

> The documentation doesn't have any examples of using an sql language
> function to do an insert, andI am at loss as to I am doing wrong here.
> The error I get trying to create the function is:  ERROR:  syntax error at
> or near "$1" at character 148

> CREATE FUNCTION taxship(varchar,integer,varchar,float,float) returns integer
> AS '
> insert into taxship(s_oid,order_id,mer_id,tax,shipping) values
> ('$1',$2,'$3',$4,$5);
> SELECT 1;
> ' LANGUAGE SQL;

i do believe you need to double up the single quotes inside the
function body, e.g.

(''$1'',$2,''$3'',$4,$5);

otherwise, the quote before the $1 ends up terminating the
function body.

richard
-- 
Richard Welty [EMAIL PROTECTED]
Averill Park Networking 518-573-7592
Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security


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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] sql insert function

2004-01-12 Thread Doug McNaught

"Chris Ochs" <[EMAIL PROTECTED]> writes:

> The documentation doesn't have any examples of using an sql language
> function to do an insert, andI am at loss as to I am doing wrong here.
> The error I get trying to create the function is:  ERROR:  syntax error at
> or near "$1" at character 148
>
> CREATE FUNCTION taxship(varchar,integer,varchar,float,float) returns integer
> AS '
> insert into taxship(s_oid,order_id,mer_id,tax,shipping) values
> ('$1',$2,'$3',$4,$5);
> SELECT 1;
> ' LANGUAGE SQL;

When you want to use single quotes inside a quoted string (which is
what a function body is) you need to escape them.

-Doug

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

   http://archives.postgresql.org

Re: [GENERAL] sql insert function

Chris Ochs wrote:
Never mind, I forgot to quote the quote's...
Heh... and here I was thinking you were trying to build a function ;)

And I made the same mistake as you... guess I should proofread instead 
of copy-pasting ;)

Alex Satrapa

---(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: [GENERAL] sql insert function

Chris Ochs wrote:
CREATE FUNCTION taxship(varchar,integer,varchar,float,float) returns integer
AS '
insert into taxship(s_oid,order_id,mer_id,tax,shipping) values
('$1',$2,'$3',$4,$5);
SELECT 1;
' LANGUAGE SQL;
try

CREATE FUNCTION taxship (varchar,integer,varchar,float,float) RETURNS 
integer AS '
BEGIN
  insert into taxship(s_oid,order_id,mer_id,tax,shipping)
values ('$1',$2,'$3',$4,$5);
  return 1;
END' LANGUAGE 'plpgsql';

since what you are trying to do is a compound statement.



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

[GENERAL] Dump/Restore ordering problem?

2004-01-12 Thread D. Dante Lorenso

First I created a function that selected the next available pin
code from a table of pre-defined pin codes:
   CREATE FUNCTION "public"."get_next_pin_code" () RETURNS varchar AS'
   DECLARE
   my_pin_code VARCHAR;
   BEGIN
   ...
   /* this is the pincode we just fetched */
   RETURN (my_pin_code);
   END;
   'LANGUAGE 'plpgsql';
Then I created a table that used that function to set a default value:

   CREATE TABLE "public"."account" (
   "acct_id" BIGSERIAL,
   ...,
  "acct_pin_code" VARCHAR(16) NOT NULL DEFAULT get_next_pin_code()
   ) WITH OIDS;
But, now when I pg_dump and pg_restore this database to another server,
there seems to be a problem with the ordering of the dump in that the
account table is not recreated because the function get_next_pin_code()
is not yet defined.  It seems like the function is not being created
until AFTER the table is created and this causes an ordering problem.
To dump and restore I've been doing this:

   pg_dump -U dbuser -Ft dbname | pg_restore -c -Ft | psql -U dbuser -h 
db.otherdbhost.com dbname

I've been able to work around this by creating a TRIGGER that sets the
default value instead of defining it in the table definition, but that
just seems like a hack.  Is there something I need to do to make the
dependency ordering work smarter during a dump/restore?  Or is this the
right way to do it?
Dante



 

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

Re: [GENERAL] Case sensitivity

2004-01-12 Thread scott.marlowe

On Mon, 12 Jan 2004, Richard Huxton wrote:

> On Sunday 11 January 2004 09:29, Dario Ottaviano wrote:
> > I use postgres on a window server (IIS 5.1)
> > Is there anybody that knows if  is possible to make postgres  no case
> > sensitive in the manipulating data into tables/views?
> 
> There's no general "case_sensitive = yes/no" flag. There are case-insensitive 
> operators you can use though.

Is there a case insensitive locale?  Or is that even possible?


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

Re: [GENERAL] History-based (or logged) database.

2004-01-12 Thread elein

Yes, I did.  For just the simple updating, (not the
logging you are doing) NEW is what you want.  But OLD is proper
for archiving/logging.

--elein

On Mon, Jan 12, 2004 at 08:22:27PM +0700, Chris Travers wrote:
> Hi Elein;
> 
> Nope, OLD is correct.  I track the OLD values and then use the view to
> combine those with the current ones.  This allows the OLAP portions of the
> code to hit against *all* the data, while archiving old, outdated
> information in the archive table.  It also allows deleted tuples to be
> tracked with the same trigger since a deleted row doesn't exactly have a NEW
> tuple :-) Maybe you misunderstand what I am trying to do?
> 
> Best WIshes,
> Chris Travers

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

   http://archives.postgresql.org

Re: [GENERAL] Drawbacks of using BYTEA for PK?

2004-01-12 Thread D. Dante Lorenso


Tom Lane wrote:

Adding an MD5 hash contributes *absolutely zero*, except waste of space,
to any attempt to make a GUID.  The hash will add no uniqueness that was
not there before.

The cool thing about a 'GUID' (or in my example a hashed sequence number 
[sure
toss in some entropy if you want it]) is that if you happen to reference 
that
value as a primary key on a table, the URL that passes the argument can not
be guessed at easily.  For example using a sequence:

   http://domain.com/application/load_record.html?customer_id=12345

Then, users of the web will assume that you have at most 12345 
customers.  And
they can try to look up information on other customers by doing:

   http://domain.com/application/load_record.html?customer_id=12346
   http://domain.com/application/load_record.html?customer_id=12344
...basically walking the sequence.  Sure, you will protect against this with
access rights, BUT...seeing the sequence is a risk and not something you 
want
to happen.  NOW, if you use a GUID:

   
http://domain.com/application/load_record.html?customer_id=f46d6296-5362-2526-42e3-1b8ce9d1

Right, so now try to guess the next value in this sequence.  It's a little
more protective and obfuscated (an advantage in using GUIDs).
Dante

 





---(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: [GENERAL] what we need to use postgresql in the enterprise

2004-01-12 Thread Jeff Eckermann

--- [EMAIL PROTECTED] wrote:
> I write this to tell you why we won't use postgresql
> even though we wish we
> could at a large company.  Don't get me wrong I love
> postgresql in many
> ways and for many reasons , but fact is fact.  If
> you need more detail I
> can be glad to prove all my points.  Our goal is to
> make logical systems.
> We don't want php,perl, or c++ making all the
> procedure calls and having
> the host language to be checking for errors and
> handleing all the
> transactions commits and rollbacks.  That is not
> very logical in a large
> batch system.  Also please don't tell me to code the
> changes myself.  I'm
> not at that part of the food chain.  That is to low
> level for me and I
> don't have the time to put that hat on.  I build the
> applications that use
> the database systems.  Also please feel free to
> correct me in any area I
> don't know everything I'm just stating my opinion
> here
> 
> 1.  Need commit roll back in pl/pgsql much like
> Oracle does
> 2.  Need exception handling in pl/pgsql must like
> Oracle does
> 3.  A>Need sub transactions .  B>And if an inner
> transactions fails it
> should not cause all others to fail.  If #2 was
> robust enough than #3 B
> might not be an issue.

#1 & #3 both refer to the same thing, i.e. nested
transactions.  Alvaro Herrera has been working on this
for some time, and recently stated on (I think) the
pgsql-hackers list that he intended to have nested
transactions ready for the next release of PostgreSQL.
 On the other hand, Tom Lane recently responded to a
question about nested transactions by warning about
the complexity of the problems needing to be overcome
to make that happen, and expressing doubt about an
early solution.  So you could say that the status is
unclear.  A question on the -hackers list would
probably get more information.

Agreed that Oracle-style exception handling in
pl/pgsql would be a good thing.  If I understand
things correctly, the new error codes scheme in
PostgreSQL version 7.4 makes that easier to implement
than before.  But I am not aware of anyone working on
it.

*** Note To Developers ***
Adding Oracle-style exception handling to pl/pgsql
would greatly ease the migration path from Oracle to
PostgreSQL, and could easily result in many more
instances of Postgres being used for enterprise apps
that are now using Oracle.  But I'm not up to the
task, so I'm flagging it here for anyone else who
might want to take a crack at it.

> 
> With those two things I could accomplish pretty much
> everything with
> postgresql that we're currently doing in Oracle.
> 
> 1. It's a must if you have long running complicated
> and time consuming
> batch processing.  There is no reason why one should
> say do all of commit
> and rollbacks from the client. Our current batch
> system gets fired off by
> running some sql scripts that start an entry point
> into the batch system.
> Once the first stored procedure is called it will
> call all the rest.  This
> encapsulates all logic and processing in the
> database where it belongs.
> There is no client traffic because once that first
> script kicks off there
> is no outside process running , just our pl/sql. 
> Now I'm not a postgresql
> expert at all but when I read up on it looks like
> this is something you
> can't accomplish and I see no word of this being
> worked on.
> 
> 2. Without this you can't trust complicated code as
> far as I'm concerned. I
> need to log some errors and continue processing and
> for others log and exit
> which I think you can do now in pl/pgsql.  Point
> being pl/pgsql exception
> handling is almost nonexistent at best.
> 
> 3. We use this all the time in pl/sql and we need
> to. To write this off as
> not need is wrong and will not get postgresql to
> where it can be(AT THE
> TOP).
> 
> 
> 
> 
> 
> 
>
*
> PRIVILEGED AND CONFIDENTIAL: This communication,
> including attachments, is for the exclusive use of
> addressee and may contain proprietary, confidential
> and/or privileged information.  If you are not the
> intended recipient, any use, copying, disclosure,
> dissemination or distribution is strictly
> prohibited.  If you are not the intended recipient,
> please notify the sender immediately by return
> e-mail, delete this communication and destroy all
> copies.
>
*
> 
> 
> ---(end of
> broadcast)---
> TIP 1: subscribe and unsubscribe commands go to
[EMAIL PROTECTED]


__
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus

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

   http://archives.postgresql.org

Re: [GENERAL] Any way to SELECT a list of table names?

2004-01-12 Thread Ken Godee

On Sat, 10 Jan 2004, Eric Freeman wrote:


Is there any way in Postgres to SELECT a list of table names from inside of 
a C program using ECPG?
Something similar to SELECT current_user that will give you all of the 
tables in the database you are connected to.


Not sure if this is what you're trying to do but...

"SELECT tablename FROM pg_tables where tablename not like 'pg_%'"

Will get a list of tables in the db you're connected to.

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

[GENERAL] unsubscribe

2004-01-12 Thread Eric Freeman



_
High-speed usersbe more efficient online with the new MSN Premium Internet 
Software. http://join.msn.com/?pgmarket=en-us&page=byoa/prem&ST=1

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

Re: [GENERAL] Any way to SELECT a list of table names?

2004-01-12 Thread scott.marlowe

On Sat, 10 Jan 2004, Eric Freeman wrote:

> Is there any way in Postgres to SELECT a list of table names from inside of 
> a C program using ECPG?
> Something similar to SELECT current_user that will give you all of the 
> tables in the database you are connected to.

If you are running 7.4 and have the information_schema you can use that to 
find out just about anything.

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] unsubscribe

2004-01-12 Thread Melanie Bergeron

unsubscribe



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Drawbacks of using BYTEA for PK?

2004-01-12 Thread David Garamond

Tom Lane wrote:
David Garamond <[EMAIL PROTECTED]> writes:

Perhaps I can make a GUID by MD5( two random numbers || a timestamp || a 
unique seed like MD5 of '/sbin/ifconfig' output)...
Adding an MD5 hash contributes *absolutely zero*, except waste of space,
to any attempt to make a GUID.  The hash will add no uniqueness that was
not there before.
Of course, in the above case, MD5 is used to compress the "uniqueness" 
(which should be more than 128-bit, comprised of: a) [good] random 
number; b) timestamp; c) a "node ID" element, either from /sbin/config 
output which contain MAC address, or from the hash of harddisk content, 
etc) into a 128-bit space.

--
dave
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] int8 version of NUMERIC?

2004-01-12 Thread Bruno Wolff III

On Sun, Jan 11, 2004 at 21:53:09 +0700,
  David Garamond <[EMAIL PROTECTED]> wrote:
> 
> My concern is that, the PostgreSQL docs says NUMERIC & DECIMAL is very 
> slow compared to INT/BIGINT. Should I worry about that?

Most likely disk IO not cpu will be your bottleneck and the extra overhead
of numeric relative to int or float won't be a big deal.

Numeric is stored usingh based 1 (at least in 7.4.x) and hence isn't
that horrible performance-wise (as compared to say storing it as an ascii
string).

---(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: [GENERAL] PostgreeSQL C header files

2004-01-12 Thread joseph speigle

hi,

I have done work on sourceforge for my c++ cgi library.  It is at 
http://sourceforge.net/projects/cgiutils2/

It is built up of 3 separate libraries, the cgi library which parses post requests and 
gets form values and such, a session library, and a template library which uses a 
custom parser.  The first and last don't need any data storage.  For sessions, it 
doesn't use a postgresql backend, becase it uses mmap and semaphores for memory 
management of sessions.  I am in the process of adding mysql and postgresql c++ 
library code and configure switches to allow the user to store session information in 
the databases, however, instead of in unix memory.  

I have a website which exclusively generates all content from the cgi c++ library (it 
uses the cgi c++ library to do sessions, templates and form processing) and it is 
http://www.myowndictionary.com/.  That client uses postgresql c++ library off gborg 
which is libpqxx.  Source for the client is 
http://www.sirfsup.com/languages/cPP/lcgi/decoy.htm

Libpqxx has docs of its own but lots of it reproduces what's in libpq docs as it's 
just a c++ wrapper with classes methods and calls and such for the C code.

I am always looking for developers to help with the sourceforge project, if you want 
you could write the libpqxx class for the sessions or something.

On Wed, Dec 24, 2003 at 11:06:21AM +0100, Christian Kienle wrote:
> > See the "Client Interfaces" and possibly the "Server Programming"
> > sections in the PostgreSQL manual:
> > http://www.postgresql.org/docs/current/static/client-interfaces.html
> > http://www.postgresql.org/docs/current/static/server-programming.html
> > The examples in the contrib directory of the source distribution might
> > also be helpful.
> 
> Thank you very much. 
> 
> 
> 
> 
> 
> -- 
> Linux is like a wigwam - no gates, no windows and an apache inside.
> 
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

-- 
joe speigle

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Drawbacks of using BYTEA for PK?

David Garamond <[EMAIL PROTECTED]> writes:
> Perhaps I can make a GUID by MD5( two random numbers || a timestamp || a 
> unique seed like MD5 of '/sbin/ifconfig' output)...

Adding an MD5 hash contributes *absolutely zero*, except waste of space,
to any attempt to make a GUID.  The hash will add no uniqueness that was
not there before.

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] insertion with trigger failed unexpectedly

Richard Huxton <[EMAIL PROTECTED]> writes:
> On Monday 12 January 2004 05:57, [EMAIL PROTECTED] wrote:
>> i have a problem with insertion data and running post insert trigger
>> on it.

> Better post the CREATE TABLE, trigger code and a sample INSERT.

And the specific error messages you're getting, and the PG version
number.  Also, you say it happens "twice a day" --- do you mean at
specific times of day, like noon and midnight?

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] what we need to use postgresql in the enterprise

2004-01-12 Thread Bob . Henkel


I couldn't agree with you more.  I'm just a developer in a very large
company and getting anyone to listen and then understand that logic would
be a nightmare to say the least.  If it was my company I would put money
toward those issues.





   

  Robert Treat 

  <[EMAIL PROTECTED]To:   [EMAIL PROTECTED], [EMAIL 
PROTECTED]   
  eforge.net>cc:   

 Subject:  Re: [GENERAL] what we need 
to use postgresql in the enterprise  
  01/12/2004 12:45 AM  

   

   





I think your pretty much on target with the below. It is possible to work
around these issues on some level, but I can see how that might get
unwieldly
in a hurry.  While I won't tell you to "go code it if you need it", I might

ask that you consider what your paying in Oracle licenses and think about
spending that money to hire someone to develop those features in
PostgreSQL,
I'm thinking you'd save money in the long run.

Robert Treat

On Friday 09 January 2004 14:48, [EMAIL PROTECTED] wrote:
> I write this to tell you why we won't use postgresql even though we wish
we
> could at a large company.  Don't get me wrong I love postgresql in many
> ways and for many reasons , but fact is fact.  If you need more detail I
> can be glad to prove all my points.  Our goal is to make logical systems.
> We don't want php,perl, or c++ making all the procedure calls and having
> the host language to be checking for errors and handleing all the
> transactions commits and rollbacks.  That is not very logical in a large
> batch system.  Also please don't tell me to code the changes myself.  I'm
> not at that part of the food chain.  That is to low level for me and I
> don't have the time to put that hat on.  I build the applications that
use
> the database systems.  Also please feel free to correct me in any area I
> don't know everything I'm just stating my opinion here
>
> 1.  Need commit roll back in pl/pgsql much like Oracle does
> 2.  Need exception handling in pl/pgsql must like Oracle does
> 3.  A>Need sub transactions .  B>And if an inner transactions fails it
> should not cause all others to fail.  If #2 was robust enough than #3 B
> might not be an issue.
>
> With those two things I could accomplish pretty much everything with
> postgresql that we're currently doing in Oracle.
>
> 1. It's a must if you have long running complicated and time consuming
> batch processing.  There is no reason why one should say do all of commit
> and rollbacks from the client. Our current batch system gets fired off by
> running some sql scripts that start an entry point into the batch system.
> Once the first stored procedure is called it will call all the rest.
This
> encapsulates all logic and processing in the database where it belongs.
> There is no client traffic because once that first script kicks off there
> is no outside process running , just our pl/sql.  Now I'm not a
postgresql
> expert at all but when I read up on it looks like this is something you
> can't accomplish and I see no word of this being worked on.
>
> 2. Without this you can't trust complicated code as far as I'm concerned.
I
> need to log some errors and continue processing and for others log and
exit
> which I think you can do now in pl/pgsql.  Point being pl/pgsql exception
> handling is almost nonexistent at best.
>
> 3. We use this all the time in pl/sql and we need to. To write this off
as
> not need is wrong and will not get postgresql to where it can be(AT THE
> TOP).
>
>
>
>
>
>
> *
> PRIVILEGED AND CONFIDENTIAL: This communication, including attachments,
is
> for the exclusive use of addressee and may contain proprietary,
> confidential and/or privileged information.  If you are not the intended
> recipient, any use, copying, disclosure, dissemination or distribution is
> strictly prohibited.  If you are not the intended recipient, please
notify
> the sender immediately by return e-mail, delete this communication and
> destroy all copies.
> ***

Re: [GENERAL] Drawbacks of using BYTEA for PK?

2004-01-12 Thread David Garamond

D. Dante Lorenso wrote:
GUID?  Isn't that really nothing more than an MD5 on a sequence?

   SELECT (MD5(NEXTVAL('my_table_seq'))) AS my_guid;
I know there are several algorithms to generate GUID, but this is 
certainly inadequate :-) You need to make sure that the generated GUID 
will be unique throughout cyberspace (or to be more precise, the GUID 
should have a very very small chance of colliding with other people's 
GUID). Even OID is not a good seed at all.

Perhaps I can make a GUID by MD5( two random numbers || a timestamp || a 
unique seed like MD5 of '/sbin/ifconfig' output)...

Since 7.4 has the md5 function built-in, there's your support ;-)
Well, until there's a GUID or INT128 or BIGBIGINT builtin type I doubt 
many people will regard PostgreSQL as fully supporting GUID. I believe 
there's the pguuid project in GBorg site that does something like this.

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

Re: [GENERAL] History-based (or logged) database.

2004-01-12 Thread Chris Travers

Hi Elein;

Nope, OLD is correct.  I track the OLD values and then use the view to
combine those with the current ones.  This allows the OLAP portions of the
code to hit against *all* the data, while archiving old, outdated
information in the archive table.  It also allows deleted tuples to be
tracked with the same trigger since a deleted row doesn't exactly have a NEW
tuple :-) Maybe you misunderstand what I am trying to do?

Best WIshes,
Chris Travers


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

Re: [GENERAL] insertion with trigger failed unexpectedly

2004-01-12 Thread Sai Hertz And Control Systems

Dear Anton Nikiforov,

The problem:
From the very beginning everything was fine and all records that i
was getting from routers were calculated just right. I spent a weeks
monitoring and testing my software.
Now i have 10.000.000 records in raw table and when i'm inserting data
alot of records are missing in raw and daily. Sometimes i got UPDATE
failed errors, sometimes INSERT failed, but in general i'm getting
this messages twice a day but not only two records are missing -
hundreds of them.
I am suggesting something but may be its wrong in eithercase we would 
require your PostgreSQL version number etc
Also
Checkout the disk space if you have run out of diskspace

Currently i have no idea where to go and what to
check. I did my tests mainly on FreeBSD platform and now i did tests
on RedHat Linux and the result is the same - some records just did not
 

This is a real problem its seems no disk space probelm in this case 
would you please show us the code

reach the database (trigger has logger that is saying that everything

Regards,
Vishal Kashyap
---(end of broadcast)---
TIP 8: explain analyze is your friend

[GENERAL] Dump tables with pg_dump - no or different Owner

2004-01-12 Thread Victor Spång Arthursson

Hi!

Have a problem, probably easy to solve... I want to dump a database 
which resides on my local server with another, and not existing, owner 
than the one who actually owns it locally.

The beginning of the dump file looks like:


\connect - postgres
SET search_path = public, pg_catalog;

--
-- TOC entry 2 (OID 16978)
-- Name: ingrediens; Type: TABLE; Schema: public; Owner: postgres

I want all instances of "postgres" to be changed in the dump (only) to 
for example "anotheruser" (which will be the one on the remote server).

I'm having problem dumping from the first to the second server, and 
suspects that the differing owners can be the key to solve the 
problem...

In advance, lots of thanks,
sincerely:
Victor

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] problems dumping from one server 2 another

2004-01-12 Thread Victor Spång Arthursson

2004-01-10 kl. 00.21 skrev Richard Huxton:

Can't get the

sudo -u user1 /usr/local/bin/pg_dump db1 | /usr/local/bin/psql -U 
user2
-h host2 db2

to work.

Only thing that happens is that I get multiple passwordprompts, and
then I gets told that the password is incorrect…
You probably want a .pgpass file - see the section on libpq in the 
manuals
(client interfaces / libpq / files)
Hi and thanks for the tip!

Have created my .pgpass-file, and is able to connect as well to my 
local server as to my remote server, each for them self.

This:

/usr/local/bin/psql -U user_local db_local

and:

/usr/local/bin/psql -U user_remote -h host_remote db_remote

works fine.

This, though, doesn't work:

/usr/local/bin/psql -U user_local db_local | /usr/local/bin/psql -U 
user_remote -h host_remote db_remote

Any tips, anyone?

Sincerely

Victor

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

Re: [GENERAL] insertion with trigger failed unexpectedly

On Monday 12 January 2004 05:57, [EMAIL PROTECTED] wrote:
> Dear all,
> i have a problem with insertion data and running post insert trigger
> on it.

> When i'm doing tests - everything is going well, but in
> production when multiple records being inserted losses happend.

OK - there must be something different happening with the live situation. 
Better post the CREATE TABLE, trigger code and a sample INSERT.

PS -  is the problem:
1. INSERT fails to raw table, and daily is not updated
2. INSERT fails to raw table, but daily is updated
3. INSERT works on raw table but daily is not updated

-- 
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Drawbacks of using BYTEA for PK?

On Sunday 11 January 2004 22:05, D. Dante Lorenso wrote:
> David Garamond wrote:
> > Are there any drawbacks of using BYTEA for PK compared to using a
> > primitive/atomic data types like INT/SERIAL? (like significant
> > performance hit, peculiar FK behaviour, etc).
> >
> > I plan to use BYTEA for GUID (of course, temporarily I hope, until
> > PostgreSQL officially supports GUID data type), since it seems to be
> > the most convenient+compact compared to other data types currently
> > available. I use GUIDs for most PK columns.
>
> GUID?  Isn't that really nothing more than an MD5 on a sequence?
>
> SELECT (MD5(NEXTVAL('my_table_seq'))) AS my_guid;

I think the point of a GUID is it's supposed to be unique across any number of 
machines without requiring those machines to coordinate their use of GUID 
values.

I think the typical approach is to use something like:
hash_fn( network_mac_address || other_hopefully_unique_constant || 
sequence_val )
and make sure that the probability of getting collisions is acceptably low.

ISTR a long discussion a year or two back on one of the lists, for those that 
are interested.
-- 
  Richard Huxton
  Archonet Ltd

---(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: [GENERAL] Case sensitivity

On Sunday 11 January 2004 09:29, Dario Ottaviano wrote:
> I use postgres on a window server (IIS 5.1)
> Is there anybody that knows if  is possible to make postgres  no case
> sensitive in the manipulating data into tables/views?

There's no general "case_sensitive = yes/no" flag. There are case-insensitive 
operators you can use though.

-- 
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Bug with rename bigserial column