Re: [GENERAL] Index seems "lost" after consecutive deletes

[Edson Richter]

Em 14/06/2016 01:33, David G. Johnston escreveu:
On Monday, June 13, 2016, Edson Richter > wrote:


Em 13/06/2016 23:36, Edson Richter escreveu:

Em 13/06/2016 23:18, rob stone escreveu:

On Mon, 2016-06-13 at 22:41 -0300, Edson Richter wrote:

Em 13/06/2016 22:33, Edson Richter escreveu:

I've a table "A" with 4,000,000 records.

I've decided to delete records from oldest to newest but I can't
delete records that have references in tables "B", "C" or "D".


so, I've


with qry as (

 select A.id

   from A

 where not exists (select 1 from B where B.a_id = A.id)

and not exists (select 1 from C where C.a_id = A.id)

and not exists (select 1 from D where D.a_id = A.id)

and A.creation_date < (now()::date - interval '12 month')

   order by A.id DESC

   limit 2000

)

delete from A where id in (select id from qry);


All three referenced tables have indexes (B.a_id; C.a_id; D.a_id)
in
order to make query faster.

So for first 2 million rows it worked really well, taking about 1
minute to delete each group of 2000 records.

Then, after a while I just started to get errors like:


ERROR: update or delete in "A" violates foreign key "fk_C_A" in
"C".

DETAIL:  Key (id)=(3240124) is still referenced by table "C".


Seems to me that indexes got lost in the path - the query is
really
specific and no "C" referenced records can be in my deletion.

Has anyone faced a behavior like this?

Am I doing something wrong?



Of course:
Version stringPostgreSQL 9.4.8 on x86_64-unknown-linux-gnu,
compiled
by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
Oracle Linux 7 x64 with all updates. Running on EXT4 file system.
Computer is Dell R420 with mirrored disks, 80GB of RAM (database has
<
40GB in total).

Sorry for not putting the info in the first e-mail.

Edson



What does:-

SELECT COUNT(*) FROM C WHERE C.a_id = 3240124;

return?

Is it a many-to-one or a one-to-one relationship?




SELECT COUNT(*) FROM C WHERE C.a_id = 3240124;
 count
---
 1
(1 registro)


A.id is primary key of A table. Each table has its own primary key.

Relationship to others table is 1-N, being N = {0,1}


A.id -> B.a_id (being B.a_id unique but not enforced by unique key)

A.id -> C.a_id (being C.a_id unique but not enforced by unique key)

A.id -> D.a_id (being D.a_id unique but not enforced by unique key)


Regards,

Edson


Just in case, I've run:

- vacuum full analyze verbose;
- reindex index ix_c_a_id;

Result I get same error. So, I'm inclined to discard that this is
a index error.


Interesting:

with qry as (select A.id
 from A
where creatingdate < (now()::date - interval '12 month')
and not exists (select 1 from B where B.a_id = A.id)
and not exists (select 1 from C where C.a_id = A.id)
and not exists (select 1 from D where D.a_id = A.id)
order by A.id limit 2000)

select * from qry where id = 3240124;


Why do you assume 3240124 is within the first 2000 qualified records 
that the CTE is limited to checking?


Because this is the key causing the error on delete.




Total query runtime: 2.2 secs
0 rows retrieved.


Why delete causes error, but querying don't?


Given the error message this answer seems self-evident...


Not self-evident to me: delete says I'm trying to delete the record with 
id = 3240124 and I can't. But the select says this record with id = 
3240124 is not there!!!




Would it be a bug when using delete ... where id in (subquery)?

I'm unsure regarding the solution but I suspect the problem is that 
between selecting the A row and deleting it another concurrent process 
added a record to C that, if you were to re-run the select would cause 
the row from A to be skipped.  But the single query doesn't have that 
option so it ends up failing.


There is a FOR UPDATE clause you can add to the select but I don't 
think that works here since table C is the one being altered and at 
the time of the query there is nothing to lock.


I'm doubting this is a bug, just poor concurrency understanding.  
Sorry I cannot enlighten further at the moment.


There is not other process adding/updating records - and even there is, 
the ID would be so high (because it is a sequence) that cannot be the 
3240124 (this record has been generated more than 2 years ago - that's 
why my query has the "creationdate" filter - I don't touch records 
created within last 12 months).


Also, I've tried the same with "for update": same error!


Edson


David J.

Re: [GENERAL] Index seems "lost" after consecutive deletes

[David G. Johnston]

On Monday, June 13, 2016, Edson Richter  wrote:

> Em 13/06/2016 23:36, Edson Richter escreveu:
>
> Em 13/06/2016 23:18, rob stone escreveu:
>
> On Mon, 2016-06-13 at 22:41 -0300, Edson Richter wrote:
>
> Em 13/06/2016 22:33, Edson Richter escreveu:
>
> I've a table "A" with 4,000,000 records.
>
> I've decided to delete records from oldest to newest but I can't
> delete records that have references in tables "B", "C" or "D".
>
>
> so, I've
>
>
> with qry as (
>
> select A.id
>
>   from A
>
> where not exists (select 1 from B where B.a_id = A.id)
>
>and not exists (select 1 from C where C.a_id = A.id)
>
>and not exists (select 1 from D where D.a_id = A.id)
>
>and A.creation_date < (now()::date - interval '12 month')
>
>   order by A.id DESC
>
>   limit 2000
>
> )
>
> delete from A where id in (select id from qry);
>
>
> All three referenced tables have indexes (B.a_id; C.a_id; D.a_id)
> in
> order to make query faster.
>
> So for first 2 million rows it worked really well, taking about 1
> minute to delete each group of 2000 records.
>
> Then, after a while I just started to get errors like:
>
>
> ERROR: update or delete in "A" violates foreign key "fk_C_A" in
> "C".
>
> DETAIL:  Key (id)=(3240124) is still referenced by table "C".
>
>
> Seems to me that indexes got lost in the path - the query is
> really
> specific and no "C" referenced records can be in my deletion.
>
> Has anyone faced a behavior like this?
>
> Am I doing something wrong?
>
>
>
> Of course:
> Version stringPostgreSQL 9.4.8 on x86_64-unknown-linux-gnu,
> compiled
> by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
> Oracle Linux 7 x64 with all updates. Running on EXT4 file system.
> Computer is Dell R420 with mirrored disks, 80GB of RAM (database has
> <
> 40GB in total).
>
> Sorry for not putting the info in the first e-mail.
>
> Edson
>
>
>
> What does:-
>
> SELECT COUNT(*) FROM C WHERE C.a_id = 3240124;
>
> return?
>
> Is it a many-to-one or a one-to-one relationship?
>
>
>
>
> SELECT COUNT(*) FROM C WHERE C.a_id = 3240124;
>
>  count
> ---
>  1
> (1 registro)
>
>
> A.id is primary key of A table. Each table has its own primary key.
>
> Relationship to others table is 1-N, being N = {0,1}
>
>
> A.id -> B.a_id (being B.a_id unique but not enforced by unique key)
>
> A.id -> C.a_id (being C.a_id unique but not enforced by unique key)
>
> A.id -> D.a_id (being D.a_id unique but not enforced by unique key)
>
>
> Regards,
>
> Edson
>
>
> Just in case, I've run:
>
> - vacuum full analyze verbose;
> - reindex index ix_c_a_id;
>
> Result I get same error. So, I'm inclined to discard that this is a index
> error.
>
>
> Interesting:
>
> with qry as (select A.id
>  from A
> where creatingdate < (now()::date - interval '12 month')
> and not exists (select 1 from B where B.a_id = A.id)
> and not exists (select 1 from C where C.a_id = A.id)
> and not exists (select 1 from D where D.a_id = A.id)
> order by A.id limit 2000)
>
> select * from qry where id = 3240124;
>

Why do you assume 3240124 is within the first 2000 qualified records that
the CTE is limited to checking?


>
>
> Total query runtime: 2.2 secs
> 0 rows retrieved.
>
>
> Why delete causes error, but querying don't?
>

Given the error message this answer seems self-evident...


>
> Would it be a bug when using delete ... where id in (subquery)?
>
>

I'm unsure regarding the solution but I suspect the problem is that between
selecting the A row and deleting it another concurrent process added a
record to C that, if you were to re-run the select would cause the row from
A to be skipped.  But the single query doesn't have that option so it ends
up failing.

There is a FOR UPDATE clause you can add to the select but I don't think
that works here since table C is the one being altered and at the time of
the query there is nothing to lock.

I'm doubting this is a bug, just poor concurrency understanding.  Sorry I
cannot enlighten further at the moment.

David J.

Re: [GENERAL] Automate copy - Postgres 9.2

[Patrick B]

Yep... thanks Melvin..

It's working as expected now

Cheers

Re: [GENERAL] Index seems "lost" after consecutive deletes

[Edson Richter]

Em 13/06/2016 23:36, Edson Richter escreveu:

Em 13/06/2016 23:18, rob stone escreveu:

On Mon, 2016-06-13 at 22:41 -0300, Edson Richter wrote:

Em 13/06/2016 22:33, Edson Richter escreveu:

I've a table "A" with 4,000,000 records.

I've decided to delete records from oldest to newest but I can't
delete records that have references in tables "B", "C" or "D".


so, I've


with qry as (

 select A.id

   from A

 where not exists (select 1 from B where B.a_id = A.id)

and not exists (select 1 from C where C.a_id = A.id)

and not exists (select 1 from D where D.a_id = A.id)

and A.creation_date < (now()::date - interval '12 month')

   order by A.id DESC

   limit 2000

)

delete from A where id in (select id from qry);


All three referenced tables have indexes (B.a_id; C.a_id; D.a_id)
in
order to make query faster.

So for first 2 million rows it worked really well, taking about 1
minute to delete each group of 2000 records.

Then, after a while I just started to get errors like:


ERROR: update or delete in "A" violates foreign key "fk_C_A" in
"C".

DETAIL:  Key (id)=(3240124) is still referenced by table "C".


Seems to me that indexes got lost in the path - the query is
really
specific and no "C" referenced records can be in my deletion.

Has anyone faced a behavior like this?

Am I doing something wrong?



Of course:
Version stringPostgreSQL 9.4.8 on x86_64-unknown-linux-gnu,
compiled
by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
Oracle Linux 7 x64 with all updates. Running on EXT4 file system.
Computer is Dell R420 with mirrored disks, 80GB of RAM (database has
<
40GB in total).

Sorry for not putting the info in the first e-mail.

Edson



What does:-

SELECT COUNT(*) FROM C WHERE C.a_id = 3240124;

return?

Is it a many-to-one or a one-to-one relationship?




SELECT COUNT(*) FROM C WHERE C.a_id = 3240124;
 count
---
 1
(1 registro)


A.id is primary key of A table. Each table has its own primary key.

Relationship to others table is 1-N, being N = {0,1}


A.id -> B.a_id (being B.a_id unique but not enforced by unique key)

A.id -> C.a_id (being C.a_id unique but not enforced by unique key)

A.id -> D.a_id (being D.a_id unique but not enforced by unique key)


Regards,

Edson


Just in case, I've run:

- vacuum full analyze verbose;
- reindex index ix_c_a_id;

Result I get same error. So, I'm inclined to discard that this is a 
index error.



Interesting:

with qry as (select A.id
 from A
where creatingdate < (now()::date - interval '12 month')
and not exists (select 1 from B where B.a_id = A.id)
and not exists (select 1 from C where C.a_id = A.id)
and not exists (select 1 from D where D.a_id = A.id)
order by A.id limit 2000)

select * from qry where id = 3240124;

Total query runtime: 2.2 secs
0 rows retrieved.


Why delete causes error, but querying don't?
Would it be a bug when using delete ... where id in (subquery)?

Regards,

Edson

Re: [GENERAL] Index seems "lost" after consecutive deletes

[Edson Richter]

Em 13/06/2016 23:18, rob stone escreveu:

On Mon, 2016-06-13 at 22:41 -0300, Edson Richter wrote:

Em 13/06/2016 22:33, Edson Richter escreveu:

I've a table "A" with 4,000,000 records.

I've decided to delete records from oldest to newest but I can't
delete records that have references in tables "B", "C" or "D".


so, I've


with qry as (

 select A.id

   from A

 where not exists (select 1 from B where B.a_id = A.id)

and not exists (select 1 from C where C.a_id = A.id)

and not exists (select 1 from D where D.a_id = A.id)

and A.creation_date < (now()::date - interval '12 month')

   order by A.id DESC

   limit 2000

)

delete from A where id in (select id from qry);


All three referenced tables have indexes (B.a_id; C.a_id; D.a_id)
in
order to make query faster.

So for first 2 million rows it worked really well, taking about 1
minute to delete each group of 2000 records.

Then, after a while I just started to get errors like:


ERROR: update or delete in "A" violates foreign key "fk_C_A" in
"C".

DETAIL:  Key (id)=(3240124) is still referenced by table "C".


Seems to me that indexes got lost in the path - the query is
really
specific and no "C" referenced records can be in my deletion.

Has anyone faced a behavior like this?

Am I doing something wrong?



Of course:
Version stringPostgreSQL 9.4.8 on x86_64-unknown-linux-gnu,
compiled
by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
Oracle Linux 7 x64 with all updates. Running on EXT4 file system.
Computer is Dell R420 with mirrored disks, 80GB of RAM (database has
<
40GB in total).

Sorry for not putting the info in the first e-mail.

Edson



What does:-

SELECT COUNT(*) FROM C WHERE C.a_id = 3240124;

return?

Is it a many-to-one or a one-to-one relationship?




SELECT COUNT(*) FROM C WHERE C.a_id = 3240124;

 count
---
 1
(1 registro)


A.id is primary key of A table. Each table has its own primary key.

Relationship to others table is 1-N, being N = {0,1}


A.id -> B.a_id (being B.a_id unique but not enforced by unique key)

A.id -> C.a_id (being C.a_id unique but not enforced by unique key)

A.id -> D.a_id (being D.a_id unique but not enforced by unique key)


Regards,

Edson

Re: [GENERAL] Automate copy - Postgres 9.2

[Melvin Davidson]

On Mon, Jun 13, 2016 at 10:07 PM, rob stone  wrote:

> On Tue, 2016-06-14 at 13:33 +1200, Patrick B wrote:
> >
> >
> > 2016-06-14 9:47 GMT+12:00 Adrian Klaver :
> > > On 06/13/2016 02:42 PM, Patrick B wrote:
> > > > Hi guys,
> > > >
> > > > I created this function:
> > > >
> > > > CREATE or REPLACE FUNCTION function(account_id
> > > > integer)
> > > >
> > > > RETURNS void AS $$
> > > >
> > > > begin
> > > >
> > > > execute 'COPY
> > > >
> > > > (
> > > >
> > > > SELECT * FROM backup_table WHERE account_id = ' ||
> > > > account_id || 'AND status = 1
> > > >
> > > > )
> > > >
> > > > TO ''/var/lib/pgsql/'||account_id||'.sql''';
> > > >
> > > > end
> > > >
> > > > $$ language 'plpgsql';
> > > >
> > > >
> > > >
> > > > The function works fine.. but is not what I need, actually.
> > > > The function above works by calling it specifying the account_id.
> > > > For
> > > > example:
> > > >
> > > > You want to copy ( backup ) for the account_id number 63742:
> > > >
> > > > select function(63742);
> > > >
> > > >
> > > > *What I need is:*
> > > >
> > > > When calling the function, I have to specify the limit of
> > > > account_ids to
> > > > be copied. For example:
> > > > To perform the commands in the function to 40 different
> > > > account_ids:
> > > >
> > > > select function (40);
> > > >
> > > >
> > > >
> > > > How can I do that? I can't...
> > > >
> > >
> > > I believe this has been asked and answered, namely there needs to
> > > be further information on how you want to determine the account ids
> > > to be selected.
> > >
> >
> > The account_ids can be random.. does not need to have an order, as
> > they all will be copied some day.
> >
> > There are more than 1.000.000 million rows in that backup table (
> > attachments: as pictures, pdfs, etc ), and that's why I can't specify
> > the account_Id manually.. and also need a limit, so the server won't
> > stop while performing the COPY
> >
> > > - Also, each file must have the account_id's name. Example for the
> > > account_id = 124134
> > > 124134.sql
> >
> > Please, if you guys could give a help here..
> > Cheers
> > P.
>
>
> 1) COPY TO produces a text file and having a file created with a suffix
> of SQL is slightly misleading. You can specify delimiter and
> encapsulation characters. So, a suffix of csv is more appropriate.
>
> 2) Assuming backup_table is static and nobody is inserting or updating
> data, you just need to read the rows and write out the million or so
> individual files. If it's not static, you'll have to update (or delete)
> the rows written to file, perhaps SET status = 2, so that you don't
> write the same file multiple times.
>
> 3) If you are worried about throttling, put a pause into the program
> looping through backup_table. You have to write a program to read
> backup_table in order to supply your function with an account_id.
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

It sounds to me like you are over complicating the problem.
You should make the function accept two parameters, a start and a stop id.
eg: CREATE or REPLACE FUNCTION function(start_account_id integer,
end__account_id)

Then you can modify it to fetch through a temp table table of just the
account_id's you need.

eg:
DECLARE
  refcur refcursor;

BEGIN
  CREATE TEMP TABLE accts_to_copy
  AS SELECT account_id from backup_table
 WHERE account_id >= start_account_id
  AND account_id <= end_account_id;


  FOR account_id IN refcur
SELECT account_id FROM accts_to_copy LOOP
   do_your_copy
END LOOP ;


-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Index seems "lost" after consecutive deletes

[rob stone]

On Mon, 2016-06-13 at 22:41 -0300, Edson Richter wrote:
> Em 13/06/2016 22:33, Edson Richter escreveu:
> > I've a table "A" with 4,000,000 records.
> > 
> > I've decided to delete records from oldest to newest but I can't 
> > delete records that have references in tables "B", "C" or "D".
> > 
> > 
> > so, I've
> > 
> > 
> > with qry as (
> > 
> > select A.id
> > 
> >   from A
> > 
> > where not exists (select 1 from B where B.a_id = A.id)
> > 
> >    and not exists (select 1 from C where C.a_id = A.id)
> > 
> >    and not exists (select 1 from D where D.a_id = A.id)
> > 
> >    and A.creation_date < (now()::date - interval '12 month')
> > 
> >   order by A.id DESC
> > 
> >   limit 2000
> > 
> > )
> > 
> > delete from A where id in (select id from qry);
> > 
> > 
> > All three referenced tables have indexes (B.a_id; C.a_id; D.a_id)
> > in 
> > order to make query faster.
> > 
> > So for first 2 million rows it worked really well, taking about 1 
> > minute to delete each group of 2000 records.
> > 
> > Then, after a while I just started to get errors like:
> > 
> > 
> > ERROR: update or delete in "A" violates foreign key "fk_C_A" in
> > "C".
> > 
> > DETAIL:  Key (id)=(3240124) is still referenced by table "C".
> > 
> > 
> > Seems to me that indexes got lost in the path - the query is
> > really 
> > specific and no "C" referenced records can be in my deletion.
> > 
> > Has anyone faced a behavior like this?
> > 
> > Am I doing something wrong?
> > 
> > 
> 
> Of course:
> Version stringPostgreSQL 9.4.8 on x86_64-unknown-linux-gnu,
> compiled 
> by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
> Oracle Linux 7 x64 with all updates. Running on EXT4 file system.
> Computer is Dell R420 with mirrored disks, 80GB of RAM (database has
> < 
> 40GB in total).
> 
> Sorry for not putting the info in the first e-mail.
> 
> Edson
> 
> 

What does:-

SELECT COUNT(*) FROM C WHERE C.a_id = 3240124;

return?

Is it a many-to-one or a one-to-one relationship?




-- 
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] Automate copy - Postgres 9.2

[rob stone]

On Tue, 2016-06-14 at 13:33 +1200, Patrick B wrote:
> 
> 
> 2016-06-14 9:47 GMT+12:00 Adrian Klaver :
> > On 06/13/2016 02:42 PM, Patrick B wrote:
> > > Hi guys,
> > > 
> > > I created this function:
> > > 
> > >             CREATE or REPLACE FUNCTION function(account_id
> > > integer)
> > > 
> > >             RETURNS void AS $$
> > > 
> > >             begin
> > > 
> > >             execute 'COPY
> > > 
> > >             (
> > > 
> > >             SELECT * FROM backup_table WHERE account_id = ' ||
> > >             account_id || 'AND status = 1
> > > 
> > >             )
> > > 
> > >             TO ''/var/lib/pgsql/'||account_id||'.sql''';
> > > 
> > >             end
> > > 
> > >             $$ language 'plpgsql';
> > > 
> > > 
> > > 
> > > The function works fine.. but is not what I need, actually.
> > > The function above works by calling it specifying the account_id.
> > > For
> > > example:
> > > 
> > > You want to copy ( backup ) for the account_id number 63742:
> > > 
> > >     select function(63742);
> > > 
> > > 
> > > *What I need is:*
> > > 
> > > When calling the function, I have to specify the limit of
> > > account_ids to
> > > be copied. For example:
> > > To perform the commands in the function to 40 different
> > > account_ids:
> > > 
> > >     select function (40);
> > > 
> > > 
> > > 
> > > How can I do that? I can't...
> > > 
> >  
> > I believe this has been asked and answered, namely there needs to
> > be further information on how you want to determine the account ids
> > to be selected.
> > 
> 
> The account_ids can be random.. does not need to have an order, as
> they all will be copied some day.
> 
> There are more than 1.000.000 million rows in that backup table (
> attachments: as pictures, pdfs, etc ), and that's why I can't specify
> the account_Id manually.. and also need a limit, so the server won't
> stop while performing the COPY
> 
> > - Also, each file must have the account_id's name. Example for the
> > account_id = 124134
> > 124134.sql
> 
> Please, if you guys could give a help here.. 
> Cheers
> P.


1) COPY TO produces a text file and having a file created with a suffix
of SQL is slightly misleading. You can specify delimiter and
encapsulation characters. So, a suffix of csv is more appropriate.

2) Assuming backup_table is static and nobody is inserting or updating
data, you just need to read the rows and write out the million or so
individual files. If it's not static, you'll have to update (or delete)
the rows written to file, perhaps SET status = 2, so that you don't
write the same file multiple times.

3) If you are worried about throttling, put a pause into the program
looping through backup_table. You have to write a program to read
backup_table in order to supply your function with an account_id.




-- 
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] Index seems "lost" after consecutive deletes

[Edson Richter]

Em 13/06/2016 22:33, Edson Richter escreveu:

I've a table "A" with 4,000,000 records.

I've decided to delete records from oldest to newest but I can't 
delete records that have references in tables "B", "C" or "D".



so, I've


with qry as (

select A.id

  from A

where not exists (select 1 from B where B.a_id = A.id)

   and not exists (select 1 from C where C.a_id = A.id)

   and not exists (select 1 from D where D.a_id = A.id)

   and A.creation_date < (now()::date - interval '12 month')

  order by A.id DESC

  limit 2000

)

delete from A where id in (select id from qry);


All three referenced tables have indexes (B.a_id; C.a_id; D.a_id) in 
order to make query faster.


So for first 2 million rows it worked really well, taking about 1 
minute to delete each group of 2000 records.


Then, after a while I just started to get errors like:


ERROR: update or delete in "A" violates foreign key "fk_C_A" in "C".

DETAIL:  Key (id)=(3240124) is still referenced by table "C".


Seems to me that indexes got lost in the path - the query is really 
specific and no "C" referenced records can be in my deletion.


Has anyone faced a behavior like this?

Am I doing something wrong?




Of course:
Version stringPostgreSQL 9.4.8 on x86_64-unknown-linux-gnu, compiled 
by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit

Oracle Linux 7 x64 with all updates. Running on EXT4 file system.
Computer is Dell R420 with mirrored disks, 80GB of RAM (database has < 
40GB in total).


Sorry for not putting the info in the first e-mail.

Edson


--
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] Automate copy - Postgres 9.2

[Patrick B]

2016-06-14 9:47 GMT+12:00 Adrian Klaver :

> On 06/13/2016 02:42 PM, Patrick B wrote:
>
>> Hi guys,
>>
>> I created this function:
>>
>> CREATE or REPLACE FUNCTION function(account_id integer)
>>
>> RETURNS void AS $$
>>
>> begin
>>
>> execute 'COPY
>>
>> (
>>
>> SELECT * FROM backup_table WHERE account_id = ' ||
>> account_id || 'AND status = 1
>>
>> )
>>
>> TO ''/var/lib/pgsql/'||account_id||'.sql''';
>>
>> end
>>
>> $$ language 'plpgsql';
>>
>>
>>
>> The function works fine.. but is not what I need, actually.
>> The function above works by calling it specifying the account_id. For
>> example:
>>
>> You want to copy ( backup ) for the account_id number 63742:
>>
>> select function(63742);
>>
>>
>> *What I need is:*
>>
>> When calling the function, I have to specify the limit of account_ids to
>> be copied. For example:
>> To perform the commands in the function to 40 different account_ids:
>>
>> select function (40);
>>
>>
>>
>> How can I do that? I can't...
>>
>
> I believe this has been asked and answered, namely there needs to be
> further information on how you want to determine the account ids to be
> selected.
>
>
The account_ids can be random.. does not need to have an order, as they all
will be copied some day.

There are more than 1.000.000 million rows in that backup table (
attachments: as pictures, pdfs, etc ), and that's why I can't specify the
account_Id manually.. and also need a limit, so the server won't stop while
performing the COPY

- Also, each file must have the account_id's name. Example for the
> account_id = 124134
> 124134.sql


Please, if you guys could give a help here..
Cheers
P.

[GENERAL] Index seems "lost" after consecutive deletes

[Edson Richter]

I've a table "A" with 4,000,000 records.

I've decided to delete records from oldest to newest but I can't delete 
records that have references in tables "B", "C" or "D".



so, I've


with qry as (

select A.id

  from A

where not exists (select 1 from B where B.a_id = A.id)

   and not exists (select 1 from C where C.a_id = A.id)

   and not exists (select 1 from D where D.a_id = A.id)

   and A.creation_date < (now()::date - interval '12 month')

  order by A.id DESC

  limit 2000

)

delete from A where id in (select id from qry);


All three referenced tables have indexes (B.a_id; C.a_id; D.a_id) in 
order to make query faster.


So for first 2 million rows it worked really well, taking about 1 minute 
to delete each group of 2000 records.


Then, after a while I just started to get errors like:


ERROR: update or delete in "A" violates foreign key "fk_C_A" in "C".

DETAIL:  Key (id)=(3240124) is still referenced by table "C".


Seems to me that indexes got lost in the path - the query is really specific and no 
"C" referenced records can be in my deletion.

Has anyone faced a behavior like this?

Am I doing something wrong?


--
Atenciosamente,

Edson Carlos Ericksson Richter



--
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] Automate copy - Postgres 9.2

[Adrian Klaver]

On 06/13/2016 02:42 PM, Patrick B wrote:

Hi guys,

I created this function:

CREATE or REPLACE FUNCTION function(account_id integer)

RETURNS void AS $$

begin

execute 'COPY

(

SELECT * FROM backup_table WHERE account_id = ' ||
account_id || 'AND status = 1

)

TO ''/var/lib/pgsql/'||account_id||'.sql''';

end

$$ language 'plpgsql';



The function works fine.. but is not what I need, actually.
The function above works by calling it specifying the account_id. For
example:

You want to copy ( backup ) for the account_id number 63742:

select function(63742);


*What I need is:*

When calling the function, I have to specify the limit of account_ids to
be copied. For example:
To perform the commands in the function to 40 different account_ids:

select function (40);



How can I do that? I can't...


I believe this has been asked and answered, namely there needs to be 
further information on how you want to determine the account ids to be 
selected.




Cheers
Patrick



--
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] Automate copy - Postgres 9.2

[Patrick B]

Hi guys,

I created this function:

CREATE or REPLACE FUNCTION function(account_id integer)

RETURNS void AS $$

begin

execute 'COPY

(

SELECT * FROM backup_table WHERE account_id = ' || account_id || 'AND
> status = 1

)

TO ''/var/lib/pgsql/'||account_id||'.sql''';

end

$$ language 'plpgsql';



The function works fine.. but is not what I need, actually.
The function above works by calling it specifying the account_id. For
example:

You want to copy ( backup ) for the account_id number 63742:

select function(63742);


*What I need is:*

When calling the function, I have to specify the limit of account_ids to be
copied. For example:
To perform the commands in the function to 40 different account_ids:

select function (40);



How can I do that? I can't...

Cheers
Patrick

Re: [GENERAL] BDR

[Alvaro Aguayo Garcia-Rada]

Block based replication is the replication mechanism postgres incorporates 
natively. It's, in brief, sending all the file-level changes to all the slaves, 
so the data folder is always the same. It's like having a replicated folder, 
not including logs and some other things.

The disadvantage of block level replication, according to BDR, it that, when 
you have an open transaction, all DML is written to disk, even when the 
transaction is not committed. In a large transaction, all the date would be 
send to the slaves, even when, at the end, the transaction is rolled back. With 
BDR, the transaction is sent to the other masters only once it's committed.

Of course, this can be a problem in both cases, depending on your environment. 
With block level replication, you can get unnecessary traffic for transactions 
that would be finally rolled back(in contrast with BDR, which will send the 
whole transaction once it's committed); on BDR, you will get traffic peaks for 
some large transactions, as the whole transaction is sent once it's 
committed(in contrast to block level replication, which would send changes as 
they are being executed). The later can also cause some delay, depending on the 
connection between the servers:

Regards,

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 
954183248
Website: www.ocs.pe

- Original Message -
From: "Rakesh Kumar" 
Cc: "PostgreSql-general" 
Sent: Monday, 13 June, 2016 07:13:09
Subject: Re: [GENERAL] BDR

http://bdr-project.org/docs/next/logical-vs-physical.html

"It (BDR) has significant advantages - and some disadvantages - when
compared to PostgreSQL's older physical (block-based) streaming or
archive-based replication with warm or hot standby"

What exactly is block based? Changes are recorded in the redo log,
right? Does that mean that in streaming replication, from redo log the
server applies changes at the block level of the data-file. That would
also mean that at any time, both primary and standby would be exactly
same, block by block.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


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

[Rakesh Kumar]

http://bdr-project.org/docs/next/logical-vs-physical.html

"It (BDR) has significant advantages - and some disadvantages - when
compared to PostgreSQL's older physical (block-based) streaming or
archive-based replication with warm or hot standby"

What exactly is block based? Changes are recorded in the redo log,
right? Does that mean that in streaming replication, from redo log the
server applies changes at the block level of the data-file. That would
also mean that at any time, both primary and standby would be exactly
same, block by block.


-- 
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] table name size

[David G. Johnston]

On Mon, Jun 13, 2016 at 7:21 AM, Sridhar N Bamandlapally <
sridhar@gmail.com> wrote:

> Hi
>
> how to create table name with size, the limitation we are facing is 63
> length
>
> these are dynamic tables created from application
>
> issue is:
> we cannot suggest/tell client to change NAMEDATALEN constant in
> src/include/pg_config_manual.h
>
> do we have any other option,
>
>
​Ensure that the application's algorithm for generating names doesn't
generate names that exceed 63 characters.​

​If you're asking if there a runtime setting to control this the answer is
no.​

​David J.​

[GENERAL] table name size

[Sridhar N Bamandlapally]

Hi

how to create table name with size, the limitation we are facing is 63
length

these are dynamic tables created from application

issue is:
we cannot suggest/tell client to change NAMEDATALEN constant in
src/include/pg_config_manual.h

do we have any other option,

please

Thanks
Sridhar
OpenText