Re: [GENERAL] PostgreSQL crash with PANIC message
On Thu, Mar 24, 2016 at 5:57 AM, Adrian Klaver wrote: > On 03/23/2016 12:02 PM, Moreno Andreo wrote: >> >> Il 23/03/2016 19:57, Adrian Klaver ha scritto: >>> >>> >>> Might help to look in: >>> >>> Control Panel --> Administrative Tools --> Event Viewer >> >> >> No help there. Nothing strange or pointing to critical resource usage. > > > The only thing I have left is the generic answer for when things act funny > on Windows: > > Do you have AntiVirus software running against the Postgres data directory? That's likely the reason, something creeping behind is forcing disconnections from the server. -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Only owners can ANALYZE tables...seems overly restrictive
On Mon, Feb 29, 2016 at 6:31 AM, Stephen Frost wrote: If we had plenty more bits to allow ANALYZE to be independently GRANT'able, then maybe, but those are a limited resource. On Mon, Feb 29, 2016 at 10:35 AM, Joshua D. Drake wrote: 3. This can already be handled by GRANT: * psql -U jd -h localhost; * create table foo (id text); * create role jd_role; * alter table foo owner to jd_role; * grant jd_role to boo; * \c jd boo * analyze foo; On 02/29/2016 09:09 AM, David G. Johnston wrote: Given these two things it seems the least difficult solution that doesn't make things any worse is to make "ANALYZE" grantable. If you were going to give the user owner permissions anyway then having a less-inclusive permission cannot hurt. == My last comment sums things up pretty well. I assume someone will insist that a security definer function is "the officially supported way to do this", and if the community wants to agree then fine. Otherwise, if you are going to tell me to give someone ownership of a table so that they can ANALYZE it then no intermediate solution I propose can be considered off-limits on security grounds because nothing - relative to the table in question - is less secure. Which means that, aside from effort, the main blocking factors here are code complexity (which I understand) and limited grant "bits" as Stephen puts it. So I pose the question: do any of the committers consider a grant bit too valuable to consume on an ANALYZE grant? If that and/or general code complexity means this will not be added even if a patch was proposed for 9.7 then I'll move on and institute one of the hacks that has been proffered. Otherwise I have (more than) half a mind to find some way to get a patch written. David J.
[GENERAL] Re: Doesn't PostgreSQL clean data in data file after delete records form table?
Hi, Thank you very much. It help us a lot -- View this message in context: http://postgresql.nabble.com/Doesn-t-PostgreSQL-clean-data-in-data-file-after-delete-records-from-table-tp5894764p5894960.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL crash with PANIC message
On 03/23/2016 12:02 PM, Moreno Andreo wrote: Il 23/03/2016 19:57, Adrian Klaver ha scritto: Might help to look in: Control Panel --> Administrative Tools --> Event Viewer No help there. Nothing strange or pointing to critical resource usage. The only thing I have left is the generic answer for when things act funny on Windows: Do you have AntiVirus software running against the Postgres data directory? Thanks in advance, Moreno. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL crash with PANIC message
Il 23/03/2016 19:57, Adrian Klaver ha scritto: Might help to look in: Control Panel --> Administrative Tools --> Event Viewer No help there. Nothing strange or pointing to critical resource usage. Thanks in advance, Moreno. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL crash with PANIC message
On 03/23/2016 11:43 AM, Moreno Andreo wrote: Hello, a customer of ours has an old PC (CoreDuo, 4 GB RAM, Windows XP) that's running PostgreSQL. Yesterday PostgreSQL started crashing suddenly with the following log 2016-03-23 10:40:42 CET LOG: CreateProcess call failed: unrecognized winsock error 10004 (error code 1450) 2016-03-23 10:40:42 CET LOG: could not fork new process for connection: unrecognized winsock error 10004 2016-03-23 10:40:43 CET LOG: could not receive data from client: unrecognized winsock error 10055 2016-03-23 10:40:43 CET LOG: unexpected EOF on client connection 2016-03-23 10:42:48 CET LOG: CreateProcess call failed: unrecognized winsock error 10004 (error code 1450) 2016-03-23 10:42:48 CET LOG: could not fork autovacuum worker process: unrecognized winsock error 10004 2016-03-23 10:42:49 CET LOG: CreateProcess call failed: unrecognized winsock error 10004 (error code 1450) 2016-03-23 10:42:49 CET LOG: could not fork autovacuum worker process: unrecognized winsock error 10004 2016-03-23 10:42:50 CET LOG: CreateProcess call failed: unrecognized winsock error 10004 (error code 1450) 2016-03-23 10:42:50 CET LOG: could not fork autovacuum worker process: unrecognized winsock error 10004 2016-03-23 10:42:51 CET LOG: CreateProcess call failed: unrecognized winsock error 10004 (error code 1450) 2016-03-23 10:42:51 CET LOG: could not fork autovacuum worker process: unrecognized winsock error 10004 2016-03-23 10:42:52 CET LOG: CreateProcess call failed: unrecognized winsock error 10004 (error code 1450) 2016-03-23 10:42:52 CET LOG: could not fork autovacuum worker process: unrecognized winsock error 10004 2016-03-23 10:42:53 CET LOG: CreateProcess call failed: unrecognized winsock error 10004 (error code 1450) 2016-03-23 10:42:53 CET LOG: could not fork autovacuum worker process: unrecognized winsock error 10004 2016-03-23 10:42:54 CET LOG: CreateProcess call failed: unrecognized winsock error 10004 (error code 1450) 2016-03-23 10:42:54 CET LOG: could not fork autovacuum worker process: unrecognized winsock error 10004 2016-03-23 10:42:55 CET LOG: CreateProcess call failed: unrecognized winsock error 10004 (error code 1450) 2016-03-23 10:42:55 CET LOG: could not fork autovacuum worker process: unrecognized winsock error 10004 2016-03-23 10:42:56 CET LOG: CreateProcess call failed: unrecognized winsock error 10004 (error code 1450) 2016-03-23 10:42:56 CET LOG: could not fork autovacuum worker process: unrecognized winsock error 10004 2016-03-23 10:43:24 CET LOG: could not receive data from client: unrecognized winsock error 10055 2016-03-23 10:43:24 CET LOG: unexpected EOF on client connection 2016-03-23 10:43:24 CET LOG: could not receive data from client: unrecognized winsock error 10061 2016-03-23 10:43:24 CET LOG: unexpected EOF on client connection 2016-03-23 10:43:27 CET LOG: could not receive data from client: unrecognized winsock error 10055 2016-03-23 10:43:27 CET LOG: unexpected EOF on client connection 2016-03-23 10:43:30 CET PANIC: could not write to log file 6, segment 217 at offset 3366912, length 8192: Invalid argument The machine has 4,5 GB of allocated memory (thus swapping). I googled to get some references to what's above and that's results: Winsock 10004 means Interrupted function call; Error code 1450 should mean "Insufficient system resources exist to complete the requested service" Winsock 10055 means "No buffer space available" Winsock 10061 means "Connection refused" The only thing I couldn't get some serious reference was the PANIC message that's in the last row. But putting the other things together, I figured out the machine is too obsolete and ran out of system resources, causing error 1450, which Winsock 10055 is solely a consequence, and probably the PANIC is generated by this mess. Am I right? Might help to look in: Control Panel --> Administrative Tools --> Event Viewer Thanks in advance, Moreno. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL crash with PANIC message
Hello, a customer of ours has an old PC (CoreDuo, 4 GB RAM, Windows XP) that's running PostgreSQL. Yesterday PostgreSQL started crashing suddenly with the following log 2016-03-23 10:40:42 CET LOG: CreateProcess call failed: unrecognized winsock error 10004 (error code 1450) 2016-03-23 10:40:42 CET LOG: could not fork new process for connection: unrecognized winsock error 10004 2016-03-23 10:40:43 CET LOG: could not receive data from client: unrecognized winsock error 10055 2016-03-23 10:40:43 CET LOG: unexpected EOF on client connection 2016-03-23 10:42:48 CET LOG: CreateProcess call failed: unrecognized winsock error 10004 (error code 1450) 2016-03-23 10:42:48 CET LOG: could not fork autovacuum worker process: unrecognized winsock error 10004 2016-03-23 10:42:49 CET LOG: CreateProcess call failed: unrecognized winsock error 10004 (error code 1450) 2016-03-23 10:42:49 CET LOG: could not fork autovacuum worker process: unrecognized winsock error 10004 2016-03-23 10:42:50 CET LOG: CreateProcess call failed: unrecognized winsock error 10004 (error code 1450) 2016-03-23 10:42:50 CET LOG: could not fork autovacuum worker process: unrecognized winsock error 10004 2016-03-23 10:42:51 CET LOG: CreateProcess call failed: unrecognized winsock error 10004 (error code 1450) 2016-03-23 10:42:51 CET LOG: could not fork autovacuum worker process: unrecognized winsock error 10004 2016-03-23 10:42:52 CET LOG: CreateProcess call failed: unrecognized winsock error 10004 (error code 1450) 2016-03-23 10:42:52 CET LOG: could not fork autovacuum worker process: unrecognized winsock error 10004 2016-03-23 10:42:53 CET LOG: CreateProcess call failed: unrecognized winsock error 10004 (error code 1450) 2016-03-23 10:42:53 CET LOG: could not fork autovacuum worker process: unrecognized winsock error 10004 2016-03-23 10:42:54 CET LOG: CreateProcess call failed: unrecognized winsock error 10004 (error code 1450) 2016-03-23 10:42:54 CET LOG: could not fork autovacuum worker process: unrecognized winsock error 10004 2016-03-23 10:42:55 CET LOG: CreateProcess call failed: unrecognized winsock error 10004 (error code 1450) 2016-03-23 10:42:55 CET LOG: could not fork autovacuum worker process: unrecognized winsock error 10004 2016-03-23 10:42:56 CET LOG: CreateProcess call failed: unrecognized winsock error 10004 (error code 1450) 2016-03-23 10:42:56 CET LOG: could not fork autovacuum worker process: unrecognized winsock error 10004 2016-03-23 10:43:24 CET LOG: could not receive data from client: unrecognized winsock error 10055 2016-03-23 10:43:24 CET LOG: unexpected EOF on client connection 2016-03-23 10:43:24 CET LOG: could not receive data from client: unrecognized winsock error 10061 2016-03-23 10:43:24 CET LOG: unexpected EOF on client connection 2016-03-23 10:43:27 CET LOG: could not receive data from client: unrecognized winsock error 10055 2016-03-23 10:43:27 CET LOG: unexpected EOF on client connection 2016-03-23 10:43:30 CET PANIC: could not write to log file 6, segment 217 at offset 3366912, length 8192: Invalid argument The machine has 4,5 GB of allocated memory (thus swapping). I googled to get some references to what's above and that's results: Winsock 10004 means Interrupted function call; Error code 1450 should mean "Insufficient system resources exist to complete the requested service" Winsock 10055 means "No buffer space available" Winsock 10061 means "Connection refused" The only thing I couldn't get some serious reference was the PANIC message that's in the last row. But putting the other things together, I figured out the machine is too obsolete and ran out of system resources, causing error 1450, which Winsock 10055 is solely a consequence, and probably the PANIC is generated by this mess. Am I right? Thanks in advance, Moreno. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Including SQL files
On Mon, 2016-03-21 at 15:58 +0100, Alexander Farber wrote: > Thanks for your replies. > > > While I use "\i" regularly I just didn't realize it would be suitable > here as well :-) > \ir migth be a better option for a bundle of scripts, related to main script (words.sql in your case), not to cwd > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Doesn't PostgreSQL clean data in data file after delete records form table?
On Wed, Mar 23, 2016 at 03:27:45AM -0700, zh1029 wrote: > Hi, > we are using PostgreSQL 9.3.6 version and observe data file size is not > decreased after we deleted records from the table. It looks quite abnormal. > Is it as PostreSQL designed? > > DBTestPostgres=# select count (*) from test_data ; > 0 > > # ls -l > total 788932 > . > -rw--- 1 _nokfssystestpostgres root *801067008* Mar 23 17:51 32768 Yes, that's perfectly OK. It is due to MVCC mechanisms. subsequent vacuum (at least in your case) should free the disk space, and if it doesn't, check other ways, described here: http://www.depesz.com/2011/07/06/bloat-happens/ Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Doesn't PostgreSQL clean data in data file after delete records form table?
Hi, we are using PostgreSQL 9.3.6 version and observe data file size is not decreased after we deleted records from the table. It looks quite abnormal. Is it as PostreSQL designed? DBTestPostgres=# select count (*) from test_data ; 0 # ls -l total 788932 . -rw--- 1 _nokfssystestpostgres root *801067008* Mar 23 17:51 32768 -rw--- 1 _nokfssystestpostgres root221184 Mar 23 17:47 32768_fsm -rw--- 1 _nokfssystestpostgres root 0 Mar 23 17:44 32768_vm -rw--- 1 _nokfssystestpostgres root 0 Mar 23 17:43 32771 -rw--- 1 _nokfssystestpostgres root 8192 Mar 23 17:43 32773 -rw--- 1 _nokfssystestpostgres root 512 Mar 23 13:57 pg_filenode.map -rw--- 1 _nokfssystestpostgres root116404 Mar 23 17:19 pg_internal.init -rw--- 1 _nokfssystestpostgres root 4 Mar 23 13:57 PG_VERSION Thanks! -- View this message in context: http://postgresql.nabble.com/Doesn-t-PostgreSQL-clean-data-in-data-file-after-delete-records-form-table-tp5894764.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Partitioning and ORM tools
On Wed, Mar 23, 2016 at 9:39 AM, Chris Travers wrote: > Use a view with a DO INSTEAD trigger. That will allow you to return the > tuple properly. > > On Tue, Mar 22, 2016 at 7:40 PM, CS DBA > wrote: > >> Hi All; >> >> we setup partitioning for a large table but had to back off because the >> return status (i.e: "INSERT 0 1") returns "INSERT 0 0" when inserting into >> the partitioned table which causes the ORM tool to assume the insert >> inserted 0 rows. Is there a standard / best practices work around for this? >> > Apologies for the top post above. Just noting additionally that the view with DO INSTEAD approach was suggested to me by Matt Trout (major contributor to the DBIx::Class ORM in Perl. I have used it. It works well. I think it is the best practice there. > >> Thanks in advance >> >> >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > > > -- > Best Wishes, > Chris Travers > > Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor > lock-in. > http://www.efficito.com/learn_more > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more
Re: [GENERAL] Partitioning and ORM tools
Use a view with a DO INSTEAD trigger. That will allow you to return the tuple properly. On Tue, Mar 22, 2016 at 7:40 PM, CS DBA wrote: > Hi All; > > we setup partitioning for a large table but had to back off because the > return status (i.e: "INSERT 0 1") returns "INSERT 0 0" when inserting into > the partitioned table which causes the ORM tool to assume the insert > inserted 0 rows. Is there a standard / best practices work around for this? > > Thanks in advance > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more
