Re: [GENERAL] Delete Duplicates with Using

FYI, On 10/16/2017 8:58 AM, Igal @ Lucee.org wrote: test=*# with keep as (select max(ctid) as ctid from dubletten group by c1,c2,c3) delete from dubletten where ctid not in (select ctid from keep); I like this solution, but would using a subquery be much slower than the implicit join o

Re: [GENERAL] Delete Duplicates with Using

Andreas, On 10/15/2017 11:53 PM, Andreas Kretschmer wrote: other solution, using the CTID-column: (rows with (1,1,1) and (5,5,5) are identical) test=*# select * from dubletten ;  c1 | c2 | c3 ++   1 |  1 |  1   1 |  1 |  1   1 |  2 |  3   2 |  3 |  4   3 |  4 |  5   4 |  5 |  5   5

Re: [GENERAL] Delete Duplicates with Using

Am 14.10.2017 um 08:20 schrieb Igal @ Lucee.org: Hello, I run the SQL query below to delete duplicates from a table. The subquery is used to identify the duplicated rows (row_num is a BIGSERIAL column). other solution, using the CTID-column: (rows with (1,1,1) and (5,5,5) are identical

Re: [GENERAL] Delete Duplicates with Using

On 10/14/2017 12:32 AM, legrand legrand wrote: DELETE FROM table_with_duplicates AS T1 USING table_with_duplicates AS T2 WHERE T1.column_1 = T2.column_1 AND T1.column_2 = T2.column_2 AND T1.column_3 = T2.column_3 AND T1.row_num < T2.row_num Thank you, I actua

Re: [GENERAL] Delete Duplicates with Using

DELETE FROM table_with_duplicates AS T1 USING table_with_duplicates AS T2 WHERE T1.column_1 = T2.column_1 AND T1.column_2 = T2.column_2 AND T1.column_3 = T2.column_3 AND T1.row_num < T2.row_num -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f

[GENERAL] Delete Duplicates with Using

Hello, I run the SQL query below to delete duplicates from a table.  The subquery is used to identify the duplicated rows (row_num is a BIGSERIAL column). /** delete older copies of duplicates */ DELETE FROM table_with_duplicatesAS T WHERE row_num IN (     SELECT     T1.row_num     FROM    ta

Re: [GENERAL] delete a file everytime pg server starts/crashes

On Sun, Oct 8, 2017 at 10:01 AM, athinivas wrote: > Yes, will take the epoch value from same and create a new file. Upon > subsequent calls, will access with that filename...if it fails(incase if > the > postmaster is restarted), will create a new one. > > @Melvin Does this capture all failure sc

Re: [GENERAL] delete a file everytime pg server starts/crashes

Yes, will take the epoch value from same and create a new file. Upon subsequent calls, will access with that filename...if it fails(incase if the postmaster is restarted), will create a new one. @Melvin Does this capture all failure scenarios?? Or you have any other better ways to do it. Your com

Re: [GENERAL] delete a file everytime pg server starts/crashes

On Sun, Oct 8, 2017 at 8:33 AM, athinivas wrote: > Hi, > > Thank you...will try it :) ...As of now, I'm creating the filename as > pg_start_time so that, every time the server is up, a new file will be > created. > > > Regards, > Athi > > > > -- > Sent from: http://www.postgresql-archive.org/Post

Re: [GENERAL] delete a file everytime pg server starts/crashes

Hi, Thank you...will try it :) ...As of now, I'm creating the filename as pg_start_time so that, every time the server is up, a new file will be created. Regards, Athi -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list

Re: [GENERAL] delete a file everytime pg server starts/crashes

Actually if the name of the file doesn't matter you could put it into $PGDATA/pg_stat and name it global.stat. When postgres stops (clean or because of failure), replaces the file with his own. So your content will be erased. I'm not sure it's completely safe but works in simple test. -- Sent fr

Re: [GENERAL] delete a file everytime pg server starts/crashes

Look at inotify: https://github.com/rvoicilas/inotify-tools You can check for instance if postmaster.pid exists. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subsc

Re: [GENERAL] delete a file everytime pg server starts/crashes

That seems like an odd requirement and I don't think PostgreSQL can do it itself, because if postgresql should crash properly then the process that should write/remove that file would also crash The simplest way would be to write a cronjob that connects to the database and does a simple query

Re: [GENERAL] delete a file everytime pg server starts/crashes

> > On Thu, Oct 5, 2017 at 10:04 AM, athinivas wrote: > >> Hi, >> >> I'm having a requirement to delete a file in system whenever pg server is >> started/crashed. Any idea? >> >> Thanks, >> Athi >> >> If you’re running on Linux you can modify the init.d (or service) file and add a line to delete t

Re: [GENERAL] delete a file everytime pg server starts/crashes

On Thu, Oct 5, 2017 at 10:04 AM, athinivas wrote: > Hi, > > I'm having a requirement to delete a file in system whenever pg server is > started/crashed. Any idea? > > Thanks, > Athi > > > > -- > Sent from: http://www.postgresql-archive.org/PostgreSQL-general- > f1843780.html > > > -- > Sent via p

[GENERAL] delete a file everytime pg server starts/crashes

Hi, I'm having a requirement to delete a file in system whenever pg server is started/crashed. Any idea? Thanks, Athi -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to y

Re: [GENERAL] DELETE and JOIN

Good morning and thank you for the replies. I've ended up with the following DELETE USING (in order to delete reviews coming from different user id, but same IP address in the last 24 hours): DELETE FROM words_reviews r USING words_users u WHERE r.uid = u.uid

Re: [GENERAL] DELETE and JOIN

On Mon, Mar 13, 2017 at 9:39 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > Good evening, > > In a 9.5 database I would like players to rate each other and save the > reviews in the table: > > CREATE TABLE words_reviews ( > uid integer NOT NULL CHECK (uid <> author) REFERENCES

Re: [GENERAL] DELETE and JOIN

On 03/13/2017 09:39 AM, Alexander Farber wrote: Good evening, In a 9.5 database I would like players to rate each other and save the reviews in the table: CREATE TABLE words_reviews ( uid integer NOT NULL CHECK (uid <> author) REFERENCES words_users ON DELETE CASCADE, author int

Re: [GENERAL] DELETE and JOIN

Alexander Farber writes: > ... > However, before saving a review, I would like to delete all previous > reviews coming from the same IP in the past 24 hours: > ... > I have the feeling that the _author_ip variable is not really necessary and > I could use some kind of "DELETE JOIN" here, but can n

[GENERAL] DELETE and JOIN

Good evening, In a 9.5 database I would like players to rate each other and save the reviews in the table: CREATE TABLE words_reviews ( uid integer NOT NULL CHECK (uid <> author) REFERENCES words_users ON DELETE CASCADE, author integer NOT NULL REFERENCES words_users(uid) ON DELET

Re: [GENERAL] Delete from table conditionally

Hello > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Arup Rakshit > Sent: Samstag, 17. Dezember 2016 08:39 > To: Pgsql-general > Subject: [GENERAL] Delete from table conditionally >

[GENERAL] Delete from table conditionally

Hi, I am using Postgresql 9.5. Below is the sample data taken from the mail table with only required columns: id | question_id | answer_id | content +-+---+-- 2 | 25 | 270 | Arup 3 | 26 | 276 | Kajal 4 | 26 | 276 | Arup

Re: [GENERAL] Delete trigger

Hi Ioana and David. Thank you. Yes, I can see the problem. I will look into your suggestions. Leif - Original Message - > On Friday, September 18, 2015, Leif Jensen wrote: > > >Hello Laurenz, > > > >Thank you for you suggestion. I really want to aviod that someone > > '

Re: [GENERAL] Delete trigger

On Friday, September 18, 2015, Leif Jensen wrote: >Hello Laurenz, > >Thank you for you suggestion. I really want to aviod that someone > 'accidentally' deletes too much by typing (programming) a not full > qualified DELETE ... statement. In your case one would have to always use > the del

Re: [GENERAL] Delete trigger

It depends on the size of the table and the frequency of updates, deletes but cold consider an audit table with triggers for update, delete and truncate. At least you have a way to recover deleted records. Ioana On Fri, Sep 18, 2015 at 5:52 AM, Leif Jensen wrote: >Hello Laurenz, > >Tha

Re: [GENERAL] Delete trigger

Hello Laurenz, Thank you for you suggestion. I really want to aviod that someone 'accidentally' deletes too much by typing (programming) a not full qualified DELETE ... statement. In your case one would have to always use the delete function, but no restrictions on using the DELETE statem

Re: [GENERAL] Delete trigger

Leif Jensen wrote: >If I do "DELETE FROM devicegroup WHERE group=1" I do not want to delete > anything. I only want to > delete if I do "DELETE FROM devicegroup WHERE groupid=x AND ctrlid=y AND > userid=z". I don't wanna let > anyone delete more than 1 row at a time. I can't think of a way t

Re: [GENERAL] Delete trigger

gt; > > -Original Message- > > From: pgsql-general-ow...@postgresql.org > > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Leif Jensen > > Sent: Freitag, 18. September 2015 10:23 > > To: pgsql-general > > Subject: [GENERAL] Delete trigger > > &g

Re: [GENERAL] Delete trigger

.@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Leif Jensen > Sent: Freitag, 18. September 2015 10:23 > To: pgsql-general > Subject: [GENERAL] Delete trigger > >Hi, > >I am running PostgreSQL 9.3.4. I have a simple table with 3 fields: > > CR

[GENERAL] Delete trigger

Hi, I am running PostgreSQL 9.3.4. I have a simple table with 3 fields: CREATE TABLE devicegroup ( groupid integer NOT NULL, ctrlid integer NOT NULL, userid integer NOT NULL ); ALTER TABLE ONLY devicegroup ADD CONSTRAINT pk_devicegroup PRIMARY KEY (groupid, ctrlid, userid);

Re: [GENERAL] Delete rule does not prevent truncate

For crying out loud GET OVER IT! You've been given a very reasonable and quick solution to your problem. You can either 1. Keep crying and moaning until someone changes the rules. 2. Give up and port to another database. 3. Write the triggers and solve your problem! On Fri, Jul 24, 2015 at 5:27 AM

Re: [GENERAL] Delete rule does not prevent truncate

> > Just in case it has not been made obvious yet, rules are silently > deprecated. They still exist because views depend on them, but it is > generally considered best practices to not use them outside that realm. Well, the manual doesn't seem to reflect that fact. If that's how the developers

Re: [GENERAL] Delete rule does not prevent truncate

On 23 July 2015 at 19:25, Scott Marlowe wrote: > stick to triggers, they're faster Erm, not according to your beloved manual !!! 38.7. Rules Versus Triggers " a rule issuing one extra command is likely to be faster than a trigger" "The summary is, rules will only be significantly slower than t

Re: [GENERAL] Delete rule does not prevent truncate

On 07/24/2015 02:32 AM, Andres Freund wrote: On 2015-07-24 10:29:21 +0100, Tim Smith wrote: That's not the point. Backups are important, but so is the concept of various layers of anti-fat-finger protection. Restoring off backups should be last resort, not first. Oh, comeon. Install a TRU

Re: [GENERAL] Delete rule does not prevent truncate

On Fri, Jul 24, 2015 at 3:27 AM, Tim Smith wrote: > On 23 July 2015 at 19:25, Scott Marlowe wrote: >> stick to triggers, they're faster > > > Erm, not according to your beloved manual !!! > > 38.7. Rules Versus Triggers > > " a rule issuing one extra command is likely to be faster than a trigger"

Re: [GENERAL] Delete rule does not prevent truncate

On 07/24/2015 02:24 AM, Tim Smith wrote: Just in case it has not been made obvious yet, rules are silently deprecated. They still exist because views depend on them, but it is generally considered best practices to not use them outside that realm. Well, the manual doesn't seem to reflect that

Re: [GENERAL] Delete rule does not prevent truncate

On Fri, Jul 24, 2015 at 10:24:55AM +0100, Tim Smith wrote: > > If that's how the developers feel about rules, i.e they can't be > bothered to make any changes to the rules code any more, no matter how > minor (e.g. this TRUNCATE issue) Who is this "they"? As I think I suggested in another mail,

Re: [GENERAL] Delete rule does not prevent truncate

On 2015-07-24 10:29:21 +0100, Tim Smith wrote: > That's not the point. Backups are important, but so is the concept of > various layers of anti-fat-finger protection. Restoring off backups > should be last resort, not first. Oh, comeon. Install a TRUNCATE trigger and let this thread die. Andre

Re: [GENERAL] Delete rule does not prevent truncate

On 24 July 2015 at 01:37, Rob Sargent wrote: > Fair enough but both blackhats and the authorized are just as likely to drop > the database as truncate something (intentionally or not) and backups > stashed everywhere is the first order of business. That's not the point. Backups are important,

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] 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: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 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] 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.

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] 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 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 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 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 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

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] 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] 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

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] 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

On 22 July 2015 at 16:55, Joshua D. Drake wrote: > > On 07/22/2015 08:42 AM, Geoff Winkless wrote: > >> On 22 July 2015 at 16:32, Joshua D. Drake > >wrote: >> >> This is actually wrong. The end result is the same but it does not >> in any way have the same e

Re: [GENERAL] Delete rule does not prevent truncate

On 07/22/2015 08:42 AM, Geoff Winkless wrote: On 22 July 2015 at 16:32, Joshua D. Drake mailto:j...@commandprompt.com>>wrote: This is actually wrong. The end result is the same but it does not in any way have the same effect. ​ "in any way"? ​ ​I'd say in the primary way it has the sam

Re: [GENERAL] Delete rule does not prevent truncate

On 22 July 2015 at 16:32, Joshua D. Drake wrote: > This is actually wrong. The end result is the same but it does not in any > way have the same effect. ​ "in any way"? ​ ​I'd say in the primary way it has the same effect: all rows are removed from the table. ​ > And I will submit a patch. >

Re: [GENERAL] Delete rule does not prevent truncate

On 07/22/2015 06:24 AM, Tim Smith wrote: Adrian, It still doesn't make much sense, especially as given the rather obscure and questionable design decision of allowing triggers to refer to truncate ops, but not allowing rules to refer to truncate ops !!! Actually it makes perfect sense becaus

Re: [GENERAL] Delete rule does not prevent truncate

On 07/22/2015 06:13 AM, Tim Smith wrote: Melvin, May I point out that the manual states : "TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table" This is actually wrong. The end result is the same but it does not in any way have

Re: [GENERAL] Delete rule does not prevent truncate

On 07/22/2015 06:24 AM, Tim Smith wrote: Adrian, It still doesn't make much sense, especially as given the rather obscure and questionable design decision of allowing triggers to refer to truncate ops, but not allowing rules to refer to truncate ops !!! Surely either you say "look, truncate is

Re: [GENERAL] Delete rule does not prevent truncate

No, I am saying if you CREATE PROCEDURE do_nothing() RETURNS VOID $BODY$ BEGIN RETURN; END LANGUAGE plpgsql; CREATE TRIGGER no_trunc INSTEAD OF TRUNCATE ON your_table EXECUTE PROCEDURE do_nothing; Then you can handle the problem. You should also create a TRIGGER for DELETE to do the same. On

Re: [GENERAL] Delete rule does not prevent truncate

Adrian, It still doesn't make much sense, especially as given the rather obscure and questionable design decision of allowing triggers to refer to truncate ops, but not allowing rules to refer to truncate ops !!! Surely either you say "look, truncate is truncate, its there for one purpose and one

Re: [GENERAL] Delete rule does not prevent truncate

On 07/22/2015 06:13 AM, Tim Smith wrote: Melvin, May I point out that the manual states : "TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table" Thus, if you are telling me to effectively think of TRUNCATE as an alias to DELETE, t

Re: [GENERAL] Delete rule does not prevent truncate

Melvin, May I point out that the manual states : "TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table" Thus, if you are telling me to effectively think of TRUNCATE as an alias to DELETE, then I would think its not entirely unreason

Re: [GENERAL] Delete rule does not prevent truncate

Actually, if you use a TRIGGER instead of rule, you can handle this. The manual states event can be: INSERT UPDATE [ OF column_name [, ... ] ] DELETE*TRUNCATE <-* http://www.postgresql.org/docs/9.4/interactive/sql-createtrigger.html I suggest you review carefully. On Wed, Jul 22, 2015 at

[GENERAL] Delete rule does not prevent truncate

Hi, I very much hope this is an accidental bug rather than a deliberate feature ! PostgreSQL 9.4.4 create rule no_auditupd as on update to app_security.app_audit do instead nothing; create rule no_auditdel as on delete to app_security.app_audit do instead nothing; \d+ app_security.app_audit R

Re: [GENERAL] delete is getting hung when there is a huge data in table

On Wed, May 6, 2015 at 1:56 AM, Mitu Verma wrote: > Thank you so much all of you. > > Table audittraillogentry have PRIMARY KEY and FOREIGN KEY defined, below is > the detail of existing table audittraillogentry. > > As you can see ., it is referenced by 2 tables , "cdrdetails" and > "cdrlogentr

Re: [GENERAL] delete is getting hung when there is a huge data in table

"cdrlogentry" CONSTRAINT "cdrlogentry_audittableindex_fkey" FOREIGN KEY (audittableindex) REFERENCES audittraillogentry(tableindex) Has OIDs: no Tablespace: "mmdata" -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: May 03, 2015 9:43

Re: [GENERAL] delete is getting hung when there is a huge data in table

> Now issue is that when this script for the deletion of data is launched , it is taking more than 7 days and doing nothing i.e not a single row has been deleted. Deleting a large number of rows can take a long time. Often it's quicker to delete smaller chunks. The LIMIT clause is not suppor

Re: [GENERAL] delete is getting hung when there is a huge data in table

This delete runs in a single transaction. That means the entire transaction has to complete before you will see anything deleted. Interrupting the transaction simply rolls it back, so nothing is deleted. Tom already pointed out the potential foreign key slowdown, another slowdown may simply be

Re: [GENERAL] delete is getting hung when there is a huge data in table

On Saturday, May 2, 2015, Mitu Verma wrote: > > still this delete operation is not working and not a single row has been > deleted from the table. > > Because of MVCC other sessions are not able to see partial deletions...and as you aluded to knowing the data itself is not actually removed by a d

Re: [GENERAL] delete is getting hung when there is a huge data in table

Mitu Verma writes: > 1. If postgreSQL has some limitations for deletion of large data? Not as such, but you've not given us any details that would permit comment. A reasonably likely bet is that this table is referenced by a foreign key in some other table, and that other table has no index on t

[GENERAL] delete is getting hung when there is a huge data in table

Hi, I am facing an issue with the deletion of huge data. We have a cronscript which is used to delete the data of last 3 months from one of the tables. Data in the table is large (8872597 as you can see the count below) since it is from last 3 months. fm_db_Server3=# select count(*) from auditt

Re: [GENERAL] Delete trigger and data integrity

On 27 May 2014 12:25, Yvonne Zannoun wrote: > CREATE OR REPLACE FUNCTION delete_records() > RETURNS TRIGGER AS $$ > BEGIN > delete from "TABLE"; > RETURN NEW; > END; > $$ > LANGUAGE plpgsql; You can't return NEW in an ON DELETE trigger - there is no NEW record. Since you're going with a STATE

Re: [GENERAL] Delete trigger and data integrity

Thank you very much, that answers my question. And yes, I think you are right with the FOR EACH ROW/STATEMENT, I didn't think that through for this example. Thank you for your help! Kind regards, *Yvonne Zannoun* Graduate Technical Consultant Snowflake Software *Tel: +44 (0) 23 80238 232* Emai

Re: [GENERAL] Delete trigger and data integrity

Yvonne Zannoun wrote: > I have this question regarding delete triggers and how it affects data > integrity. > So here goes: I have this trigger which deletes everything before I insert > new rows. > > CREATE OR REPLACE FUNCTION delete_records() > RETURNS TRIGGER AS $$ > BEGIN > delete from "

[GENERAL] Delete trigger and data integrity

Hello everyone, I have this question regarding delete triggers and how it affects data integrity. So here goes: I have this trigger which deletes everything before I insert new rows. CREATE OR REPLACE FUNCTION delete_records() RETURNS TRIGGER AS $$ BEGIN delete from "TABLE"; RETU

Re: [GENERAL] Delete duplicate records with same fields

Hi, For finding duplicates you can use ctid. select o.ctid, o.a, o.b from test o where exists ( select 'x' from test i where i.a = o.a and i.b = o.b and i.ctid < o.ctid ); for deleting, delete from test where

[GENERAL] Delete duplicate records with same fields

Hi all, Is there any way to delete duplicate rows in psql with no unique fields? I have a set of old records with their duplicates in my db and they are all having the same fields. How can I delete duplicates? Thanks in Advance,Arun

[GENERAL] DELETE with LIMIT - workaround?

I have something that I think is a fairly common code model, but with an SQL query that feels like it's fighting the system. The 'cron' table has a number of tasks (one row = one task), and the primary loop of the program (massively simplified) fetches one row, processes it, commits. One row/task

Re: [GENERAL] DELETE or TRUNCATE?

Hi, Yes,DELETE would be better this case. The TRUNCATE operation required AccessExclusiveLock on Table before perform TRUNCATE operation. So,if you the table size is bing,it is batter to do ANALYZE after report and VACUUM non-peak(less business) hours. Regards, Chiru On Thu, May 16, 2013 at

[GENERAL] DELETE or TRUNCATE?

Hi! I have a process that replaces the contents of a table. The canonical data store is somewhere else. At the moment, the import looks like this: CREATE TEMPORARY TABLE markets_import( LIKE x INCLUDING ALL ); COPY markets_import FROM STDIN; ... \. -- COPY a bunch of other tables BEGIN; TRUNCA

Re: [PERFORM] [GENERAL] DELETE taking too much memory

On Fri, Jul 8, 2011 at 4:35 AM, Dean Rasheed wrote: > > On Thu, 2011-07-07 at 15:34 +0200, vincent dephily wrote: > >> Hi, > >> > >> I have a delete query taking 7.2G of ram (and counting) but I do not > >> understant why so much memory is necessary. The server has 12G, and > >> I'm afraid it'll g

Re: [PERFORM] [GENERAL] DELETE taking too much memory

On Fri, Jul 8, 2011 at 12:48 PM, Dean Rasheed wrote: > Yes, it's the same issue that affects deferrable PK and FK > constraints, but even non-deferrable FKs use AFTER ROW triggers that > suffer from this problem. These triggers don't show up in a "\d" from > psql, but they are there (try select *

Re: [GENERAL] DELETE taking too much memory

On 8 July 2011 10:44, Vincent de Phily wrote: > On Friday 08 July 2011 10:05:47 Dean Rasheed wrote: >> > On Thu, 2011-07-07 at 15:34 +0200, vincent dephily wrote: >> >> Hi, >> >> >> >> I have a delete query taking 7.2G of ram (and counting) but I do not >> >> understant why so much memory is neces

Re: [GENERAL] DELETE taking too much memory

On Friday 08 July 2011 10:05:47 Dean Rasheed wrote: > > On Thu, 2011-07-07 at 15:34 +0200, vincent dephily wrote: > >> Hi, > >> > >> I have a delete query taking 7.2G of ram (and counting) but I do not > >> understant why so much memory is necessary. The server has 12G, and > >> I'm afraid it'll g

Re: [GENERAL] DELETE taking too much memory

On Thursday 07 July 2011 22:26:45 Guillaume Lelarge wrote: > On Thu, 2011-07-07 at 15:34 +0200, vincent dephily wrote: > > Hi, > > > > I have a delete query taking 7.2G of ram (and counting) but I do not > > understant why so much memory is necessary. The server has 12G, and > > I'm afraid it'll g

Re: [GENERAL] DELETE taking too much memory

> On Thu, 2011-07-07 at 15:34 +0200, vincent dephily wrote: >> Hi, >> >> I have a delete query taking 7.2G of ram (and counting) but I do not >> understant why so much memory is necessary. The server has 12G, and >> I'm afraid it'll go into swap. Using postgres 8.3.14. >> >> I'm purging some old da

Re: [GENERAL] DELETE taking too much memory

On Thu, 2011-07-07 at 15:34 +0200, vincent dephily wrote: > Hi, > > I have a delete query taking 7.2G of ram (and counting) but I do not > understant why so much memory is necessary. The server has 12G, and > I'm afraid it'll go into swap. Using postgres 8.3.14. > > I'm purging some old data from

[GENERAL] DELETE taking too much memory

Hi, I have a delete query taking 7.2G of ram (and counting) but I do not understant why so much memory is necessary. The server has 12G, and I'm afraid it'll go into swap. Using postgres 8.3.14. I'm purging some old data from table t1, which should cascade-delete referencing rows in t2. Here's an

Re: [GENERAL] Delete Trigger

Chris Berry writes: > When I update, insert or delete to one table, I need a trigger to delete and > possibly insert into another table. The trigger I have works for insert and > update, but returns an error when I do a delete. There's no "NEW" row in a delete trigger (... and the error message s

[GENERAL] Delete Trigger

When I update, insert or delete to one table, I need a trigger to delete and possibly insert into another table. The trigger I have works for insert and update, but returns an error when I do a delete. The is no explicit link between the tables, so I can't do 'DELETE CASCADE' or anything like that

Re: [GENERAL] delete query taking way too long

On Thu, 12 Aug 2010 12:50:49 +0100 Thom Brown wrote: > On 12 August 2010 12:14, Ivan Sergio Borgonovo > wrote: > > I've > > delete from catalog_items where ItemID in (select id from > > import.Articoli_delete); > > > > id and ItemID have an index. > > > > catalog_items is ~1M rows > > Articoli_d

Re: [GENERAL] delete query taking way too long

On Thu, 12 Aug 2010 17:14:17 +0530 Jayadevan M wrote: > > I've > > delete from catalog_items where ItemID in (select id from > > import.Articoli_delete); > Does catalog_items have child tables where the FK columns are not > indexed? Regards, Possibly, but very small ones. What I missed to say i

Re: [GENERAL] delete query taking way too long

> I've > delete from catalog_items where ItemID in (select id from > import.Articoli_delete); > > id and ItemID have an index. > > catalog_items is ~1M rows > Articoli_delete is less than 2K rows. > > This query has been running for roughly 50min. > Right now it is the only query running. > > Postg

Re: [GENERAL] delete query taking way too long

On 12 August 2010 12:14, Ivan Sergio Borgonovo wrote: > I've > delete from catalog_items where ItemID in (select id from > import.Articoli_delete); > > id and ItemID have an index. > > catalog_items is ~1M rows > Articoli_delete is less than 2K rows. > > This query has been running for roughly 50m

Re: [GENERAL] delete query taking way too long

> From: Ivan Sergio Borgonovo > To: pgsql-general@postgresql.org > Date: 12/08/2010 16:43 > Subject: [GENERAL] delete query taking way too long > Sent by: pgsql-general-ow...@postgresql.org > > I've > delete from catalog_items where ItemID in (select id from &g

Re: [GENERAL] delete query taking way too long

What's the output of explain? --- On Thu, 12/8/10, Ivan Sergio Borgonovo wrote: > From: Ivan Sergio Borgonovo > Subject: [GENERAL] delete query taking way too long > To: pgsql-general@postgresql.org > Date: Thursday, 12 August, 2010, 12:14 > I've > delete from c

  1   2   3   4   >