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

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  

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

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

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:

[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   

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

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

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:

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

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

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

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

[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

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)

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

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

[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

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 <pgsql-general@postgresql.org> > Subject: [GENERAL] Delete

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

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

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

Re: [GENERAL] Delete trigger

> > -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 <pgsql-general@postgresql.org> > > Subject: [GENER

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

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

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 <pgsql-general@postgresql.org> > Subject: [GENERAL] Delete trigger > >Hi, > >I am running PostgreSQL 9.3.4. I have a

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

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 scott.marl...@gmail.com 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

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

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. Andres

Re: [GENERAL] Delete rule does not prevent truncate

On 24 July 2015 at 01:37, Rob Sargent robjsarg...@gmail.com 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.

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, if

Re: [GENERAL] Delete rule does not prevent truncate

On Fri, Jul 24, 2015 at 3:27 AM, Tim Smith randomd...@gmail.com wrote: On 23 July 2015 at 19:25, Scott Marlowe scott.marl...@gmail.com 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

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

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

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

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.

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

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

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

Re: [GENERAL] Delete rule does not prevent truncate

On Thu, Jul 23, 2015 at 12:09 PM, Adrian Klaver adrian.kla...@aklaver.com 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

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

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

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

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

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

Re: [GENERAL] Delete rule does not prevent truncate

2015-07-23 9:06 GMT+02:00 Tim Smith randomdev4+postg...@gmail.com: 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

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

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

Re: [GENERAL] Delete rule does not prevent truncate

On 22 July 2015 at 16:32, Joshua D. Drake 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 same effect: all rows are removed from the table. ​ And I will

Re: [GENERAL] Delete rule does not prevent truncate

On 22 July 2015 at 16:55, Joshua D. Drake j...@commandprompt.com wrote: On 07/22/2015 08:42 AM, Geoff Winkless wrote: On 22 July 2015 at 16:32, Joshua D. Drake j...@commandprompt.com mailto:j...@commandprompt.comwrote: This is actually wrong. The end result is the same but it does not

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 08:42 AM, Geoff Winkless wrote: On 22 July 2015 at 16:32, Joshua D. Drake j...@commandprompt.com mailto:j...@commandprompt.comwrote: 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

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.

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,

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

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

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

[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

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

...@sss.pgh.pa.us] Sent: May 03, 2015 9:43 AM To: Mitu Verma Cc: 'pgsql-general@postgresql.org' (pgsql-general@postgresql.org) Subject: Re: [GENERAL] delete is getting hung when there is a huge data in table Mitu Verma mitu.ve...@ericsson.com writes: 1. If postgreSQL has some limitations for deletion

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 mitu.ve...@ericsson.com 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

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

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

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

On Saturday, May 2, 2015, Mitu Verma mitu.ve...@ericsson.com 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

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

Mitu Verma mitu.ve...@ericsson.com 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

[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

[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;

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 TABLE;

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*

Re: [GENERAL] Delete trigger and data integrity

On 27 May 2014 12:25, Yvonne Zannoun yvonne.zann...@snowflakesoftware.com 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.

[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

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

[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;

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 Table after report and VACUUM table non-peak(less business) hours. Regards, Chiru On Thu, May

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 table

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 go into

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 go into swap.

Re: [GENERAL] DELETE taking too much memory

On 8 July 2011 10:44, Vincent de Phily vincent.deph...@mobile-devices.fr 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

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

On Fri, Jul 8, 2011 at 12:48 PM, Dean Rasheed dean.a.rash...@gmail.com 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

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

On Fri, Jul 8, 2011 at 4:35 AM, Dean Rasheed dean.a.rash...@gmail.comwrote: 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

[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

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 table

[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

Re: [GENERAL] Delete Trigger

Chris Berry chrisdberr...@googlemail.com 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 (...

[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. PostgreSQL 8.3.4 on

Re: [GENERAL] delete query taking way too long

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

Re: [GENERAL] delete query taking way too long

From: Ivan Sergio Borgonovo m...@webthatworks.it 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 import.Articoli_delete

Re: [GENERAL] delete query taking way too long

On 12 August 2010 12:14, Ivan Sergio Borgonovo m...@webthatworks.it 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

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. PostgreSQL 8.3.4

Re: [GENERAL] delete query taking way too long

On Thu, 12 Aug 2010 17:14:17 +0530 Jayadevan M jayadevan.maym...@ibsplc.com 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.

  1   2   3   4   >