[ADMIN] Problem with PgTcl auditing function on trigger

[Glyn Astill]

Hi people,

I've tried posting on the general list about this, but I never get a
reply, so I'm trying here.

I have a function that is run each time an INSERT, DELETE or UPDATE
happens on a row and log into an audit table.

It is based on the info here:

http://www.alberton.info/postgresql_table_audit.html

We have a table Customers.CREDIT with a primary key "NUMBER",
"TRANSNO", "RECNUM".

I have a trigger as follows:

CREATE TRIGGER "tg_audit_credit"
AFTER INSERT OR DELETE OR UPDATE ON "Customers"."CREDIT"
FOR EACH ROW
EXECUTE PROCEDURE "log_to_audit_table" ();

This uses the attached tcl function which basically runs this for a
delete

spi_exec -array C "INSERT INTO audit_table(ts, usr, tbl, fld,
pk_name, pk_value, mod_type, old_val, new_val)
 VALUES (CURRENT_TIMESTAMP, '$tguser', '$tgname', '$modified_field',
'$pk_name', '$pk_value', '$TG_op', '$previous_value', NULL)"

The function works fine for this SQL statement

delete from "CREDIT" where "TRANSNO" < 11148188 AND "TRANSNO" >
11148180;

However if I try this one I get a syntax error.

delete from "CREDIT" where "RECNUM" < 2484907 AND "RECNUM" > 2484905;


The error is below. Do I need to escape my strings? And if so how do
I do this?

Thanks
Glyn


SEE=# delete from "CREDIT" where "RECNUM" < 2484907 AND "RECNUM" >
2484905;
ERROR:  syntax error at or near "S"
CONTEXT:  syntax error at or near "S"
while executing
"spi_exec -array C "INSERT INTO audit_table(ts, usr, tbl, fld,
pk_name, pk_value, mod_type, old_val, new_val)
VALUES (CURRENT_TIMESTAMP, '$tguser', '$t..."
("foreach" body line 5)
invoked from within
"foreach field $TG_relatts {
if {! [string equal -nocase [lindex [array get OLD $field] 0]
$pk_name]} {
  set modified_field [lindex [array get..."
("DELETE" arm line 11)
invoked from within
"switch $TG_op {
INSERT {

  #get PK value
  foreach field $TG_relatts {
if {[string equal -nocase [lindex [array get NEW $field] 0]
$pk_name]} {..."
(procedure "__PLTcl_proc_5667381_trigger_16644" line 23)
invoked from within
"__PLTcl_proc_5667381_trigger_16644 tg_audit_credit 16644 CREDIT
Customers {{} RECNUM TRANSNO NUMBER EXMON EXYEAR OLDTICK COACHES
VALUE POSTAGE DEPOSIT..."




  __
Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com


Audit_recnums2.tcl
Description: 1337167722-Audit_recnums2.tcl

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [ADMIN] Need some info on Postgresql

[Scott Marlowe]

On Jan 4, 2008 12:41 AM, Suresh Gupta VG <[EMAIL PROTECTED]> wrote:
>
> Hi Scott,
>
> Thanks for your reply and pls see my inline comments in red color.

> I am using Postgresql 7.4.2 version on Solaris.
>
>
>
> You need to update to 7.4.18 or whatever the last version was.  7.4.2
>
> has known data eating bugs, and if you value your data even a little,
>
> you should update.  this is a relatively painless update not requiring
>
> a dump / restore.
>
>
>
> Suresh : Can you pls tell is 7.4.2 is latest or 7.4.18 is latest? We will
> plan to upgrade to 8.2.5.

http://www.postgresql.org/ says the latest minor update to the 7.4
branch is 7.4.18.  You are missing over two years of bug fixes by
running 7.4.2.  updating to 7.4.2 will take minutes.

Migrating to 8.2.5 requires you to test your applications against
8.2.5 then dump and restore your database. Update 7.4 first.  Then
plan a migration.

> > There are number of tables
> > say about 30+ tables in our database. I started to "reindex" the tables
> > individually. "reindex table <>". All the queries executed
> > normally with less than 1 minute of duration. But one table is not
> > responding any thing even after 10 minutes. The details of the table are …
>
> I've had large tables take well over 10 minutes on 7.4 to reindex.
> That's not necessarily a sign of a problem.  How many rows does this
> table have? Do you get any kind of error message or the database just
> never returns.  Have you tried vacuum full on this table instead?  Do
> the logs say anything about the reindex operation getting an error
> etc???
>
>
> Suresh : This table contains only 5 columns and 5 rows of data. I don't get
> any error message instead it remains un answered, I am using "ctrl C" to
> cancel the command. I also had done the vacuum on this table, pls see the
> result below. Pls give the general path of log files available in the
> database. I don't have idea of this logs files of PGSQL.

If it's hanging with only 5 rows then you've likely that reindex can't
get some kind of lock it needs.  I'm not sure if 7.4 has the same
views as 8.2 so I'm not sure if pg_locks has the answer or not.  while
your reindex is trying to run, in another shell, in psql to your db,
type in

select * from pg_locks;

No idea where your log files are, they change based on how pgsql was installed.

> You would do well to consider a migration to 8.2.5 or 8.3 when it
> comes out of beta / RC status.  7.4.x is getting rather old, and the
> 8.x series (8.0, 8.1, 8.2, and now 8.3) have each had major
> improvements in speed.
>
> Suresh : We will plan to upgrade to 8.X. What is this beta / RC status, can
> you brief me pls?

It's due out this year, most likely before spring.

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

Re: [ADMIN] Need some info on Postgresql

[Scott Marlowe]

On Jan 4, 2008 4:40 AM, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> http://www.postgresql.org/ says the latest minor update to the 7.4
> branch is 7.4.18.  You are missing over two years of bug fixes by
> running 7.4.2.  updating to 7.4.2 will take minutes.

Correction, should be "updating to 7.4.18 will take minutes."

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

[ADMIN] invalid byte sequence for encoding "UNICODE":0xa1

[Popovici Alexandru]

Hello,

 For my database ( PostgreSQL 8) I made a backup (
using pgAdmin ). Now when I try to restore the data (
again using pgAdmin ) at some point the process is
aborted:

---
   pg_restore: ERROR: invalid byte sequence for
encoding "UNICODE":0xa1
   CONTEXT: COPY ...
   pg_restore: [archiver(db)] error returned by
PQendcopy: invalid byte sequence for encading
"UNICODE":0xa1
   CONTEXT: COPY ...
   pg_restore: *** aborted because of error
   Process returned exit code 1
---   

   If I delete the database and restore only the
schema I get this message:

---   
  "WARNING: errors ignored on restore:21
   Process returned exit code 1"
---
   
   The designer of database has told me that this last
message is OK, and this is the message which should I
get after restoring the schema and data.
   I have asked him why I can restore the data but
unfortunatelly he doesn't now where the problem is.
   The database is installed on a server with Windows
2003 .

Can you please help me?


  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


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

   http://archives.postgresql.org

Re: [ADMIN] Postgres 7.4 VACUUM FULL multiple AccessExclusiveLocks?

[Scott Marlowe]

On Jan 3, 2008 5:12 PM, Mike White <[EMAIL PROTECTED]> wrote:
> I'm performing a VACUUM FULL on a 7.4.7 db table, one that is not huge
> at any given time, but which has a lot of churn.  Just the kind of
> usage pattern that 7.4 loves.  :)  This is something that we have to do
> every 6 months or so, and it always takes a long time.

I wonder why you have to do it every 6 months...  Is this one of those
things where slowly the table bloats because fsm is too small or
vacuum is too infrequent?  Or is this a case of some kind of
fragmentation?  I've got plenty of heavily updated tables that USED to
live in a 7.4 and I never needed to run vacuum full on it, because
autovacuum (with slightly more aggressive settings than default) was
plenty on that db to keep the table clean.  So I'm wondering if
there's some preventative maintenance that would stop you needing to
do this.

> Today, however,
> I've noticed that the pg_lock table shows two AccessExclusiveLock
> entries that are completely identical.  Is this a sign of internal
> deadlock, or is this behavior expected during VACUUM FULL operation?

> Also, please don't tell me to upgrade.

OK.  I will tell you to update.  7.4.7 had many data eating bugs that
were fixed by 7.4.18.

> We're happily running 8.2 on
> some production machines, but I am tied to 7.4 on this particular box
> for reasons beyond my control.  :(

Been there, done that, got the t-shirt :-(

>
> =# select * from pg_locks where mode = 'AccessExclusiveLock';
>   relation  |  database  | transaction |  pid  |mode |
> granted
> ++-+---+-+-
>  2434930929 | 2434930890 | | 18356 | AccessExclusiveLock |
> t
>  2434930929 | 2434930890 | | 18356 | AccessExclusiveLock |
> t

I'm guessing that the richer view that exists in 8.2 might show
differences, but the simpler view in 7.4 doesn't.  You might try
adapting parts of the 8.2 view into 7.4 and seeing if that helps.

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

Re: [ADMIN] When does VACUUM FULL not clean out all deleted data?

[Joshua D. Drake]

James Cloos wrote:

"Tom" == Tom Lane <[EMAIL PROTECTED]> writes:



Why was the db using that extra five plus gigs?


Tom> Smells like a hadn't-been-vacuuming-often-enough problem.

Sorry, I forgot to mention:  autovacuum was enabled and I also ran
this script every night via cron:

#!/bin/bash
FULL=''
test $(date +%w) -eq 0 && FULL='FULL'
exec psql -U dbm -c "VACUUM ${FULL} VERBOSE ANALYZE" dbm

The app at least felt faster with the daily vacuum, even though
autovacuum was running.  I don't have a good explanation for that,
either.


Autovacuum by default won't do enough work for many production 
databases. You still have to configure it.


Joshua D. Drake


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

Re: [ADMIN] Need source for Solaris 9

[Usama Dar]

On Jan 4, 2008 4:24 PM, Suresh Gupta VG <[EMAIL PROTECTED]> wrote:

>  Hi Team,
>
>
>
> I am using 7.4.2 version of Postgresql on "*Solaris 9 9/04 s9s_u7wos_09
> SPARC*". I need to upgrade to pgsql-8.2.5 version. I could not get the
> source for the solaris-9 on the site(http://www.postgresql.org/download/).
> I could see only for Solaris-10. Can you please provide me the right link
> for the download of source for the Solaris-9.
>

There is no separate source for separate platforms, the same code is
compiled on all supported platforms. please go to
http://www.postgresql.org/ftp/source/v8.2.5/ download the
postgresql-8.2.5.tar.gzMake
sure you read the compilation instructions and FAQs for Solaris
distributed as part of the code.




-- 
Usama Munir Dar http://www.linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar

Re: [ADMIN] Need source for Solaris 9

[Scott Marlowe]

On Jan 4, 2008 5:24 AM, Suresh Gupta VG <[EMAIL PROTECTED]> wrote:
>
> Hi Team,
>
> I am using 7.4.2 version of Postgresql on "Solaris 9 9/04 s9s_u7wos_09
> SPARC". I need to upgrade to pgsql-8.2.5 version. I could not get the source
> for the solaris-9 on the site(http://www.postgresql.org/download/). I could
> see only for Solaris-10. Can you please provide me the right link for the
> download of source for the Solaris-9.

Well, I think you're confusing source with binary package.  The source
files will / should compile on any platform with a reasonable build
environment, including solaris 9.  A binary package for solaris 9 is
another issue.  I'm not sure where you would get that.  But you should
be able to download postgresql-8.2.5.tar.gz and compile that on
solaris, assuming you've got a decent build environment.

The source can be found here:

http://ftp9.us.postgresql.org/pub/mirrors/postgresql/v8.2.5/postgresql-8.2.5.tar.gz

(that's just one of the mirrors.)

Then read the INSTALL instructions in the file and come back if you
need more help.

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

[ADMIN] Need source for Solaris 9

[Suresh Gupta VG]

Hi Team, 

 

I am using 7.4.2 version of Postgresql on "Solaris 9 9/04 s9s_u7wos_09
SPARC". I need to upgrade to pgsql-8.2.5 version. I could not get the
source for the solaris-9 on the
site(http://www.postgresql.org/download/). I could see only for
Solaris-10. Can you please provide me the right link for the download of
source for the Solaris-9.

 

Regards,

G. V. Suresh Gupta


-

Innovative Technology Solutions(ITS), Zensar Technologies 

Zensar Knowledge Park, Plot#5, MIDC IT Tower, 

Kharadi, Off Nagar Road, Pune - 411014

Landline :  +91-20-66453471   | +91-9890898688 

Email :   [EMAIL PROTECTED]| website:  www.zensar.com
 

 

 



DISCLAIMER:
This email may contain confidential or privileged information for the intended 
recipient(s) and the views expressed in the same are not necessarily the views 
of Zensar Technologies Ltd. If you are not the intended recipient or have 
received this e-mail by error, its use is strictly prohibited, please delete 
the e-mail and notify the sender. Zensar Technologies Ltd. does not accept any 
liability for virus infected mails. 

Re: [ADMIN] Need some info on Postgresql

[Scott Marlowe]

On Jan 4, 2008 5:14 AM, Suresh Gupta VG <[EMAIL PROTECTED]> wrote:
>
> Hi Scott,
>
> Thanks for your reply.
>
> I got the result for the query select * from pg_locks; as below. Can you
> explain me the output please.
>
> relation | database | transaction |  pid  |  mode   | granted
>
> --+--+-+---+-+-
>   |  | 7784829 | 21222 | ExclusiveLock   | t
> 87441 |87343 | | 21173 | AccessShareLock | t
> 87441 |87343 | | 21044 | AccessShareLock | t
> 87441 |87343 | | 21209 | AccessShareLock | t
>   |  | 7782935 | 20942 | ExclusiveLock   | t
>   |  | 7784034 | 21106 | ExclusiveLock   | t
>   |  | 7784091 | 21112 | ExclusiveLock   | t
> 87441 |87343 | | 21197 | AccessShareLock | t
>   |  | 7783246 | 20989 | ExclusiveLock   | t
> 87441 |87343 | | 21081 | AccessShareLock | t
>   |  | 7783542 | 21034 | ExclusiveLock   | t
>   |  | 7782957 | 20944 | ExclusiveLock   | t
>   |  | 7785128 | 21268 | ExclusiveLock   | t
> 87441 |87343 | | 21228 | AccessShareLock | t
> 87441 |87343 | | 20971 | AccessShareLock | t
>   |  | 7783524 | 21032 | ExclusiveLock   | t
>   |  | 7784237 | 21136 | ExclusiveLock   | t
> 87441 |87343 | | 21063 | AccessShareLock | t
> 87441 |87343 | | 20952 | AccessShareLock | t
>   |  | 7784975 | 21247 | ExclusiveLock   | t
> 87441 |87343 | | 21222 | AccessShareLock | t
>   |  | 7783940 | 21092 | ExclusiveLock   | t
>   |  | 7783596 | 21044 | ExclusiveLock   | t
>   |  | 7783670 | 21052 | ExclusiveLock   | t
>   |  | 7784165 | 21124 | ExclusiveLock   | t
> 87441 |87343 | | 21046 | AccessShareLock | t
> 87441 |87343 | | 21164 | AccessShareLock | t
>   |  | 7784793 | 21218 | ExclusiveLock   | t
> 87441 |87343 | | 21247 | AccessShareLock | t
> 87441 |87343 | | 21278 | AccessShareLock | t
>   |  | 7784393 | 21158 | ExclusiveLock   | t
>   |  | 7783363 | 21008 | ExclusiveLock   | t
> 87441 |87343 | | 21249 | AccessShareLock | t
> 87441 |87343 | | 21280 | AccessShareLock | t
>   |  | 7785374 | 21303 | ExclusiveLock   | t
>   |  | 7783707 | 21061 | ExclusiveLock   | t
> 87441 |87343 | | 21147 | AccessShareLock | t
> 87441 |87343 | | 21304 | AccessShareLock | t

Look up those relation numbers as oid against pg_class.  Note that I'm
running 8.2 and haven't run 7.4 seriously in over two years, so some
of this might not match up.  A query like this works in 8.2:

select pgc.relname, pgl2.relation from pg_locks pgl join pg_locks pgl2
on (pgl.relation is null AND pgl2.relation is not null and
pgl.pid=pgl2.pid) join pg_class pgc on (pgl2.relation=pgc.oid);

Like I said, no guarantees that will run on 7.4

What you're looking for is something holding an exclusive lock on the
table you're trying to reindex.  But it doesn't sound as if that table
is bloated to me, so I'm guessing something else is wrong.

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

   http://archives.postgresql.org

Re: [ADMIN] When does VACUUM FULL not clean out all deleted data?

[James Cloos]

> "Tom" == Tom Lane <[EMAIL PROTECTED]> writes:

>> Why was the db using that extra five plus gigs?

Tom> Smells like a hadn't-been-vacuuming-often-enough problem.

Sorry, I forgot to mention:  autovacuum was enabled and I also ran
this script every night via cron:

#!/bin/bash
FULL=''
test $(date +%w) -eq 0 && FULL='FULL'
exec psql -U dbm -c "VACUUM ${FULL} VERBOSE ANALYZE" dbm

The app at least felt faster with the daily vacuum, even though
autovacuum was running.  I don't have a good explanation for that,
either.

-JimC
-- 
James Cloos <[EMAIL PROTECTED]> OpenPGP: 1024D/ED7DAEA6

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

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

Re: [ADMIN] Need some info on Postgresql

[Suresh Gupta VG]

Hi Scott,

 

Thanks for your reply and pls see my inline comments in red color.

 

Regards,

G. V. Suresh Gupta


-

Innovative Technology Solutions(ITS), Zensar Technologies 

Zensar Knowledge Park, Plot#5, MIDC IT Tower, 

Kharadi, Off Nagar Road, Pune - 411014

Landline :  +91-20-66453471   | +91-9890898688 

Email :   [EMAIL PROTECTED]| website:  www.zensar.com

 

 

 

 

 

-Original Message-
From: Scott Marlowe [mailto:[EMAIL PROTECTED] 
Sent: Friday, January 04, 2008 7:53 AM
To: Suresh Gupta VG
Cc: Peter Koczan; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Need some info on Postgresql

 

On Jan 3, 2008 5:33 AM, Suresh Gupta VG <[EMAIL PROTECTED]> wrote:

> 

> Hi Team,

> 

> I am using Postgresql 7.4.2 version on Solaris.

 

You need to update to 7.4.18 or whatever the last version was.  7.4.2

has known data eating bugs, and if you value your data even a little,

you should update.  this is a relatively painless update not requiring

a dump / restore.

 

Suresh : Can you pls tell is 7.4.2 is latest or 7.4.18 is latest? We
will plan to upgrade to 8.2.5. 

 

> There are number of tables

> say about 30+ tables in our database. I started to "reindex" the
tables

> individually. "reindex table <>". All the queries executed

> normally with less than 1 minute of duration. But one table is not

> responding any thing even after 10 minutes. The details of the table
are ...

 

I've had large tables take well over 10 minutes on 7.4 to reindex.

That's not necessarily a sign of a problem.  How many rows does this

table have? Do you get any kind of error message or the database just

never returns.  Have you tried vacuum full on this table instead?  Do

the logs say anything about the reindex operation getting an error

etc???

Suresh : This table contains only 5 columns and 5 rows of data. I don't
get any error message instead it remains un answered, I am using "ctrl
C" to cancel the command. I also had done the vacuum on this table, pls
see the result below. Pls give the general path of log files available
in the database. I don't have idea of this logs files of PGSQL.

commercelink=# vacuum verbose analyze currency;

INFO:  vacuuming "public.currency"

INFO:  index "currency_pkey" now contains 5 row versions in 2 pages

DETAIL:  0 index pages have been deleted, 0 are currently reusable.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "currency": found 0 removable, 5 nonremovable row versions in 1
pages

DETAIL:  0 dead row versions cannot be removed yet.

There were 3 unused item pointers.

0 pages are entirely empty.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  analyzing "public.currency"

INFO:  "currency": 1 pages, 5 rows sampled, 5 estimated total rows

VACUUM

commercelink=# reindex table currency;

^CCancel request sent

ERROR:  canceling query due to user request

commercelink=#

 

 

> Can you please tell me why it not executing this command on the
particular

> table and I am the owner of the DB? Can you please suggest some thing.

 

Is it really not executing?  Is it simply returning without a notice,

or hanging?

Suresh : It is not executing at all. It is not hanging, I am canceling
the query by giving "ctrl c". Then it is giving me the database prompt
as usual.

 

 

> We

> found these days, the database is taking more time to execute any
query. We

> are doing "vacuum Verbose analyze" regularly at low traffic time.

 

You would do well to consider a migration to 8.2.5 or 8.3 when it

comes out of beta / RC status.  7.4.x is getting rather old, and the

8.x series (8.0, 8.1, 8.2, and now 8.3) have each had major

improvements in speed.

 

Suresh : We will plan to upgrade to 8.X. What is this beta / RC status,
can you brief me pls? 



DISCLAIMER:
This email may contain confidential or privileged information for the intended 
recipient(s) and the views expressed in the same are not necessarily the views 
of Zensar Technologies Ltd. If you are not the intended recipient or have 
received this e-mail by error, its use is strictly prohibited, please delete 
the e-mail and notify the sender. Zensar Technologies Ltd. does not accept any 
liability for virus infected mails. 

[ADMIN] Postgres 7.4 VACUUM FULL multiple AccessExclusiveLocks?

[Mike White]

I'm performing a VACUUM FULL on a 7.4.7 db table, one that is not huge
at any given time, but which has a lot of churn.  Just the kind of
usage pattern that 7.4 loves.  :)  This is something that we have to do
every 6 months or so, and it always takes a long time.  Today, however,
I've noticed that the pg_lock table shows two AccessExclusiveLock
entries that are completely identical.  Is this a sign of internal
deadlock, or is this behavior expected during VACUUM FULL operation?

Also, please don't tell me to upgrade.  We're happily running 8.2 on
some production machines, but I am tied to 7.4 on this particular box
for reasons beyond my control.  :(

=# select * from pg_locks where mode = 'AccessExclusiveLock';
  relation  |  database  | transaction |  pid  |mode |
granted
++-+---+-+-
 2434930929 | 2434930890 | | 18356 | AccessExclusiveLock |
t
 2434930929 | 2434930890 | | 18356 | AccessExclusiveLock |
t
(2 rows)

miro_stats=# VACUUM FULL VERBOSE tsdb_archive_state;
INFO:  vacuuming "public.tsdb_archive_state"
INFO:  "tsdb_archive_state": found 0 removable, 59138 nonremovable row
versions in 4597419 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 60 to 60 bytes long.
There were 583813020 unused item pointers.
Total free space (including removable row versions) is 35231071156
bytes.
4596598 pages are or will become empty, including 0 at the end of the
table.
4597196 pages containing 35231061344 free bytes are potential move
destinations.
CPU 99.57s/20.04u sec elapsed 3948.67 sec.
INFO:  index "tsdb_archive_state_pkey" now contains 59138 row versions
in 132 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.02u sec elapsed 0.02 sec.
(Hanging here for about 4 hours.)

Thanks,
Mike White


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

Re: [ADMIN] MacOS X 10.5.1 and compiling for multiple Architectures

[Chris Ruprecht]

Hi Shane,

Sure, I'm aware of that.
I meant, that I have the PHP module running as 64-bit (since the Apple- 
provided Apache is 64-bit) but without the other architectures built- 
in. I know that I can re-build PG to be only 32 bit, but since we're  
slowly moving into a 64-bit world anyway, there is no real need to do  
that, except if there are major issues with PG 64-bit under OS X. So  
far, it's still up and running.


best regards,
chris
--
chris ruprecht
network grunt and bit pusher extraordinaíre





On Jan 3, 2008, at 14:09, Shane Ambler wrote:


Chris Ruprecht wrote:

Hey Tom, Shane,


I'm good to run with the x86_64 only binary since my Apache is  
built using that architecture.


That's not a real reason to run pg in 64 bit.

Although you would need php and libpq to be the same architecture as  
apache (the running architect if multiple available) - the  
postgresql server can be 32 bit. If apache is only 64 bit then you  
will need a 64 bit libpq.



Apache calls functions in the php module which then calls functions  
in the libpq - they must all have matching architectures available  
to do this which is why apple supplies fat binary libs with osx.



From there to postgresql is a network connection (or local socket)  
so each program is just sending data to the other - they don't need  
to be the same architecture any more than you need a 64 bit sparc  
browser to connect to a 64 bit sparc web server.




--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

---(end of  
broadcast)---

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




smime.p7s
Description: S/MIME cryptographic signature

Re: [ADMIN] When does VACUUM FULL not clean out all deleted data?

[Scott Marlowe]

On Jan 4, 2008 9:35 AM, James Cloos <[EMAIL PROTECTED]> wrote:
> > "Tom" == Tom Lane <[EMAIL PROTECTED]> writes:
>
> >> Why was the db using that extra five plus gigs?
>
> Tom> Smells like a hadn't-been-vacuuming-often-enough problem.
>
> Sorry, I forgot to mention:  autovacuum was enabled and I also ran
> this script every night via cron:
>
> #!/bin/bash
> FULL=''
> test $(date +%w) -eq 0 && FULL='FULL'
> exec psql -U dbm -c "VACUUM ${FULL} VERBOSE ANALYZE" dbm

Note that routine vacuum FULL is not a good idea actually.  vacuum
full is something you run when things have gone wrong (i.e. not enough
regular vacuuming) and it is usually best followed by reindexing all
your indexes.

It's far better to keep track of bloat and run vacuum full, if at all,
by hand, and only when needed.  Running it regularly with no
reindexing often results in index bloat which makes the database
slower not faster.

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

[ADMIN] how to array and other select

[Guy Deleeuw]

Hello the list

Before all : happy new year for all.

I Don't know how can I write a sql select.

I have 2 tables :
One with an array of codes

and another with a code

I try to retrieve the array of codes from the first table and return
only the codes existing on the second table
If possible I search also to retrieve 2 others fields from the first
table that corresponds to the code array value



I have :
 SELECT tcode[idx] AS code FROM (SELECT tcode,
generate_series(array_lower(tcode,1), array_upper(tcode,1)) AS idx FROM
table_cat_origin WHERE code=120 ) asdf
 UNION
 SELECT code FROM table_f260_cat_origin
;

but the number of returned rows are incorrect.

Anyone can help me ?

Thanks in advance

Guy


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

Re: [ADMIN] Vacuum taking an age

[Andrew Sullivan]

On Fri, Jan 04, 2008 at 06:55:13AM +0200, Brian Modra wrote:
> I started a vacuum on the table yesterday, and its still running. I
> guess thats because the table is live. I am pretty sure that if I take
> it offline, then the vacuum will complete relatively quickly. Am I
> right? (I don't want to take it offline unless I really need to.)

No.  The vacuum on that table is going to take a long time, period.  See
upthread about not brushing teeth for 3 months.  

Anyway, I can't believe a plain vacuum is going to help you here.  I'd
perform CLUSTER or else dump the database and reload it.  You're looking at
a service outage, I think.

A


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

Re: [ADMIN] Postgres 7.4 VACUUM FULL multiple AccessExclusiveLocks?

[Tom Lane]

Mike White <[EMAIL PROTECTED]> writes:
> I've noticed that the pg_lock table shows two AccessExclusiveLock
> entries that are completely identical.  Is this a sign of internal
> deadlock, or is this behavior expected during VACUUM FULL operation?

I think it's just the regular and session versions of the lock.  Don't
recall at the moment whether it's easier to tell them apart in newer
releases.

> Also, please don't tell me to upgrade.

You should *definitely* be on 7.4.x where x is considerably greater than
7.  This is a painless update and failing to do it borders on negligence.
Read the release notes at
http://www.postgresql.org/docs/7.4/static/release.html

regards, tom lane

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

Re: [ADMIN] invalid byte sequence for encoding "UNICODE":0xa1

[Ivo Rossacher]

Am Freitag, 4. Januar 2008 15.58:23 schrieb Popovici Alexandru:
> Hello,
>
>  For my database ( PostgreSQL 8) I made a backup (
> using pgAdmin ). Now when I try to restore the data (
> again using pgAdmin ) at some point the process is
> aborted:
>
> ---
>pg_restore: ERROR: invalid byte sequence for
> encoding "UNICODE":0xa1
>CONTEXT: COPY ...
>pg_restore: [archiver(db)] error returned by
> PQendcopy: invalid byte sequence for encading
> "UNICODE":0xa1
>CONTEXT: COPY ...
>pg_restore: *** aborted because of error
>Process returned exit code 1
> ---

There are serveral possible reasons for this issue.
-Restoring on a newer server then the backup was taken from. The servers got 
pickier regarding encoding over several versions.
-Restoring on a server with diverent encoding then the encoding of the server 
where the backup came from.
-Restoring with a differen client encoding at the pgAdmin (probably different 
version or setting).
-Corruptet data file.
-...

If you need more assistance we need to know the platform, versions and 
parameters you used for the process.

Best regards
Ivo
>
>If I delete the database and restore only the
> schema I get this message:
>
> ---
>   "WARNING: errors ignored on restore:21
>Process returned exit code 1"
> ---
>
>The designer of database has told me that this last
> message is OK, and this is the message which should I
> get after restoring the schema and data.
>I have asked him why I can restore the data but
> unfortunatelly he doesn't now where the problem is.
>The database is installed on a server with Windows
> 2003 .
>
> Can you please help me?
>
>
>  
> ___
>_ Be a better friend, newshound, and
> know-it-all with Yahoo! Mobile.  Try it now. 
> http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
>
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org



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