[GENERAL] Installing on Windows without using msi Installer

[Jeff Williams]

I would like to install PostgreSQL manually as part of my applications 
install using InnoSetup.

Is there anywhere the steps required to do this and if so where would I find 
them?

Many thanks
Jeff

-- 
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] Postgresql partitioning

[Ram Ravichandran]

Thanks for the quick response. And I assume that primary key
uniqueness is not tested across tables. Right?

Thanks,
Ram

On Fri, Mar 21, 2008 at 8:59 PM, Erik Jones <[EMAIL PROTECTED]> wrote:
> On Mar 21, 2008, at 7:15 PM, Ram Ravichandran wrote:
>
>  > Hey,
>  >
>  > Suppose I have a table with the following fields:
>  >
>  > CREATE TABLE distributors (
>  > id DECIMAL(3) PRIMARY KEY,
>  > nameVARCHAR(40),
>  > status INTEGER
>  > );
>  >
>  > I would ike to partition this table based on status which can be
>  > [0,1,2,3,4].
>  >
>  > I was wondering if the records can change their status. i.e. If I did
>  > UPDATE distributors SET status = 4 WHERE id = 231122;
>  >
>  > would POSTGRESQL automatically change the record from the current
>  > partition (say partition where status = 3) to the partition where
>  > status = 4?
>
>  No.  Assuming you have CHECK constraints on you partition tables and
>  constraint_exclusion=on postgres will emit an error on an update like
>  that.
>
>
>  > Or would I have to explicitly delete it from one partition table, and
>  > reinsert it in the other?
>
>  Yes.  Also, note that this can't be done in an ON UPDATE trigger as
>  CHECK constraints are checked before any triggers are run.
>
>  Erik Jones
>
>  DBA | Emma(R)
>  [EMAIL PROTECTED]
>  800.595.4401 or 615.292.5888
>  615.292.0777 (fax)
>
>  Emma helps organizations everywhere communicate & market in style.
>  Visit us online at http://www.myemma.com
>
>
>
>

-- 
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] Trigger transactions

[Postgres User]

Question answered- needed to move Insert statement before Update in
main function.

On Fri, Mar 21, 2008 at 6:42 PM, Postgres User
<[EMAIL PROTECTED]> wrote:
> if a function includes this SQL:
>
>  Update Table1  Set field_1 = 'ab';
>  Insert Table2(field_2) VALUES('cd');
>
>
> and I create an update trigger on Table1:
>
>  Create Trigger Table1_Update AFTER Update
>  On Table1 FOR EACH ROW:
>
>  Select * From Table2
>
>
> will the Select statement in the trigger see the row that I inserted
> in the main function?
> if not, is there another way to write these statements to that it does?
>
> thanks
>

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

[GENERAL] Trigger transactions

[Postgres User]

if a function includes this SQL:

  Update Table1  Set field_1 = 'ab';
  Insert Table2(field_2) VALUES('cd');


and I create an update trigger on Table1:

  Create Trigger Table1_Update AFTER Update
  On Table1 FOR EACH ROW:

  Select * From Table2


will the Select statement in the trigger see the row that I inserted
in the main function?
if not, is there another way to write these statements to that it does?

thanks

-- 
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] Postgresql partitioning

[Erik Jones]

On Mar 21, 2008, at 7:15 PM, Ram Ravichandran wrote:


Hey,

Suppose I have a table with the following fields:

CREATE TABLE distributors (
id DECIMAL(3) PRIMARY KEY,
nameVARCHAR(40),
status INTEGER
);

I would ike to partition this table based on status which can be  
[0,1,2,3,4].


I was wondering if the records can change their status. i.e. If I did
UPDATE distributors SET status = 4 WHERE id = 231122;

would POSTGRESQL automatically change the record from the current
partition (say partition where status = 3) to the partition where
status = 4?


No.  Assuming you have CHECK constraints on you partition tables and  
constraint_exclusion=on postgres will emit an error on an update like  
that.



Or would I have to explicitly delete it from one partition table, and
reinsert it in the other?


Yes.  Also, note that this can't be done in an ON UPDATE trigger as  
CHECK constraints are checked before any triggers are run.


Erik Jones

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

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




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

[GENERAL] Postgresql partitioning

[Ram Ravichandran]

Hey,

Suppose I have a table with the following fields:

CREATE TABLE distributors (
 id DECIMAL(3) PRIMARY KEY,
 nameVARCHAR(40),
 status INTEGER
 );

I would ike to partition this table based on status which can be [0,1,2,3,4].

I was wondering if the records can change their status. i.e. If I did
UPDATE distributors SET status = 4 WHERE id = 231122;

would POSTGRESQL automatically change the record from the current
partition (say partition where status = 3) to the partition where
status = 4?
Or would I have to explicitly delete it from one partition table, and
reinsert it in the other?

Thanks,

Ram

-- 
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] Transaction wraparound problem with database postgres

[Markus Wollny]

Tom Lane wrote:
> "Markus Wollny" <[EMAIL PROTECTED]> writes:
>> I'd still like to find out what exactly happened here so I can
>> prevent the same from happening again in the future.
> 
> Me too.  It would seem that something did a vacuum of postgres with a
> strange choice of xid cutoff, but I can't think of what would cause
> that.  
> 
> Do you ever do VACUUM FREEZE on your databases?

No, I actually never heard of VACUUM FREEZE, I have to admit.


Computec Media AG
Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)
Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Niels Herrmann
Vorsitzender des Aufsichtsrates: Jürg Marquard 
Umsatzsteuer-Identifikationsnummer: DE 812 575 276



-- 
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] Transaction wraparound problem with database postgres

[Markus Wollny]

Andreas 'ads' Scherbaum wrote:
> Hello,
> First of all, it would help you and most of the readers on this list,
> if you have the error messages in english. There is a german
> mailinglist too, if you want to ask in german.  

Sorry, I tried to describe the issue as best as I could and included the actual 
log entries only for completeness, but was in too much of a hurry to find the 
correct translations.

> vacuum all databases, add the VERBOSE option to see, what actually
> happens. 

Alas, too late, I got rid of the offending 'postgres' database already by 
dropping and recreating.

> Are you using the database 'postgres' at all? 

No, not at all. Didn't touch it ever after initdb.

> And are you sure, that you include all databases? 

Yes. I run the following every night:

su postgres -c '/opt/pgsql/bin/psql -t -c "select datname from pg_database 
order by datname;" template1 | xargs -n 1 /opt/pgsql/bin/psql -q -c "vacuum 
verbose analyze;"'

> Any error messages in the vacuum output? 

None.

> Oh, and by the way: why do you have autovacuum and a manual vacuum
> run every night plus the vacuum run with verbose? 

Paranoia, mostly, I think. I'm using PostgreSQL since long before autovacuum 
was introduced and always thought that it couldn't do any harm to keep my 
original vacuum job running once every night, even though autovacuum does a 
remarkable job, especially for a couple of busy tables where the nightly vacuum 
was not quite enough. Plus, having the verbose output from the log, I get 
useful info for setting the 'max_fsm_pages'/'max_fsm_relations'-options to 
sensible values. Is it a problem to have cron'ed VACUUM-runs in parallel with 
autovacuum?

>> Urgent help would be very much appreciated.
> 
> That's a bit late here ;-)

Ah, well obviously it wasn't - it's always an extremely pleasant surprise when 
one is actually in dire need of help and gets an almost immediate and helpful 
response.

I wish you all happy Easter!

Kind regards

   Markus


Computec Media AG
Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)
Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Niels Herrmann
Vorsitzender des Aufsichtsrates: Jürg Marquard 
Umsatzsteuer-Identifikationsnummer: DE 812 575 276



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

[GENERAL] C function and enum types parameters

[Edoardo Panfili]

I use a C function in my database from 2002, all goes well also with 
postgresql 8.3 but with 8.3.1 it no longer works, the problem is with 
this line:


text *hibrid = (PG_ARGISNULL( 0) ||
VARSIZE(PG_GETARG_TEXT_P( 0))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(0));

the argument number 0 is an enum defined by

CREATE TYPE hibridationLevel AS ENUM('none','genus','specie');

the error is "ERROR:  invalid memory alloc request size 2298488997"

I can't figure by now how to correct my error, can anyone help me?

thank you
edoardo
--
Jabber: [EMAIL PROTECTED]
tel: 075 9142766

--
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] Transaction wraparound problem with database postgres

[Tom Lane]

"Markus Wollny" <[EMAIL PROTECTED]> writes:
> Sorry for the quick updates to my own messages, but I didn't want to
> lean back and wait - so I took to more aggressive measures. All my
> other databases in this cluster are fine - and the 'postgres' database
> doesn't seem to do anything really useful except being the default
> database. I dropped it and recreated it with template1 as template,
> afterwards I could start up my cluster with no problems
> whatsoever.

Yeah, if there were no other problems apparent in pg_database I was
going to suggest that as a recovery method.

> I'd still like to find out what exactly happened here so I
> can prevent the same from happening again in the future.

Me too.  It would seem that something did a vacuum of postgres
with a strange choice of xid cutoff, but I can't think of what
would cause that.

Do you ever do VACUUM FREEZE on your databases?

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] Transaction wraparound problem with database postgres

[Markus Wollny]

Hi!

Thanks for all the quick replies :)

Tom Lane wrote:
> "Markus Wollny" <[EMAIL PROTECTED]> writes:
>> Just some more info, hoping that it helps with a diagnosis:
> 
>>  1: datname (typeid = 19, len = 64, typmod = -1, byval = f)
>>  2: age (typeid = 23, len = 4, typmod = -1, byval = t)
>>  3: datfrozenxid(typeid = 28, len = 4, typmod = -1,
>>  byval = t)  1: datname = "postgres"   
>>  (typeid = 19, len = 64, typmod = -1, byval = f) 2: age =
>>  "-2147321465" (typeid = 23, len = 4, typmod = -1, byval =
>> t) 3: datfrozenxid = "1835116837" (typeid = 28, len = 4, typmod =
>> -1, byval = t) 
> 
> What are the datfrozenxid's of the other rows in pg_database?
> Do the other fields of postgres' row look sane?

Yes, there were no issues on any of the databases that are actually in use:

# select datname, age(datfrozenxid), datfrozenxid from pg_database;
  datname   |age| datfrozenxid
+---+--
 rpfcms | 104213725 |   3881601233
 rpfflash   | 147289015 |   3838525943
 postgres   | 103052193 |   3882762765
 template1  | 104213787 |   3881601171
 template0  |   3052193 |   3982762765
 ezpublish  | 147419044 |   3838395914
 community  | 147566532 |   3838248426
 abo| 147689637 |   3838125321
 bluebox| 147679271 |   3838135687
 cbox   | 147582662 |   3838232296
 mpo| 147309716 |   3838505242
 newsletter | 147309110 |   3838505848
 pcaction   | 147297707 |   3838517251
 pcgames| 147291588 |   3838523370
 magazine   | 147419044 |   3838395914

Only the 'postgres' db was affected - which is puzzling because we don't 
actually use this database actively for anything.

Kind regards
 
  Markus


Computec Media AG
Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)
Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Niels Herrmann
Vorsitzender des Aufsichtsrates: Jürg Marquard 
Umsatzsteuer-Identifikationsnummer: DE 812 575 276



-- 
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] Transaction wraparound problem with database postgres

[Markus Wollny]

Hi!
 
Sorry for the quick updates to my own messages, but I didn't want to lean back 
and wait - so I took to more aggressive measures. All my other databases in 
this cluster are fine - and the 'postgres' database doesn't seem to do anything 
really useful except being the default database. I dropped it and recreated it 
with template1 as template, afterwards I could start up my cluster with no 
problems whatsoever. I'd still like to find out what exactly happened here so I 
can prevent the same from happening again in the future. The age(datfrozenxid) 
is positive again:
 
# SELECT datname, age(datfrozenxid), datfrozenxid FROM pg_database where 
datname='postgres';
 datname  |age| datfrozenxid
--+---+--
 postgres | 100291695 |   3882762765
(1 Zeile)

As I mentioned earlier, I'm running autovaccuum and use a nightly cron to run 
vacuum verbose analyze over all my databases. So lack of vacuum cannot be the 
issue, I think. But what else could have happened here? I regularly scan my 
logs, and there was no early warning for this issue.
 
The first event of this type in the server log was from today:
 
<2008-03-21 17:08:48 CET - 32161: xxx.xxx.xxx.xxx(52833)@magazine>WARNUNG:  
Datenbank »postgres« muss innerhalb von 1100 Transaktionen gevacuumt werden
<2008-03-21 17:08:48 CET - 32161: xxx.xxx.xxx.xxx(52833)@magazine>TIPP:  Um ein 
Abschalten der Datenbank zu vermeiden, führen Sie in »postgres« ein VACUUM über 
die komplette Datenbank aus.
 
(i.e. database 'postgres' need to be vacuumed within 1100 transactions...)
 
A mere three hours later, the server already refused any further requests:
<2008-03-21 20:05:21 CET - 25184: xxx.xxx.xxx.xxx(60837)@magazine>FEHLER:  
Datenbank nimmt keine Befehle an, um Datenverlust in Datenbank »postgres« wegen 
Transaktionsnummernüberlauf zu vermeiden

(ie. database no longer accepts any commands in order to prevent data loss in 
database 'postgres' because of transaction id wraparound)
 
Now that the adrenaline level has dropped to normal, I'd still like to know 
what exactly has happened here; The cluster has been initdb'ed on 2007-04-27.
 
Kind regards
 
   Markus


Computec Media AG
Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)
Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Niels Herrmann
Vorsitzender des Aufsichtsrates: Jürg Marquard 
Umsatzsteuer-Identifikationsnummer: DE 812 575 276

Re: [GENERAL] Transaction wraparound problem with database postgres

[Tom Lane]

"Markus Wollny" <[EMAIL PROTECTED]> writes:
> Just some more info, hoping that it helps with a diagnosis:
 
>  1: datname (typeid = 19, len = 64, typmod = -1, byval = f)
>  2: age (typeid = 23, len = 4, typmod = -1, byval = t)
>  3: datfrozenxid(typeid = 28, len = 4, typmod = -1, byval = t)
> 
>  1: datname = "postgres"(typeid = 19, len = 64, typmod = -1, 
> byval = f)
>  2: age = "-2147321465" (typeid = 23, len = 4, typmod = -1, byval = t)
>  3: datfrozenxid = "1835116837" (typeid = 28, len = 4, typmod = -1, 
> byval = t)

What are the datfrozenxid's of the other rows in pg_database?
Do the other fields of postgres' row look sane?

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] Transaction wraparound problem with database postgres

[Andreas 'ads' Scherbaum]

Hello,

On Fri, 21 Mar 2008 21:50:57 +0100 Markus Wollny wrote:

> My database cluster has just stopped working. I get the following message:
> psql: FATAL:  Datenbank nimmt keine Befehle an, um Datenverlust in Datenbank 
> »postgres« wegen Transaktionsnummernüberlauf zu vermeiden
> TIP:  Halten Sie den Postmaster an und verwenden Sie ein Standalone-Backend, 
> um VACUUM in der Datenbank »postgres« auszuführen.

First of all, it would help you and most of the readers on this list, if
you have the error messages in english. There is a german mailinglist
too, if you want to ask in german.


> That's what I just did, but the problem persists. Whenever I issue a 
> 'vacuum', the number of transactions simply decreases. 

vacuum all databases, add the VERBOSE option to see, what actually
happens.


> I am absolutely lost about what to do now - and it's a puzzle how this could 
> have happened in the first place. I have configured autovaccum AND I run a 
> vacuum verbose analyze over all databases every single night. What do I do 
> now? Is there some alternative to reinit and going back to the last dump?

Are you using the database 'postgres' at all? And are you sure, that
you include all databases? Any error messages in the vacuum output?

Oh, and by the way: why do you have autovacuum and a manual vacuum run
every night plus the vacuum run with verbose?


> Urgent help would be very much appreciated. 

That's a bit late here ;-)


Kind regards

-- 
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors

-- 
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] --enable-thread-safety bug

[Tom Lane]

Steve Clark <[EMAIL PROTECTED]> writes:
> The return from malloc should be checked to make sure it succeeds - 
> right???

Probably, but what do you expect the code to do if it doesn't succeed?
This function seems not to have any defined error-return convention.

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] [postgis-users] how many min. floating-points?

[John Smith]

On Fri, Mar 21, 2008 at 4:24 PM, Andrej Ricnik-Bay
<[EMAIL PROTECTED]> wrote:
> On 22/03/2008, John Smith <[EMAIL PROTECTED]> wrote:
> >  > > please don't cross-post my cross-post. if i wanted to post it to the
> >  > > postgresql list, i would have ;)
>
> >  > That seems to be quite a silly request, considering you were asking for
> >  > assistance on public lists.
>
> > no seriously! if i wanted to post it to the postgresql list, i would
> >  have. thanks but no thanks.
> >  jzs
>
> You did.  I can't see what would make you think you hadn't.
>
> Here's the relevant header part from you original message:

no he dragged my subsequent posts soley to the postgis list to this
list- it is silly for someone who objects to cross-post to cross-post
an objectionable cross-post! have a good weekend, goodbye!
jzs

-- 
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] Transaction wraparound problem with database postgres

[Joshua D. Drake]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Fri, 21 Mar 2008 21:50:57 +0100
"Markus Wollny" <[EMAIL PROTECTED]> wrote:

> That's what I just did, but the problem persists. Whenever I issue a
> 'vacuum', the number of transactions simply decreases. 
> 
> This is PostgreSQL 8.2.4 on x86_64-unknown-linux-gnu, compiled by GCC
> gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
> 
> I am absolutely lost about what to do now - and it's a puzzle how
> this could have happened in the first place. I have configured
> autovaccum AND I run a vacuum verbose analyze over all databases
> every single night. What do I do now? Is there some alternative to
> reinit and going back to the last dump?
> 
> Urgent help would be very much appreciated. 
> 

Vacuum every database. (template1,postgres too). This could happen if
you have long running transactions that are not allow VACUUM to
actually work.

Sincerely,

Joshua D. Drake


> Kind regards
> 
>Markus
> 
> 
> 
> Computec Media AG
> Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)
> Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Niels
> Herrmann Vorsitzender des Aufsichtsrates: Jürg Marquard 
> Umsatzsteuer-Identifikationsnummer: DE 812 575 276
> 
> 


- -- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH5CcAATb/zqfZUUQRAl7CAJ9QGYJVqNVfHFgjVyCBswp1+d8kJgCfe+io
5d28sM4Gw4OkSBh/+U4jMDI=
=iBx9
-END PGP SIGNATURE-

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

[GENERAL] Transaction wraparound problem with database postgres

[Markus Wollny]

Hi!
 
My database cluster has just stopped working. I get the following message:
psql: FATAL:  Datenbank nimmt keine Befehle an, um Datenverlust in Datenbank 
»postgres« wegen Transaktionsnummernüberlauf zu vermeiden
TIP:  Halten Sie den Postmaster an und verwenden Sie ein Standalone-Backend, um 
VACUUM in der Datenbank »postgres« auszuführen.
 
I did as suggested, stopped tzhe postmaster and started a single backend on 
database 'postgres'; I issued a "VACCUM" there.
su postgres -c "/opt/pgsql/bin/postgres --single -D /var/lib/pgsql/data 
postgres" 

backend> VACUUM

However, this doesn't seem to help - I receive lots and lots of messages like 
this:

<2008-03-21 21:43:27 CET - 11845: @>WARNUNG:  Datenbank »postgres« muss 
innerhalb von 4294805194 Transaktionen gevacuumt werden
<2008-03-21 21:43:27 CET - 11845: @>TIPP:  Um ein Abschalten der Datenbank zu 
vermeiden, führen Sie in »postgres« ein VACUUM über die komplette Datenbank aus.

i.e. "database 'postgres' must be vacuumed within 4294805194 transactions."

That's what I just did, but the problem persists. Whenever I issue a 'vacuum', 
the number of transactions simply decreases. 

This is PostgreSQL 8.2.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 
4.1.2 20061115 (prerelease) (Debian 4.1.1-21)

I am absolutely lost about what to do now - and it's a puzzle how this could 
have happened in the first place. I have configured autovaccum AND I run a 
vacuum verbose analyze over all databases every single night. What do I do now? 
Is there some alternative to reinit and going back to the last dump?

Urgent help would be very much appreciated. 

Kind regards

   Markus



Computec Media AG
Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)
Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Niels Herrmann
Vorsitzender des Aufsichtsrates: Jürg Marquard 
Umsatzsteuer-Identifikationsnummer: DE 812 575 276

Re: [GENERAL] Transaction wraparound problem with database postgres

[Markus Wollny]

Hi!
 
Just some more info, hoping that it helps with a diagnosis:
 
 1: datname (typeid = 19, len = 64, typmod = -1, byval = f)
 2: age (typeid = 23, len = 4, typmod = -1, byval = t)
 3: datfrozenxid(typeid = 28, len = 4, typmod = -1, byval = t)

 1: datname = "postgres"(typeid = 19, len = 64, typmod = -1, 
byval = f)
 2: age = "-2147321465" (typeid = 23, len = 4, typmod = -1, byval = t)
 3: datfrozenxid = "1835116837" (typeid = 28, len = 4, typmod = -1, 
byval = t)

Then I issue a vacuum:
1: datname (typeid = 19, len = 64, typmod = -1, byval = f)
 2: age (typeid = 23, len = 4, typmod = -1, byval = t)
 3: datfrozenxid(typeid = 28, len = 4, typmod = -1, byval = t)

 1: datname = "postgres"(typeid = 19, len = 64, typmod = -1, 
byval = f)
 2: age = "-2147321383" (typeid = 23, len = 4, typmod = -1, byval = t)
 3: datfrozenxid = "1835116837" (typeid = 28, len = 4, typmod = -1, 
byval = t)
 
It worries me, that 'age' is negative.
 
Kind regards
 
   Markus




Von: Markus Wollny
Gesendet: Fr 21.03.2008 21:50
An: pgsql-general@postgresql.org
Betreff: Transaction wraparound problem with database postgres


Hi!
 
My database cluster has just stopped working. I get the following message:
psql: FATAL:  Datenbank nimmt keine Befehle an, um Datenverlust in Datenbank 
»postgres« wegen Transaktionsnummernüberlauf zu vermeiden
TIP:  Halten Sie den Postmaster an und verwenden Sie ein Standalone-Backend, um 
VACUUM in der Datenbank »postgres« auszuführen.
 
I did as suggested, stopped tzhe postmaster and started a single backend on 
database 'postgres'; I issued a "VACCUM" there.
su postgres -c "/opt/pgsql/bin/postgres --single -D /var/lib/pgsql/data 
postgres" 

backend> VACUUM

However, this doesn't seem to help - I receive lots and lots of messages like 
this:

<2008-03-21 21:43:27 CET - 11845: @>WARNUNG:  Datenbank »postgres« muss 
innerhalb von 4294805194 Transaktionen gevacuumt werden
<2008-03-21 21:43:27 CET - 11845: @>TIPP:  Um ein Abschalten der Datenbank zu 
vermeiden, führen Sie in »postgres« ein VACUUM über die komplette Datenbank aus.

i.e. "database 'postgres' must be vacuumed within 4294805194 transactions."

That's what I just did, but the problem persists. Whenever I issue a 'vacuum', 
the number of transactions simply decreases. 

This is PostgreSQL 8.2.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 
4.1.2 20061115 (prerelease) (Debian 4.1.1-21)

I am absolutely lost about what to do now - and it's a puzzle how this could 
have happened in the first place. I have configured autovaccum AND I run a 
vacuum verbose analyze over all databases every single night. What do I do now? 
Is there some alternative to reinit and going back to the last dump?

Urgent help would be very much appreciated. 

Kind regards

   Markus



Computec Media AG
Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)
Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Niels Herrmann
Vorsitzender des Aufsichtsrates: Jürg Marquard 
Umsatzsteuer-Identifikationsnummer: DE 812 575 276

[GENERAL] MySQL to Postgres question

[Edward Blake]

The table I have in MySQL is similar to below:

 0 SET FOREIGN_KEY_CHECKS=0;
 1 CREATE TABLE products (
 2 product_id integer(11) not null auto_increment,
 3 product_name varchar(255) not null,
 4 product_descrition varchar(255) not null,
 5 class_id integer(11) not null,
 6 subclass_id integer(11) not null,
 7 department_id integer(11) not null
 8 PRIMARY KEY (product_id),
 9 KEY class_id (class_id),
10 KEY subclass_id (subclass_id),
11 KEY department_id (department_id)
12 );

When I try and rewrite it as a Postgres statement (below), it fails at line
9.
 0 SET CONSTRAINTS ALL DEFERRED;
 1 CREATE TABLE products (
 2  product_id serial[11] not null,
 3  product_name varchar[255] not null,
 4  product_descrition varchar[255] not null,
 5  class_id integer[11] not null,
 6  subclass_id integer[11] not null,
 7  department_id integer[11] not null
 8  PRIMARY KEY (product_id),
 9  KEY class_id (class_id),
10  KEY subclass_id (subclass_id),
11  KEY department_id (department_id)
12 );

Any ideas?

Re: [GENERAL] MySQL to Postgres question

[Paul Boddie]

On 21 Mar, 17:15, [EMAIL PROTECTED] ("Edward Blake") wrote:
>
> When I try and rewrite it as a Postgres statement (below), it fails at line
> 9.
>  0 SET CONSTRAINTS ALL DEFERRED;
>  1 CREATE TABLE products (
>  2  product_id serial[11] not null,
>  3  product_name varchar[255] not null,
>  4  product_descrition varchar[255] not null,
>  5  class_id integer[11] not null,
>  6  subclass_id integer[11] not null,
>  7  department_id integer[11] not null
>  8  PRIMARY KEY (product_id),
>  9  KEY class_id (class_id),

Isn't KEY a MySQL shorthand for creating an index within the table
declaration. Why not create the index afterwards using CREATE INDEX
instead?

> 10  KEY subclass_id (subclass_id),
> 11  KEY department_id (department_id)
> 12 );
>
> Any ideas?

Yes, just decouple the index declarations from the table declaration.
There are benefits to doing this, too, such as being able to populate
tables more rapidly before the indexes are added - a technique which
appears to be useful for certain kinds of applications.

Paul

-- 
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] [postgis-users] how many min. floating-points?

[Andrej Ricnik-Bay]

On 22/03/2008, John Smith <[EMAIL PROTECTED]> wrote:
>  > > please don't cross-post my cross-post. if i wanted to post it to the
>  > > postgresql list, i would have ;)

>  > That seems to be quite a silly request, considering you were asking for
>  > assistance on public lists.

> no seriously! if i wanted to post it to the postgresql list, i would
>  have. thanks but no thanks.
>  jzs

You did.  I can't see what would make you think you hadn't.

Here's the relevant header part from you original message:
8<8<8<8
Date: Thu, 20 Mar 2008 14:02:12 -0400
From: "John Smith" <[EMAIL PROTECTED]>
To: "PostgreSQL General" 
Subject: [GENERAL] [postgis-users] how many min. floating-points?
Cc: "PostGIS Users Discussion" <[EMAIL PROTECTED]>
8<8<8<8

[GENERAL] --enable-thread-safety bug

[Steve Clark]

Hello List,

I am running 8.3.1 on FreeBSD 6.2 patch-7.

The ports for Freebsd turn on --enable-thread-safety during configure 
of pg.


When running my app after some time I have been getting a core dump - 
sig 11.


#0  0x28333b96 in memcpy () from /lib/libc.so.6
(gdb) bt
#0  0x28333b96 in memcpy () from /lib/libc.so.6
#1  0x280d0122 in ecpg_init_sqlca (sqlca=0x0) at misc.c:100
#2  0x280d0264 in ECPGget_sqlca () at misc.c:145
#3  0x280d056c in ecpg_log (
format=0x280d1d78 "free_params line %d: parameter %d = %s\n") at 
misc.c:243
#4  0x280c9758 in free_params (paramValues=0x836fe00, nParams=104, 
print=1 '\001',

lineno=3303) at execute.c:1045
#5  0x280c9f08 in ecpg_execute (stmt=0xa726f00) at execute.c:1298
#6  0x280ca978 in ECPGdo (lineno=3303, compat=0, force_indicator=1,
connection_name=0x0, questionmarks=0 '\0', st=0,
query=0x806023c "update T_UNIT_STATUS_LOG set ip_address  =  $1 
:: inet   , last_ip_address  =  $2  :: inet   , unit_date  =  $3  :: 
timestamp with time zone  , unit_raw_time  =  $4  , status_date  = now 
() , unit_ac"...) at execute.c:1636

#7  0x08057a46 in UpdateTUSL (pCachedUnit=0x807b680, msg=0xbfbf8850 "",
p_threshold=80, p_actualIP=0xbfbfe880 "24.39.85.226")
at srm2_monitor_db.pgc:3303
#8  0x0804f174 in main (argc=3, argv=0xbfbf7fc0) at 
srm2_monitor_server.c:3265

(gdb) f 2
#2  0x280d0264 in ECPGget_sqlca () at misc.c:145
145 ecpg_init_sqlca(sqlca);
(gdb) p sqlca
$1 = (struct sqlca_t *) 0x0

in looking in the code in misc.c

I see:

struct sqlca_t *
ECPGget_sqlca(void)
{
#ifdef ENABLE_THREAD_SAFETY
struct sqlca_t *sqlca;

pthread_once(&sqlca_key_once, ecpg_sqlca_key_init);

sqlca = pthread_getspecific(sqlca_key);
if (sqlca == NULL)
{
sqlca = malloc(sizeof(struct sqlca_t));
^
ecpg_init_sqlca(sqlca);
pthread_setspecific(sqlca_key, sqlca);
}
return (sqlca);
#else
return (&sqlca);
#endif
}

The return from malloc should be checked to make sure it succeeds - 
right???


Steve

--
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] 8.3.0 upgrade

[Devrim GÜNDÜZ]

Hi,

On Fri, 2008-03-21 at 14:37 -0500, Adam Rich wrote:
> I have applications that depend on libpq.so.4
> 
> Where do I get that, if not compat-postgresql-libs-4-2 ??

I'll send you instructions for how to build custom compat package.

Regards,
-- 
Devrim GÜNDÜZ , RHCE
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] begin-end blocks in psql

[Rodrigo E. De León Plicet]

On 3/21/08, Gauthier, Dave <[EMAIL PROTECTED]> wrote:
> I can do it in a formal declaration of a procedure, and then execute the
> procedure. But is there a less formal way?

No. There are no anonymous blocks in PostgreSQL.

-- 
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] 8.3.0 upgrade

[Adam Rich]

> No, you need compat-3, not compat-4. For example:
> 
> [EMAIL PROTECTED] ~]# yum install php-pgsql 
> --> Processing Dependency: libpq.so.3 for package: php-pgsql

I have applications that depend on libpq.so.4

Where do I get that, if not compat-postgresql-libs-4-2 ??


-- 
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] 8.3.0 upgrade

[Devrim GÜNDÜZ]

Hi,

On Fri, 2008-03-21 at 14:13 -0500, Adam Rich wrote:
> I clicked on my OS (RHEL/CentOS 4 - x86) Then on "C" as you said,
> But the RPM list still only contains the compat-postgresql-libs-3-2
> package, when I'm looking for compat-postgresql-libs-4-2

No, you need compat-3, not compat-4. For example:

[EMAIL PROTECTED] ~]# yum install php-pgsql

--> Processing Dependency: libpq.so.3 for package: php-pgsql

Regards,
-- 
Devrim GÜNDÜZ , RHCE
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] Client-requested cast mode to emulate Pg8.2 on v8.3

[Tom Lane]

"Anton Melser" <[EMAIL PROTECTED]> writes:
> Anyway, maybe I spoke too soon :-(.

> ERROR: operator is not unique: integer || unknown

> I did, of course, not follow the instructions and just blinding
> applied them all, but from reading them it doesn't look like the issue
> here. Does this error mean there are too many operators or not enough?

Too many.  You might have to remove the anynonarray || text and
text || anynonarray operators if you're going to continue to rely
on implicit casts to text.

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] 8.3.0 upgrade

[Adam Rich]

> Pick your OS/Arch from this list, and click to it:
> 
> http://yum.pgsqlrpms.org/rpmchart.php
> 
> Then click to "C" at the top, and download the compat package.

Devrim,
I clicked on my OS (RHEL/CentOS 4 - x86) Then on "C" as you said,
But the RPM list still only contains the compat-postgresql-libs-3-2
package, when I'm looking for compat-postgresql-libs-4-2

Any other ideas?





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

[GENERAL] begin-end blocks in psql

[Gauthier, Dave]

How do you do a simple begin-end statement block inside of a psql
session?

 

For Example, I want to...

 

-  begin

- declare a variable x to be an integer.  Set it = 5

- declare s to be a varchar(8).  Set it to "foo"

- insert into mytable (theint,thestr) values (x.s);

-  end

 

I can do it in a formal declaration of a procedure, and then execute the
procedure.  But is there a less formal way?

 

Thanks

-dave

 

 

 

Re: [GENERAL] MySQL to Postgres question

[Adam Rich]

> > I am not sure about 8.3 but certainly earlier releases of PostgreSQL
> > would have specific dependency issues when a sequence was applied to
> a
> > a column after the fact, versus using the serial or bigserial
> > psuedo-types.

I'd like to point out that using pg_dump does in fact apply sequences
to columns after the fact. (at least in 8.3)  Columns lose their "serial"
designation after each backup/restore (and therefore during version
upgrades)

mydb=# create table foo(id serial, bar varchar);

NOTICE:  CREATE TABLE will create implicit sequence "foo_id_seq" for serial
column "foo.id"
CREATE TABLE

Then, pg_dump produces:

-bash-3.00$ pg_dump -s --table=foo mydb

CREATE TABLE foo (
id integer NOT NULL,
bar character varying
);

CREATE SEQUENCE foo_id_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER SEQUENCE foo_id_seq OWNED BY foo.id;
ALTER TABLE foo ALTER COLUMN id SET DEFAULT nextval('foo_id_seq'::regclass);




-- 
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] Cast character to boolean

[Edmund.Bacon]

Roberts, Jon wrote:

A case statement will work just fine:

select case when sub.col1 = 'y' then true else false end as
col1_boolean, sub.col1 from (select cast('y' as varchar) as col1) sub


Be aware of the danger here.  What happens if col1 is NULL?

--
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] Table size

[Webb Sprague]

>  I meant, "I did not know such facility exists"

When you use pgautodoc, it automatically grabs those comments and puts
them in the web page it crreates...  more coolness!

-- 
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] Client-requested cast mode to emulate Pg8.2 on v8.3

[Anton Melser]

>  > I have the suspicion that his mother is named Lois, his father is
>  > unknown and he has a sensitivity to Kryptonite. But that's just
>  > speculation of course...
>  >
>  > Alban Hertroys
>
>
> Superman married Lois, I hope that isn't his Mom's name.

I got that he was the *son* of Superman... and really, in which
episode does he marry Lois (I admit I am not a devotee...)? I thought
the whole point was the sexual tension between the two...

Anyway, maybe I spoke too soon :-(.

ERROR: operator is not unique: integer || unknown

I did, of course, not follow the instructions and just blinding
applied them all, but from reading them it doesn't look like the issue
here. Does this error mean there are too many operators or not enough?
Meaning another function + cast would solve it? Or maybe making the
function more complex (by taking into account more possible cases)?
Cheers
Anton

-- 
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] MySQL to Postgres question

[Tom Lane]

"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
>> Why

> I am not sure about 8.3 but certainly earlier releases of PostgreSQL
> would have specific dependency issues when a sequence was applied to a
> a column after the fact, versus using the serial or bigserial
> psuedo-types.

As of (I think) 8.2, you can use ALTER SEQUENCE OWNED BY to manage
the dependency.  In earlier releases it's true that you couldn't
exactly duplicate what SERIAL did (at least not without manual
catalog hacking), but now it truly is just a macro for things you
can do with SQL commands.

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] [postgis-users] how many min. floating-points?

[John Smith]

On Fri, Mar 21, 2008 at 10:17 AM, Colin Wetherbee <[EMAIL PROTECTED]> wrote:
> John Smith wrote:
> > On Thu, Mar 20, 2008 at 2:16 PM, Colin Wetherbee
> > <[EMAIL PROTECTED]> wrote:
> >> Please don't cross-post, especially since nobody on the PostGIS
> >> mailing list answered your previous question.
> >
> > please don't cross-post my cross-post. if i wanted to post it to the
> > postgresql list, i would have ;)
>
> That seems to be quite a silly request, considering you were asking for
> assistance on public lists.

no seriously! if i wanted to post it to the postgresql list, i would
have. thanks but no thanks.
jzs

-- 
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] PGSQL database size question

[Shane Ambler]

Dan99 wrote:

Hi,

I am currently brainstorming ideas for a new RIA that I am planing to
make.  In the RIA there would be many different sections which do
different things.  Some of these sections would require data from
other sections and some sections can sit on there own.  Knowing that
this RIA is meant to be easily to distribute and easy to resize,
should the RIA use multiple databases and few tables within each
database, or should there be only one database with lots of tables in
it?  I guess my question is, what is the point at which it is more
advantageous to use multiple databases?  One of the downfalls of using
multiple databases that I know of, is that it is much harder to
transfer/manipulate data between two tables in two different
databases.



You can have all your tables available and together whether they have 
data in them or not.


Having 10,000 tables in one db will affect performance a lot less than 
having 100 million rows in one table.


If you want to separate some of the data then you can use schema's, you 
will find it easier to access more than one schema in a single 
connection than using two connections to get the other data or using 
extra's like plproxy or dbilink to get to the data in another db.



It really gets down to your client design. Will it determine what tables 
are available before it runs a select or will it handle zero rows 
returned from a select?


Or will you have different clients (or modules) that access the 
different tables depending on what options are included into it?


Will this all be from your one site or will it be downloaded and setup 
on each client's server?



Then look to the future. As your site grows, will the data grow to the 
volume and amount of traffic that you will need to spread the load?

Will you have multiple servers with the same data?
Will you do this by having different tables on different servers?
Will you split data across servers? split by username?


The design of your schema definition is more dependant on what data you 
need to store and how it needs to be linked together as well as how your 
client will access it, than on the limits of number of tables that can 
be created in one db.




--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

--
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] Client-requested cast mode to emulate Pg8.2 on v8.3

[Joshua D. Drake]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Fri, 21 Mar 2008 18:13:27 +0100
Alban Hertroys <[EMAIL PROTECTED]> wrote:

> On Mar 21, 2008, at 5:58 PM, Anton Melser wrote:
> 
> > Tom the Champion strikes again!
> > Cheers
> > Anton
> 
> I have the suspicion that his mother is named Lois, his father is  
> unknown and he has a sensitivity to Kryptonite. But that's just  
> speculation of course...
> 
> Alban Hertroys

Superman married Lois, I hope that isn't his Mom's name.

Joshua D. Drake


- -- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH4+3kATb/zqfZUUQRAmLqAJwOEpP72iWgZ9ZaW2wKt2ozk9ayegCgky7j
ChRNSQDwQHMHks3xHDa+cFs=
=mRsX
-END PGP SIGNATURE-

-- 
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] Client-requested cast mode to emulate Pg8.2 on v8.3

[Alban Hertroys]

On Mar 21, 2008, at 5:58 PM, Anton Melser wrote:


Tom the Champion strikes again!
Cheers
Anton


I have the suspicion that his mother is named Lois, his father is  
unknown and he has a sensitivity to Kryptonite. But that's just  
speculation of course...


Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,47e3ecbe9784203213352!



--
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] MySQL to Postgres question

[Justin]

Joshua D. Drake wrote:


I am not sure about 8.3 but certainly earlier releases of PostgreSQL
would have specific dependency issues when a sequence was applied to a
a column after the fact, versus using the serial or bigserial
psuedo-types.

Sincerely,

Joshua D. Drake

- -- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/

United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

  
You still get an error if creating a table that specifies a sequence 
that does not exist yet.I like to control the name of the sequence, 
plus the starting values or change the incrementing values.


I might have read something wrong but using serial tells PostgreSQL to 
automatic transforms.


CREATE TABLE /|tablename|/ (
   /|colname|/ SERIAL

to 


CREATE SEQUENCE /|tablename|/_/|colname|/_seq;
CREATE TABLE /|tablename|/ (
   /|colname|/ integer NOT NULL DEFAULT 
nextval('/|tablename|/_/|colname|/_seq'));

I copied this from the help files.

Re: [GENERAL] Client-requested cast mode to emulate Pg8.2 on v8.3

[Anton Melser]

On 21/03/2008, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Anton Melser" <[EMAIL PROTECTED]> writes:
>  > ... But it is COMPLETELY out of the
>
> > question to redo the db abstraction layer, and without these implicit
>  > casts that is what will be needed. Is there REALLY no way to reenable
>  > it?
>
>
> http://people.planetpostgresql.org/peter/index.php?/archives/18-Readding-implicit-casts-in-PostgreSQL-8.3.html

Tom the Champion strikes again!
Cheers
Anton

-- 
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] MySQL to Postgres question

[Joshua D. Drake]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Fri, 21 Mar 2008 12:47:38 -0500
Justin <[EMAIL PROTECTED]> wrote:


> >   
> Why

I am not sure about 8.3 but certainly earlier releases of PostgreSQL
would have specific dependency issues when a sequence was applied to a
a column after the fact, versus using the serial or bigserial
psuedo-types.

Sincerely,

Joshua D. Drake

- -- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH4+khATb/zqfZUUQRAp+yAKCrIGYWojKFhuyFy3biKQKgxJQ1kwCfRbB2
oF5G5DKbyHWN62wlXRDKmUQ=
=bUNa
-END PGP SIGNATURE-

-- 
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] Table size

[Pavan Deolasee]

On Fri, Mar 21, 2008 at 10:25 PM, Pavan Deolasee
<[EMAIL PROTECTED]> wrote:

>
>  Oh cool.. I did not such facility exists.
>


I meant, "I did not know such facility exists"

Thanks,
Pavan


-- 
Pavan Deolasee
EnterpriseDB 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] Table size

[Pavan Deolasee]

On Fri, Mar 21, 2008 at 10:12 PM, Andreas Kretschmer
<[EMAIL PROTECTED]> wrote:

>
>  Comments on objects can set by:
>
>  comment on ... is 'comment';
>

Oh cool.. I did not such facility exists.

Thanks,
Pavan


-- 
Pavan Deolasee
EnterpriseDB 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] MySQL to Postgres question

[Joshua D. Drake]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Fri, 21 Mar 2008 12:38:49 -0500
Justin <[EMAIL PROTECTED]> wrote:

> > Any ideas?
> 
> Another way to do auto increment fields is create your own sequences.

I would not suggest that.

Sincerely,

Joshua D. Drake

- -- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH4+aZATb/zqfZUUQRAqKVAJ97RECRp6mQuDehzzI1sFmtzTg0zwCgh3yu
NrnoKXNupj6sfkjIu6wG8zw=
=lMPe
-END PGP SIGNATURE-

-- 
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] MySQL to Postgres question

[Justin]

Joshua D. Drake wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Fri, 21 Mar 2008 12:38:49 -0500
Justin <[EMAIL PROTECTED]> wrote:

  

Any ideas?
  

Another way to do auto increment fields is create your own sequences.



I would not suggest that.



  

Why

Re: [GENERAL] Client-requested cast mode to emulate Pg8.2 on v8.3

[Tom Lane]

"Anton Melser" <[EMAIL PROTECTED]> writes:
> ... But it is COMPLETELY out of the
> question to redo the db abstraction layer, and without these implicit
> casts that is what will be needed. Is there REALLY no way to reenable
> it?

http://people.planetpostgresql.org/peter/index.php?/archives/18-Readding-implicit-casts-in-PostgreSQL-8.3.html

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] Table size

[Andreas Kretschmer]

Pavan Deolasee <[EMAIL PROTECTED]> schrieb:

> On Fri, Mar 21, 2008 at 3:03 PM, lak <[EMAIL PROTECTED]> wrote:
> > I have two questions.
> >  How can I enter comments into a table? Where the comments are stored?
> >
> 
> What do you mean by comments in a table ?

Comments on a table or a column or on other objects. 

Comments on objects can set by:

comment on ... is 'comment';

Please read within psql the output from '\h comment' to learn more.
Comments are stored in pg_description:
http://www.postgresql.org/docs/current/interactive/catalog-pg-description.html

> 
> >  In  psql How can I know the size of a single table?
> >
> 
> Select pg_relation_size('mytable');

More about that:
http://andreas.scherbaum.la/blog/archives/282-table-size,-database-size.html


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."(unknow)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

-- 
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] MySQL to Postgres question

[Justin]

Edward Blake wrote:

The table I have in MySQL is similar to below:

 0 SET FOREIGN_KEY_CHECKS=0;
 1 CREATE TABLE products (
 2 product_id integer(11) not null auto_increment,
 3 product_name varchar(255) not null,
 4 product_descrition varchar(255) not null,
 5 class_id integer(11) not null,
 6 subclass_id integer(11) not null,
 7 department_id integer(11) not null
 8 PRIMARY KEY (product_id),
 9 KEY class_id (class_id),
10 KEY subclass_id (subclass_id),
11 KEY department_id (department_id)
12 );

When I try and rewrite it as a Postgres statement (below), it fails at 
line 9.

 0 SET CONSTRAINTS ALL DEFERRED;
 1 CREATE TABLE products (
 2  product_id serial[11] not null,
 3  product_name varchar[255] not null,
 4  product_descrition varchar[255] not null,
 5  class_id integer[11] not null,
 6  subclass_id integer[11] not null,
 7  department_id integer[11] not null
 8  PRIMARY KEY (product_id),
 9  KEY class_id (class_id),
10  KEY subclass_id (subclass_id),
11  KEY department_id (department_id)
12 );

Any ideas?


Another way to do auto increment fields is create your own sequences.

Also according to what i have read from the postgresql documents there 
is no performance difference between varchar and text. 


create sequence my_auto_increment
 INCREMENT 1
 START 1
 CACHE 1;

 CREATE TABLE products (
  product_id integer primary key  default 
nextval(('my_auto_increment'::text)::regclass),

product_name text not null,
 product_descrition text not null,
 class_id integer not null,
 subclass_id integer not null,
 department_id integer not null);





--
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] ecpg program getting stuck

[Steve Clark]

Tom Lane wrote:

Steve Clark <[EMAIL PROTECTED]> writes:


I have a program that worked fine in 7.4.19. I am in process of upgrading
to 8.3.1 and my program runs for a while and then hangs. I used gdb
to attach to the process and it shows the following backtrace which 
shows it

going into the libpq library and getting stuck.



Well, gdb is lying to you to some extent (you'd probably get a better
backtrace if you had built libpq with debug symbols), but I think it's
simply waiting for a query response.  Is the connected backend busy?

If the problem is that some query is taking way longer than you were
expecting, the first thought that comes to mind is "did you ANALYZE
your tables after reloading", and the second is "check for
configuration settings that you forgot to propagate into the new
installation".

regards, tom lane




Hi Tom,

I was testing with 8.2.6 before 8.3.0 came out and I don't think I saw 
this problem.
I recompiled with debugging turned on and I'll do a closer inspection 
when/if it happens

again.

Steve

--
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] MySQL to Postgres question

[Tom Lane]

"Edward Blake" <[EMAIL PROTECTED]> writes:
> When I try and rewrite it as a Postgres statement (below), it fails at line
> 9.
>  0 SET CONSTRAINTS ALL DEFERRED;

I don't think that does the same thing as mysql's foreign_key_checks = 0.

>  2  product_id serial[11] not null,

This is trying to create an array, it is not at all the same as integer(11).
All of your other uses of square brackets are wrong too.  The varchars
will be okay with (255) but you should just drop the (11)'s --- use
either plain integer or bigint depending on what range you need.

>  9  KEY class_id (class_id),

PG doesn't have this type of clause within CREATE TABLE.  To create
a non-unique index you need a separate CREATE INDEX statement, eg

CREATE INDEX products_class_id ON products(class_id);

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] deadlock error messages

[Craig Ringer]

dan chak wrote:


What would be really great would be to know what the other query is, 
as opposed to just the pid (not sure from this output if it's 4483 or 
29245).  Also not sure if this is the right list for this.  But 
potentially someone on here may have a good tip on debugging deadlocking?


Just logging the other query would be a nasty information disclosure 
problem.


Process blah waits for sharelock on transaction blah blocked by process blah
Context: sql statement "update passwords set password = 'kitty' where 
user = 'dummy'"
Blocking sql statement: "update passwords set password = 'woof' where 
user 'dummy'"


Ouch.

What could be more securely done, though, would be to issue a NOTICE on 
the backend on which the deadlocking transaction not killed is running 
that includes the backend pid and the problem statement. Your later log 
analysis could then match up the statements from the separate log 
records. Including the transaction IDs of both in both log lines would 
be nice too, as pids get reused.


Sound sane?

--
Craig Ringer

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

Re: [GENERAL] Table size

[Shane Ambler]

Pavan Deolasee wrote:

On Fri, Mar 21, 2008 at 3:03 PM, lak <[EMAIL PROTECTED]> wrote:

I have two questions.
 How can I enter comments into a table? Where the comments are stored?



What do you mean by comments in a table ?


I think what you are referring to is detailed in
http://www.postgresql.org/docs/8.3/interactive/sql-comment.html

The comments are stored in pg_description (and pg_shdescription) but you 
should use the comment command instead of manipulating it manually.



 In  psql How can I know the size of a single table?



Select pg_relation_size('mytable');


Thanks,
Pavan





--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

--
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] MySQL to Postgres question

[Andreas 'ads' Scherbaum]

Hello,

On Fri, 21 Mar 2008 12:15:05 -0400 Edward Blake wrote:

>  9  KEY class_id (class_id),
> 10  KEY subclass_id (subclass_id),
> 11  KEY department_id (department_id)

this should create an index, or?
You want to do this later, after table creation.


Kind regards

-- 
Andreas 'ads' Scherbaum
German PostgreSQL User Group

-- 
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] Table size

[Craig Ringer]

lak wrote:

I have two questions.
How can I enter comments into a table? Where the comments are stored?
  

Assuming you want comments on the table schema definitions, use COMMENT ON.

CREATE TABLE sometable (
-- definition
);

COMMENT ON TABLE sometable IS "This is a table";

If that's not what you're after, you might need to be more specific.

--
Craig Ringer


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

Re: [GENERAL] Table size

[Erik Jones]

On Mar 21, 2008, at 4:33 AM, lak wrote:


I have two questions.
How can I enter comments into a table? Where the comments are stored?


Comments are created with the COMMENT sql command and, in pg, are  
stored in pg_description.



In  psql How can I know the size of a single table?



pg_relation_size()

Often called as pg_size_pretty(pg_relation_size('some_table')) for  
more readable output.


Use pg_total_relation_size() for the size with indexes and toast tables.

Erik Jones

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

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




--
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] MySQL to Postgres question

[Rodrigo Gonzalez]

Edward Blake escribió:

The table I have in MySQL is similar to below:

 0 SET FOREIGN_KEY_CHECKS=0;
 1 CREATE TABLE products (
 2 product_id integer(11) not null auto_increment,
 3 product_name varchar(255) not null,
 4 product_descrition varchar(255) not null,
 5 class_id integer(11) not null,
 6 subclass_id integer(11) not null,
 7 department_id integer(11) not null
 8 PRIMARY KEY (product_id),
 9 KEY class_id (class_id),
10 KEY subclass_id (subclass_id),
11 KEY department_id (department_id)
12 );

When I try and rewrite it as a Postgres statement (below), it fails at 
line 9.

 0 SET CONSTRAINTS ALL DEFERRED;
 1 CREATE TABLE products (
 2  product_id serial[11] not null,
 3  product_name varchar[255] not null,
 4  product_descrition varchar[255] not null,
 5  class_id integer[11] not null,
 6  subclass_id integer[11] not null,
 7  department_id integer[11] not null
 8  PRIMARY KEY (product_id),
 9  KEY class_id (class_id),
10  KEY subclass_id (subclass_id),
11  KEY department_id (department_id)
12 );

Any ideas? 

CREATE TABLE products (
product_id serial not null,
product_name varchar(255) not null,
product_description varchar(255) not null,
class_id integer not null,
subclass_id integer not null,
department_id integer not null,
PRIMARY KEY (product_id)
);

CREATE INDEX idx_prod_class_id ON products (class_id);
CREATE INDEX idx_prod_subclass_id ON products (subclass_id);
CREATE INDEX idx_prod_department_id ON products (department_id);




smime.p7s
Description: S/MIME Cryptographic Signature

Re: [GENERAL] MySQL to Postgres question

[Joshua D. Drake]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Fri, 21 Mar 2008 12:15:05 -0400
"Edward Blake" <[EMAIL PROTECTED]> wrote:

> When I try and rewrite it as a Postgres statement (below), it fails
> at line 9.
>  0 SET CONSTRAINTS ALL DEFERRED;
>  1 CREATE TABLE products (
>  2  product_id serial[11] not null,
>  3  product_name varchar[255] not null,
>  4  product_descrition varchar[255] not null,
>  5  class_id integer[11] not null,
>  6  subclass_id integer[11] not null,
>  7  department_id integer[11] not null
>  8  PRIMARY KEY (product_id),
>  9  KEY class_id (class_id),
> 10  KEY subclass_id (subclass_id),
> 11  KEY department_id (department_id)
> 12 );
> 
> Any ideas?

http://www.postgresql.org/docs/8.3/static/sql-createtable.html

I have no idea what KEY means in MySQL. Is it supposed to create an
INDEX? If so, you will need to create the indexes (not including the
PRIMARY KEY) after you create the table.

And just a quick editor view, you spelled description incorrectly in
product_descrition and your product_id is your primary key so you don't
have to set it NOT NULL.

Joshua D. Drake



- -- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH4+GiATb/zqfZUUQRAqnnAJ424OFzGg23QFyKEy+MuiAVii02MQCfZL6Z
grPtt4bz9bwTcQYBgiuPTQM=
=U4S5
-END PGP SIGNATURE-

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

[GENERAL] MySQL to Postgres question

[Edward Blake]

The table I have in MySQL is similar to below:

 0 SET FOREIGN_KEY_CHECKS=0;
 1 CREATE TABLE products (
 2 product_id integer(11) not null auto_increment,
 3 product_name varchar(255) not null,
 4 product_descrition varchar(255) not null,
 5 class_id integer(11) not null,
 6 subclass_id integer(11) not null,
 7 department_id integer(11) not null
 8 PRIMARY KEY (product_id),
 9 KEY class_id (class_id),
10 KEY subclass_id (subclass_id),
11 KEY department_id (department_id)
12 );

When I try and rewrite it as a Postgres statement (below), it fails at line
9.
 0 SET CONSTRAINTS ALL DEFERRED;
 1 CREATE TABLE products (
 2  product_id serial[11] not null,
 3  product_name varchar[255] not null,
 4  product_descrition varchar[255] not null,
 5  class_id integer[11] not null,
 6  subclass_id integer[11] not null,
 7  department_id integer[11] not null
 8  PRIMARY KEY (product_id),
 9  KEY class_id (class_id),
10  KEY subclass_id (subclass_id),
11  KEY department_id (department_id)
12 );

Any ideas?

Re: [GENERAL] ecpg program getting stuck

[Tom Lane]

Steve Clark <[EMAIL PROTECTED]> writes:
> I have a program that worked fine in 7.4.19. I am in process of upgrading
> to 8.3.1 and my program runs for a while and then hangs. I used gdb
> to attach to the process and it shows the following backtrace which 
> shows it
> going into the libpq library and getting stuck.

Well, gdb is lying to you to some extent (you'd probably get a better
backtrace if you had built libpq with debug symbols), but I think it's
simply waiting for a query response.  Is the connected backend busy?

If the problem is that some query is taking way longer than you were
expecting, the first thought that comes to mind is "did you ANALYZE
your tables after reloading", and the second is "check for
configuration settings that you forgot to propagate into the new
installation".

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] Client-requested cast mode to emulate Pg8.2 on v8.3

[Anton Melser]

>  >  - Is there a way to turn it back to the old behaviour with a
>  >warning going to the logs?
>
>
> No.
>
>
>  >  - Is there a way to get v8.2.x to warn on the dubious casts
>  >so we can tighten the application side while on v8.2?
>
>
> Seems to me the easiest way would be to try it out on an 8.3
>  installation and exercise each query once. There may be a better way
>  but I don't know it...

Hi,
This seems like it is one of the most frustrating (for me) decisions
that has ever been made by the postgres developers...
My situation is the following :
I inherited an application based on a dead project (byline, and don't
even mention aplaws, it's about as alive a zombie from Resident
Evil... it moves, but it ain't alive!) and we currently use postgres
8.1. The performance sucks, and there are several things in 8.3 that
are very interesting, notably synchronous_commit, plus all the
perfermance goodies since 8.1. But it is COMPLETELY out of the
question to redo the db abstraction layer, and without these implicit
casts that is what will be needed. Is there REALLY no way to reenable
it?
I fully realise and respect the logic in doing this but not having a
fallback (even if it means recompiling from source) is painful!
Am I really stuck with pre-8.3?
Cheers
Anton

-- 
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] Table size

[Pavan Deolasee]

On Fri, Mar 21, 2008 at 3:03 PM, lak <[EMAIL PROTECTED]> wrote:
> I have two questions.
>  How can I enter comments into a table? Where the comments are stored?
>

What do you mean by comments in a table ?

>  In  psql How can I know the size of a single table?
>

Select pg_relation_size('mytable');


Thanks,
Pavan


-- 
Pavan Deolasee
EnterpriseDB 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] Cast character to boolean

[Roberts, Jon]

A case statement will work just fine:

select case when sub.col1 = 'y' then true else false end as
col1_boolean, sub.col1 from (select cast('y' as varchar) as col1) sub



Jon
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Gordon
> Sent: Tuesday, March 18, 2008 10:18 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Cast character to boolean
> 
> I'm currently refactoring a database that somebody else designed.
> When the database was designed he used character columns with a length
> of 1 char to represent some values that really should have been
> represented as booleans.  He used 'y' for true and 'n' for false.
> 
> I want to cast these columns into the correct type, because you could
> in theory set the columns in question to any single character value.
> I don't seem to be able to do so, however, the database keeps claiming
> that the cast cannot be done.
> 
> I tried casting the columns in question to character varying and then
> changing all the 'y's to 'TRUE's, and all the 'n's to 'FALSE's. This
> wasn't a problem.  But casting from this format to boolean still gives
> an error.
> 
> Does anybody know how to do this?
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

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

[GENERAL] Running function automatically on (unclean) disconnect

[Stumo]

Hi all.

I'd like to get a function to run automatically on the server when a
postgresql client disconnects (either all disconnections, or just
unclean ones). This is to do some cleanup that can't be done with
transactions easily from what I can tell. Is it possible?

To give you an idea of why I need it, here's an outline of what I'm
doing:
1 server, several clients.
Each client retrieves an unprocessed record from the server, marks
that it's processing that record, does some processing (this will take
some time), and adds some extra data to the server based on the
processing.

Because I want several clients to be able to do this processing, I
need to mark which records are currently being processed - e.g. set a
status flag to 'processing'

For clients that complete successfully, as part of the transaction
they will change the status flag to 'processed' and move on.

However, if a client disconnects (which, because of the system I am
using, is relatively likely) then the record will still be marked as
processing even though nothing is processing it.

Do I have any alternatives? The best I've come up with is that each
client takes an advisory lock based on the row they're working on, and
every so often a program checks to see if there are any records
labelled processing that don't have an advisory lock (as these are
released on disconnection). But the polling seems more awkward than
just having the cleanup done whenever the server times out an unclean
disconnection.

Thanks all

Stuart Moore

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

[GENERAL] ecpg program getting stuck

[Steve Clark]

Hello List

I have a program that worked fine in 7.4.19. I am in process of upgrading
to 8.3.1 and my program runs for a while and then hangs. I used gdb
to attach to the process and it shows the following backtrace which 
shows it

going into the libpq library and getting stuck. Anybody have any ideas on
how to proceed?

I don't see anything in the postgres error log file.

(gdb) bt
#0  0x282e2de3 in poll () from /lib/libc.so.6
#1  0x280af646 in pqPutMsgStart () from /usr/X11R6/lib/libpq.so.5
#2  0x280afa0b in pqWaitTimed () from /usr/X11R6/lib/libpq.so.5
#3  0x280afa65 in pqWait () from /usr/X11R6/lib/libpq.so.5
#4  0x280ad595 in PQgetResult () from /usr/X11R6/lib/libpq.so.5
#5  0x280ad6b3 in PQgetResult () from /usr/X11R6/lib/libpq.so.5
#6  0x280c3655 in ECPGdo () from /usr/X11R6/lib/libecpg.so.6
#7  0x08054378 in updateTCTS (pUnit=0x8081800, pMsg=0xbfbf8850 "")
at srm2_monitor_db.pgc:2313
#8  0x0804f8ae in main (argc=3, argv=0xbfbf7fc0) at 
srm2_monitor_server.c:3356


Thanks,
Steve

--
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] Cast character to boolean

[Gordon]

On Mar 19, 5:52 pm, [EMAIL PROTECTED] wrote:
> On Mar 18, 9:18 am, Gordon <[EMAIL PROTECTED]> wrote:
>
>
>
> > I'm currently refactoring a database that somebody else designed.
> > When the database was designed he used character columns with a length
> > of 1 char to represent some values that really should have been
> > represented as booleans.  He used 'y' for true and 'n' for false.
>
> > I want to cast these columns into the correct type, because you could
> > in theory set the columns in question to any single character value.
> > I don't seem to be able to do so, however, the database keeps claiming
> > that the cast cannot be done.
>
> > I tried casting the columns in question to character varying and then
> > changing all the 'y's to 'TRUE's, and all the 'n's to 'FALSE's. This
> > wasn't a problem.  But casting from this format to boolean still gives
> > an error.
>
> > Does anybody know how to do this?
>
> Have yout tried
> ALTER TABLE foo ALTER col TYPE boolean USING CASE WHEN col = 'y' THEN
> true WHEN column = 'n' then FALSE END;

I did find a solution in the end but it was nothing like as elegant as
yours.  In the end I created two new boolean columns, updated the
values in the new columns depending on the values in the old columns,
dropped the old columns and renamed the new boolean columns to the
names of the deleted columns.  As you cn imagine, not a fun
procedure.  I'll keep this post bookmarked though if I ever have to do
anything like that again.

-- 
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] Cast character to boolean

[elbacon]

On Mar 18, 9:18 am, Gordon <[EMAIL PROTECTED]> wrote:
> I'm currently refactoring a database that somebody else designed.
> When the database was designed he used character columns with a length
> of 1 char to represent some values that really should have been
> represented as booleans.  He used 'y' for true and 'n' for false.
>
> I want to cast these columns into the correct type, because you could
> in theory set the columns in question to any single character value.
> I don't seem to be able to do so, however, the database keeps claiming
> that the cast cannot be done.
>
> I tried casting the columns in question to character varying and then
> changing all the 'y's to 'TRUE's, and all the 'n's to 'FALSE's. This
> wasn't a problem.  But casting from this format to boolean still gives
> an error.
>
> Does anybody know how to do this?

Have yout tried
ALTER TABLE foo ALTER col TYPE boolean USING CASE WHEN col = 'y' THEN
true WHEN column = 'n' then FALSE END;

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

[GENERAL] Cast character to boolean

[Gordon]

I'm currently refactoring a database that somebody else designed.
When the database was designed he used character columns with a length
of 1 char to represent some values that really should have been
represented as booleans.  He used 'y' for true and 'n' for false.

I want to cast these columns into the correct type, because you could
in theory set the columns in question to any single character value.
I don't seem to be able to do so, however, the database keeps claiming
that the cast cannot be done.

I tried casting the columns in question to character varying and then
changing all the 'y's to 'TRUE's, and all the 'n's to 'FALSE's. This
wasn't a problem.  But casting from this format to boolean still gives
an error.

Does anybody know how to do this?

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

[GENERAL] Table size

[lak]

I have two questions.
How can I enter comments into a table? Where the comments are stored?

In  psql How can I know the size of a single table?

If know pls replay.
Thanks i advance



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

[GENERAL] deadlock error messages

[dan chak]

When there's a deadlock detected, the error message recorded in the  
log prints out the query being aborted, and the process id of the  
other transaction involved in the deadlock.  It would be great if more  
context was printed for the other query (e.g. the query itself), as  
opposed to just the process id.  By the time the logs are parsed, the  
process id isn't very useful.  Example output we can parse  out:


PGError: ERROR:  deadlock detected
DETAIL:  Process 29245 waits for ShareLock on transaction 136665841;  
blocked by process 4483.
Process 4483 waits for ShareLock on transaction 136665996; blocked by  
process 29245.
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."forms" x WHERE  
"id" = $1 FOR SHARE OF x"
SQL statement "INSERT INTO form_qualifications (arrival_id,  
program_id, simple_form_id, associated_form_id, position, site_id,  
created_at) select  $1 ,  $2 [gs.ser],  $3 [gs.ser],  $4 ,  $5 ,  $6 ,  
now() from generate_series(1, array_upper( $3 , 1)) as gs(ser)"

PL/pgSQL function "insert_form_qualifications" line 2 at SQL statement
:
  select insert_form_qualifications(73404580,
 
array 
[9997,9998,11334,2034,2051,10006,2053,6088,8966,2054,8967,2055,2056,8977,8978,2058,2052,2065 
],
 
array 
[6991,6991,6991,6831,6831,6831,6831,6831,6831,6831,6831,6831,6831,6992,6831,6992,6831,6831 
],

1479,
2,
1)

What would be really great would be to know what the other query is,  
as opposed to just the pid (not sure from this output if it's 4483 or  
29245).  Also not sure if this is the right list for this.  But  
potentially someone on here may have a good tip on debugging  
deadlocking?


Thanks,

Dan

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

[GENERAL] PGSQL database size question

[Dan99]

Hi,

I am currently brainstorming ideas for a new RIA that I am planing to
make.  In the RIA there would be many different sections which do
different things.  Some of these sections would require data from
other sections and some sections can sit on there own.  Knowing that
this RIA is meant to be easily to distribute and easy to resize,
should the RIA use multiple databases and few tables within each
database, or should there be only one database with lots of tables in
it?  I guess my question is, what is the point at which it is more
advantageous to use multiple databases?  One of the downfalls of using
multiple databases that I know of, is that it is much harder to
transfer/manipulate data between two tables in two different
databases.

Let me know what you think.

Thanks,
Daniel

-- 
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] Feature request/suggestion - CREATE SCHEMA LIKE

[ipajor]

On Mar 17, 4:01 pm, wstrzalka <[EMAIL PROTECTED]> wrote:
> Hi
>
>Features like CREATE DATABASE WITH TEMPLATE or CREATE TABLE LIKE
> are very usefull but it would be great to have such a feature on the
> mid-level too. I mean something CREATE SCHEMA LIKE that would copy all
> the template schema relations, etc...
>  What do you think about it ? Would it be hard to implement ? Is it
> worth the effort ?

Hey there,

I have been using scripts for a while, but it would be definitely
helpful and very nice to have those features. It would make my life
much easier.
If it s possible, please do it!
Thanks.

-- 
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] cast problem 8.3.1

[Steve Clark]

Charles Simard wrote:


|> 
|> postgres error log:

|> ERROR:  operator does not exist: text = integer
|> HINT:  No operator matches the given name and argument type(s). You 
|> might need to add explicit type casts.
|> STATEMENT:  update T_MON_DEVICE_STATUS set device_ip  =  $1  :: inet 
|>   , status  =  $2  :: integer  , status_date  = now () , 
|> last_event_log_no  = case  $3  when 0 then null else  $4  :: integer 
|> end  where unit_serial_no =  $5  :: text   and device_name = 
|>  $6  :: text
|> 



You're not casting your $3.



thanks Charles and Rodrigo - that fixed it.

Steve

--
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] [postgis-users] how many min. floating-points?

[Colin Wetherbee]

John Smith wrote:

On Thu, Mar 20, 2008 at 2:16 PM, Colin Wetherbee
<[EMAIL PROTECTED]> wrote:

Please don't cross-post, especially since nobody on the PostGIS
mailing list answered your previous question.


please don't cross-post my cross-post. if i wanted to post it to the 
postgresql list, i would have ;)


That seems to be quite a silly request, considering you were asking for 
assistance on public lists.


Regardless, what did you mean by "floating points"?

We'd like to help, but I don't think we're having much success 
interpreting your question.


Some more information about your actual problem would be helpful, too.

Colin

--
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] [postgis-users] how many min. floating-points?

[John Smith]

On Thu, Mar 20, 2008 at 2:16 PM, Colin Wetherbee <[EMAIL PROTECTED]> wrote:
> John Smith wrote:
> > guys,
>
> Please don't cross-post, especially since nobody on the PostGIS mailing
> list answered your previous question.

On Thu, Mar 20, 2008 at 6:45 PM, Colin Wetherbee <[EMAIL PROTECTED]> wrote:
> Dunno about that.  On the PostGIS list, he said:

please don't cross-post my cross-post. if i wanted to post it to the
postgresql list, i would have ;)
jzs

-- 
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] dynamically generated SQL and planner/performance

[Ivan Sergio Borgonovo]

On Fri, 21 Mar 2008 12:15:19 +0100
"Albe Laurenz" <[EMAIL PROTECTED]> wrote:

> The execution plan of dynamic queries from PL/pgSQL will not be
> cached; the query will be prepared at execution time every time you
> execute it.

Pardon my ignorance but I really have a very vague idea of what the
planner does and how it works.

Does the planner "compose" plans or does it have one plan for each
statement?

if I've nested functions what does the planner?

When I do select * from func(); explain analyse doesn't say much.

> The plan for executing "select * from FBuilder()" will not be
> affected by the SQL statements you execute from within FBuilder().
> All you can do to hint at the planner that calling FBuilder() will
> be expensive is (from version 8.3 on) to include a COST clause in
> the CREATE FUNCTION statement.

> Unless the dynamic statements are complicated or are called very
> often, I would not worry too much about the additional cost of
> preparing the statement.

The generated statement is very similar to the one I posted.
I just have to call the right function according to what I find in a
table.

The work flow is something like:
- user write in a table a choice (int)
- each int is associated with a plpgsql function
- the statement is assembled, executed and the the specific function
result is returned in a way similar to the one of FBuilder

the dynamically assembled query is very similar to the one shown in
FBuilder.
There is a simple select that fetch the function name and an argument.
It is a bit more complicated than the one shown since the arguments
are taken from different tables and there is a where clause.

But nothing far from a:

[A]
select a.Name, b.arg from FuncTable a join Args b on b.id=a.id where
b.val>7;

then the statement is really as:

[B]
statement := ' select * from ' || _Name || '(' || _arg || ')';

Just I fetch a couple more args.

and then I actually do a

[C]
execute statement into res;
return;

Now I'd like what I'm loosing using dynamic queries.

Can I say that:
- I won't lose anything in step [A]
- I won't lose anything in step [B]
- I'll lose something in step [C]
- I won't lose anything in the actual execution of function _Name(...)

My main concern is _Name(...) _Name[s] functions are all made with
static statements but they are relatively critical since most use
aggregates, a bunch of join and sub-queries... so no matter if the
'select * from ' || _Name ...
is not optimised I'd like to know if the execution of what's inside
each _Name function will be.

thanks

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


-- 
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] Get index information from information_schema?

[Albe Laurenz]

Dann Corbit wrote:
> > > I need a method of extracting information about indexes of any table
> > > from information_schema.
> > >
> > > Have you any suggestions?
> > 
> > I am afraid that indexes are not covered by information_schema.
> > 
> > You'd have to dig into pg_catalog.pg_index for this.
> 
> Doesn't the PostgreSQL schema have the
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE view?
> 
> Or (conversely) are indexes not stored as constraints?

You will find constraints in information_schema, but not indexes.
In general, indexes are no constraints.

Some constraints are implemented as indexes (primary key, unique),
but that's a different story.

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] dynamically generated SQL and planner/performance

[Albe Laurenz]

Ivan Sergio Borgonovo wrote:
> I've a lot of code that should call different functions according to
> values in tables.
> 
> something like:
> 
> 
> create table FuncName(Name varchar(10), arg int);
> 
> insert into FuncName values('ciro',5);
> insert into FuncName values('pino',7);
> insert into FuncName values('nano',11);
> 
> create or replace function ciro(arg int, out res int) as
> $$
> begin
>   res:=arg;
>   return;
> end;
> $$ language plpgsql;
> 
> create or replace function pino(arg int, out res int) as
> $$
> begin
>   res:=arg*2;
>   return;
> end;
> $$ language plpgsql;
> 
> create or replace function nano(arg int, out res int) as
> $$
> begin
>   res:=arg*4;
>   return;
> end;
> $$ language plpgsql;
> 
> 
> create or replace function FBuilder(out res int) as
> $$
> declare
>   statement varchar(256);
>   _Name varchar(10);
>   _arg int;
> begin
>   select into _Name, _arg Name, arg from FuncName order by random();
>   statement := ' select * from ' || _Name || '(' || _arg || ')';
>   execute statement into res;
>   return;
> end;
> $$ language plpgsql;
> 
> select * from FBuilder();
> 
> I don't understand which impact will have on the planner, caching
> etc... generating the statement dynamically.
> 
> Will the execution of ciro, pino e nano be affected?
> Or will just the plan for executing FBuilder statement be affected?

The execution plan of dynamic queries from PL/pgSQL will not be cached;
the query will be prepared at execution time every time you execute it.

The plan for executing "select * from FBuilder()" will not be affected
by the SQL statements you execute from within FBuilder().
All you can do to hint at the planner that calling FBuilder() will be
expensive is (from version 8.3 on) to include a COST clause in the
CREATE FUNCTION statement.

Unless the dynamic statements are complicated or are called very often,
I would not worry too much about the additional cost of preparing the statement.

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] cursor manipulation

[Albe Laurenz]

Justin wrote:
> Cursor manipulation with select statements
> 
> This is something i did in foxpro and wonder if it is at all
> possible in pl/pgSQL.

Yes, it is. See
http://www.postgresql.org/docs/current/static/plpgsql-cursors.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