Re: [GENERAL] Delete rule does not prevent truncate

So tell me guys, instead of bashing away at the fact I only quoted half a sentence or whatever, how about you answer the following : What exactly is was the design decision that lead to TRUNCATE being supported by triggers but not by rules ? I suspect that TRUNCATE was added to triggers because s

Re: [GENERAL] Delete rule does not prevent truncate

2015-07-23 9:06 GMT+02:00 Tim Smith : > So tell me guys, instead of bashing away at the fact I only quoted > half a sentence or whatever, how about you answer the following : > > What exactly is was the design decision that lead to TRUNCATE being > supported by triggers but not by rules ? > > Some

Re: [GENERAL] Lots of stuck queries after upgrade to 9.4

Well, so far with commit_delay=0 no problems. I will report back of couse if something happens, but I believe that the problem may indeed be solved/masked with that setting. Rough description of our setup, or how to reproduce: * Timeseries data in table , say, "measurements", size: 3-4TB, about 10

[GENERAL] Q: text query search and

Hi I'm trying to implement a text search in PG . My goal to enable the user search on several columns also on partial words. here is sample code : create table test_test( text_data tsvector, text_a varchar,text_b varchar); insert into test_test(text_a,text_b) select 'name 10.10.2.3 ','name3 nam

Re: [GENERAL] Delete rule does not prevent truncate

On Thu, Jul 23, 2015 at 08:06:19AM +0100, Tim Smith wrote: > What exactly is was the design decision that lead to TRUNCATE being > supported by triggers but not by rules ? There are two things. First, probably the design decision was, "I care about triggers." TRUNCATE was added (I believe) in ve

[GENERAL] plv8 package in PostgreSQL 9.4 Yum repositories

Hi, Is there a specific reason why plv8 doesn't get distributed anymore with the Yum repositories for PostgreSQL 9.4? In the list of available/supported packages, plv8 is mentioned on http://yum.postgresql.org/news-packagelist.php and it was available up to PostgreSQL 9.3. As far as we see, recen

Re: [GENERAL] Lots of stuck queries after upgrade to 9.4

On 07/23/2015 11:31 AM, Spiros Ioannou wrote: Well, so far with commit_delay=0 no problems. I will report back of couse if something happens, but I believe that the problem may indeed be solved/masked with that setting. Rough description of our setup, or how to reproduce: * Timeseries data in ta

Re: [GENERAL] Delete rule does not prevent truncate

On Thu, Jul 23, 2015 at 12:57:20PM +0100, Tim Smith wrote: > It is important to realize that a rule is really a command transformation > mechanism, or command macro. The transformation happens before the > execution of the command starts. If you actually want an operation that > fires independently

Re: [GENERAL] plv8 package in PostgreSQL 9.4 Yum repositories

Hi, On Thu, 2015-07-23 at 13:09 +0200, Jens Depuydt wrote: > Is there a specific reason why plv8 doesn't get distributed anymore > with the Yum repositories for PostgreSQL 9.4? I was not aware that 1.4.4 is released and it can be built cleanly. Sorry about that. > In the list of available/sup

Re: [GENERAL] Q: text query search and

amihay gonen wrote: > explain ANALYZE select * from test_test > where text_data@@plainto_tsquery('name'); > > my questions are : > 1. why the index is not used (I guess it is related to the way >the data is generated) Because the sequential scan has a lower cost based on available statisti

Re: [GENERAL] Q: text query search and

On Thu, Jul 23, 2015 at 2:55 AM, amihay gonen wrote: > Hi I'm trying to implement a text search in PG . > > My goal to enable the user search on several columns also on partial words. > > here is sample code : > create table test_test( text_data tsvector, text_a varchar,text_b varchar); > > inse

[GENERAL] Using the database to validate data

Hello, I have an application that occasionally performs large batch inserts of user hand-generated data. Input is a tab delimited file with typically hundreds to a thousand lines of data. Because the data is generated by hand, there are always many transaction-stopping errors in a typical input r

Re: [GENERAL] Delete rule does not prevent truncate

Andrew, >From the manual: It is important to realize that a rule is really a command transformation mechanism, or command macro. The transformation happens before the execution of the command starts. If you actually want an operation that fires independently for each physical row, you probably wa

Re: [GENERAL] Using the database to validate data

On 07/23/2015 05:55 AM, JPLapham wrote: Hello, I have an application that occasionally performs large batch inserts of user hand-generated data. Input is a tab delimited file with typically hundreds to a thousand lines of data. Because the data is generated by hand, there are always many transa

Re: [GENERAL] Q: text query search and

Kevin, I am also interested in a deeper explanation of what you mean by, "The easy way to do that is just to add a trigram index and search for similar strings, and forget about full text search." Because I need to make a decision about whether to use full text search or use other pattern matching

Re: [GENERAL] Delete rule does not prevent truncate

On 2015-07-23 12:57:20 +0100, Tim Smith wrote: > Thus, I should not have to use a trigger for TRUNCATE because the "each > row" concept does not apply. Plus it makes perfect sense to want to > transform the truncate command and transform into ignore That'd entirely defeat the point of TRUNCATE

Re: [GENERAL] Delete rule does not prevent truncate

On 07/23/2015 04:57 AM, Tim Smith wrote: Andrew, From the manual: It is important to realize that a rule is really a command transformation mechanism, or command macro. The transformation happens before the execution of the command starts. If you actually want an operation that fires independe

Re: [GENERAL] Delete rule does not prevent truncate

On 07/23/2015 12:09 PM, Adrian Klaver wrote: On 07/23/2015 04:57 AM, Tim Smith wrote: Andrew, From the manual: It is important to realize that a rule is really a command transformation mechanism, or command macro. The transformation happens before the execution of the command starts. If you a

Re: [GENERAL] Delete rule does not prevent truncate

On 07/23/2015 11:15 AM, Rob Sargent wrote: On 07/23/2015 12:09 PM, Adrian Klaver wrote: On 07/23/2015 04:57 AM, Tim Smith wrote: Andrew, From the manual: It is important to realize that a rule is really a command transformation mechanism, or command macro. The transformation happens before t

Re: [GENERAL] Delete rule does not prevent truncate

On Thu, Jul 23, 2015 at 12:09 PM, Adrian Klaver wrote: > On 07/23/2015 04:57 AM, Tim Smith wrote: >> >> Andrew, >> >> From the manual: >> >> It is important to realize that a rule is really a command >> transformation mechanism, or command macro. The transformation happens >> before the execution

Re: [GENERAL] Delete rule does not prevent truncate

On 07/23/2015 12:25 PM, Adrian Klaver wrote: On 07/23/2015 11:15 AM, Rob Sargent wrote: On 07/23/2015 12:09 PM, Adrian Klaver wrote: On 07/23/2015 04:57 AM, Tim Smith wrote: Andrew, From the manual: It is important to realize that a rule is really a command transformation mechanism, or comm

Re: [GENERAL] Setting up HA postgresql

Thanks so much for lending your expertise Tatsuo. Aviel I have found and linked below the relevant section of the official pgpool-II docs on using pgpool-II for failover. It is available starting in pgpool-II version 3.1: http://www.pgpool.net/docs/latest/pgpool-en.html#stream *Will J. Dunn* On

[GENERAL] The fastest way to update thousands of rows in moderately sized table

Hello,I have a table with 30 million records in which I need to update a single column for a couple of thousands of rows, let's say 10 000. The new column value is identical for all matching rows.Doing update "TRANSLATION" set fk_assignmentwhere fk_job = 1000; takes 45 seconds. I understand that

Re: [GENERAL] Using the database to validate data

On 07/23/2015 12:04 PM, Jon Lapham wrote: On 07/23/2015 03:02 PM, Adrian Klaver wrote: http://pgloader.io/ Ok, thanks, I'll look into pgloader's data validation abilities. However, my naive understanding of pgloader is that it is used to quickly load data into a database, which is not what I

Re: [GENERAL] The fastest way to update thousands of rows in moderately sized table

On 07/23/2015 01:17 PM, twoflower wrote: Hello, I have a table with 30 million records in which I need to update a single column for a couple of thousands of rows, let's say 10 000. The new column value is identical for all matching rows. Doing |update "TRANSLATION" set fk_assignment where fk_jo

Re: [GENERAL] Using the database to validate data

Shouldn't be too difficult to import those new rows into one table, write a procedure that inserts them into the real table one by one and logs the validation failure if any - committing good rows and rolling back bad. In fact if you could then write the failures to a third table with a completely

[GENERAL] Re: The fastest way to update thousands of rows in moderately sized table

Adrian Klaver-4 wrote > Have you tried wrapping the above in a BEGIN/COMMIT block? Yes, I am running the tests inside a BEGIN TRANSACTION / ROLLBACK block. -- View this message in context: http://postgresql.nabble.com/The-fastest-way-to-update-thousands-of-rows-in-moderately-sized-table-tp5859

Re: [GENERAL] Backup fatal issue

​Hi Melvin, I tried to execute pg_dump and after I tried to launch pg_start_backup and pg_stop_backup. In both cases, I did not receive any error, so I decided to investigate in BarMan. Thanks for your help. Bye. Meph​ On 17 July 2015 at 16:16, Melvin Davidson wrote: > If this is occurring wi

Re: [GENERAL] Backup fatal issue

Hi Tom, I am using only pg_ctl to start and stop my database. At the moment I do not use any script. Bye. Meph On 17 July 2015 at 16:32, Tom Lane wrote: > mephysto writes: > > I have some problems in a postgres cluster when I try to execute a > backup. > > ... > > FATAL: semctl(983046, 3, SE

[GENERAL] Schema Help Needed To Get Unstuck

While designing the schema for a new application have become high-centered and stuck relating some many-to-many tables. Fresh eyes and suggestions are needed on how to create many-to-many association tables among these five. Table 'Permits': Contains information about each permit, PK is permi

Re: [GENERAL] Delete rule does not prevent truncate

On Thu, Jul 23, 2015 at 12:28:32PM -0600, Rob Sargent wrote: > I'm suggesting OP might find changing truncate statements to deletes > (without a where clause) a simpler solution. Something has to change. Well, OP isn't looking for a solution to "delete all rows" but rather to _prevent_ deletion.

[GENERAL] Schema Help Needed To Get Unstuck

While designing the schema for a new application have become high-centered and stuck relating some many-to-many tables. Fresh eyes and suggestions are needed on how to create many-to-many association tables among these five. Table 'Permits': Contains information about each permit, PK is permi

Re: [GENERAL] Delete rule does not prevent truncate

On 07/23/2015 04:15 PM, Karsten Hilbert wrote: On Thu, Jul 23, 2015 at 12:28:32PM -0600, Rob Sargent wrote: I'm suggesting OP might find changing truncate statements to deletes (without a where clause) a simpler solution. Something has to change. Well, OP isn't looking for a solution to "delet

Re: [GENERAL] Schema Help Needed To Get Unstuck

On Thu, Jul 23, 2015 at 3:06 PM, Rich Shepard wrote: > While designing the schema for a new application have become > high-centered > and stuck relating some many-to-many tables. Fresh eyes and suggestions are > needed on how to create many-to-many association tables among these five. > > Tab

Re: [GENERAL] Delete rule does not prevent truncate

On 07/23/2015 05:08 PM, Rob Sargent wrote: On 07/23/2015 04:15 PM, Karsten Hilbert wrote: On Thu, Jul 23, 2015 at 12:28:32PM -0600, Rob Sargent wrote: I'm suggesting OP might find changing truncate statements to deletes (without a where clause) a simpler solution. Something has to change. Wel

Re: [GENERAL] Delete rule does not prevent truncate

On 07/23/2015 06:27 PM, Adrian Klaver wrote: On 07/23/2015 05:08 PM, Rob Sargent wrote: On 07/23/2015 04:15 PM, Karsten Hilbert wrote: On Thu, Jul 23, 2015 at 12:28:32PM -0600, Rob Sargent wrote: I'm suggesting OP might find changing truncate statements to deletes (without a where clause) a s

Re: [GENERAL] Delete rule does not prevent truncate

On 07/23/2015 05:37 PM, Rob Sargent wrote: On 07/23/2015 06:27 PM, Adrian Klaver wrote: On 07/23/2015 05:08 PM, Rob Sargent wrote: On 07/23/2015 04:15 PM, Karsten Hilbert wrote: On Thu, Jul 23, 2015 at 12:28:32PM -0600, Rob Sargent wrote: I'm suggesting OP might find changing truncate statem

Re: [GENERAL] Schema Help Needed To Get Unstuck

On Thu, 23 Jul 2015, David G. Johnston wrote: Conditions <- (Permit + Location + Parameter + Frequency + Temporal (from, until)) Monitoring <- (Condition + Event) While you may require additional modelling tables to support your user interface (i.e., picking valid combinations of PeLoPa when cr

Re: [GENERAL] Schema Help Needed To Get Unstuck

Does this help? CREATE TABLE permits ( permit_number bigint, ..., other_columns , ..., CONSTRAINT permits_pk PRIMARY KEY (permit_number) ); CREATE TABLE parameters ( permit_number bigint, parameter varchar(50), ..., other_columns , ..., CONSTRAINT parameters_pk PRIMARY KEY (permit_number, paramet

Re: [GENERAL] Schema Help Needed To Get Unstuck

On Thu, 23 Jul 2015, Melvin Davidson wrote: Does this help? Melvin, Yep. After pondering David's response I recognized my error: I had the permit table as the center of the relationships when it should be the conditions table. As you wrote, permits, parameters, and locations all feed the

Re: [GENERAL] Using the database to validate data

If I am following correctly, you can do it in your application as follows. 1.begin transaction 2.insert each data. Catch db exception, and save exception message and other information you need to array. 3.in the end ,you can get all the information about the wrong data in array if there is any.

Re: [GENERAL] Using the database to validate data

On 07/23/2015 08:20 PM, 林士博 wrote: If I am following correctly, you can do it in your application as follows. 1.begin transaction 2.insert each data. Catch db exception, and save exception message and other information you need to array. 3.in the end ,you can get all the information

Re: [GENERAL] Using the database to validate data

Yes. You are right. Do it in postgresql procedure is faster than in application.

Re: [GENERAL] Re: The fastest way to update thousands of rows in moderately sized table

Try creating an index on TRANSLATION fk_job. >From the Execution plan you posted, ->(actual time=43262.266..43262.266 rows=0 loops=1) actually, there is no row to be updated. So, if you have a index on TRANSLATION fk_job, the update sql as behind should be finished within several seconds. ->updat

[GENERAL] Re: The fastest way to update thousands of rows in moderately sized table

林士博 wrote > Try creating an index on TRANSLATION fk_job. The index is already there. -- View this message in context: http://postgresql.nabble.com/The-fastest-way-to-update-thousands-of-rows-in-moderately-sized-table-tp5859144p5859191.html Sent from the PostgreSQL - general mailing list archiv

Re: [GENERAL] Re: The fastest way to update thousands of rows in moderately sized table

Sorry, my mistake. Execution plan of an update sql seems always get rows=0. Can you post execution plan of the original update sql. EXPLAIN (ANALYZE ON, BUFFERS ON) update "TRANSLATION" set fk_assignmentwhere fk_job = 1000;

[GENERAL] Re: The fastest way to update thousands of rows in moderately sized table

林士博 wrote > Can you post execution plan of the original update sql.EXPLAIN (ANALYZE > ON, BUFFERS ON) update "TRANSLATION" setfk_assignmentwhere fk_job = 1000; Here it is: Update on "TRANSLATION" (cost=0.56..9645.13 rows=3113 width=391) (actual time=35091.036..35091.036 rows=0 loops=1)    Buffe