[GENERAL] pgcrypto: is an IV needed with pgp_sym_encrypt()?

[Bill Moseley]

I'm just starting with pgcrypto, and I'm curious if it's
needed/recommended to use an initialization vector/value (IV) with
the pgp_sym_encrypt() function.

The docs hint that an IV is used automatically, but encrypting plain
text that starts the same seems to result in initial common cipher
text.  So, I'm not clear.

2. Data is prefixed with block of random bytes. This is equal to
using random IV.

So, I'm currently generating a substring of a md5 hash of a few items
and pre-pending that to the plain text I need to encrypt as the IV.
Then when I decrypt I remove that prefix.


BTW, this is for credit card storage, which is a business requirement.

Besides following the PCI DSS and external audit procedures, the plan
is to use pgcrypto (pgp_sym_encrypt() with AES-256) as part of a
credit card storage server.  The server and db are SSL only and the
key is passed from the application and never stored anyplace (except
in memcached on other servers during the session).  The key is a
user's plain text password plus an application-specific secret.  So,
each row has its own key.  Passwords must be changed periodically,
etc.

I'd welcome any comments or recommendations from others that have
implemented something similar.

Thanks,

-- 
Bill Moseley
[EMAIL PROTECTED]


---(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] keeping 3 tables in sync w/ each other

[Ow Mun Heng]

Hi,

I have 3 tables

foo
foo_loading_source1
foo_loading_source2

which is something like

create table foo (a int, b int, c int)
create table foo_loading_source1 (a int, b int, c int)
create table foo_loading_source2 (a int, b int, c int)

Is there a way which can be made easier to keep these 3 tables DDL in
sync?

the loading_sourceX tables are just a temporary-in-transit table for
data \copy'ied into the DB before being inserted into the main foo
table.

Currently, each time I add a new column to foo, I have to "remember" to
add the same to the other 2 table.

Can I use inheritance? References?

or will I have to create a procedural function such that it will add the
same ddl to all 3 tables each time a new column is defined?



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

Re: [GENERAL] help w/ SRF function

[Ow Mun Heng]

On Mon, 2007-09-17 at 09:42 +0800, Ow Mun Heng wrote:
> On Mon, 2007-09-17 at 09:21 +0800, Ow Mun Heng wrote:
> > Hi,
> > 
> > I want to use a SRF to return multi rows.
> > 
> > current SRF is pretty static.
> > 
> > create type foo_type as (
> > id smallint
> > data1 int
> > data2 int
> > )
> > 
> > CREATE OR REPLACE FUNCTION foo_func()
> >   RETURNS SETOF foo AS
> > $BODY$
> > SELECT
> > TRH.ID,
> > TRH.data1,
> > TRH.data2,
> > FROM D 
> > INNER JOIN  TS 
> >  ON TS.id = D.id
> > inner join TRH
> >  on ts.id = trh.id
> > WHERE D.start_timestamp BETWEEN '8/1/2007' AND '9/8/2007'
> > And D.code IN ('ID_123')
> > $BODY$
> > LANGUAGE 'sql' IMMUTABLE STRICT;
> > 
> > I would like for the above to be a little bit more dynamic in that the
> > start_timestamp and the code can be input-fields.
> > 
> > eg:
> > 
> > CREATE OR REPLACE FUNCTION foo_func(fromdate timestamp, todate
> > timestamp, code text)
> >   RETURNS SETOF foo AS
> > $BODY$
> > SELECT
> > TRH.ID,
> > TRH.data1,
> > TRH.data2,
> > FROM D 
> > INNER JOIN  TS 
> >  ON TS.id = D.id
> > inner join TRH
> >  on ts.id = trh.id
> > WHERE D.start_timestamp BETWEEN fromdate AND todate
> > And D.code IN (code)
> > $BODY$
> > LANGUAGE 'sql' IMMUTABLE STRICT;
> > 
> > How can I go about this this? The above will fail due to missing columns
> > fromdate/todate/code.
> > 
> > Or should I use plpgsql as SQL cannot handle variable substitution?
> > 
> > What about doing  dynamic SQL eg:
> > 
> > Dsql = select X,Y,Z from foo, join bar on bar.a = foo.a 
> >where D.start_timestamp between ' || fromdate ||' and ' ||
> > todate||'
> > 
> > execute DSQL
> > 
> > Thanks for any/all help.
> 
> 
> Seems like I found this after I posted the question. (Doh! Why does this
> always happen)
> 
> Variable substition can happen using $1/$2/$3 notation.
> 
> CREATE OR REPLACE FUNCTION foo_func(timestamp,timestamp,code)
>  RETURNS SETOF foo AS
> BODY$
>   SELECT
>   TRH.ID,
>   TRH.data1,
>   TRH.data2,
>   FROM D 
>   INNER JOIN  TS 
>ON TS.id = D.id
>   inner join TRH
>on ts.id = trh.id
>   WHERE D.start_timestamp BETWEEN $1 AND $2
>   And D.code IN ($3)
> $BODY$
> LANGUAGE 'sql' IMMUTABLE STRICT;
> 
> 
> But If I were to use ALIASINg, I get an error
> 
> eg: DECLARE 
>   DECLARE
> fromdate ALIAS for $1;
> todate ALIAS for $2;
> code ALIAS for $3;
> 
> 
> ERROR:  syntax error at or near "ALIAS"
> LINE 5: fromdate ALIAS for $1;


anyone knows how come I can't use the reference fromdate/todate etc or
use aliases but have to resort to using $1/$2 etc?

Many Thanks

---(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] CPU spike when doing PARSE (what is this?)

[Ow Mun Heng]

Just a short background.

using Perl-DBI to pull data from mssql into PG and \copy into a temp
table where the following is done.


my $query1 = "DELETE FROM $table_name
  WHERE $unique_id in
  (SELECT $unique_id from $table_name_loading)";
my $query2 = "INSERT INTO $table_name SELECT * FROM $table_name_loading";
my $query3 = "UPDATE sync_log SET last_sync=?,
  record_update_date_time=current_timestamp
  WHERE table_name=?
  AND db_name = ?";
my $query4 = "TRUNCATE TABLE $table_name_loading";


I constantly see an operation in htop (an alternative to top)

postgres:username databasename 127.0.0.1(37833) PARSE

which sucks up huge blobs of my CPU time and I would like to know what it is 
exactly. 
I would not be surprised if it's the DELETE which is the bottleneck, as it's 
DELETING 
from a huge table > 6 million in size from the loading_temp_table.



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

Re: [GENERAL] For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER

[Joshua D. Drake]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Ow Mun Heng wrote:
> On Mon, 2007-09-17 at 22:14 -0700, Joshua D. Drake wrote:
>> Phoenix Kiula wrote:
>>> So a YES/NO question:
>>>
>>> Q: To get rid of index bloat, is a VACUUM ANALYZE enough? Or must I
>>> reindex/cluster indexes?
>> If you overrun your max_fsm_pages, no:
>>   else yes;
> 
> Maybe my english suck, but I don't understand the above answer.
> 
> If I overrun my Max_FSM_pages then a vacuum analyse is enough to return
> it back to normal.

If you overrun your max_fsm_pages then vacuum analyze IS NOT ENOUGH. If
you do not overrun your max_fsm_pages, yes vacuum analyze can deal with
the issue.

Joshua D. Drake

> 
> If I _didn't_ overrun my fsm, then a reindex/cluster is necessary.
> 
> Did I get that right? (I feel it's wrong and a reindex/cluster is needed
> only when I overrun my max_fsm)
> 
> 
> 


- --

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFG72kwATb/zqfZUUQRAqNMAJsFjqWirgGF+VlEIwaVDnxBAefeSwCfesD1
osqiudjcEY/tyibvNZRJ/UU=
=apjz
-END PGP SIGNATURE-

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

   http://archives.postgresql.org/

Re: [GENERAL] For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER

[Ow Mun Heng]

On Mon, 2007-09-17 at 22:14 -0700, Joshua D. Drake wrote:
> Phoenix Kiula wrote:
> > So a YES/NO question:
> > 
> > Q: To get rid of index bloat, is a VACUUM ANALYZE enough? Or must I
> > reindex/cluster indexes?
> 
> If you overrun your max_fsm_pages, no:
>   else yes;

Maybe my english suck, but I don't understand the above answer.

If I overrun my Max_FSM_pages then a vacuum analyse is enough to return
it back to normal.

If I _didn't_ overrun my fsm, then a reindex/cluster is necessary.

Did I get that right? (I feel it's wrong and a reindex/cluster is needed
only when I overrun my max_fsm)




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

Re: [GENERAL] For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER

[Joshua D. Drake]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Phoenix Kiula wrote:
> The manual is vague. Several threads about this, in language that is
> ambiguous to me.
> 
> So a YES/NO question:
> 
> Q: To get rid of index bloat, is a VACUUM ANALYZE enough? Or must I
> reindex/cluster indexes?

If you overrun your max_fsm_pages, no:
  else yes;

Sincerely,

Joshua D. Drake

> 
> Thanks.
> 
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faq
> 


- --

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFG717QATb/zqfZUUQRAh6uAJ9CGXbA2BxXvMbSZP9Gv8gI9QBkXgCePhqe
6aS3fp60g7YrWECspTVcxyE=
=u2o/
-END PGP SIGNATURE-

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

[GENERAL] For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER

[Phoenix Kiula]

The manual is vague. Several threads about this, in language that is
ambiguous to me.

So a YES/NO question:

Q: To get rid of index bloat, is a VACUUM ANALYZE enough? Or must I
reindex/cluster indexes?

Thanks.

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

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

[GENERAL] Q:Aggregrating Weekly Production Data. How do you do it?

[Ow Mun Heng]

Just wondering how everyone is doing aggregration of production data.

Data which runs in the vicinity of a few million a week.

What are the methods which will effectively provide the
min/max/average/count/stdev of the weekly sample size based on different
products/software mix etc.

and still be able to answer correctly, what's the average of data_1 over
the pass 2 months? 

I can't just take the average of an 8 averages of each week)

eg:
wk   avg data_1
w1 - 2
w2 - 2
w3 - 2
w4 - 3
w5 - 1
w6 - 2
w7 - 2
w8 - 2
average of past 2 months = ave(w1-w8) which is statistically wrong.

using sum of data_1 per week would work though. Please share your
expertise / experience.

(getting min/max/count isn't much of an issue. Stdev is the main issue I
believe)

One such instance I've read about is..

>From this website : (it references using SQL Server Analysis services
but I think the concept is the same)

http://www.phptr.com/articles/printerfriendly.asp?p=337135&rl=1

1. Calculate sum of square of each sale
2. multiple the result of step 1 by the sales count
3. sum all sales
4. Square the result of step 3
5. Substract the result of step 4 from the result of step 2
6. Multiply the sales count by one less than sales count ("sales_count"
* ("sales_count" - 1))
7. Divide the result of step 5 by the result of step 6
8. Stdev will be the square root of step 7

The results are valid (verified with actual data) but I don't understand
the logic. All the Statistical books I've read marked stdev as sqrt
(sum(x - ave(x))^2 / (n - 1). The formula is very different, hence the
confusion. 


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

   http://archives.postgresql.org/

Re: [GENERAL] New/Custom DataType - Altering definition / seeing definition in pgAdmin3

[Ow Mun Heng]

On Mon, 2007-09-17 at 17:28 -0300, Roberto Spier wrote:
> Ow Mun Heng escreveu:
> > I've created a new custom datatype
> > to view the datatype in psql I do
> > => \d foo
> >
> > what is the equilvalent on pgadmin3 or any other SQL query tool?
> >
> >   
> Within pgAdmin3: File > Option > Display tab > set Types

Ah.. I see.. Many Thanks.


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

   http://archives.postgresql.org/

Re: [GENERAL] postgres catalog files problem

[Josh Harrison]

Thanks.
Actually Im currently testing "oracle to postgres migration" with a small
dataset. I haven't started working with the real dataset. I wanted to check
with a small dataset before I start with the big one.
I know the records that I deleted from the pg_class file. so what do i do
next? Will it help if I drop the problematic tables and recreate them? (like
i said im currently testing with a small datset only and my real data are in
oracle and they are not messed in nay way). What will happen to my catalog
files if i do that?
Josh

n 9/17/07, Erik Jones <[EMAIL PROTECTED]> wrote:
>
> On Sep 17, 2007, at 11:57 AM, Josh Harrison wrote:
> >
> > On 9/17/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Josh Harrison"
> > <[EMAIL PROTECTED]> writes:
> > > When i try to give this query
> >
> > > UPDATE payment
> > > SET desc='New description'
> > > WHERE payment_id='xyz'
> >
> > > I got the error
> > > ERROR: could not open relation with OID 672178
> >
> > Hmm, there apparently *is* a pg_class row for relation 'payment', else
> > you'd not get this far, and I'll bet it's got OID 672178 --- try
> > "select
> > oid from pg_class where relname = 'payment'" to see.  If so, it seems
> > likely that this is just an index corruption and you can get out of it
> > by REINDEXing pg_class_oid_index.  Depending on what PG version you
> > are
> > using, that may require special setup --- read the REINDEX reference
> > page *for your version* before proceeding.
> >
> > What version is it, anyway, and what were you doing before you got
> > this
> > error the first time?  This isn't exactly an everyday type of problem.
> >
> > regards, tom lane
> > Hi,
> > Yes...there is a relation in pg_class with the name 'payment' but
> > its oid is not 672178. So why is it giving me "could not open
> > relation with OID 672178" when i try an update statement ?
> > I use version 8.2. I think the problem started when i manually
> > deleted some rows from the pg_class catalog file instead of using
> > 'drop table' sql command. Do you think this created the problem?
> >
> > Thanks
> > josh
> >
>
> Yep, that would do it.  Never manually edit catalog tables unless you
> *really* know what you're doing and then think ten times about it
> first.  My guess is that you deleted an entry for a TOAST table or
> index on that table and there are still entries in pg_depend (as well
> as others) so that when you try to access that table it isn't finding
> the related, dependant objects.  Others may know more, but I don't
> know enough to help you get your catalogs back in order past
> restoring from a backup.  Also, if I were you I'd see if you can get
> a dump of the current database first thing.  Do you know what you
> deleted from pg_class?
>
>
> Erik Jones
>
> Software Developer | Emma(r)
> [EMAIL PROTECTED]
> 800.595.4401 or 615.292.5888
> 615.292.0777 (fax)
>
> Emma helps organizations everywhere communicate & market in style.
> Visit us online at http://www.myemma.com
>
>
>

[GENERAL] PostgreSQL Conference Fall 07, all speaker slots filled!

[Joshua D. Drake]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hello,

I wanted to drop a line and let everyone know that the current list of
speakers has been finalized for the conference. The list and their talks
can be found here:

http://www.postgresqlconference.org/

A general synopsis of each talk will be up shortly. If you are
interesting in attending please visit the registration form at the above
URL.

Also thanks to our current sponsors:

Command Prompt: http://www.commandprompt.com/
Continuent: http://www.continuent.com/
EnterpriseDB: http://www.enterprisedb.com/
Greenplum: http://www.greenplum.com/
OmniTI: http://www.omniti.com/
OTG: http://www.otg-nc.com/
Sun: http://www.sun.com/

And our local community sponsor:

PDXPUG: http://pugs.postgresql.org/pdx

If you are interested in sponsoring, please visit:

http://www.postgresqlconference.org/sponsor.html

Sincerely,

Joshua D. Drake


- --

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFG7rz+ATb/zqfZUUQRAl4jAJ9t/EIb2Ar4COQ9GPl1mS63dXgXIgCfaA64
F/5RgcSIYTw2w1oHm6GInAY=
=SZwU
-END PGP SIGNATURE-

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

   http://archives.postgresql.org/

Re: [GENERAL] processing urls with tsearch2

[Laimonas Simutis]

Thanks for the advice, for right now I went with the second option of
preprocessing the text before passing it to the to_tsquery.

However I would like to see what it would take to get some of the
dictionaries available out there to be hooked into the postgres on windows.
Does anyone have any pointers or ideas on where I can start to look if I
want to compile and add a dictionary to tsearch2 but on windows environment?

Thanks,

Laimis


On 9/13/07, Laimonas Simutis <[EMAIL PROTECTED]> wrote:
>
> Any way to install the dictionary without the make? As in is there binary
> versions of it available? I am running postgresql on windows servers...
>
> On 9/13/07, Oleg Bartunov <[EMAIL PROTECTED]> wrote:
> >
> > On Thu, 13 Sep 2007, Laimonas Simutis wrote:
> >
> > > Hey guys,
> > >
> > > maybe anyone using tsearch2 could advise on this. With the default
> > > installation, url, host and some other tokens are processed with the
> > simple
> > > dictionary. Thus term like mywebsite.com gets stored as 'mywebsite.com'.
> > The
> > > parser correctly assigns token id of type host to the term, but then
> > the
> > > dictionary the terms gets routed through is simple and what gets
> > stored is
> > > mywebsite.com
> > >
> > > The questions are:
> > >
> > > 1) is there a dictionary available that I could utilize that will
> > remove
> > > .com, .net, .org, etc? I could write one myself, but after seeing some
> > > sample dictionary implementations and C code I try to avoid, I got
> > scared a
> > > bit.
> >
> > Yes, we have dict_regex, which was developed by Sergey Karpov, see
> > details
> > http://lynx.sao.ru/~karpov/software/postgres_dict_regex.html
> > It uses pcre library and you need to know perl regexps.
> >
> > >
> > > 2) has anyone else dealt with this maybe in a different way?
> >
> > sure, preprocess text using prefered language before passing to
> > ro_tsvector
> >
> > >
> > >
> > > Thanks for any suggestions and help,
> > >
> > > Laimis
> > >
> >
> > Regards,
> > Oleg
> > _
> > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
> > Sternberg Astronomical Institute, Moscow University, Russia
> > Internet: [EMAIL PROTECTED], 
> > http://www.sai.msu.su/~megera/
> > phone: +007(495)939-16-83, +007(495)939-23-83
> >
>
>

Re: [GENERAL] postgres catalog files problem

[Tom Lane]

"Josh Harrison" <[EMAIL PROTECTED]> writes:
> When i try to give this query

> UPDATE payment
> SET desc='New description'
> WHERE payment_id='xyz'

> I got the error
> ERROR: could not open relation with OID 672178

Hmm, there apparently *is* a pg_class row for relation 'payment', else
you'd not get this far, and I'll bet it's got OID 672178 --- try "select
oid from pg_class where relname = 'payment'" to see.  If so, it seems
likely that this is just an index corruption and you can get out of it
by REINDEXing pg_class_oid_index.  Depending on what PG version you are
using, that may require special setup --- read the REINDEX reference
page *for your version* before proceeding.

What version is it, anyway, and what were you doing before you got this
error the first time?  This isn't exactly an everyday type of problem.

regards, tom lane

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

   http://archives.postgresql.org/

Re: [GENERAL] strange TIME behaviour

[Raymond O'Donnell]

On 15/09/2007 14:53, rihad wrote:


I'm still unsure if the timezone issue is at all important when
comparing timestamps (greater/less/etc), or when adding intervals to
preset dates?


Do you have situations where the interval you're dealing with spans a 
change between winter & summer time?


Ray.


---
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL PROTECTED]
---

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] creation of tables with warnings

[Scott Marlowe]

On 9/17/07, SHARMILA JOTHIRAJAH <[EMAIL PROTECTED]> wrote:
> Hi
> Sometimes when I create a table with the
> CREATE TABLE sql command,
> it creates a table but with some warnings
>
> For example, I create this table in Aqua studio as
> create table foo(col1 varchar2);
>
> gives
>
> Warnings: --->
>W (1):
>   <---
>  0 record(s) affected
>
> The table is created. So what does this warnings mean? where and how do i
> check them?

Are you sure you're using PostgreSQL?  Cause in 8.2.4 I get:

create table foo(col1 varchar2);
ERROR:  type "varchar2" does not exist
LINE 1: create table foo(col1 varchar2);

which is what I expect.  Is this a cut and paste or did you copy this by hand.

Please cut and paste EXACTLY what you're typing and what postgresql is saying.

Also, is this in psql or some other client?

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

[GENERAL] Tsearch2 - spanish

[Felipe de Jesús Molina Bravo]

Hi

I had installed postgresql-8.2.4 and tsearch2 with dictionary spanish.
My problem is:

prueba=# select to_tsvector('espanol','melón');
ERROR:  Affix parse error at 506 line

And if execute:

prueba=# select lexize('sp','melón');
 lexize  
-
 {melon}
(1 row)




I tried many dictionaries with the same results. Also I change the
codeset of files :aff and dict (from "latin1 to utf8" and "utf8 to
iso88591") and got the same error

where  can I investigate for resolve about this problem?

My dictionary at 506 line had:

flag *J:# isimo
E   > -E, ÍSIMO # grande grandísimo
E   > -E, ÍSIMOS# grande grandísimos
E   > -E, ÍSIMA # grande grandísima
E   > -E, ÍSIMAS# grande grandísimas
O   > -O, ÍSIMO # tonto tontísimo
O   > -O, ÍSIMA # tonto tontísima
O   > -O, ÍSIMOS# tonto tontísimos
O   > -O, ÍSIMAS# tonto tontísimas
L   > ÍSIMO # formal formalísimo
L   > ÍSIMA # formal formalísima
L   > ÍSIMOS# formal formalísimos
L   > ÍSIMAS# formal formalísimas

If removed "Í" then I don't have problem, but the lexema is incorrect


I saw the post
http://archives.postgresql.org/pgsql-general/2007-07/msg00888.php

Maybe Marcelo had resolve the problem, can you tell me your
configuration of tsearch2?



best regards

PD I need to resolve it for my work

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

Re: [GENERAL] New/Custom DataType - Altering definition / seeing definition in pgAdmin3

[Roberto Spier]

Ow Mun Heng escreveu:

I've created a new custom datatype

eg: 


create type foo as (
id smallint
data1 int
data2 int
)

to view the datatype in psql I do
=> \d foo

what is the equilvalent on pgadmin3 or any other SQL query tool?

  

Within pgAdmin3: File > Option > Display tab > set Types

As i understand from the docs, I can't alter / change the datatype
definition. If I need to change it, I need to drop it and re-create it
again. Correct me if I'm wrong.

  
Correct. pgAdmin3 will be your friend, as it shows a commented out drop  
type instruction   within  eery create type script.


HTH



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

Re: [GENERAL] postgres catalog files problem

[Josh Harrison]

Hi,
Yes...there is a relation in pg_class with the name 'payment' but its oid is
not 672178. So why is it giving me "could not open relation with OID 672178"
when i try an update statement ?
I use version 8.2. I think the problem started when i manually deleted some
rows from the pg_class catalog file instead of using 'drop table' sql
command. Do you think this created the problem?

Thanks
josh

On 9/17/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>
> "Josh Harrison" <[EMAIL PROTECTED]> writes:
> > When i try to give this query
>
> > UPDATE payment
> > SET desc='New description'
> > WHERE payment_id='xyz'
>
> > I got the error
> > ERROR: could not open relation with OID 672178
>
> Hmm, there apparently *is* a pg_class row for relation 'payment', else
> you'd not get this far, and I'll bet it's got OID 672178 --- try "select
> oid from pg_class where relname = 'payment'" to see.  If so, it seems
> likely that this is just an index corruption and you can get out of it
> by REINDEXing pg_class_oid_index.  Depending on what PG version you are
> using, that may require special setup --- read the REINDEX reference
> page *for your version* before proceeding.
>
> What version is it, anyway, and what were you doing before you got this
> error the first time?  This isn't exactly an everyday type of problem.
>
> regards, tom lane
>

Re: [GENERAL] creation of tables with warnings

[SHARMILA JOTHIRAJAH]

sorry about that. I copied it.This is proper one
create table foo(col1 varchar);

i use aqua studio
Thanks


Scott Marlowe <[EMAIL PROTECTED]> wrote: On 9/17/07, SHARMILA JOTHIRAJAH  wrote:
> Hi
> Sometimes when I create a table with the
> CREATE TABLE sql command,
> it creates a table but with some warnings
>
> For example, I create this table in Aqua studio as
> create table foo(col1 varchar2);
>
> gives
>
> Warnings: --->
>W (1):
>   <---
>  0 record(s) affected
>
> The table is created. So what does this warnings mean? where and how do i
> check them?

Are you sure you're using PostgreSQL?  Cause in 8.2.4 I get:

create table foo(col1 varchar2);
ERROR:  type "varchar2" does not exist
LINE 1: create table foo(col1 varchar2);

which is what I expect.  Is this a cut and paste or did you copy this by hand.

Please cut and paste EXACTLY what you're typing and what postgresql is saying.

Also, is this in psql or some other client?


   
-
Fussy? Opinionated? Impossible to please? Perfect.  Join Yahoo!'s user panel 
and lay it on us.

Re: [GENERAL] creation of tables with warnings

[Scott Marlowe]

On 9/17/07, SHARMILA JOTHIRAJAH <[EMAIL PROTECTED]> wrote:
> sorry about that. I copied it.This is proper one
> create table foo(col1 varchar);
>
> i use aqua studio

Can you copy the error too?  Or is it in some kind of popup that makes
it hard to do?  It just seems odd that you get a warning with nothing
in it.  You might wanna try issuing your create table statement from
psql and see what it says.  Some clients munge the output from
postgresql into something other than useful.  :)

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

Re: [GENERAL] postgres catalog files problem

[Erik Jones]

On Sep 17, 2007, at 12:31 PM, Josh Harrison wrote:


Thanks.
Actually Im currently testing "oracle to postgres migration" with a  
small dataset. I haven't started working with the real dataset. I  
wanted to check with a small dataset before I start with the big one.
I know the records that I deleted from the pg_class file. so what  
do i do next? Will it help if I drop the problematic tables and  
recreate them? (like i said im currently testing with a small  
datset only and my real data are in oracle and they are not messed  
in nay way). What will happen to my catalog files if i do that?

Josh


If you can successfully drop those tables, then yes.  Given that this  
is just a test database, if you have any issues doing that, I'd scrap  
the whole database.


Erik Jones

Software Developer | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com



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

Re: [GENERAL] postgres catalog files problem

[Erik Jones]

On Sep 17, 2007, at 11:57 AM, Josh Harrison wrote:


On 9/17/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Josh Harrison"  
<[EMAIL PROTECTED]> writes:

> When i try to give this query

> UPDATE payment
> SET desc='New description'
> WHERE payment_id='xyz'

> I got the error
> ERROR: could not open relation with OID 672178

Hmm, there apparently *is* a pg_class row for relation 'payment', else
you'd not get this far, and I'll bet it's got OID 672178 --- try  
"select

oid from pg_class where relname = 'payment'" to see.  If so, it seems
likely that this is just an index corruption and you can get out of it
by REINDEXing pg_class_oid_index.  Depending on what PG version you  
are

using, that may require special setup --- read the REINDEX reference
page *for your version* before proceeding.

What version is it, anyway, and what were you doing before you got  
this

error the first time?  This isn't exactly an everyday type of problem.

regards, tom lane
Hi,
Yes...there is a relation in pg_class with the name 'payment' but  
its oid is not 672178. So why is it giving me "could not open  
relation with OID 672178" when i try an update statement ?
I use version 8.2. I think the problem started when i manually  
deleted some rows from the pg_class catalog file instead of using  
'drop table' sql command. Do you think this created the problem?


Thanks
josh



Yep, that would do it.  Never manually edit catalog tables unless you  
*really* know what you're doing and then think ten times about it  
first.  My guess is that you deleted an entry for a TOAST table or  
index on that table and there are still entries in pg_depend (as well  
as others) so that when you try to access that table it isn't finding  
the related, dependant objects.  Others may know more, but I don't  
know enough to help you get your catalogs back in order past  
restoring from a backup.  Also, if I were you I'd see if you can get  
a dump of the current database first thing.  Do you know what you  
deleted from pg_class?



Erik Jones

Software Developer | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com



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

[GENERAL] creation of tables with warnings

[SHARMILA JOTHIRAJAH]

Hi
Sometimes when I create a table with the 
CREATE TABLE sql command,
it creates a table but with some warnings

For example, I create this table in Aqua studio as
create table foo(col1 varchar2);

gives 

Warnings: ---> 
   W (1): 
  <--- 
 0 record(s) affected 

The table is created. So what does this warnings mean? where and how do i check 
them?

Thanks in advance


   
-
Be a better Globetrotter. Get better travel answers from someone who knows.
Yahoo! Answers - Check it out.

Re: [GENERAL] postgres catalog files problem

[Tom Lane]

"Josh Harrison" <[EMAIL PROTECTED]> writes:
> Yes...there is a relation in pg_class with the name 'payment' but its oid is
> not 672178. So why is it giving me "could not open relation with OID 672178"
> when i try an update statement ?

Well, are there any other relations that the UPDATE might need to touch?
(Think about foreign keys, or even just indexes.)

> I use version 8.2. I think the problem started when i manually deleted some
> rows from the pg_class catalog file instead of using 'drop table' sql
> command. Do you think this created the problem?

Egad.  That is *definitely* in the category of "don't do that if you're
not damn sure of what you're doing".

regards, tom lane

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

Re: [GENERAL] postgres catalog files problem

[Josh Harrison]

Thanks Eric,
ll try to explain as much as possible

When i try to give this query

UPDATE payment
SET desc='New description'
WHERE payment_id='xyz'

I got the error
ERROR: could not open relation with OID 672178

This error was consistent for this query and it yielded the same OID every time.

When I checked the pg_class with oid=672178, there were no records
found with that OID.
So I checked the other catalog files to see if there are any reference
to that OID in any of them.
I found a couple of them in the pg_trigger files that references that
OID 672178.

That is why I tried the first query ie.,
select * from pg_trigger where tgrelid not in (select oid from pg_class)
to see if there are any other rows in pg_trigger without corresponding
OID in pg_class.

Is it common to have records in pg_triggers and other catalog files
iwthout corresponding OID in the pg_class file?

>>Do you use CLUSTER on any kind of regular basis?  Have you had any other
kind of abnormal performance issues (other errors, system crashes,
etc...)?
I don't use cluster on any kind. Im not sure about the performance since Im
working with a very small test dataset.
Thanks
Josh

On 9/17/07, Erik Jones <[EMAIL PROTECTED]> wrote:
>
>
> On Sep 17, 2007, at 9:02 AM, Josh Harrison wrote:
> >
> > On 9/17/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Josh Harrison"
> > <[EMAIL PROTECTED]> writes:
> > > if I give this query, which checks for the triggers that do not have
> > > corresponding tables in the pg_class
> >
> > > select * from pg_trigger where tgrelid not in (select relfilenode
> > from
> > > pg_class),
> >
> > This query is wrong --- relfilenode is not a join column for any other
> > table.  You should be using pg_class.oid there.
> >
> > regards, tom lane
>
> > Thanks Tom.
> > I tried it using pg_class.oid and I still have some records which r
> > not in the pg_class files. What can I do about that?
> > (BTW what does relfilenode in pg_class stands for?)
> > Thanks again
> > Josh
>
> relfilenode is the name of the actual file that holds the given
> relation's data.  Now that you've got that query right, can you give
> us some more details about what's happening?  Just to be sure, when
> checking triggers, the query should be
>
> select * from pg_trigger where tgrelid not in (select oid from
> pg_class);
>
> What are the queries that are generating these errors?  Why are you
> looking at pg_trigger?  In order to help, we'll need to know more
> about the situation that just the error type as different situations
> can yield that.  Do the same queries consistently yield the error?
> Do you see the same oids in the errors, or do they change?  Do you
> use CLUSTER on any kind of regular basis?  Have you had any other
> kind of abnormal performance issues (other errors, system crashes,
> etc...)?  The more info you give, the better help you can receive.
>
>
> Erik Jones
>
> Software Developer | Emma(r)
> [EMAIL PROTECTED]
> 800.595.4401 or 615.292.5888
> 615.292.0777 (fax)
>
> Emma helps organizations everywhere communicate & market in style.
> Visit us online at http://www.myemma.com
>
>
>

Re: [GENERAL] postgres catalog files problem

[Erik Jones]

On Sep 17, 2007, at 9:02 AM, Josh Harrison wrote:


On 9/17/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Josh Harrison"  
<[EMAIL PROTECTED]> writes:

> if I give this query, which checks for the triggers that do not have
> corresponding tables in the pg_class

> select * from pg_trigger where tgrelid not in (select relfilenode  
from

> pg_class),

This query is wrong --- relfilenode is not a join column for any other
table.  You should be using pg_class.oid there.

regards, tom lane



Thanks Tom.
I tried it using pg_class.oid and I still have some records which r  
not in the pg_class files. What can I do about that?

(BTW what does relfilenode in pg_class stands for?)
Thanks again
Josh


relfilenode is the name of the actual file that holds the given  
relation's data.  Now that you've got that query right, can you give  
us some more details about what's happening?  Just to be sure, when  
checking triggers, the query should be


select * from pg_trigger where tgrelid not in (select oid from  
pg_class);


What are the queries that are generating these errors?  Why are you  
looking at pg_trigger?  In order to help, we'll need to know more  
about the situation that just the error type as different situations  
can yield that.  Do the same queries consistently yield the error?   
Do you see the same oids in the errors, or do they change?  Do you  
use CLUSTER on any kind of regular basis?  Have you had any other  
kind of abnormal performance issues (other errors, system crashes,  
etc...)?  The more info you give, the better help you can receive.



Erik Jones

Software Developer | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.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

Re: [GENERAL] postgres catalog files problem

[Josh Harrison]

Thanks Tom.
I tried it using pg_class.oid and I still have some records which r not in
the pg_class files. What can I do about that?
(BTW what does relfilenode in pg_class stands for?)
Thanks again
Josh

On 9/17/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>
> "Josh Harrison" <[EMAIL PROTECTED]> writes:
> > if I give this query, which checks for the triggers that do not have
> > corresponding tables in the pg_class
>
> > select * from pg_trigger where tgrelid not in (select relfilenode from
> > pg_class),
>
> This query is wrong --- relfilenode is not a join column for any other
> table.  You should be using pg_class.oid there.
>
> regards, tom lane
>

Re: [GENERAL] postgres catalog files problem

[Tom Lane]

"Josh Harrison" <[EMAIL PROTECTED]> writes:
> if I give this query, which checks for the triggers that do not have
> corresponding tables in the pg_class

> select * from pg_trigger where tgrelid not in (select relfilenode from
> pg_class),

This query is wrong --- relfilenode is not a join column for any other
table.  You should be using pg_class.oid there.

regards, tom lane

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

[GENERAL] postgres catalog files problem

[Josh Harrison]

Hi,
I noticed that I have too many records in my pg_ catalog files with the same
name. For example
if I give this query, which checks for the triggers that do not have
corresponding tables in the pg_class

select * from pg_trigger where tgrelid not in (select relfilenode from
pg_class),

I get  set of records like this (omitted some fields)

tgrelid tgname   tgfoid
tgtype  tgconstrname
629324  RI_ConstraintTrigger_654082  1644   5
payment_fk_id  ...


 629324  RI_ConstraintTrigger_654083  1645   17
payment_fk_id...


That is there are records in my pg_trigger that has no corresponding tgrelid
in pg_class. Why is that? Is it okay to manually delete these records from
the catalog files?

So Im getting this errorthis error when I try to update some tables
' could not open relation with oid'

Thanks for your help
josh

Re: [GENERAL] count (*)

[Albe Laurenz]

alonso wrote:
> I've got it, but I don't know the reason of it.
> In the database table the records have duplicated and I've 
> two rows for
> every primary key - with different OIDs.
> Very strange however...

Very strange indeed.

Are you positive that there is a primary key constraint on
the column? Can you show some evidence?

> Is there any safe method to get rid of only one of each 
> duplicated row?

If "id" is your "primary key":

DELETE FROM test USING test a
WHERE test.id = a.id AND test.oid > a.oid;

Yours,
Laurenz Albe

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

   http://archives.postgresql.org/