[GENERAL] Er Data Modeller for PostgreSQL

[Günce Kaya]

Hi All,

I'm looking for an ER Data Modeller tool for postgresql. I use Navicat
Premium for postgresql and the tool has a modeller but I would like to
display a database modeller that belonging to a tables of an schema under a
database.

If I use Navicat for modeller, I have to drag and drop whole tables which I
want to add to data modeller. So It's pretty manual process.

Is there any way to display an er modeller that show only chosen schema
under the database?

Any advice would be appreciated.

Regards,

-- 
Gunce Kaya

[GENERAL] Fwd: idea for big speed optimization

[Андрей Брюхов]

-- Forwarded message --
From: Андрей Брюхов 
Date: 2016-12-22 17:08 GMT+03:00
Subject: idea for big speed optimization
To: jdr...@postgresql.org


Hello!
Can Postgres Organization use free super optimizaing C compiler for compile
postgres (advenced builds) ?

https://www.pgroup.com/products/community.htm

Re: [GENERAL] Postgres 9.6 Streaming Replication on Solaris 10

[rich]

Hi John,    I agree with you.  On Solaris I should not have to set LD_LIBRARY_PATH.  The location of the *.so files is baked in.  The strange part is that postgres starts up without errors and can be accessed from pgAdmin 4.  The problem is occurring from the replication functionality.  I am beginning to suspect there is a problem with the binary that I downloaded from the postgres site.  When I execute ldd -r  /usr/postgres/9.6-pgdg/lib/libpqwalreceiver.so there are many symbols not found.  I do not know what to make of it just yet.  I did reinstall.Regards,Rich Rutkowskir...@salepointdata.com

Re: [GENERAL] error updating a tuple after promoting a standby

[Tom DalPozzo]

2016-12-22 10:23 GMT+01:00 Brian Sutherland :

> Perhaps try 9.5.5 which has a fix for a problem with the same symptoms:
>
> https://wiki.postgresql.org/wiki/Free_Space_Map_Problems
> https://www.postgresql.org/docs/9.5/static/release-9-5-5.
> html#AEN126074
>
> Yes it was that!
I tried the procedure in wiki and it worked.
Thank you very much!
Pupillo

Re: [GENERAL] Disabling inheritance with query.

[Edmundo Robles]

Sorry  the  full message is this

I  want to do that because,  I have  a  partitioned table  (big_table like
master and  child like ...t201610,t201611,t201612...)  and others
 t1,t2,t3,t4  have  foreign keys  reference to big_table  and i had many
trobules at insert data, reading the doc:
"A serious limitation of the inheritance feature is that indexes (including
unique constraints) and foreign key constraints only apply to single
tables, not to their inheritance children. "


So i have two  choices:

1. The obvious and boring  is create  triggers between tables to simulate
foreign key behavior. but  each insert in T1..T4 must read the whole  data
 in  child tables thinking on 20 million of records and growing  each
minute. So this  option  maybe is not the best  choice.


2. Another is create  child tables  for t1..t4  like
t1_201610,t2_201611,...  t4_201612, and create  the foreign keys
 referencing the right table. that sounds better for t1,t3 and t4   those
tables are historical no problem with that, but   t2 must be have the last
 information.

if i do a query on t2  i will have many records from child tables  of t2
instead the last record. Yes, i could  write  a query to get the last
record, but  that query is hardcoded  inside  a program, and i don't have
 the code :( ,


The creation of child_tables  must be  automatic at insert data , so  when
the new child is created  i must  disable   the inherits for  all  childs
of t2 and set the inhertis to the new  t2_child





On Thu, Dec 22, 2016 at 8:51 AM, Edmundo Robles 
wrote:

> I want to do that because,  I have  a  partitioned table  (big_table)  and
> others  (t1,t2,t3,t4)  have  foreign keys  reference to big_table  and i
> had many trobules at insert data, reading the doc:
> "A serious limitation of the inheritance feature is that indexes
> (including unique constraints) and foreign key constraints only apply to
> single tables, not to their inheritance children. "
>
> On Wed, Dec 21, 2016 at 4:58 PM, Tom Lane  wrote:
>
>> Edmundo Robles  writes:
>> > i need  disable  inheritance  from many tables in a query like
>> > "delete from pg_inherits where inhparent=20473"  instead alter table ...
>> > but  is safe?   which is the risk for  database if  i  delete it?
>>
>> This seems really dangerous.  You're certainly missing the pg_depend
>> linkages, not to mention attribute inheritance counts in pg_attribute,
>> and there may be other things I'm not remembering offhand.
>>
>> Why can't you use the normal ALTER TABLE approach?
>>
>> regards, tom lane
>>
>
>

Re: [GENERAL] Disabling inheritance with query.

[Scott Mead]

On Thu, Dec 22, 2016 at 9:51 AM, Edmundo Robles 
wrote:

> I want to do that because,  I have  a  partitioned table  (big_table)  and
> others  (t1,t2,t3,t4)  have  foreign keys  reference to big_table  and i
> had many trobules at insert data, reading the doc:
> "A serious limitation of the inheritance feature is that indexes
> (including unique constraints) and foreign key constraints only apply to
> single tables, not to their inheritance children. "
>
> On Wed, Dec 21, 2016 at 4:58 PM, Tom Lane  wrote:
>
>> Edmundo Robles  writes:
>> > i need  disable  inheritance  from many tables in a query like
>> > "delete from pg_inherits where inhparent=20473"  instead alter table ...
>> > but  is safe?   which is the risk for  database if  i  delete it?
>>
>
If you need to do it from many tables, you could write a script to generate
the ALTER TABLE statements

select 'ALTER TABLE ' || schemaname ||'.' || psut.relname || ' NO INHERIT '
|| pc.relname ||';'
  from pg_stat_user_tables psut, pg_class pc, pg_inherits pi
 where pi.inhrelid = psut.relid
   AND pi.inhparent = pc.oid
   AND pi.inhparent = 20473;

I wouldn't manually hit the catalogs, but, this will write all of the ALTER
TABLE statements that you need.


>
>> This seems really dangerous.  You're certainly missing the pg_depend
>> linkages, not to mention attribute inheritance counts in pg_attribute,
>> and there may be other things I'm not remembering offhand.
>>
>> Why can't you use the normal ALTER TABLE approach?
>>
>> regards, tom lane
>>
>
>


-- 
--
Scott Mead
Sr. Architect
*OpenSCG *
http://openscg.com

Re: [GENERAL] Disabling inheritance with query.

[Edmundo Robles]

I want to do that because,  I have  a  partitioned table  (big_table)  and
others  (t1,t2,t3,t4)  have  foreign keys  reference to big_table  and i
had many trobules at insert data, reading the doc:
"A serious limitation of the inheritance feature is that indexes (including
unique constraints) and foreign key constraints only apply to single
tables, not to their inheritance children. "

On Wed, Dec 21, 2016 at 4:58 PM, Tom Lane  wrote:

> Edmundo Robles  writes:
> > i need  disable  inheritance  from many tables in a query like
> > "delete from pg_inherits where inhparent=20473"  instead alter table ...
> > but  is safe?   which is the risk for  database if  i  delete it?
>
> This seems really dangerous.  You're certainly missing the pg_depend
> linkages, not to mention attribute inheritance counts in pg_attribute,
> and there may be other things I'm not remembering offhand.
>
> Why can't you use the normal ALTER TABLE approach?
>
> regards, tom lane
>

Re: [GENERAL] How well does PostgreSQL 9.6.1 support unicode?

[Vick Khera]

On Wed, Dec 21, 2016 at 11:31 AM, Tom Lane  wrote:

> Well, we're picky to the extent that RFC 3629 tells us to be picky:
> http://www.faqs.org/rfcs/rfc3629.html
>

And I'm *GLAD* it is that way. Who wants garbage in their database? :)

Re: [GENERAL] Disabling inheritance with query.

[Jaime Soler]

You should use alter table XX NO INHERIT parent_table;


2016-12-22 9:49 GMT+01:00 Francisco Olarte :

> Edmundo:
>
> On Wed, Dec 21, 2016 at 11:36 PM, Edmundo Robles 
> wrote:
> > i need  disable  inheritance  from many tables in a query like
> > "delete from pg_inherits where inhparent=20473"  instead alter table ...
> > but  is safe?   which is the risk for  database if  i  delete it?
>
> Dangers of touching the catalog directly have already been pointed by
> TL, along with the question of why isn't normal ALTER TABLE ok.
>
> If it is because there are a lot of childs, I would like to point a
> simple script ( if you are fluent in any scripting language, or even
> in SQL ) can be used to automatically generate a bunch of alter table
> commands. Even a simple text editor will do ( turn your query above
> into something generating a bunch of table names, edit it ). Or just
> try something like ( beware, untested )
>
> with childs as (select relname from pg_class, pg_inherits where
> pg_class.oid=inhrelid and inhparent='20473)
> SELECT 'ALTER TABLE ' || relname || ' rest of alter table command;'
> from childs ;
>
> And feed the result back to the server using your favorite tool (
> quoting maybe needed, schema names may be needed, YMMV ).
>
> Francisco Olarte.
>
>
> --
> 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] error updating a tuple after promoting a standby

[Brian Sutherland]

Perhaps try 9.5.5 which has a fix for a problem with the same symptoms:

https://wiki.postgresql.org/wiki/Free_Space_Map_Problems
https://www.postgresql.org/docs/9.5/static/release-9-5-5.html#AEN126074

On Wed, Dec 21, 2016 at 10:51:47AM +0100, Tom DalPozzo wrote:
> Hi,
> I was doing some tests with backup, replication, standby. After promoting a
> standby server, I found my db in a condition that raises me an error while
> trying to update a particular tuple.
> Below here you can se my UPDATE statment and the error raised.
> The select * from stato where id=409; executed immediately after worked
> well however.
> I checked the file and it's readable.
> Before my standby promotion test I performed millions of this UPDATE
> statments without problem on my db.
> I can not reproduce the issue.
> 
> Perhaps I did something wrong during my test but I don't know what. I
> didn't touch any file in base directory however.
> Anyway I'd like to know if in your opinion it's possible that this error
> was caused by something wrong done by me or if it should never happen as
> the file is perfectly readable.
> 
> Regards
> Pupillo
> 
> 
> 
> 
> psql (9.5.4)
> Type "help" for help.
> 
> ginopino=# UPDATE stato SET
> dati='\x5353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353'
> WHERE id=409;
> ERROR:  could not read block 12281 in file "base/16384/29153": read only 0
> of 8192 bytes
> ginopino=# select * from stato where id=409;  <<< IT WORKS FINE

-- 
Brian Sutherland


-- 
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] Disabling inheritance with query.

[Francisco Olarte]

Edmundo:

On Wed, Dec 21, 2016 at 11:36 PM, Edmundo Robles  wrote:
> i need  disable  inheritance  from many tables in a query like
> "delete from pg_inherits where inhparent=20473"  instead alter table ...
> but  is safe?   which is the risk for  database if  i  delete it?

Dangers of touching the catalog directly have already been pointed by
TL, along with the question of why isn't normal ALTER TABLE ok.

If it is because there are a lot of childs, I would like to point a
simple script ( if you are fluent in any scripting language, or even
in SQL ) can be used to automatically generate a bunch of alter table
commands. Even a simple text editor will do ( turn your query above
into something generating a bunch of table names, edit it ). Or just
try something like ( beware, untested )

with childs as (select relname from pg_class, pg_inherits where
pg_class.oid=inhrelid and inhparent='20473)
SELECT 'ALTER TABLE ' || relname || ' rest of alter table command;'
from childs ;

And feed the result back to the server using your favorite tool (
quoting maybe needed, schema names may be needed, YMMV ).

Francisco Olarte.


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