[GENERAL] nice to have feature: default_index_tablespace

[Marc Mamin]

Hello,

SET default_tablespace is not really usefull for cases where data and
indexes should be splitted on different tablespaces.

Having an additional default_index_tablespace parameter would hence be
nice.


best regards,

Marc Mamin

[GENERAL] Grant column level permissions

[dipti shah]

Hi, from postgesql features list mentioned at
http://www.postgresql.org/about/press/features84.html, I came to know that
it is possible to grant column level permissions.

PostgreSQL is "the most secure by default" and part of that is making
security tools easy to use. 8.4 makes our existing connection and access
control more flexible and simpler to understand.
Column-Level Permissions DBAs can now grant permissions (SELECT, UPDATE) on
specific columns as well as on entire tables. This makes it easier to secure
sensitive data in your database.
Could anyone please give me the example of how to grant column level
permissions? Basically, I want to give permissions to set of
users(user-group) to only couple of columns in my table.

Thanks,
Dipti

Re: [GENERAL] Solid State Drives with PG

[Yeb Havinga]

Gordan Bobic wrote:

John R Pierce wrote:

all the enterprise SAN guys I've talked with say the Intel x25 drives 
are consumer junk, about the only thing they will use is STEC Zeus, 
and even then they mirror them.


A couple of points there.

1) Mirroring flash drives is a bit ill advised since flash has a 
rather predictable long-term wear-out failure point. It would make 
more sense to mirror with a mechanical disk and use the SSD for reads, 
with some clever firmware to buffer up the extra writes to the 
mechanical disk and return completed status as soon as the data has 
been committed to the faster flash disk.
Interesting, a few days ago I read something in the mdadm config about a 
config for mirroring over 'slower' links, and was waiting for a proper 
use case/excuse to go playing with it ;-) (looking up again)...


  -W, --write-mostly
 subsequent  devices  lists  in a --build, --create, or 
--add command will be flagged as 'write-mostly'.  This is valid for 
RAID1 only and means that
 the 'md' driver will avoid reading from these devices if 
at all possible.  This can be useful if mirroring over a slow link.


regards,
Yeb Havinga


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] beginner problems with count(*)

[Me Self]

Hello Im just getting started using postgres and Ive run run into a
problem with count(*):

When I do "select count(*) from mytable" or "select count(*) from
mytable where mycolumn=x" then I get wrong number. The number of rows
returned is lower than the actual number. How can that happen? The
table is generally updated from JDBC with autocommit true and there is
no concurrent acess to the database yet - so I shouldnt have any open
transactions. The first few rows in the table was added from
pgAdminIII which has had its database connection time out on occasions
long after I was finished doing any work though..

Im using postgres 8.4.0 on linux 32 bit. Hope someone can help?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] beginner problems with count(*)

[A. Kretschmer]

In response to Me Self :
> Hello Im just getting started using postgres and Ive run run into a
> problem with count(*):
> 
> When I do "select count(*) from mytable" or "select count(*) from
> mytable where mycolumn=x" then I get wrong number. The number of rows
> returned is lower than the actual number. How can that happen? The

What exactly do you mean with 'actual number'? Let me guess: you have a
SERIAL column, and the current value is greater than the number of rows
returned by your select, right?

Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Extending PostGreSQL/PostGIS with an abstract data type.

[donia zheni]

Dear all,

I have a question concerning "an extension of Postresql/PostGIS with a 
spatio-temporal Abstract data type".

PostgreSQL have many types 
categories : base types, composite types, domains, etc...


I would like to extend PostGIS type system with a new abstract 
data type. 

This means that i must guaranty encapsulation of attributes : 
which means normally any user of a the DBMS may use the new type through
 it's operations and can not select over the types attributes (like 
geometry, line or point types)


My question is : can this abstract data type be simply a compsite 
type (created whith CREATE TYPE and defined with plpgSQL) or must it be a
 base type (thus created inevitabely with a low level langage like C with 
typedef )?

When i tried with a composite type, i could select over my type 
attributes! and it's not an abstract data type any more!


And if it's defined with "typedef", must all operations be defined also in C or 
can some functions be defined with plpgsql? 


Thank 
you for you collaboration

Donia 


  

Re: [GENERAL] Grant column level permissions

[Michael Glaesemann]

On Apr 8, 2010, at 4:22 , dipti shah wrote:

> Hi, from postgesql features list mentioned at
> http://www.postgresql.org/about/press/features84.html, I came to know that
> it is possible to grant column level permissions.



> Could anyone please give me the example of how to grant column level
> permissions? Basically, I want to give permissions to set of
> users(user-group) to only couple of columns in my table.

Have you reviewed the fine documentation?


Michael Glaesemann
grzm seespotcode net




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Grant column level permissions

[dipti shah]

Yup. I read it and tired couple of ways but couldn't figured out how to
specify column names. It gives me below error message and hence, I asked for
the example.

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( *column* [, ...] )
[,...] | ALL [ PRIVILEGES ] ( *column* [, ...] ) }
ON [ TABLE ] *tablename* [, ...]
TO { [ GROUP ] *rolename* | PUBLIC } [, ...] [ WITH GRANT OPTION ]


techdb=# grant select(column['description']) ON techtable TO user1;
ERROR:  syntax error at or near "column"
LINE 1: grant select(column['description']) ON techtable TO user1;
 ^

Thanks,
Dipti.

On Thu, Apr 8, 2010 at 4:13 PM, Michael Glaesemann wrote:

>
> On Apr 8, 2010, at 4:22 , dipti shah wrote:
>
> > Hi, from postgesql features list mentioned at
> > http://www.postgresql.org/about/press/features84.html, I came to know
> that
> > it is possible to grant column level permissions.
>
> 
>
> > Could anyone please give me the example of how to grant column level
> > permissions? Basically, I want to give permissions to set of
> > users(user-group) to only couple of columns in my table.
>
> Have you reviewed the fine documentation?
> 
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
>

Re: [GENERAL] Grant column level permissions

[dipti shah]

Okay. I think I got it but it is not working the way it should. I have given
select permission on one column but still it is displaying both the columns.
Could you please tell me what is wrong.


techdb=# GRANT SELECT (description), UPDATE (description) ON techtable TO
user1;
GRANT
sysdb=> select * from techtable;
 number | description
+-
(0 rows)

techdb=>

Thanks.

On Thu, Apr 8, 2010 at 5:02 PM, dipti shah  wrote:

> Yup. I read it and tired couple of ways but couldn't figured out how to
> specify column names. It gives me below error message and hence, I asked for
> the example.
>
> GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( *column* [, ...] )
>
> [,...] | ALL [ PRIVILEGES ] ( *column* [, ...] ) }
> ON [ TABLE ] *tablename* [, ...]
> TO { [ GROUP ] *rolename* | PUBLIC } [, ...] [ WITH GRANT OPTION ]
>
>
> techdb=# grant select(column['description']) ON techtable TO user1;
> ERROR:  syntax error at or near "column"
> LINE 1: grant select(column['description']) ON techtable TO user1;
>  ^
>
> Thanks,
> Dipti.
>
>
> On Thu, Apr 8, 2010 at 4:13 PM, Michael Glaesemann 
> wrote:
>
>>
>> On Apr 8, 2010, at 4:22 , dipti shah wrote:
>>
>> > Hi, from postgesql features list mentioned at
>> > http://www.postgresql.org/about/press/features84.html, I came to know
>> that
>> > it is possible to grant column level permissions.
>>
>> 
>>
>> > Could anyone please give me the example of how to grant column level
>> > permissions? Basically, I want to give permissions to set of
>> > users(user-group) to only couple of columns in my table.
>>
>> Have you reviewed the fine documentation?
>> 
>>
>> Michael Glaesemann
>> grzm seespotcode net
>>
>>
>>
>>
>

Re: [GENERAL] Grant column level permissions

[Michał Pawlikowski]

On Thu, Apr 8, 2010 at 1:41 PM, dipti shah  wrote:
> Okay. I think I got it but it is not working the way it should. I have given
> select permission on one column but still it is displaying both the columns.
> Could you please tell me what is wrong.
>
>
> techdb=# GRANT SELECT (description), UPDATE (description) ON techtable TO
> user1;
> GRANT
> sysdb=> select * from techtable;
>  number | description
> +-
> (0 rows)
>
> techdb=>
>
> Thanks.

test=# CREATE USER user1 ENCRYPTED PASSWORD 'test';
CREATE ROLE
test=# CREATE TABLE t1 (col1 int, col2 int);
CREATE TABLE

test=# REVOKE ALL ON t1 FROM user1 ;
REVOKE

test=# GRANT SELECT (col1) ON t1 TO user1;
GRANT

test=# \c test user1
You are now connected to database "test" as user "user1".

test=> select col1 from t1;
 col1
--
(0 rows)

test=> select col2 from t1;
ERROR:  permission denied for relation t1




-- 
Pozdrawiam / Best Regards / Mit freundlichen Grüßen
Michal Pawlikowski
XMPP: misiekb...@gmail.com
FB: http://tinyurl.com/fbmbst
MS: http://myspace.com/mpawlikowski

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Grant column level permissions

[dipti shah]

I also tried below:

techdb=# revoke all ON techtable from public;
REVOKE
techdb=# select pc.relname, pc.relacl from pg_class pc, pg_namespace pn
where pc.relnamespace=pn.oid and pn.nspname='techdb' and
pc.relname='techtable';
 relname   |   relacl
---+-
 techtable | {postgres=arwdDxt/postgres}
(1 row)

techdb=# GRANT SELECT (description), UPDATE (description) ON techtable TO
user1;
GRANT
techdb=# select pc.relname, pc.relacl from pg_class pc, pg_namespace pn
where pc.relnamespace=pn.oid and pn.nspname='techdb' and
pc.relname='techtable';
 relname   |   relacl
---+-
 techtable | {postgres=arwdDxt/postgres}
(1 row)

Please note that giving select permission on description column doesn't made
any difference in permissions set pf pg_namespace,

techdb=# \q
> psql -h techdbdev1.lon -d techdb -E
psql (8.4.1)
Type "help" for help.

techdb=> select * from techtable;
ERROR:  permission denied for relation techtable
techdb=>

...and it gives permission denied..!

Please help me to sort this out.

Thanks.

On Thu, Apr 8, 2010 at 5:11 PM, dipti shah  wrote:

> Okay. I think I got it but it is not working the way it should. I have
> given select permission on one column but still it is displaying both the
> columns. Could you please tell me what is wrong.
>
>
> techdb=# GRANT SELECT (description), UPDATE (description) ON techtable TO
> user1;
> GRANT
> sysdb=> select * from techtable;
>  number | description
> +-
> (0 rows)
>
> techdb=>
>
> Thanks.
>
>
> On Thu, Apr 8, 2010 at 5:02 PM, dipti shah wrote:
>
>> Yup. I read it and tired couple of ways but couldn't figured out how to
>> specify column names. It gives me below error message and hence, I asked for
>> the example.
>>
>> GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( *column* [, ...] )
>>
>> [,...] | ALL [ PRIVILEGES ] ( *column* [, ...] ) }
>> ON [ TABLE ] *tablename* [, ...]
>> TO { [ GROUP ] *rolename* | PUBLIC } [, ...] [ WITH GRANT OPTION ]
>>
>>
>> techdb=# grant select(column['description']) ON techtable TO user1;
>> ERROR:  syntax error at or near "column"
>> LINE 1: grant select(column['description']) ON techtable TO user1;
>>  ^
>>
>> Thanks,
>> Dipti.
>>
>>
>> On Thu, Apr 8, 2010 at 4:13 PM, Michael Glaesemann 
>> wrote:
>>
>>>
>>> On Apr 8, 2010, at 4:22 , dipti shah wrote:
>>>
>>> > Hi, from postgesql features list mentioned at
>>> > http://www.postgresql.org/about/press/features84.html, I came to know
>>> that
>>> > it is possible to grant column level permissions.
>>>
>>> 
>>>
>>> > Could anyone please give me the example of how to grant column level
>>> > permissions? Basically, I want to give permissions to set of
>>> > users(user-group) to only couple of columns in my table.
>>>
>>> Have you reviewed the fine documentation?
>>> 
>>>
>>> Michael Glaesemann
>>> grzm seespotcode net
>>>
>>>
>>>
>>>
>>
>

Re: [GENERAL] Grant column level permissions

[dipti shah]

Ohh...sorry. It works but I am wondering why pg_namespace doesn't display
any information.

techdb=> select description from techtable;
 description
-
(0 rows)

techdb=> select number from techtable;
ERROR:  permission denied for relation techtable

Thanks a ton.

On Thu, Apr 8, 2010 at 5:22 PM, dipti shah  wrote:

> I also tried below:
>
> techdb=# revoke all ON techtable from public;
> REVOKE
> techdb=# select pc.relname, pc.relacl from pg_class pc, pg_namespace pn
> where pc.relnamespace=pn.oid and pn.nspname='techdb' and
> pc.relname='techtable';
>  relname   |   relacl
> ---+-
>  techtable | {postgres=arwdDxt/postgres}
> (1 row)
>
>
> techdb=# GRANT SELECT (description), UPDATE (description) ON techtable TO
> user1;
> GRANT
> techdb=# select pc.relname, pc.relacl from pg_class pc, pg_namespace pn
> where pc.relnamespace=pn.oid and pn.nspname='techdb' and
> pc.relname='techtable';
>  relname   |   relacl
> ---+-
>  techtable | {postgres=arwdDxt/postgres}
> (1 row)
>
> Please note that giving select permission on description column doesn't
> made any difference in permissions set pf pg_namespace,
>
> techdb=# \q
> > psql -h techdbdev1.lon -d techdb -E
> psql (8.4.1)
> Type "help" for help.
>
> techdb=> select * from techtable;
> ERROR:  permission denied for relation techtable
> techdb=>
>
> ...and it gives permission denied..!
>
> Please help me to sort this out.
>
> Thanks.
>
> On Thu, Apr 8, 2010 at 5:11 PM, dipti shah wrote:
>
>> Okay. I think I got it but it is not working the way it should. I have
>> given select permission on one column but still it is displaying both the
>> columns. Could you please tell me what is wrong.
>>
>>
>> techdb=# GRANT SELECT (description), UPDATE (description) ON techtable TO
>> user1;
>> GRANT
>> sysdb=> select * from techtable;
>>  number | description
>> +-
>> (0 rows)
>>
>> techdb=>
>>
>> Thanks.
>>
>>
>> On Thu, Apr 8, 2010 at 5:02 PM, dipti shah wrote:
>>
>>> Yup. I read it and tired couple of ways but couldn't figured out how to
>>> specify column names. It gives me below error message and hence, I asked for
>>> the example.
>>>
>>> GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( *column* [, ...] )
>>>
>>> [,...] | ALL [ PRIVILEGES ] ( *column* [, ...] ) }
>>> ON [ TABLE ] *tablename* [, ...]
>>> TO { [ GROUP ] *rolename* | PUBLIC } [, ...] [ WITH GRANT OPTION ]
>>>
>>>
>>> techdb=# grant select(column['description']) ON techtable TO user1;
>>> ERROR:  syntax error at or near "column"
>>> LINE 1: grant select(column['description']) ON techtable TO user1;
>>>  ^
>>>
>>> Thanks,
>>> Dipti.
>>>
>>>
>>> On Thu, Apr 8, 2010 at 4:13 PM, Michael Glaesemann >> > wrote:
>>>

 On Apr 8, 2010, at 4:22 , dipti shah wrote:

 > Hi, from postgesql features list mentioned at
 > http://www.postgresql.org/about/press/features84.html, I came to know
 that
 > it is possible to grant column level permissions.

 

 > Could anyone please give me the example of how to grant column level
 > permissions? Basically, I want to give permissions to set of
 > users(user-group) to only couple of columns in my table.

 Have you reviewed the fine documentation?
 

 Michael Glaesemann
 grzm seespotcode net




>>>
>>
>

Re: [GENERAL] Grant column level permissions

[Michał Pawlikowski]

On Thu, Apr 8, 2010 at 1:52 PM, dipti shah  wrote:
> techdb=> select * from techtable;
> ERROR:  permission denied for relation techtable
> techdb=>
>
> ...and it gives permission denied..!

This should work:
SELECT description from techtable;
UPDATE techtable SET description = 'xyz'

This should not work:
select * from techtable;
UPDATE techtable SET other_column = 'xyz'

-- 
Pozdrawiam / Best Regards / Mit freundlichen Grüßen
Michal Pawlikowski
XMPP: misiekb...@gmail.com
FB: http://tinyurl.com/fbmbst
MS: http://myspace.com/mpawlikowski

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Extending PostGreSQL/PostGIS with an abstract data type.

[Craig Ringer]

donia zheni wrote:

> And if it's defined with "typedef", must all operations be defined also
> in C or can some functions be defined with plpgsql?

If you want a new data type that's only accessible using functions and
operators defined to operate on it, you must write it in C.

If you wanted to use PL/PgSQL to implement some operators, you'd have to
expose innards of the data type at the SQL level. Since you explicitly
do not want to do this, you cannot use PL/PgSQL.

--
Craig Ringer

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] [offtopic] How do you name a table...

[Ognjen Blagojevic]

Hi,

How do you name a table which sole purpose is to store a list of values? 
E.g.


Table: sex
id name
---
1 male
2 female

Table: day
id name
---
1 Sunday
2 Monday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday

Is this:
a. Lookup table
b. Classifier
c. Cypher(er)?

I'm looking for the appropriate term in English.

Regards,
Ognjen

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [offtopic] How do you name a table...

[Ian Barwick]

2010/4/8 Ognjen Blagojevic :
> Hi,
>
> How do you name a table which sole purpose is to store a list of values?
(...)
> Is this:
> a. Lookup table
> b. Classifier
> c. Cypher(er)?
>
> I'm looking for the appropriate term in English.

I'd call it a lookup-table.


Ian Barwick

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Modify standard PG type casts?

[Łukasz Dejneka]

Hi all

Is it possible to modify a type cast which is a standard cast in
Postgres? I need to modify the ::TSQuery type cast, so it points to my
own function. I would preffer not to create additional custom user
cast.

Manual only mentions about creating and dropping custom user casts via
SQL commands, I tried to locate the sourcecode which defines them, but
was unsuccessful... Maybe someone knows where it sits and could point
me to the right source file.

Is creating a custom cast the only option?

Thanks in advance

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [offtopic] How do you name a table...

[Yeb Havinga]

Ognjen Blagojevic wrote:

Hi,

How do you name a table which sole purpose is to store a list of 
values? E.g.


Is this:
a. Lookup table
b. Classifier
c. Cypher(er)?

d. valueset?

regards,
Yeb Havinga


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [offtopic] How do you name a table...

[Karsten Hilbert]

On Thu, Apr 08, 2010 at 10:16:57PM +0900, Ian Barwick wrote:

> > How do you name a table which sole purpose is to store a list of values?
> (...)
> > Is this:
> > a. Lookup table
> > b. Classifier
> > c. Cypher(er)?

lookup (*_lu, lu_*) or enum or just what it is (gender,
document_type, ...)

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [offtopic] How do you name a table...

[Bill Moran]

In response to Ognjen Blagojevic :

> Hi,
> 
> How do you name a table which sole purpose is to store a list of values? 
> E.g.
> 
> Table: sex
> id name
> ---
> 1 male
> 2 female
> 
> Table: day
> id name
> ---
> 1 Sunday
> 2 Monday
> 3 Tuesday
> 4 Wednesday
> 5 Thursday
> 6 Friday
> 7 Saturday
> 
> Is this:
> a. Lookup table
> b. Classifier
> c. Cypher(er)?
> 
> I'm looking for the appropriate term in English.

I try to make it an ENUM when it's very unlikely to change, i.e. day of
the week is a good candidate for an enum ... when's the last time that
changed?

For lookup tables that aren't static enough to be an enum, it usually
ends up appended with _list (i.e. gender_list, county_list, etc)

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Unable to uninstall postgres from Add/Remove programs

[Jeffrey Ottery]

Running Windows XP Pro. Have been successfully using and deploying
postgres for 2 years.

When I try to Uninstall PostgreSQL 8.3 using Windows Add/Remove
Programs I get this message:

Service "PostgreSQL Database Server 8.3' (pgsql-8.3) failed to start.
Verify that you have sufficient privileges to start system services.

I have tried the following:

Created and logged on as various users as members of Administrators
group.
Deleted the 'postgres' user (is this a problem?)
Removed all references to postgres in the registry. Rebooted
Removed the PostgreSQL Service from the service list. (sc delete
pgsql-8.3)
Deleted the whole Postgres 8.3 directory in Program Files.

I have worked on this for 6 hours today to no avail. G.

Desperate for some suggestions??

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Cannot read block 348938 of pdbsynchtable

[Utsav Turray]

Dear All ,

I am using postgres  7.3. on RHEL 4.0

If I do any operation(select , delete , count , update , vacuum,reindex) on a 
table ; the error comes "Cannot read block 348938 of pdbsynchtable"

Given below is the o/p of the files corresponding to the table 

-rw---  1 postgres postgres 1073741824 Apr  7 10:56 25205
-rw---  1 postgres postgres 1073741824 Aug 29  2009 25205.1
-rw---  1 postgres postgres 1073741824 Mar 30 12:32 25205.2
-rw---  1 postgres postgres 1048674304 Apr  7 10:55 25205.3

I some blogs its given that this problem occurs due to bad sectors on disk and 
the resolution given is reidexing, vacuuming the table or restoring to the last 
backup.

In my case neither the table is getting reindexed nor vaccum ,even i am not 
able to get the count of the recordss in the table. There is no option of 
restoring to backup as i dont have one.
Even if If i try to pad the file  25205.3  using DD command I am not able to 
calculate the bytes to be padded as the total count of the blocks is comming 
out to be 521228 and the error is coming cannot read the 348938 block.

Kindly help in identifying and solving this problem.


Regards,
Utsav
Disclaimer :- This e-mail and any attachment may contain confidential, 
proprietary or legally privileged information. If you are not the original 
intended recipient and have erroneously received this message, you are 
prohibited from using, copying, altering or disclosing the content of this 
message. Please delete it immediately and notify the sender. Newgen Software 
Technologies Ltd (NSTL)  accepts no responsibilities for loss or damage arising 
from the use of the information transmitted by this email including damages 
from virus and further acknowledges that no binding nature of the message shall 
be implied or assumed unless the sender does so expressly with due authority of 
NSTL. 

Re: [GENERAL] [offtopic] How do you name a table...

[Justin Graf]

On 4/8/2010 9:30 AM, Bill Moran wrote:
> In response to Ognjen Blagojevic:
>
>>
>> Is this:
>> a. Lookup table
>> b. Classifier
>> c. Cypher(er)?
>>
>> I'm looking for the appropriate term in English.
>>  
> I try to make it an ENUM when it's very unlikely to change, i.e. day of
> the week is a good candidate for an enum ... when's the last time that
> changed?
>
> For lookup tables that aren't static enough to be an enum, it usually
> ends up appended with _list (i.e. gender_list, county_list, etc)
>
>
As  others have said enum for things that are static like day, week, 
month etc..
For things that are not static and change like system/application 
settings  i do something like this

CREATE TABLE syssettings
(
   sys_id serial primary key,
   sys_group text,
   sys_value text,
   sys_displayvalue text,
   sys_datatype text DEFAULT 'text'
)

This allows for easy grouping values together and assigning the data 
type for casting, along with how the information is to be displayed in 
the user interface.

example what the data looks like:  the below data is used to create 
 pull down list on a website.  So if the user of the application 
wants to add more options all they have is make an entry.

15;"ExamLevel";"1";"Level 1";"integer"
16;"ExamLevel";"2";"Level 2";"integer"
17;"ExamLevel";"3";"Level 3";"integer"
18;"QuestionOrder";"StandardList";"Standard not random";"text"
19;"QuestionOrder";"RandomList";"Random from List";"text"
20;"QuestionOrder";"Random";"Random From All Questions";"text"
21;"ExamType";"MPI";"MPI";"char(10)"
22;"ExamType";"RT";"RT";"char(10)"
23;"ExamType";"UT";"UT";"char(10)"
24;"ExamType";"ECT";"ECT";"char(10)"
25;"ExamType";"LPI";"LPI";"char(10)"



All legitimate Magwerks Corporation quotations are sent in a .PDF file 
attachment with a unique ID number generated by our proprietary quotation 
system. Quotations received via any other form of communication will not be 
honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally 
privileged, confidential or other information proprietary to Magwerks 
Corporation and is intended solely for the use of the individual to whom it 
addresses. If the reader of this e-mail is not the intended recipient or 
authorized agent, the reader is hereby notified that any unauthorized viewing, 
dissemination, distribution or copying of this e-mail is strictly prohibited. 
If you have received this e-mail in error, please notify the sender by replying 
to this message and destroy all occurrences of this e-mail immediately.
Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cannot read block 348938 of pdbsynchtable

[Ben Chobot]

On Apr 7, 2010, at 11:12 PM, Utsav Turray wrote:

> Dear All ,
>  
> I am using postgres  7.3. on RHEL 4.0

I don't know the answer to your problem, but I do know that you have many, many 
years worth of bug fixes, improvements, and optimizations you are missing due 
to running on such old software. Newer versions are still free, so do yourself 
a favor and address whatever has kept you from upgrading until now.

[GENERAL] fulltext search stemming/ spelling problems

[Corin]

Hi!

I'm using postgres 8.4.3 and try to get stemming/ wrong word correction 
working.


I already installed the myspell dictionaries using apt-get and created 
postgres dictionaries like this:


Fulltext search configuration »public.english_ispell«
Parser: »pg_catalog.default«
 Token  |Dictionaries
-+
asciihword  | english_ispell,english_stem,simple
asciiword   | english_ispell,english_stem,simple
email   | simple
file| simple
float   | simple
host| simple
hword   | english_ispell,english_stem,simple
hword_asciipart | english_ispell,english_stem,simple
hword_numpart   | simple
hword_part  | english_ispell,english_stem,simple
int | simple
numhword| simple
numword | simple
sfloat  | simple
uint| simple
url | simple
url_path| simple
version | simple
word| english_ispell,english_stem,simple

But when I do, for example, SELECT to_tsvector('english_ispell', 
'gitar') the result is only:

'gitar':1

Shouldn't the word be corrected to 'guitar'?

SELECT plainto_tsquery('english_ispell','gitar') doesn't work neither:
'gitar'

Thanks,
Corin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] C-language functions: SRF question

[Jorge Arevalo]

Hello,

I need to code a set-returning function, but it's my first time, and
I'm not sure if my point is the right one. I'd like to know your
opinion:

My function will return an array of structs. Then, In the first call
(SRF_IS_FIRSTCALL() is true), I get the array from another function.
For the next calls, I'll need to access a position of this array, so,
I'll need to store it, and the position needed, in any place. The
place should be the user_fctx field of the FuncCallContext.

I've created a struct that will store my array of structs and an int
value, a index. In the first call, this index is 0. In next calls, I
can recover data from FuncCallContext, get the struct indexed by the
index value, build my tuple, return it and increment the index.

Is this a good way of doing it? Is it possible? And another small
question: if the memory for my array of structs is allocated inside
the function that provides me the array, should I deallocate it in the
SRF? Using pfree? It wasn't allocated by palloc...


Thanks in advance, and best regards,
Jorge

--
http://www.gis4free.org/blog

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] C-language functions: SRF question

[Merlin Moncure]

On Thu, Apr 8, 2010 at 11:40 AM, Jorge Arevalo
 wrote:
> Is this a good way of doing it? Is it possible? And another small
> question: if the memory for my array of structs is allocated inside
> the function that provides me the array, should I deallocate it in the
> SRF? Using pfree? It wasn't allocated by palloc...

why didn't you use palloc? external library? postgresql guarantees
that memory allocated w/palloc is cleaned up.  you pretty much have to
assume any backend api calls can bounce you out of the transaction in
which case you will leak if you alloc'd with non palloc allocator.  do
not call pfree on any memory allocated by anything else than palloc.
every malloc needs a free.

if the array construction code is also yours you may want to consider
reworking it to take an allocator.   the more you use palloc the less
problems you will have (and as a bonus, you get to worry less about
pfree'ing stuff).

if you must use malloc and don't want to be cavalier about leaking
memory, try and keep allocations as short lived as possible or at very
specific points, like a static pointer.

be sure to review the memory manager readme in src/backend/utils/mmgr/README

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cannot read block 348938 of pdbsynchtable

[Greg Stark]

On Thu, Apr 8, 2010 at 7:12 AM, Utsav Turray  wrote:
> Even if If i try to pad the file  25205.3  using DD command I am not able to
> calculate the bytes to be padded as the total count of the blocks is comming
> out to be 521228 and the error is coming cannot read the 348938 block.

Assuming 25205 is the right file for this table then block 348938
would be in 25205.2 and be bytes 694,352kB - 694,360kB.

try

 dd if=25205.2 of=/tmp/data  bs=8192 skip=$(( 348938 - 2 * 128 * 1024)) count=1

see if you get the same error. If you do and you're happy to zero out
the bad block, deleting any data on it you could do something like:

 dd if=/dev/zero of=25205.2   bs=8192 seek=$(( 348938 - 2 * 128 *
1024)) count=1

-- 
greg

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] C-language functions: SRF question

[Jorge Arevalo]

On Thu, Apr 8, 2010 at 6:18 PM, Merlin Moncure  wrote:
> On Thu, Apr 8, 2010 at 11:40 AM, Jorge Arevalo
>  wrote:
>> Is this a good way of doing it? Is it possible? And another small
>> question: if the memory for my array of structs is allocated inside
>> the function that provides me the array, should I deallocate it in the
>> SRF? Using pfree? It wasn't allocated by palloc...
>
> why didn't you use palloc? external library? postgresql guarantees
> that memory allocated w/palloc is cleaned up.  you pretty much have to
> assume any backend api calls can bounce you out of the transaction in
> which case you will leak if you alloc'd with non palloc allocator.  do
> not call pfree on any memory allocated by anything else than palloc.
> every malloc needs a free.
>
> if the array construction code is also yours you may want to consider
> reworking it to take an allocator.   the more you use palloc the less
> problems you will have (and as a bonus, you get to worry less about
> pfree'ing stuff).
>
> if you must use malloc and don't want to be cavalier about leaking
> memory, try and keep allocations as short lived as possible or at very
> specific points, like a static pointer.
>

The memory is allocated in a function of an external library. But I
can force this library to allocate memory in a valid context (the one
pointed by fcinfo->flinfo->fn_mcxt, actually). If I do that, I suppose
I can call pfree. Or even better,  I don't need to worry about that,
am I right?

BTW, this code is for WKT Raster. A PostGIS extension. We can use the
memory context I said (fcinfo->flinfo->fn_mcxt) to allocate memory
when we need to call one of our functions from a standard "version 1"
function, but is this the right context? I mean, the context we should
use for a whole-plugin-lifetime persistence.

> be sure to review the memory manager readme in src/backend/utils/mmgr/README
>
Excelent reading! Thanks for the info. And sorry if my questions are very basic.

Best regards,
Jorge

> merlin
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Postgres doesn't seem to enforce array dimensions

[Joseph S]

What's up with Postgres and array dimensions?  The table definition 
doesn't even store how many dimensions the array should be.


[local]:playpen=> create table arrtest(
playpen(>id SERIAL PRIMARY KEY,
playpen(>   arrone int[],
playpen(>   arrtwo int[][]
playpen(> );
NOTICE:  CREATE TABLE will create implicit sequence "arrtest_id_seq" for 
serial column "arrtest.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
"arrtest_pkey" for table "arrtest"

CREATE TABLE
Time: 22.688 ms
[local]:playpen=> INSERT into arrtest (arrone) VALUES (null);  --should 
work, and does

INSERT 0 1
Time: 3.798 ms
[local]:playpen=> INSERT into arrtest (arrone) VALUES ( ARRAY[1,2]);
INSERT 0 1
Time: 0.668 ms
[local]:playpen=> INSERT into arrtest (arrone) VALUES ( ARRAY[[3,4]]); 
--should not work

INSERT 0 1
Time: 0.715 ms
[local]:playpen=> INSERT into arrtest (arrtwo) VALUES ( ARRAY[5,6]); 
--should not work

INSERT 0 1
Time: 0.671 ms
[local]:playpen=> INSERT into arrtest (arrtwo) VALUES ( ARRAY[[7,8]]);
INSERT 0 1
Time: 0.704 ms
[local]:playpen=> select * from arrtest;
 id | arrone  | arrtwo
+-+-
  1 | |
  2 | {1,2}   |
  3 | {{3,4}} |
  4 | | {5,6}
  5 | | {{7,8}}
(5 rows)

Time: 1.217 ms
[local]:playpen=>  \d arrtest
  Table "public.arrtest"
 Column |   Type|  Modifiers
+---+--
 id | integer   | not null default nextval('arrtest_id_seq'::regclass)
 arrone | integer[] |
 arrtwo | integer[] |
Indexes:
"arrtest_pkey" PRIMARY KEY, btree (id)

[local]:playpen=> select version();
  version 


---
 PostgreSQL 8.4.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 
20060404 (Red Hat 3.4.6-9), 32-bit


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres doesn't seem to enforce array dimensions

[Jeff Davis]

On Thu, 2010-04-08 at 13:30 -0400, Joseph S wrote:
> What's up with Postgres and array dimensions?  The table definition 
> doesn't even store how many dimensions the array should be.
> 

That's a little strange, but it is documented behavior:

>From http://www.postgresql.org/docs/8.4/static/arrays.html :

"However, the current implementation ignores any supplied array size
limits, i.e., the behavior is the same as for arrays of unspecified
length. 

The current implementation does not enforce the declared number of
dimensions either. Arrays of a particular element type are all
considered to be of the same type, regardless of size or number of
dimensions. So, declaring the array size or number of dimensions in
CREATE TABLE is simply documentation; it does not affect run-time
behavior."

Regards,
Jeff Davis


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] fulltext search stemming/ spelling problems

[Oleg Bartunov]

On Thu, 8 Apr 2010, Corin wrote:


Hi!

I'm using postgres 8.4.3 and try to get stemming/ wrong word correction 
working.


I already installed the myspell dictionaries using apt-get and created 
postgres dictionaries like this:


Fulltext search configuration ?public.english_ispell?
Parser: ?pg_catalog.default?
Token  |Dictionaries
-+
asciihword  | english_ispell,english_stem,simple
asciiword   | english_ispell,english_stem,simple
email   | simple
file| simple
float   | simple
host| simple
hword   | english_ispell,english_stem,simple
hword_asciipart | english_ispell,english_stem,simple
hword_numpart   | simple
hword_part  | english_ispell,english_stem,simple
int | simple
numhword| simple
numword | simple
sfloat  | simple
uint| simple
url | simple
url_path| simple
version | simple
word| english_ispell,english_stem,simple

But when I do, for example, SELECT to_tsvector('english_ispell', 'gitar') the 
result is only:

'gitar':1

Shouldn't the word be corrected to 'guitar'?


english_ispell dictionary is a morphology kind of dictionary ! Read docs.
Also, simple dictionary will never invoked, since english_stem dictionary
recognizes everything !




SELECT plainto_tsquery('english_ispell','gitar') doesn't work neither:
'gitar'


Better, use ts_debug() function or ts_dict() for testing.



Thanks,
Corin




Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] fulltext search stemming/ spelling problems

[Corin]

On 08.04.2010 20:15, Oleg Bartunov wrote:

On Thu, 8 Apr 2010, Corin wrote:

english_ispell dictionary is a morphology kind of dictionary ! Read docs.
Also, simple dictionary will never invoked, since english_stem dictionary
recognizes everything !
I'm not sure what you mean with 'morphology'. I sure read the docs but 
couldn't find anything about 'morphology disctionaries'.


I created it myself with the following commands, after I installed the 
ispell dictionaries using "apt-get":


CREATE TEXT SEARCH DICTIONARY english_ispell (
   TEMPLATE = ispell,
   DictFile = system_en_us,
   AffFile = system_en_us
);

CREATE TEXT SEARCH CONFIGURATION english_ispell ( COPY = 
pg_catalog.english );

ALTER TEXT SEARCH CONFIGURATION english_ispell
 ALTER MAPPING FOR asciiword, asciihword, hword_asciipart, word, hword, 
hword_part WITH english_ispell, english_stem;


Thank's for the hint with simple dictionary. I'll remove it - but when 
it's never triggered, I gues it won't solve my problem neither?


Better, use ts_debug() function or ts_dict() for testing.

ts_debug shows:
SELECT ts_debug('english_ispell','gitar');
(asciiword,"Word, all 
ASCII",gitar,"{english_ispell,english_stem}",english_stem,{gitar})

(1 line)

ts_dict does not seem to exist, I neither couldn't find it in the docs.


Regards,
Oleg

Thanks,
Corin


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] fulltext search stemming/ spelling problems

[Oleg Bartunov]

On Thu, 8 Apr 2010, Corin wrote:


On 08.04.2010 20:15, Oleg Bartunov wrote:

On Thu, 8 Apr 2010, Corin wrote:

english_ispell dictionary is a morphology kind of dictionary ! Read docs.
Also, simple dictionary will never invoked, since english_stem dictionary
recognizes everything !
I'm not sure what you mean with 'morphology'. I sure read the docs but 
couldn't find anything about 'morphology disctionaries'.


it means, that (from 
http://www.postgresql.org/docs/current/static/textsearch-dictionaries.html#TEXTSEARCH-ISPELL-DICTIONARY)

12.6.5. Ispell Dictionary

The Ispell dictionary template supports morphological dictionaries, which can 
normalize many different linguistic forms of a word into the same lexeme. For 
example, an English Ispell dictionary can match all declensions and 
conjugations of the search term bank, e.g., banking, banked, banks, banks', and 
bank's.

you confused with the name !



I created it myself with the following commands, after I installed the ispell 
dictionaries using "apt-get":


CREATE TEXT SEARCH DICTIONARY english_ispell (
  TEMPLATE = ispell,
  DictFile = system_en_us,
  AffFile = system_en_us
);

CREATE TEXT SEARCH CONFIGURATION english_ispell ( COPY = pg_catalog.english 
);

ALTER TEXT SEARCH CONFIGURATION english_ispell
ALTER MAPPING FOR asciiword, asciihword, hword_asciipart, word, hword, 
hword_part WITH english_ispell, english_stem;


Thank's for the hint with simple dictionary. I'll remove it - but when it's 
never triggered, I gues it won't solve my problem neither?


Better, use ts_debug() function or ts_dict() for testing.

ts_debug shows:
SELECT ts_debug('english_ispell','gitar');
(asciiword,"Word, all 
ASCII",gitar,"{english_ispell,english_stem}",english_stem,{gitar})

(1 line)

ts_dict does not seem to exist, I neither couldn't find it in the docs.


sorry, ts_lexize



Regards,
Oleg

Thanks,
Corin



Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Removing files under pg_clog

[Steven Harms]

I ran into a script today that was removing files under
/var/lib/pgsql/data/pg_clog today because they were too large
(Postgresql 7.4).  My initial thought was this could cause data loss
or corruption, can someone provide insight as to if that is correct?

Thanks,

Steve

-- 
GPG Key ID: C92EF367 / 1428 FE8E 1E07 DDA8 EFD7 195F DCCD F5B3 C92E F367

WWW: http://www.sharms.org/blog

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Removing files under pg_clog

[Alvaro Herrera]

Steven Harms escribió:
> I ran into a script today that was removing files under
> /var/lib/pgsql/data/pg_clog today because they were too large
> (Postgresql 7.4).  My initial thought was this could cause data loss
> or corruption, can someone provide insight as to if that is correct?

Yeah.  How large?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Removing files under pg_clog

[Steven Harms]

I don't have stats on how big they were getting, but they are running
this every night, which I suspect causes issues (and I suspect the
reason their logs were getting big is because they programmed a bunch
of locked transactions):

find /pgsql/data/pg_xlog -type f -mtime +1 | xargs rm -f
find /pgsql/data/pg_clog -type f -mtime +1 | xargs rm -f

On Thu, Apr 8, 2010 at 4:06 PM, Alvaro Herrera
 wrote:
> Steven Harms escribió:
>> I ran into a script today that was removing files under
>> /var/lib/pgsql/data/pg_clog today because they were too large
>> (Postgresql 7.4).  My initial thought was this could cause data loss
>> or corruption, can someone provide insight as to if that is correct?
>
> Yeah.  How large?
>
> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>



-- 
GPG Key ID: C92EF367 / 1428 FE8E 1E07 DDA8 EFD7 195F DCCD F5B3 C92E F367

WWW: http://www.sharms.org/blog

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Removing files under pg_clog

[Alvaro Herrera]

Steven Harms escribió:
> I don't have stats on how big they were getting, but they are running
> this every night, which I suspect causes issues (and I suspect the
> reason their logs were getting big is because they programmed a bunch
> of locked transactions):
> 
> find /pgsql/data/pg_xlog -type f -mtime +1 | xargs rm -f
> find /pgsql/data/pg_clog -type f -mtime +1 | xargs rm -f

Oh, the *directories* were getting big, not the files?  (Normally
pg_clog files do not grow beyond a certain, rather small size, which is
why I was asking)  Yeah, they are bound to lose or corrupt data sooner
rather than later.  If they ever see their system crash, it won't be
able to recover due to pg_xlog deletion.  (Note that pg_xlog is quite
different from pg_clog).  pg_clog deletion guarantees that they will
have problem vacuuming or something.

-- 
Alvaro Herrerahttp://www.advogato.org/person/alvherre
"And as an added bonus, now my computer goes to the toilet for me, leaving me
free to spend time on more useful activities! yay slug codefests!" (C. Parker)

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] fulltext search stemming/ spelling problems

[Corin]

On 08.04.2010 21:27, Oleg Bartunov wrote:
it means, that (from 
http://www.postgresql.org/docs/current/static/textsearch-dictionaries.html#TEXTSEARCH-ISPELL-DICTIONARY) 



12.6.5. Ispell Dictionary

The Ispell dictionary template supports morphological dictionaries, 
which can normalize many different linguistic forms of a word into the 
same lexeme. For example, an English Ispell dictionary can match all 
declensions and conjugations of the search term bank, e.g., banking, 
banked, banks, banks', and bank's.
I already read this but I don't know how to solve my problems with this 
information.


SELECT ts_lexize('english_ispell','guitar');
{guitar}
(1 line)

SELECT ts_lexize('english_ispell','bank');
{bank}
(1 line)

SELECT ts_debug('english_ispell','bank');
(asciiword,"Word, all 
ASCII",bank,"{english_ispell,english_stem}",english_ispell,{bank})

(1 line)

SELECT plainto_tsquery('english_ispell','bank');
'bank'
(1 line)

Regards,
Oleg
It would be very nice if you (or anyone else) could provide me with 
concrete instructions or any howto. What can I do to find the error in 
my setup? What output should I expect from the above comments if 
everything worked correctly?


Thanks,
Corin


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] how to debug the codes in the PostgresMain() from the begining of this function to the "for (;;)" loop that handles the connection?

[sunpeng]

how to debug the codes in the PostgresMain() from the begining of this
function to the "for (;;)" loop that handles the connection?
if i use eclipse cdt to attach the postgres process forked by postmaster,
this part of codes from the begining of this PostgresMain() to the "for
(;;)" loop could not be debugged. So anybody know how to debug this part
codes? thanks.

thanks

[GENERAL] when i logged in mydb,any sql command used to list all the tables in this mydb?

[sunpeng]

once i have created mydb and several relations in it,are there any sql
commands used to list all the tables in this mydb?
i noticed there are no database( pg_database.oid) field in pg_class table,so
i can not use
select relname from pg_class,pg_database where pg_database.datname like
'mydb' and pg_class.database = pg_database.oid;
anybody knows how to do it?
another question:how postgresql internal knows which relations belongs to
which database?

thanks

Re: [GENERAL] Postgres doesn't seem to enforce array dimensions

[Simon Riggs]

On Thu, 2010-04-08 at 11:02 -0700, Jeff Davis wrote:

> The current implementation does not enforce the declared number of
> dimensions either. Arrays of a particular element type are all
> considered to be of the same type, regardless of size or number of
> dimensions. So, declaring the array size or number of dimensions in
> CREATE TABLE is simply documentation; it does not affect run-time
> behavior.

And if you don't like that, use a CHECK constraint to enforce whatever
rule you think appropriate. All, some or none: your choice. Cool.

-- 
 Simon Riggs   www.2ndQuadrant.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] when i logged in mydb,any sql command used to list all the tables in this mydb?

[Diego Schulz]

On Thu, Apr 8, 2010 at 6:34 PM, sunpeng  wrote:
> once i have created mydb and several relations in it,are there any sql
> commands used to list all the tables in this mydb?
> i noticed there are no database( pg_database.oid) field in pg_class table,so
> i can not use
> select relname from pg_class,pg_database where pg_database.datname like
> 'mydb' and pg_class.database = pg_database.oid;
> anybody knows how to do it?
> another question:how postgresql internal knows which relations belongs to
> which database?
>
> thanks
>
>

hi,

You can use the -E option for psql, so it will output all querys
executed behind the scenes when you use meta-commands like \dt.
You can then copy and modify those querys to better suit your needs.

Example

$ psql -E mydb
psql (8.4.3)
Type "help" for help.

mydb=# \dt
.. (the query that gets executed is appears here)..

 List of relations
 Schema |   Name   | Type  | Owner
+--+---+
 public | sometable | table | myname



HTH,

diego

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] when i logged in mydb,any sql command used to list all the tables in this mydb?

[Kenichiro Tanaka]

Hello sunpeng

First,I answer this question.
>another question:how postgresql internal knows which
>relations belongs to which database?

When we use "psql" command ,it means "psql -d postgres".
#you can change default parameter to set PGDATABASE (OS parameter)

"-d" indicate your connecting database and this is the ANSWER.
So, it is natural that pg_class does not have "database" field.
We can not access other database with psql without using dblink.

I think we can  display some database's pg_class with following command,

#DISPLAY  "test" and "postgres"'s tables

#Your postgresql have to be installed dblink
#1)create dblink
select dblink_connect('test','host=postgres01 port=1843 dbname=test 
user=p843');
select dblink_connect('postgres','host=postgres01 port=1843 
dbname=postgres user=p843');


#2) display
select 'test',* from dblink('test','select oid, relname from pg_class') 
t1(oid oid, relname text)

union
select 'postgres',* from dblink('postgres','select oid, relname from 
pg_class') t1(oid oid, relname text);




Thank you

once i have created mydb and several relations in it,are there any sql 
commands used to list all the tables in this mydb?
i noticed there are no database( pg_database.oid) field in pg_class 
table,so i can not use
select relname from pg_class,pg_database where pg_database.datname 
like 'mydb' and pg_class.database = pg_database.oid;

anybody knows how to do it?
another question:how postgresql internal knows which relations belongs 
to which database?


thanks




--

Kenichiro Tanaka
K.K.Ashisuto
http://www.ashisuto.co.jp/english/index.html



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] when i logged in mydb,any sql command used to list all the tables in this mydb?

[sunpeng]

hi,Kenichiro,
  Thanks for your answer!
  Then another question comes: According to 8.4 document 44.1(Most system
catalogs are copied from the template database during database creation and
are thereafter database-specific.),we know  that each created database has
their own pg_... tables and thus if a superuser administrator wants to list
all the tables in all the databases,how do the postgresql interval implement
it? will the postgresql interval load all the pg_ tables  in all the
databases to get the final answer?
 Thanks!
peng

2010/4/8 Kenichiro Tanaka 

> Hello sunpeng
>
> First,I answer this question.
>
> >another question:how postgresql internal knows which
> >relations belongs to which database?
>
> When we use "psql" command ,it means "psql -d postgres".
> #you can change default parameter to set PGDATABASE (OS parameter)
>
> "-d" indicate your connecting database and this is the ANSWER.
> So, it is natural that pg_class does not have "database" field.
> We can not access other database with psql without using dblink.
>
> I think we can  display some database's pg_class with following command,
> 
> #DISPLAY  "test" and "postgres"'s tables
>
> #Your postgresql have to be installed dblink
> #1)create dblink
> select dblink_connect('test','host=postgres01 port=1843 dbname=test
> user=p843');
> select dblink_connect('postgres','host=postgres01 port=1843 dbname=postgres
> user=p843');
>
> #2) display
> select 'test',* from dblink('test','select oid, relname from pg_class')
> t1(oid oid, relname text)
> union
> select 'postgres',* from dblink('postgres','select oid, relname from
> pg_class') t1(oid oid, relname text);
>
> 
>
> Thank you
>
>
>  once i have created mydb and several relations in it,are there any sql
>> commands used to list all the tables in this mydb?
>> i noticed there are no database( pg_database.oid) field in pg_class
>> table,so i can not use
>> select relname from pg_class,pg_database where pg_database.datname like
>> 'mydb' and pg_class.database = pg_database.oid;
>> anybody knows how to do it?
>> another question:how postgresql internal knows which relations belongs to
>> which database?
>>
>> thanks
>>
>>
>
> --
> 
> Kenichiro Tanaka
> K.K.Ashisuto
> http://www.ashisuto.co.jp/english/index.html
> 
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Unable to uninstall postgres from Add/Remove programs

[Craig Ringer]

On 8/04/2010 2:01 PM, Jeffrey Ottery wrote:

Running Windows XP Pro. Have been successfully using and deploying
postgres for 2 years.

When I try to Uninstall PostgreSQL 8.3 using Windows Add/Remove
Programs I get this message:

Service "PostgreSQL Database Server 8.3' (pgsql-8.3) failed to start.
Verify that you have sufficient privileges to start system services.


One must wonder why the uninstaller wants to *start* the postgresql service.

I don't suppose you know whether you installed with the "One Click" 
installer or the msi installer from postgresql.org ? If there's no 
reference to EnterpriseDB anywhere in the documentation and the Start 
menu entry for PostgreSQL doesn't have a "StackBuilder" it's probably 
the MSI installer.



I have tried the following:

Created and logged on as various users as members of Administrators
group.
Deleted the 'postgres' user (is this a problem?)
Removed all references to postgres in the registry. Rebooted
Removed the PostgreSQL Service from the service list. (sc delete
pgsql-8.3)
Deleted the whole Postgres 8.3 directory in Program Files.

I have worked on this for 6 hours today to no avail. G.

Desperate for some suggestions??


As a last resort you can manually uninstall Pg. You're half-way there 
already :S


Make sure no Pg processes are running, then *delete* the program 
directory. Then clean up the registry entries Windows uses to track 
program installations to get rid of the add/remove programs entry:


  http://support.microsoft.com/kb/310750

now open the Start menu, right click on "All programs", choose "explore 
all users", find the Postgresql folder and delete that too.


I don't think Pg scatters too much through the Windows registry, so if 
you've removed the uninstall entry, the program files, the start menu 
entries, the user account and the service definition you should be ok.


--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] C-Language Function on VC2005: could not load library ERROR

[chaoyong wang]

Hi,I'm using VC2005 to create PG C-language Fun in my contrib xml_index, 
which import other library, and I have add the include and lib directory by 
changing Mkvcbuild.pm and config.pl. 
But after I executed the following commands:build DEBUGperl 
install.pl "C:\Program Files\PostgreSQL\8.3"initdb.exe -D "C:\Program 
Files\PostgreSQL\8.3\data" -E UTF8 --locale=C"pg_ctl" -D "C:/Program 
Files/PostgreSQL/8.3/data" -l logfile startcreatedb testpsql 
test
when I trying to create the function by the following commands:
CREATE OR REPLACE FUNCTION create_xml_value_index(text,text,text)
RETURNS boolAS '$libdir/xml_index'LANGUAGE C STRICT IMMUTABLE;
It reports an ERROR:could not load library "C:\Program 
Files\PostgreSQL\8.3\lib/xml_index.dll": The specified module could not be 
found.
I checked the directory C:\Program Files\PostgreSQL\8.3\lib, xml_index.dll 
exists.I tried to changed "$libdir/xml_index" by "C:\\Program 
Files\\PostgreSQL\\8.3\\lib\\xml_index", error remains.
Has anyone ever encountered this problem? Thanks for all replies.
Best RegardsCristian  
_
Hotmail: Trusted email with powerful SPAM protection.
https://signup.live.com/signup.aspx?id=60969

Re: [GENERAL] when i logged in mydb,any sql command used to list all the tables in this mydb?

[Kenichiro Tanaka]

Hi peng

>  According to 8.4 document 44.1(Most system catalogs are
>  copied from the template database during database creation and are 
thereafter database
>  -specific.),we know  that each created database has their own pg_... 
tables

I believe it is right.

>  and thus if  a superuser administrator wants to list all the tables 
in all the
>  databases,how do the postgresql interval implement it? will the 
postgresql
>  interval load all the pg_  tables  in all the databases to get 
the final answer?


I think postgresql never  interval load all the pg_ tables in all 
the databases.

If administrator want to list all the tables,we heve to create application.

ex.
oid2name -q | awk '{print$2}'|grep -v template0 |xargs -l1 oid2name -d

* oid2name is contrib module.

Thank you


hi,Kenichiro,
  Thanks for your answer!
  Then another question comes: According to 8.4 document 44.1(Most 
system catalogs are copied from the template database during database 
creation and are thereafter database-specific.),we know  that each 
created database has their own pg_... tables and thus if a superuser 
administrator wants to list all the tables in all the databases,how do 
the postgresql interval implement it? will the postgresql interval 
load all the pg_ tables  in all the databases to get the final answer?

 Thanks!
peng

2010/4/8 Kenichiro Tanaka >


Hello sunpeng

First,I answer this question.

>another question:how postgresql internal knows which
>relations belongs to which database?

When we use "psql" command ,it means "psql -d postgres".
#you can change default parameter to set PGDATABASE (OS parameter)

"-d" indicate your connecting database and this is the ANSWER.
So, it is natural that pg_class does not have "database" field.
We can not access other database with psql without using dblink.

I think we can  display some database's pg_class with following
command,

#DISPLAY  "test" and "postgres"'s tables

#Your postgresql have to be installed dblink
#1)create dblink
select dblink_connect('test','host=postgres01 port=1843
dbname=test user=p843');
select dblink_connect('postgres','host=postgres01 port=1843
dbname=postgres user=p843');

#2) display
select 'test',* from dblink('test','select oid, relname from
pg_class') t1(oid oid, relname text)
union
select 'postgres',* from dblink('postgres','select oid, relname
from pg_class') t1(oid oid, relname text);



Thank you


once i have created mydb and several relations in it,are there
any sql commands used to list all the tables in this mydb?
i noticed there are no database( pg_database.oid) field in
pg_class table,so i can not use
select relname from pg_class,pg_database where
pg_database.datname like 'mydb' and pg_class.database =
pg_database.oid;
anybody knows how to do it?
another question:how postgresql internal knows which relations
belongs to which database?

thanks



-- 


Kenichiro Tanaka
K.K.Ashisuto
http://www.ashisuto.co.jp/english/index.html



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org

)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general





--

Kenichiro Tanaka
K.K.Ashisuto
http://www.ashisuto.co.jp/english/index.html



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] C-Language Function on VC2005: could not load library ERROR

[Craig Ringer]

On 9/04/2010 11:35 AM, chaoyong wang wrote:
> It reports an ERROR:
> could not load library "C:\Program 
> Files\PostgreSQL\8.3\lib/xml_index.dll": The specified module could not 
> be found.
> 
> I checked the directory C:\Program Files\PostgreSQL\8.3\lib, 
> xml_index.dll exists.
> I tried to changed "$libdir/xml_index" by "C:\\Program 
> Files\\PostgreSQL\\8.3\\lib\\xml_index", error remains.
> 
> Has anyone ever encountered this problem? Thanks for all replies.

Most likely a library that xml_index.dll requires to run cannot be
found. Use dependency walker (depends.exe) from dependencywalker.com to
identify the xml_index.dll's direct dependencies, and make sure they're
all accessible.

--
Craig Ringer

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general