Re: [BUGS] BUG #8275: Updateable View based on inheritance (partition) throws Error on INSERT Statement
Looking further I just found that, if we don't want query to scan through child table then we should use ONLY during CREATE VIEW. So if I replaced my create view query with: CREATE view view_tp_sales as SELECT * FROM ONLY tp_sales; Then INSERT stmt working find. So when you create VIEW on top of inheritance (partition) table you need to create it using ONLY keyword, right ? anyone please correct me if I am wrong. Regards, Rushabh On Tue, Jul 2, 2013 at 10:29 AM, wrote: > The following bug has been logged on the website: > > Bug reference: 8275 > Logged by: Rushabh Lathia > Email address: rushabh.lat...@gmail.com > PostgreSQL version: 9.2.4 > Operating system: All > Description: > > View based on inheritance throws error on insert statement. > > > Testcase: > > > DROP TABLE tp_sales cascade; > > > CREATE TABLE tp_sales > ( > salesman_id INT4, > salesman_name VARCHAR, > sales_region VARCHAR, > sales_amount INT4 > ); > > > create table tp_sales_p_india ( check (sales_region = 'INDIA')) inherits > (tp_sales); > create table tp_sales_p_rest (check (sales_region != 'INDIA')) inherits > (tp_sales); > > > CREATE OR REPLACE FUNCTION tp_sales_insert_trigger() > RETURNS TRIGGER AS $$ > BEGIN > IF NEW.sales_region = 'INDIA' THEN > INSERT INTO tp_sales_p_india VALUES (NEW.*); > ELSE > INSERT INTO tp_sales_p_rest VALUES (NEW.*); > END IF; > RETURN NULL; > END; > $$ > LANGUAGE plpgsql; > > > > > CREATE TRIGGER insert_tp_sales_trigger > BEFORE INSERT ON tp_sales > FOR EACH ROW EXECUTE PROCEDURE tp_sales_insert_trigger(); > > > INSERT INTO tp_sales VALUES (100,'Foo','INDIA',15000); > INSERT INTO tp_sales VALUES (110,'Bar','UK',24000); > > > CREATE view view_tp_sales as SELECT * FROM tp_sales; > > > -- run insert on view > postgres=# INSERT INTO view_tp_sales VALUES (120,'XYZ','INDIA',11000); > ERROR: new row for relation "tp_sales_p_rest" violates check constraint > "tp_sales_p_rest_sales_region_check" > DETAIL: Failing row contains (120, XYZ, INDIA, 11000). > postgres=# INSERT INTO view_tp_sales VALUES (120,'ABC','HELLO',11000); > ERROR: new row for relation "tp_sales_p_india" violates check constraint > "tp_sales_p_india_sales_region_check" > DETAIL: Failing row contains (120, ABC, HELLO, 11000). > postgres=# select version(); > version > > > - > PostgreSQL 9.4devel on x86_64-unknown-linux-gnu, compiled by gcc (GCC) > 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit > (1 row) > > > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- Rushabh Lathia
Re: [BUGS] Postgres crash? could not write to log file: No spaceleft on device
Dear All, I succeed to find some Postgres note where similar issue is related to NFS mount option 'intr'. It was recommended to use 'hard,nointr' on Solaris NFS or do not use NFS at all. The default on SUN is 'intr' and on Linux is 'nointr', even not specified. Surprisingly the issue doesn't happens on any my Linux installations. According to the very long note the issue happens during heavy load, when PostgreSQL doesn't have answer during some time. By the way adding 'noac' mount option prevent successfully reproduced by me the "buggy kernel" error that also followed by PostgreSQL crash on this my particular system. All this leads me to conclusion that something basically wrong: these mount options have to be documented somewhere as recommended for NFS or log/bg writers have to take it somehow into account. Can this be confirmed as a bug? Can it be fixed in some nearest future? 2013-07-02 01:37:58 GMTXX000PANIC: could not write to file "pg_xlog/xlogtemp.24205": Interrupted system call 2013-07-02 01:40:52 GMT0LOG: WAL writer process (PID 24205) was terminated by signal 6 2013-07-02 01:40:52 GMT0LOG: terminating any other active server processes 2013-07-02 01:40:52 GMT57P03FATAL: the database system is in recovery mode 2013-07-02 02:17:41 GMT57P03FATAL: the database system is in recovery mode 2013-07-02 02:17:41 GMT0LOG: autovacuum launcher started 2013-07-02 02:17:41 GMT0LOG: database system is ready to accept connections 2013-07-02 02:44:50 GMTXX000PANIC: could not write to file "pg_xlog/xlogtemp.14855": Interrupted system call 2013-07-02 02:48:02 GMT0LOG: WAL writer process (PID 14855) was terminated by signal 6 2013-07-02 02:48:02 GMT0LOG: terminating any other active server processes 2013-07-02 02:48:02 GMT57P03FATAL: the database system is in recovery mode 2013-07-02 04:15:49 GMTXX000PANIC: could not open file "pg_xlog/000100B900C9" (log file 185, segment 201): Interrupted system call 2013-07-02 04:18:55 GMT0LOG: WAL writer process (PID 2296) was terminated by signal 6 2013-07-02 04:18:55 GMT0LOG: terminating any other active server processes 2013-07-02 04:18:55 GMT57P03FATAL: the database system is in recovery mode Sincerely yours, Yuri Levinsky, DBA Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222 -Original Message- From: Andres Freund [mailto:and...@2ndquadrant.com] Sent: Wednesday, June 26, 2013 4:04 PM To: Heikki Linnakangas Cc: Greg Stark; Tom Lane; Jeff Davis; Yuri Levinsky; pgsql-bugs@postgresql.org Subject: Re: [BUGS] Postgres crash? could not write to log file: No spaceleft on device On 2013-06-26 15:40:08 +0300, Heikki Linnakangas wrote: > On 26.06.2013 15:21, Andres Freund wrote: > >On 2013-06-26 13:14:37 +0100, Greg Stark wrote: > >>On Wed, Jun 26, 2013 at 12:57 AM, Tom Lane wrote: > >>> (Though if it is, it's not apparent why such failures would only > >>>be manifesting on the pg_xlog files and not for anything else.) > >> > >>Well data files are only ever written to in 8k chunks. Maybe these > >>errors are only occuring on>8k xlog records such as records with > >>multiple full page images. I'm not sure how much we write for other > >>types of files but they won't be written to as frequently as xlog or > >>data files and might not cause errors that are as noticeable. > > > >We only write xlog in XLOG_BLCKSZ units - which is 8kb by default as > >well... > > Actually, XLogWrite() writes multiple pages at once. If all > wal_buffers are dirty, it can try to write them all in one write() call. Oh. Misremembered that. > We've discussed retrying short writes before, and IIRC Tom has argued > that it shouldn't be necessary when writing to disk. Nevertheless, I > think we should retry in XLogWrite(). It can write much bigger chunks > than most > write() calls, so there's more room for a short write to happen t$here > if it can happen at all. Secondly, it PANICs on failure, so it would > be nice to try a bit harder to avoid that. At the very least we should log the amount of bytes actually writen if it was a short write to make it possible to discern that case from the direct ENOSPC response. This might also be caused by the fact that until recently the SIGALRM handler didn't set SA_RESTART... If a backend decided to write out the xlog directly it very well might have an active alarm... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services This mail was received via Mail-SeCure System. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8275: Updateable View based on inheritance (partition) throws Error on INSERT Statement
On 2 July 2013 08:44, Rushabh Lathia wrote: > Looking further I just found that, if we don't want query to scan through > child table then we should use ONLY during CREATE VIEW. > > So if I replaced my create view query with: > > CREATE view view_tp_sales as SELECT * FROM ONLY tp_sales; > > Then INSERT stmt working find. > > So when you create VIEW on top of inheritance (partition) table you need to > create it using ONLY keyword, right ? > anyone please correct me if I am wrong. > > Regards, > Rushabh > > > On Tue, Jul 2, 2013 at 10:29 AM, wrote: >> >> The following bug has been logged on the website: >> >> Bug reference: 8275 >> Logged by: Rushabh Lathia >> Email address: rushabh.lat...@gmail.com >> PostgreSQL version: 9.2.4 >> Operating system: All >> Description: >> >> View based on inheritance throws error on insert statement. >> >> >> Testcase: >> >> >> DROP TABLE tp_sales cascade; >> >> >> CREATE TABLE tp_sales >> ( >> salesman_id INT4, >> salesman_name VARCHAR, >> sales_region VARCHAR, >> sales_amount INT4 >> ); >> >> >> create table tp_sales_p_india ( check (sales_region = 'INDIA')) inherits >> (tp_sales); >> create table tp_sales_p_rest (check (sales_region != 'INDIA')) inherits >> (tp_sales); >> >> >> CREATE OR REPLACE FUNCTION tp_sales_insert_trigger() >> RETURNS TRIGGER AS $$ >> BEGIN >> IF NEW.sales_region = 'INDIA' THEN >> INSERT INTO tp_sales_p_india VALUES (NEW.*); >> ELSE >> INSERT INTO tp_sales_p_rest VALUES (NEW.*); >> END IF; >> RETURN NULL; >> END; >> $$ >> LANGUAGE plpgsql; >> >> >> >> >> CREATE TRIGGER insert_tp_sales_trigger >> BEFORE INSERT ON tp_sales >> FOR EACH ROW EXECUTE PROCEDURE tp_sales_insert_trigger(); >> >> >> INSERT INTO tp_sales VALUES (100,'Foo','INDIA',15000); >> INSERT INTO tp_sales VALUES (110,'Bar','UK',24000); >> >> >> CREATE view view_tp_sales as SELECT * FROM tp_sales; >> >> >> -- run insert on view >> postgres=# INSERT INTO view_tp_sales VALUES (120,'XYZ','INDIA',11000); >> ERROR: new row for relation "tp_sales_p_rest" violates check constraint >> "tp_sales_p_rest_sales_region_check" >> DETAIL: Failing row contains (120, XYZ, INDIA, 11000). >> postgres=# INSERT INTO view_tp_sales VALUES (120,'ABC','HELLO',11000); >> ERROR: new row for relation "tp_sales_p_india" violates check constraint >> "tp_sales_p_india_sales_region_check" >> DETAIL: Failing row contains (120, ABC, HELLO, 11000). >> postgres=# select version(); >> version >> >> >> - >> PostgreSQL 9.4devel on x86_64-unknown-linux-gnu, compiled by gcc (GCC) >> 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit >> (1 row) >> This is a bug. Thanks for the report! I think the rewritten query should only use inheritance if inheritance was requested in the original query, *and* if inheritance was enabled in the view's query, per attached patch against HEAD. We should probably also include some additional regression tests for these kinds of query. Regards, Dean updatable-views-inh.patch Description: Binary data -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Postgres crash? could not write to log file: No space left on device
On 26.06.2013 17:15, Tom Lane wrote: Heikki Linnakangas writes: We've discussed retrying short writes before, and IIRC Tom has argued that it shouldn't be necessary when writing to disk. Nevertheless, I think we should retry in XLogWrite(). It can write much bigger chunks than most write() calls, so there's more room for a short write to happen there if it can happen at all. Secondly, it PANICs on failure, so it would be nice to try a bit harder to avoid that. Seems reasonable. My concern about the idea in general was the impossibility of being sure we'd protected every single write() call. But if we can identify specific call sites that seem at more risk than most, I'm okay with adding extra logic there. Committed a patch to add retry loop to XLogWrite(). I noticed that FileWrite() has some additional Windows-specific code to also retry on an ERROR_NO_SYSTEM_RESOURCES error. That's a bit scary, because we don't check for that in any other write() calls in the backend. If we really need to be prepared for that on Windows, I think that would need to be in a wrapper function in src/port or src/backend/port. Would a Windows-person like to comment on that? - Heikki -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8275: Updateable View based on inheritance (partition) throws Error on INSERT Statement
On 2 July 2013 09:30, Dean Rasheed wrote: > I think the rewritten query should only use inheritance if inheritance > was requested in the original query, *and* if inheritance was enabled > in the view's query, per attached patch against HEAD. On second thoughts, I think this should only apply to INSERT. UPDATE and DELETE should continue work the same as SELECT, respecting the inheritance flag from the view query regardless of the inheritance flag in the outer query. Attached is an updated patch for HEAD, with regression tests. This should also be applied to the 9.3beta branch. Regards, Dean updatable-views-inh.patch Description: Binary data -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #8276: Can't install PostgreSQL with russian name for OS user
The following bug has been logged on the website: Bug reference: 8276 Logged by: Valery Email address: valeriy.smir...@itv.ru PostgreSQL version: 9.2.2 Operating system: Windows 7 Description: I have russian letters in operating system user name. During default PostgreSQL install I received installation error: Problem running post-install step. In installation log I found following errors: Failed to initialise the database cluster with initdb. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #8277: error in instalation
The following bug has been logged on the website: Bug reference: 8277 Logged by: Nuno Carvalho Email address: nuno...@hotmail.com PostgreSQL version: 9.2.4 Operating system: windows 8 64 bit Description: good night, I can not install postgres on windows 8 64 bit whenever an error -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #8278: psql describe table
The following bug has been logged on the website: Bug reference: 8278 Logged by: rostap Email address: ros...@wdcarea.com PostgreSQL version: 9.2.4 Operating system: mac os x Description: describe table does not list tables with the same names in different schemas. only one table is being listed while the search_path is set properly. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8278: psql describe table
Hello 2013/7/3 : > The following bug has been logged on the website: > > Bug reference: 8278 > Logged by: rostap > Email address: ros...@wdcarea.com > PostgreSQL version: 9.2.4 > Operating system: mac os x > Description: > > describe table does not list tables with the same names in different > schemas. only one table is being listed while the search_path is set > properly. > It is not a bug, but requested feature - it respect SEARCH_PATH. you can use wildcard postgres=# \dt *.xx List of relations Schema | Name | Type | Owner +--+---+--- public | xx | table | pavel xx | xx | table | pavel (2 rows) Regards Pavel Stehule > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
