RDS Monitoring using PMM2, Missing Host Metrics for Postgres

I am using PMM2 for monitoring all my RDS instances.

I added Postgres as a remote database, But by adding a database as remote in PMM2 we miss host-level metrics like CPU, Memory etc. What is the recommended way of adding Postgres (RDS) or a way to inject missing metrics to get the complete Node Summary?

Go to Source
Author: xrage

ERROR: 42883: function “lock_database” does not exist at character 8

I’am working on a gis project with postrgresql, and with ruby on rails tools, I can write data to the db.
But when I checking the log of postgresql, I got the following error:

2020-08-23 22:39:32.454 CST [79106] chentoz@gis-edit ERROR: 42883: function “lock_database” does not exist at character 8

I got no clue from google about topic like “lock_database”, and there is also a saying that database lock is not implemented yet.

Have any guy met with error or some hints for digging the error ?

Go to Source
Author: tonyc

What database and cloud service should I use for an Instagram-like app?

I am a beginner in the world of databases and I’m so confused with the amount of tools available. I am building an Instagram like app in Flutter and wanted to know what database should I study for it?

And which cloud service should I use, AWS or Azure?

Go to Source
Author: Aeden Thomas

What exactly does PostgreSQL (or other databases) do internally when you “prepare” a query rather than just running it directly?

When I face a huge number of repeated INSERTs in a loop, I tend to first create a “prepare skeleton query” prior to the loop, and in the loop, I simply “execute” this prepared query and send it all the values. I heard long ago, and can understand in some abstract sense, that this is more optimized than just having the loop with a normal parameterized query in it.

However, I don’t understand what exactly PG is doing that makes it so much faster. If it even is so much faster. I frankly have never done any real benchmarks to compare the two different methods.

Also, shouldn’t PG be “warmed up” to a query once you repeat it several (let alone many) times in very short succession, and perhaps do the same thing as I do manually, but internally, when I just use a normal parameterized query?

I find myself constantly second-guessing what the database is up to internally. I have no real grasp of how “smart” it is. I fear that I’m doing a lot of things which are meaningless because it’s already taken care of internally by its smart code.

Maybe “preparing” and “executing” is an archaic practice which has no real benefit these days?

It sounds like PG is basically allocating resources to “prepare” for upcoming huge amounts of similar INSERT queries, but I don’t understand what exactly it would do differently compared to just executing them one by one. I also don’t understand what the point would be of a non-INSERT prepared query/statement.

PS: Just to clear up any confusion: I never use non-parameterized queries, whether they are prepared or not. A lot of people confuse “parameterized queries” and “prepared statements”. Even I called the latter “prepared queries” in this question…

Go to Source
Author: Jevontay Rohler

PostgreSQL rule to delete record before insertion, if the record has changed

I’m not sure it’s possible to do this with a rule, but what I want to do is, when you insert a record:

  • If it doesn’t exist, just insert it as usual.
  • If it exists, but each field is equal to what you’re inserting, then do nothing.
  • If it exists, but any field differs from what you’re inserting, then delete the old record and insert the new one.

The reason for this is that, in the last case, I want to use the cascade deletion productively. That is, if a record has changed, then everything that refers to it is now void and should be removed.

Let’s say my table is:

create table foo (
  id  integer primary key,
  foo integer,
  bar integer
);

My first attempt was to just delete the old record regardless (before checking the difference):

create rule refresh as
  on insert to foo
  do instead (
    delete from foo where id = new.id;
    insert into foo values (new.id, new.foo, new.bar));

This doesn’t work; it’s an infinite loop.

Is it possible to do what I’m after with rules, or would I need a full-blown trigger?

Go to Source
Author: Xophmeister

How to use the i command in SQL-Shell?

I’m new to PostgreSQL and I wanted to use or run a specific file on the SQL-Shell on windows but I`m getting some errors of what I think is not making sense from my knowledge.

I ran the following…

i 'C: UsersUsernameDocumentsperson.sql' : No such file or directory

I don`t know why this is not functioning with the quotes and by default this file exists, which I downloaded from a website!

Through my experimentation, I ran the following again without quotes with a different error

i C: UsersUsernameDocumentsperson.sql : Permission denied

I don’t know why it says at first it doesn’t exist but later getting no permission over the file. My question is what is the problem with the following executions and is something wrong with my running?

I looked at this site and saw common problems but it couldn’t help me further!

Thanks!

Go to Source
Author: Gregory