[GENERAL] RHEL 7
I am currently running Version 9.3.10 of postgres with RHEL 6. I am going to upgrade my O/S soon to RHEL 7. Do I need to upgrade to version 9.4.x of postgres? Paul Tilles
[GENERAL] V8.4 TOAST table problem
Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order
to take advantage of autovacuum features. This server exists in a very
closed environment (isolated network, limited root privileges; this
explains the older software in use) and runs on RHEL5.5 (i686). After
the upgrade, the database has constantly been growing to the tune of 5-6
GB a day. Normally, the database, as a whole, is ~20GB; currently, it is
~89GB. We have a couple other servers which run equivalent databases and
actually synchronize the records to each other via a 3rd party
application (one I do not have access to the inner workings). The other
databases are ~20GB as they should be.
Running the following SQL, it's fairly obvious there's an issue with a
particular table, and, more specifically, its TOAST table.
|SELECT nspname|| '.' || relnameAS relation,
pg_size_pretty(pg_relation_size(C.oid)) AS size
FROM pg_class C
LEFT JOIN pg_namespace NON (N.oid= C.relnamespace)
WHERE nspnameNOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT20;|
Which produces:
relation | size
--+-
pg_toast.pg_toast_16874 | 89 GB
fews00.warmstates | 1095 MB
...
(20 rows)
This TOAST table is for a table called timeseries which saves large
records of blobbed data. A|SUM(LENGTH(blob)/1024./1024.)| of all the
records in timeseries yields ~16GB for that column. There should be
[b]no reason[/b] this table's TOAST table should be as large as it is.
I've performed a |VACUUM FULL VERBOSE ANALYZE timeseries|, and the
vacuum runs to completion with no errors.
INFO: vacuuming pg_toast.pg_toast_16874
INFO: pg_toast_16874: found 22483 removable, 10475318 nonremovable
row versions in 10448587 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 37 to 2036 bytes long.
There were 20121422 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
4944885 pages are or will become empty, including 0 at the end of
the table. 4944885 pages containing 0 free bytes are potential move
destinations.
CPU 75.31s/29.59u sec elapsed 877.79 sec.
INFO: index pg_toast_16874_index now contains 10475318 row
versions in 179931 pages
DETAIL: 23884 index row versions were removed.
101623 index pages have been deleted, 101623 are currently reusable.
CPU 1.35s/2.46u sec elapsed 21.07 sec.
REINDEXed the table which freed [b]some[/b] space (~1GB). I can't
CLUSTER the table as there isn't enough space on disk for the process,
and I'm waiting to rebuild the table entirely as I'd like to find out
why it is so much bigger than equivalent databases we have.
Ran a query from the PostgreSQL wiki here - Show Database Bloat
http://wiki.postgresql.org/wiki/Show_database_bloat, and this is what
I get:
current_database | schemaname | tablename | tbloat | wastedbytes |
iname | ibloat | wastedibytes
--++++-+-++--
ptrdb04 | fews00 | timeseries | 1.0 | 0 | idx_timeseries_synchlevel
| 0.0 | 0
ptrdb04 | fews00 | timeseries | 1.0 | 0 | idx_timeseries_localavail
| 0.0 | 0
ptrdb04 | fews00 | timeseries | 1.0 | 0 | idx_timeseries_expirytime
| 0.0 | 0
ptrdb04 | fews00 | timeseries | 1.0 | 0 | idx_timeseries_expiry_null
| 0.0 | 0
ptrdb04 | fews00 | timeseries | 1.0 | 0 | uniq_localintid | 0.0 | 0
ptrdb04 | fews00 | timeseries | 1.0 | 0 | pk_timeseries | 0.1 | 0
ptrdb04 | fews00 | idx_timeseries_expiry_null | 0.6 | 0 | ? | 0.0 | 0
It looks like the database doesn't consider this space as empty, at
all, but I just don't see where all the disk space is coming from!
I suspect that this database server is deciding to use 4-5x as much disk
space to save the same records pulled from the other data servers. My
question is this: Is there a way I can verify the physical disk size of
a row? I'd like to compare the size of one row on this database to
another healthy database.
Thanks for any help you can provide!
Paul Tilles
[GENERAL] upgrading from V8.3.4 to V9.2.4
We are going to be upgrading our postgres version from 8.3.4 to 9.2.4 in the near future. Can anyone give me a short list of gotchas concerning problems we might step into? We have a number of functions written in PL/pgSQL. Will they work in 9.2.4 without modification? Thanks in advance to the Community for your help. Paul TIlles -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] possible ecpg compatibility problem
I have an executable which uses ecpg which was created on a system using postgres version 8.2.6. I sent it to a site with version 8.4.7 installed. The executable fails with a memory fault. Is there a problem doing this? Paul Tilles -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Changes for version 8.4
Where can I find a list of changes for Version 8.4 of postgres? Paul Tilles -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Version 8.2.6 - ecpg: current_timestamp - how to control timezone
We have noticed that in the ecpg version for postgres 7.4.8, that the timezone associated with current_timestamp was UTC. We recently upgraded to postgres Version 8.2.6 and notice that the timezone associated with current_timestamp is the local time zone. How do we set the time zone for current_timestamp to be UTC? Paul Tilles -- 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-timestamp- interval problem V8.2.x
A colleague of mine is working with the following code (postgres Version
8.2.4):
#include stdio.h
#include stdlib.h
#include pgtypes_timestamp.h
#include pgtypes_interval.h
EXEC SQL INCLUDE sqlca;
main(argc, argv)
int argc;
char *argv[];
{
timestamp dt1st, dt2nd;
interval *int_day, *int_pt;
char timebeg[40];
char timeend[40];
int rc;
strcpy(timebeg,2008-04-01);
strcpy(timeend,2008-04-05);
printf(timebeg: %s\n, timebeg);
printf(timeend: %s\n, timeend);
dt1st=PGTYPEStimestamp_from_asc(timebeg,NULL);
dt2nd=PGTYPEStimestamp_from_asc(timeend,NULL);
int_day=PGTYPESinterval_from_asc(02 01:02:03, NULL);
printf(int_day : %s\n, PGTYPESinterval_to_asc(int_day));
rc=PGTYPEStimestamp_sub(dt2nd, dt1st, int_pt);
printf(rc, date2 - date1 = %d, %s\n, rc, PGTYPESinterval_to_asc(int_pt));
exit(0);
}
The output is
timebeg: 2008-04-01
timeend: 2008-04-05
int_day : @ 2 days 1 hour 2 mins 3 secs
rc, date2 - date1 = 0, @ 1 sec ago
The result of the subtraction of the the two timestamps is not correct.
What's going on?
TIA.
Paul Tilles
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Version 8.2.4 ecpg - function not found
We are upgrading from Version 7.4.8 to 8.2.4. In 7.4, there were functions called ECPGis_informix_null ECPGset_informix_null In 8.2.4, I do not see these functions. Instead, I see functions ECPGis_noind_null ECPGset_noind_null Are they functionally the same? Also, the 8.2.4 doc (Section 31.9.1) describes the functions risnull and rsetnull. These are the names of the original Informix functions. Are they available for use through ecpg? As always, TIA. Paul Tilles ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] date of next Version 8.2 release
We would like to use the 8.2 version of postgres with our next software build. The next 8.2 version will contain a software patch which is critical to our needs. When is the next release of 8.2 expected to occur? As always, TIA. Paul Tilles ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] ecpg: dtime_t vs timestamp
We have upgraded from Version 7.4.x to Version 8.2.4.
In 7.4.x, we use the Informix compatibility functionality to use legacy
code.
Our .pgc code looks as follows:
#include Ice.h
EXEC SQL include sqlda;
EXEC SQL include sqltypes;
EXEC SQL include sql3types;
EXEC SQL include pgtypes_timestamp;
EXEC SQL include pgtypes_date;
EXEC SQL include pgtypes_interval;
EXEC SQL include pgtypes_numeric;
EXEC SQL BEGIN DECLARE SECTION;
struct Ice_t
{
charlid[9];
charpe[3];
short dur;
charts[3];
charextremum[2];
dtime_t obstime;
double value;
charshef_qual_code[2];
longquality_code;
short revision;
charproduct_id[11];
dtime_t producttime;
dtime_t postingtime;
}
The Ice.h header file includes the postgres datetime.h header file. The
datetime.h file has the line typedef timestamp dtime_t. Using Version
7.4.x, this worked. Now when we use Version 8.2.4 (which has an updated
ecpg), this results in the message
ERROR: invalid datatype 'dtime_t'
How should we fix this?
Paul Tilles
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] problem calling psql multiple times from a script ?
I am executing a script which contains multiple executions of psql. The last execution of psql renames the database. It looks something like the following psql -f create_tables.sql db_name1 psql -f modify_tables.sql db_name1 psql -f add_indexes.sql db_name1 psql template1 XX_rename ALTER DATABASE db_name1 RENAME TO db_name2; XX_rename I notice sometimes that the ALTER DATABASE ... RENAME ... statement fails with an error that a user has the database (db_name1) open. I am wondering if one of the previous executions of psql is doing some back room work in the database while allowing the script to continue. I am wondering if this back room work prevents the database from being renamed. Which system table could I check to see if the database is open? I have seen this type of back room behavior with our Informix databases. Multiple calls to dbaccess followed by a database rename caused the rename to fail in some cases. I had to add a loop with a sleep followed by a check if the database was open. We are using postgres Version 7.4.8. TIA. Paul Tilles ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] psql access of user's environmental variables
I need to use the value of an environment variable as part of an SQL query within psql. I can do the following withing psql: \set local_site `echo $FXA_LOCAL_SITE \echo local site = :local_site The result is local_site = xxx which is correct. What I really want to do is the following: \set local_site `echo $FXA_LOCAL_SITE UPDATE table_name SET office_id = :local_site; This results in the message column xxx does not exist Is there any way that I can use the value of the FXA_LOCAL_SITE env variable in my UPDATE statement? Paul Tilles ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] psql access of user's environmental variables
Tom, Thank you. That works. My psql does not allow me to leave off the trailing `. It is my typing that is the problem. Paul Tom Lane wrote: Paul Tilles [EMAIL PROTECTED] writes: What I really want to do is the following: \set local_site `echo $FXA_LOCAL_SITE UPDATE table_name SET office_id = :local_site; This results in the message column xxx does not exist Yes, because you have no quotes in the value of the variable, so that update looks to the server like UPDATE table_name SET office_id = xxx; After some fooling around, the easiest way to get the needed quotes is to embed them in the echo result: \set local_site `echo '$FXA_LOCAL_SITE'` BTW, does your psql really let you leave off the trailing ` like that? Mine doesn't. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] doesn't recognize !=- (not equal to a negative value)
Version postgres 7.4.7: Following sql UPDATE tablename SET value = 0.0 where value!=-9.4; results in the error message ERROR: operator does not exist: smallint !=- integer HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. Seems that postgres has a problem parsing a not equal negative value. Anybody know if this is fixed in later versions? Paul Tilles ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] doesn't recognize !=- (not equal to a negative value)
Yes. That works. I think that the parser should work properly either way. Paul Bruce Momjian wrote: Paul Tilles wrote: Version postgres 7.4.7: Following sql UPDATE tablename SET value = 0.0 where value!=-9.4; results in the error message ERROR: operator does not exist: smallint !=- integer HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. Have you tried? value != -9.4 --- Seems that postgres has a problem parsing a not equal negative value. Anybody know if this is fixed in later versions? Paul Tilles ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] RPM for RedHat 7.2
I am looking to install the new Postgres V7.4 on RedHat Linux 7.2. Is there an RPM available? If so, where? TIA, Paul Tilles ---(end of broadcast)--- TIP 3: 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
