Re: [GENERAL] Regex with patterns in table field

[pgdb]

Hi Chris,
 
the query below works for me -
 
select * from TABLENAME where 'TEXT' ~ pattern;
 
Thanks for helping!
 
Regards
 
chris smith writes: 

> On 4/14/06, pgdb <[EMAIL PROTECTED]>wrote: 
>> 
>> 
>> Hi Andreas, 
>> 
>> strange but I don't see html from my original
email received from the 
>> mailing list, hope this reply is ok:) 
>> 
>> If I'm not wrong, the example you've provided is
trying to return matching 
>> rows from multiple patterns and texts as inputs in
the regex search. 
>> 
>> The text in regular _expression_ have to be just a
single string(with no white 
>> character) for my case. Matching should then be
done with patterns from each 
>> row, returning the row(s) that contain matching
regex pattern(s). Appreciate 
>> any advice. Thanks. 
> 
> This page might be what you're after: 
> 
>
http://www.postgresql.org/docs/8.1/static/functions-matching.html

> 
> -- 
> Postgresql & php tutorials 
> http://www.designmagick.com/ 



Free POP3 Email from www.gawab.com
Sign up NOW and get your account @gawab.com!!

Re: [GENERAL] corrupted item pointer:???

[hubert depesz lubaczewski]

On 4/13/06, Tom Lane <[EMAIL PROTECTED]> wrote:
"hubert depesz lubaczewski" <[EMAIL PROTECTED]> writes:> On 4/13/06, Tom Lane <[EMAIL PROTECTED]> wrote:>> AFAICS, the only non-index-related occurrence of that error message
>> is in PageRepairFragmentation, which is invoked by VACUUM.  I'd say>> it indicates a real problem and you shouldn't ignore it.  You might>> try using pg_filedump or some such to examine the table and see if
>> there's anything obvious about what happened to the corrupted page.> i'm not familiar with this utility.http://sources.redhat.com/rhdb/> i can of course find it using google, but how do i check what is wrong?
pg_filedump will complain about a bad item pointer (looks like themessage will be something about "Error: Item contents extend beyond block")> i am even willing to upload the dump file, but with 4 milion records in
> table, it is going to be rather large...I don't think we want to see the whole thing!  But "pg_filedump -i -f"output would be interesting for the specific block(s) that pg_filedumpreports errors for.
if i understand correctly i have to do pg_filedump  of table, check output for errors, and make pg_filedump -i -f of problematic blocks.if that's ok - i'm running it.
as soon as i have some info - i'll let you know.depesz

Re: [GENERAL] corrupted item pointer:???

[hubert depesz lubaczewski]

On 4/13/06, Tom Lane <[EMAIL PROTECTED]> wrote:
pg_filedump will complain about a bad item pointer (looks like themessage will be something about "Error: Item contents extend beyond block")the problematic table spans over 3 files (18026 
18026.1 and 18026.2).i made pg_filedump _FILE_ > ~/_FILE_.dumpit went finegrep -i error ~/*.dump also didn't show anything.the dumps are quite large:[EMAIL PROTECTED]:~$ ls -l *.dump-rw-r--r--  1 pgdba pgdba 154631630 2006-04-14 18:03 
18026.1.dump-rw-r--r--  1 pgdba pgdba 108808017 2006-04-14 18:03 18026.2.dump-rw-r--r--  1 pgdba pgdba 161625849 2006-04-14 18:01 18026.dumpwhat else can i look in it for?best regardshubert

[GENERAL] Kernel 2.4->2.6 upgrade results in PANIC: could not locate a valid checkpoint record

[Achilleus Mantzios]

Hi, we have happily been running postgresql 7.4.x (currently 7.4.12) on a 
debian linux with kernel 2.4.x for some years without any issues.
Yesterday after switching to 2.6.16 we experienced the following problem
upon pgsql startup:

LOG:  could not open file "/var/lib/pgsql/data/pg_xlog/00690091" 
(log file 1 05, segment 145): No such file or directory
LOG:  invalid primary checkpoint record
LOG:  could not open file "/var/lib/pgsql/data/pg_xlog/00690091" 
(log file 1 05, segment 145): No such file or directory
LOG:  invalid secondary checkpoint record
PANIC:  could not locate a valid checkpoint record
LOG:  startup process (PID 414) was terminated by signal 6
LOG:  aborting startup due to startup process failure

After reverting back to kernel 2.4, postgresql started up hapilly again.

Any ideas?
-- 
-Achilleus


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

Re: [GENERAL] Kernel 2.4->2.6 upgrade results in PANIC: could not locate a

[Achilleus Mantzios]

Problem solved.
The sysadmins yesterday forgot to mention to me
that they didnt mount correctly all the file systems
(including the one of pg_xlog).

Sorry for the noise :-)

O Achilleus Mantzios έγραψε στις Apr 14, 2006 :

> 
> Hi, we have happily been running postgresql 7.4.x (currently 7.4.12) on a 
> debian linux with kernel 2.4.x for some years without any issues.
> Yesterday after switching to 2.6.16 we experienced the following problem
> upon pgsql startup:
> 
> LOG:  could not open file "/var/lib/pgsql/data/pg_xlog/00690091" 
> (log file 1 05, segment 145): No such file or directory
> LOG:  invalid primary checkpoint record
> LOG:  could not open file "/var/lib/pgsql/data/pg_xlog/00690091" 
> (log file 1 05, segment 145): No such file or directory
> LOG:  invalid secondary checkpoint record
> PANIC:  could not locate a valid checkpoint record
> LOG:  startup process (PID 414) was terminated by signal 6
> LOG:  aborting startup due to startup process failure
> 
> After reverting back to kernel 2.4, postgresql started up hapilly again.
> 
> Any ideas?
> 

-- 
-Achilleus


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

[GENERAL] what the problem with this query

[venu gopal]

Hi all,   When i try to run the following query it gives the following error what was wrong in the query.Query:SELECT o.orgunitname AS ouname, e.entrynumber AS value_field, centroid(c.the_geom) AS the_geom, c.ogc_fid AS ogc_fid FROM (ctrphc AS c INNER JOIN orgunit o ON c.dhs_ouname = o.orgunitname) INNER JOIN (SELECT orgunitid, entrynumber FROM routinedataou4 WHERE dataelementid=1195 AND dataperiodid = 86) AS e ON o.orgunitid = e.orgunitid) AS new_table USING UNIQUE ogc_fid USING SRID=-1Error::ERROR:  syntax error at or near ")" at character 333Regards,venu

Re: [GENERAL] [ADMIN] what the problem with this query

[Guido Barosio]

o.orgunitid = e.orgunitid)

That ")" is not valid Venu. (Nothing is being enclosed)

Regards,
Guido


On 4/14/06, venu gopal <[EMAIL PROTECTED]> wrote:
>
> Hi all,
>When i try to run the following query it gives the following error what
> was wrong in the query.
>
> Query:SELECT o.orgunitname AS ouname, e.entrynumber AS value_field,
> centroid(c.the_geom) AS the_geom, c.ogc_fid AS ogc_fid FROM (ctrphc AS c
> INNER JOIN orgunit o ON c.dhs_ouname = o.orgunitname) INNER JOIN (SELECT
> orgunitid, entrynumber FROM routinedataou4 WHERE dataelementid=1195 AND
> dataperiodid = 86) AS e ON o.orgunitid = e.orgunitid) AS new_table USING
> UNIQUE ogc_fid USING SRID=-1
>
> Error::ERROR:  syntax error at or near ")" at character 333
>
> Regards,
> venu
>
>


--
Guido Barosio
---
http://www.globant.com
[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] what the problem with this query

[Michael Glaesemann]

On Apr 14, 2006, at 20:09 , venu gopal wrote:

Query:SELECT o.orgunitname AS ouname, e.entrynumber AS value_field,  
centroid(c.the_geom) AS the_geom, c.ogc_fid AS ogc_fid FROM (ctrphc  
AS c INNER JOIN orgunit o ON c.dhs_ouname = o.orgunitname) INNER  
JOIN (SELECT orgunitid, entrynumber FROM routinedataou4 WHERE  
dataelementid=1195 AND dataperiodid = 86) AS e ON o.orgunitid =  
e.orgunitid) AS new_table USING UNIQUE ogc_fid USING SRID=-1


Error::ERROR:  syntax error at or near ")" at character 333


[please don't post HTML]

I'm not familiar with the syntax you're using, but one problem is  
that your parentheses are not balanced -- there's an extra closing  
parenthesis. That's probably what the error is complaining about. You  
might want to try to use whitespace to see the structure of your  
query better, e.g.,


SELECT o.orgunitname AS ouname
, e.entrynumber AS value_field
, centroid(c.the_geom) AS the_geom
, c.ogc_fid AS ogc_fid
FROM (
ctrphc AS c
INNER JOIN orgunit o ON c.dhs_ouname = o.orgunitname)
INNER JOIN (
SELECT orgunitid
, entrynumber
FROM routinedataou4
WHERE dataelementid=1195
AND dataperiodid = 86
) AS e ON o.orgunitid = e.orgunitid) AS new_table USING UNIQUE  
ogc_fid USING SRID=-1


Hope this helps a bit.

Michael Glaesemann
grzm myrealbox com




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

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

[GENERAL] Curious UDP packets

[Kari Pahula]

Hello.  There are messages like this in shorewall's logs.  x.x.x.x is
my site's IP address, both as the source and the destination.  I've
been told that they're caused by postgresql.  Having these messages
filtered out doesn't seem to affect postgresql in any way.  Does
anyone know why postgresql would want to talk to itself in this way?
I'm using postgresql 7.4.7.  My guess would be that this is some sort
of a heartbeat.

omega kernel: Shorewall:all2all:REJECT:IN= OUT=lo
SRC=x.x.x.x DST=x.x.x.x LEN=1016 TOS=0x00 PREC=0x00
TTL=64 ID=21629 DF PROTO=UDP SPT=32769 DPT=32769 LEN=996

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

Re: [GENERAL] Curious UDP packets

[Magnus Hagander]

> Hello.  There are messages like this in shorewall's logs.  
> x.x.x.x is my site's IP address, both as the source and the 
> destination.  I've been told that they're caused by 
> postgresql.  Having these messages filtered out doesn't seem 
> to affect postgresql in any way.  Does anyone know why 
> postgresql would want to talk to itself in this way?
> I'm using postgresql 7.4.7.  My guess would be that this is 
> some sort of a heartbeat.
> 
> omega kernel: Shorewall:all2all:REJECT:IN= OUT=lo SRC=x.x.x.x 
> DST=x.x.x.x LEN=1016 TOS=0x00 PREC=0x00
> TTL=64 ID=21629 DF PROTO=UDP SPT=32769 DPT=32769 LEN=996

The PostgreSQL stats collector uses UDP over a random loopback port. It
should normally use localhost, though and not a "real" IP. 

To see if that's it, turn of the stats collector
(start_stats_collector=off), restart postgresql (restart needed ,not
enough to just HUP) and see if they go away.

Another way might be to see if the pg_stat_activity view is empty
(select * from pg_stat_activity). With the stats collector running it
should never be empty - it should contain at least your own process -
but if the stats packets don't get through that's what would happen.

//Magnus

---(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] what the problem with this query

[William Penberthy]

IT appears you have one too many
closing parans…

 

SELECT o.orgunitname AS ouname,
e.entrynumber AS value_field, centroid

(

c.the_geom

)   AS
the_geom, c.ogc_fid AS ogc_fid FROM 

(

ctrphc
AS c INNER JOIN orgunit o ON c.dhs_ouname = o.orgunitname

)   INNER
JOIN 

(   SELECT
orgunitid, entrynumber FROM routinedataou4 WHERE dataelementid=1195 AND
dataperiodid = 86

)   AS
e ON o.orgunitid = e.orgunitid

)   AS
new_table USING UNIQUE ogc_fid USING SRID=-1



 









From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On
Behalf Of venu gopal
Sent: Friday, April 14, 2006 5:09
AM
To: pgsql-admin@postgresql.org;
pgsql-general@postgresql.org
Subject: [GENERAL] what the
problem with this query



 



Hi all,
   When i try to run the following query it gives the following error
what was wrong in the query.

Query:SELECT o.orgunitname AS ouname,
e.entrynumber AS value_field, centroid(c.the_geom) AS the_geom, c.ogc_fid AS
ogc_fid FROM (ctrphc AS c INNER JOIN orgunit o ON c.dhs_ouname = o.orgunitname)
INNER JOIN (SELECT orgunitid, entrynumber FROM routinedataou4 WHERE
dataelementid=1195 AND dataperiodid = 86) AS e ON o.orgunitid = e.orgunitid) AS
new_table USING UNIQUE ogc_fid USING SRID=-1

Error::ERROR:  syntax error
at or near ")" at character 333

Regards,
venu

[GENERAL] ilike and utf-8

[Raphael Bauduin]

Hi,

Does the ilike operator work fine with cyrillic text put in a UTF-8
encoded database?
I've had remarks of a user (of http://myowndb.com, a web database)
with text in cyrillic that his searches are not case insensitive,
although I use the ilke operator in the code. And it works perfectly
for my data (that are not in cyrillic).

Thanks

Raph

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

   http://archives.postgresql.org

Re: [GENERAL] export from views

[John DeSoi]

On Apr 13, 2006, at 10:58 AM, Tomas Lanczos wrote:

I am quite a newbie in the database business, so sorry for stupid  
questions.
Is there any convenient and simple method/tool to export data from  
views to

file, something like COPY TO (file) from a table?



One option is to create a temp table and then use COPY on the temp  
table. Something like


CREATE TEMP TABLE temp_table AS SELECT * from some_view;


It would be really nice if copy worked on views directly.




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


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

Re: [GENERAL] ilike and utf-8

[Martijn van Oosterhout]

On Fri, Apr 14, 2006 at 03:16:01PM +0200, Raphael Bauduin wrote:
> Hi,
> 
> Does the ilike operator work fine with cyrillic text put in a UTF-8
> encoded database?
> I've had remarks of a user (of http://myowndb.com, a web database)
> with text in cyrillic that his searches are not case insensitive,
> although I use the ilke operator in the code. And it works perfectly
> for my data (that are not in cyrillic).

UTF-8 support for case-comparison is operatnig system dependant. What
systems are we comparing here?

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


signature.asc
Description: Digital signature

Re: [GENERAL] ilike and utf-8

[Tomi NA]

On 4/14/06, Martijn van Oosterhout  wrote:
On Fri, Apr 14, 2006 at 03:16:01PM +0200, Raphael Bauduin wrote:> Hi,>> Does the ilike operator work fine with cyrillic text put in a UTF-8> encoded database?> I've had remarks of a user (of 
http://myowndb.com, a web database)> with text in cyrillic that his searches are not case insensitive,> although I use the ilke operator in the code. And it works perfectly
> for my data (that are not in cyrillic).UTF-8 support for case-comparison is operatnig system dependant. Whatsystems are we comparing here?I'd like to know the same thing. I'm using GNU/linux and ISO-8859-2 (when UTF-8 isn't an option).
Tomislav

Re: [GENERAL] corrupted item pointer:???

[Tom Lane]

"hubert depesz lubaczewski" <[EMAIL PROTECTED]> writes:
> i made pg_filedump _FILE_ > ~/_FILE_.dump
> it went fine
> grep -i error ~/*.dump also didn't show anything.

Oh, that's interesting.  Looking more closely, the test in
PageRepairFragmentation()

if (itemidptr->itemoff < (int) pd_upper ||
itemidptr->itemoff >= (int) pd_special)
ereport(ERROR,
(errcode(ERRCODE_DATA_CORRUPTED),
 errmsg("corrupted item pointer: %u",
itemidptr->itemoff)));

is slightly tighter than what pg_filedump does:

  // Make sure the item can physically fit on this block before
  // formatting
  if ((itemOffset + itemSize > blockSize) ||
  (itemOffset + itemSize > bytesToFormat))
printf ("  Error: Item contents extend beyond block.\n"
" BlockSize<%d> Bytes Read<%d> Item Start<%d>.\n",
blockSize, bytesToFormat, itemOffset + itemSize);

I'm guessing that the lack of a check for itemOffset < pd_upper is why
pg_filedump is failing to notice anything wrong.  Do you want to add one
and try again?

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] what the problem with this query

[Tom Lane]

Michael Glaesemann <[EMAIL PROTECTED]> writes:
> I'm not familiar with the syntax you're using, but one problem is  
> that your parentheses are not balanced -- there's an extra closing  
> parenthesis. That's probably what the error is complaining about. You  
> might want to try to use whitespace to see the structure of your  
> query better, e.g.,

Also, consider using a newer Postgres release.  8.0 and up provide
a cursor pointer so that you don't have to count characters:

regression=# SELECT o.orgunitname AS ouname, e.entrynumber AS value_field, 
centroid(c.the_geom) AS the_geom, c.ogc_fid AS ogc_fid FROM (ctrphc AS c INNER 
JOIN orgunit o ON c.dhs_ouname = o.orgunitname) INNER JOIN (SELECT orgunitid, 
entrynumber FROM routinedataou4 WHERE dataelementid=1195 AND dataperiodid = 86) 
AS e ON o.orgunitid = e.orgunitid) AS new_table USING UNIQUE ogc_fid USING 
SRID=-1;
ERROR:  syntax error at or near ")" at character 333
LINE 1: ...taperiodid = 86) AS e ON o.orgunitid = e.orgunitid) AS new_t...
 ^
regression=#

regards, tom lane

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

Re: [GENERAL] ilike and utf-8

[Raphael Bauduin]

It's a Debian GNU/Linux, with a self-compiled 8.1.3 postgresql.

Raph

On 4/14/06, Martijn van Oosterhout  wrote:
> On Fri, Apr 14, 2006 at 03:16:01PM +0200, Raphael Bauduin wrote:
> > Hi,
> >
> > Does the ilike operator work fine with cyrillic text put in a UTF-8
> > encoded database?
> > I've had remarks of a user (of http://myowndb.com, a web database)
> > with text in cyrillic that his searches are not case insensitive,
> > although I use the ilke operator in the code. And it works perfectly
> > for my data (that are not in cyrillic).
>
> UTF-8 support for case-comparison is operatnig system dependant. What
> systems are we comparing here?
>
> Have a nice day,
> --
> Martijn van Oosterhout  http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.
>
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.1 (GNU/Linux)
>
> iD8DBQFEP6gDIB7bNG8LQkwRAgyUAJsGusLIxrdkiaDg11727770bquYCgCfWgCZ
> /SYTVp84hAf/jx8pO+js8pY=
> =afee
> -END PGP SIGNATURE-
>
>
>

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

Re: [GENERAL] ilike and utf-8

[Tom Lane]

"Raphael Bauduin" <[EMAIL PROTECTED]> writes:
> Does the ilike operator work fine with cyrillic text put in a UTF-8
> encoded database?

If you've initdb'd in an appropriate locale (probably named something
like ru_RU.utf8) then it should work.  I wouldn't expect a random
non-Russian locale to necessarily know about Cyrillic case conversions,
however.

Martijn's nearby comment about OS dependency really boils down to the
fact that different OSes may have different definitions for similarly
named locales.  We need to know what locale you're using (try "SHOW
LC_CTYPE") as well as the OS.

regards, tom lane

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

Re: [GENERAL] ilike and utf-8

[Raphael Bauduin]

On 4/14/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Raphael Bauduin" <[EMAIL PROTECTED]> writes:
> > Does the ilike operator work fine with cyrillic text put in a UTF-8
> > encoded database?
>
> If you've initdb'd in an appropriate locale (probably named something
> like ru_RU.utf8) then it should work.  I wouldn't expect a random
> non-Russian locale to necessarily know about Cyrillic case conversions,
> however.

The problem is that the system is serving, at the same time, content
for different locales, so I can't set it at the environment level.
Maybe I should set a user setting so a user can choose which locale to
use.

Thanks for the help!

Raph

>
> Martijn's nearby comment about OS dependency really boils down to the
> fact that different OSes may have different definitions for similarly
> named locales.  We need to know what locale you're using (try "SHOW
> LC_CTYPE") as well as the OS.
>
> regards, tom lane
>

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

[GENERAL] Database owner can't select

[Kevin Galligan]

I'm a newbie.  I'll admit it.  I'm having some trouble. 
I have 8.1 installed on ubuntu.  I have a simple database set
up.  I have a new user set up and I can log in as that user. 
I've set the owner of the simple database to that user with 'ALTER
DATABASE [db] OWNER TO [user]', and this user shows up as the owner
with '\l+'.  Yet, I can't do anything with the database as that
user.  Can't even select.  I added select privaledges to a
table for that user, and then select worked.

However, from what I can gather from reading this...

http://www.postgresql.org/docs/8.1/interactive/privileges.html

the owner should be able to do this kind of stuff without explicitly
adding privs.  Any idea?  I assume the ORIGINAL owner has
full privs, but a set owner does not.  Is this correct?

If so, is there any way to set privs for a new owner without explicitly listing each table?

Thanks in advance

Re: [GENERAL] ilike and utf-8

[Balazs . Klein]

I have a similar problem that I raised here (see link) but I don't have
the solution yet.
I received several ideas, but so far not a solution that would actually
work for me.
You may want to give the function that you find in this thread a try.
It didn't work for me, but maybe it will for you - let me know please
if it does, I am still looking for an answer.

http://groups.google.com/group/pgsql.general/browse_thread/thread/20aed89ab0e19e3d/4771fb1be397afea#4771fb1be397afea
 
Regards,

Balázs


---(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] Database owner can't select

[Tom Lane]

"Kevin Galligan" <[EMAIL PROTECTED]> writes:
> set up and I can log in as that user.  I've set the owner of the simple
> database to that user with 'ALTER DATABASE [db] OWNER TO [user]', and this
> user shows up as the owner with '\l+'.  Yet, I can't do anything with the
> database as that user.

Ownership of a database doesn't imply much except the right to drop the
DB as a whole.  In particular, it has essentially nothing to do with
privileges on pre-existing objects within the database.

> However, from what I can gather from reading this...
> http://www.postgresql.org/docs/8.1/interactive/privileges.html
> the owner should be able to do this kind of stuff without explicitly adding
> privs.

Where do you get that from?  It's certainly not the intended meaning.

Perhaps what you want to do is also ALTER OWNER on each
table/function/etc within the database.

regards, tom lane

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

Re: [GENERAL] Database owner can't select

[Kevin Galligan]

I think I was confusing owner of database and owner of each
object.  I assumed if you owned the database you could do what you
wanted with the tables in the database.

Assumptions are bad.

Thanks for the response.On 4/14/06, Tom Lane <[EMAIL PROTECTED]> wrote:
"Kevin Galligan" <[EMAIL PROTECTED]> writes:> set up and I can log in as that user.  I've set the owner of the simple> database to that user with 'ALTER DATABASE [db] OWNER TO [user]', and this
> user shows up as the owner with '\l+'.  Yet, I can't do anything with the> database as that user.Ownership of a database doesn't imply much except the right to drop theDB as a whole.  In particular, it has essentially nothing to do with
privileges on pre-existing objects within the database.> However, from what I can gather from reading this...> http://www.postgresql.org/docs/8.1/interactive/privileges.html
> the owner should be able to do this kind of stuff without explicitly adding> privs.Where do you get that from?  It's certainly not the intended meaning.Perhaps what you want to do is also ALTER OWNER on each
table/function/etc within the database.regards,
tom lane

Re: [GENERAL] corrupted item pointer:???

[hubert depesz lubaczewski]

On 4/14/06, Tom Lane <[EMAIL PROTECTED]> wrote:
I'm guessing that the lack of a check for itemOffset < pd_upper is whypg_filedump is failing to notice anything wrong.  Do you want to add oneand try again?sure. but could you please tell me what to change? c is not my favourite language and i'd like not to damage something else while trying to change it myself.
hubert

[GENERAL] Querying a BYTEA datatype

[Shoaib Mir]

Hi All,Need an urgent help on the following:How can  in PostgreSQL we handle quries on the content of bytea data type?Like if we want to do some search on the basis of bytea content, is there a way?
/Shoaib

Re: [GENERAL] Querying a BYTEA datatype

[Merlin Moncure]

On 4/14/06, Shoaib Mir <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> Need an urgent help on the following:
>
> How can  in PostgreSQL we handle quries on the content of bytea data type?
>
> Like if we want to do some search on the basis of bytea content, is there a
> way?
>

you have two options that i know of:
1. convert to text and do text processing
2. make a c function that processes binary and link it to the server. 
this will probably give you the best performance

---(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] Fetch in select statement

[Sam Thukral]

Hello,

 

Is it possible to fetch from a cursor and join the results
with the out parameters from a function?  Is it possible to do this in a
prepared statement in ADO using ODBC?

 

Sam Thukral

 

 

[GENERAL] Replacing MD5 hash in pg_auth...

[Peter van der Maas]

Hello,

Is it correct to assume that if a user has write permission to
\data\global\pg_auth on a Win32 machine, the superuser's MD5 hash can be
replaced with one of a known origin in order to own the DB?

I do practice as noted in the Win FAQ, just want to make sure I am not
missing something:

"If you are running PostgreSQL on a multi-user system, you should remove
the permissions from all non-administrative users from the PostgreSQL
directories. No user ever needs permissions on the PostgreSQL files -
all communication is done through the libpq connection. Direct access to
data files can lead to information disclosure or system instability!"


Thanks in advance for any input,
Peter van der Maas

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

Re: [GENERAL] Replacing MD5 hash in pg_auth...

[Steve Atkins]

On Apr 14, 2006, at 6:47 PM, Peter van der Maas wrote:


Hello,

Is it correct to assume that if a user has write permission to
\data\global\pg_auth on a Win32 machine, the superuser's MD5 hash  
can be

replaced with one of a known origin in order to own the DB?


Probably. It'd be much easier to edit pg_hba.conf, though.

If anyone other than postgres has read permission, let alone write
permission, to /usr/local/pgsql/data or equivalent, or anywhere  
underneath

there, you're on very shaky security grounds.



I do practice as noted in the Win FAQ, just want to make sure I am not
missing something:

"If you are running PostgreSQL on a multi-user system, you should  
remove

the permissions from all non-administrative users from the PostgreSQL
directories. No user ever needs permissions on the PostgreSQL files -
all communication is done through the libpq connection. Direct  
access to

data files can lead to information disclosure or system instability!"


As in "We 0wn3rz y0uz database".

Cheers,
  Steve


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

Re: [GENERAL] Replacing MD5 hash in pg_auth...

[Tom Lane]

Steve Atkins <[EMAIL PROTECTED]> writes:
> On Apr 14, 2006, at 6:47 PM, Peter van der Maas wrote:
>> Is it correct to assume that if a user has write permission to
>> \data\global\pg_auth on a Win32 machine, the superuser's MD5 hash  
>> can be replaced with one of a known origin in order to own the DB?

> Probably. It'd be much easier to edit pg_hba.conf, though.

Actually, if you have write permission on the $PGDATA tree, you
*already* own the DB for every practical purpose.  Focusing on passwords
is silly.

regards, tom lane

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

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

[GENERAL] 21 bit number for sequence

[Shoaib Mir]

Right now Sequence by default uses bigint which supports upto 19bit numbers but I want to use a 21bit that can be supported by NUMERIC datatype. Is there any way I can sepcify while creating a sequence what datatype to use or if I can specify to create sequence with numeric datatype?
/Shoaib

Re: [GENERAL] Querying a BYTEA datatype

[Shoaib Mir]

Thanks MerlinOn 4/15/06, Merlin Moncure <[EMAIL PROTECTED]> wrote:
On 4/14/06, Shoaib Mir <[EMAIL PROTECTED]> wrote:> Hi All,>> Need an urgent help on the following:>> How can  in PostgreSQL we handle quries on the content of bytea data type?
>> Like if we want to do some search on the basis of bytea content, is there a> way?>you have two options that i know of:1. convert to text and do text processing2. make a c function that processes binary and link it to the server.
this will probably give you the best performance