Re: [SQL] pg primary key bug?
Hi, sorry, but we have the case number 3 in with the same problem. Also this time we do not find any linux box crash nor pg stop or restart. The pg version is 7.4.2 on dual xeon + scsi running also RedHat 3.0 AS. In all the cases we are running RedHat AS 3.0. This system was running for over 12 m. without any problems. I send also the state of the problem table (also the same) and my question is: Need we to stop using vacuum full for now? And can only vacuum analyze make the same problem in pg? As I understand the problem is in OS by making vacuum full analyze (as Tom wrote). We do not found any problems in OS and the ony solution we see is to stop using vacuum full analyze. Also we are using only jdbc to access pg. Is it possible that jdbc to make this problem? regards, ivan. serv117=# select oid, xmin, cmin, xmax, cmax, ctid, * from a_constants_str ; oid | xmin | cmin | xmax | cmax | ctid | constname | fid | constvalue ---+-+-+-+-+--+---+-+- 760807304 | 7357839 | 0 | 0 | 0 | (0,1) | PARTID | 0 | SOF_79 760807305 | 7357839 | 0 | 0 | 0 | (0,2) | AACCGRID | 0 | SOF_29 760807306 | 7357839 | 0 | 0 | 0 | (0,3) | AKLTYPID | 0 | SOF_47 760807307 | 7357839 | 0 | 0 | 0 | (0,4) | AOBLASTID | 0 | SOF_41 760807308 | 7357839 | 0 | 0 | 0 | (0,5) | ANMGRID | 0 | SOF_102 760807309 | 7357839 | 0 | 0 | 0 | (0,6) | LOCAID | 0 | SOF_112 760807310 | 7357839 | 0 | 0 | 0 | (0,7) | AKLGRID | 0 | SOF_116 760807311 | 7357839 | 0 | 0 | 0 | (0,8) | ADARID | 0 | SOF_33 760807314 | 7357839 | 0 | 0 | 0 | (0,11) | ASLUID | 0 | SOF_86 760807315 | 7357839 | 0 | 0 | 0 | (0,12) | AUSERID | 0 | SOF_28 760807318 | 7357839 | 0 | 0 | 0 | (0,15) | ANLIZPID | 0 | SOF_100137 760807316 | 7507505 | 3 | 3 | 0 | (0,36) | ASETUPID | 0 | SOF_4618 760807324 | 7750088 | 7766293 | 7766293 | 2 | (0,92) | DOCID | 0 | SOF_836141 760807319 | 7740812 | 2 | 2 | 0 | (4,8) | ANOMID | 0 | SOF_31353 760807325 | 7750088 | 19 | 19 | 0 | (4,111) | DOCRID | 0 | SOF_2067257 760807326 | 7750088 | 41 | 41 | 7750975 | (6,27) | DOCPLAID | 0 | SOF_44261 760807327 | 7750088 | 46 | 46 | 7750975 | (7,106) | DOCPOGPLA | 0 | SOF_58034 760807324 | 7750088 | 7766293 | 7766293 | 1 | (9,107) | DOCID | 0 | SOF_836141 760807313 | 7680519 | 2 | 2 | 0 | (10,3) | NASTRF | 0 | SOF_161 760807312 | 7688072 | 2 | 2 | 0 | (10,92) | AGRADID | 0 | SOF_804 760807324 | 7750088 | 7766293 | 7766293 | 1 | (12,18) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (13,94) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (15,45) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (17,4) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (18,80) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (20,31) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (21,109) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (23,58) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (25,9) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (26,85) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (28,36) | DOCID | 0 | SOF_836141 760807324 | 7750088 | 7766293 | 7766293 | 1 | (29,114) | DOCID | 0 | SOF_836141 760807317 | 7702028 | 2 | 2 | 0 | (51,41) | AMITAID | 0 | SOF_345 760807320 | 7702064 | 2 | 2 | 0 | (51,42) | ATRANSID | 0 | SOF_458 760807321 | 7707993 | 2 | 2 | 0 | (57,8) | TDOCID | 0 | SOF_546 760807323 | 7753774 | 3 | 3 | 0 | (59,7) | AKLIID | 0 | SOF_22695 760807322 | 7707993 | 2385 | 2385 | 0 | (59,95) | TDOCRID | 0 | SOF_105930 (37 rows) serv117=# \d a_constants_str Table "public.a_constants_str" Column | Type | Modifiers +---+--- constname | character varying(30) | not null fid | integer | not null constvalue | character varying(30) | Indexes: "a_constants_str_pkey" primary key, btree (constname, fid) Tom Lane wrote: pginfo <[EMAIL PROTECTED]> writes: Will upgrade to 8.0 solve this type of problems ? The problem is probably not Postgres' fa
[SQL] Timestamp with timezone question.
AFAIK, the input for a timestamptz is converted and stored as UTC. And outputing a timezonetz value converts the internally stored UTC value to the current locale's timezone. So there is not a way to actually store the original TZ itself, whereas the timetz type clearly does that. Consider a schema designed to store internet mail. Since pgsql always converts a timestamptz to UTC, we have lost the information of the Sender's local timezone. Should i go with a separete date and timetz ? -- -Achilleus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Working with XML.
Title: Message
Hi Theo,There have been 2 major changes
between the 7 and 8 versions that affect the coding in xml2. You need to
edit the source code in order for it to compile properly on 7.First,
work_mem has to be changed to SortMem (line 666).
I.e. tupstore = tuplestore_begin_heap(true, false,
work_mem);should be changed to:
tupstore =
tuplestore_begin_heap(true, SortMem);
Second, the error reporting framework has changed.
To fix this, you need to change all the ereport coding to use
elog. I.e.
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("xpath_table must be called as a table function")));should be
changed to:
elog(ERROR,"xpath_table must be called as a table
function");
I have attached an edited copy of xpath.c with these changes, if you would
like to work with it.
Regards,
George- Original Message - From: Theo Galanakis
To: 'pgsql-sql@postgresql.org' Sent: Sunday, February 20, 2005 11:28
PMSubject: FW: [SQL] Working with XML.Hi,
I have copied all the files manually from
http://developer.postgresql.org/docs/pgsql/contrib/ for the xml2 contribution.
However I have the following issue when I attempt to compile with
gmake:gcc -I/usr/include/libxml2 -fpic -I. -I../../src/include
-D_GNU_SOURCE -c -o xpath.o xpath.cxpath.c: In function
`xpath_table':xpath.c:689: `work_mem' undeclared (first use in this
function)xpath.c:689: (Each undeclared identifier is reported only
oncexpath.c:689: for each function it appears in.)gmake: *** [xpath.o]
Error 1I have installed :
libxml2-devel-2.5.10-1.rpmWhat am I doing wrong, or can someone point me
to the direction of a binary for XML2 on RedHat ES3, Postgres
7.4.5.Cheers,
Theo
/* Parser interface for DOM-based parser (libxml) rather than
stream-based SAX-type parser */
/*#include "errcodes.h"*/
#include "postgres.h"
#include "fmgr.h"
#include "executor/spi.h"
#include "funcapi.h"
#include "miscadmin.h"
#include "lib/stringinfo.h"
/* libxml includes */
#include
#include
#include
#include
#include
/* declarations */
static void *pgxml_palloc(size_t size);
static void *pgxml_repalloc(void *ptr, size_t size);
static void pgxml_pfree(void *ptr);
static char *pgxml_pstrdup(const char *string);
static void pgxml_errorHandler(void *ctxt, const char *msg,...);
voidelog_error(int level, char *explain, int force);
voidpgxml_parser_init(void);
static xmlChar *pgxmlNodeSetToText(xmlNodeSetPtr nodeset,
xmlChar * toptagname, xmlChar * septagname,
xmlChar * plainsep);
text *pgxml_result_to_text(xmlXPathObjectPtr res, xmlChar * toptag,
xmlChar * septag, xmlChar * plainsep);
xmlChar*pgxml_texttoxmlchar(text *textstring);
static xmlXPathObjectPtr pgxml_xpath(text *document, xmlChar * xpath);
Datum xml_valid(PG_FUNCTION_ARGS);
Datum xpath_nodeset(PG_FUNCTION_ARGS);
Datum xpath_string(PG_FUNCTION_ARGS);
Datum xpath_number(PG_FUNCTION_ARGS);
Datum xpath_bool(PG_FUNCTION_ARGS);
Datum xpath_list(PG_FUNCTION_ARGS);
Datum xpath_table(PG_FUNCTION_ARGS);
/* Global variables */
char *errbuf; /* per line error buffer */
char *pgxml_errorMsg = NULL; /* overall error message */
/* Convenience macros */
#define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin,
CStringGetDatum(cstrp)))
#define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout,
PointerGetDatum(textp)))
#define ERRBUF_SIZE 200
/* memory handling passthrough functions (e.g. palloc, pstrdup are
currently macros, and the others might become so...) */
static void *
pgxml_palloc(size_t size)
{
/* elog(DEBUG1,"Alloc %d in CMC %x",size,CurrentMemoryContext); */
return palloc(size);
}
static void *
pgxml_repalloc(void *ptr, size_t size)
{
/* elog(DEBUG1,"ReAlloc in CMC %x",CurrentMemoryContext);*/
return repalloc(ptr, size);
}
static void
pgxml_pfree(void *ptr)
{
/* elog(DEBUG1,"Free in CMC %x",CurrentMemoryContext); */
return pfree(ptr);
}
static char *
pgxml_pstrdup(const char *string)
{
return pstrdup(string);
}
/* The error handling function. This formats an error message and sets
* a flag - an ereport will be issued prior to return
*/
static void
pgxml_errorHandler(void *ctxt, const char *msg,...)
{
va_list args;
va_start(args, msg);
vsnprintf(errbuf, ERRBUF_SIZE, msg, args);
va_end(args);
/* Now copy the argument across */
if (pgxml_errorMsg == NULL)
pgxml_errorMsg = pstrdup(errbuf);
else
{
int32 xsize = strlen(pgxml_errorMsg);
pgxml_errorMsg = repalloc(pgxml_errorMsg,
(size_t)
Re: [SQL] Timestamp with timezone question.
On 2005-02-21, Achilleus Mantzios <[EMAIL PROTECTED]> wrote: > Consider a schema designed to store internet mail. > > Since pgsql always converts a timestamptz to UTC, we have lost > the information of the Sender's local timezone. > > Should i go with a separete date and timetz ? No. Consider instead storing a timestamptz with the actual time of the mail, and a separate field with an interval representing the zone offset. Then you can use AT TIME ZONE to recover the sender's local time. e.g. (this table has columns serial, timestamptz, interval) insert into dtz values (DEFAULT, '2005-03-21 07:05:00 -0800', '2005-03-21 07:05:00 -0800'::timestamp - '2005-03-21 07:05:00 -0800'::timestamptz at time zone 'UTC' ); (the timestamp - timestamptz thing is just a reasonably reliable way of getting the timezone offset without complicated parsing.) select * from dtz; id | t| z ++--- 1 | 2005-03-21 15:05:00+00 | -08:00:00 (1 row) select *, t at time zone z as ot from dtz; id | t| z | ot ++---+- 1 | 2005-03-21 15:05:00+00 | -08:00:00 | 2005-03-21 07:05:00 (1 row) -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Timestamp with timezone question.
O Andrew - Supernews έγραψε στις Feb 21, 2005 : > On 2005-02-21, Achilleus Mantzios <[EMAIL PROTECTED]> wrote: > > Consider a schema designed to store internet mail. > > > > Since pgsql always converts a timestamptz to UTC, we have lost > > the information of the Sender's local timezone. > > > > Should i go with a separete date and timetz ? > > No. Consider instead storing a timestamptz with the actual time of the > mail, and a separate field with an interval representing the zone offset. > Then you can use AT TIME ZONE to recover the sender's local time. > > e.g. (this table has columns serial, timestamptz, interval) > > insert into dtz values (DEFAULT, > '2005-03-21 07:05:00 -0800', > '2005-03-21 07:05:00 -0800'::timestamp > - '2005-03-21 07:05:00 -0800'::timestamptz at time zone 'UTC' > ); > > (the timestamp - timestamptz thing is just a reasonably reliable way of > getting the timezone offset without complicated parsing.) > > select * from dtz; > id | t| z > ++--- > 1 | 2005-03-21 15:05:00+00 | -08:00:00 > (1 row) > > select *, t at time zone z as ot from dtz; > id | t| z | ot > ++---+- > 1 | 2005-03-21 15:05:00+00 | -08:00:00 | 2005-03-21 07:05:00 > (1 row) Cool thanx. I ended up displaying the actual date header field of the SMTP message (just like the yahoo guys do). > > -- -Achilleus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] query for records based on date
If I have a table with an attribute of type "timestamp without time zone", what is the best way to query that table for records that are older than, say, one week. I suppose I could get the current time in epoch seconds and the time of the attribute in the same, and select those records where the difference is greater than the number of seconds in a week. Is there something easier like SELECT * FROM table WHERE attribute IS OLDER THAN one week; Thanks. -- Brandon ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] query for records based on date
Brandon Metcalf wrote: If I have a table with an attribute of type "timestamp without time zone", what is the best way to query that table for records that are older than, say, one week. I suppose I could get the current time in epoch seconds and the time of the attribute in the same, and select those records where the difference is greater than the number of seconds in a week. Is there something easier like SELECT * FROM table WHERE attribute IS OLDER THAN one week; Look up "intervals" in the manuals - you can just subtract timestamps/dates. Probably also worth searching the mailing lists for "ago()" and "volatile" to learn about the issues with indexing. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] query for records based on date
m == martin@bugs.unl.edu.ar writes: m> El Lun 21 Feb 2005 17:00, Brandon Metcalf escribió: m> > If I have a table with an attribute of type "timestamp without time m> > zone", what is the best way to query that table for records that are m> > older than, say, one week. m> > m> > I suppose I could get the current time in epoch seconds and the time of m> > the attribute in the same, and select those records where the difference m> > is greater than the number of seconds in a week. Is there something m> > easier like m> > m> > SELECT * FROM table WHERE attribute IS OLDER THAN one week; m> SELECT * FROM table WHERE attribute < (now()::DATE - 7)::TIMESTAMP; Thanks. -- Brandon ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] query for records based on date
El Lun 21 Feb 2005 17:00, Brandon Metcalf escribió: > If I have a table with an attribute of type "timestamp without time > zone", what is the best way to query that table for records that are > older than, say, one week. > > I suppose I could get the current time in epoch seconds and the time of > the attribute in the same, and select those records where the difference > is greater than the number of seconds in a week. Is there something > easier like > > SELECT * FROM table WHERE attribute IS OLDER THAN one week; SELECT * FROM table WHERE attribute < (now()::DATE - 7)::TIMESTAMP; -- 17:41:37 up 162 days, 7:36, 2 users, load average: 0.11, 0.12, 0.08 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] pg primary key bug?
pginfo <[EMAIL PROTECTED]> writes: > sorry, but we have the case number 3 in with the same problem. > Also this time we do not find any linux box crash nor pg stop or restart. Hmm, well there's *something* mighty curious about the history of this table. The xmin values span a range of almost 400,000 transactions and yet the oids are twenty-three consecutive values. Is this the only table you've inserted into during the last 400K transactions? It's also odd that there's so much empty space (only 37 rows in 60 pages). It's hard to see how that could be unless there were many updates on the table, but judging from the name and contents of the table I can hardly see a reason for lots of updates. How is this table used exactly? Do you do UPDATEs on it? DELETEs? SELECT FOR UPDATE? Do you do VACUUM, or VACUUM FULL, or both? regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] pg primary key bug?
Hi Tom, Tom Lane wrote: pginfo <[EMAIL PROTECTED]> writes: sorry, but we have the case number 3 in with the same problem. Also this time we do not find any linux box crash nor pg stop or restart. Hmm, well there's *something* mighty curious about the history of this table. The xmin values span a range of almost 400,000 transactions and yet the oids are twenty-three consecutive values. Is this the only table you've inserted into during the last 400K transactions? No. It's also odd that there's so much empty space (only 37 rows in 60 pages). It's hard to see how that could be unless there were many updates on the table, but judging from the name and contents of the table I can hardly see a reason for lots of updates. How is this table used exactly? In this table we store the last value for the ID of part from other tables. For each table we have one constant in this table. We are using the table as sequence. For Example if we nee to insert the next record in some table we make: select constvalue from a_constants_str where constname ='...' for update; increase the value and make update a_constants_str set constvalue= (new value) where... It is not so easy as I described, but in general this is the case. Al this is connected with replications and data syncronisation and so on. Do you do UPDATEs on it? Yes, see the description. DELETEs? No, never. SELECT FOR UPDATE? Yes. Do you do VACUUM, or VACUUM FULL, or both? Only vacuum full analyze once per day. Also once per 4h pg_dump (for pg 7.x we do not have any chance to restore data without full dump. With 8.0 we will test the ne solution and stop to make pg_dump. We have > 200 relative big installs for this ERP and all are using pg. For the last 3 y. we have one time data corruption and in this 3 cases problem with pkey's. All the time pg is working relative well . regards, ivan. regards, tom lane
Re: [SQL] Timestamp with timezone question.
On Mon, Feb 21, 2005 at 16:16:04 +0200, Achilleus Mantzios <[EMAIL PROTECTED]> wrote: > > Since pgsql always converts a timestamptz to UTC, we have lost > the information of the Sender's local timezone. > > Should i go with a separete date and timetz ? Someone else gave you a recommended solution. However there was a discussion on the order of 6 months ago about changing the timestamptz type to keep this information. Nothing will be changing in the near future, but you may (or may not) find the discussion useful. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Timestamp with timezone question.
O Bruno Wolff III έγραψε στις Feb 22, 2005 : > On Mon, Feb 21, 2005 at 16:16:04 +0200, > Achilleus Mantzios <[EMAIL PROTECTED]> wrote: > > > > Since pgsql always converts a timestamptz to UTC, we have lost > > the information of the Sender's local timezone. > > > > Should i go with a separete date and timetz ? > > Someone else gave you a recommended solution. > > However there was a discussion on the order of 6 months ago about changing > the timestamptz type to keep this information. Nothing will be changing > in the near future, but you may (or may not) find the discussion useful. Thanx, i'll check it out sometime. Apart from that, having a new (version of a) type (timestamptz) just to provide a convinience function isnt too useful. > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > -- -Achilleus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
