[SQL] diff value retuns, debug mode and play mode
I have a postgresql function at the bottom , when i run at EMS POSTGRESQL debug mode with F8 step by step. Its work great but when i use with play button it returns null what is the different about debug mode and run mode. BEST REGARDS. CREATE OR REPLACE FUNCTION "public"."allocatedamount" (varchar, integer) RETURNS numeric AS $body$ /* allocatedamount (varchar,integer) raporlarda kullan?lmak ?zere belirli bir sold un nekadar?n?n allocate edildi?ini bulmak i?in $1: Varchar: Tablo ismi.(SOCIAL,ACCOMODATION,vs) $2: Integer: Soldid. */ declare res numeric(15,2); _pida integer[4]; _pid integer; _rec record; begin if $1 = 'REGISTRATION' then begin _pid:=1; for _rec in select personaldetails.id from personaldetails where personaldetails.id=$2 or personaldetails.masterid=$2 loop _pida[_pid]:=_rec.id; _pid:=_pid+1; end loop; select into res sum(allocatedpayments.amount) from allocatedpayments where allocatedpayments.tablename='REGISTRATION' and allocatedpayments.tableid = ANY (_pida::integer[]); res=888; end; ELSIF $1 = 'MASTER-REGISTRATION' then select into res sum(allocatedpayments.amount) from allocatedpayments where allocatedpayments.tablename='REGISTRATION' and allocatedpayments.tableid=$2; ELSIF $1 = 'ACC-REGISTRATION' then select into res sum(allocatedpayments.amount) from allocatedpayments where allocatedpayments.tablename='REGISTRATION' and allocatedpayments.tableid=$2; else select into res sum(allocatedpayments.amount) from allocatedpayments where allocatedpayments.tablename=$1 and allocatedpayments.tableid=$2; end if; if res is null then res:=0; end if; return res; end; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Relation in tables
Use a view per department, which show/hide the columns according to your liking. Give each department a schema and put everything related to it inside for cleanliness. Use UPDATE triggers on the views, which in fact write to the products table, so that the departments can only update the columns you like. You can even make some columns readable but not writeable, by raising an exception if a modification is attempted on that column. If you want to reuse your code between departments, you will want all the views to have the same columns, so make them return NULL for the fields that they cannot see. Finally don't forget to make the products table inaccessible the departments. Okay, I will use Triggers to manage the insert and update table and viewers to select records, but need I use more than one table (with inherits)? Or I just use product table with the ALL departaments coluns and check the perms into Triggers and Viewers??? Thanks ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] pg primary key bug?
Hi all, Sorry for my post, but I think it is pg primary key bug. It is secont time in that we found the bug (see the replays for this message). We have many server with pg and use it over jdbc + jboss. I am not able to stop this server for long time, but I have dumped the problem table. It is very important to know if it is bug, because we have many server running pg + our ERP and continuing to install new. 01=# select * from a_constants_str order by constname; constname | fid | constvalue ---+-+ AACCGRID | 0 | SOF_3 ADARID | 0 | SOF_2 AGRADID | 0 | SOF_165 AKLGRID | 0 | SOF_8 AKLIID | 0 | SOF_3513 AKLTYPID | 0 | SOF_3 ANMGRID | 0 | SOF_10 ANOMID | 0 | SOF_747 AOBLASTID | 0 | SOF_3 ASETUPID | 0 | SOF_399 ASLUID | 0 | SOF_17 AUSERID | 0 | SOF_3 DOCID | 0 | SOF_25658 DOCPLAID | 0 | SOF_19738 DOCPLAID | 0 | SOF_19738 DOCPOGPLA | 0 | SOF_24281 DOCRID | 0 | SOF_184547 LOCAID | 0 | SOF_13 NASTRF | 0 | SOF_1 TDOCID | 0 | SOF_47 TDOCRID | 0 | SOF_2439 (21 rows) 01=# select * from a_constants_str where constname='DOCPLAID' ; constname | fid | constvalue ---+-+ DOCPLAID | 0 | SOF_19738 DOCPLAID | 0 | SOF_19738 (2 rows) 01=# \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) regards, ivan [EMAIL PROTECTED] wrote: There are not enough spaces available in the column to allow for the number of DOC_IDs. There are three spaces, allowing for at most four occurrences of DOC_ID, but there are eleven. --Rick Michael Glaesemann <[EMAIL PROTECTED]To: pginfo <[EMAIL PROTECTED]> > cc: pgsql-sql@postgresql.org Sent by: Subject: Re: [SQL] pg primary key bug? [EMAIL PROTECTED] tgresql.org 02/03/2005 09:14 AM On Feb 3, 2005, at 21:53, pginfo wrote: I am using pg 7.4.5 on RedHat AS 3.0. sklad21=# \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) sklad21=# select * from a_constants_str ; constname | fid | constvalue ---+-+- AACCGRID | 0 | SOF_3 AKLTYPID | 0 | SOF_3 ADARID| 0 | SOF_2 AOBLASTID | 0 | SOF_6 AUSERID | 0 | SOF_17 ANMGRID | 0 | SOF_21 LOCAID| 0 | SOF_41 DOCID | 0 | SOF_1585254 DOCPLAID | 0 | SOF_1052900 AKLIID| 0 | SOF_18740 DOCRID| 0 | SOF_2268142 DOCPOGPLA | 0 | SOF_324586 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 AKLGRID | 0 | SOF_45 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 ASETUPID | 0 | SOF_4605 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 TDOCID| 0 | SOF_337 TDOCRID | 0 | SOF_19450 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 AGRADID | 0 | SOF_256 DOCID | 0 | SOF_1585254 ASLUID| 0 | SOF_46 NASTRF| 0 | SOF_88 ANOMID| 0 | SOF_1200 (30 rows) Pls., see
[SQL] No triggers visible for different user in information_schema.triggers
Hello, I am using a postgres 7.4 dbms, a single database DB in the public scheme and two database users USER1 and USER2, both of them having superuser priviliges. USER1 is owner of all tables in the database DB. I have defined triggers on some tables as user USER1, and all of them are shown in the information_schema.triggers view. USER2 also can create and drop these triggers, but does not see any entry in information_schema.triggers. The query SELECT * FROM information_schema.triggers; returns an empty result. I thought this is caused by a lack of priviliges, but USER1 as well as USER2 are superusers. What is my fault? Kind regards, Oliver Brueck ---(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?
On Thu, Feb 17, 2005 at 04:12:38PM +0100, pginfo wrote:
> 01=# select * from a_constants_str where constname='DOCPLAID' ;
> constname | fid | constvalue
> ---+-+
> DOCPLAID | 0 | SOF_19738
> DOCPLAID | 0 | SOF_19738
> (2 rows)
Do you have any inherited tables? What's the result of the following
query?
SELECT tableoid::regclass, *
FROM a_constants_str
WHERE constname = 'DOCPLAID';
Inherited tables are documented to have deficiencies regarding
constraints. Observe:
CREATE TABLE parent (
constname varchar(30) NOT NULL,
fid integer NOT NULL,
constvalue varchar(30),
PRIMARY KEY (constname, fid)
);
CREATE TABLE child () INHERITS (parent);
INSERT INTO parent VALUES ('DOCPLAID', 0, 'SOF_19738');
INSERT INTO parent VALUES ('DOCPLAID', 0, 'SOF_19738');
ERROR: duplicate key violates unique constraint "parent_pkey"
INSERT INTO child VALUES ('DOCPLAID', 0, 'SOF_19738');
SELECT tableoid::regclass, * FROM parent;
tableoid | constname | fid | constvalue
--+---+-+
parent | DOCPLAID | 0 | SOF_19738
child| DOCPLAID | 0 | SOF_19738
(2 rows)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(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,
No, I do not inherited tables.
The result of this query is:
01=# SELECT tableoid::regclass, *
01-# FROM a_constants_str
01-# WHERE constname = 'DOCPLAID';
tableoid | constname | fid | constvalue
-+---+-+
a_constants_str | DOCPLAID | 0 | SOF_19738
a_constants_str | DOCPLAID | 0 | SOF_19738
(2 rows)
regards,
ivan.
Michael Fuhr wrote:
On Thu, Feb 17, 2005 at 04:12:38PM +0100, pginfo wrote:
01=# select * from a_constants_str where constname='DOCPLAID' ;
constname | fid | constvalue
---+-+
DOCPLAID | 0 | SOF_19738
DOCPLAID | 0 | SOF_19738
(2 rows)
Do you have any inherited tables? What's the result of the following
query?
SELECT tableoid::regclass, *
FROM a_constants_str
WHERE constname = 'DOCPLAID';
Inherited tables are documented to have deficiencies regarding
constraints. Observe:
CREATE TABLE parent (
constname varchar(30) NOT NULL,
fid integer NOT NULL,
constvalue varchar(30),
PRIMARY KEY (constname, fid)
);
CREATE TABLE child () INHERITS (parent);
INSERT INTO parent VALUES ('DOCPLAID', 0, 'SOF_19738');
INSERT INTO parent VALUES ('DOCPLAID', 0, 'SOF_19738');
ERROR: duplicate key violates unique constraint "parent_pkey"
INSERT INTO child VALUES ('DOCPLAID', 0, 'SOF_19738');
SELECT tableoid::regclass, * FROM parent;
tableoid | constname | fid | constvalue
--+---+-+
parent | DOCPLAID | 0 | SOF_19738
child| DOCPLAID | 0 | SOF_19738
(2 rows)
Re: [SQL] pg primary key bug?
pginfo <[EMAIL PROTECTED]> writes: > 01=# select * from a_constants_str where constname='DOCPLAID' ; > constname | fid | constvalue > ---+-+ > DOCPLAID | 0 | SOF_19738 > DOCPLAID | 0 | SOF_19738 > (2 rows) Could we see the system columns on these rows? select oid,xmin,cmin,xmax,cmax,ctid,* from a_constants_str where ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] No triggers visible for different user in information_schema.triggers
On Thu, Feb 17, 2005 at 04:58:24PM +0100, Oliver Brück wrote: > > SELECT * FROM information_schema.triggers; > > returns an empty result. I thought this is caused by a lack of > priviliges, but USER1 as well as USER2 are superusers. The PostgreSQL documentation defines information_schema.triggers as: The view triggers contains all triggers defined in the current database that are owned by the current user. (The owner of the table is the owner of the trigger.) SQL:1999 defines the TRIGGERS view as: Identify the triggers in this catalog that are owned by a given user. SQL:2003 (Working Draft) says: Identify the triggers on tables in this catalog that are accessible to a given user or role. Apparently PostgreSQL implements the SQL:1999 specification of information_schema.triggers. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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
Re: [SQL] pg primary key bug?
Hi, Tom Lane wrote: pginfo <[EMAIL PROTECTED]> writes: 01=# select * from a_constants_str where constname='DOCPLAID' ; constname | fid | constvalue ---+-+ DOCPLAID | 0 | SOF_19738 DOCPLAID | 0 | SOF_19738 (2 rows) Could we see the system columns on these rows? select oid,xmin,cmin,xmax,cmax,ctid,* from a_constants_str where ... regards, tom lane yes, 01=# select oid,xmin,cmin,xmax,cmax,ctid,* from a_constants_str; oid | xmin | cmin | xmax | cmax | ctid | constname | fid | constvalue +-+-+-+-+-+---+-+ 17910 | 22331 | 2 | 2 | 26679 | (0,7) | ANMGRID | 0 | SOF_10 17908 | 985 | 6 | 6 | 0 | (0,18) | AKLTYPID | 0 | SOF_3 17907 | 985 | 30 | 30 | 0 | (0,21) | ADARID | 0 | SOF_2 17921 | 985 | 34 | 34 | 0 | (0,22) | AOBLASTID | 0 | SOF_3 17911 | 4640 | 6 | 6 | 26679 | (0,24) | AACCGRID | 0 | SOF_3 17920 | 1220598 | 2 | 2 | 1475630 | (0,47) | ASLUID | 0 | SOF_17 17917 | 643083 | 2 | 2 | 1475630 | (0,49) | LOCAID | 0 | SOF_13 17918 | 762851 | 3 | 3 | 1475630 | (0,50) | AUSERID | 0 | SOF_3 17923 | 35539 | 165 | 165 | 1475630 | (0,51) | ASETUPID | 0 | SOF_399 283686 | 514327 | 3 | 3 | 1475630 | (0,52) | NASTRF | 0 | SOF_1 17909 | 2156667 | 2 | 2 | 2193198 | (0,54) | AKLGRID | 0 | SOF_8 17922 | 2103298 | 2 | 2 | 2193198 | (0,55) | AGRADID | 0 | SOF_165 17913 | 2092705 | 2 | 2 | 2193198 | (0,56) | ANOMID | 0 | SOF_747 63247 | 2226373 | 2 | 2 | 2233003 | (0,126) | TDOCID | 0 | SOF_47 17914 | 2232893 | 2235861 | 2235861 | 3 | (1,125) | DOCID | 0 | SOF_25658 17915 | 2232893 | 2235861 | 2235861 | 19 | (3,38) | DOCRID | 0 | SOF_184547 17916 | 2232893 | 2235861 | 2235861 | 42 | (4,71) | DOCPLAID | 0 | SOF_19738 17919 | 2232893 | 231 | 231 | 2233003 | (5,104) | DOCPOGPLA | 0 | SOF_24281 17912 | 2231139 | 2 | 2 | 0 | (6,1) | AKLIID | 0 | SOF_3513 17916 | 2232893 | 2235861 | 2235861 | 41 | (7,62) | DOCPLAID | 0 | SOF_19738 63249 | 2226373 | 103 | 103 | 0 | (16,31) | TDOCRID | 0 | SOF_2439 (21 rows) and 01=# select oid,xmin,cmin,xmax,cmax,ctid,* from a_constants_str where constname='DOCPLAID'; oid | xmin | cmin | xmax | cmax | ctid | constname | fid | constvalue ---+-+-+-+--++---+-+ 17916 | 2232893 | 2235861 | 2235861 | 42 | (4,71) | DOCPLAID | 0 | SOF_19738 17916 | 2232893 | 2235861 | 2235861 | 41 | (7,62) | DOCPLAID | 0 | SOF_19738 (2 rows) regards, ivan.
[SQL] LOOP?
I am writing a trigger to inspect a row of a temporary table to determine if
the row has any null columns, I will return as soon as I find a null
column. I ran across this in the manual and It seems like I could use
The FOR-IN-EXECUTE statement is another way to iterate over rows:
[<>]
FOR record_or_row IN EXECUTE text_expression LOOP
statements
END LOOP;
postgresql has a RECORD type which is an abstract row.
Does it have any fields to tell the length?
Does it have an accessor function like new.element(index};
I am trying to write a general function where from instance to instance the
columns in the record change. If I can't do this I will have to create a
new function for each temporary table.
Is this possible or should I quit looking;
FOR record IN {something related to NEW } LOOP
END LOOP;
---(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
[SQL] Comments on subquery performance
(second attempt in two days to post this message...I appologise if for some reason a duplicate appears) Hello all, I created a query that contains two subqueries and joins and would like some feedback on whether: 1) this approach is logical; and, 2) if this is an optimal approach (performance wise) to return the records I seek. Essentially, I am attempting to perform a 'lookup' on a value in another table 3 times, for three different columns. I have three columns with category codes in tblListings and would like to 'lookup' the actual category text in tblCategory. I have created a functional query that contains two subqueries and joins, but am concerned this may not be the fastest (or logical?) way to achieve what I seek. I thought of another approach to create a function to evaluate the records on a row-by-row and column-by-column approach, but thought that may prove even slower. I would appreciate any comments on my logic or learning of any alternative means that would result in better performance. I have included the SQL for reference. Thanks in advance. Regards, Jim 8<- SELECT "CatID1", "CatID2", "CatID3", c1 AS "CatName1", c2 AS "CatName2", t6."CatName" AS "CatName3" FROM (SELECT "CatID1", "CatID2", "CatID3", c1, t4."CatName" AS c2 FROM (SELECT t1."CatID1", t1."CatID2", t1."CatID3", t2."CatName" AS c1 FROM "MySchema"."tblListings" t1 INNER JOIN "MySchema"."tblCategories" t2 ON (t1."CatID1" = t2."CatID")) t3 LEFT OUTER JOIN "MySchema"."tblCategories" t4 ON (t3."CatID2" = t4."CatID")) t5 LEFT OUTER JOIN "MySchema"."tblCategories" t6 ON (t5."CatID3" = t6."CatID"); 8<- _ Take advantage of powerful junk e-mail filters built on patented Microsoft® SmartScreen Technology. http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines Start enjoying all the benefits of MSN® Premium right now and get the first two months FREE*. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] LOOP?
Ray Madigan wrote: I am writing a trigger to inspect a row of a temporary table to determine if the row has any null columns, I will return as soon as I find a null column. I ran across this in the manual and It seems like I could use The FOR-IN-EXECUTE statement is another way to iterate over rows: [<>] FOR record_or_row IN EXECUTE text_expression LOOP statements END LOOP; postgresql has a RECORD type which is an abstract row. Does it have any fields to tell the length? Does it have an accessor function like new.element(index}; Short answer - no to the above. You'll not find what you want in plpgsql, perhaps look at pltcl or plperl for this sort of thing. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] pg primary key bug?
pginfo <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Could we see the system columns on these rows? > 01=# select oid,xmin,cmin,xmax,cmax,ctid,* from a_constants_str where > constname='DOCPLAID'; > oid | xmin | cmin | xmax | cmax | ctid | constname | fid | > constvalue > ---+-+-+-+--++---+-+ > 17916 | 2232893 | 2235861 | 2235861 | 42 | (4,71) | DOCPLAID | 0 | > SOF_19738 > 17916 | 2232893 | 2235861 | 2235861 | 41 | (7,62) | DOCPLAID | 0 | > SOF_19738 > (2 rows) Given the identical OID and xmin values, it seems certain that these are the "same" row, ie there was only one insertion event. My bet is that the one at (7,62) is the original, and that the one at (4,71) is a copy that was made by VACUUM FULL trying to move the row to compact the table. So the question is how did both copies get to be marked simultaneously valid? That should be impossible, unless a disk write got dropped. Have you had any system crashes during VACUUM FULL operations recently? 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 Lane wrote: pginfo <[EMAIL PROTECTED]> writes: Tom Lane wrote: Could we see the system columns on these rows? 01=# select oid,xmin,cmin,xmax,cmax,ctid,* from a_constants_str where constname='DOCPLAID'; oid | xmin | cmin | xmax | cmax | ctid | constname | fid | constvalue ---+-+-+-+--++---+-+ 17916 | 2232893 | 2235861 | 2235861 | 42 | (4,71) | DOCPLAID | 0 | SOF_19738 17916 | 2232893 | 2235861 | 2235861 | 41 | (7,62) | DOCPLAID | 0 | SOF_19738 (2 rows) Given the identical OID and xmin values, it seems certain that these are the "same" row, ie there was only one insertion event. My bet is that the one at (7,62) is the original, and that the one at (4,71) is a copy that was made by VACUUM FULL trying to move the row to compact the table. So the question is how did both copies get to be marked simultaneously valid? That should be impossible, unless a disk write got dropped. Have you had any system crashes during VACUUM FULL operations recently? regards, tom lane I do not know exact, but it is possible. This is customer server and I do not have any info about server/os crash, but I am 100% sure. I will ask as soon as possible and replay. Also I will check the logs. We start pg witl pg_ctl ...-D... -l /logfile and if we can found the needet info in pg log file I can check it ( I do not nkow what to search in this file). We are running vacuum once per 24 h. The OS is RedHat AS 3. PostgreSQL is 7.4.1 (but we have the same problem on 7.4.5). Will upgrade to 8.0 solve this type of problems ? regards, ivan.
Re: [SQL] pg primary key bug?
pginfo <[EMAIL PROTECTED]> writes: > Will upgrade to 8.0 solve this type of problems ? The problem is probably not Postgres' fault at all. I'm wondering about disks with write cacheing enabled. And you didn't turn off fsync, I trust? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Comments on subquery performance
T- Bone wrote: (second attempt in two days to post this message...I appologise if for some reason a duplicate appears) Hello all, I created a query that contains two subqueries and joins and would like some feedback on whether: 1) this approach is logical; and, 2) if this is an optimal approach (performance wise) to return the records I seek. Well you could just do: SELECT l.*, c1.catname, c2.catname, c3.catname FROM tbl_listing l, tbl_categories c1, tbl_categories c2, tbl_categories c3 WHERE l.catid1 = c1.catid AND l.catid2 = c2.catid AND l.catid3 = c3.catid -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] pg primary key bug?
Hi, Tom Lane wrote: pginfo <[EMAIL PROTECTED]> writes: Will upgrade to 8.0 solve this type of problems ? The problem is probably not Postgres' fault at all. I'm wondering about disks with write cacheing enabled. And you didn't turn off fsync, I trust? About fsync (part from postgresql.conf) : #--- # WRITE AHEAD LOG #--- # - Settings - #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync # the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync #wal_buffers = 8 # min 4, 8KB each # - Checkpoints - #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 300 # range 30-3600, in seconds #checkpoint_warning = 30 # 0 is off, in seconds Also part from pg logfile: LOG: statistics collector process (PID 2716) exited with exit code 1 LOG: shutting down LOG: database system is shut down LOG: could not create IPv6 socket: Address family not supported by protocol LOG: database system was shut down at 2005-02-11 19:58:26 EET LOG: checkpoint record is at 2/BAC39188 LOG: redo record is at 2/BAC39188; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 2221145; next OID: 826607 LOG: database system is ready LOG: recycled transaction log file "000200BA" LOG: recycled transaction log file "000200BB" LOG: recycled transaction log file "000200BC" LOG: recycled transaction log file "000200BD" LOG: recycled transaction log file "000200BE" WARNING: index "a_constants_str_pkey" contains 1449 row versions, but table contains 1422 row versions HINT: Rebuild the index with REINDEX. ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" LOG: received smart shutdown request FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command LOG: statistics collector process (PID 2713) exited with exit code 1 LOG: shutting down LOG: database system is shut down LOG: could not create IPv6 socket: Address family not supported by protocol LOG: database system was shut down at 2005-02-16 08:32:21 EET LOG: checkpoint record is at 2/BFAE09EC LOG: redo record is at 2/BFAE09EC; undo record is at 0/0; shutdown TRUE Note we was informed about the problem on 2005-02-16 and rebooted the box.
Re: [SQL] pg primary key bug?
Hi Ivan, Sorry, I can't remember all you said in earlier posts, but I was wondering, your log file says: > HINT: Rebuild the index with REINDEX. Did you do that, and did it solve the problem? regards Iain - Original Message - From: pginfo To: Tom Lane Cc: [EMAIL PROTECTED] ; Michael Glaesemann ; pgsql-sql@postgresql.org ; [EMAIL PROTECTED] Sent: Friday, February 18, 2005 1:53 PM Subject: Re: [SQL] pg primary key bug? Hi,Tom Lane wrote: pginfo <[EMAIL PROTECTED]> writes: Will upgrade to 8.0 solve this type of problems ? The problem is probably not Postgres' fault at all. I'm wondering about disks with write cacheing enabled. And you didn't turn off fsync, I trust? About fsync (part from postgresql.conf) :#---# WRITE AHEAD LOG#---# - Settings -#fsync = true # turns forced synchronization on or off#wal_sync_method = fsync # the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync#wal_buffers = 8 # min 4, 8KB each# - Checkpoints -#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each#checkpoint_timeout = 300 # range 30-3600, in seconds#checkpoint_warning = 30 # 0 is off, in secondsAlso part from pg logfile:LOG: statistics collector process (PID 2716) exited with exit code 1LOG: shutting downLOG: database system is shut downLOG: could not create IPv6 socket: Address family not supported by protocolLOG: database system was shut down at 2005-02-11 19:58:26 EETLOG: checkpoint record is at 2/BAC39188LOG: redo record is at 2/BAC39188; undo record is at 0/0; shutdown TRUELOG: next transaction ID: 2221145; next OID: 826607LOG: database system is readyLOG: recycled transaction log file "000200BA"LOG: recycled transaction log file "000200BB"LOG: recycled transaction log file "000200BC"LOG: recycled transaction log file "000200BD"LOG: recycled transaction log file "000200BE"WARNING: index "a_constants_str_pkey" contains 1449 row versions, but table contains 1422 row versionsHINT: Rebuild the index with REINDEX.ERROR: duplicate key violates unique constraint "a_constants_str_pkey"ERROR: duplicate key violates unique constraint "a_constants_str_pkey"ERROR: duplicate key violates unique constraint "a_constants_str_pkey"ERROR: duplicate key violates unique constraint "a_constants_str_pkey"ERROR: duplicate key violates unique constraint "a_constants_str_pkey"ERROR: duplicate key violates unique constraint "a_constants_str_pkey"ERROR: duplicate key violates unique constraint "a_constants_str_pkey"ERROR: duplicate key violates unique constraint "a_constants_str_pkey"ERROR: duplicate key violates unique constraint "a_constants_str_pkey"ERROR: duplicate key violates unique constraint "a_constants_str_pkey"ERROR: duplicate key violates unique constraint "a_constants_str_pkey"LOG: received smart shutdown requestFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to administrator commandFATAL: terminating connection due to admi
Re: [SQL] pg primary key bug?
Hi Iain, Iain wrote: Hi Ivan, Sorry, I can't remember all you said in earlier posts, but I was wondering, your log file says: > HINT: Rebuild the index with REINDEX. Did you do that, and did it solve the problem? No it do not solve the problem. I sendet the log only to show that we do not have any server crash nor pg restart. regards, ivan. regards Iain - Original Message - From: pginfo To: Tom Lane Cc: [EMAIL PROTECTED] ; Michael Glaesemann ; pgsql-sql@postgresql.org ; [EMAIL PROTECTED] Sent: Friday, February 18, 20051:53 PM Subject: Re: [SQL] pg primary keybug? Hi, Tom Lane wrote: pginfo <[EMAIL PROTECTED]> writes: Will upgrade to 8.0 solve this type of problems ? The problem is probably not Postgres' fault at all. I'm wondering about disks with write cacheing enabled. And you didn't turn off fsync, I trust? About fsync (part from postgresql.conf): #--- #WRITE AHEADLOG #--- #- Settings - #fsync =true # turns forced synchronization on or off #wal_sync_method =fsync # the default varies acrossplatforms: # fsync, fdatasync, open_sync, or open_datasync #wal_buffers =8 # min 4, 8KB each # - Checkpoints - #checkpoint_segments =3 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 300 # range30-3600, in seconds #checkpoint_warning =30 # 0 is off, inseconds Also part from pg logfile: LOG: statisticscollector process (PID 2716) exited with exit code 1 LOG: shuttingdown LOG: database system is shut down LOG: could not createIPv6 socket: Address family not supported by protocol LOG: databasesystem was shut down at 2005-02-11 19:58:26 EET LOG: checkpointrecord is at 2/BAC39188 LOG: redo record is at 2/BAC39188; undorecord is at 0/0; shutdown TRUE LOG: next transaction ID: 2221145;next OID: 826607 LOG: database system is ready LOG: recycledtransaction log file "000200BA" LOG: recycled transaction logfile "000200BB" LOG: recycled transaction log file"000200BC" LOG: recycled transaction log file"000200BD" LOG: recycled transaction log file"000200BE" WARNING: index "a_constants_str_pkey" contains1449 row versions, but table contains 1422 row versions HINT: Rebuildthe index with REINDEX. ERROR: duplicate key violates uniqueconstraint "a_constants_str_pkey" ERROR: duplicate key violatesunique constraint "a_constants_str_pkey" ERROR: duplicate keyviolates unique constraint "a_constants_str_pkey" ERROR: duplicatekey violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint"a_constants_str_pkey" ERROR: duplicate key violates uniqueconstraint "a_constants_str_pkey" ERROR: duplicate key violatesunique constraint "a_constants_str_pkey" ERROR: duplicate keyviolates unique constraint "a_constants_str_pkey" ERROR: duplicatekey violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint"a_constants_str_pkey" ERROR: duplicate key violates uniqueconstraint "a_constants_str_pkey" LOG: received smart shutdownrequest FATAL: terminating connection due to administratorcommand FATAL: terminating connection due to administratorcommand FATAL: terminating connection due to administratorcommand FATAL: terminating connection due to administratorcommand FATAL: terminating connection due to administratorcommand FATAL: terminating connection due to administratorcommand FATAL: terminating connection due to administratorcommand FATAL: terminating connection due to administratorcommand FATAL: terminating connection due to administratorcommand FATAL: terminating connection due to administratorcommand FATAL: terminating connection due to administratorcommand FATAL: terminating connection due to administratorcommand FATAL: terminating connection due to administratorcommand FATAL: terminating connection due to administratorcommand FATAL: terminating connection due to administratorcommand FATAL: terminating connection due to administratorcommand FATAL: terminating connection due to administratorcommand FATAL: terminating connection due to administratorcommand FATAL: terminating connection due to administratorcommand FATAL: terminating connection due to administratorcommand FATAL: terminating connection due
