Thread: 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.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 rod@iol.ie
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?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.
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.
I need to be notified when the table is full to launch a script whis dumps this table.
2018-01-19 16:44 GMT+01:00 David G. Johnston <david.g.johnston@gmail.com>:
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?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. 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.
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.
On Fri, Jan 19, 2018 at 03:40:01PM +0000, Raymond O'Donnell wrote: > 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"? There could be two definitions here: 1) A table contains more data than a customly-defined amount of data on-disk. 2) The partition where the table data is located runs out of disk space. -- Michael
Attachment
On Sun, Jan 21, 2018 at 1:27 PM, Michael Paquier <michael.paquier@gmail.com> wrote: > On Fri, Jan 19, 2018 at 03:40:01PM +0000, Raymond O'Donnell wrote: ... >> How do you define "full"? > There could be two definitions here: > 1) A table contains more data than a customly-defined amount of data > on-disk. > 2) The partition where the table data is located runs out of disk > space. I see a third definition. No more rows can be inserted without previously deleting. The simplest case I can think of is "b boolean primary key" with two rows already in. But I doubt the OP was referring to any of that. Francisco Olarte
2018-01-21 19:31 GMT+01:00 Francisco Olarte <folarte@peoplecall.com>: > On Sun, Jan 21, 2018 at 1:27 PM, Michael Paquier > <michael.paquier@gmail.com> wrote: >> On Fri, Jan 19, 2018 at 03:40:01PM +0000, Raymond O'Donnell wrote: > ... >>> How do you define "full"? The only possible and meaningful case, IMHO, as stated by David earlier, is "file system full". Which is communicated by Postgres with the "Class 53 — Insufficient Resources" error codes. Please refer to official documentation like: https://www.postgresql.org/docs/10/static/errcodes-appendix.html For specific programming languages more details need to be checked.
> On Jan 21, 2018, at 6:44 PM, Vincenzo Romano <vincenzo.romano@notorand.it> wrote: > > 2018-01-21 19:31 GMT+01:00 Francisco Olarte <folarte@peoplecall.com>: >> On Sun, Jan 21, 2018 at 1:27 PM, Michael Paquier >> <michael.paquier@gmail.com> wrote: >>> On Fri, Jan 19, 2018 at 03:40:01PM +0000, Raymond O'Donnell wrote: >> ... >>>> How do you define "full"? > > The only possible and meaningful case, IMHO, as stated by David > earlier, is "file system full". If your filesystem is full you're pretty much off the air. It's something that should never happen on a production system. So ... any automation around "the filesystem is full" is going to be much the same as "the server is dead". You're unlikely to be able to do anything useful from the app at that point, let alone from a trigger function. If the answer involves handling the case where the file system is full we're not answering a useful question, and the original poster probably needs to clarify. > Which is communicated by Postgres with the "Class 53 — Insufficient > Resources" error codes. > Please refer to official documentation like: > > https://www.postgresql.org/docs/10/static/errcodes-appendix.html > > For specific programming languages more details need to be checked. > Cheers, Steve
> On Jan 21, 2018, at 6:44 PM, Vincenzo Romano <vincenzo.romano@notorand.it> wrote:
>
> 2018-01-21 19:31 GMT+01:00 Francisco Olarte <folarte@peoplecall.com>:
>> On Sun, Jan 21, 2018 at 1:27 PM, Michael Paquier
>> <michael.paquier@gmail.com> wrote:
>>> On Fri, Jan 19, 2018 at 03:40:01PM +0000, Raymond O'Donnell wrote:
>> ...
>>>> How do you define "full"?
>
> The only possible and meaningful case, IMHO, as stated by David
> earlier, is "file system full".
If your filesystem is full you're pretty much off the air. It's something
that should never happen on a production system. So ... any automation
around "the filesystem is full" is going to be much the same as "the server
is dead". You're unlikely to be able to do anything useful from the
app at that point, let alone from a trigger function.
Well, I'll agree that PostgreSQL is likely "off the air". On my system (Linux/Intel), I use quotas to restrict a user's use of disk space. Yes, even the PostgreSQL user. I also, at times, use a separate filesystem for a database and use a TABLESPACE for the tables within it. This is easy to do with Linux because I can create a filesystem in a regular disk file. It doesn't perform as well as "native", but my system is not a highly used, performance oriented, system. Use of a separate filesystem and tablespaces is, I think, a decent way to control disk usage for "something" so that if "something" goes "insane", it can't really impact "others" very much. Of course, others may reasonably disagree with me on this. Each cat, his own rat.
If the answer involves handling the case where the file system is full we're
not answering a useful question, and the original poster probably needs to
clarify.
> Which is communicated by Postgres with the "Class 53 — Insufficient
> Resources" error codes.
> Please refer to official documentation like:
>
> https://www.postgresql.org/docs/10/static/errcodes- appendix.html
>
> For specific programming languages more details need to be checked.
>
Cheers,
Steve
I have a theory that it's impossible to prove anything, but I can't prove it.
Maranatha! <><
John McKown
John McKown
2018-01-22 14:58 GMT+01:00 John McKown <john.archie.mckown@gmail.com>: > On Mon, Jan 22, 2018 at 2:07 AM, Steve Atkins <steve@blighty.com> wrote: >> >> >> > On Jan 21, 2018, at 6:44 PM, Vincenzo Romano >> > <vincenzo.romano@notorand.it> wrote: >> > >> > 2018-01-21 19:31 GMT+01:00 Francisco Olarte <folarte@peoplecall.com>: >> >> On Sun, Jan 21, 2018 at 1:27 PM, Michael Paquier >> >> <michael.paquier@gmail.com> wrote: >> >>> On Fri, Jan 19, 2018 at 03:40:01PM +0000, Raymond O'Donnell wrote: >> >> ... >> >>>> How do you define "full"? >> > >> > The only possible and meaningful case, IMHO, as stated by David >> > earlier, is "file system full". >> >> If your filesystem is full you're pretty much off the air. It's something >> that should never happen on a production system. So ... any automation >> around "the filesystem is full" is going to be much the same as "the >> server >> is dead". You're unlikely to be able to do anything useful from the >> app at that point, let alone from a trigger function. > > > Well, I'll agree that PostgreSQL is likely "off the air". On my system > (Linux/Intel), I use quotas to restrict a user's use of disk space. Yes, > even the PostgreSQL user. I also, at times, use a separate filesystem for a > database and use a TABLESPACE for the tables within it. This is easy to do > with Linux because I can create a filesystem in a regular disk file. It > doesn't perform as well as "native", but my system is not a highly used, > performance oriented, system. Use of a separate filesystem and tablespaces > is, I think, a decent way to control disk usage for "something" so that if > "something" goes "insane", it can't really impact "others" very much. Of > course, others may reasonably disagree with me on this. Each cat, his own > rat. > > >> >> >> If the answer involves handling the case where the file system is full >> we're >> not answering a useful question, and the original poster probably needs to >> clarify. >> >> > Which is communicated by Postgres with the "Class 53 — Insufficient >> > Resources" error codes. >> > Please refer to official documentation like: >> > >> > https://www.postgresql.org/docs/10/static/errcodes-appendix.html >> > >> > For specific programming languages more details need to be checked. >> > >> >> Cheers, >> Steve > > > > > -- > I have a theory that it's impossible to prove anything, but I can't prove > it. > > Maranatha! <>< > John McKown Going back to the original question, how can a client know that "a table is full"? I think that it should see commands like INSERTs and UPDATEs failing with some error code. Maybe those I pointed to. All other facts seem to fall beyond the question scope, while being really interesting and insightful. -- Vincenzo Romano - NotOrAnd.IT Information Technologies -- NON QVIETIS MARIBVS NAVTA PERITVS