Re: [GENERAL] Regex with patterns in table field

[chris smith]

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/

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

Re: [GENERAL] Regex with patterns in table field

[pgdb]

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.
 
Regards
pgdb
 
A. Kretschmer writes: 

> am 13.04.2006, um 12:47:38 + mailte pgdb
folgendes: 
>> 
>> 


>> 

>> 

>> 
Hi,
>> 
 
>> 
like to know how, if possible, for patterns 
>> as attribute in a table to be used in a regex
search with a 
>> fixed string. The query should therefore return
the rows that 
>> have matching patterns with the single
text.
> 
> It's hard to read such text, please use plain ASCII
insteed HTML. 
> 
> Perhaps you are looking for something like this:

> 
> test=# select * from search; 
> text 
>  
> this is a test 
> (1 row) 
> 
> test=# select * from pattern; 
> text 
> - 
> ^this.* 
> ^foo 
> (2 rows) 
> 
> test=# select pattern.text, search.text from pattern,
search where search.text ~ pattern.text; 
> text | text 
> -+ 
> ^this.* | this is a test 
> (1 row) 
> 
> 
> 
> HTH, Andreas 
> -- 
> Andreas Kretschmer (Kontakt: siehe Header) 
> Heynitz: 035242/47215, D1: 0160/7141639 
> GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net 
> === Schollglas Unternehmensgruppe === 
> 
> ---(end of
broadcast)--- 
> TIP 3: Have you checked our extensive FAQ? 
> 
> http://www.postgresql.org/docs/faq 



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

Re: [GENERAL] Off-Topic: DBMS Market Research

[Ron Mayer]

Renato Cramer wrote:

Can someone where I can found DBMS Market Researches?
What institutes publish reliable researchs? Gartner, IDC?


Note it's hard for any company to provide reliable research
that spans both open-source and non-open-source products.

For example, one company I'm familiar with sells a
specialized storage appliance that contains postgresql
inside, but doesn't tell anyone it's there.   Neither
Gartner nor IDC nor any other market research group
would count it correctly.

The best analogy I've heard is market research of
breathable gases.   Any market research company
would happily conclude that that Tobacco
Smoke is a far more desirable breathable substance
than air.   Just look at the revenue numbers:
   Cigarettes - $48.7 billion in 1997
   Cigars - $ 0.9 billion in 1997
   Fresh Air  - $ 0.0 billion in 1997
So the obvious conclusion is that if you're a business
the revenue figures obviously show that best
practices in the industry is to use smoke.

Absurd, yes; but it seems that's how most corporations
pick their databases and operating systems.


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Where is client/server compatibility documented?

[Scott Marlowe]

On Thu, 2006-04-13 at 15:06, Wes wrote:
> Where can I find documentation on what client library version interoperate
> with which server versions?  I've been unable to find anything in the manual
> or archives.  The only thing I've found is some information in the archives
> on certain psql versions.  Is there a compatibility matrix somewhere for the
> client libraries?
> 
> Specifically, I have a C application (ecpg/libpq), as well as perl DBI,
> built against PostgreSQL 7.4.1.  Am I safe in upgrading the server to 8.1.3
> before the application can be rebuilt?  I've seen no problems on the test
> server, but...
> 
> There's no system catalog monkey business - just plain old SQL.

The latest libs that come with 8.1 can talk to any client that talks the
V2 or V3 protocol, which takes you back to 7.0 or 6.5 or something like
that.  Maybe even earlier.

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

[GENERAL] Where is client/server compatibility documented?

[Wes]

Where can I find documentation on what client library version interoperate
with which server versions?  I've been unable to find anything in the manual
or archives.  The only thing I've found is some information in the archives
on certain psql versions.  Is there a compatibility matrix somewhere for the
client libraries?

Specifically, I have a C application (ecpg/libpq), as well as perl DBI,
built against PostgreSQL 7.4.1.  Am I safe in upgrading the server to 8.1.3
before the application can be rebuilt?  I've seen no problems on the test
server, but...

There's no system catalog monkey business - just plain old SQL.

Wes



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

Re: [GENERAL] PostgreSQL's XML support comparison against other RDBMSes

[Balazs . Klein]

Maybe add to it the
  insert/update data in tables based on an XML (a'la MS
updategram)

Balázs


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

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

[Tom Lane]

"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 the
message 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_filedump
reports errors for.

regards, tom lane

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

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, Richard Huxton  wrote:>> Hmm - I believe that means a data/index block was corrupted.
> indices were recreated (reindex table), so i think this is data related> problem.AFAICS, the only non-index-related occurrence of that error messageis in PageRepairFragmentation, which is invoked by VACUUM.  I'd say
it indicates a real problem and you shouldn't ignore it.  You mighttry using pg_filedump or some such to examine the table and see ifthere's anything obvious about what happened to the corrupted page.
i'm not familiar with this utility.i can of course find it using google, but how do i check what is wrong?i am even willing to upload the dump file, but with 4 milion records in table, it is going to be rather large...
pg_relation_size says that the table is about 3g i size depesz

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

[hubert depesz lubaczewski]

On 4/13/06, Richard Huxton  wrote:
All looks fine. Can you isolate the row(s) in question that seem to bethe problem? Then we can have a look at the system columns.http://www.postgresql.org/docs/8.1/static/ddl-system-columns.html
i ran the test to find it. as soon as i will get it (probably tomorrow) i will mail it to the list.hubert

Re: [GENERAL] the integer type

[Zahir Lalani]

Tony

Just a thought. I have spent the last couple of weeks googling for admin
tools for Postgres - and never came across yours!

Zahir

> >
> >   
> PG Lightning Admin was created with input from several MS SQL server 
> DBAs who just absolutely detested pgAdmin III.
> We where converting a large MS SQL application to Postgresql and they 
> complained to me every day about pgAdmin, so I started writing PG 
> Lightning admin for them and it took
> off from there.  If you have never used a RDBMS before, 
> pgAdmin III is 
> acceptable, but once you have used tools like the MS Query 
> Analyser or 
> even commercial admin tools for Firebird (IB expert for example) it 
> (pgAdmin) really starts to lose it's luster.
> 
> I admit my views on this subject are biased though :-)


__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__

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

Re: [GENERAL] the integer type

[Tony Caduto]

Zahir Lalani wrote:

This is exactly how I felt - It made me wonder whether I should go back
to MS SQL - the whole UI experience is much better.

But now that I have seen lightning - hopefully it will get easier. Also,
I was trying to connect this to ColdFusion/Dreamweaver.
Since the field was defined as int4, Coldfusion raised an error in its
validation as it had no clue what int4 was?

Zahir

  
PG Lightning Admin was created with input from several MS SQL server 
DBAs who just absolutely detested pgAdmin III.
We where converting a large MS SQL application to Postgresql and they 
complained to me every day about pgAdmin, so I started writing PG 
Lightning admin for them and it took
off from there.  If you have never used a RDBMS before, pgAdmin III is 
acceptable, but once you have used tools like the MS Query Analyser or 
even commercial admin tools for Firebird (IB expert for example) it 
(pgAdmin) really starts to lose it's luster.


I admit my views on this subject are biased though :-)

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration 



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

Re: [GENERAL] the integer type

[Zahir Lalani]

This is exactly how I felt - It made me wonder whether I should go back
to MS SQL - the whole UI experience is much better.

But now that I have seen lightning - hopefully it will get easier. Also,
I was trying to connect this to ColdFusion/Dreamweaver.
Since the field was defined as int4, Coldfusion raised an error in its
validation as it had no clue what int4 was?

Zahir

> >   
> Actually if you try and type in integer in the combobox, it 
> accepts the 
> text, but the ok button wont enable until you pick one from the list.
> Yes, you could make a "integer" domain but why would you do that when 
> Postgresql actually will accept a type called integer?
> 
> I just tried it on version 1.4.2 and it will not accept 
> integer in the 
> combobox for a new field in the table editor.
> 
> The whole point here is people coming from systems such as MS 
> SQL server 
> for example have no clue what int4 or float8 is.
> 
> -- 
> Tony Caduto
> AM Software Design
> http://www.amsoftwaredesign.com
> Home of PG Lightning Admin for Postgresql
> Your best bet for Postgresql Administration 


__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__

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

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

Re: [GENERAL] the integer type

[Tony Caduto]

Dave Page wrote:


Which allows you to use any custom datatype or domain that you like.

  
double precision == float8

(http://www.postgresql.org/docs/8.1/interactive/datatype.html) which
works just fine.

Regards, Dave.

  
Actually if you try and type in integer in the combobox, it accepts the 
text, but the ok button wont enable until you pick one from the list.
Yes, you could make a "integer" domain but why would you do that when 
Postgresql actually will accept a type called integer?


I just tried it on version 1.4.2 and it will not accept integer in the 
combobox for a new field in the table editor.


The whole point here is people coming from systems such as MS SQL server 
for example have no clue what int4 or float8 is.


--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration 



---(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] the integer type

[Dave Page]

 

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of Tony Caduto
> Sent: 13 April 2006 16:19
> To: chris smith
> Cc: Zahir Lalani; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] the integer type
>  
> It's not a error, pgAdmin III simply does not display the 
> word integer in it's drop down comboboxes, it uses all the 
> internal representation of types not the SQL standard aliases.

Which allows you to use any custom datatype or domain that you like.

> PG Lightning Admin on the other hand does use the SQL 
> standard names in the comboboxes which make things a little 
> easier for those coming from other databases and that's why 
> PG Lightning Admin is the best choice for all users running a 
> win32 desktop or coming from another database system such as 
> MS SQL server.  Just because it's free (pgAdminIII) does not 
> mean it should be your only or best choice.
> 
> pgAdmin III also won't accept double precision in it's type 
> selection combobox.

double precision == float8
(http://www.postgresql.org/docs/8.1/interactive/datatype.html) which
works just fine.

Regards, Dave.

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

Re: [GENERAL] browser shows garbage instead of UTF-8 characters

[Leif B. Kristensen]

On Thursday 13 April 2006 13:12, Sadm sadm wrote:
>Hello!
>I have a PostgreSQL database with UTF-8 encoding. It stores russian
> characters. When I fetch a record from this database and output it to
> the web browser as html page, it looks like a garbage instead of
> normal russian characters. I'm using html with  http-equiv="Content-Type" content="text/html; charset=utf-8">.
>
>Please help me to find a mistake.
>Thanks in advance.

If you're using PHP, you should issue the command:

pg_set_client_encoding($db, $encoding);

I don't know which value of $encoding you should use with Cyrillic 
letters, but I'm using LATIN1 which works fine with Norwegian.
-- 
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] the integer type

[Tony Caduto]

chris smith wrote:


What error do you get? I'm sure pgAdmin will show something.

Try it from console:

psql.exe dbname
create table t1(a int);

(Always CC the list, someone else might be able to help you if I don't
know the answer).
  
It's not a error, pgAdmin III simply does not display the word integer 
in it's drop down comboboxes, it uses all the internal representation of 
types not the SQL standard aliases.


PG Lightning Admin on the other hand does use the SQL standard names in 
the comboboxes which make things a little easier for those coming from 
other databases and that's why PG
Lightning Admin is the best choice for all users running a win32 desktop 
or coming from another database system such as MS SQL server.  Just 
because it's free (pgAdminIII) does not mean it should be your only or 
best choice.


pgAdmin III also won't accept double precision in it's type selection 
combobox.


Later,

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration 



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] export from views

[Michael Glaesemann]

On Apr 13, 2006, at 23:58 , Tomas Lanczos wrote:

Is there any convenient and simple method/tool to export data from  
views to

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


In psql, take a look at \o (and \? while you're at it).

Hope this helps.

Michael Glaesemann
grzm myrealbox com




---(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] export from views

[Tomas Lanczos]

Hello everybody,

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?

I am using WinXP and Postgresql 8.1.

Many thanks in advance

Tomas


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

Re: [GENERAL] posting request

[Peter L. Berghold]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

chris smith wrote:
>We don't bite :)
> 
> 

much. :-D



- --

Peter L. Berghold [EMAIL PROTECTED]
"Those who fail to learn from history are condemned to repeat it."
AIM: redcowdawgYahoo IM: blue_cowdawg  ICQ: 11455958
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.6 (GNU/Linux)
Comment: Using GnuPG with Red Hat - http://enigmail.mozdev.org

iD8DBQFEPl2yUM9/01RIhaARAqlTAJ9qwllCQi64cLDLmOuMR//t8zgdqgCgmV6c
4YLCvs6/KBB8oYFB5Z7+NRQ=
=ybcZ
-END PGP SIGNATURE-
begin:vcard
fn:Peter  L. Berghold
n:Berghold;Peter 
org:IBM;GSD
email;internet:[EMAIL PROTECTED]
title:Unix Specialist
x-mozilla-html:FALSE
url:http://www.berghold.net
version:2.1
end:vcard


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

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

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

[Tom Lane]

"hubert depesz lubaczewski" <[EMAIL PROTECTED]> writes:
> On 4/13/06, Richard Huxton  wrote:
>> Hmm - I believe that means a data/index block was corrupted.

> indices were recreated (reindex table), so i think this is data related
> problem.

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.

regards, tom lane

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

Re: [GENERAL] table as hashtable

[Richard Huxton]

Anton Andreev wrote:


 Hi,

Can I use a Postgre table as hashtable\\?


It's PostgreSQL or Postgres, BTW


when I do select where column='test', I want this to be done by hashtable,
not linear, not by b-tree. I do not need to sort, sum or else I just want
to get the value as fast as possible.


There are hash indexes. Read the docs about them though.
http://www.postgresql.org/docs/8.1/static/indexes-types.html

--
  Richard Huxton
  Archonet Ltd

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

  http://archives.postgresql.org

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

[Richard Huxton]

hubert depesz lubaczewski wrote:

On 4/13/06, Richard Huxton  wrote:

Hmm - I believe that means a data/index block was corrupted.


indices were recreated (reindex table), so i think this is data related
problem.

Have you seen any crashes, or hardware-related errors in your logs?

nope. uptime is over 40 days.

the machine is not used for anything else so i can't tell anything, but i
didn't see any problems with it.


What are your config settings, particularly the first three here:

   http://www.postgresql.org/docs/8.1/static/runtime-config-wal.html
fsync, wal_sync_method, full_page_writes



sure:
 fsync
---
 on



 wal_sync_method
-
 fdatasync



 full_page_writes
--
 on


All looks fine. Can you isolate the row(s) in question that seem to be 
the problem? Then we can have a look at the system columns.

http://www.postgresql.org/docs/8.1/static/ddl-system-columns.html

--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] the integer type

[chris smith]

On 4/14/06, Zahir Lalani <[EMAIL PROTECTED]> wrote:
> Hi Chris
>
> I am using pgAdmin III.
>
> Created a table, then started adding fields. This is where the problem
> hits - the drop down does not give you an INTEGER option, so you cannot
> shoose it.
> Also I tried creating a script in the query view and running it, but
> again, if I used INT or INTEGER it errors. Change it INT4 and its fine.


What error do you get? I'm sure pgAdmin will show something.

Try it from console:

psql.exe dbname
create table t1(a int);

(Always CC the list, someone else might be able to help you if I don't
know the answer).

> > -Original Message-
> > From: chris smith [mailto:[EMAIL PROTECTED]
> > Sent: 13 April 2006 15:17
> > To: Zahir Lalani
> > Cc: pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] the integer type
> >
> >
> > > I have only recently started to use Postgresql and have a
> > problem. I am
> > > using v8.1 on windows.
> > > I cannot seem to get the DB to accept either INT or INTEGER
> > as a type
> > > when using the admin tool. Only INT2 or INT4 work.
> > > I even downloaded Navicat trial and this has the same
> > issue. Is there a
> > > setup required to allow these
> > > standard types to be enabled?
> >
> > It's a built in type so you don't need to do anything.
> >
> > So something like this:
> >
> > create table t1(a int);
> >
> > fails?
> >
> > What message do you get?
> >
> > --
> > Postgresql & php tutorials
> > http://www.designmagick.com/
> >
>
>
> __
> This email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email
> __
>


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

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

Re: [GENERAL] Select first ten of each category?

[Brent Wood]

On Wed, 12 Apr 2006, Benjamin Smith wrote:

> I'm stumped on this one...
>
> I have a table defined thusly:
>
> create table items (
> id serial,
> category integer not null references category(id),
> name varchar not null,
> price real,
> unique(category, name));



I think this should work

select * from items
order by price desc
limit 10;


Cheers,

  Brent Wood

>
> It has a LARGE number of entries. I'd like to grab the 10 most expensive items
> from each category in a single query. How can this be done? Something like
>
> Select items.*
> FROM items
> where id IN (
>   select firstTen(id) FROM items
>   group by category
>   ORDER BY price DESC
>   )
> ORDER BY price desc;
>
> But I've not found any incantation to make this idea work...
>

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

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

Re: [GENERAL] [HACKERS] RH9 postgresql 8.0.7 rpm

[Gaetano Mendola]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Devrim GUNDUZ wrote:
> Hi,
> 
> On Wed, 2006-04-12 at 13:07 +0200, Gaetano Mendola wrote:
>> I was able to create it with:
>>
>> --nodeps --define 'buildrhel3 1' --define 'build9 1' 
> 
> I'll be hppy if you send the RPMs directly to me; so that I can upload
> them. A tarball would be fine.
> 

Dev I'll send you in private a link to my own web server so you can
download the files from there.

Regards
Gaetano Mendola

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

iD8DBQFEPQ+B7UpzwH2SGd4RAsbxAKCK2AIZK5+YzY5+BeGnoWY4+n3/3QCfUfiE
SpVt3/M0srlf6Vw3MhLGUXs=
=9pF+
-END PGP SIGNATURE-


---(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] table as hashtable

[Anton Andreev]

 Hi,

Can I use a Postgre table as hashtable\\?

when I do select where column='test', I want this to be done by hashtable,
not linear, not by b-tree. I do not need to sort, sum or else I just want
to get the value as fast as possible.

10x to anyone answering me

Cheers,
Anton



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

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

Re: [GENERAL] how to prevent generating same clipids

[Ian Harding]

This problem has been solved, by the use of sequences.  If you can't
use them as a default, you can use them instead of

MAX(clipid)

You would use

NEXTVAL(clipid_seq)

assuming you had first done

CREATE SEQUENCE clipid_seq;
SELECT SETVAL('clipid_seq', (select MAX(clipid) from whatevertable));

This will guarantee no duplicates.  It will not guarantee no missing values.

On 10 Apr 2006 22:43:16 -0700, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
> Hi
>Now we cannot change the field type. Because already our application
> is running and thousands of records are already entered. we are getting
> same no for clipid when 2 users are entering data at a time. so how to
> solve the problem
>
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>

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

Re: [GENERAL] the integer type

[chris smith]

> I have only recently started to use Postgresql and have a problem. I am
> using v8.1 on windows.
> I cannot seem to get the DB to accept either INT or INTEGER as a type
> when using the admin tool. Only INT2 or INT4 work.
> I even downloaded Navicat trial and this has the same issue. Is there a
> setup required to allow these
> standard types to be enabled?

It's a built in type so you don't need to do anything.

So something like this:

create table t1(a int);

fails?

What message do you get?

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

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

   http://archives.postgresql.org

Re: [GENERAL] browser shows garbage instead of UTF-8 characters

[Harald Armin Massa]

Sadm,what is the encoding setting of your database driver?What happens if you put out the retrieved Data to a console or a debug window?Have you analyzed the garbage, what is it? Escaped Characters? Hungarian? Arabian? Chinese??
How do you "fetch a record", how do you output it to "THE" "Web browser"? What are your HTML Headers? Which HTML / XHTML do you use? Harald

On 4/13/06, Sadm sadm <[EMAIL PROTECTED]> wrote:

Hello!I have a PostgreSQL database with UTF-8 encoding. It stores russian characters.When I fetch a record from this database and output it to the web browser as html page, itlooks like a garbage instead of normal russian characters. I'm using html with .
Please help me to find a mistake.Thanks in advance.--___Get your free email from 
http://mymail.bsdmail.com
---(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-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-PostgreSQL - supported by a community that does not put you on hold

Re: [GENERAL] posting request

[chris smith]

On 4/13/06, Anton Andreev <[EMAIL PROTECTED]> wrote:
>
>   I want to post some questions.

Go ahead and post them :) We don't bite :)

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

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Is a high tab_reloid worrying?

[John Sidney-Woollett]

Thanks for the reassurance. You're right the db has been around for a while.

Doea anyone know if OIDs for data and system (DDL) objects from the same 
number generator?


John

Christopher Browne wrote:

In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] (John 
Sidney-Woollett) transmitted:


I just added a new table to a slony relication set. The new table
seems to have a really high tab_reloid value of 94,198,669



I presume the database instance has been around for a while?  If so,
then I wouldn't worry too much.

I'm not certain comprehensively what would consume OIDs, but I expect
temp tables would, so if you have applications that generate them,
that would naturally lead to increases in OID values.

The only time you get *low* values is if you define tables immediately
after creating the database.


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

  http://archives.postgresql.org

[GENERAL]

[Sadm sadm]

Hello!
I have a PostgreSQL database with UTF-8 encoding and Resin application server.
When I fetch a record from database using jdbc connection and output it to the 
web browser as 
html page, it looks like a garbage instead of normal russian characters. html 
 is set.

Please help me to find a mistake.
Thanks in advance.




-- 
___
Get your free email from http://mymail.bsdmail.com

---(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] case insensitive match in unicode

[Balazs . Klein]

This perl function doesn't work for me.
I keep getting a
Cannot decode string with wide characters at
/usr/lib/perl5/5.8.7/i386-linux/Encode.pm line 166 error

Do you have any suggestion?
Thanks
Balázs


---(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] posting request

[Anton Andreev]

  I want to post some questions.



---(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] browser shows garbage instead of UTF-8 characters

[Sadm sadm]

Hello!
I have a PostgreSQL database with UTF-8 encoding. It stores russian characters. 
When I fetch a record from this database and output it to the web browser as 
html page, it 
looks like a garbage instead of normal russian characters. I'm using html with 
.

Please help me to find a mistake.
Thanks in advance.


-- 
___
Get your free email from http://mymail.bsdmail.com

---(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] the integer type

[Zahir Lalani]

Hello All

I have only recently started to use Postgresql and have a problem. I am
using v8.1 on windows.
I cannot seem to get the DB to accept either INT or INTEGER as a type
when using the admin tool. Only INT2 or INT4 work.
I even downloaded Navicat trial and this has the same issue. Is there a
setup required to allow these
standard types to be enabled?

 
Zahir Lalani 


 


__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Regex with patterns in table field

[A. Kretschmer]

am  13.04.2006, um 12:47:38 + mailte pgdb folgendes:
> 
> 
> 
> 
> Hi,
>  
> like to know how, if possible, for patterns
> as attribute in a table to be used in a regex search with a
> fixed string. The query should therefore return the rows that
> have matching patterns with the single text.

It's hard to read such text, please use plain ASCII insteed HTML.

Perhaps you are looking for something like this:

test=# select * from search;
  text

 this is a test
(1 row)

test=# select * from pattern;
  text
-
 ^this.*
 ^foo
(2 rows)

test=# select pattern.text, search.text from pattern, search where search.text 
~ pattern.text;
  text   |  text
-+
 ^this.* | this is a test
(1 row)



HTH, Andreas
-- 
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz:  035242/47215,  D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===Schollglas Unternehmensgruppe=== 

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

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

Re: [GENERAL] How to compile PostGreSQL on Windows 64 bits ?

[Dave Page]

 

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of 
> DANTE ALEXANDRA
> Sent: 13 April 2006 14:02
> To: pgsql-general@postgresql.org
> Cc: DANTE ALEXANDRA
> Subject: [GENERAL] How to compile PostGreSQL on Windows 64 bits ?
> 
> Hello List,
> 
> After having compiled and installed PostGreSQL on IA64, Red 
> Hat Enterprise Linux 4 AS, I had to do the same thing on 
> Windows 64 bits...
> I have read that PostGreSQL wasn't test on Windows 64 bits, 
> is it still correct ?

Yes.

> My problem is to find how obtain an Unix/Linux environnement 
> compilation on Windows 64 bits.
> Unfortunately, MinGW, Cygwin, and others tools are not 
> available on Windows 64 bits.

No.

> Did somebody already make a compilation on Windows 64 bits ?
> Could you give me some details ?

It hasn't been done yet. Magnus has been playing with generating VC++
project files from the existing Makefiles which will probably offer one
way to do it, and I've been dabbling with the posix stuff in 2k3 server
R2 (previously Interix) with the aim of building a 64bit server, but
nothing is anywhere near usable yet.

Regards, Dave

---(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] How to compile PostGreSQL on Windows 64 bits ?

[DANTE ALEXANDRA]

Hello List,

After having compiled and installed PostGreSQL on IA64, Red Hat 
Enterprise Linux 4 AS, I had to do the same thing on Windows 64 bits...
I have read that PostGreSQL wasn't test on Windows 64 bits, is it still 
correct ?


My problem is to find how obtain an Unix/Linux environnement compilation 
on Windows 64 bits.
Unfortunately, MinGW, Cygwin, and others tools are not available on 
Windows 64 bits.


Did somebody already make a compilation on Windows 64 bits ?
Could you give me some details ?

Thank you in advance.

Regards,
Alexandra DANTE

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

[GENERAL] Regex with patterns in table field

[pgdb]

Hi,
 
like to know how, if possible, for patterns
as attribute in a table to be used in a regex search with a
fixed string. The query should therefore return the rows that
have matching patterns with the single text.
 
Regards
pgdb

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

Re: [GENERAL] Is a high tab_reloid worrying?

[Christopher Browne]

In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] (John 
Sidney-Woollett) transmitted:
> I just added a new table to a slony relication set. The new table
> seems to have a really high tab_reloid value of 94,198,669

I presume the database instance has been around for a while?  If so,
then I wouldn't worry too much.

I'm not certain comprehensively what would consume OIDs, but I expect
temp tables would, so if you have applications that generate them,
that would naturally lead to increases in OID values.

The only time you get *low* values is if you define tables immediately
after creating the database.
-- 
output = ("cbbrowne" "@" "gmail.com")
http://linuxdatabases.info/info/slony.html
As of next Tuesday, all terminal input will be line-at-a-time.
Please update your programs.

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

   http://archives.postgresql.org

Re: [GENERAL] unattened dump

[Paolo Sala]

Martijn van Oosterhout scrisse in data 04/13/06 10:47:


On Thu, Apr 13, 2006 at 10:35:55AM +0200, Paolo Sala wrote:
 

Hi all I am a newbe in postgres and I'm trying to obtain an unattended 
dump to pgsql. I mean, I've tryed to use pg_dump using the -U flag but I 
havn't found a way to pass a password in a unattended way. So I have to 
guess the only solution is to configure pg_hba.conf to use ident as 
authentication method?
   



Well, there's the .pgpass file. However, I usually find the best method is
to connect using unix domain sockets using ident (which isn't really
ident, it uses the OS to verify the user) from the database user
(postgres). Set this up in a cronjob and you're all set.
 

Thank you very much Martjin I was in doubt using your procedure because 
I have phppgadmin installed on the same server and configured to use 
unix domain sockets. But now I have disabled the use of postgres user in 
phppgadmin so I hope postgres is secure...


Thank you very much

Piviul

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

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

[hubert depesz lubaczewski]

On 4/13/06, Richard Huxton  wrote:
Hmm - I believe that means a data/index block was corrupted.indices were recreated (reindex table), so i think this is data related problem. 
Have you seen any crashes, or hardware-related errors in your logs?nope. uptime is over 40 days.the machine is not used for anything else so i can't tell anything, but i didn't see any problems with it.
What are your config settings, particularly the first three here:   
http://www.postgresql.org/docs/8.1/static/runtime-config-wal.htmlfsync, wal_sync_method, full_page_writessure:irr=# show fsync; fsync--- on(1 row)irr=# show wal_sync_method;
 wal_sync_method- fdatasync(1 row)irr=# show full_page_writes; full_page_writes-- on(1 row)depesz

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

[Richard Huxton]

hubert depesz lubaczewski wrote:

hi.
basic information:
machine: desktop computer, with sata hard drive, no bad blocks. 2g 
ram.AMD Sempron(tm) Processor 2600+

system: linux debian testing, using 2.6.11 kernel.
postgresql: 8.1.3 compiled by hand using:

...


version() -> PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc 
(GCC) 3.3.5 (Debian 1:3.3.5-13)


OK - nothing unusual there.


on this machine i have copy of 40G database from production servers.
i was testing migration to hstore ( 
http://www.sai.msu.su/~megera/postgres/gist/hstore/README.hstore)


at some point ot tests it failed saying corrupted item pointer.



is there any way i can check what went wrong?
i dont need to recover the data.
i just need to know wherher the problem is hstore-related, 
hardware-related or just random thing happening because of nothing.


Hmm - I believe that means a data/index block was corrupted.

Have you seen any crashes, or hardware-related errors in your logs?

What are your config settings, particularly the first three here:
  http://www.postgresql.org/docs/8.1/static/runtime-config-wal.html
fsync, wal_sync_method, full_page_writes

--
  Richard Huxton
  Archonet Ltd

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

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

Re: [GENERAL] [Slony1-general] Is a high tab_reloid worrying?

[John Sidney-Woollett]

My tables are defined "WITHOUT OID" - does that make a difference?

John

Hannu Krosing wrote:

Ühel kenal päeval, N, 2006-04-13 kell 10:06, kirjutas John
Sidney-Woollett:

I just added a new table to a slony relication set. The new table seems 
to have a really high tab_reloid value of 94,198,669


...

Is this something I should be worried about? Can I find out where all 
the intermediate OIDs have gone?



probably to data rows, unless you have all your tables defined using
WITHOUT OID. OIDs are assigned from a global "sequence".


Hannu


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

   http://archives.postgresql.org


---(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] corrupted item pointer:???

[hubert depesz lubaczewski]

hi.basic information:machine: desktop computer, with sata hard drive, no bad blocks. 2g ram.AMD Sempron(tm) Processor 2600+system: linux debian testing, using 2.6.11 kernel.postgresql: 8.1.3 compiled by hand using:
./configure \    --prefix=/home/pgdba/work \    --without-debug \    --disable-debug \    --with-pgport=5810 \    --with-tcl \    --with-perl \    --with-python \
    --without-krb4 \    --without-krb5 \    --without-pam \    --without-rendezvous \    --with-openssl \    --with-readline \    --with-zlib \    --with-gnu-ld
version() -> PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-13)on this machine i have copy of 40G database from production servers.i was testing migration to hstore (
http://www.sai.msu.su/~megera/postgres/gist/hstore/README.hstore)at some point ot tests it failed saying corrupted item pointer.
migration was done using:LOOP:   select data from test_table where primary_key = constant;   select data from secondary_table where unique_key = constant;   update test_table set hstore_field = some_value where primary_key = constant
REPEAT FOR ALL items;this loop was made in external code (perl, using dbi + dbd::pg).we did about 37 such loops every second.everything went fine.then - every 30 minutes) i ran vacuum to reclaim space from test_table.
at one of such vacuums it paniced showing me forementioned error and killing all connections.is there any way i can check what went wrong?i dont need to recover the data.i just need to know wherher the problem is hstore-related, hardware-related or just random thing happening because of nothing.
i tried:hand-vacuumvacuum analyzevacuum full analyzereindex the tableanother vacuumnone of this worked.i still get the corrupted item pointer message.any clues on how can i check what went wrong?
depesz

Re: [GENERAL] [Slony1-general] Is a high tab_reloid worrying?

[Hannu Krosing]

Ühel kenal päeval, N, 2006-04-13 kell 10:06, kirjutas John
Sidney-Woollett:
> I just added a new table to a slony relication set. The new table seems 
> to have a really high tab_reloid value of 94,198,669
...
> Is this something I should be worried about? Can I find out where all 
> the intermediate OIDs have gone?

probably to data rows, unless you have all your tables defined using
WITHOUT OID. OIDs are assigned from a global "sequence".


Hannu


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

   http://archives.postgresql.org

[GENERAL] Is a high tab_reloid worrying?

[John Sidney-Woollett]

I just added a new table to a slony relication set. The new table seems 
to have a really high tab_reloid value of 94,198,669


I'm using Slon 1.1.5 with pg 7.4.6 and 7.4.11 on unix

On the master I see (wcprogallery is the new table added to set 4)

 tab_id|tab_reloid|tab_relname |tab_nspname|tab_set|tab_idxname
---+--++---+---+---
   4005| 18284|wccustomer  |customer   |  4|wccustomer_pkey
 ...
   4035| 18858|wcrecommend |customer   |  4|wcrecommend_pkey
   4036|  94198669|wcprogallery|customer   |  4|wcprogallery_pkey

On the slave I see

 tab_id|tab_reloid|tab_relname |tab_nspname|tab_set|tab_idxname
---+--++---+---+---
   4005|   1671239|wccustomer  |customer   |  4|wccustomer_pkey
 ...
   4035|   1671608|wcrecommend |customer   |  4|wcrecommend_pkey
   4036|   5741203|wcprogallery|customer   |  4|wcprogallery_pkey

Is this something I should be worried about? Can I find out where all 
the intermediate OIDs have gone?


I have around 70 tables and about 200 pgplsql functions in the database.

John

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

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

Re: [GENERAL] unattened dump

[Martijn van Oosterhout]

On Thu, Apr 13, 2006 at 10:35:55AM +0200, Paolo Sala wrote:
> Hi all I am a newbe in postgres and I'm trying to obtain an unattended 
> dump to pgsql. I mean, I've tryed to use pg_dump using the -U flag but I 
> havn't found a way to pass a password in a unattended way. So I have to 
> guess the only solution is to configure pg_hba.conf to use ident as 
> authentication method?

Well, there's the .pgpass file. However, I usually find the best method is
to connect using unix domain sockets using ident (which isn't really
ident, it uses the OS to verify the user) from the database user
(postgres). Set this up in a cronjob and you're all set.

Have a ncie 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] how can I create the DML for an existing database - within an application

[Dave Page]

 

  
  
  From: Harald Armin Massa 
  [mailto:[EMAIL PROTECTED] Sent: 13 April 2006 
  09:37To: Dave PageSubject: Re: [GENERAL] how can I 
  create the DML for an existing database - within an 
  application
  Dave,
  
  >> 
AFAIK, pgadmin opens a pipe to pg_dump.Yes, when creating an actual 
backup. To display the definition on the main window it goes to great 
pains to reverse engineer the SQL from thecatalogues.
  do you think it is possible to "isolate that pain" within the source 
  and expose it as a module? Or do you think this would be way to 
  difficult?  
That 
would require significant restructuring of pgAdmin's code, in a way that would 
not make it more usable within pgAdmin. It's not something we would consider 
doing.
 
Regards, Dave. 

[GENERAL] unattened dump

[Paolo Sala]

Hi all I am a newbe in postgres and I'm trying to obtain an unattended 
dump to pgsql. I mean, I've tryed to use pg_dump using the -U flag but I 
havn't found a way to pass a password in a unattended way. So I have to 
guess the only solution is to configure pg_hba.conf to use ident as 
authentication method?


Thank you very much

Piviul


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

Re: [GENERAL] how can I create the DML for an existing database - within an application

[Dave Page]

 

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of 
> Magnus Hagander
> Sent: 13 April 2006 08:40
> To: Harald Armin Massa; pgsql-general
> Subject: Re: [GENERAL] how can I create the DML for an 
> existing database - within an application
> 
> > I want to get the output from
> > 
> > pg_dump --struct-only --table=whatever
> > 
> > inside a programm. Of course I could call pg_dump in a seperate 
> > process and capture the output and all; but ...
> > 
> > as PGAdmin is doing it someway, I strongly suspect there is 
> a kind of 
> > call to recreate the DML language from a table in the 
> database. Please 
> > give me a hint!
> 
> AFAIK, pgadmin opens a pipe to pg_dump.

Yes, when creating an actual backup. To display the definition on the
main window it goes to great pains to reverse engineer the SQL from the
catalogues.

Regards, Dave

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

   http://archives.postgresql.org

Re: [GENERAL] how can I create the DML for an existing database - within an application

[Magnus Hagander]

> I want to get the output from
> 
> pg_dump --struct-only --table=whatever
> 
> inside a programm. Of course I could call pg_dump in a 
> seperate process and capture the output and all; but ...
> 
> as PGAdmin is doing it someway, I strongly suspect there is a 
> kind of call to recreate the DML language from a table in the 
> database. Please give me a hint! 

AFAIK, pgadmin opens a pipe to pg_dump.

//Magnus

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