Re: [GENERAL] Single missing WAL in long sequence..

[berdam]

how to unsubscribe of this list??

Simon Riggs escreveu:

On Mon, 2009-03-23 at 21:51 -0700, Aimon Bustardo wrote:

  
Hi, I ran into a recovery problem where I have a single missing WAL  
file in a long sequence. I need a way to recover past that missing WAL  
archive. I am desperately hoping there is a way to do this. Any help  
that can be given will be extremely appreciated!



There is no supported way of doing this because there may have been
dependencies between what was in the missing file and what comes later.

We are able to assist you with commercial recovery services to
investigate and recover data following detailed analysis. It will take
some time and is outside the help we can reasonably provide for free.

  


--
-
Uberdam Cavaletti
Formado em Tecnologia da informação - Unoesc - Xxe
Pós graduado em Desenvolvimento Java - Unoesc - Xxe

Acesse  http://www.curricular.com.br/berdam
Acesse  http://passandoveneno.blogspot.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] debugging in pgadmin

[berdam]

asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff

asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff

asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff

asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
asdfasdfasdfasdfasdfasdfasdfasdfasdfasd
asdfasdff
josep porres escreveu:

Ooops,   I haven't realized the second schema, I've found the function.
Here you are your test log.

2009/3/24 josep porres mailto:jmpor...@gmail.com>>

good morning,

I have a demo database, but it's empty.
if you tell me where is the creation script, I will try it

2009/3/23 Dave Page mailto:dp...@pgadmin.org>>

On Mon, Mar 23, 2009 at 1:56 PM, josep porres
mailto:jmpor...@gmail.com>> wrote:

> well, now a log with only trying to debug setting a breakpoint

Hmmm - do you still have the demo schema on that server? Can
you try
setting a breakpoint on the list_emp() function, and then
calling it
please?


--
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com






  


--
-
Uberdam Cavaletti
Formado em Tecnologia da informação - Unoesc - Xxe
Pós graduado em Desenvolvimento Java - Unoesc - Xxe

Acesse  http://www.curricular.com.br/berdam
Acesse  http://passandoveneno.blog

Re: Res: [GENERAL] Case sensitivity problems with user name

[berdam]

asdsss
sss
sss
sss

Berdam escreveu:

aer






ggg
gg
g
gg

*De:* John R Pierce 
*Para:* Will Rutherdale (rutherw) 
*Cc:* pgsql-general@postgresql.org
*Enviadas:* Terça-feira, 24 de Março de 2009 17:21:20
*Assunto:* Re: [GENERAL] Case sensitivity problems with user name

Will Rutherdale (rutherw) wrote:
> When I try going into psql as user postgres to change the user name, 
I get this problem:

>
> postgres=# alter user mickey rename to Mickey;
>
> ERROR: role “mickey” already exists
>

you might try...

alter user "mickey" rename to "Mickey";




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

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



Veja quais são os assuntos do momento no Yahoo! + Buscados: Top 10 
 
- Celebridades 
 
- Música 
 
- Esportes 



--
-
Uberdam Cavaletti
Formado em Tecnologia da informação - Unoesc - Xxe
Pós graduado em Desenvolvimento Java - Unoesc - Xxe

Acesse  http://www.curricular.com.br/berdam
Acesse  http://passandoveneno.blogspot.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] Last modification time of a database?

[Tom Lane]

Erik Jones  writes:
> These are all client databases at the web hosting company I work at.   
> I can't go putting triggers on all of their tables.  I think I'll just  
> start taking snapshots of pertinent data from pg_stat_activity and  
> after I've been collecting data for a while run a report of dbs that  
> haven't seen connections in X long since what I'm really after is  
> inactive databases.

It seems like a pretty low-precision result would be sufficient for what
you need.  Have you tried just tracking the last file mod time within
each database directory?  This would be later than the real last use
due to delayed vacuum, etc, but it might be good enough.

regards, tom lane

-- 
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] Last modification time of a database?

[Erik Jones]

On Mar 23, 2009, at 5:00 PM, Craig Ringer wrote:


Erik Jones wrote:

Am I missing something obvious here?  If not, has anyone come up  
with a reliable way to do this?


Triggers on all your tables that append to a logging table?

Have the client do it?

Note that you do *NOT* want to have triggers that attempt to UPDATE  
a table to record the last modified time for that table. They'll  
cause transactions that touch the same table to block waiting until  
the first one commits/rolls back, so they'll ruin your concurrency.  
They may also cause unexpected deadlock aborts of transactions.


These are all client databases at the web hosting company I work at.   
I can't go putting triggers on all of their tables.  I think I'll just  
start taking snapshots of pertinent data from pg_stat_activity and  
after I've been collecting data for a while run a report of dbs that  
haven't seen connections in X long since what I'm really after is  
inactive databases.


Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k






--
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] Proper entry of polygon type data

[Brent Wood]

Hi Peter,

If you want to use Postgres to store/manage/query spatial data, I strongly 
recommend you look at PostGIS, & not the native Postgres geometry types.


Brent Wood

Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> Peter Willis  03/24/09 10:35 AM >>>
Hello,

I would like to use 'polygon' type data and am wondering about
the entry format of the vertex coordinates.

Are the coordinates of the polygon type to be entered one
entry per polygon vertex, or one entry per polygon edge segment?

For example:
I have a triangle with vertex corners A, B, C.

One entry per vertex format suggests

INSERT INTO my_table (my_polygon_column)
VALUES ( ((Ax,Ay),(Bx,By),(Cx,Cy)) );


One entry per edge format suggests

INSERT INTO my_table (my_polygon_column)
VALUES ( ((Ax,Ay),(Bx,By),(Bx,By),(Cx,Cy),(Cx,Cy),(Ax,Ay)) );

Which entry format is the correct one?

If per vertex format is the correct one, do I need to
'close' the path by entering the first vertex again at the end of the
list?

ie:
INSERT INTO my_table (my_polygon_column)
VALUES ( ((Ax,Ay),(Bx,By),(Cx,Cy),(Ax,Ay)) );

Thanks,

Peter

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

NIWA is the trading name of the National Institute of Water & Atmospheric 
Research Ltd.

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

Re: Res: [GENERAL] Case sensitivity problems with user name

[Raymond O'Donnell]

On 24/03/2009 20:25, Berdam wrote:
> aer
> 

?


--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
r...@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

-- 
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] Case sensitivity problems with user name

[Raymond O'Donnell]

On 24/03/2009 20:38, Will Rutherdale (rutherw) wrote:

> Pardon my newness to Postgres.

We've all been there at some point! - except of course for Tom Lane.

:-)

Ray.

--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
r...@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

-- 
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] Defer a functional index calculation?

[Randall Lucas]

On Mon, Mar 23, 2009 at 4:46 AM, Sam Mason  wrote:

> On Fri, Mar 20, 2009 at 05:37:33PM -0700, Randall Lucas wrote:
> > I added a functional index.
> >
> >   create table example (id serial primary key, stuff text, parent_id
> int);
> >   create index example_root_idx on example (get_root_id(id));
> >
> > (get_root_id(id) pulls an example row and recurses onto parent_id until
> it
> > hits a root)
>
> I don't think you can do that; are you lying in the function's
> definition that it's "immutable"?  As far as I know, and a quick check
>

Busted!  Yes, I was lying to postgres.  (The function is all-but-immutable,
your honor; I was only trying lazily to memoize its output...)

What about having some trigger to "cache" the entry's root "parent_id"
> in another column?
>

Looks like that's what I'm headed for.

Thank you,

Randall

Re: [GENERAL] Case sensitivity problems with user name

[Will Rutherdale (rutherw)]

Yes, that worked, thanks.

After also doing this:

alter database "mickey" rename to "Mickey";

it solved the problem.

Pardon my newness to Postgres.

-Will


-Original Message-
From: John R Pierce [mailto:pie...@hogranch.com] 
Sent: 24 March 2009 16:21
To: Will Rutherdale (rutherw)
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Case sensitivity problems with user name

Will Rutherdale (rutherw) wrote:
> When I try going into psql as user postgres to change the user name, I

> get this problem:
>
> postgres=# alter user mickey rename to Mickey;
>
> ERROR: role "mickey" already exists
>

you might try...

alter user "mickey" rename to "Mickey";




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

Res: [GENERAL] Case sensitivity problems with user name

[Berdam]

aer






ggg
gg
g
gg



De: John R Pierce 
Para: Will Rutherdale (rutherw) 
Cc: pgsql-general@postgresql.org
Enviadas: Terça-feira, 24 de Março de 2009 17:21:20
Assunto: Re: [GENERAL] Case sensitivity problems with user name

Will Rutherdale (rutherw) wrote:
> When I try going into psql as user postgres to change the user name, I get 
> this problem:
> 
> postgres=# alter user mickey rename to Mickey;
> 
> ERROR: role “mickey” already exists
> 

you might try...

alter user "mickey" rename to "Mickey";




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


  Veja quais são os assuntos do momento no Yahoo! +Buscados
http://br.maisbuscados.yahoo.com

Re: [GENERAL] Case sensitivity problems with user name

[John R Pierce]

Will Rutherdale (rutherw) wrote:
When I try going into psql as user postgres to change the user name, I 
get this problem:


postgres=# alter user mickey rename to Mickey;

ERROR: role “mickey” already exists



you might try...

alter user "mickey" rename to "Mickey";




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

[GENERAL] Case sensitivity problems with user name

[Will Rutherdale (rutherw)]

Hi.

 

I have a little problem with user names and Postgres commands.

 

Due to quirks in corporate account management using ADS, some user names
come out as, say, Mickey when you log into Linux instead of mickey.
This unfortunate user tries to run Postgres commands and gets for
instance:

 

$ psql

psql: FATAL:  database "Mickey" does not exist

 

$ id

uid=123456(Mickey) gid=...

 

$ pg_dump >dump.out

pg_dump: [archiver (db)]  connection to database "Mickey" failed: FATAL:
database "Mickey" does not exist

 

$ psql mickey mickey

Welcome to psql 8.3.6, the PostgreSQL interactive terminal.

. . .

 

I have set up pg_hba.conf with lines like 

local all all trust

host all all 127.0.0.1/32 trust

 

I tried modifying pg_ident.conf but couldn't get any results.

 

When I try going into psql as user postgres to change the user name, I
get this problem:

postgres=# alter user mickey rename to Mickey;

ERROR:  role "mickey" already exists

 

What can I do so that the default user is accepted?  I would like the
user to be able to just type:

 

$ psql

 

and get in successfully.

 

Any help would be appreciated.

 

-Will

 

Res: [GENERAL] [win32] 8.3.5 -> 8.3.7 install trouble

[Berdam]

asss
asd
fas
dfasdffddsssa
asdf
asdf
asdasdfasdfasdfasdfasdfasdfsa
fas
df
asdf
asdf
asd
fas
df
asdf
asdf
asd
fasdfasdfasdfasdfsad

 

Uberdam Cavaletti
Formado em Tecnologia da Informação - Unoesc - Xxe
Pós-graduado em desenvolvimento Java - Unoesc - Xxe

http://www.curricular.com.br/berdam

Visite
http://passandoveneno.blogspot.com







De: Kev 
Para: pgsql-general@postgresql.org
Enviadas: Terça-feira, 24 de Março de 2009 9:24:53
Assunto: [GENERAL] [win32] 8.3.5 -> 8.3.7 install trouble

I went to upgrade an installation and was stopped with (approximately)
the following message after clicking Next on the step where I told it
to use the same data directory:

"The existing data directory (date time: floating-point numbers) is
not compatible with this server (date time: 64-bit integers)."

This is with the EDB postgresql-8.3.7-1-windows.exe that I found on
postgresql.org.  Apparently for 8.3.5 I had found a .zip file
containing an .msi.  I think the new text threw me--I remember the
install being easy, and the .msi mentioned stuff about Group Policy
and other advanced options and I thought, "Well, I don't need anything
that fancy."

Just a warning in case someone else was thinking they were compatible.

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


  Veja quais são os assuntos do momento no Yahoo! +Buscados
http://br.maisbuscados.yahoo.com

Re: [GENERAL] LISTEN/NOTIFY problem

[Dmitri Girski]

Thank you guys for your replies!

It is definitely something wrong with my code, here is the thread which does
the listening:
http://mitek.id.au/flex/DBListener.cpp

The rest of application is fairly big and does not relate to the database.
For libpq calls I am using the SPTK (http://sptk.net) library. I've added
additional wrappers for PGsocket, PQfreemem.

So the current code works without any problem, but as soon as I remove
subcribe() function from the loop and call it just once, daemon eventually
will stop getting notifications.
There is nothing wrong with the socket or db connections - all possible
error will be logged.


Cheers,
Dmitri.


On Mon, Mar 23, 2009 at 10:00 PM, Dmitri Girski  wrote:

> Hi everybody,
> I've got a weird problem with LISTEN/NOTIFY.
>
> My C++ app subscribes for the notifications, just like in libpq examples:
> http://www.postgresql.org/docs/8.3/static/libpq-example.html
>
> The only difference, that I am setting the timeout on select just to check
> if application wants to exit.
>
> //open session
>
> //subscribe
>
> while(!exit)
> {
> sock = PGsocket(conn)
>
> res = select(sock);
>
> if (res)
>
>//check if it timeout
>//or if there was a notification
>
>
> }
>
> The problem that I am facing is that after some time notifications stop
> coming.
> select() returns on timeout and nothing else.
>
> Just as a test a included UNLISTEN/LISTEN sequence into the loop, so after
> timeout or event application re-subscribes. And this helps.
>
> The question is, what I am doing wrong with the code in the first place?
>
> Any help is appreciated.
>
> Cheers,
> Dmitri.
>
>
>
>
>
>
>
>
>
>
> --
> @Gmail
>



-- 
@Gmail

[GENERAL] Fulltext: problem with english words in german text

[Torsten Zühlsdorff]

Hello,

i have a problem with understanding fulltext search in PG 8.3.

Example:

CREATE TABLE tfulltext (body text, fulltext tsvector);

INSERT INTO tfulltext VALUES ('title und description sind wichtige 
grundlagen', to_tsvector('pg_catalog.german', 'title und description 
sind wichtige grundlagen'));


SELECT * from tfulltext;
  body  | 
fulltext

+---
 title und description sind wichtige grundlagen | 'titl':1 'wichtig':5 
'grundlag':6 'description':3


I expect, that the query:
SELECT * FROM tfulltext WHERE fulltext @@ to_tsquery('title');

Will return the entry. But the result-set is empty. If i use 'titl' 
(without 'e') as parameter of to_tsquery it returns the entry.


I try to cast the search-parameter to ts_vector, but it didn't work. How 
can i solve the problem?


Thanks for every hint and greetings from Germany,
Torsten
--
http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 
verschiedenen Datenbanksystemen abstrahiert,
Queries von Applikationen trennt und automatisch die Query-Ergebnisse 
auswerten kann.


--
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: problem with english words in german text

[Torsten Zühlsdorff]

[..]

I've just noticed, that i forgot to change the subject. While writing i 
figured out, that the question is not the one, i want to ask.


I am sorry for confusions.

Greetings,
Torsten
--
http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 
verschiedenen Datenbanksystemen abstrahiert,
Queries von Applikationen trennt und automatisch die Query-Ergebnisse 
auswerten kann.


--
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: problem with english words in german text

[Torsten Zühlsdorff]

Torsten Zühlsdorff schrieb:

Hello,

i have a problem with understanding fulltext search in PG 8.3.

[..]


I solved it. I have to specify the language in to_tsquery(). -.-

Greetings,
Torsten
--
http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 
verschiedenen Datenbanksystemen abstrahiert,
Queries von Applikationen trennt und automatisch die Query-Ergebnisse 
auswerten kann.


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

[GENERAL] [win32] 8.3.5 -> 8.3.7 install trouble

[Kev]

I went to upgrade an installation and was stopped with (approximately)
the following message after clicking Next on the step where I told it
to use the same data directory:

"The existing data directory (date time: floating-point numbers) is
not compatible with this server (date time: 64-bit integers)."

This is with the EDB postgresql-8.3.7-1-windows.exe that I found on
postgresql.org.  Apparently for 8.3.5 I had found a .zip file
containing an .msi.  I think the new text threw me--I remember the
install being easy, and the .msi mentioned stuff about Group Policy
and other advanced options and I thought, "Well, I don't need anything
that fancy."

Just a warning in case someone else was thinking they were compatible.

-- 
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] Timezone calculation question

[Tom Lane]

=?UTF-8?B?0KDQvtC80LDQvSDQnNCw0YjQuNGA0L7Qsg==?=  writes:
> Seems I've missed something important about time zones. On my server 
> i've got local timezone 'W-SU' (Moscow time):

> => show timezone;
>  TimeZone
> --
>  W-SU

> => select now();
>   now 
> ---
>  2009-03-24 13:23:39.655057+03

> Till now all seems ok. Than i'm trying to figure out local time at near 
> region:

> => select now() at time zone 'UTC+4';
>   timezone 
> 
>  2009-03-24 06:28:30.383373

That's not "near" Moscow.  What's confusing you is that the sign
conventions are different --- in time zone names, plus means west
of Greenwich, but elsewhere (in particular, in timestamp values)
plus means east of Greenwich.

Don't blame us, blame POSIX and ISO for not talking to each other
when they made the relevant standards.  Note the fine print here:
http://developer.postgresql.org/pgdocs/postgres/datatype-datetime.html#DATATYPE-TIMEZONES

regards, tom lane

-- 
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] debugging in pgadmin

[Dave Page]

On Tue, Mar 24, 2009 at 1:42 PM, josep porres  wrote:
> i've just upgraded the server to 8.3.7 holding the same data directory
>
> select * from pldbg_get_proxy_info();
> "serverversionstr","serverversionnum","proxyapiver","serverprocessid"
> "PostgreSQL 8.3devel on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
> (mingw-special)",80300,3,2948
>
> is it ok for testing your dll's?

Yes. I'll sent them offlist if that's OK?


-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.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] debugging in pgadmin

[josep porres]

by the way, after upgrading, i get the same behaviour
even executing the function in a query window created after setting the
breakpoint.

2009/3/24 josep porres 

> i've just upgraded the server to 8.3.7 holding the same data directory
>
> select * from pldbg_get_proxy_info();
> "serverversionstr","serverversionnum","proxyapiver","serverprocessid"
> "PostgreSQL 8.3devel on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
> 3.4.2 (mingw-special)",80300,3,2948
>
> is it ok for testing your dll's?
>
> 2009/3/24 Dave Page 
>
> On Tue, Mar 24, 2009 at 7:50 AM, josep porres  wrote:
>> > Ooops,   I haven't realized the second schema, I've found the function.
>> > Here you are your test log.
>>
>> OK, I think we've tracked down the problem. Can I send you some
>> updated DLLs to try offlist?
>>
>>
>> --
>> Dave Page
>> EnterpriseDB UK:   http://www.enterprisedb.com
>>
>
>

Re: [GENERAL] debugging in pgadmin

[josep porres]

i've just upgraded the server to 8.3.7 holding the same data directory

select * from pldbg_get_proxy_info();
"serverversionstr","serverversionnum","proxyapiver","serverprocessid"
"PostgreSQL 8.3devel on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)",80300,3,2948

is it ok for testing your dll's?

2009/3/24 Dave Page 

> On Tue, Mar 24, 2009 at 7:50 AM, josep porres  wrote:
> > Ooops,   I haven't realized the second schema, I've found the function.
> > Here you are your test log.
>
> OK, I think we've tracked down the problem. Can I send you some
> updated DLLs to try offlist?
>
>
> --
> Dave Page
> EnterpriseDB UK:   http://www.enterprisedb.com
>

Re: [GENERAL] debugging in pgadmin

[Dave Page]

On Tue, Mar 24, 2009 at 7:50 AM, josep porres  wrote:
> Ooops,   I haven't realized the second schema, I've found the function.
> Here you are your test log.

OK, I think we've tracked down the problem. Can I send you some
updated DLLs to try offlist?


-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.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] 1- and 2-dimensional indexes on same column: why is the 2d one preferred?

[Marinos Yannikos]

Tom Lane schrieb:

Marinos Yannikos  writes:

"i_a" btree (a)
"i_ab" btree (a, b)


I suspect that these indexes are exactly the same size --- look at
pg_class.relpages or use the pg_relation_size() function to verify.


For some reason, the first one is actually about twice the size of the 
second (175458 relpages vs. 88186, pg_relation_size() confirms it).



It wouldn't
really matter anyway because the actual runtime should be pretty
much the same too.


The runtime is unfortunately worse in some cases due to the degradation 
we've been seeing (lots of INSERT/UPDATE on this table), but I think we 
fixed this with nightly REINDEX runs on the 2-dimensional indexes (which 
is probably also the reason for the odd sizes above). I guess we can 
just drop the first index then.


Thanks,
-mjy


--
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] text column constraint, newbie question

[Daniel Verite]

RebeccaJ wrote:


Thanks, everyone, for your contribution to this thread. I'm
approaching the database design of my web application differently,
now. Before, I was planning to have CHECK constraints in all of my
text or char fields, to keep out all semicolons, single quotes, and
anything else that looked dangerous. Now I'm thinking that I'll be
using htmlentities(), pg_escape_string() and pg_query_params() as
safety filters, and otherwise allowing users to store whatever they
want to, in the fields where I store/retrieve user input.


Note that htmlentities() expects LATIN1-encoded strings and is thus 
unusable on UTF-8 contents.
So if you end up talking UTF-8 with the database, you'll probably need 
to use htmlspecialchars() instead, and UTF-8 as your HTML charset.


Best regards,

--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

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

[GENERAL] Timezone calculation question

[Роман Маширов]

Seems I've missed something important about time zones. On my server 
i've got local timezone 'W-SU' (Moscow time):


=> show timezone;
TimeZone
--
W-SU

=> select now();
 now 
---

2009-03-24 13:23:39.655057+03

Till now all seems ok. Than i'm trying to figure out local time at near 
region:


=> select now() at time zone 'UTC+4';
 timezone 


2009-03-24 06:28:30.383373


select now() at time zone 'UTC+3';
 timezone 


2009-03-24 07:24:11.011075

Seems like zone offset been added twice...
Server 8.3.5, OS FreeBSD 7.0-RELEASE

Which way should i get correct local time, when i've got timestamp with 
timezone and name of the target timezone?


Thanks beforehand
--
MRJ



--
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] Move PG installation to a new computer

[Thomas Kellerer]

Albe Laurenz, 24.03.2009 10:34:

Thomas Kellerer wrote:
I'm getting a new notebook and want to confirm that my idea 
on how to move my Postgres installation will work This is a 
development/test installation and not a production system, so 
it doesn't need to be 100% fail safe.


Both systems are Windows XP 32bit.

My plan was to install the same PG release (8.3) on the new 
computer, skip the initdb task during installation, but let 
the installer create the necessary service pointing to an 
empty data directory. Using the same OS user account.


Once the installation is finished, copy all files from the 
old datadir (after shutting down that postmaster) to the new 
datadir as the PG Windows user, so that access right are 
setup correctly. 

As both machines have the same OS and architecture my 
assumption is that this should work. 
Or am I mistaken?


Yes, that should work.


Great, thanks for the answer.

Thomas


--
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] Accent insensitive search?

[Jasen Betts]

On 2009-03-18, cifroes  wrote:
> This is a multi-part message in MIME format.
>
> --_=_NextPart_001_01C9A7E6.B32BBA87
> Content-Type: text/plain;
>   charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> Hi,
>
> I have a DB in utf-8 and postgres 8.3.x.=20
>
> How can I do an accent insensitive search (like ...) ?

use a posix regular expression that matches the string you want:

select * from foo where colname ~ '^[CcĆćĈĉĊċČč][ĀāĂ㥹Aa][Pp][ŌōŎŏŐő]$';

you could write a function to do the translation.


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

[GENERAL] lots of WAL files retained with restore_command through %r parameter

[Duco Fijma]

Hello,

I'm having a problem with a warm standy-by / continuous archiving setup. 
The archive of WAL segments on the "slave" server sometimes (and only 
sometimes) grows _huge_.


I'm using a warm standy-by / continuous archiving setup with two 
servers. The "slave" servers, in stand-by mode, is processing WAL 
segments shipped from the "master" server using "restore_command" in 
recovery.conf. restore_command calls a scripts that subsequently uses 
pg_standby to process the WAL's:


The setup uses "restore_command" in recovery.conf to call a script that 
subsequently calls pg_standby to process the WAL's:



restore_command='/blabla/restore.sh %f %p %r'

and, in restore.sh:

.
.
/usr/lib/postgresql/8.3/bin/pg_standby -d -s 5 -t /tmp/pgsql.trigger 
/blabla/remote_logs "$f" "$p" "$r"

.
.

As I understand it, the %r variable passed to restore_command is the 
name of the oldest file that needs to be kept, "containing the last 
valid restart point."


Normally, I see increments to this parameter in calls to restore_command 
and then pg_standby removing older WAL's.


Then sometimes, the value of the %r parameter in calls of 
restore_command never changes any more, pg_standby then does not remove 
any WAL's anymore, the partition hosting the WAL's from the master 
server fills up.


I don't precisely understand how the %r value passed to restore_command 
is calculated. Could the problem be some long lasting transaction on the 
master? Any other condition I could check? The documentation states that 
%r points to the WAL with the last valid restart point. However, I could 
not find a further definition of the term "restart point" in the 
Postgres manual.


Any ideas?

Regards,

Duco Fijma



--
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] Single missing WAL in long sequence..

[Albe Laurenz]

Aimon Bustardo wrote:
> Hi, I ran into a recovery problem where I have a single missing WAL  
> file in a long sequence. I need a way to recover past that missing WAL  
> archive. I am desperately hoping there is a way to do this. Any help  
> that can be given will be extremely appreciated!

I am sorry, but you cannot do that.
If you cannot get the missing WAL segment, you cannot recover.

Yours,
Laurenz Albe

-- 
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] Move PG installation to a new computer

[Albe Laurenz]

Thomas Kellerer wrote:
> I'm getting a new notebook and want to confirm that my idea 
> on how to move my Postgres installation will work This is a 
> development/test installation and not a production system, so 
> it doesn't need to be 100% fail safe.
> 
> Both systems are Windows XP 32bit.
> 
> My plan was to install the same PG release (8.3) on the new 
> computer, skip the initdb task during installation, but let 
> the installer create the necessary service pointing to an 
> empty data directory. Using the same OS user account.
> 
> Once the installation is finished, copy all files from the 
> old datadir (after shutting down that postmaster) to the new 
> datadir as the PG Windows user, so that access right are 
> setup correctly. 
> 
> As both machines have the same OS and architecture my 
> assumption is that this should work. 
> Or am I mistaken?

Yes, that should work.

Yours,
Laurenz Albe

-- 
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] Accessing large objects

[Albe Laurenz]

> how to access large objects from the database when using 
> PGAdmin like client applications? When a normal select is 
> executed on lo type columns it gives only the number which 
> refers to large objects stored in 'pg_largeobjects' table. 
> When records from pg_largeobjects are fetched using select, 
> it gives per page, binary string data. How to get the 
> complete large objects from it?
> 
> I am using PG 8.3.6 on Windows XP SP3 for development.

With the Large Object API, which can be used from almost all
programming APIs. It is documented in

http://www.postgresql.org/docs/8.3/static/largeobjects.html

Yours,
Laurenz Albe

-- 
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] Single missing WAL in long sequence..

[Simon Riggs]

On Mon, 2009-03-23 at 21:51 -0700, Aimon Bustardo wrote:

> Hi, I ran into a recovery problem where I have a single missing WAL  
> file in a long sequence. I need a way to recover past that missing WAL  
> archive. I am desperately hoping there is a way to do this. Any help  
> that can be given will be extremely appreciated!

There is no supported way of doing this because there may have been
dependencies between what was in the missing file and what comes later.

We are able to assist you with commercial recovery services to
investigate and recover data following detailed analysis. It will take
some time and is outside the help we can reasonably provide for free.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


-- 
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] debugging in pgadmin

[josep porres]

good morning,

I have a demo database, but it's empty.
if you tell me where is the creation script, I will try it

2009/3/23 Dave Page 

> On Mon, Mar 23, 2009 at 1:56 PM, josep porres  wrote:
> > well, now a log with only trying to debug setting a breakpoint
>
> Hmmm - do you still have the demo schema on that server? Can you try
> setting a breakpoint on the list_emp() function, and then calling it
> please?
>
>
> --
> Dave Page
> EnterpriseDB UK:   http://www.enterprisedb.com
>

[GENERAL] Tools for converting XML file with dtd to table?

[Joost Kraaijeveld]

Hi,

Are there any tools available for converting XML files for which a dtd
is available into a PostgreSQL table without any programming on my
part? 

The files are basically XML formatted table dumps, with some elements
having attributes.

TIA

-- 
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
web: www.askesis.nl



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