[GENERAL] Database fixed size

[Adir Shaban]

Hey,

Is there anyway to limit a database size?
For example, I need to create a database for user X and I don't want it to
use more than 5 GB.

Re: [GENERAL] Database fixed size

[amul sul]

On Wed, Sep 28, 2016 at 1:39 PM, Adir Shaban  wrote:
> Hey,
>
> Is there anyway to limit a database size?
Nope.
check this 
https://www.postgresql.org/message-id/619537.42270.qm%40web53708.mail.re2.yahoo.com
thread.

Regards,
Amul


-- 
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] Database fixed size

[Albe Laurenz]

Adir Shaban wrote:
> Is there anyway to limit a database size?
> For example, I need to create a database for user X and I don't want it to 
> use more than 5 GB.

You can create a tablespace on a device with limited size.
Then you can create the database on that tablespace.

Yours,
Laurenz Albe

-- 
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] isnull() function in pgAdmin3

[Alexander Farber]

Maybe your are after IS NOT DISTINCT FROM NULL

https://www.postgresql.org/docs/current/static/functions-comparison.html

Re: [GENERAL] Database fixed size

[Tom Lane]

Albe Laurenz  writes:
> Adir Shaban wrote:
>> Is there anyway to limit a database size?
>> For example, I need to create a database for user X and I don't want it to 
>> use more than 5 GB.

> You can create a tablespace on a device with limited size.
> Then you can create the database on that tablespace.

Note the reference to tablespace.  It's unwise to just put the whole
cluster on a tiny device (or equivalently, attempt to solve this with
OS-level disk quotas applied to the whole installation).  The reason is
that PG gets very unhappy if it runs out of WAL space.  Hitting a limit
on table size per se behaves a bit more sanely, though even there you
can get into trouble --- for instance, in some situations VACUUM will
try to allocate additional disk space, making recovery harder.

regards, tom lane


-- 
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] how to monitor the progress of really large bulk operations?

[Mike Sofen]

From: Pavel StehuleSent: Tuesday, September 27, 2016 9:18 PM
2016-09-28 6:13 GMT+02:00 Pavel Stehule mailto:pavel.steh...@gmail.com> >:

Hi

2016-09-27 23:03 GMT+02:00 Mike Sofen mailto:mso...@runbox.com> >:

Hi gang,

how to view the state of a transaction in flight, seeing how many rows have 
been read or inserted (possible for a transaction in flight?), memory 
allocations across the various PG processes, etc.

some years ago I used a trick 
http://okbob.blogspot.cz/2014/09/nice-unix-filter-pv.html#links

 

pltoolbox has counter function 
https://github.com/okbob/pltoolbox/blob/master/utils.c

pavel=# insert into omega2 select (x.xx).* 
   from (select pst.counter(omega,20, true) xx 
from omega
) x;
NOTICE:  processed 20 rows, current value is '(5,8)'
NOTICE:  processed 20 rows, current value is '(5,8)'

Regards

Pavel

 

 

Pavel - That’s a very interesting function and thanks for sharing your toolbox. 
 The big question of course, is what is the impact on performance, scalability 
and stability?  Would it work inside of a stored function that would allow me 
write out the progress to a tracking table?  

 

Mike

Re: [GENERAL] Database fixed size

[Adir Shaban]

So how did heroku limit the database size per user?
I thought about creating triggers on insert and updates but that will lead
to bad performance

On Wed, Sep 28, 2016, 15:31 Tom Lane  wrote:

> Albe Laurenz  writes:
> > Adir Shaban wrote:
> >> Is there anyway to limit a database size?
> >> For example, I need to create a database for user X and I don't want it
> to use more than 5 GB.
>
> > You can create a tablespace on a device with limited size.
> > Then you can create the database on that tablespace.
>
> Note the reference to tablespace.  It's unwise to just put the whole
> cluster on a tiny device (or equivalently, attempt to solve this with
> OS-level disk quotas applied to the whole installation).  The reason is
> that PG gets very unhappy if it runs out of WAL space.  Hitting a limit
> on table size per se behaves a bit more sanely, though even there you
> can get into trouble --- for instance, in some situations VACUUM will
> try to allocate additional disk space, making recovery harder.
>
> regards, tom lane
>

Re: [GENERAL] how to monitor the progress of really large bulk operations?

[Pavel Stehule]

2016-09-28 14:34 GMT+02:00 Mike Sofen :

> *From:* Pavel Stehule*Sent:* Tuesday, September 27, 2016 9:18 PM
> 2016-09-28 6:13 GMT+02:00 Pavel Stehule :
>
> Hi
>
> 2016-09-27 23:03 GMT+02:00 Mike Sofen :
>
> Hi gang,
>
> how to view the state of a transaction in flight, seeing how many rows
> have been read or inserted (possible for a transaction in flight?), memory
> allocations across the various PG processes, etc.
>
> some years ago I used a trick http://okbob.blogspot.cz/2014/
> 09/nice-unix-filter-pv.html#links
>
>
>
> pltoolbox has counter function https://github.com/okbob/
> pltoolbox/blob/master/utils.c
>
> pavel=# insert into omega2 select (x.xx).*
>
>from (select pst.counter(omega,20, true) xx
>
> from omega
>
> ) x;
>
> NOTICE:  processed 20 rows, current value is '(5,8)'
>
> NOTICE:  processed 20 rows, current value is '(5,8)'
>
> Regards
>
> Pavel
>
>
>
>
>
> Pavel - That’s a very interesting function and thanks for sharing your
> toolbox.  The big question of course, is what is the impact on performance,
> scalability and stability?  Would it work inside of a stored function that
> would allow me write out the progress to a tracking table?
>

When a IO is bottleneck then counter has zero overhead. The usage is same
as any PostgreSQL set returning function. This function should be stable -
it is pretty simple

Regards

Pavel


>
>
> Mike
>

Re: [GENERAL] isnull() function in pgAdmin3

[Adrian Klaver]

On 09/27/2016 01:10 PM, dudedoe01 wrote:

Thanks Kevin, Igor and Adrian. In MySQL with the isnull() function I get all
three values RPG INV, Owner Inventory, and Builder Inventory showed up in
the view while when I do the is null function in postgreSQL I only see
Builder Inventory and Owner Inventory show up in the view. I don't know why
the RPG_INV is not showing up.


You don't say what Postgres version you are on, but if it is 9.5+ then 
this may come into play:


https://www.postgresql.org/docs/9.5/static/release-9-5.html
"

Adjust operator precedence to match the SQL standard (Tom Lane)

The precedence of <=, >= and <> has been reduced to match that of <, > 
and =. The precedence of IS tests (e.g., x IS NULL) has been reduced to 
be just below these six comparison operators. Also, multi-keyword 
operators beginning with NOT now have the precedence of their base 
operator (for example, NOT BETWEEN now has the same precedence as 
BETWEEN) whereas before they had inconsistent precedence, behaving like 
NOT with respect to their left operand but like their base operator with 
respect to their right operand. The new configuration parameter 
operator_precedence_warning can be enabled to warn about queries in 
which these precedence changes result in different parsing choices.

"

Also from your original post:

(case
when
((`s`.`Funding_Date` = '')
and (isnull(`s`.`Actual_Close_Date`)
or (`s`.`Actual_Close_Date` = '')))
then'RPG_INV'
when
((isnull(`s`.`Funding_Date`)

  or (`s`.`Funding_Date` <> ''))
and ((`s`.`Actual_Close_Date` = '')
or isnull(`s`.`Actual_Close_Date`)))
then
'Builder_Inventory'
else 'Owner_Inventory'
end) AS `Lot_Status`,


and from below:

(case when
((l."Funding_Date" = '') and ((h."Actual_Close_Date") is null
or (h."Actual_Close_Date" = ''))) then 'RPG Inventory'
when
(((l."Funding_Date") is null or (l."Funding_Date" <> ''))
and ((h."Actual_Close_Date" = '') or
(h."Actual_Close_Date") is null))
then
'Builder Inventory'
else 'Owner Occupied'
end) AS "Lot_Status",


I would try something like:

((l."Funding_Date" = '') and ((h."Actual_Close_Date" is null)
or (h."Actual_Close_Date" = '')))






In MySQL:

Owner Inventory is 4481 rows of data
Builder Inventory is 1312 rows of data
RPG_Inv is 374 rows of data

gives the total of 6167 rows

In postgreSQL:

Owner Inventory is 4521 rows of data
Builder inventory is 1646 rows of data

gives the total of 6167 rows

create view si_model as select
c."DCAD_Prop_ID" as DCAD_ID,
m."Address" as Address,
m."Addition" as Addition,
m."Block" as Block,
m."Lot" as Lot,
m."Lot_Size" as Lot_Size,
m."Lot_Type" as Lot_Type,
l."Funding_Date" as Lot_Sale_Date,
h."Actual_Close_Date" as Home_Closing_Date,
m."District" as District,
g."nhs_builder" as Builder,
g."nhs_sale_date" as NHSContractDate,
'' as "Banks & Ind. Owned Lots",
'' as "Repurchased",
m."Reserved_Lots" as Reserved,
d."Permit__" as "Permit #",
d."Permit_Date" as "Permit Date",
d."Foundation_Date" as "Foundation Date",
d."Frame_Date" as "Frame Date",
d."HCS" as HCS,
'' as "Notes_Comments",
l."Lot_Status" as "Lot Funding Status",
'' as "Property Description",
'' as "GIS Map",
d."Project_ID" as Project_ID,
(case when
((l."Funding_Date" = '') and ((h."Actual_Close_Date") is null
or (h."Actual_Close_Date" = ''))) then 'RPG Inventory'
when
(((l."Funding_Date") is null or (l."Funding_Date" <> ''))
and ((h."Actual_Close_Date" = '') or
(h."Actual_Close_Date") is null))
then
'Builder Inventory'
else 'Owner Occupied'
end) AS "Lot_Status",

((case when c."DCAD_Prop_ID" = m."DCAD_Prop_ID" then 'YES' else '' end)) as
"Home Sale",
((case when m."Address" =  s."Address_of_Inventory_Home" then 'C Spec' else
'' end)) as "Current Specs",
((case when g."nhs_address" = m."Address" and g."nhs_can" = 'false' and
g."nhs_build_spec" = 'Build' then 'Build'
when g."nhs_address" = m."Address" and g."nhs_can" = 'false' and
g."nhs_build_spec" = 'Spec' then 'Spec' else '' end))
as "Build/Spec"
from "Calculations" c
left join "MasterLotList" m on ((c."DCAD_Prop_ID" = m."DCAD_Prop_ID"))
left join "HomeClosings" h on ((h."Address" = m."Address"))
left join "GrossNewHomeSales" g on ((g."nhs_address" = m."Address"))
left join "HCSTable" d on ((d."DCAD_Prop_ID" = c."DCAD_Prop_ID"))
left join "LotSales" l on ((l."DCAD_Prop_ID" = c."DCAD_Prop_ID"))
left join "CurrentSpecs" s on ((s."Address_of_Inventory_Home" =
m."Address"))

Any help provided would be greatly appreciated.



--
View this message in context: 
http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p

Re: [GENERAL] isnull() function in pgAdmin3

[Thomas Kellerer]

dudedoe01 schrieb am 27.09.2016 um 19:04:
> I am trying to emulate the isnull() function used in MySQL into postreSQL. I
> have tried different ways such is null but it's not producing the results
> desired. I am doing a data migration from MySQL into postgreSQL and need
> help with the isnull() in pgAdmin3.
> 
> Any assistance provided would be greatly appreciated.
> 
> Thanks,
> 
> In MySQL:
> 
> (case
> when
> ((`s`.`Funding_Date` = '')
> and (isnull(`s`.`Actual_Close_Date`)
> or (`s`.`Actual_Close_Date` = '')))
> then
> 'RPG_INV'
> when
> ((isnull(`s`.`Funding_Date`)
> or (`s`.`Funding_Date` <> ''))
> and ((`s`.`Actual_Close_Date` = '')
> or isnull(`s`.`Actual_Close_Date`)))
> then
> 'Builder_Inventory'
> else 'Owner_Inventory'
> end) AS `Lot_Status`,
> 

Unrelated, but: why are you storing DATEs in VARCHAR columns? 

A migration might be a good moment to clean up the data model and store DATEs 
in a DATE (or TIMESTAMP) column. 





-- 
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] isnull() function in pgAdmin3

[Kevin Grittner]

On Tue, Sep 27, 2016 at 3:12 PM, dudedoe01  wrote:
> The tables I migrated from MySQL into postgreSQL have exactly the same amount
> of rows of data so the problem is inside the view being created.

Have you actually confirmed that there are any cases where
isnull(expression) yields a different result than (expression) is
null when the expression gives the same value?  I'm suspicious that
an expression could be yielding a different result, perhaps based
on join conditions handling comparisons between null values
differently.  Remember, for example, that in PostgreSQL NULL = NULL
does not evaluate to TRUE.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

[GENERAL] Graphical entity relation model

[jotpe]

Does anybody know a Software for generating graphical entity relation models 
from existing postgresql databases?

Best regards Johannes

Re: [GENERAL] Graphical entity relation model

[Scott Mead]

On Wed, Sep 28, 2016 at 4:17 PM, jotpe  wrote:

> Does anybody know a Software for generating graphical entity relation
> models from existing postgresql databases?
>
>
My personal favorites (in order):
schemaspy.sf.net
https://www.dbvis.com/
http://www.sqlpower.ca/page/architect


> Best regards Johannes




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

[GENERAL] cluster on table rewrite

[Jeff Janes]

I need to add a new column to my largest table, something like:

alter table foo add column col_15 text not null default 'foobar';

I am tempted to add the column as NULL, and then use coalesce and nullif in
the client code to re-interpret NULL as being the value 'foobar'.  But I
think that that would be penny wise and pound foolish, so am willing to
bite the bullet of doing a table rewrite.

But while I am rewriting it anyway, is there a way to get it to re-CLUSTER
on the cluster index, as one operation?  The rewrite caused by the ADD
COLUMN doesn't automatically cluster.

The closest I can hit upon is to do something like:

create table foo_new as select *, 'foobar'::text as col_15 from foo order
by col_8;

But then I have to manually juggle renaming tables and foreign key
constraints and such.

Is there a better way?

Cheers,

Jeff

Re: [GENERAL] Graphical entity relation model

[Mike Sofen]

From: jotpe   Sent: Wednesday, September 28, 2016 1:17 PM



Does anybody know a Software for generating graphical entity relation models 
from existing postgresql databases?
Best regards Johannes

---

I don’t know any useful free ones, but my favorite less expensive one (and the 
one I’ve used for the last few years) is xcase (http://www.xcase.com/) .  It 
can reverse engineer against most current popular databases including Postgres. 
 The current version has 2 postgres quirks: 1) you cannot (for some bizzare 
reason) declare a json/jsonb column, so I declare it as text and simply adjust 
the generated DDL as needed.  2) you cannot declare an index on a text column 
(as if they are thinking it is akin to a sql server text column).  Other than 
those issues, which can be easily worked around, it is by far the fastest 
modeling tool I’ve ever used (I’ve used the more famous ones), and the fully 
comprehensive, with a very modern looking UI.

 

In contrast, there are the over-priced dinosaurs with old ugly UIs.  A while 
back I reviewed some of the modeling tools, and none did it for me, I went 
ahead and got another license to xcase.

 

Mike Sofen (Synthetic Genomics)

Re: [GENERAL] Graphical entity relation model

[Stephen Davies]

On 29/09/16 05:47, jotpe wrote:

Does anybody know a Software for generating graphical entity relation models
from existing postgresql databases?

Best regards Johannes


I like SchemaSpy.

--
=
Stephen Davies Consulting P/L Phone: 08-8177 1595
Adelaide, South Australia.Mobile:040 304 0583


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

[Francisco Reyes]

On 09/26/2016 09:05 PM, Michael Paquier wrote:

What are the files in pg_xlog/archive_status? Do see a lot of .ready
entries? Perhaps you could de-bloat things by using archive_command =
'/bin/true' (REM on Windows as far as I recall).



There is no archive_status

 ll 9.3/main/pg_xlog/ |grep arch
postgres@s0pdb03:~$

As for changing archive_command to /bin/true, don't see how that would 
help. My archive command is pointing to a completely different folder.



Just try anyway, changed archive_command to bin true. Files in pg_xlog 
keep growing. Currently at over 5K files. Isn't wal_keep_segments the 
upper limit of how many wal files would be retained in Postgresql 9.3?



wal_keep_segments=300

checkpoint_segments=6


Yet have over 5K files, and more getting created, in my pg_xlog folder.



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

[Michael Paquier]

On Thu, Sep 29, 2016 at 2:25 PM, Francisco Reyes  wrote:
> There is no archive_status
>
>  ll 9.3/main/pg_xlog/ |grep arch
> postgres@s0pdb03:~$

Meh. There must be a folder named archive_status in pg_xlog. Or
something is broken with your installation. Don't you have in your log
files something like that?
LOG:  could not create archive status file
"pg_xlog/archive_status/00010001.ready": No such file
or directory
That's what you would get if archive_status was for some reason
missing, and pg_xlog would just keep the WAL segments around until its
partition gets full.
-- 
Michael


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