http://blog.gmane.org/gmane.comp.db.postgresql.general hourly 1 1901-01-01T00:00+00:00 http://gmane.org/img/gmane-25t.png http://gmane.org http://comments.gmane.org/gmane.comp.db.postgresql.general/172818 <pre>Client reported an issue where it appears a foreign key has been violated prod=#\d rma_items [snip] rma_items_rma_id_status_fk" FOREIGN KEY (rma_id, rma_status) REFERENCES rmas(id, status) ON UPDATE CASCADE ON DELETE CASCADE prod=# select i.rma_id, i.rma_status, r.id, r.status from rmas r join rma_items i on i.rma_id = r.id and i.rma_status != r.status; rma_id | rma_status | id | status ------------+------------+------------+-------- 1008122437 | r | 1008122437 | c (1 row) Attempting to reinsert this data again causes a violation error, so it doesn't appear to be broken prod=# begin; BEGIN prod=# insert into rma_items (rma_id, order_item_id, return_reason_id, rma_status) values (1008122437, 1007674099, 9797623, 'r'); ERROR: insert or update on table "rma_items" violates foreign key constraint "rma_items_rma_id_status_fk" DETAIL: Key (rma_id, rma_status)=(1008122437, r) is not present in table "rmas". prod=# rollback; ROLLBACK This is running 9.2.4 on CentOS. If anyone can suggest how I can look into this deeper and find what the problem may be, I'd appreciate it. I'm here at PGCon if anyone is available to help IRL as well -- Keith Fiske Database Administrator OmniTI Computer Consulting, Inc. http://www.keithf4.com </pre> Keith Fiske 2013-05-23T14:15:09 http://comments.gmane.org/gmane.comp.db.postgresql.general/172811 <pre>Greetings! Another post on this list suggested using a DO block if the user's Postgres version is 9.0 or later. The documentation for the DO block says what it is, but not what it is for. The only benefit I could see for it is allowing the use of locally defined variables. I'm sure there's more to it than that. What justifies the existence of the DO block? The message that mentioned the DO block is quoted below as an example. Thanks very much! RobR -----Original Message----- From: pgsql-general-owner&lt; at &gt;postgresql.org [mailto:pgsql-general-owner&lt; at &gt;postgresql.org] On Behalf Of Sergey Konoplev Sent: Thursday, May 23, 2013 2:14 AM To: Sajeev Mayandi Cc: pgsql-general&lt; at &gt;postgresql.org Subject: Re: [GENERAL] Rule for all the tables in a schema On Wed, May 22, 2013 at 10:34 PM, Sajeev Mayandi &lt;Sajeev_Mayandi&lt; at &gt;symantec.com&gt; wrote: You can use DO block if your postgres version is &gt;=9.0. DO $$ DECLARE _tablename text BEGIN FOR SELECT INTO _tablename tablename FROM pg_tables WHERE schemaname = 'schemaname' LOOP EXECUTE 'CREATE RULE ... TO $1 ...' USING _tablename; END LOOP; END $$; For &lt;9.0 you can use shell script with psql to do the same. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray.ru&lt; at &gt;gmail.com -- Sent via pgsql-general mailing list (pgsql-general&lt; at &gt;postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general </pre> Rob Richardson 2013-05-23T12:58:30 http://comments.gmane.org/gmane.comp.db.postgresql.general/172808 <pre>I did: CREATE TYPE telephone AS ( area text, number text, ext text ); Then: CREATE TABLE directory ( id integer, tel telephone, faxes telephone[] ); After some data has been entered, I tried: UPDATE directory SET tel = ROW('11', '2222222', '333') WHERE id = 1; UPDATE directory SET faxes[1] = ROW('11', '2222222', '333') WHERE id = 1; both worked fine, but: UPDATE directory SET faxes = ARRAY[ROW('11', '2222222', '333'), ROW('44', '555', '666')] WHERE id = 1; has failed: (You will need to rewrite or cast the expression) with arrow pointing to ARRAY. Does anybody know how to overcome it? Please note, I'm not seeking an alternative, as I can still do the following: UPDATE directory SET faxes = '{(11\,2222222\,333),(44\,555\,666)}' WHERE id=1; I would like, however, do the same with ARRAY/ROW. W </pre> Wojciech Skaba 2013-05-23T09:40:37 http://comments.gmane.org/gmane.comp.db.postgresql.general/172802 <pre>Hi, I am in the process of creating a rule that provides upsert functionality for all the tables which has primary key. The issue here is we have some 50 to 60 odd tables and have to write a functions that iterates through all these tables , create rules for each of this table, so that the rule body provides upsert functionality for every one of them. Is there a way, I can say create a rule for all the tables in an schema? This will avoid writing complicated functions. Thanks, Sajeev </pre> Sajeev Mayandi 2013-05-23T05:34:46 http://comments.gmane.org/gmane.comp.db.postgresql.general/172801 <pre>Hi, (We're running postgres 9.2.X) from reading http://www.postgresql.org/docs/9.2/static/storage-file-layout.html the directories under $PG_DATADIR/data/base should correspond to an actual database. I've however found a few directories in $PG_DATADIR/data/base where select datname from pg_database where oid = &lt;oid&gt; returns 0 rows. The machine does have "high churn" on databases and have suffered a few crashes, is it possible for things to get left behind in $PG_DATADIR/data/base after recovery ? or am I possibly missing things still in use, that's reflected elsewhere ? Additionally, the directory and it's contents haven't been touched in quite a while as well, and some of them do correspond to being touched at the machine having had a hard crash. The reason I'm asking is that space used as reported by pg_database_size is off by a LOT compared to df on the filesystem (4-5TB as oposed to df/du agreeing on 9-ish TB). Cheers, </pre> Kjetil Jørgensen 2013-05-21T20:21:51 http://comments.gmane.org/gmane.comp.db.postgresql.general/172790 <pre>Hi, I have the following simple query on a simple table: system=# select * from history where lookup = 'trunk' and lookupid = '248' order by created desc limit 1000; system=# \d history Table "public.history" Column | Type | Modifiers ----------+--------------------------+------------------------------------------------------ id | integer | not null default nextval('history_id_seq'::regclass) created | timestamp with time zone | creator | integer | not null default 1 contact | integer | not null default 1 type | character varying | not null default ''::character varying lookup | text | lookupid | integer | not null default 1 value | text | Indexes: "history_pkey" PRIMARY KEY, btree (id) "history_created_index" btree (created) "history_creator_index" btree (creator) "history_lookup_lookupid_creator_index" btree (lookup, lookupid, creator) "history_lookup_lookupid_index" btree (lookup, lookupid) Foreign-key constraints: "history_contact_constraint" FOREIGN KEY (contact) REFERENCES contact(id) "history_creator_constraint" FOREIGN KEY (creator) REFERENCES contact(id) system=# explain select * from history where lookup = 'trunk' and lookupid = '248' order by created desc limit 1000; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..14799.28 rows=1000 width=58) -&gt; Index Scan Backward using history_created_index on history (cost=0.00..12201987.90 rows=824499 width=58) Filter: ((lookup = 'trunk'::text) AND (lookupid = 248)) (3 rows) Why doesn't it use the index specified in select? How can I change the query to make it faster? Thanks, Antonio </pre> Antonio Goméz Soto 2013-05-22T19:38:24 http://comments.gmane.org/gmane.comp.db.postgresql.general/172788 <pre>Hi All, We are facing one strange problem about data file corruptions. We have many postgres databases. At some point, one simple query on one database started crashing back-end. The query is select count(*), col1 from tab1 group by col1; After using pg_filedump (http://pgfoundry.org/projects/pgfiledump/) on data files for tab1 (relnodeid in pg_class), we found that the number of attributes per tuple is different for few tuples in data file than the rest. pg_filedump utility prints the contents of each tuple along with block header, data header. In our case, the same data file has the following two data headers. valid header ============ &lt;Data&gt; ------ Item 1 – Length: 114 Offset: 32648 (0x7f88) Flags: NORMAL XMIN: 8849668 XMAX: 0 CID|XVAC: 0 Block Id: 0 linp Index: 1 Attributes: 10 Size: 24 infomask: 0x0902 (HASVARWIDTH|XMIN_COMMITTED|XMAX_INVALID) Invalid header ========== &lt;Data&gt; ------ Item 1 – Length: 234 Offset: 32528 (0x7f10) Flags: NORMAL XMIN: 2959623 XMAX: 0 CID|XVAC: 0 Block Id: 155 linp Index: 1 *Attributes: 92* Size: 40 infomask: 0x0903 (HASNULL|HASVARWIDTH|XMIN_COMMITTED|XMAX_INVALID) As you can see, # of attributes and XMIN are different. I have few questions regarding this. 1. When such case can occur? 2. Later we found that invalid header is actually valid header for other table's data file. So somehow data of one table got inserted into another and both tables have different # of attributes. Can this be possible? Any hardware issue can cause this? 3. Has anybody seen this problem? Thanking you. - Nachiket </pre> PG User 2013-05-22T19:03:20 http://comments.gmane.org/gmane.comp.db.postgresql.general/172778 <pre>I'm not sure if this is a bug, or something I'm not understanding. When I have a column referenced in the select both fully qualified and not fully qualified, an explicit inner join, and that column in the order by (unqualified, and not in a function call), it complains that is ambiguous: create table test1 (id integer, sortable_name varchar); create table test2 (id integer, test1_id integer); select test1.sortable_name, sortable_name from test1 inner join test2 on test1.id=test1_id order by sortable_name; ERROR: ORDER BY "sortable_name" is ambiguous LINE 1: ...t1 inner join test2 on test1.id=test1_id order by sortable_n... All of these work: select test1.sortable_name, sortable_name from test1 order by sortable_name /* no join */ select test1.sortable_name, sortable_name from test1, test2 where test1.id=test1_id order by sortable_name; /* implicit join */ select test1.sortable_name from test1 inner join test2 on test1.id=test1_id order by sortable_name /* only one sortable_name in select */ select test1.sortable_name, sortable_name from test1 inner join test2 on test1.id=test1_id order by test1.sortable_name /* qualify sortable_name in order, but not select */ select test1.sortable_name, test1.sortable_name from test1 inner join test2 on test1.id=test1_id order by sortable_name /* qualify sortable_name both selects */ select sortable_name, sortable_name from test1 inner join test2 on test1.id=test1_id order by sortable_name /* unqualified everywhere */ select test1.sortable_name, sortable_name from test1 inner join test2 on test1.id=test1_id order by substring(sortable_name,1,2); /* use a function in the order */ I've tried this on 9.1.4, 9.1.9, and 9.2.3. The actual application usage looks more like SELECT users.*, sortable_name FROM users &lt;single join and multiple where clauses&gt; ORDER BY sortable_name. The application code always appends sortable_name to the select list because, depending on available features, sortable_name might be a function call and in a GROUP BY. Thanks for any insight, Cody Cutrer </pre> Cody Cutrer 2013-05-22T16:41:12 http://comments.gmane.org/gmane.comp.db.postgresql.general/172775 <pre>I have a database that is on a RAID5 machine that is almost out of memory (277GB of 330GB used). I have deleted some data and run the VACUUM FULL command, but after a few hours gave me the error message "Server closed the connection unexpectedly. This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed." I know that the server did not fail, so i assume that this timed out. The server is running version 7.4 (i know it's an old version). The database has several tables, but 99% of the data is located in a single table. My fear is that there's not enough system memory left to perform the full vacuum since it's all contained in a single table. Any ideas? -- View this message in context: http://postgresql.1045698.n5.nabble.com/VACUUM-FULL-freezes-tp5756477.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. </pre> RDNikeAir 2013-05-22T14:49:06 http://comments.gmane.org/gmane.comp.db.postgresql.general/172767 <pre>hello , I'm restoring a 7 days ago full backup + wal files. I have to recover more than 6k wal files and I have no time, I would accept to work with a ~ 3 days ago snapshot, can I stop the recovery process and start the server?, if yes, how? My recovery.conf file only has this line : restore_command = 'envdir /etc/wal-e.d/env /etc/wal-e.d/wal-e wal-fetch "%f" "%p"' Thanks in advance. </pre> Fabio Rueda Carrascosa 2013-05-22T10:08:59 http://comments.gmane.org/gmane.comp.db.postgresql.general/172764 <pre>Hi, I am using postgresql 8.1 (CentOS5). I have the following table: system # \d history Table "public.history" Column | Type | Modifiers ----------+--------------------------+------------------------------------------------------ id | integer | not null default nextval('history_id_seq'::regclass) created | timestamp with time zone | creator | integer | not null default 1 contact | integer | not null default 1 type | character varying | not null default ''::character varying lookup | text | lookupid | integer | not null default 1 value | text | Indexes: "history_pkey" PRIMARY KEY, btree (id) "history_created_index" btree (created) "history_creator_index" btree (creator) "history_lookup_lookupid_creator_index" btree (lookup, lookupid, creator) "history_lookup_lookupid_index" btree (lookup, lookupid) Foreign-key constraints: "history_contact_constraint" FOREIGN KEY (contact) REFERENCES contact(id) "history_creator_constraint" FOREIGN KEY (creator) REFERENCES contact(id) system # explain select history.id, history.created, creator, contact, history.type, lookup, lookupid, value from history where (lookup = 'phone' and lookupid = '672') or creator = '790' order by history.creator desc limit 1000; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..132041.59 rows=1000 width=58) -&gt; Index Scan Backward using history_creator_index on history (cost=0.00..11746815.97 rows=88963 width=58) Filter: (((lookup = 'phone'::text) AND (lookupid = 672)) OR (creator = 790)) (3 rows) This table contains 2 million rows, the query takes 800 seconds on SSD HD. I think - probably naive - the query should use the history_lookup_lookupid_creator_index. Why doesn't it, and how can I speed up the query? Thanks, Antonio. </pre> Antonio Goméz Soto 2013-05-22T08:50:13 http://comments.gmane.org/gmane.comp.db.postgresql.general/172746 <pre>It's my first time using PostgreSQL and I need to do basic things as establishing a new user,create a new database,access psql command line client etc. I tried : su - postgres to access the server but I don't have a valid password,I don't know if the server is requesting the postgres system account password,or the database one. I installed PGSQL that come with Oracle Linux 6.4 and don't know what is the PGSQL default configuration. Basically,I start the server with: /etc/init.d/postgresql start ,and I am stuck about what to do next,and with permissions,authentications etc. Note: I changed in pg_hba.conf authorizations (ident to trust) but still no access to a workable PGSQL account. </pre> luis redondo 2013-05-22T02:15:18 http://comments.gmane.org/gmane.comp.db.postgresql.general/172731 <pre>Hi, We have following situation: Pg master on US east coast. Pg slave on US west coast. Replication set using omnipitr + streaming replication. Setup on slave: postgres=# select name, setting from pg_settings where name ~ '(checkpo|wal)'; name | setting ------------------------------+------------- checkpoint_completion_target | 0.9 checkpoint_segments | 30 checkpoint_timeout | 300 checkpoint_warning | 30 log_checkpoints | on max_wal_senders | 3 wal_block_size | 8192 wal_buffers | 2048 wal_keep_segments | 0 wal_level | hot_standby wal_receiver_status_interval | 10 wal_segment_size | 2048 wal_sync_method | fdatasync wal_writer_delay | 200 (14 rows) Replication is working usually well: $ grep -c 'FATAL:..could not receive data from WAL stream:' postgresql-2013-05-* postgresql-2013-05-14.log:7 postgresql-2013-05-15.log:7 postgresql-2013-05-16.log:9 postgresql-2013-05-17.log:9 postgresql-2013-05-18.log:8 postgresql-2013-05-19.log:8 postgresql-2013-05-20.log:7 postgresql-2013-05-21.log:7 After each such break, Pg switches to omnipitr, which does recover some wal files, and then it switches back to SR. All looks fine. But. In pg_xlog on slave we have lots of files - almost 500 now. I.e. WAL segments. What's weird is their numbering. List of 500 files is too big for now, so just last 50: pg_xlog$ ls -l | tail -n 50 -rw------- 1 postgres postgres 16777216 May 16 22:41 000000010000008B00000009 -rw------- 1 postgres postgres 16777216 May 17 01:16 000000010000008B00000029 -rw------- 1 postgres postgres 16777216 May 17 03:56 000000010000008B00000049 -rw------- 1 postgres postgres 16777216 May 17 06:36 000000010000008B00000069 -rw------- 1 postgres postgres 16777216 May 17 09:16 000000010000008B00000089 -rw------- 1 postgres postgres 16777216 May 17 11:56 000000010000008B000000A9 -rw------- 1 postgres postgres 16777216 May 17 14:36 000000010000008B000000C9 -rw------- 1 postgres postgres 16777216 May 17 17:16 000000010000008B000000E9 -rw------- 1 postgres postgres 16777216 May 17 19:56 000000010000008C0000000A -rw------- 1 postgres postgres 16777216 May 17 22:36 000000010000008C0000002A -rw------- 1 postgres postgres 16777216 May 18 01:12 000000010000008C0000004A -rw------- 1 postgres postgres 16777216 May 18 03:52 000000010000008C0000006A -rw------- 1 postgres postgres 16777216 May 18 06:32 000000010000008C0000008A -rw------- 1 postgres postgres 16777216 May 18 09:13 000000010000008C000000AA -rw------- 1 postgres postgres 16777216 May 18 14:33 000000010000008C000000EA -rw------- 1 postgres postgres 16777216 May 18 17:13 000000010000008D0000000B -rw------- 1 postgres postgres 16777216 May 18 19:53 000000010000008D0000002B -rw------- 1 postgres postgres 16777216 May 18 22:33 000000010000008D0000004B -rw------- 1 postgres postgres 16777216 May 19 01:05 000000010000008D0000006B -rw------- 1 postgres postgres 16777216 May 19 03:45 000000010000008D0000008B -rw------- 1 postgres postgres 16777216 May 19 06:25 000000010000008D000000AB -rw------- 1 postgres postgres 16777216 May 19 09:05 000000010000008D000000CB -rw------- 1 postgres postgres 16777216 May 19 11:45 000000010000008D000000EB -rw------- 1 postgres postgres 16777216 May 19 14:25 000000010000008E0000000C -rw------- 1 postgres postgres 16777216 May 19 17:05 000000010000008E0000002C -rw------- 1 postgres postgres 16777216 May 19 19:45 000000010000008E0000004C -rw------- 1 postgres postgres 16777216 May 19 22:25 000000010000008E0000006C -rw------- 1 postgres postgres 16777216 May 20 01:01 000000010000008E0000008C -rw------- 1 postgres postgres 16777216 May 20 03:41 000000010000008E000000AC -rw------- 1 postgres postgres 16777216 May 20 06:21 000000010000008E000000CC -rw------- 1 postgres postgres 16777216 May 20 09:01 000000010000008E000000EC -rw------- 1 postgres postgres 16777216 May 20 11:41 000000010000008F0000000D -rw------- 1 postgres postgres 16777216 May 20 14:21 000000010000008F0000002D -rw------- 1 postgres postgres 16777216 May 20 17:01 000000010000008F0000004D -rw------- 1 postgres postgres 16777216 May 20 19:41 000000010000008F0000006D -rw------- 1 postgres postgres 16777216 May 20 22:21 000000010000008F0000008D -rw------- 1 postgres postgres 16777216 May 21 01:00 000000010000008F000000AD -rw------- 1 postgres postgres 16777216 May 21 03:35 000000010000008F000000CD -rw------- 1 postgres postgres 16777216 May 21 06:15 000000010000008F000000ED -rw------- 1 postgres postgres 16777216 May 21 08:55 00000001000000900000000E -rw------- 1 postgres postgres 16777216 May 21 11:35 00000001000000900000002E -rw------- 1 postgres postgres 16777216 May 21 14:15 00000001000000900000004E -rw------- 1 postgres postgres 16777216 May 21 16:55 00000001000000900000006E -rw------- 1 postgres postgres 16777216 May 21 19:35 00000001000000900000008E -rw------- 1 postgres postgres 16777216 May 21 20:00 000000010000009000000093 -rw------- 1 postgres postgres 16777216 May 21 20:05 000000010000009000000094 -rw------- 1 postgres postgres 16777216 May 21 20:10 000000010000009000000095 -rw------- 1 postgres postgres 16777216 May 21 20:14 000000010000009000000096 -rw------- 1 postgres postgres 16777216 May 21 19:55 000000010000009000000097 drwx------ 2 postgres postgres 28672 May 21 20:10 archive_status Aside from couple of last ones, every 32nd wal segment is kept for some reason. Pg is 9.2.4. All these "older" (that is aside from 5 newest) have *.ready 0-byte files in archive_status. archiving on slave is /bin/true only: postgres=# select name, setting from pg_settings where name ~ '(archive)'; name | setting ---------------------------+----------- archive_command | /bin/true archive_mode | on archive_timeout | 3600 max_standby_archive_delay | 30 (4 rows) I am at loss, no idea what could be wrong. I mean - sure, I can remove all those old files, and we'll be fine, but new ones will most likely accumulate, and removing them by hand, or from a cronjob is kind of pointless. Is there anything I could check that could lead to finding cause of this weird behavior? Best regards, depesz </pre> hubert depesz lubaczewski 2013-05-21T20:17:44 http://comments.gmane.org/gmane.comp.db.postgresql.general/172721 <pre>We have a long-running stored proc that duplicates a particular row in table called tb_location_map and also duplicates all related rows in several other tables. The new records created contain foreign keys to our user data table, tb_entity. However there is no other reference to tb_entity in the cloning function &lt;http://pastebin.com/uU0S9x71&gt;. We are seeing in SOME cases, but not all, that during the execution of this function, that the corresponding row in tb_entity is locked with a ROW SHARE lock (according to tb_locks, searching by the user's PID and tb_entity's relation ID), which is causing updates to the user's data to block while waiting for the map clone to finish. What could be causing this ROW SHARE lock to be in place for some cloning operations and not others? Perhaps there is a way to see which specific statement is causing the lock to occur, but I'm not sure how to look this up. Any help would be appreciated. Thanks! </pre> Moshe Jacobson 2013-05-21T18:39:57 http://comments.gmane.org/gmane.comp.db.postgresql.general/172718 <pre>I am wanting to partition my data based on a mod of one of the bigint columns, but when I run my insert test all the data goes into the base table and not the partitions.  Here is what the table looks like: CREATE table MyMappingTable ( id bigserial NOT NULL,                                      c1 bigInt NOT NULL,                                      c2 bigInt NOT NULL,                                      c3 bigint NOT NULL,                                      count bigint DEFAULT 1,                                      createdTime timestamp with time zone default CURRENT_TIMESTAMP,                                      CONSTRAINT MyMappingTable_index PRIMARY KEY (id) ) with (OIDS=FALSE); CREATE TABLE MyMappingTableT1 (PRIMARY KEY (id), CHECK((c1 % 5) = 0)) INHERITS (MyMappingTable); CREATE TABLE MyMappingTableT2 (PRIMARY KEY (id), CHECK((c1 % 5) = 1)) INHERITS (MyMappingTable); CREATE TABLE MyMappingTableT3 (PRIMARY KEY (id), CHECK((c1 % 5) = 2)) INHERITS (MyMappingTable); CREATE TABLE MyMappingTableT4 (PRIMARY KEY (id), CHECK((c1 % 5) = 3)) INHERITS (MyMappingTable); CREATE TABLE MyMappingTableT5 (PRIMARY KEY (id), CHECK((c1 % 5) = 4)) INHERITS (MyMappingTable); Here is the trigger function that I added to the database: CREATE OR REPLACE FUNCTION my_mapping_table_insert_trigger() RETURNS trigger AS $$ BEGIN   IF ( (NEW.c1 % 5) = 0 ) THEN      INSERT INTO MyMappingTableT1 VALUES (NEW.*);    ELSIF ( (NEW.c1 % 5) = 1 ) THEN      INSERT INTO MyMappingTableT2 VALUES (NEW.*);   ELSIF ( (NEW.c1 % 5) = 2 ) THEN      INSERT INTO MyMappingTableT3 VALUES (NEW.*);   ELSIF ( (NEW.c1 % 5) = 3 ) THEN      INSERT INTO MyMappingTableT4 VALUES (NEW.*);   ELSIF ( (NEW.c1 % 5) = 4 ) THEN      INSERT INTO MyMappingTableT5 VALUES (NEW.*);   ELSE     RAISE EXCEPTION 'c1 mod out of range.  Something wrong with the my_mapping_table_insert_trigger() function!';   END IF;   RETURN NULL; END; $$ LANGUAGE plpgsql; Here is the Trigger that I added to the table: CREATE TRIGGER insert_my_mapping_table_trigger    BEFORE INSERT ON MyMappingTable    FOR EACH ROW EXECUTE PROCEDURE my_mapping_table_insert_trigger(); SET constraint_exclusion = ON; Regards, Richard</pre> Richard Onorato 2013-05-21T17:33:59 http://comments.gmane.org/gmane.comp.db.postgresql.general/172710 <pre>Hello, I intend to create some native data types for PostgreSQL, but since our servers run on Windows and Linux, I would have to compile the libraries for Windows and for Linux (will need nothing else than the standard C libs). Our build server is a Debian 6 Linux system. Is there a way to also create the Windows libraries on it, or do I need to have a Windows system with Visual C++ for that? Regards, Daniel Migowski </pre> Daniel Migowski 2013-05-21T05:58:32 http://comments.gmane.org/gmane.comp.db.postgresql.general/172692 <pre>Hi all, I have a C function that works fine in all cases except if I try to run it after create extension without reconnecting to the database. So this fails: createdb -U postgres -h localhost ttt psql -U postgres -h localhost ttt create extension postgis; create extension pgrouting; </pre> Stephen Woodbridge 2013-05-19T17:55:37 http://comments.gmane.org/gmane.comp.db.postgresql.general/172666 <pre>On most nested loops that I do explain/explain analyze on, the row estimation for the nested-loop itself is a product of the inner nodes of the nested loop. However in this case, I am stumped! explain select  era.child_entity  from entity_rel era  join user_entity ue on ue.entity_id = era.parent_entity and ue.user_id=12345 Nested Loop  (cost=0.00..2903.37 rows=29107 width=4)   -&gt;  Index Only Scan using entity_pk on user_entity ue  (cost=0.00..62.68 rows=2 width=4)         Index Cond: (user_id = 10954)   -&gt;  Index Scan using rel_parent on entity_rel era  (cost=0.00..1261.85 rows=317 width=8)         Index Cond: (parent_entity = ue.entity_id) How can the estimated number of rows for the nested loop node EXCEED the product of the 2 row estimates of the tables being joined? Not only does it exceed it - but it is orders of magnitude greater.  Am I missing something obvious here?  I an see the nested loop row estimate being LESS but certainly not more. PostgreSQL 9.2.4 on x86_64-pc-solaris2.10, compiled by gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath), 64-bit </pre> Jeff Amiel 2013-05-17T16:25:28 http://comments.gmane.org/gmane.comp.db.postgresql.general/172655 <pre>Hi folkes, I'm looking for a nice way to build this scenario: I've got a lot of locations with some special types. For example I've got workplaces, places like real laboratories and virtual places like maybe parcel service. For each of the different types I need to store some common attributes as well as some special ones. Having OOP in mind I came to the point of inherit tables. so I've create something like that (just a minimal example): CREATE TABLE locations( id SERIAL PRIMARY KEY, name varchar(50) ); CREATE TABLE workplaces( workers integer ) INHERITS (locations); But now I got stuck with the primary key thing. As described in the documentation it is not supported. And now I'm looking for the best way on having at table workplaces also the unique constraint from locations etc. so e.g. I can do something like that: INSERT INTO workplaces (name, workers) VALUES ('My Place', 5); having the incrementation and the uniqueness. I was thinking off creating a number of triggers doing this for me but wondering whether there might be a better way. Cheers, Frank BTW: Using Postgres 9.2 and up </pre> Frank Lanitz 2013-05-17T12:46:58 http://comments.gmane.org/gmane.comp.db.postgresql.general/172645 <pre>Hi All, I tried bumping my read ahead up to 4096. Instead of having faster reads, it seems it actually slowed things down. In fact, most of the tuning suggestions I've tried have made little to no difference in the results I get from bonnie++. I'll include a table of values in html. I'm wondering if these are normal values in my case; 4 disk RAID10 Linux ext3 146GB SAS 15K RPM Drive. Thank you, Ramsey </pre> Ramsey Gurley 2013-05-17T00:56:01 http://comments.gmane.org/gmane.comp.db.postgresql.general/172634 <pre>Hi to everybody, i just wanna ask you if somebody that already has the certification or if is studying to get it knows if there's a book (or a set of books) that you recommend me that contains most of the necessary information to cover the topics of the certification, that are the next: - PostgreSQL System Architecture - Installation - Configuration - Creating and Managing Databases - Introduction to PSQL - pgAdmin III - Security basics - SQL - Backup and Recovery - Point-in Time Recovery - Routine Maintenance - Postgres Data Dictionary - Moving Data Or if the documentation is enough to cover those topics, because i want to study to get the certification. Regards. *************************** Oscar Calderon Analista de Sistemas Soluciones Aplicativas S.A. de C.V. www.solucionesaplicativas.com Cel. (503) 7741 7850 </pre> Oscar Calderon 2013-05-16T19:46:16 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.db.postgresql.general