pgaudit?
I found this, for RHEL 7: https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/repoview/pgaudit_96.html But I can not find the same package for PostgreSQL 10, is this an oversight or intensional or am I just blind? Regards, BTJ -- --- Bjørn T Johansen b...@havleik.no --- Someone wrote: "I understand that if you play a Windows CD backwards you hear strange Satanic messages" To which someone replied: "It's even worse than that; play it forwards and it installs Windows" ---
Notify client when a table was full
Hi, I'm looking for a function in postgresql which notify the client if a table was full or not.So I found the function Notify https://www.postgresql.org/docs/9.0/static/sql-notify.html. This function send a notification when a new action was done to the table. Is there a way to send a notification only when the table was full and no future actions (insertion of new rows for examples) will be done. I was connected to an external api and saving the data received from it to a postgres database and I want to be notified when the table was full and no rows will be inserted. Does it realizable or Should I create a trigger and listens for every insertion and notify the client?
Re: Notify client when a table was full
On 19/01/18 15:34, hmidi slim wrote: Hi, I'm looking for a function in postgresql which notify the client if a table was full or not.So I found the function Notify https://www.postgresql.org/docs/9.0/static/sql-notify.html. This function send a notification when a new action was done to the table. Is there a way to send a notification only when the table was full and no future actions (insertion of new rows for examples) will be done. I was connected to an external api and saving the data received from it to a postgres database and I want to be notified when the table was full and no rows will be inserted. Does it realizable or Should I create a trigger and listens for every insertion and notify the client? How do you define "full"? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie
Re: Notify client when a table was full
On Fri, Jan 19, 2018 at 8:34 AM, hmidi slim wrote: > Hi, > I'm looking for a function in postgresql which notify the client if a > table was full or not.So I found the function Notify > https://www.postgresql.org/docs/9.0/static/sql-notify.html. > This function send a notification when a new action was done to the table. > Is there a way to send a notification only when the table was full and no > future actions (insertion of new rows for examples) will be done. I was > connected to an external api and saving the data received from it to a > postgres database and I want to be notified when the table was full and no > rows will be inserted. Does it realizable or Should I create a trigger and > listens for every insertion and notify the client? > The only way a table becomes "full" is if you run out of disk space - and you should be monitoring that at the O/S level and not at a database table level... As for clients - if they are inserting data they will simply get failures when they attempt to do so - for me that seems like sufficient notification for something that should rarely if ever happen. David J.
Use left hand column for null values
I'm trying to fill up columns containing NULL with the most recent NOT NULL value from left to right. Example: Select 2, 1, null, null, 3 Should be converted into 2, 1, 1, 1, 3 The following query works but I wonder if there is an easier way for tables with 50 or more columns: with a (c1, c2, c3, c4, c5) as ( values(2, 1, null::int, null::int, 3) ) select c1, coalesce (c2, c1) as c2, coalesce (c3, c2, c1) as c3, coalesce (c4, c3, c2, c1) as c4, coalesce (c5, c4, c3, c2, c1) as c5 from a Thanks Klaus
Re: Notify client when a table was full
I need to be notified when the table is full to launch a script whis dumps this table. H.S 2018-01-19 16:44 GMT+01:00 David G. Johnston : > On Fri, Jan 19, 2018 at 8:34 AM, hmidi slim wrote: > >> Hi, >> I'm looking for a function in postgresql which notify the client if a >> table was full or not.So I found the function Notify >> https://www.postgresql.org/docs/9.0/static/sql-notify.html. >> This function send a notification when a new action was done to the >> table. Is there a way to send a notification only when the table was full >> and no future actions (insertion of new rows for examples) will be done. I >> was connected to an external api and saving the data received from it to a >> postgres database and I want to be notified when the table was full and no >> rows will be inserted. Does it realizable or Should I create a trigger and >> listens for every insertion and notify the client? >> > > The only way a table becomes "full" is if you run out of disk space - and > you should be monitoring that at the O/S level and not at a database table > level... > > As for clients - if they are inserting data they will simply get failures > when they attempt to do so - for me that seems like sufficient notification > for something that should rarely if ever happen. > > David J. >
Re: Notify client when a table was full
On Fri, Jan 19, 2018 at 8:47 AM, hmidi slim wrote: > I need to be notified when the table is full to launch a script whis dumps > this table. > Please don't top post. You are going to need to explain the entire process is greater detail if you want help. As Raymond said while I was writing, you at least need to define how you would know that the table is "full" before you can program something to detect the same. You mention an external system - are you dealing with a push or pull oriented synchronization? David J.
Re: Use left hand column for null values
On Fri, Jan 19, 2018 at 8:46 AM, wrote: > I’m trying to fill up columns containing NULL with the most recent NOT > NULL value from left to right. > > Example: > > Select 2, 1, null, null, 3 > > Should be converted into > > 2, 1, 1, 1, 3 > > > > The following query works but I wonder if there is an easier way for > tables with 50 or more columns: > > > > with a (c1, c2, c3, c4, c5) as ( > > values(2, 1, null::int, null::int, 3) > > ) > > select > > c1, > > coalesce (c2, c1) as c2, > > coalesce (c3, c2, c1) as c3, > > coalesce (c4, c3, c2, c1) as c4, > > coalesce (c5, c4, c3, c2, c1) as c5 > > from a > > > My $0.02 Unless you have some need to generalize I'd say just brute-force it and be done. You could maybe play with arrays to get something that looks different but I don't think it would be much shorter to code or easier to understand. It is structured enough that you could probably build the query as a string and then "EXECUTE" it but you'd probably spend more time working that out than just coding it once. If you don't have to return 50 columns but could return text (or an array) it might be worth some R&D to find something similar. One trick I use when dealing with structured but tedious queries is to write them in a spreadsheet. I between auto-fill and formulas I can usually speed up the input of the structured data compared to typing it out line-by-line manually. David J.
Query optimization with repeated calculations
Hello, I have a general query optimization question involving repeated calculations. I frequently want to make views that repeat a calculation over and over again in lots of columns. In the example below, let’s say it’s (a+b), but we can imagine the calculation being much more complicated. For example: CREATE VIEW AS SELECT (a+b)*c as c1, (a+b)*d as d1, (a+b)*e as e1 FROM table; My question is, is PostgreSQL "smart" enough to cache this (a+b) calculation in the above example, or does it recalculate it in each column? If it recalculates it in each column, would I generally get better performance by using a subquery to convince PostgreSQL to cache the result? For example: CREATE VIEW AS SELECT x*c as c1, x*d as d1, x*e as e1 FROM (SELECT (a+b) as x, * FROM table) x; Or perhaps I could use a CTE? In some instances, I might need 2 or more subqueries to “cache” all the calculations (if one column depends on another column that depends on another column), and I feel that sometimes Postgres makes better optimization decisions when there are not subqueries involved, but I am not sure. Obviously I could benchmark everything every single time this comes up, but there is overhead in that and some kind of rule of thumb or general guideline would be helpful as it is a frequent question for me. Thank you! Robert
AW: Use left hand column for null values
> [David G. Johnston] > My $0.02 > > Unless you have some need to generalize I'd say just brute-force it and be > done. > > You could maybe play with arrays to get something that looks different but I > don't think it would be much shorter to code or easier to understand. My concern was performance, because I feared that coalesce would evaluate e.g. column c1 50 times. But I did some testing and it seems to scale linear. 100,000 rows with 50 columns take 25 seconds on my test machine. Not exciting but something I can work with. I played already with arrays but what I came up with was slower than the plain coalesce. Klaus
Re: Query optimization with repeated calculations
Robert McGehee writes: > I frequently want to make views that repeat a calculation over and over > again in lots of columns. In the example below, let’s say it’s (a+b), but > we can imagine the calculation being much more complicated. > For example: > CREATE VIEW AS > SELECT (a+b)*c as c1, (a+b)*d as d1, (a+b)*e as e1 > FROM table; > My question is, is PostgreSQL "smart" enough to cache this (a+b) > calculation in the above example, or does it recalculate it in each column? Postgres will not notice that there's duplicate subexpressions. > If it recalculates it in each column, would I generally get better > performance by using a subquery to convince PostgreSQL to cache the > result? For example: > CREATE VIEW AS > SELECT x*c as c1, x*d as d1, x*e as e1 > FROM (SELECT (a+b) as x, * FROM table) x; Maybe, if the subexpression is expensive enough (a+b probably doesn't meet that threshold). You'd need to take care to prevent the subquery from being "flattened" into the upper query. Typically this might require an optimization fence like OFFSET 0, which is problematic for a view because it could prevent optimizations that are far more important than avoiding duplicated calculations. CTEs are likewise a rather blunt tool that could cost more than they save. It might work better to put the redundant calculations in a LATERAL subquery, ie CREATE VIEW AS SELECT x*c as c1, x*d as d1, x*e as e1 FROM tab, LATERAL (SELECT a+b as x OFFSET 0) ss; I haven't really experimented, but I think that this'd dodge most of the optimization pitfalls, except one: in this formulation, a+b will be evaluated for every "tab" row even if the particular use of the view does not demand any of the columns that use "x". regards, tom lane
Re: pgaudit?
On 01/19/2018 03:33 AM, Bjørn T Johansen wrote: I found this, for RHEL 7: https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/repoview/pgaudit_96.html But I can not find the same package for PostgreSQL 10, is this an oversight or intensional or am I just blind? Well pgAudit supports 10: https://github.com/pgaudit/pgaudit "pgAudit versions relate to PostgreSQL major versions as follows: pgAudit v1.2.X is intended to support PostgreSQL 10. " I cannot find it either, so I have to believe this is an oversight. You might want to contact the Yum team directly: https://yum.postgresql.org/contact.php Regards, BTJ -- Adrian Klaver adrian.kla...@aklaver.com
Re: Possible bug: could not open relation with OID [numbers] SQL State: XX000
I would like to refresh the topic and add another report about the issue that
just happened to me.I'm sure it's the toast table that cannot be opened
inside the function.I have added following RAISE NOTICE clauses to it and
run analyze inside of the function:
analyze verbose temp_table;
raise notice 'oid temp_table %', ( SELECT array_agg(relname::TEXT||
relfilenode::TEXT|| 'relpages:'||relpages::TEXT|| 'reltuples:' ||
reltuples::TEXT|| 'relallvisible:' ||relallvisible::TEXT||'reltoastrelid:'||
reltoastrelid::TEXT) FROM pg_class where relname= 'temp_table');
raise notice 'rel size %', (select
pg_total_relation_size('temp_table'));
It's pointing to the toast table:
1 live rows and 1 dead rows; 1 rows in sample, 1 estimated total
rowspsql:/tmp/gg:23: NOTICE: oid temp_table
{temp_table106538relpages:1reltuples:1relallvisible:0reltoastrelid:*106541*}psql:/tmp/gg:23:
NOTICE: rel size 32768psql:/tmp/gg:23: ERROR: could not open relation with
OID *106541*
Thank you for the advice about ON COMMIT DROP - it's working.When the table
size is smaller, about 16k this issue simply disappears.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
